By William Sescu
I have setup a DataGuard environment and followed the instructions from Oracle to create the Standby Redo Logs. The Standby Redo Logs have to be the same size as the Online Redo Logs. If not, the RFS process won’t attach Standby Redo Logs, and you should have at least one more of the Standby Redo Log Group as you have for your Online Redo Log Group per Thread.
For my single instance, this should be quite straight forward, and so I issued the following commands on the primary and standby.
alter database add standby logfile group 4 size 1073741824; alter database add standby logfile group 5 size 1073741824; alter database add standby logfile group 6 size 1073741824; alter database add standby logfile group 7 size 1073741824;
After setting all up, I started the new cool Broker command “DGMGRL> VALIDATE DATABASE VERBOSE ‘<DB>’;” and surprisingly found, that the validation complains that I do have insufficient Standby Redo Logs.
Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (OCM12C_SITE2) (OCM12C_SITE1) 1 3 3 Insufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (OCM12C_SITE1) (OCM12C_SITE2) 1 3 3 Insufficient SRLs
After looking everything up on Primary and Standby, the number of Log Groups and the sizes looked ok. I do have 3 Online Redo Log Groups with 1G each, and I have 4 Standby Redo Log Groups with 1G each.
-- Standby SQL> select thread#, group#, sequence#, status, bytes from v$log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------------- ---------- 1 1 0 UNUSED 1073741824 1 3 0 UNUSED 1073741824 1 2 0 UNUSED 1073741824 SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 0 UNASSIGNED 1073741824 1 5 552 ACTIVE 1073741824 1 6 0 UNASSIGNED 1073741824 0 7 0 UNASSIGNED 1073741824 -- Primary SQL> select thread#, group#, sequence#, status, bytes from v$log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------------- ---------- 1 1 550 INACTIVE 1073741824 1 2 551 INACTIVE 1073741824 1 3 552 CURRENT 1073741824 SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 0 UNASSIGNED 1073741824 1 5 0 UNASSIGNED 1073741824 1 6 0 UNASSIGNED 1073741824 0 7 0 UNASSIGNED 1073741824
The only strange thing, is that the Standby Redo Log Group 7, shows up with Thread 0, instead of Thread 1.
Did not even know, that a thread 0 exists. It always starts with 1, and in case of RAC, you might see Thread 2, 3 or more. But if you want to, you can perfectly create thread 0 without any issues. For what reasons, I don’t know.
SQL> alter database add standby logfile thread 0 group 8 size 1073741824; Database altered.
Ok. Lets correct the Thread 0 thing, and then lets see want the “DGMGRL> VALIDATE DATABASE VERBOSE ‘<DB>’;” shows.
-- On Standby DGMGRL> EDIT DATABASE 'OCM12C_SITE1' SET STATE = 'APPLY-OFF'; Succeeded. SQL> alter database drop standby logfile group 7; Database altered. SQL> alter database add standby logfile thread 1 group 7 size 1073741824; Database altered. SQL> select thread#, group#, sequence#, status, bytes from v$standby_log; THREAD# GROUP# SEQUENCE# STATUS BYTES ---------- ---------- ---------- ---------- ---------- 1 4 553 ACTIVE 1073741824 1 5 0 UNASSIGNED 1073741824 1 6 0 UNASSIGNED 1073741824 1 7 0 UNASSIGNED 1073741824 DGMGRL> EDIT DATABASE 'OCM12C_SITE1' SET STATE = 'APPLY-ON'; Succeeded. -- On Primary SQL> alter database drop standby logfile group 7; Database altered. SQL> alter database add standby logfile thread 1 group 7 size 1073741824; Database altered.
And here we go. Now I have sufficient Standby Redo Logs.
Current Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (OCM12C_SITE2) (OCM12C_SITE1) 1 3 4 Sufficient SRLs Future Log File Groups Configuration: Thread # Online Redo Log Groups Standby Redo Log Groups Status (OCM12C_SITE1) (OCM12C_SITE2) 1 3 4 Sufficient SRLs
Conclusion
Even on a single instance, use the thread number in your create Standby Redo Log statement.
alter database add standby logfile thread 1 group 4 size 1073741824; alter database add standby logfile thread 1 group 5 size 1073741824; alter database add standby logfile thread 1 group 6 size 1073741824; alter database add standby logfile thread 1 group 7 size 1073741824;
Cheers,
William