Thursday, 3 August 2017

Insufficient SRLs reported by DGMGRL VALIDATE DATABASE-12c

DGMGRL> validate database 'STG_PROD';

   Database Role:    Primary database

   Ready for Switchover:  Yes

 DGMGRL> validate database 'stg_prod_dg';

   Database Role:     Physical standby database
  Primary Database:  STG_PROD

   Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

   Flashback Database Status:
    STG_PROD:     On
    stg_prod_dg:  Off

   Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (STG_PROD)              (stg_prod_dg)
    1         11                      5                       Insufficient SRLs   ======>>>>>>THIS

   Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (stg_prod_dg)           (STG_PROD)
    1         11                      5                       Insufficient SRLs  ======>>>>>>THIS

This is because if you see the thread#, it is showing as 0 which is incorrect. it has to be 1 ( stand alone DB)


SQL> set pages 200
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

    THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13         54 ACTIVE      419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400
         0         17          0 UNASSIGNED  419430400
         0         18          0 UNASSIGNED  419430400
         0         19          0 UNASSIGNED  419430400
         0         20          0 UNASSIGNED  419430400
         0         21          0 UNASSIGNED  419430400
         0         22          0 UNASSIGNED  419430400
         0         23          0 UNASSIGNED  419430400

###

SQL> set pages 2000
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13          0 UNASSIGNED  419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400
         0         17          0 UNASSIGNED  419430400
         0         18          0 UNASSIGNED  419430400
         0         19          0 UNASSIGNED  419430400
         0         20          0 UNASSIGNED  419430400
         0         21          0 UNASSIGNED  419430400
         0         22          0 UNASSIGNED  419430400
         0         23          0 UNASSIGNED  419430400


##Solution.

SQL> alter database drop standby logfile group 17;
alter database drop standby logfile group 17
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


##Stop the MRP on STANDBY###
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database drop standby logfile group 17;

Database altered.

SQL> alter database drop standby logfile group 18;

Database altered.

SQL> alter database drop standby logfile group 19;

Database altered.

SQL> alter database drop standby logfile group 20;

Database altered.

SQL> alter database drop standby logfile group 21;

Database altered.

SQL> alter database drop standby logfile group 22;

Database altered.

SQL> alter database drop standby logfile group 23;

Database altered.

SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13         54 ACTIVE      419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400


SQL> alter database add standby logfile thread 1 group 17 size 419430400;

Database altered.

SQL> alter database add standby logfile thread 1 group 18 size 419430400;
alter database add standby logfile thread 1 group 19 size 419430400;
alter database add standby logfile thread 1 group 20 size 419430400;
alter database add standby logfile thread 1 group 21 size 419430400;
alter database add standby logfile thread 1 group 22 size 419430400;
alter database add standby logfile thread 1 group 23 size 419430400;
Database altered.

SQL>

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13         54 ACTIVE      419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400
         1         17          0 UNASSIGNED  419430400
         1         18          0 UNASSIGNED  419430400
         1         19          0 UNASSIGNED  419430400
         1         20          0 UNASSIGNED  419430400
         1         21          0 UNASSIGNED  419430400
         1         22          0 UNASSIGNED  419430400
         1         23          0 UNASSIGNED  419430400

12 rows selected.

Do the same on Primary as well. 

SQL> alter database drop standby logfile group 17;
alter database drop standby logfile group 18;
alter database drop standby logfile group 19;
alter database drop standby logfile group 20;
alter database drop standby logfile group 21;
alter database drop standby logfile group 22;
alter database drop standby logfile group 23;
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13          0 UNASSIGNED  419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400


SQL> alter database add standby logfile thread 1 group 17 size 419430400;
alter database add standby logfile thread 1 group 18 size 419430400;
alter database add standby logfile thread 1 group 19 size 419430400;
alter database add standby logfile thread 1 group 20 size 419430400;
alter database add standby logfile thread 1 group 21 size 419430400;
alter database add standby logfile thread 1 group 22 size 419430400;
alter database add standby logfile thread 1 group 23 size 419430400;
Database altered.

SQL>

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
SQL>
SQL> select thread#, group#, sequence#, status, bytes from v$standby_log;

   THREAD#     GROUP#  SEQUENCE# STATUS          BYTES
---------- ---------- ---------- ---------- ----------
         1         12          0 UNASSIGNED  419430400
         1         13          0 UNASSIGNED  419430400
         1         14          0 UNASSIGNED  419430400
         1         15          0 UNASSIGNED  419430400
         1         16          0 UNASSIGNED  419430400
         1         17          0 UNASSIGNED  419430400
         1         18          0 UNASSIGNED  419430400
         1         19          0 UNASSIGNED  419430400
         1         20          0 UNASSIGNED  419430400
         1         21          0 UNASSIGNED  419430400
         1         22          0 UNASSIGNED  419430400
         1         23          0 UNASSIGNED  419430400

12 rows selected.



## Start the MRP on standby 
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process , status , sequence#,thread# from v$managed_standby ;

PROCESS   STATUS        SEQUENCE#    THREAD#
--------- ------------ ---------- ----------
ARCH      CLOSING              52          1
ARCH      CONNECTED             0          0
ARCH      CLOSING              53          1
ARCH      CLOSING              51          1
RFS       IDLE                 54          1
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
MRP0      APPLYING_LOG         54          1

9 rows selected.



##Now check the switchover status

DGMGRL> validate database 'STG_PROD';

  Database Role:    Primary database

  Ready for Switchover:  Yes      <-----------Ready for Switchover

DGMGRL> validate database 'stg_prod_dg';

  Database Role:     Physical standby database
  Primary Database:  STG_PROD

  Ready for Switchover:  Yes    <--------This confirms Switchover readiness
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    STG_PROD:     On
    stg_prod_dg:  Off




Posted by at
Labels:

2 comments:

  1. vinhnguyen14 June 2018 at 09:32

    Thank you very much! i have used this procedure to apply for my case and it is worked.
    Vinh

    ReplyDelete
  2. Nabhesh Joshi13 December 2020 at 18:49

    Glad to hear that my post helped. Thank you for visiting !

    ReplyDelete

Subscribe to: Post Comments (Atom)