Roll forward of Data Guard in Oracle

Roll forward of Data Guard in Oracle

In this blog post, we will be discussing the roll forward option of Dataguard in Oracle, which is a critical process in ensuring High Availability and Disaster Recovery for your database. We will be sharing some SQL commands that you can use to check the status of your Dataguard configuration and perform the roll forward process.
Roll forward of Data Guard in Oracle is the process of applying archived redo logs to a standby database to keep it synchronized with the primary database. It is a crucial aspect of maintaining a high availability and disaster recovery solution.

Firstly, we need to ensure that our Dataguard standby database is synchronized with the primary database. You can run the following SQL scripts on your DR standby to check the status of the managed recovery process:

SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

This script will show you the status of the managed recovery process, which should be “APPLYING_LOG” if everything is working correctly.

Next, we need to check the status of our Dataguard configuration using the following SQL scripts:

select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database ;

This script will show you the database role, protection mode, and other critical parameters of your Dataguard configuration.

select database_role,db_unique_name,open_mode,protection_mode from v$database;

This script will show you the database role, database name, open mode, and protection mode.

select THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#;

This script will show you the last applied log sequence number for each thread.

select sequence#,applied from v$archived_log order by sequence#;

This script will show you the archived logs’ sequence numbers and whether they have been applied to the standby database.

select * from v$archive_gap;

This script will show you any archive gaps that need to be resolved.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied" 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;

The SQL script provided above is used to check the last sequence received and applied on the standby database. The V$ARCHIVED_LOG and V$LOG_HISTORY views are used to retrieve the last archived log sequence and last applied log sequence, respectively. This script helps in determining the point at which to start rolling forward the standby database.

Standby database needs to be opened in mount mode, and the DataGuard recovery process should be canceled using the ‘recover managed standby database cancel’ command. This ensures that the standby database is not applying any logs while the roll forward process is in progress.

Once the standby database is in mount mode, the ‘recover database’ command can be used to roll forward the standby database. The ‘noredo’ option is used to specify that no redo logs should be applied during the recovery process. Instead, the archived redo logs will be used to roll forward the database.

Perform the following steps on the DR Standby:

sqlplus / as sysdba

alter database recover managed standby database cancel;

shut immediate;

startup mount;

tnsping <Prod_Service>

tnsping <DR_Service>

Create an execute script to run the roll forward command in the background;

cat rollfwd.sh

recover database from service <Prod_Service> noredo using compressed backupset;

Create an execute script by the name rollfwd_main.sh containing the env file and connecting through rman to execute the roll forward script created earlier

cat rollfwd_main.sh

. ./.db_env

rman target sys/Password@<DR_Service> debug cmdfile=rollfwd.sh log=rollforward.log

Execute the command in the background

nohup ./rollfwd_main.sh &

Monitor the progress

tail -f rollforward.log