SRL stands for Standby Redo Log. Today in this article we will learn about how to resolve Insufficient SRLs reported by DGMGRL. If you are having different number of SRLs between Primary and Standby, then you may get Insufficient SRLs message. Also, this may also happen if same no of SRLs you’re having in Primay and Standby with different thread. Let’s go through the steps in detail :
Table of Contents
Issue: Insufficient SRLs reported by DGMGRL :
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(TEST1) (TEST1STD)
1 3 2 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(TEST1STD) (TEST1)
1 3 0 Insufficient SRLs
Warning: standby redo logs not configured for thread 1 on TEST1
Solution :
After checking we can see that sufficient no. of SRL is present in primary and standby, but still you’re getting Insufficient SRLs because these are thread 0 :
primary :
SQL> select thread#,group#,sequence#,status ,bytes from v$log;
THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------------- ----------
1 1 55 CURRENT 209715200
1 2 53 INACTIVE 209715200
1 3 54 INACTIVE 209715200
SQL> select thread#,group#,sequence#,status ,bytes from v$standby_log;
THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------- ----------
0 4 0 UNASSIGNED 209715200
0 5 0 UNASSIGNED 209715200
0 6 0 UNASSIGNED 209715200
0 7 0 UNASSIGNED 209715200
standby :
SQL> select thread#,group#,sequence#,status ,bytes from v$log;
THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------------- ----------
1 1 0 UNUSED 209715200
1 3 0 UNUSED 209715200
1 2 0 UNUSED 209715200
SQL> select thread#,group#,sequence#,status ,bytes from v$standby_log;
THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------- ----------
1 4 55 ACTIVE 209715200
1 5 0 UNASSIGNED 209715200
0 6 0 UNASSIGNED 209715200
0 7 0 UNASSIGNED 209715200
Then drop the SRLs from Standby which have thread 0 as below and re-create them :
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 size 209715200;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 209715200;
Database altered.
Check now the number of Standby Log :
SQL> select thread#,group#,sequence#,status ,bytes from v$standby_log;
THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------- ----------
1 4 55 ACTIVE 209715200
1 5 0 UNASSIGNED 209715200
1 6 0 UNASSIGNED 209715200
1 7 0 UNASSIGNED 20971520
In Primary, we have to make 4 SRLs as well with thread 1, let’s go through it :
Primary :
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
SQL> alter database add standby logfile thread 1 group 4 size 209715200;
Database altered.
SQL> alter database add standby logfile thread 1 group 5 size 209715200;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 size 209715200;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 size 209715200;
Database altered.
Check in Primary :
SQL> select thread#,group#,sequence#,status ,bytes from v$standby_log;
THREAD# GROUP# SEQUENCE# STATUS BYTES
---------- ---------- ---------- ---------- ----------
1 4 0 UNASSIGNED 209715200
1 5 0 UNASSIGNED 209715200
1 6 0 UNASSIGNED 209715200
1 7 0 UNASSIGNED 209715200
Check again. Error is gone.
Connected as SYSDG.
DGMGRL> validate database verbose 'TEST1STD';
Database Role: Physical standby database
Primary Database: TEST1
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
TEST1 : On
TEST1STD: On
Capacity Information:
Database Instances Threads
TEST1 1 1
TEST1STD 1 1
Managed by Clusterware:
TEST1 : NO
TEST1STD: NO
Temporary Tablespace File Information:
TEST1 TEMP Files: 1
TEST1STD TEMP Files: 1
Data file Online Move in Progress:
TEST1: No
TEST1STD: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 0 seconds ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 0 seconds ago)
Transport Status: Success
Log Files Cleared:
TEST1 Standby Redo Log Files: Cleared
TEST1STD Online Redo Log Files: Cleared
TEST1STD Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(TEST1) (TEST1STD)
1 3 4 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(TEST1STD) (TEST1)
1 3 4 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(TEST1) (TEST1STD)
1 200 MBytes 200 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(TEST1STD) (TEST1)
1 200 MBytes 200 MBytes
Apply-Related Property Settings:
Property TEST1 Value TEST1STD Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0
Transport-Related Property Settings:
Property TEST1 Value TEST1STD Value
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional OPTIONAL
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON
Automatic Diagnostic Repository Errors:
Error TEST1 TEST1STD
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO
DGMGRL>
So, this is the way how you can troubleshoot Insufficient SRLs reported by DGMGRL. Hope this article helps!! Also, you can take the reference from the Oracle Doc. to resolve the Insufficient SRLs messages.
Other ORA-error related articles:
Pingback: How to drop standby redo logs in Oracle : 5 Easy Steps