Oracle dataguard real time apply and non-real time apply

Oracle dataguard real time apply and non-real time apply

Real-time apply, which allows Data Guard to recover redo data from the current standby redo log file as it is being filled up by the RFS process.(Standby redo logs must for it)

How to enable real time apply?

Physical Standby:

SQL> alter database recover managed standby database cancel;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

For Logical Standby:

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE

How to check real-time apply is enabled or not?

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_2             VALID     LOCAL          YES  MANAGED REAL TIME APPLY

(or)

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
DELL      READ ONLY WITH APPLY PHYSICAL STANDBY DELL



Non Real-time Apply (Managed Recovery), which allows Data Guard to recover redo data from the archived redo log file as it is being filled up from the standby redo log files.


How to enable Non-real time apply?

Physical Standby:

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you face below error:
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Stop the mrp process first:

SQL> alter database recover managed standby database cancel;

Logical Standby:

SQL>ALTER DATABASE START LOGICAL STANDBY APPLY;

How to check real-time apply is enabled or not?

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_2             VALID     LOCAL          YES  MANAGED

(or)

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

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
DELL      READ ONLY            PHYSICAL STANDBY DELL



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 ('Mohd Qader');
1 row created.
00:05:22 SQL>  COMMIT;
Commit complete.

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

NAME
------------------------------
Mohd Qader


On Standby:

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

NAME
------------------------------
Mohd Qader

00:05:21 SQL>