Building a Physical Standby Database (12/14) - Recover managed standby database

Building a Physical Standby Database (11/14) - Create spfile for both primary and standby database

So far, the physical standby RAC is built up and mount, but the gap between the primary and the standby has not been applied. The standby database needs to be recovered. Let's see how is recovery working.

1. On the primary side.
Please notice that, the SWITCHOVER_STATUS is RESOLVABLE GAP on primary. But don't worry, once the Managed Recovery Process (MRP) is functional on standby, the status will become normal.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         1 READ WRITE           PRIMARY          RESOLVABLE GAP
         2 READ WRITE           PRIMARY          RESOLVABLE GAP

SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 LNS       WRITING               1         91        574          1
         2 LNS       WRITING               2         91       6249          1

2. On the standby side.
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         1 MOUNTED              PHYSICAL STANDBY RECOVERY NEEDED
         2 MOUNTED              PHYSICAL STANDBY RECOVERY NEEDED

SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  2         91       2231          1
         1 RFS       IDLE                  0          0          0          0
The results show that the two LNS on the primary database are transporting redo logs to to the standby database where RFS are receiving redo logs, and we don't see any MRP on the standby database, which means the transportation is working, but the applying is not.

3. Recover the standby database.
The current switchover_status of the standby database is "RECOVER NEEDED", so next step will be recovering the standby database. In this case, we choose instance #2 of the standby RAC to recover the database.
[oracle@standby02 ~]$ sqlplus / as sysdba
...
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         2 MOUNTED              PHYSICAL STANDBY NOT ALLOWED
         1 MOUNTED              PHYSICAL STANDBY NOT ALLOWED

SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  0          0          0          0
         1 RFS       IDLE                  2         91       2441          2
         1 RFS       IDLE                  0          0          0          0
         2 MRP0      APPLYING_LOG          2         72       7925       7927
You can see the MRP0 on instance #2 is started, which means the applying is working now.

4. Check the primary database again.
SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;


   INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
---------- -------------------- ---------------- --------------------
         1 READ WRITE           PRIMARY          TO STANDBY
         2 READ WRITE           PRIMARY          TO STANDBY

SQL> select inst_id, process, status, thread#, sequence#, block#, blocks from gv$managed_standby where process in ('RFS','LNS','MRP0');

   INST_ID PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
         1 LNS       WRITING               1         91        574          1
         2 LNS       WRITING               2         91       8869          1

5. Test transportation and check the apply lag time after "switch logfile".
We issue the following command on both instances of the primary database.
SQL> alter system switch logfile;
Then we wait for MRP0 on instance #2 to digest the two new archived logs, then we check data guard stats like this.
SQL> SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME       VALUE        DATUM_TIME           TIME_COMPUTED
---------- ------------ -------------------- --------------------
apply lag  +00 00:02:23 11/09/2012 20:22:12  11/09/2012 20:22:12
The apply lag time is within our expectations, and we can confirm that the data guard is built up and working now.

Building a Physical Standby Database (13/14) - Configure data guard broker