How to manage Standby Redo Logs (SRL) in Standby Database
Standby Redo Logs (SRL) is similar to Online Redo Log (ORL) and only difference between two is that Standby Redo Log is used to store redo data received from another database (primary database).Standby Redo Logs are only used if have the LGWR as transport mode to Remote Standby Database.
Q. How to check using Real-Time Apply ?
SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
Q. How to identify standby redo logs ?
SQL> select * from v$standby_log;
Q. How to check members of standby redo log file
SQL> select * from v$logfile where type=’STANDBY’;
Q. How to add Standby Redo Log File to a Specific Group Number
SQL> alter database add standby logfile group 6 (
'/<full path for srl>/log06a.dbf',
'/<full path for srl>/log06b.dbf'
) size 250m;
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
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
3. Stop the data-guard recovery process in Standby database:
alter database recover managed standby database cancel;
4. Drop the existing Standby redo files:
There is rule to configure standby redo log file Standby redolog file: N+1 and N stand for redo log files in primary.
SQL> select GROUP#,MEMBERS,BYTES/1024/1024 from v$log;
GROUP# MEMBERS BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 1 200
4 1 200
alter database add standby logfile thread 1 group 5 size 200m;
alter database add standby logfile thread 1 group 6 size 200m;
alter database add standby logfile thread 1 group 7 size 200m;
alter database add standby logfile thread 1 group 8 size 200m;
alter database add standby logfile thread 2 group 9 size 200m;
alter database add standby logfile thread 2 group 10 size 200m;
alter database add standby logfile thread 2 group 11 size 200m;
alter database add standby logfile thread 2 group 12 size 200m;
SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------
1 5 0 209715200 YES UNASSIGNED
1 6 0 209715200 YES UNASSIGNED
1 7 0 209715200 YES UNASSIGNED
1 8 0 209715200 YES UNASSIGNED
2 9 0 209715200 YES UNASSIGNED
2 10 0 209715200 YES UNASSIGNED
2 11 0 209715200 YES UNASSIGNED
2 12 0 209715200 YES UNASSIGNED
8 rows selected.
SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 494 209715200 NO CURRENT
1 2 493 209715200 YES INACTIVE
2 3 471 209715200 YES INACTIVE
2 4 472 209715200 NO CURRENT
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.
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;
Comments
Post a Comment