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
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
*/
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>
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.