RollForward Standby Database using rman service
3 min readOverview:As we are already aware that the rolling forward an physical standby database via an incremental SCN based backup method is one of the easiest way of getting your standby database with lag into sync with the primary database.The lag can be due to missing archives on the primary which haven’t been shipped or applied on the standby.In 12c, this procedure has been dramatically simplified. You can use the RECOVER … FROM SERVICE command to synchronize the physical standby database with primary database. This command does the following: Creates an incremental backup containing the changes to the primary database. All changes to data files on the primary database, beginning with the SCN in the standby data file header, are included in the incremental backup. Transfers the incremental backup over the network to the physical standby database. Applies the incremental backup to the physical standby database. Let’s move on in rolling forward a physical standby database. |
Step 1 : Check the primary database archive log sequence.
Primary:
select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;select thread#,max(sequence#) from v$archived_log group by thread#;
Step 2: Check the standby database archive log sequence.
Standby Archive log Sequence:
select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
Step 3: Perform an tnsping for the primary database service from standby database server.
Primary :
. oraenv
<Primary_Instance_name>
sqlplus / as sysdba
show parameter service (or) SELECT SERVICE_ID,NAME FROM V$ACTIVE_SERVICES; (or) SELECT CON_ID,SERVICE_ID,NAME FROM CDB_SERVICES;
Standby :
tnsping <Primary database service >
Step 4: Stop the MRP in standby database.
Standby :
Check the mrp is running in real time apply or non-real time.
set pages 999 lines 1000
col DEST_NAME for a20
select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
Stop the MRP:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 5: Recover standby using service of primary database.
Roll forward via rman using service name
. oraenv
<Standby_Instance_Name>
env | grep ORA /* Make sure the environment has been set correctly */
sqlplus / as sysdba
shutdown immediate;
exit
sqlplus / as sysdba
startup mount
exit
rman target /
RECOVER DATABASE FROM SERVICE <Primary database service> NOREDO USING COMPRESSED BACKUPSET;
Step 6: Take a backup of standby database datafile location before restoring the standby control file.
Standby database datafile location:
. oraenv
<Standby_Instance_Name>
env | grep ORA /* Make sure the environment has been set correctly */
sqlplus / as sysdba
shutdown immediate;
exit
sqlplus / as sysdba
startup mount;
SET LINES 300 PAGESIZE 0
spool catalog_datafilecopy.sql
SELECT 'CATALOG DATAFILECOPY '''||name||''';' from v$datafile;
spool off;
exit
Step 7: Refresh the standby control file.
. oraenv
<Standby_Instance_Name>
env | grep ORA /* Make sure the environment has been set correctly */
sqlplus / as sysdba
shutdown immediate;
exit;
startup nomount;
exit
env | grep ORA /* Make sure the environment has been set correctly */
rman target /
RESTORE STANDBY CONTROLFILE FROM SERVICE <Primary database service>;
Step 8: Catalog the standby datafiles location.
. oraenv
<Standby_Instance_Name>
env | grep ORA /* Make sure the environment has been set correctly */
sqlplus / as sysdba
startup mount;
exit
env | grep ORA /* Make sure the environment has been set correctly */
rman target /
@catalog_datafilecopy.sql
exit
Step 9: Commit the changes to the controlfile.
env | grep ORA /* Make sure the environment has been set correctly */
rman target /
switch database to copy;
exit
Step 10 : Clear the online & standby redo log group in standby database.
. oraenv
<Standby_Instance_Name>
env | grep ORA /* Make sure the environment has been set correctly */
sqlplus / as sysdba
select group# from v$log;
select 'alter database clear logfile group '||group#||';' from v$log;
select group# from v$standby_log;
select 'alter database clear logfile group '||group#||';' from v$standby_log;
exit;
Step 11: Start the MRP based on the output from above mentioned step 4 & validate the standby database.
. oraenv
<Standby_Instance_Name>
env | grep ORA /* Make sure the environment has been set correctly */
sqlplus / as sysdba
alter database open readonly; /* Based on the step 2 open_mode output*/
select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;
Starting mrp in real time :
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Starting mrp in non-realtime:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Check the status of mrp:
set pages 999 lines 300
select PROCESS,STATUS, GROUP#, THREAD#,SEQUENCE# , BLOCK#, BLOCKS from gv$managed_standby where PROCESS like 'MRP%';
