Step to resize standby redolog files in Dataguard

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 dataguard 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:\ORACLEXE\STANDBYREDO04.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 6 ('D:\ORACLEXE\STANDBYREDO05.log') SIZE 200M;
alter database add standby logfile THREAD 1 group 7 ('D:\ORACLEXE\STANDBYREDO06.log') SIZE 200M;

--Example for two members
--alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO01A.log','D:\ORACLEXE\STANDBYREDO01B.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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.