Enable Real Time Apply

How to Enable Real Time Apply

Real-time apply : When real-time apply is enabled, the log apply services can apply redo data as it is received,
                                     without waiting for the current standby redo log file to be archived.

Pre-requisites: Configure Physical Standby database. Click here

0. Enviroment
1. Set up log_archive_dest_2 on Primary
2. Verify Real-Tme Apply Status on Standby
3. Try to enable real-time apply
4. Add standby redo logs on primary and standby
5. Enable Real-Time Apply
6. Quick Testing


0. Enviroment

Source:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Target:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Set up log_archive_dest_2 on the primary with LGWR ASYNC or SYNC

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=w5005prg LGWR ASYNC VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLE) DB_UNIQUE_NAME=w5005p
                                                 rg


2. Verify Real-Tme Apply Status on Standby

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

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

SQL>

Note: If it is not in Real-Time apply mode then SRL (Standby Redo Logs) will not be in use.

SRL=NO
RECOVERY_MODE = Managed : It means Redo-Apply only, Not Real Time Aapply.


3. Try to enable real-time apply

SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

SQL>

/*
standby redo logs are populated with redo information as fast as the primary redo logs, 
rather than waiting for the redo log to be archived and shipped to the standby.  
This results in faster switchover and failover times because the standby redo log files have been applied 
already to the standby database by the time the failover or switchover begins.
Oracle recommends the below formula to calculate the number of Standby redo logs file as 
(maximum number of logfiles for each thread + 1) * maximum number of threads
*/


4. Add standby redo logs

On Primary

SQL> select name, open_mode, database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
W5005PR   READ WRITE           PRIMARY

SQL> col member for a50
SQL> set lines 180
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  +DATA/w5005pr/onlinelog/group_3.263.900456463      NO
         2         ONLINE  +DATA/w5005pr/onlinelog/group_2.266.900456463      NO
         1         ONLINE  +DATA/w5005pr/onlinelog/group_1.267.900456461      NO

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

no rows selected

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

    GROUP# size in MB
---------- ----------
         1         50
         2         50
         3         50

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

no rows selected

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50

SQL> select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP# MEMBER                                                  BYTES
---------- -------------------------------------------------- ----------
         4 +DATA/w5005pr/onlinelog/group_4.259.900718771        52428800
         5 +DATA/w5005pr/onlinelog/group_5.258.900718779        52428800
         6 +DATA/w5005pr/onlinelog/group_6.257.900718785        52428800
         7 +DATA/w5005pr/onlinelog/group_7.256.900718791        52428800

SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED

SQL>

On Standby

SQL> alter database add standby logfile group 4 size 50M;
alter database add standby logfile group 4 size 50M
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 4 size 50M;

Database altered.

SQL> alter database add standby logfile group 5 size 50M;

Database altered.

SQL> alter database add standby logfile group 6 size 50M;

Database altered.

SQL> alter database add standby logfile group 7 size 50M;

Database altered.

SQL>


5. Enable Real-Time Apply on Standby

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

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

Database altered.

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

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

SQL>

On Primary

SQL> SET LINES 180
SQL> col DEST_NAME for a30
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;

   DEST_ID DEST_NAME                      STATUS    TYPE           SRL RECOVERY_MODE
---------- ------------------------------ --------- -------------- --- -----------------------
         2 LOG_ARCHIVE_DEST_2             VALID     PHYSICAL       YES MANAGED REAL TIME APPLY

SQL>


6. Quick Testing

On Primary

SQL> set time on
00:03:15 SQL> create table test (name varchar2(30));

Table created.


00:03:24 SQL> insert into test values ('Rajasekhar Amudala');

1 row created.

00:05:22 SQL>  COMMIT;

Commit complete.

00:05:23 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:24 SQL>

On Standby

SQL> set time on
00:05:30 SQL> select * from test;

NAME
------------------------------
Rajasekhar Amudala

00:05:21 SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.