Steps to resize standby redolog files

Steps to resize standby redolog files

Resize Standby Redo Logs

1. On primary defer log shipping (dynamic change)

alter system set log_archive_dest_state_2 = defer scope = memory;

2. On standby database cancel managed recovery

alter database recover managed standby database cancel;

3. Drop standby logs on standby database

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;

4. Recreate the new Standby logs

alter database add standby logfile THREAD 1 group 4 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;   alter database add standby logfile THREAD 1 group 5 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;   alter database add standby logfile THREAD 1 group 6 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;   alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;

5. Enable log shipping on the Primary database

alter system set log_archive_dest_state_2 = enable scope = memory;

6. Enable managed recovery on standby database

alter database recover managed standby database using current logfile disconnect;

7. Check the the standby logs are being used by running following query :

set lines 155 pages 9999 col thread# for 9999990 col sequence# for 999999990 col grp for 990 col fnm for a50 head "File Name" col "Fisrt SCN Number" for 999999999999990 break on thread # skip 1 select a.thread# ,a.sequence# ,a.group# grp      , a.bytes/1024/1024 Size_MB      ,a.status      ,a.archived      ,a.first_change# "First SCN Number"      ,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time"    ,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"  from  v$standby_log a  order by 1,2,3,4  /

Should return the following :

THREAD#  SEQUENCE#  GRP    SIZE_MB STATUS     ARC Fisrt SCN Number First SCN Time              Last SCN Time -------- ---------- ---- ---------- ---------- --- ---------------- --------------------------- ---------------------------        1          0    4        100 UNASSIGNED NO                 0                   0    6        100 UNASSIGNED YES                0                   0    7        100 UNASSIGNED YES                0                7316    5        100 ACTIVE     YES        153517071 04-Feb-11 13:39:32          04-Feb-11 13:40:41