Configure MRP in Standby
3 min readAdd the Target Database TNS entry to the Source's tnsnames.ora file(s) (all of them if it's a clustered Source)
Set the log_archive_dest_<#> parameter on the Source Database, as Oracle:
export ORACLE_SID=<source/target_sid>
sqlplus '/as sysdba'
show parameter log_archive_dest_
alter system set log_archive_dest_3='SERVICE="<db_unique_name>" LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=<db_unique_name>' scope=both sid='*';
# Use a dest that isn't used, Example Using 3:
alter system set log_archive_dest_3='SERVICE="orcls" LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=orcls' scope=both sid='*';
# To clear the parameter, Example Using 3:
alter system set log_archive_dest_3='' scope=both sid='*';
Then enable the log_archive_dest_state_ parameter on the Source Database, as Oracle:
export ORACLE_SID=<source_sid>
sqlplus '/as sysdba'
# Enable it, Example Using 3:
alter system set log_archive_dest_state_3='ENABLE' scope=both sid='*';
# To disable shipping (Only needs to be done in Source):
alter system set log_archive_dest_state_3='DEFER' scope=both sid='*';
Set fal/standby_file_management parameters on the Target Database, as Oracle:
export ORACLE_SID=<target_sid>
sqlplus '/as sysdba'
alter system set standby_file_management=AUTO scope=both sid='*';
# Example for fal, orcls is Target, and orcl Source (Using the db_unique_name):
alter system set fal_client='orcls' scope=both sid='*';
alter system set fal_server='orcl' scope=both sid='*';
Using the db_unique_name, set log_archive_config parameter on BOTH Target/Source Databases, as Oracle:
sqlplus '/as sysdba'
show parameter log_archive_config
# Example of current config
log_archive_config='dg_config=(orcl,orcls)'
# Example of adding a NEW standby to the above parameter (orcls is the new standby):
alter system set log_archive_config='dg_config=(orcl,orcls)' scope=both sid='*';
Start the Recovery on the Target Database, as Oracle:
To Stop the Recovery on the Target Database:
alter database recover managed standby database cancel;
export ORACLE_SID=<target_sid>
sqlplus '/as sysdba'
# If standby database needs to be in real-time apply
startup nomount;
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect;
exit
# If standby database does not need to be in real-time apply
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect;
exit
# If standby database needs to be active dataguard
startup mount;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
exit
Follow the alert log for the Target AND Source Databases to make sure nothing is throwing errors
If all looks good you can then check the time Target is behind Source, on the Target Database, as Oracle:
export ORACLE_SID=<target_sid>
sqlplus '/as sysdba'
select 'Standby thread '|| thread# || ' is '|| round((sysdate - max(first_time)) * 24,1) || ' hours behind Production.' from v$log_history group by thread#;
select name,value,time_computed from v$dataguard_stats;
# Example Output:
'STANDBYTHREAD'||THREAD#||'IS'||ROUND((SYSDATE-MAX(FIRST_TIME))*24,1)||'HOURSBEHINDPRODUCTION.'
----------------------------------------------------------------------------------------------------------------------------
Standby thread 1 is 0 hours behind Production.
Standby thread 2 is .1 hours behind Production.
Name Value TIME_COMPUTED
------------------------- -------------------- ------------------------------
transport lag +00 00:00:00 12/1/2021 02:12:44
apply lag +00 00:00:00 12/1/2021 02:12:44
apply finish time 12/1/2021 02:12:44
estimated startup time 23 12/1/2021 02:12:44
