Oracle Rant

As a best practice, I don’t like logging in as SYS or SYSTEM unless I have to. I also don’t like remote OS authentication. If I connect remote, I want to use a password. If I am on the box however, I like to just connect using OS authentication (not providing a username and / or password). Now I could also give this internal user access to connect over the network, but well, I am not going to go there. The purpose of this post is to show you what I did to set this up on Windows. On Linux or Unix, it is quite easy, on Windows there is a gotcha.

First, to use OS Authentication for Oracle logins, you need to understand a parameter.

SQL> show parameter authen

NAME                    TYPE        VALUE
----------------------- ----------- -------------
os_authent_prefix       string      OPS$
remote_os_authent       boolean     FALSE

The os_authent_prefix could really be set to anything you want, but I like the default. The other parameter is set to FALSE because I don’t want to trust other operating systems authentication.

Next, you need to create the user. How do you find out who that user is? Simple, run this query after you login locally.

SQL> select UPPER(sys_context('userenv','os_user')) from dual;

UPPER(SYS_CONTEXT('USERENV','OS_USER'))
-------------------------------------------------------------------
MACHINENAME\TOM

Now that I have this information, I can easily create the user and grant him the privilege he needs.

SQL> create user "OPS$MACHINENAME\TOM" identified externally;

SQL>

As you can see, Oracle prefixed my login with OPS$ that I defined in os_authent_prefix. I also had to use the “\” because Oracle sees my login as “MACHINENAME\TOM” and not just Tom. The gotcha is that you cannot create a user with the “\” without using double quotes. If you were a Windows Domain user account then you would use “DOMAINNAME\USERNAME”.

I hope that someone finds this little tip useful in case they want to setup OS Authentication on Windows : ).

Backup and Recovery is one of the most critical tasks that a DBA has to perform. Sadly, it is also one of the most misunderstood. RMAN should be the tool of choice for backup and recovery for Oracle databases. There is so much that can be done with RMAN. What Robert and Matthew do is break it down piece by piece. There is a great intro and they build on that in subsequent chapters. If you want to link RMAN directly with your backup software (i.e. Veritas, EMC, Legato, etc..) then the authors show you how. I used the Veritas piece to link RMAN in and it worked flawlessly. They go over the latest features including block changing files (for faster incremental backups), merging level 1 and level 0 backups (for faster recoveries without the overhead of backing up the whole database), and even cover what the flash recovery area is and how to use it. There is so much great material packed in this book. Yeah, you can try and read Oracle’s docs, but rather than just show you how to do it, the authors also teach you why to do it. You can read this book chronologically or use it as a reference. The authors writing style make this book a great joy to read. They also include a lot of great examples in case you like to learn by seeing. I would highly recommend this book if you are an Oracle DBA, whether you use RMAN or not. Afterall, RMAN is what you should be using .

Oracle Automatic Storage Management was introduced in Oracle 10g and improved upon in Oracle 11g. It’s the new storage filesystem from Oracle that runs on every platform. The idea behind ASM was to create a filesystem that was designed and integrated for the Oracle database. It gives high performance, easier administration, more flexibility, and clustering capabilities for Oracle RAC, and lower cost. It’s an exciting new technology that every Oracle DBA should look into. Instead of managing files you manage disk groups which are pools of storage. The book was written by a group of authors who have great insite into ASM including the Director of Development for ASM, Rich Long. They explain the basics of the storage stack. They then talk about configuring ASM and what the gotchas are for platforms like Solaris, AIX, Linux, Windows, and HP-UX. They authors even elaborate on multi-pathing software like HDLM and EMC Powerpath. They even talk in detail about the ASMLIB utility that is used for managing disks for ASM on Linux. Next, they discuss Diskgroups and Failure Groups in ASM. They then get into ASM administrations such as Files, Aliases, Templates, Rebalancing, Space Allocation, ASMCMD, Extents, Striping, Mirroring. They then get into deployment and Storage Array Configurations. The book finally ends with a Troubleshooting Guide with a clear explanation of the Alert Log files for ASM and some scripts that can be used to maintain an ASM environment.  I would highly recommend this book for any DBA that will be working with ASM and / or RAC. The high performance, ease of use,  and low cost make it a very attractive solution. This book will teach you everything you need to know about ASM and make you an ASM expert. After learning ASM, it also makes an excellent reference. I give it a perfect rating of 5 stars!

List all srvctl commands

srvctl –h

Shutdown service

srvctl stop service –d database_name

Shutdown the database on all nodes

srvctl stop database –d database_name

Shutdown a specific instance

srvctl stop instance –d database_name -i instance_name

Shutdown nodeapps (includes listener)

srvctl stop nodeapps –n node_name

! node_name is usually the hostname of the machine

Shutdown the listener

srvctl stop listener –n node_name

Shutdown ASM

srvctl stop asm –n node_name

To Startup, replace stop with start.

If you shutdown the database it will stop the service since the service is dependent upon the database. The reverse is also true. If you startup the service it will implicitly startup the database.

To check to see if your services are running.

$CRS_HOME/bin/crs_stat –t

If you want more detailed information, drop the –t.

$CRS_HOME/bin/crs_stat

You may include the $CRS_HOME/bin in your $PATH for ease of use.