Redo Apply Mode in Oracle 12c R2 Dataguard

Redo Apply Mode in Oracle 12c R2 Dataguard:

 Primary Side:

 [oracle@paw-prim ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 6 00:33:16 2017

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size          2924928 bytes

Variable Size        1040191104 bytes

Database Buffers       553648128 bytes

Redo Buffers            13848576 bytes

Database mounted.

Database open.

 SQL> select dest_id,status,error from v$archive_dest where status<>’INACTIVE’;

DEST_ID      STATUS          ERROR

-----------------------------------------------------------------

      1        VALID

      2        VALID

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

   DEST_ID STATUS    DATABASE_MODE        TYPE      RECOVERY_MODE       ERROR

-----------------------------------------------------------------------------------

      1   VALID      OPEN                 LOCAL      IDLE

SQL> select dest_id,status,database_mode,type,recovery_mode,error from v$archive_dest_status where dest_id=2;

DEST_ID    STATUS     DATABASE_MODE        TYPE      RECOVERY_MODE        ERROR

------------------------- ---------------------------------------------------------

      2   VALID     MOUNTED-STANDBY      PHYSICAL   MANAGED

 Standby Side:

 [oracle@paw-stand1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 6 00:33:16 2017

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size          2924928 bytes

Variable Size        1040191104 bytes

Database Buffers       553648128 bytes

Redo Buffers            13848576 bytes

Database mounted.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS            BLOCK#     BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

SQL> select member from v$logfile;

MEMBER

---------------------------------------------------------------------

/u01/app/oracle/oradata/stand1/redo03.log

/u01/app/oracle/oradata/stand1/redo02.log

/u01/app/oracle/oradata/stand1/redo01.log

You can see I have not added Standby logs till of now.

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

NAME   OPEN_MODE            DATABASE_ROLE      PROTECTION_MODE

------ -------------------- ----------------    --------------------

PROD1   READ ONLY            PHYSICAL STANDBY    MAXIMUM PERFORMANCE

 SQL> show parameter db_unique_name

NAME                 TYPE     VALUE

--------------------  --------- ---------- ------------------------------

db_unique_name       string    stb1

SQL> select process,status,block#,blocks from v$managed_standby;

PROCESS   STATUS             BLOCK#    BLOCKS

--------- ------------ ---------- ----------

ARCH   CONNECTED          0       0

ARCH   CONNECTED          0       0

ARCH   CONNECTED          0       0

ARCH   CONNECTED          0       0

RFS    IDLE               0       0

RFS    IDLE               2805     1

RFS    IDLE               0       0

RFS    IDLE               0       0

8 rows selected.

SQL> set line 500

SQL> col value for a25

SQL> select name,value,datum_time,time_computed from v$dataguard_stats;




NAME                     VALUE         DATUM_TIME          TIME_COMPUTED

--------------- ----------------------- -------------------- --------------------

transport lag             +00 00:41:00  03/06/2017 01:46:25  03/06/2017 01:46:39

apply lag                                                    03/06/2017 01:46:39

apply finish time                                            03/06/2017 01:46:39

estimated startup time     12                               03/06/2017 01:46:39

SQL> select dest_id,status,error from v$archive_dest where status<>’INACTIVE’;

DEST_ID  STATUS    ERROR

----------- --------- ---------------------------------------------------------

      1    VALID

     32    VALID

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

DEST_ID STATUS    DATABASE_MODE   TYPE        RECOVERY_MODE        ERROR

---------- --------- --------------- ---------------- ----------------------- -----

      1   VALID     MOUNTED-STANDBY LOCAL       IDLE

SQL> alter database recover managed standby database using archived logfile disconnect;

Database altered.

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

DEST_ID STATUS    DATABASE_MODE   TYPE      RECOVERY_MODE    ERROR

---------- --------- --------------- -------- ------------------- ---------------

      1   VALID     MOUNTED-STANDBY LOCAL      MANAGED

SQL> select dest_id,status,error from v$archive_dest where status<>’INACTIVE’;

DEST_ID      STATUS    ERROR

------------- ----------- ------------------------------------------------------

      1        VALID

     32         VALID

SQL> select name,value,datum_time,time_computed from v$dataguard_stats;

NAME                  VALUE                 DATUM_TIME                TIME_COMPUTED

-------------------------------- ------------------------ ------------------------

transport lag          +00 00:53:00             03/06/2017 01:58:26 03/06/2017 01:59:22

apply lag              +00 00:53:00             03/06/2017 01:58:26 03/06/2017 01:59:22

apply finish time      +00 00:00:00.001                       03/06/2017 01:59:22

estimated startup time  12                                     03/06/2017 01:59:22

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE               9482     1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

Database altered.

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

 DEST_ID STATUS    DATABASE_MODE   TYPE        RECOVERY_MODE        ERROR

---------- --------- --------------- ---------------- ----------------------- -----

      1   VALID     MOUNTED-STANDBY LOCAL       MANAGED

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              10531     1      0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

 SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              10535    1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS        BLOCK#  BLOCKS  DELAY_MINS

--------- ------------ ---------- -------- ------------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              10538     1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

 SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS         BLOCK#   BLOCKS  DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              10539    1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

You can see block counts are exceeding.

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

DEST_ID STATUS    DATABASE_MODE   TYPE        RECOVERY_MODE        ERROR

---------- --------- --------------- ---------------- ----------------------- -----

      1   VALID     MOUNTED-STANDBY LOCAL       MANAGED

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              10918     1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              10937     1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

SQL> select dest_id,status,error from v$archive_dest where status<>’INACTIVE’;

 DEST_ID      STATUS    ERROR

------------ ------------ --------------------------------------------------------

      1         VALID

     32         VALID

SQL> select name,value,datum_time,time_computed from v$dataguard_stats;

NAME                     VALUE          DATUM_TIME            TIME_COMPUTED

------------------------ --------------------------------- ------------------------

transport lag          +00 01:03:00       03/06/2017 02:08:25 03/06/2017 02:09:26

apply lag             +00 01:03:00       03/06/2017 02:08:25 03/06/2017 02:09:26

apply finish time      +00 00:00:00.001                      03/06/2017 02:09:26

estimated startup time  12                                   03/06/2017 02:09:26

SQL> alter database recover managed standby database cancel;

Database altered.

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

Database altered.

Above command is depreciated in Oracle 12c Dataguard. 

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

DEST_ID      STATUS    DATABASE_MODE   TYPE       RECOVERY_MODE     ERROR

-------------- ---------- ---------------- --------- ------------------ -----------

      1         VALID     MOUNTED-STANDBY LOCAL       MANAGED

SQL> select * from v$standby_log;

no rows selected

SQL> alter database recover managed standby database cancel;

Database altered.

Primary Side:

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

   DEST_ID      STATUS    DATABASE_MODE   TYPE        RECOVERY_MODE  ERROR

-------------- --------- --------------- ------------ --------------- -----------

      1         VALID     OPEN             LOCAL       IDLE

SQL> select dest_id,status,database_mode,type,recovery_mode,error from v$archive_dest_status where dest_id=2;

 DEST_ID      STATUS    DATABASE_MODE   TYPE       RECOVERY_MODE        ERROR

------------- ---------- ----------------- ---------- --------------- -------------

      2        VALID     MOUNTED-STANDBY PHYSICAL   MANAGED

SQL> select GROUP#,THREAD#,members,BYTES/1024/1024 from v$log;

GROUP#    THREAD# MEMBERS   BYTES/1024/1024

----------- --------- -------- ----------------

      1       1        1         50

      2       1        1         50

      3       1        1         50

Standby Side:

 SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/stb1/stbredo01.log’ size 55M;

Database altered.

SQL> alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/stb1/stbredo02.log’ size 55M;

Database altered.

SQL> alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/stb1/stbredo03.log’ size 55M;

Database altered.

SQL> alter database add standby logfile thread 1 ‘/u01/app/oracle/oradata/stb1/stbredo04.log’ size 55M;

Database altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,status from v$standby_log;

  GROUP#    THREAD#  SEQUENCE# STATUS

---------- ---------- ---------- ----------

      4       0        0 UNASSIGNED

      5       0        0 UNASSIGNED

      6       0        0 UNASSIGNED

      7       0        0 UNASSIGNED

SQL> col member for a50

SQL> select GROUP#,member,type,status from v$logfile;

GROUP# MEMBER                                      TYPE     STATUS

---------- ------------------------------------------- -------- --------

      3 /u01/app/oracle/oradata/stand1/redo03.log    ONLINE

      2 /u01/app/oracle/oradata/stand1/redo02.log    ONLINE

      1 /u01/app/oracle/oradata/stand1/redo01.log    ONLINE

      4 /u01/app/oracle/oradata/stb1/stbredo01.log   STANDBY

      5 /u01/app/oracle/oradata/stb1/stbredo02.log   STANDBY

      6 /u01/app/oracle/oradata/stb1/stbredo03.log   STANDBY

      7 /u01/app/oracle/oradata/stb1/stbredo04.log   STANDBY

7 rows selected.

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

Database altered.

Above command is depreciated in Oracle 12c Dataguard. 

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

 DEST_ID   STATUS    DATABASE_MODE    TYPE      RECOVERY_MODE           ERROR

------------ -------- ----------------- --------- ------------------------- ----

      1     VALID     MOUNTED-STANDBY  LOCAL      MANAGED REAL TIME APPLY

 SQL> select name,value,datum_time,time_computed from v$dataguard_stats;

NAME                 VALUE              DATUM_TIME         TIME_COMPUTED

------------------------- ------------------------------ --------------------------

transport lag        +00 01:29:00       03/06/2017 02:34:26  03/06/2017 02:34:33

apply lag            +00 01:29:00       03/06/2017 02:34:26  03/06/2017 02:34:33

apply finish time      +00 00:00:00.001                       03/06/2017 02:34:33

estimated startup time 12                                     03/06/2017 02:34:33

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              12837     1      0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using archived logfile disconnect;

Database altered.

Above command will start Normal Apply in Oracle 12c Dataguard. 

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS                BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH      CONNECTED       0       0       0

ARCH      CONNECTED       0       0       0

ARCH      CONNECTED       0       0       0

ARCH      CONNECTED       0       0       0

RFS       IDLE            0       0       0

RFS       IDLE           13046     1       0

RFS       IDLE            0       0       0

RFS       IDLE            0       0       0

MRP0      WAIT_FOR_LOG    0       0       0

9 rows selected.

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

DEST_ID STATUS    DATABASE_MODE   TYPE       RECOVERY_MODE        ERROR

------------------- ----------------- ----------- -------------------- ------------

      1   VALID     MOUNTED-STANDBY LOCAL       MANAGED

SQL> set line 400

SQL> col for value a25

SQL> select name,value,datum_time,time_computed from v$dataguard_stats;

NAME                  VALUE             DATUM_TIME           TIME_COMPUTED

---------------------- ----------------- --------------------- --------------------

transport lag          +00 01:35:01       03/06/2017 02:40:25 03/06/2017 02:40:27

apply lag             +00 01:35:01       03/06/2017 02:40:25 03/06/2017 02:40:27

apply finish time      +00 00:00:00.001                       03/06/2017 02:40:27

estimated startup time  12                                     03/06/2017 02:40:27

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

*New: Starting with Oracle 12c R1, Above command will start REAL TIME APPLY in Oracle 12c Dataguard. 

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

DEST_ID STATUS    DATABASE_MODE   TYPE        RECOVERY_MODE             ERROR

---------- --------- --------------- ----------- ----------------------- -----

      1   VALID     MOUNTED-STANDBY LOCAL       MANAGED REAL TIME APPLY

SQL> select name,value,datum_time,time_computed from v$dataguard_stats;

NAME                 VALUE             DATUM_TIME        TIME_COMPUTED

--------------------- ----------------- ------------------- -----------------

transport lag        +00 01:35:01     03/06/2017 02:40:25  03/06/2017 02:41:20

apply lag            +00 01:35:01     03/06/2017 02:40:25 03/06/2017 02:41:20

apply finish time    +00 00:00:00.001                    03/06/2017 02:41:20

estimated startup time 12                                  03/06/2017 02:41:20

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS        BLOCK#     BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CONNECTED          0       0       0

ARCH  CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

ARCH   CONNECTED          0       0       0

RFS    IDLE               0       0       0

RFS    IDLE              13260     1       0

RFS    IDLE               0       0       0

RFS    IDLE               0       0       0

MRP0   WAIT_FOR_LOG       0       0       0

9 rows selected.

Primary Side:

 SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Standby Side:

 SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS          BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CLOSING       1       3       0

ARCH   CONNECTED     0       0       0

ARCH   CONNECTED     0       0       0

ARCH   CLOSING       1       5       0

RFS    IDLE          0       0       0

RFS    IDLE          576     1       0

RFS    IDLE          0       0       0

RFS    IDLE          0       0       0

MRP0   APPLYING_LOG  576     112640   0

9 rows selected.

SQL> select name,value,datum_time,time_computed from v$dataguard_stats;

NAME                   VALUE                     DATUM_TIME    TIME_COMPUTED

-------------------------------- --------------------------------------------------

transport lag          +00 00:00:00       03/06/2017 02:43:35 03/06/2017 02:43:36

apply lag             +00 00:00:00      03/06/2017 02:43:35 03/06/2017 02:43:36

apply finish time      +00 00:00:00.000                     03/06/2017 02:43:36

estimated startup time  12                                    03/06/2017 02:43:36

Now you can see lag is become zero now.

SQL> select process,status,block#,blocks,delay_mins from v$managed_standby;

PROCESS   STATUS          BLOCK#  BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ----------

ARCH   CLOSING       1       3       0

ARCH   CONNECTED     0       0       0

ARCH   CONNECTED     0       0       0

ARCH   CLOSING       1       5       0

RFS    IDLE          0       0       0

RFS    IDLE          599     1       0

RFS    IDLE          0       0       0

RFS    IDLE          0       0       0

MRP0   APPLYING_LOG  599     112640   0

Hope you enjoyed and learn some thing from my this post. Please do comment on this post if you liked it.

Thank you for reading… This is Airy…Enjoy Learning 🙂