如何在 Data Gaurd 中启用 Real Time Apply

Step by Step Demo DataGuard 的环境和测试：

Source:

	Platform: Linuxx86_64
	Server Name: RAC1.MODB.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.MODB.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

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

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

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.

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

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

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 ('https://modb.pro');

1 row created.

00:05:22 SQL>  COMMIT;

Commit complete.

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

NAME
------------------------------
https://modb.pro

On Standby

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

NAME
------------------------------
https://modb.pro