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.