Saturday, September 10, 2011

ADDING / RESIZING LOGS in DATAGUARD Environment

THINGS TO NOTE BEFORE DOING:
I did this on 10.2.0.4
Primary 2 node RAC
Standby 2 node RAC

The group number must be between 1 and the value of the MAXLOGFILES clause. Do not skip log file group numbers (that is, do not number groups 10, 20, 30, and so on), or you will use additional space in the standby database control file.

Although the STANDBY LOGs are only used when the database is running in the standby role, Oracle recommends that you create a standby redo log on the primary database so that the primary database can switch over quickly to the standby role without the need for additional DBA intervention.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files.

You will need one more STANDBY LOGFILE group than the number of LOGILE groups on the primary database ( for each thread).

STEP 1: Check existing LOG files and STANDBY LOG files
do this on both PRIMARY and STANDBY dbs
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$LOG order by group#;
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$STANDBY_LOG order by group#;

STEP 2: Cancel Recovery
do this on standby db.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

STEP 3: Set STANDBY_FILE_MANAGEMENT to manual
do this on both PRIMARY and STANDBY dbs
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=both sid='*';

STEP 4: DELETE and ADD LOG and STANDBY LOG files
This is usual process
do this on both PRIMARY and STANDBY dbs
alter system switch logfile; -- this on primary to make the GROUP ready for deletion
ALTER DATABASE CLEAR LOGFILE GROUP 6; -- this on standby to make the GROUP ready for deletion
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DISKG1','+DISKG2') size 150M;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 07;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('+DISKG1','+DISKG2') size 150M;

STEP 5: WINDUP and VERIFY
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$LOG order by group#; -- on both PRIMARY and STANDBY dbs
SELECT GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024 "SIZE MB", ARCHIVED,STATUS FROM V$STANDBY_LOG order by group#; -- on both PRIMARY and STANDBY dbs
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both sid='*'; -- on both PRIMARY and STANDBY dbs
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -- on STANDBY
alter system switch logfile; -- on PRIMARY and verify standby is recovering the new logs

STEP 6: Make sure Standby is not too far behind PRIMARY
do this on PRIMARY. This forces a logswitch every 1800 secs.
We needed this because during off-peak hours log switch is happing once in 10+ hours.
alter system set ARCHIVE_LAG_TARGET=1800 scope=both sid='*';