Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article I will describe Resolving Insufficient SRLs Warning in Pre-Switchover Validate Command.
When we run the Validate command on the Standby database that will be Primary before Swtichover,
we get the Insufficient SRLs warning in the Current Log File Groups and Future Log File Groups sections.
Here’s how we fix this warning.
1. We are learning configuration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: primary - Primary database prmyFS - Far sync instance physical - Physical standby database snapshot - Snapshot standby database logical - Logical standby database logical2 - Physical standby database Members Not Receiving Redo: physclFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 49 seconds ago) |
2. We are questioning whether the standby database to be primary is suitable for Switchover.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | DGMGRL> validate database physical; Database Role: Physical standby database Primary Database: primary Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: primary: On physical: Off Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (primary) (physical) 1 3 3 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (physical) (primary) 1 3 3 Insufficient SRLs Transport-Related Property Settings: Property primary Value physical Value RedoRoutes (LOCAL:prmyFS SYNC) (LOCAL:physclFS SYNC) |
3. We are querying Online and Standby Redo Logs in Primary and Standby database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [Primary - 07-04-2017 14:06:24] SQL> select thread#, group#, sequence#, status, bytes from v$log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------------- ---------- 1 1 812 CURRENT 52428800 1 2 810 INACTIVE 52428800 1 3 811 INACTIVE 52428800 [Primary - 07-04-2017 14:15:47] SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 0 UNASSIGNED 52428800 1 5 0 UNASSIGNED 52428800 1 6 0 UNASSIGNED 52428800 0 7 0 UNASSIGNED 52428800 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [Physical - 07-04-2017 14:06:24] SQL> select thread#, group#, sequence#, status, bytes from v$log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------------- ---------- 1 1 0 UNUSED 52428800 1 2 0 UNUSED 52428800 1 3 0 UNUSED 52428800 [Physical - 07-04-2017 14:15:15] SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 812 ACTIVE 52428800 1 5 0 UNASSIGNED 52428800 1 6 0 UNASSIGNED 52428800 0 7 0 UNASSIGNED 52428800 |
As we have seen above, a Redo Log is created as Thread# 0 in Standby Redo Logs.
In fact, while working to establish the Primary database Standby, Standby Redo Logs were created as an extra to the number of Online Redo Logs in the Primary.
But Oracle created one of the created as Thread# 0, which I can’t understand why.
In this case, Oracle’s best recommendation is not followed. That’s why we’re getting a warning.
4. We create a new Standby Log File with thread# 0 in the same size in the Primary.
This is because if Oracle is using it for a special reason, this is not to interfere with the process.
1 2 3 4 5 6 7 8 9 10 11 12 13 | [Primary - 07-04-2017 14:15:55] SQL> alter database add standby logfile thread 0 group 8 size 52428800; Database altered. [Primary - 07-04-2017 14:19:27] SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 0 UNASSIGNED 52428800 1 5 0 UNASSIGNED 52428800 1 6 0 UNASSIGNED 52428800 0 7 0 UNASSIGNED 52428800 0 8 0 UNASSIGNED 52428800 |
5. We create as thread #1 by DROPing the Standby Log File number 7 in Primary.
1 2 3 4 5 6 7 | [Primary - 07-04-2017 14:19:31] SQL> alter database drop standby logfile group 7; Database altered. [Primary - 07-04-2017 14:22:19] SQL> alter database add standby logfile thread 1 group 7 size 52428800; Database altered. |
6. We perform the same operations in the standby database. First we stop Redo Apply, otherwise we will get an error as below.
1 2 3 | [Physical - 07-04-2017 14:19:47] SQL> alter database recover managed standby database cancel; Database altered. |
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
7. We create a new Standby Redo Log group with thread# 0 in the Standby database, DROP the Standby Redo Log group with the current thread# 0, and create the Standby Redo Log file with the same group as thread# 1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | [Physical - 07-04-2017 14:20:26] SQL> alter database add standby logfile thread 0 group 8 size 52428800; Database altered. [Physical - 07-04-2017 14:20:29] SQL> alter database recover managed standby database disconnect; Database altered. [Physical - 07-04-2017 14:20:43] SQL> alter database drop standby logfile group 7; Database altered. [Physical - 07-04-2017 14:21:24] SQL> alter database add standby logfile thread 1 group 7 size 52428800; Database altered. |
8. We check the Standby Redo Log files on the Primary and Standby sides.
1 2 3 4 5 6 7 8 9 | [Primary - 07-04-2017 14:22:29] SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 0 UNASSIGNED 52428800 1 5 0 UNASSIGNED 52428800 1 6 0 UNASSIGNED 52428800 1 7 0 UNASSIGNED 52428800 0 8 0 UNASSIGNED 52428800 |
1 2 3 4 5 6 7 8 9 | [Physical - 07-04-2017 14:21:34] SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 812 ACTIVE 52428800 1 5 0 UNASSIGNED 52428800 1 6 0 UNASSIGNED 52428800 1 7 0 UNASSIGNED 52428800 0 8 0 UNASSIGNED 52428800 |
9. By running the Validate command again, we check if the error has occurred.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DGMGRL> validate database physical; Database Role: Physical standby database Primary Database: primary Ready for Switchover: Yes Ready for Failover: Yes (Primary Running) Flashback Database Status: primary: On physical: Off Transport-Related Property Settings: Property primary Value physical Value RedoRoutes (LOCAL:prmyFS SYNC) (LOCAL:physclFS SYNC) |
