查看REDO日志组
select group#,member from v$logfile;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------ ---
1 ONLINE /data1/dbstat/redo01.dbf NO
3 STALE ONLINE /data1/dbstat/redo3.log NO
2 ONLINE /data1/dbstat/redo2.log NO
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance dbstat (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/data1/dbstat/redo2.log'
切换日志组,当CURRENT变为非活动时(INACTIVE)将其删除:
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance dbstat (thread 1)
ORA-00312: online log 2 thread 1: '/data1/dbstat/redo2.log'
SQL> alter system switch logfile;
System altered.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 12 2147483648 1 NO ACTIVE 203704 2009-09-17 19:04:23
2 1 11 52428800 1 NO ACTIVE 203206 2009-09-17 18:56:08
3 1 13 2147483648 1 NO CURRENT 203710 2009-09-17 19:04:38
SQL> alter system checkpoint;
System altered.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 12 2147483648 1 NO INACTIVE 203704 2009-09-17 19:04:23
2 1 11 52428800 1 NO INACTIVE 203206 2009-09-17 18:56:08
3 1 13 2147483648 1 NO CURRENT 203710 2009-09-17 19:04:38
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('/data1/dbstat/redo02.dbf') size 2048M;
Database altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 12 2147483648 1 NO INACTIVE 203704 2009-09-17 19:04:23
2 1 0 2147483648 1 YES UNUSED 0
3 1 13 2147483648 1 NO CURRENT 203710 2009-09-17 19:04:38
增加REDO日志组
ALTER DATABASE ADD LOGFILE GROUP 5 (
'/u02/oradata/vids/redo05_1.log',
'/u02/oradata/vids/redo05_2.log'
) SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 6 (
'/u02/oradata/vids/redo06_1.log',
'/u02/oradata/vids/redo06_2.log'
) SIZE 200M;
ALTER DATABASE ADD LOGFILE GROUP 7 (
'/u02/oradata/vids/redo07_1.log',
'/u02/oradata/vids/redo07_2.log'
) SIZE 200M;