Single Oracle Password File on Raw with RAC

Posted by Jeremy ⋅ March 21, 2007 ⋅ 2 Comments

Had an interesting question come up yesterday. Was on the phone with a colleague who’s configuring TSM backups on a 3-node cluster that he’s just setup for a client in Miami. He wanted to parallelize the backup job across the cluster and was configuring the channels in RMAN. They were not using the sys account for backups so they had to create another account with SYSDBA privileges.

Although it is administered with the same GRANT and REVOKE syntax as other roles, SYSDBA is not a normal role. When you grant the SYSDBA role to a user their password is added to a special O.S. file called the password file – not stored in the data dictionary like other roles. On unix platforms this password file file must reside in the $ORACLE_HOME/dbs and must be named orapw[SID]. On windows it resides in %ORACLE_HOME%database and must be called PWD[SID].ora. It is created with the orapwd command-line utility.

When creating the user account for RMAN backups this is the problem they encountered:

SQL> connect sys@db2rac1 as sysdba
Enter password: ******
Connected.
SQL> create user backupuser identified by backup;

User created.

SQL> grant connect,sysdba to backupuser;

Grant succeeded.

SQL> connect backupuser@db2rac1 as sysdba;
Enter password: ******
Connected.
SQL> connect backupuser@db2rac2 as sysdba;
Enter password: ******
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL>

Naturally, the problem is that by default each node has its own copy of the oracle password file. And oracle is only updating the file for the instance where you’re logged in when you type “GRANT SYSDBA”. Of course the other roles are granted globally:

SQL> connect backupuser@db2rac2
Enter password: ******
Connected.
SQL>

Well the workaround is obvious; just log into each instance and GRANT SYSDBA. But wouldn’t it be nice if you could just manage a single password file? This is certainly possible and I even remember setting it up on 9i RAC databases with OCFS 1. I’m pretty sure that I first stole the idea from Wim Coekaerts’ 2003 article about RAC on firewire. With OCFS it’s trivial; create the password on a shared filesystem with the orapwd utility then make symbolic links in the required location with the proper name. (Unfortunately you cannot do this on windows.)

However these days we’re strongly recommending ASM for most of our clients – so there’s no shared filesystem on which to create this file. You can work around this in the same way you do for the OCR, voting disk, and ASM spfile: use a raw disk.

================= NODE 1
[oracle@rh4lab15 ~]$ ls -l /dev/raw/raw3
crw-r-----  1 oracle oinstall 162, 3 Mar 21 08:51 /dev/raw/raw3

[oracle@rh4lab15 ~]$ cd $ORACLE_HOME/dbs
[oracle@rh4lab15 dbs]$ dd if=orapwdb2rac1 of=/dev/raw/raw3
7+0 records in
7+0 records out
[oracle@rh4lab15 dbs]$ mv orapwdb2rac1 orapwdb2rac1.save
[oracle@rh4lab15 dbs]$ ln -s /dev/raw/raw3 orapwdb2rac1

================= NODE 2
[oracle@rh4lab16 ~]$ cd $ORACLE_HOME/dbs
[oracle@rh4lab16 dbs]$ mv orapwdb2rac2 orapwdb2rac2.save
[oracle@rh4lab16 dbs]$ ln -s /dev/raw/raw3 orapwdb2rac2

A few quick notes:

Make sure the raw device has the proper permissions. At a minimum these should match the permissions assigned by the orapwd utility: 640, owned by the oracle user and the oinstall group. For details about how to set this up see my previous post about udev.
The initialization parameter REMOTE_LOGIN_PASSWORD tells Oracle if the file is being shared among multiple databases. This parameter must still be set to EXCLUSIVE (not SHARED) – we are using the file in multiple instances but a single database.

That’s it. It’s a quick and easy way to ease oracle password file management in RAC.

About Jeremy

Building and running reliable data platforms that scale and perform. about.me/jeremy_schneider

View all posts by Jeremy »

Discussion

2 thoughts on “Single Oracle Password File on Raw with RAC”

Would it be possible to also just create a small NFS export which is shared across all nodes in the cluster to store your shared password file and then create a symbolic link in the correct location on each node?

I’m just curious if you’ve ever configured it that way? I’m thinking NFS might be an easy way for someone who’s uncomfortable with RAW devices to achieve this without a cluster filesystem like OCFS. But then again, it’s probably not certified by Oracle to store the password file on NFS.

By the way, I’m guessing you’re not coming into the office today ;)

LikeLike

Posted by Padraig | March 28, 2007, 1:00 pm

Good question… now that you mention it I’m kinda wondering about that myself. Let me try it out.

[oracle@rh4lab15 dbs]$ cd $ORACLE_HOME/dbs
[oracle@rh4lab15 dbs]$ egrep '(oracle|backup)' /etc/fstab
//10.9.8.1/backups      /mnt/backups            cifs    guest,file_mode=0755,dir_mode=0755,uid=oracle,gid=dba  0 0
rh4lab13:/u01/oracle    /u20/oracle             nfs     rw      0 0
[oracle@rh4lab15 dbs]$ orapwd file=newfile password=jeremy
[oracle@rh4lab15 dbs]$ cp newfile /u20/oracle/orapwdb2rac1
[oracle@rh4lab15 dbs]$ cp newfile /mnt/backups/orapwdb2rac1
[oracle@rh4lab15 dbs]$ grep SYS newfile
Binary file newfile matches
[oracle@rh4lab15 dbs]$ rm orapwdb2rac11
[oracle@rh4lab15 dbs]$ ln -s /u20/oracle/orapwdb2rac1 orapwdb2rac11
[oracle@rh4lab15 dbs]$ grep JEREMY /u20/oracle/orapwdb2rac1

While I'm at it I think I'll try a CIFS mount too, just for kicks. As you can see I've got both of them setup on my machine already. (It's the same one I did the original post on.) I created a new empty pwfile and started by copying it to the NFS mount and creating a symlink. Now lets see if Oracle can read and write to it.


SQL> connect sys/jeremy@db2rac1 as sysdba
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

SQL> grant sysdba to jeremy;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
JEREMY                         TRUE  FALSE

No problem - NFS seems to work fine. (And without any special mount options like datafiles and clusterware files require!) Let's quickly double-check from the OS that the file was changed...

[oracle@rh4lab15 dbs]$ grep JEREMY /u20/oracle/orapwdb2rac1
Binary file /u20/oracle/orapwdb2rac1 matches

Yep - looks good. How about CIFS?

[oracle@rh4lab15 dbs]$ rm orapwdb2rac11
[oracle@rh4lab15 dbs]$ ln -s /mnt/backups/orapwdb2rac1 orapwdb2rac11
[oracle@rh4lab15 dbs]$ grep BACKUPUSER newfile

Ok lets see if we can read and write this file.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

SQL> grant sysdba to backupuser;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
BACKUPUSER                     TRUE  FALSE

Let's double check from the OS again...

[oracle@rh4lab15 dbs]$ grep BACKUPUSER /mnt/backups/orapwdb2rac1
Binary file /mnt/backups/orapwdb2rac1 matches

And let's triple-check from SQLPlus...

SQL> connect backupuser@db2rac1 as sysdba
Enter password: ******
Connected.

Looks like NFS and CIFS both work. (At least with 10.2.0 which is what I had on this server.)

But you're already setting up raw devices for 2 copies of your OCR, 3 copies of your voting disk, and an SPFILE for ASM - what's one or two more for the pwfiles? Of course if you're using a NFS with a Filer then you'd put everything there but other than that it seems to me that it's a simpler and better configuration if all your shared files use raw... but hey that's just my take on it. :)

LikeLike

Posted by Jeremy | March 30, 2007, 8:54 am