how to resize and multiplex redo group members under ASM/RAC
| Topic ID: 3374 | |
| Created By: | 2008-OCT-08 21:50:59 [Vitaliy] |
| Updated By: | 2012-JUL-27 20:16:46 [Alankendall] |
| Status: | Open |
| Severity: | Normal |
| Read Only: | No |
|
9544
2008-OCT-08 21:50:59
|
||||
|
##
## Objective
##
In this example I have two ASM disk groups for redo logs:
REDOA
REDOB
There are FOUR instances, each instance will have TWO redo groups
with TWO members each, one on REDOA the other on REDOB.
Originally this database was created with a single REDO member in
REDOA disk group sized 50mb -- my goal here is to:
a) "resize" it to 256mb
b) multiplex to REDOB
##
## Procedure
##
sqlplus /nolog
connect / as sysdba
SQL> col member format a35
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ----------------------------------- ---
1 ONLINE +REDOA/dbname/redo01.log NO
2 STALE ONLINE +REDOA/dbname/redo02.log NO
7 ONLINE +REDOA/dbname/redo07.log NO
8 STALE ONLINE +REDOA/dbname/redo08.log NO
5 STALE ONLINE +REDOA/dbname/redo05.log NO
6 ONLINE +REDOA/dbname/redo06.log NO
3 ONLINE +REDOA/dbname/redo03.log NO
4 ONLINE +REDOA/dbname/redo04.log NO
8 rows selected.
SQL> set lines 132
SQL> set trims on
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 53 52428800 1 NO CURRENT 2274385 08-OCT-08
2 1 52 52428800 1 NO INACTIVE 2114717 07-OCT-08
3 2 27 52428800 1 NO CURRENT 2206475 08-OCT-08
4 2 26 52428800 1 NO INACTIVE 2030730 07-OCT-08
5 3 17 52428800 1 NO INACTIVE 1780062 06-OCT-08
6 3 18 52428800 1 NO CURRENT 2051445 07-OCT-08
7 4 21 52428800 1 NO CURRENT 2072021 07-OCT-08
8 4 20 52428800 1 NO INACTIVE 1932027 07-OCT-08
8 rows selected.
-- first create temporary redo-groups
-- so that we can delete GROUP 1-8 and recreate them
-- with a bigger 256m size ...
--
-- NOTE:
-- I didn't really have to create these temp groups with
-- 2 members each ... one member each would suffice
--
ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 10 ('+REDOA/DBNAME/redo01-tmp.log','+REDOB/DBNAME/redo01-tmp.log') SIZE 10m,
GROUP 20 ('+REDOA/DBNAME/redo02-tmp.log','+REDOB/DBNAME/redo02-tmp.log') SIZE 10m;
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 30 ('+REDOA/DBNAME/redo03-tmp.log','+REDOB/DBNAME/redo03-tmp.log') SIZE 10m,
GROUP 40 ('+REDOA/DBNAME/redo04-tmp.log','+REDOB/DBNAME/redo04-tmp.log') SIZE 10m;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 50 ('+REDOA/DBNAME/redo05-tmp.log','+REDOB/DBNAME/redo05-tmp.log') SIZE 10m,
GROUP 60 ('+REDOA/DBNAME/redo06-tmp.log','+REDOB/DBNAME/redo06-tmp.log') SIZE 10m;
ALTER DATABASE ADD LOGFILE THREAD 4
GROUP 70 ('+REDOA/DBNAME/redo07-tmp.log','+REDOB/DBNAME/redo07-tmp.log') SIZE 10m,
GROUP 80 ('+REDOA/DBNAME/redo08-tmp.log','+REDOB/DBNAME/redo08-tmp.log') SIZE 10m;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 53 52428800 1 NO CURRENT 2274385 08-OCT-08
2 1 52 52428800 1 NO INACTIVE 2114717 07-OCT-08
3 2 27 52428800 1 NO CURRENT 2206475 08-OCT-08
4 2 26 52428800 1 NO INACTIVE 2030730 07-OCT-08
5 3 17 52428800 1 NO INACTIVE 1780062 06-OCT-08
6 3 18 52428800 1 NO CURRENT 2051445 07-OCT-08
7 4 21 52428800 1 NO CURRENT 2072021 07-OCT-08
8 4 20 52428800 1 NO INACTIVE 1932027 07-OCT-08
10 1 0 10485760 2 YES UNUSED 0
20 1 0 10485760 2 YES UNUSED 0
30 2 0 10485760 2 YES UNUSED 0
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
40 2 0 10485760 2 YES UNUSED 0
50 3 0 10485760 2 YES UNUSED 0
60 3 0 10485760 2 YES UNUSED 0
70 4 0 10485760 2 YES UNUSED 0
80 4 0 10485760 2 YES UNUSED 0
16 rows selected.
SQL>
-- now run "alter system switch logfile;" on all instances
-- untill all of the GROUP 1-8 become "INACTIVE"
-- like shown below:
--
SQL> r
1* select * from v$log
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 61 52428800 1 NO INACTIVE 2288120 08-OCT-08 <-- should be "INACTIVE"
2 1 60 52428800 1 NO INACTIVE 2288098 08-OCT-08 <-- should be "INACTIVE"
3 2 31 52428800 1 NO INACTIVE 2288234 08-OCT-08 <-- should be "INACTIVE"
4 2 30 52428800 1 NO INACTIVE 2288221 08-OCT-08 <-- should be "INACTIVE"
5 3 21 52428800 1 NO INACTIVE 2288289 08-OCT-08 <-- should be "INACTIVE"
6 3 22 52428800 1 NO INACTIVE 2288294 08-OCT-08 <-- should be "INACTIVE"
7 4 25 52428800 1 NO INACTIVE 2288317 08-OCT-08 <-- should be "INACTIVE"
8 4 24 52428800 1 NO INACTIVE 2288311 08-OCT-08 <-- should be "INACTIVE"
10 1 62 10485760 2 NO CURRENT 2288123 08-OCT-08
20 1 59 10485760 2 NO INACTIVE 2288084 08-OCT-08
30 2 32 10485760 2 NO CURRENT 2288243 08-OCT-08
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
40 2 29 10485760 2 NO INACTIVE 2288217 08-OCT-08
50 3 23 10485760 2 NO CURRENT 2288296 08-OCT-08
60 3 20 10485760 2 NO INACTIVE 2288287 08-OCT-08
70 4 26 10485760 2 NO ACTIVE 2288319 08-OCT-08
80 4 27 10485760 2 NO CURRENT 2288322 08-OCT-08
16 rows selected.
-- now drop GROUP 1-8
--
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;
alter database drop logfile group 8;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
10 1 62 10485760 2 NO CURRENT 2288123 08-OCT-08
20 1 59 10485760 2 NO INACTIVE 2288084 08-OCT-08
30 2 32 10485760 2 NO CURRENT 2288243 08-OCT-08
40 2 29 10485760 2 NO INACTIVE 2288217 08-OCT-08
50 3 23 10485760 2 NO CURRENT 2288296 08-OCT-08
60 3 20 10485760 2 NO INACTIVE 2288287 08-OCT-08
70 4 26 10485760 2 NO INACTIVE 2288319 08-OCT-08
80 4 27 10485760 2 NO CURRENT 2288322 08-OCT-08
8 rows selected.
SQL>
## remove the actuall files (GROUP 1-8) from the ASM
##
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM2
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo01.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo02.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo07.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo08.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo05.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo06.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo03.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/dbname/redo04.log';
exit
## verify files were removed
##
asmcmd
ASMCMD> cd REDOA
ASMCMD> find . *
+REDOA/DBNAME/
+REDOA/DBNAME/CONTROLFILE/
+REDOA/DBNAME/CONTROLFILE/Current.256.666981619
+REDOA/DBNAME/ONLINELOG/
+REDOA/DBNAME/ONLINELOG/group_10.271.667595443
+REDOA/DBNAME/ONLINELOG/group_20.272.667595443
+REDOA/DBNAME/ONLINELOG/group_30.265.667595411
+REDOA/DBNAME/ONLINELOG/group_40.266.667595411
+REDOA/DBNAME/ONLINELOG/group_50.267.667595411
+REDOA/DBNAME/ONLINELOG/group_60.268.667595411
+REDOA/DBNAME/ONLINELOG/group_70.269.667595413
+REDOA/DBNAME/ONLINELOG/group_80.270.667595413
+REDOA/DBNAME/control01.ctl
+REDOA/DBNAME/redo01-tmp.log
+REDOA/DBNAME/redo02-tmp.log
+REDOA/DBNAME/redo03-tmp.log
+REDOA/DBNAME/redo04-tmp.log
+REDOA/DBNAME/redo05-tmp.log
+REDOA/DBNAME/redo06-tmp.log
+REDOA/DBNAME/redo07-tmp.log
+REDOA/DBNAME/redo08-tmp.log
ASMCMD> exit
-- now re-create it with "dual" members and bigger size
--
ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 1 ('+REDOA/DBNAME/redo01.log','+REDOB/DBNAME/redo01.log') SIZE 256000K,
GROUP 2 ('+REDOA/DBNAME/redo02.log','+REDOB/DBNAME/redo02.log') SIZE 256000K;
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 ('+REDOA/DBNAME/redo03.log','+REDOB/DBNAME/redo03.log') SIZE 256000K,
GROUP 4 ('+REDOA/DBNAME/redo04.log','+REDOB/DBNAME/redo04.log') SIZE 256000K;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 5 ('+REDOA/DBNAME/redo05.log','+REDOB/DBNAME/redo05.log') SIZE 256000K,
GROUP 6 ('+REDOA/DBNAME/redo06.log','+REDOB/DBNAME/redo06.log') SIZE 256000K;
ALTER DATABASE ADD LOGFILE THREAD 4
GROUP 7 ('+REDOA/DBNAME/redo07.log','+REDOB/DBNAME/redo07.log') SIZE 256000K,
GROUP 8 ('+REDOA/DBNAME/redo08.log','+REDOB/DBNAME/redo08.log') SIZE 256000K;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 262144000 2 YES UNUSED 0
2 1 0 262144000 2 YES UNUSED 0
3 2 0 262144000 2 YES UNUSED 0
4 2 0 262144000 2 YES UNUSED 0
5 3 0 262144000 2 YES UNUSED 0
6 3 0 262144000 2 YES UNUSED 0
7 4 0 262144000 2 YES UNUSED 0
8 4 0 262144000 2 YES UNUSED 0
10 1 62 10485760 2 NO CURRENT 2288123 08-OCT-08
20 1 59 10485760 2 NO INACTIVE 2288084 08-OCT-08
30 2 32 10485760 2 NO CURRENT 2288243 08-OCT-08
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
40 2 29 10485760 2 NO INACTIVE 2288217 08-OCT-08
50 3 23 10485760 2 NO CURRENT 2288296 08-OCT-08
60 3 20 10485760 2 NO INACTIVE 2288287 08-OCT-08
70 4 26 10485760 2 NO INACTIVE 2288319 08-OCT-08
80 4 27 10485760 2 NO CURRENT 2288322 08-OCT-08
16 rows selected.
-- now run "alter system switch logfile;" on all instances
-- untill all of the GROUP 10-80 become "INACTIVE"
-- like shown below:
--
1* select * from v$log
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 67 262144000 2 NO CURRENT 2289166 08-OCT-08
2 1 64 262144000 2 NO INACTIVE 2289154 08-OCT-08
3 2 37 262144000 2 NO INACTIVE 2289194 08-OCT-08
4 2 38 262144000 2 NO CURRENT 2289199 08-OCT-08
5 3 28 262144000 2 NO INACTIVE 2289258 08-OCT-08
6 3 29 262144000 2 NO CURRENT 2289266 08-OCT-08
7 4 32 262144000 2 NO CURRENT 2289332 08-OCT-08
8 4 29 262144000 2 NO INACTIVE 2289315 08-OCT-08
10 1 66 10485760 2 NO INACTIVE 2289163 08-OCT-08
20 1 65 10485760 2 NO INACTIVE 2289157 08-OCT-08
30 2 36 10485760 2 NO INACTIVE 2289192 08-OCT-08
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
40 2 35 10485760 2 NO INACTIVE 2289189 08-OCT-08
50 3 27 10485760 2 NO INACTIVE 2289254 08-OCT-08
60 3 26 10485760 2 NO INACTIVE 2289243 08-OCT-08
70 4 30 10485760 2 NO INACTIVE 2289322 08-OCT-08
80 4 31 10485760 2 NO INACTIVE 2289329 08-OCT-08
16 rows selected.
-- now drop GROUP 10-80
--
alter database drop logfile group 10;
alter database drop logfile group 20;
alter database drop logfile group 30;
alter database drop logfile group 40;
alter database drop logfile group 50;
alter database drop logfile group 60;
alter database drop logfile group 70;
alter database drop logfile group 80;
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 67 262144000 2 NO CURRENT 2289166 08-OCT-08
2 1 64 262144000 2 NO INACTIVE 2289154 08-OCT-08
3 2 37 262144000 2 NO INACTIVE 2289194 08-OCT-08
4 2 38 262144000 2 NO CURRENT 2289199 08-OCT-08
5 3 28 262144000 2 NO INACTIVE 2289258 08-OCT-08
6 3 29 262144000 2 NO CURRENT 2289266 08-OCT-08
7 4 32 262144000 2 NO CURRENT 2289332 08-OCT-08
8 4 29 262144000 2 NO INACTIVE 2289315 08-OCT-08
8 rows selected.
SQL>
## and finally remove the actuall files (GROUP 10-80) from the ASM
##
##
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/asm
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=+ASM2
sqlplus /nolog
connect / as sysdba
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo01-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo02-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo03-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo04-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo05-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo06-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo07-tmp.log';
ALTER DISKGROUP REDOA DROP FILE '+REDOA/DBNAME/redo08-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo01-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo02-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo03-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo04-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo05-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo06-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo07-tmp.log';
ALTER DISKGROUP REDOB DROP FILE '+REDOB/DBNAME/redo08-tmp.log';
exit
## verify files were removed
##
asmcmd
ASMCMD> cd REDOA
ASMCMD> find . *
+REDOA/DBNAME/
+REDOA/DBNAME/CONTROLFILE/
+REDOA/DBNAME/CONTROLFILE/Current.256.666981619
+REDOA/DBNAME/ONLINELOG/
+REDOA/DBNAME/ONLINELOG/group_1.264.667596567
+REDOA/DBNAME/ONLINELOG/group_2.263.667596569
+REDOA/DBNAME/ONLINELOG/group_3.262.667596571
+REDOA/DBNAME/ONLINELOG/group_4.261.667596573
+REDOA/DBNAME/ONLINELOG/group_5.260.667596575
+REDOA/DBNAME/ONLINELOG/group_6.259.667596577
+REDOA/DBNAME/ONLINELOG/group_7.258.667596579
+REDOA/DBNAME/ONLINELOG/group_8.257.667596581
+REDOA/DBNAME/control01.ctl
+REDOA/DBNAME/redo01.log
+REDOA/DBNAME/redo02.log
+REDOA/DBNAME/redo03.log
+REDOA/DBNAME/redo04.log
+REDOA/DBNAME/redo05.log
+REDOA/DBNAME/redo06.log
+REDOA/DBNAME/redo07.log
+REDOA/DBNAME/redo08.log
ASMCMD> cd +REDOB/
ASMCMD> pwd
+REDOB
ASMCMD> find . *
+REDOB/DBNAME/
+REDOB/DBNAME/CONTROLFILE/
+REDOB/DBNAME/CONTROLFILE/Current.256.666981619
+REDOB/DBNAME/ONLINELOG/
+REDOB/DBNAME/ONLINELOG/group_1.265.667596567
+REDOB/DBNAME/ONLINELOG/group_2.266.667596569
+REDOB/DBNAME/ONLINELOG/group_3.267.667596571
+REDOB/DBNAME/ONLINELOG/group_4.268.667596573
+REDOB/DBNAME/ONLINELOG/group_5.269.667596575
+REDOB/DBNAME/ONLINELOG/group_6.270.667596577
+REDOB/DBNAME/ONLINELOG/group_7.271.667596579
+REDOB/DBNAME/ONLINELOG/group_8.272.667596583
+REDOB/DBNAME/control02.ctl
+REDOB/DBNAME/redo01.log
+REDOB/DBNAME/redo02.log
+REDOB/DBNAME/redo03.log
+REDOB/DBNAME/redo04.log
+REDOB/DBNAME/redo05.log
+REDOB/DBNAME/redo06.log
+REDOB/DBNAME/redo07.log
+REDOB/DBNAME/redo08.log
ASMCMD> exit
[edited by: Vitaliy at 21:55 (CST) on Oct. 08, 2008]
|
15056
2012-MAY-26 03:15:05
|
||||
|
This is a very clear posting with no typo errors...just made my task so easy. Thanks a lot.
|
15215
2012-JUL-27 20:16:46
|
||||
|
I have a script that I use to generate the commands to run on the Database side for me.
ECSCDAS1S > @cr8_log
alter database drop logfile group 5;
alter database drop logfile group 2;
alter database drop logfile group 4;
alter database drop logfile group 6;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 '+CCDA_DATA_01/cscdas/redo01a.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 '+CCDA_DATA_01/cscdas/redo01b.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 '+CCDA_DATA_01/cscdas/redo02a.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 '+CCDA_DATA_01/cscdas/redo02b.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+CCDA_DATA_01/cscdas/redo05a.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 '+CCDA_DATA_01/cscdas/redo05b.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 '+CCDA_DATA_01/cscdas/redo03a.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 '+CCDA_DATA_01/cscdas/redo03b.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 '+CCDA_DATA_01/cscdas/redo04a.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 '+CCDA_DATA_01/cscdas/redo04b.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 '+CCDA_DATA_01/cscdas/redo06a.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 '+CCDA_DATA_01/cscdas/redo06b.log' size 256m reuse;
ALTER DATABASE ADD LOGFILE member '+CCDA_DATA_01/cscdas/redo01b.log' reuse to GROUP 1;
ALTER DATABASE ADD LOGFILE member '+CCDA_DATA_01/cscdas/redo02b.log' reuse to GROUP 2;
ALTER DATABASE ADD LOGFILE member '+CCDA_DATA_01/cscdas/redo03b.log' reuse to GROUP 3;
ALTER DATABASE ADD LOGFILE member '+CCDA_DATA_01/cscdas/redo04b.log' reuse to GROUP 4;
ALTER DATABASE ADD LOGFILE member '+CCDA_DATA_01/cscdas/redo05b.log' reuse to GROUP 5;
ALTER DATABASE ADD LOGFILE member '+CCDA_DATA_01/cscdas/redo06b.log' reuse to GROUP 6;
alter system switch logfile;
alter system checkpoint;
@cr8_log
The sql to "cr8_log.sql" looks like the following:
column meg format 99999999999
column status format a10
column member format a30
set wrap on
set trunc off
set heading off
set feedback off
set lines 120
set wrap off
alter system checkpoint;
spool cr8_log.lst
select 'alter database drop logfile group '||group#||';' from v$log
where status not in ('ACTIVE','CURRENT')
-- and instance_number = (select instance_number from v$instance)
;
select 'ALTER DATABASE ADD LOGFILE THREAD '||v$log.thread#
||' GROUP '||v$log.group#||' '''
||v$logfile.member||''' size '||v$log.bytes/1024/1024||'m reuse;'
from v$log,v$logfile
where v$log.group#=v$logfile.group#
union
select 'ALTER DATABASE ADD LOGFILE member '''||v$logfile.member
||''' reuse to GROUP '||v$log.group#||';'
from v$log,v$logfile
where v$log.group#=v$logfile.group#
and (
(upper(v$logfile.member) like '%2.LOG' or upper(v$logfile.member) like '%B.LOG')
and upper(v$logfile.member) not like '%MEMA%'
or upper(v$logfile.member) like '%MEMB%'
)
-- and members=2
;
column sort1 noprint
select 'alter system switch logfile;' nam,'1' sort1 from dual union
select 'alter system checkpoint;' nam,'2' sort1 from dual union
select '@cr8_log' nam,'3' sort1 from dual union
select '' nam,'4' sort1 from dual
order by sort1;
spool off
set feedback on
set heading on