Before starting Redo Apply services, the physical standby database must be in the MOUNT status. From 11g onwards, the standby database can also be in the OPEN mode. If the redo transport service is in the ARCH mode, the redo will be applied from the archived redo logfiles after being transferred to the standby database. If the redo transport service is in LGWR, the Log network server (LNS) will be reading the redo buffer in SGA and will send redo to Oracle Net Services for transmission to the standby redo logfiles of the standby database using the RFS process. On the standby database, redo will be applied from the standby redo logs.
Redo apply can be specified either as a foreground session or as a background process; it can also be started with real-time apply.
- Start Redo Apply in the foreground.
Connect to the SQLPlus command prompt and issue the following command. If the media recovery is already running, you will run into the error ORA-01153: an incompatible media recovery is active.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; Database altered.
Whenever you issue the preceding command, you can monitor the Redo Apply status from the alert logfile. Managed standby recovery is now active and is not using real-time apply. The SQL session will be active unless you terminate the session by pressing Ctrl + C or kill the session from another active session. Press Ctrl + C to stop Redo Apply.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE * ERROR at line 1: ORA-16043: Redo apply has been canceled. ORA-01013: user requested cancel of current operation
Tip
After starting media recovery, you may see errors such as the following, which are expected. This is in fact an enhancement to the Data Guard technology introduced in 10gR2 to improve speed of switchover/failover. In previous versions, role transition would require us to clear the online redo logfiles before it can become a primary database. Now, the database attempts to clear the ORLs when starting Redo Apply. If the files exist, they will be cleared; if they do not exist, it reports one of the following errors. It attempts to create the online redo logfiles before starting recovery. Even if this is not possible because of different structure or
log_file_name_convertis not set, Redo Apply does not fail. - Start Redo Apply in the background.
In order to start the Redo Apply service in the background, use the
disconnect from sessionoption. This command will return you to the SQL command line once the Redo Apply service is started. Run the following statement on the standby database:SQL> alter database recover managed standby database disconnect from session; Database altered.
- Check the Redo Apply service status.
From SQL*Plus, you can check whether the Media Recover Process (MRP) is running using the
V$MANAGED_STANDBYview:SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCKS FROM V$MANAGED_STANDBY; THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCKS ---------- ---------- --------- -------- ------------ ---------- 1 146 ARCH ARCH CLOSING 1868 1 148 ARCH ARCH CLOSING 6 0 0 ARCH ARCH CONNECTED 0 1 147 ARCH ARCH CLOSING 8 1 149 RFS LGWR IDLE 1 0 0 RFS UNKNOWN IDLE 0 0 0 RFS UNKNOWN IDLE 0 0 0 RFS N/A IDLE 0 1 149 MRP0 N/A APPLYING_LOG 204800 9 rows selected.
From the
PROCESScolumn, you can see that the background process name isMRP0; Media Recovery Process isACTIVEand the status isAPPLYING_LOG, which means that the process is actively applying the archived redo log to the standby database. From the OS, you can monitor the specific background process as follows:[oracle@oracle-stby ~]$ ps -ef|grep mrp oracle 5507 1 0 19:26 ? 00:00:02 ora_mrp0_INDIA
From the output, you can simply estimate how many standby instances are running with background recovery. Only one Media Recovery Process can be running per instance.
Also, you can query from
v$session.SQL> select program from v$session where program like '%MRP%'; PROGRAM ------------------------- oracle@oracle-stby (MRP0)
- Stop Redo Apply.
To stop the MRP, issue the following command:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Da tabase altered.
From the alert logfile, you will see the following lines:
Sun Aug 05 21:24:16 2012 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Sun Aug 05 21:24:16 2012 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /u02/app/oracle/diag/rdbms/india_un/INDIA/trace/INDIA_mrp0_5507.trc: ORA-16037: user requested cancel of managed recovery operation Managed Standby Recovery not using Real Time Apply Recovery interrupted!
After stopping the MRP, no background process is active and this can be confirmed by using the
V$MANAGED_STANDBYorV$SESSIONview shown as follows:SQL> SELECT THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCKS FROM V$MANAGED_STANDBY; THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCKS ---------- ---------- --------- -------- ------------ ---------- 1 146 ARCH ARCH CLOSING 1868 1 148 ARCH ARCH CLOSING 6 0 0 ARCH ARCH CONNECTED 0 1 147 ARCH ARCH CLOSING 8 1 149 RFS LGWR WRITING 1 0 0 RFS UNKNOWN IDLE 0 0 0 RFS UNKNOWN IDLE 0 0 0 RFS N/A IDLE 0 8 rows selected. SQL> select program from v$session where program like '%MRP%'; no rows selected
- Start real-time apply.
To start Redo Apply in real-time apply mode, you must use the
USING CURRENT LOGFILEoption as follows:SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
From the standby alert logfile, you will see the following lines:
Sun Aug 05 15:31:21 2012 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION Attempt to start background Managed Standby Recovery process (INDIA) Sun Aug 05 15:31:21 2012
We've seen how to start, stop, and monitor the Redo Apply service on the physical standby database. Also, the method to start Redo Apply in the real-time mode is covered. These are important tasks of an Oracle database administrator managing a Data Guard environment.