Friday, October 1, 2021

Change SYS password in Oracle Data Guard

In Oracle Data Guard configuration, redo transport uses Oracle Net sessions to transport redo data. These redo transport sessions are authenticated using either the Secure Socket Layer (SSL) protocol or a remote login password file. Most time, remote login password file is adopted because of the difficulty of meeting SSL authentication requirements.

When Redo Transport Authentication uses a password file, all physical and snapshot standby databases must use a copy of the password file from the primary database. Technically, the password file has to be copied every time when it is changed on primary database. 

Note: the password file should not be manually changed on physical and snapshot standby databases.

The password file will be updated(changed) whenever an administrative privilege (SYSDBA, SYSOPER, SYSDG and so on) is granted or revoked, and after the password of any user with administrative privileges is changed. Therefore, changing SYS password will update password file and should follow the following steps,

1. Change SYS password on primary database using one of following commands

   * SQL command:  alter user SYS identified by <new password>
   * SQLPlus command:  password SYS

Note: Do not use command orapwd to recreate password file unless the file is corrupted or you want to change file format.

2. Find out the password file location on primary database

If database version is 12c or higher and Grid Infrastructure (standalone or cluster) is configured, run command

   srvctl config database -db <db_unique_name>

Sample output
$ srvctl config database -db DBPRIMAY
Database unique name: DBPRIMAY
Database name: DBPRIMAY
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/DBPRIMAY/PARAMETERFILE/spfile.437.1064771063
Password file: +DATA/DBPRIMAY/PASSWORD/pwdDBPRIMAY.367.1084460285
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA1,REDO1,REDO2
Services: CNY1,HRVSTR13
OSDBA group:
OSOPER group:
Database instance: DBPRIMAY

The sample command output shows the password file of database DBPRIMARY is "+DATA/DBPRIMAY/PASSWORD/pwdDBPRIMAY.461.1084460285" which is stored on ASM.

If srvctl command does not show password file, Oracle should use default password file which is "$ORACLE_HOME/dbs/orapw<ORACLE_SID>".

3. Copy the password file from primary database to all physical and snapshot standby databases

Note: It does not need to copy password file if database version is 12.2 or higher, Oracle will automatically refresh password files on standby databases.

If the password file is on ASM disk group, the ASM command "pwcopy" has to be used to copy password file from ASM to OS. Then use OS network copy utility to copy file to standby database hosts. Command "scp -p" is recommended.

If the password file is not copied correctly, you may get following errors,

  ORA-46952: standby database format mismatch for password file '+DATA/DBSTBY/PASSWORD/pwdSTBY.251.3084560285'

It can be fixed by re-copying the password file.

No comments: