Steps to resize the 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
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