SET PAGES 999 LINES 999;
COL INSTANCE_NAME FOR A10;
COL HOST_NAME FOR A20;
COL STARTUP_TIME FOR A30;
SET COLSEP |
SET UNDERLINE =
SELECT INSTANCE_NAME,HOST_NAME,NAME,OPEN_MODE,LOG_MODE,STATUS,TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STARTUP_TIME,VERSION
FROM V$DATABASE, V$INSTANCE;
INSTANCE_N|HOST_NAME |NAME |OPEN_MODE |LOG_MODE |STATUS |STARTUP_TIME |VERSION
==========|===============|=========|============|============|========|=====================|==========
bihar |oel7.malam.com |BIHAR |READ WRITE |ARCHIVELOG |OPEN |20-MAR-2022 22:26:58 |12.1.0.2.0
SET PAGES 999 LINES 999;
COL MEMBER FOR A80;
SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
3 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_3_jt3q9gpd_.log NO 0
3 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_3_jt3q9gsn_.log YES 0
2 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_2_jt3q9dvr_.log NO 0
2 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_2_jt3q9dys_.log YES 0
1 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_1_jt3q9blf_.log NO 0
1 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_1_jt3q9btr_.log YES 0
6 rows selected.
SQL> select group#, sequence#, bytes, members, status from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
1 25 52428800 2 CURRENT
2 23 52428800 2 INACTIVE
3 24 52428800 2 INACTIVE
SQL> alter database add logfile group 4
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo') size 10M; 2 3
Database altered.
SQL> alter database add logfile group 5
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo') size 10M; 2 3
Database altered.
SQL> alter database add logfile group 6
('/u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo',
'/u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo') size 10M; 2 3
Database altered.
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
3 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_3_jt3q9gpd_.log NO 0
3 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_3_jt3q9gsn_.log YES 0
2 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_2_jt3q9dvr_.log NO 0
2 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_2_jt3q9dys_.log YES 0
1 ONLINE /u01/app/oracle/oradata/BIHAR/onlinelog/o1_mf_1_jt3q9blf_.log NO 0
1 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/onlinelog/o1_mf_1_jt3q9btr_.log YES 0
4 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo NO 0
4 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo NO 0
5 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo NO 0
5 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo NO 0
6 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo NO 0
6 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo NO 0
12 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select group#, sequence#, bytes, members, status,ARCHIVED from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 31 52428800 2 INACTIVE YES
2 29 52428800 2 INACTIVE YES
3 30 52428800 2 INACTIVE YES
4 32 10485760 2 CURRENT NO
5 27 10485760 2 INACTIVE YES
6 28 10485760 2 INACTIVE YES
6 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
SQL> select group#, sequence#, bytes, members, status,ARCHIVED from v$log;
GROUP# SEQUENCE# BYTES MEMBERS STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
4 32 10485760 2 CURRENT NO
5 27 10485760 2 INACTIVE YES
6 28 10485760 2 INACTIVE YES
SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------------------------------------- --- ----------
4 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp4_a.redo NO 0
4 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp4_b.redo NO 0
5 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp5_a.redo NO 0
5 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp5_b.redo NO 0
6 ONLINE /u01/app/oracle/oradata/BIHAR/redo_bihar_grp6_a.redo NO 0
6 ONLINE /u01/app/oracle/fast_recovery_area/BIHAR/redo_bihar_grp6_b.redo NO 0
6 rows selected.
No comments:
Post a Comment