Home » Step to resize standby redolog files in Dataguard

Step to resize standby redolog files in Dataguard

by tuanlp

 

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;


You may also like