Learn Oracle Database Administration: Real Time Log Apply on Standby Database

Sunday, October 9, 2016

Real Time Log Apply on Standby Database

Configure Real Time Log Apply on Standby

By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.

Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 4 10:57:52 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO IDLE

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO MANAGED

-- See that Recovery Mode will be just Managed.

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------               ----------       ----------     ---          ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL   NO MANAGED REAL TIME APPLY

-- We can also check this in alertlog_File.log

Completed: ALTER DATABASE RECOVER managed standby database cancel
Tue Oct 04 11:00:47 2016
.
.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...

Reference Oracle Docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1034632

Similar Posts :