In 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.
Demo: In this demo I will show you 10 simple steps to configure REDO_TRANSPORT_USER and synch Primary and Standby.
Assume that "prdbexample01" is primary server host and "drdbexample02" is DR server host.
1. Create REDO_TRANSPORT_USER database user in your Primary Database ( here your host will be prdbexample01)
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ sqlplus / as sysdba
create user REDO_TRANSPORT_USER identified by xxxxx default tablespace system temporary tablespace temp;
grant create session to REDO_TRANSPORT_USER;
grant sysoper,sysdba to REDO_TRANSPORT_USER;
-- Verify the user account
set linesize 80
column username format a20
column sysdba format a10
column sysopen format a10
column sysasm format a10
select * from v$pwfile_users;
--Output
USERNAME SYSDBA SYSOPER SYSASM
-------------------- ---------- -------------------- ----------
SYS TRUE TRUE FALSE
REDO_TRANSPORT_USER TRUE TRUE FALSE
Note That, If you have existing setup, then take backup of password files in all hosts something like below:
Backup password files on both Primary and standby servers:
$ cd $ORACLE_HOME/dbs
$ cp -p orapwtestprod orapwtestprod_bkp
2. Stop redo shipping from Primary to Standby/DR server
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ dgmgrl
DGMGRL> connect /
DGMGRL> show configuration
DGMGRL> show database testprod
DGMGRL> edit database testprod set state = TRANSPORT-OFF;
DGMGRL> show database testprod
3. Copy password file to DR/Standby database server
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ cd $ORACLE_HOME/dbs
$ scp orapwtestprod oracle@drdbexample02:$ORACLE_HOME/dbs
--login as oracle to drdbexample02 server (standy/DR server)
$ export ORACLE_SID=testdr
$ cd $ORACLE_HOME/dbs
$ mv orapwtestprod orapwtestdr
4. Verify password file in Standby/DR database
--login as oracle to drdbexample02 server (standy/DR server)
$ export ORACLE_SID=testdr
$ sqlplus / as sysdba
set linesize 80
column username format a20
column sysdba format a10
column sysopen format a10
column sysasm format a10
select * from v$pwfile_users;
--Output
USERNAME SYSDBA SYSOPER SYSASM
-------------------- ---------- -------------------- ----------
SYS TRUE TRUE FALSE
REDO_TRANSPORT_USER TRUE TRUE FALSE
5. Update REDO_TRANSPORT_USER setting in the parameter file on Primary
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ sqlplus / as sysdba
SQL> alter system set redo_transport_user = REDO_TRANSPORT_USER scope=both;
SQL> show parameter redo_transport_user
6. Update REDO_TRANSPORT_USER setting in the parameter file on Standby
--login as oracle to drdbexample02 server (standy/DR server)
$ export ORACLE_SID=testdr
$ sqlplus / as sysdba
SQL> alter system set redo_transport_user = REDO_TRANSPORT_USER scope=both;
SQL> show parameter redo_transport_user
7. Resume redo shipping from Primary to Standby/DR server
--login as oracle to prdbexample01 server (primary server)
$ dgmgrl
DGMGRL> connect /
DGMGRL> show configuration
DGMGRL> show database testprod
DGMGRL> edit database testprod set state = TRANSPORT-ON;
DGMGRL> show database testprod
8. Verify Standby/DR database is in sync with Production database
--login as oracle to drdbexample02 server (standy/DR server)
$ sqlplus / as sysdba
SQL> select NVL(value, 'N/A ') transport_lag
from v$dataguard_stats
where name = 'transport lag';
SQL> select NVL(value, 'N/A') apply_lag
from v$dataguard_stats
where name = 'apply lag';
Note: Now you can change SYS password in the Primary database and it will not impact any redo transport or DR synch.
9.Disable and then re-enable Data Guard Broker Configuration
--login as oracle to prdbexample01 server (primary server)
DGMGRL> connect /
DGMGRL> show configuration
DGMGRL> disable configuration
DGMGRL> enable configuration
DGMGRL> show database testprod
DGMGRL> show database testdr
Verify that there are no errors when running "show database" commands above
10. Verify Standby/DR database is still in sync with Production database
--login as oracle to drdbexample02 server (standby/DR server)
$ sqlplus / as sysdba
select NVL(value, 'N/A ') transport_lag
from v$dataguard_stats
where name = 'transport lag';
select NVL(value, 'N/A') apply_lag
from v$dataguard_stats
where name = 'apply lag';
OR
-- You can run below queries also:
col VALUE for a20;
select * from v$dataguard_stats;
-- find gaps : Query to verify lagging
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
-- To check real time apply
set linesize 150
set pagesize 100
set echo on
select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents
from gv$managed_standby
where PROCESS like 'MRP%' or client_process like 'LGWR';
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.
Demo: In this demo I will show you 10 simple steps to configure REDO_TRANSPORT_USER and synch Primary and Standby.
Assume that "prdbexample01" is primary server host and "drdbexample02" is DR server host.
1. Create REDO_TRANSPORT_USER database user in your Primary Database ( here your host will be prdbexample01)
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ sqlplus / as sysdba
create user REDO_TRANSPORT_USER identified by xxxxx default tablespace system temporary tablespace temp;
grant create session to REDO_TRANSPORT_USER;
grant sysoper,sysdba to REDO_TRANSPORT_USER;
-- Verify the user account
set linesize 80
column username format a20
column sysdba format a10
column sysopen format a10
column sysasm format a10
select * from v$pwfile_users;
--Output
USERNAME SYSDBA SYSOPER SYSASM
-------------------- ---------- -------------------- ----------
SYS TRUE TRUE FALSE
REDO_TRANSPORT_USER TRUE TRUE FALSE
Note That, If you have existing setup, then take backup of password files in all hosts something like below:
Backup password files on both Primary and standby servers:
$ cd $ORACLE_HOME/dbs
$ cp -p orapwtestprod orapwtestprod_bkp
2. Stop redo shipping from Primary to Standby/DR server
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ dgmgrl
DGMGRL> connect /
DGMGRL> show configuration
DGMGRL> show database testprod
DGMGRL> edit database testprod set state = TRANSPORT-OFF;
DGMGRL> show database testprod
3. Copy password file to DR/Standby database server
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ cd $ORACLE_HOME/dbs
$ scp orapwtestprod oracle@drdbexample02:$ORACLE_HOME/dbs
--login as oracle to drdbexample02 server (standy/DR server)
$ export ORACLE_SID=testdr
$ cd $ORACLE_HOME/dbs
$ mv orapwtestprod orapwtestdr
4. Verify password file in Standby/DR database
--login as oracle to drdbexample02 server (standy/DR server)
$ export ORACLE_SID=testdr
$ sqlplus / as sysdba
set linesize 80
column username format a20
column sysdba format a10
column sysopen format a10
column sysasm format a10
select * from v$pwfile_users;
--Output
USERNAME SYSDBA SYSOPER SYSASM
-------------------- ---------- -------------------- ----------
SYS TRUE TRUE FALSE
REDO_TRANSPORT_USER TRUE TRUE FALSE
5. Update REDO_TRANSPORT_USER setting in the parameter file on Primary
--login as oracle to prdbexample01 server (primary server)
$ export ORACLE_SID=testprod
$ sqlplus / as sysdba
SQL> alter system set redo_transport_user = REDO_TRANSPORT_USER scope=both;
SQL> show parameter redo_transport_user
6. Update REDO_TRANSPORT_USER setting in the parameter file on Standby
--login as oracle to drdbexample02 server (standy/DR server)
$ export ORACLE_SID=testdr
$ sqlplus / as sysdba
SQL> alter system set redo_transport_user = REDO_TRANSPORT_USER scope=both;
SQL> show parameter redo_transport_user
7. Resume redo shipping from Primary to Standby/DR server
--login as oracle to prdbexample01 server (primary server)
$ dgmgrl
DGMGRL> connect /
DGMGRL> show configuration
DGMGRL> show database testprod
DGMGRL> edit database testprod set state = TRANSPORT-ON;
DGMGRL> show database testprod
8. Verify Standby/DR database is in sync with Production database
--login as oracle to drdbexample02 server (standy/DR server)
$ sqlplus / as sysdba
SQL> select NVL(value, 'N/A ') transport_lag
from v$dataguard_stats
where name = 'transport lag';
SQL> select NVL(value, 'N/A') apply_lag
from v$dataguard_stats
where name = 'apply lag';
Note: Now you can change SYS password in the Primary database and it will not impact any redo transport or DR synch.
9.Disable and then re-enable Data Guard Broker Configuration
--login as oracle to prdbexample01 server (primary server)
DGMGRL> connect /
DGMGRL> show configuration
DGMGRL> disable configuration
DGMGRL> enable configuration
DGMGRL> show database testprod
DGMGRL> show database testdr
Verify that there are no errors when running "show database" commands above
10. Verify Standby/DR database is still in sync with Production database
--login as oracle to drdbexample02 server (standby/DR server)
$ sqlplus / as sysdba
select NVL(value, 'N/A ') transport_lag
from v$dataguard_stats
where name = 'transport lag';
select NVL(value, 'N/A') apply_lag
from v$dataguard_stats
where name = 'apply lag';
OR
-- You can run below queries also:
col VALUE for a20;
select * from v$dataguard_stats;
-- find gaps : Query to verify lagging
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
-- To check real time apply
set linesize 150
set pagesize 100
set echo on
select inst_id,process,status,client_process,THREAD#,sequence#,block#,active_agents,known_agents
from gv$managed_standby
where PROCESS like 'MRP%' or client_process like 'LGWR';
No comments:
Post a Comment