Steps on Instance 1(10.10.10.1) ORCL1 instance
select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS ARC STATUS
---------- ---------- ---------- --------------- ---------- --- ----------------
11 1 60656 300 3 YES INACTIVE
12 1 60657 300 3 NO CURRENT
13 1 60655 300 3 YES INACTIVE
14 1 60651 300 3 YES INACTIVE
15 1 60652 300 3 YES INACTIVE
16 1 60653 300 3 YES INACTIVE
17 1 60654 300 3 YES INACTIVE
7 rows selected.
select GROUP#,MEMBER from v$logfile where GROUP# in (11,12,13,14,15,16,17) order by 1;
GROUP# MEMBER
---------- ----------------------------------------
11 /data/redo01/ORCL/onlinelog/redo11a.dbf
11 /data/redo02/ORCL/onlinelog/redo11b.dbf
11 /data/redo03/ORCL/onlinelog/redo11c.dbf
12 /data/redo01/ORCL/onlinelog/redo12a.dbf
12 /data/redo02/ORCL/onlinelog/redo12b.dbf
12 /data/redo03/ORCL/onlinelog/redo12c.dbf
13 /data/redo02/ORCL/onlinelog/redo13b.dbf
13 /data/redo03/ORCL/onlinelog/redo13c.dbf
13 /data/redo01/ORCL/onlinelog/redo13a.dbf
14 /data/redo03/ORCL/onlinelog/redo14c.dbf
14 /data/redo01/ORCL/onlinelog/redo14a.dbf
14 /data/redo02/ORCL/onlinelog/redo14b.dbf
15 /data/redo03/ORCL/onlinelog/redo15c.dbf
15 /data/redo02/ORCL/onlinelog/redo15b.dbf
15 /data/redo01/ORCL/onlinelog/redo15a.dbf
16 /data/redo02/ORCL/onlinelog/redo16b.dbf
16 /data/redo03/ORCL/onlinelog/redo16c.dbf
16 /data/redo01/ORCL/onlinelog/redo16a.dbf
17 /data/redo01/ORCL/onlinelog/redo17a.dbf
17 /data/redo02/ORCL/onlinelog/redo17b.dbf
17 /data/redo03/ORCL/onlinelog/redo17c.dbf
21 rows selected.
(1) ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 1 ('/data/redo01/ORCL/onlinelog/redo01a.dbf','/data/redo02/ORCL/onlinelog/redo01b.dbf','/data/redo03/ORCL/onlinelog/redo01c.dbf') SIZE 1024M,
GROUP 2 ('/data/redo01/ORCL/onlinelog/redo02a.dbf','/data/redo02/ORCL/onlinelog/redo02b.dbf','/data/redo03/ORCL/onlinelog/redo02c.dbf') SIZE 1024M,
GROUP 3 ('/data/redo01/ORCL/onlinelog/redo03a.dbf','/data/redo02/ORCL/onlinelog/redo03b.dbf','/data/redo03/ORCL/onlinelog/redo03c.dbf') SIZE 1024M);
(2) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=11;
GROUP# ARC STATUS
---------- --- ----------------
11 YES INACTIVE
(2a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=11;
GROUP# ARC STATUS
---------- --- ----------------
11 YES INACTIVE
(3) alter database drop logfile group 11;
(4) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=12;
GROUP# ARC STATUS
---------- --- ----------------
12 YES INACTIVE
(4a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=12;
GROUP# ARC STATUS
---------- --- ----------------
12 YES INACTIVE
(5) alter database drop logfile group 12;
(6) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=13;
GROUP# ARC STATUS
---------- --- ----------------
13 YES INACTIVE
(6a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=13;
GROUP# ARC STATUS
---------- --- ----------------
13 YES INACTIVE
(7) alter database drop logfile group 13;
(8) ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 4 ('/data/redo01/ORCL/onlinelog/redo04a.dbf','/data/redo02/ORCL/onlinelog/redo04b.dbf','/data/redo03/ORCL/onlinelog/redo04c.dbf') SIZE 1024m;
(9) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=14;
GROUP# ARC STATUS
---------- --- ----------------
14 YES INACTIVE
(9a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=14;
GROUP# ARC STATUS
---------- --- ----------------
14 YES INACTIVE
(10) alter database drop logfile group 14;
(11) ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 ('/data/redo01/ORCL/onlinelog/redo05a.dbf','/data/redo02/ORCL/onlinelog/redo05b.dbf','/data/redo03/ORCL/onlinelog/redo05c.dbf') SIZE 1024m;
(12) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=15;
GROUP# ARC STATUS
---------- --- ----------------
15 YES INACTIVE
(12a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=15;
GROUP# ARC STATUS
---------- --- ----------------
15 YES INACTIVE
(13) alter database drop logfile group 15;
(14) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=16;
GROUP# ARC STATUS
---------- --- ----------------
16 YES INACTIVE
(14a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=16;
GROUP# ARC STATUS
---------- --- ----------------
16 YES INACTIVE
(15) alter database drop logfile group 16;
(16) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=17;
GROUP# ARC STATUS
---------- --- ----------------
17 YES INACTIVE
(16a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=17;
GROUP# ARC STATUS
---------- --- ----------------
17 YES INACTIVE
(17) alter database drop logfile group 17;
Steps on Instance 2(10.10.10.2) ORCL2 Instance
select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=2;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS ARC STATUS
---------- ---------- ---------- --------------- ---------- --- ----------------
21 2 53168 300 3 YES INACTIVE
22 2 53170 300 3 YES ACTIVE
23 2 53169 300 3 YES INACTIVE
24 2 53171 300 3 NO CURRENT
25 2 53165 300 3 YES INACTIVE
26 2 53166 300 3 YES INACTIVE
27 2 53167 300 3 YES INACTIVE
7 rows selected.
select GROUP#,MEMBER from v$logfile where GROUP# not in (11,12,13,14,15,16,17) order by 1;
GROUP# MEMBER
---------- ----------------------------------------
21 /data/redo04/ORCL/onlinelog/redo21a.dbf
21 /data/redo05/ORCL/onlinelog/redo21b.dbf
21 /data/redo06/ORCL/onlinelog/redo21c.dbf
22 /data/redo04/ORCL/onlinelog/redo22a.dbf
22 /data/redo05/ORCL/onlinelog/redo22b.dbf
22 /data/redo06/ORCL/onlinelog/redo22c.dbf
23 /data/redo04/ORCL/onlinelog/redo23a.dbf
23 /data/redo05/ORCL/onlinelog/redo23b.dbf
23 /data/redo06/ORCL/onlinelog/redo23c.dbf
24 /data/redo04/ORCL/onlinelog/redo24a.dbf
24 /data/redo05/ORCL/onlinelog/redo24b.dbf
24 /data/redo06/ORCL/onlinelog/redo24c.dbf
25 /data/redo04/ORCL/onlinelog/redo25a.dbf
25 /data/redo05/ORCL/onlinelog/redo25b.dbf
25 /data/redo06/ORCL/onlinelog/redo25c.dbf
26 /data/redo04/ORCL/onlinelog/redo26a.dbf
26 /data/redo05/ORCL/onlinelog/redo26b.dbf
26 /data/redo06/ORCL/onlinelog/redo26c.dbf
27 /data/redo04/ORCL/onlinelog/redo27a.dbf
27 /data/redo05/ORCL/onlinelog/redo27b.dbf
27 /data/redo06/ORCL/onlinelog/redo27c.dbf
21 rows selected.
(18) ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 6 ('/data/redo04/ORCL/onlinelog/redo06a.dbf','/data/redo05/ORCL/onlinelog/redo06b.dbf','/data/redo06/ORCL/onlinelog/redo06c.dbf') SIZE 1024M,
GROUP 7 ('/data/redo04/ORCL/onlinelog/redo07a.dbf','/data/redo05/ORCL/onlinelog/redo07b.dbf','/data/redo06/ORCL/onlinelog/redo07c.dbf') SIZE 1024M,
GROUP 8 ('/data/redo04/ORCL/onlinelog/redo08a.dbf','/data/redo05/ORCL/onlinelog/redo08b.dbf','/data/redo06/ORCL/onlinelog/redo08c.dbf') SIZE 1024M);
(19) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=21;
GROUP# ARC STATUS
--------------- ---------- --- ----------------
21 YES INACTIVE
(19a) If not above Result than run
alter system switch logfile ;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=21;
GROUP# ARC STATUS
--------------- ---------- --- ----------------
21 YES INACTIVE
(20) alter database drop logfile group 21;
(21) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=22;
GROUP# ARC STATUS
--------------- ---------- --- ----------------
22 YES INACTIVE
(21a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=22;
GROUP# ARC STATUS
--------------- ---------- --- ----------------
22 YES INACTIVE
(22) alter database drop logfile group 22;
(23) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=23;
GROUP# ARC STATUS
---------------- ---------- --- ----------------
23 YES INACTIVE
(23a) If not above Result than run
alter system switch logfile ;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=23;
GROUP# ARC STATUS
--------------- ---------- --- ----------------
23 YES INACTIVE
(24) alter database drop logfile group 23;
(25) ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 9 ('/data/redo04/ORCL/onlinelog/redo09a.dbf','/data/redo05/ORCL/onlinelog/redo09b.dbf','/data/redo06/ORCL/onlinelog/redo09c.dbf') SIZE 1024M;
(26) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=24;
GROUP# ARC STATUS
---------- --- ----------------
24 YES INACTIVE
(26a) If not above Result than run
alter system switch logfile 24;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=24;
GROUP# ARC STATUS
---------- --- ----------------
24 YES INACTIVE
(27) alter database drop logfile group 24;
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 10 ('/data/redo04/ORCL/onlinelog/redo10a.dbf','/data/redo05/ORCL/onlinelog/redo10b.dbf','/data/redo06/ORCL/onlinelog/redo10c.dbf') SIZE 1024M;
(28) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=25;
GROUP# ARC STATUS
---------- --- ----------------
25 YES INACTIVE
(28a) If not above Result than run
alter system switch logfile ;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=25;
GROUP# ARC STATUS
---------- --- ----------------
25 YES INACTIVE
(29) alter database drop logfile group 25;
(30) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=26;
GROUP# ARC STATUS
---------- --- ----------------
26 YES INACTIVE
(30a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=26;
GROUP# ARC STATUS
---------- --- ----------------
26 YES INACTIVE
(31) alter database drop logfile group 26;
(32) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=27;
GROUP# ARC STATUS
---------- --- ----------------
27 YES INACTIVE
(31a) If not above Result than run
alter system switch logfile;
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=27;
GROUP# ARC STATUS
---------- --- ----------------
27 YES INACTIVE
(32) alter database drop logfile group 27;
Note:- Find Out the Output and Confirm with Planed Activity.
For Instance 1
select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;
select GROUP#,MEMBER from v$logfile where GROUP# in (1,2,3,4,5) order by 1;
For Instance 2
select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;
select GROUP#,MEMBER from v$logfile where GROUP# not in (1,2,3,4,5) order by 1;
No comments:
Post a Comment