Oracle Data Guard 11g - Redo Transport User
Hi,
As you know when we create Data Guard Configuration, we must copy password file. Because SYS user is default redo transport user on Data Guard configurations.
SYS password is changed on primary
When we change SYS password on primary database, redo transport service is stopping and we are getting error: ORA-01031: insufficient privileges. When redo transport failed for this reason we can recreate standby password file with copy from primary side. Same time we can change redo transport user, by setting REDO_TRANSPORT_USER with SYSOPER privileged user. I demonstrated in two case, when SYS password changing on primary database.
Password files.
Traditionally we are coping password file from primary with operating system command (cp, copy). After Oracle Database 11g version RMAN can duplicate password file with DUPLICATE command, so when we using RMAN DUPLICATE command with FOR STANDBY FROM ACTIVE DATABASE option, we can use PASSWORD FILE option for copy primary database password file to standby database.
Documentation says :
PASSWORD FILE
Specifies that RMAN should use the password file on the source database to overwrite the password file currently used by the auxiliary instance. This option is only valid when FROM ACTIVE DATABASE is specified; otherwise, RMAN signals an error.If FOR STANDBY is specified, then RMAN copies the password file by default; if not specified, then RMAN does not copy the password file by default. You can use PASSWORD FILE to request that RMAN overwrite the existing password file with the password file from the source database. If you want the duplicate database to contain all the passwords available on your production database, then use the PASSWORD FILE option.
Demonstrations.
1. In this case transport service running on Primary database, Standby database opened Read Only With Apply (Active Data Guard is running). REDO_TRANSPORT_USER parameter is not set on both side.
On primary side:
Alter log of primary database:
On standby side :
Alert log of standby database:
Alert log of primary database:
Yes, we are getting error when changed SYS password. I coping primary password for standby with Linux copy command (cp)
Trying again.
Alert log of primary database:
On Standby:
Alert log of standby database:
Transport continue normally after copy password file of primary database to standby side.
2. In this case I create a user and granting SYSOPER and setting REDO_TRANSPORT_USER to this user.
On primary side :
Alert log of primary database:
On Standby side :
Now we can check, transport process.
On primary:
Redo transport service using RTU user for transport redo from primary to standby database. I changing
SYS user password on primary again.
On primary :
It means transport is not stopped. If we change RTU user’s password then Redo Transport will stop, Because RTU user is privileged SYSOPER and this password change must be on password file. It means, if we change RTU user’s password we must copy password file from primary to standby side, again.
Conclusion
Password files must be same for Data Guard Configuration databases. In a Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database, and that copy must be refreshed whenever the SYSOPER or SYSDBA privilege is granted or revoked, and after the password of any user with these privileges is changed.
Regards
Mahir M. Quluzade
As you know when we create Data Guard Configuration, we must copy password file. Because SYS user is default redo transport user on Data Guard configurations.
SYS password is changed on primary
When we change SYS password on primary database, redo transport service is stopping and we are getting error: ORA-01031: insufficient privileges. When redo transport failed for this reason we can recreate standby password file with copy from primary side. Same time we can change redo transport user, by setting REDO_TRANSPORT_USER with SYSOPER privileged user. I demonstrated in two case, when SYS password changing on primary database.
Password files.
Traditionally we are coping password file from primary with operating system command (cp, copy). After Oracle Database 11g version RMAN can duplicate password file with DUPLICATE command, so when we using RMAN DUPLICATE command with FOR STANDBY FROM ACTIVE DATABASE option, we can use PASSWORD FILE option for copy primary database password file to standby database.
Documentation says :
PASSWORD FILE
Specifies that RMAN should use the password file on the source database to overwrite the password file currently used by the auxiliary instance. This option is only valid when FROM ACTIVE DATABASE is specified; otherwise, RMAN signals an error.If FOR STANDBY is specified, then RMAN copies the password file by default; if not specified, then RMAN does not copy the password file by default. You can use PASSWORD FILE to request that RMAN overwrite the existing password file with the password file from the source database. If you want the duplicate database to contain all the passwords available on your production database, then use the PASSWORD FILE option.
Demonstrations.
1. In this case transport service running on Primary database, Standby database opened Read Only With Apply (Active Data Guard is running). REDO_TRANSPORT_USER parameter is not set on both side.
SQL> show parameter redo_transport_user NAME TYPE VALUE ------------------- ------------- ----------------- redo_transport_user string
On primary side:
SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 307 SQL> select process, status from v$managed_standby; PROCESS STATUS --------- ------------ ARCH CLOSING ARCH CLOSING ARCH CONNECTED ARCH CLOSING LNS WRITING
Alter log of primary database:
****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Wed May 01 12:06:51 2013 Archived Log entry 611 added for thread 1 sequence 307 ID 0xf23a6e3f dest 1: LNS: Standby redo logfile selected for thread 1 sequence 308 for destination LOG_ARCHIVE_DEST_2
On standby side :
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 307
Alert log of standby database:
Media Recovery Waiting for thread 1 sequence 308 Wed May 01 12:06:51 2013 Archived Log entry 308 added for thread 1 sequence 307 ID 0xf23a6e3f dest 1: Wed May 01 12:06:51 2013Primary database is in MAXIMUM PERFORMANCE mode ASYNC transport going normally. Now I'm changing SYS password of primary.
SQL> alter user SYS identified by SYSPass1; User altered. SQL> alter system switch logfile; System altered. SQL> select process, status from v$managed_standby; PROCESS STATUS --------- ------------ ARCH OPENING ARCH CLOSING ARCH CONNECTED ARCH CLOSING LNS OPENING
Alert log of primary database:
****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Error 1031 received logging on to the standby Error 1031 for archive log file 3 to 'stldb' Wed May 01 12:15:15 2013 Archived Log entry 613 added for thread 1 sequence 308 ID 0xf23a6e3f dest 1: Errors in file /u01/app/oracle/diag/rdbms/admdb/admdb/trace/admdb_nsa2_2698.trc: ORA-01031: insufficient privileges Wed May 01 12:16:01 2013 Error 1031 received logging on to the standby PING[ARC2]: Heartbeat failed to connect to standby 'stldb'. Error is 1031.
Yes, we are getting error when changed SYS password. I coping primary password for standby with Linux copy command (cp)
[oracle@oel62-x64 dbs]$ cp orapwadmdb orapwstldb
Trying again.
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 310
Alert log of primary database:
****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Wed May 01 12:19:06 2013 Archived Log entry 614 added for thread 1 sequence 309 ID 0xf23a6e3f dest 1: LNS: Standby redo logfile selected for thread 1 sequence 310 for destination LOG_ARCHIVE_DEST_2 Wed May 01 12:19:07 2013 ARC3: Standby redo logfile selected for thread 1 sequence 309 for destination LOG_ARCHIVE_DEST_2 Thread 1 cannot allocate new log, sequence 311 Checkpoint not complete Current log# 1 seq# 310 mem# 0: /u01/app/oracle/oradata/admdb/redo01.log Thread 1 advanced to log sequence 311 (LGWR switch) Current log# 2 seq# 311 mem# 0: /u01/app/oracle/oradata/admdb/redo02.log
On Standby:
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 310
Alert log of standby database:
RFS[18]: Assigned to RFS process 5039 RFS[18]: Selected log 4 for thread 1 sequence 308 dbid -231090113 branch 813174214 Recovery of Online Redo Log: Thread 1 Group 4 Seq 308 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/stldb/sredo01.log Wed May 01 12:15:14 2013 Media Recovery Waiting for thread 1 sequence 309 Wed May 01 12:15:14 2013 Archived Log entry 309 added for thread 1 sequence 308 ID 0xf23a6e3f dest 1: Wed May 01 12:19:05 2013 Primary database is in MAXIMUM PERFORMANCE mode RFS[19]: Assigned to RFS process 5184 RFS[19]: Selected log 4 for thread 1 sequence 310 dbid -231090113 branch 813174214 Wed May 01 12:19:07 2013 RFS[20]: Assigned to RFS process 5186 RFS[20]: Selected log 5 for thread 1 sequence 309 dbid -231090113 branch 813174214 Wed May 01 12:19:08 2013 Recovery of Online Redo Log: Thread 1 Group 5 Seq 309 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/stldb/sredo02.log Wed May 01 12:19:08 2013 Archived Log entry 310 added for thread 1 sequence 309 ID 0xf23a6e3f dest 1: Media Recovery Waiting for thread 1 sequence 310 (in transit) Recovery of Online Redo Log: Thread 1 Group 4 Seq 310 Reading mem 0
Transport continue normally after copy password file of primary database to standby side.
2. In this case I create a user and granting SYSOPER and setting REDO_TRANSPORT_USER to this user.
On primary side :
SQL> create user RTU identified by rtu; User created. SQL> grant SYSOPER to RTU; Grant succeeded. SQL> select * from v$PWFILE_USERS; USERNAME SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS TRUE TRUE FALSE RTU FALSE TRUE FALSE SQL> alter system set REDO_TRANSPORT_USER='RTU'; System altered. SQL> show parameter REDO_TRANSPORT_USER NAME TYPE VALUE ------------------ --------- --------------- redo_transport_user string RTU
Alert log of primary database:
Wed May 01 12:29:10 2013 redo_transport_user changed to RTU
On Standby side :
SQL> select username from all_users 2> where username ='RTU'; USERNAME ------------------------------ RTU SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS TRUE TRUE FALSE SQL> grant SYSOPER to RTU; Grant succeeded. SQL> alter system set REDO_TRANSPORT_USER=RTU; System altered.
Now we can check, transport process.
On primary:
SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 314On standby side :
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 314
Redo transport service using RTU user for transport redo from primary to standby database. I changing
SYS user password on primary again.
SQL> alter user sys identified by SYSPass2; User altered.
On primary :
SQL> alter system switch logfile; System altered. SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 315 On Standby side : SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 315
It means transport is not stopped. If we change RTU user’s password then Redo Transport will stop, Because RTU user is privileged SYSOPER and this password change must be on password file. It means, if we change RTU user’s password we must copy password file from primary to standby side, again.
Conclusion
Password files must be same for Data Guard Configuration databases. In a Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database, and that copy must be refreshed whenever the SYSOPER or SYSDBA privilege is granted or revoked, and after the password of any user with these privileges is changed.
Regards
Mahir M. Quluzade
Comments
Post a Comment