Step to resize standby redolog files
In Data-guard, if we recreate or add the standby redolog files due to thread number issue or Size mentioned wrong then in that case we need to drop or create the standby redo log files in DB standby database.
Error due to thread and size:
No standby redo logfiles selected
RFS[602]: No standby redo logfiles of size 1024000 blocks exist
RFS[602]: No standby redo logfiles selected (reason:7)
RFS[4]: No standby redo logfiles created for T-1
RFS[4]: Opened log for T-1.S-57586 dbid 618415567 branch 953210132
Following are the steps to drop and create new standby database:
1. Check Primary Redo Thread Number and size.
SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS ------- ------ --------- ---------- --- ---------- 1 1 57586 209715200 NO CURRENT 1 2 57584 209715200 YES INACTIVE 1 3 57585 209715200 YES INACTIVE
Note: 209715200/1014/1024 = 200 MB
2. Check Standby Thread number and Size for Standby redo logs.
SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS ------- ------ --------- ---------- --- ---------- 0 4 0 52428800 YES UNASSIGNED 0 5 0 52428800 YES UNASSIGNED 0 6 0 52428800 YES UNASSIGNED 0 7 0 52428800 YES UNASSIGNED
Note: 52428800/2014/1024= 50MB
If you find anything different from both size or thread number then recreate the standby redo to avoids error in alert log.
As you see standby is unassigned status means it is not used by standby database.
3. Stop the data-guard recovery process in Standby database:
-- for standby db which is under recovery, recovery needs to be stopped first
alter database recover managed standby database cancel;
4. Drop the existing Standby redo files:
Note: There is rule to configure standby redo log file Standby redolog file: N+1 and N stand for redo log files in primary.
In this example we configure 6 standby redo files.
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
5. Create new Standby logfile with THREAD Clauses.
alter database add standby logfile thread 1 group 4 size 200m;
alter database add standby logfile THREAD 1 group 4 ('D:ORACLEXESTANDBYREDO04.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 5 ('D:ORACLEXESTANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 6 ('D:ORACLEXESTANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 7 ('D:ORACLEXESTANDBYREDO06.log') SIZE 200M;
--Example for two members
--alter database add standby logfile THREAD 1 group 5 ('D:ORACLEXESTANDBYREDO01A.log','D:ORACLEXESTANDBYREDO01B.log') SIZE 200M;
--Example for ASM
--alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 200M;
Note: in my case standby redo size(50M) is different with primary redo size(200M). so I corrected that one also while created new standby.
6. Now verify the thread number and redo log
SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
7. Start the Recovery of the dataguard in oracle.
alter database recover managed standby database disconnect from session;