Scripts to resize standby redolog files

来源：互联网发布：易语言玫瑰花表白源码编辑：程序博客网时间：2024/06/05 19:21

I have already posted about an issue that required me to drop and recreate standby log files so I thought I would post the scripts I used.

Resize Standby Redo Logs

1. On primary defer log shipping (dynamic change)

altersystem setlog_archive_dest_state_2 = defer scope = memory;

2. On standby database cancel managed recovery

alterdatabase recover managed standby databasecancel;

3. Drop standby logs on standby database

ALTERDATABASE DROP STANDBY LOGFILE GROUP4;
 
ALTERDATABASE DROP STANDBY LOGFILE GROUP5;
 
ALTERDATABASE DROP STANDBY LOGFILE GROUP6;
 
ALTERDATABASE DROP STANDBY LOGFILE GROUP7;

4. Recreate the new Standby logs

alterdatabase add standby logfile THREAD 1 group4 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)')SIZE1000M;
 
alterdatabase add standby logfile THREAD 1 group5 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)')SIZE1000M;
 
alterdatabase add standby logfile THREAD 1 group6 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)')SIZE1000M;
 
alterdatabase add standby logfile THREAD 1 group7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)')SIZE1000M;

5. Enable log shipping on the Primary database

altersystem setlog_archive_dest_state_2 = enable scope = memory;

6. Enable managed recovery on standby database

alterdatabase recover managed standby databaseusing currentlogfile disconnect;

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

setlines 155 pages 9999
col thread# for9999990
colsequence#for999999990
col grp for990
col fnm fora50 head "File Name"
col"Fisrt SCN Number" for 999999999999990
breakonthread
# skip 1
selecta.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  orderby 1,2,3,4
 /

Should return the following :

THREAD# SEQUENCE#  GRP    SIZE_MB STATUS     ARC Fisrt SCN Number FirstSCN 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

0 0