oracle 清理 redo log,清除失败的redolog

SQL>alter system switch logfile;

System altered.

SQL>alter system checkpoint;

System altered.

SQL>select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                   IS_

---------- ------- ------- ---------------------------------------- ---

3         ONLINE  D:\ORACLE\ORADATA\TEST\REDO03.LOG        NO

2 INVALID ONLINE  D:\ORACLE\ORADATA\TEST\REDO02_NEW.LOG    NO

1         ONLINE  D:\ORACLE\ORADATA\TEST\REDO01.LOG        NO

3         ONLINE  D:\ORACLE\ORADATA\TEST\REDO03_B.LOG      NO

1         ONLINE  D:\ORACLE\ORADATA\TEST\REDO01_B.LOG      NO

2         ONLINE  D:\ORACLE\ORADATA\TEST\REDO02_B.LOG      NO

6 rows selected.

SQL>select group#, sequence#, members, archived, status from v$log;

GROUP#  SEQUENCE#    MEMBERS ARC STATUS

---------- ---------- ---------- --- ----------------

1         69          2 YES INACTIVE

2         70          2 YES INACTIVE

3         71          2 NO  CURRENT

清除包含失败的logfile的log组2

SQL> alter database test clear logfile group 2;

Database altered.

SQL>select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                   IS_

---------- ------- ------- ---------------------------------------- ---

3         ONLINE  D:\ORACLE\ORADATA\TEST\REDO03.LOG        NO

2         ONLINE  D:\ORACLE\ORADATA\TEST\REDO02_NEW.LOG    NO

1         ONLINE  D:\ORACLE\ORADATA\TEST\REDO01.LOG        NO

3         ONLINE  D:\ORACLE\ORADATA\TEST\REDO03_B.LOG      NO

1         ONLINE  D:\ORACLE\ORADATA\TEST\REDO01_B.LOG      NO

2         ONLINE  D:\ORACLE\ORADATA\TEST\REDO02_B.LOG      NO

6 rows selected.

SQL>select group#, sequence#, members, archived, status from v$log;

GROUP#  SEQUENCE#    MEMBERS ARC STATUS

---------- ---------- ---------- --- ----------------

1         69          2 YES INACTIVE

2          0          2 YES UNUSED

3         71          2 NO  CURRENT

SQL>alter system switch logfile;

System altered.

SQL>alter system switch logfile;

System altered.

SQL>alter system checkpoint;

System altered.

SQL>select group#, sequence#, members, archived, status from v$log;

GROUP#  SEQUENCE#    MEMBERS ARC STATUS

---------- ---------- ---------- --- ----------------

1         73          2 NO  CURRENT

2         72          2 YES INACTIVE

3         71          2 YES INACTIVE

SQL>alter system switch logfile;

System altered.

SQL>alter system checkpoint;

System altered.

SQL>select group#, sequence#, members, archived, status from v$log;

GROUP#  SEQUENCE#    MEMBERS ARC STATUS

---------- ---------- ---------- --- ----------------

1         73          2 YES INACTIVE

2         72          2 YES INACTIVE

3         74          2 NO  CURRENT

SQL>select * from v$logfile;

GROUP# STATUS  TYPE    MEMBER                                   IS_

---------- ------- ------- ---------------------------------------- ---

3         ONLINE  D:\ORACLE\ORADATA\TEST\REDO03.LOG        NO

2         ONLINE  D:\ORACLE\ORADATA\TEST\REDO02_NEW.LOG    NO

1         ONLINE  D:\ORACLE\ORADATA\TEST\REDO01.LOG        NO

3         ONLINE  D:\ORACLE\ORADATA\TEST\REDO03_B.LOG      NO

1         ONLINE  D:\ORACLE\ORADATA\TEST\REDO01_B.LOG      NO

2         ONLINE  D:\ORACLE\ORADATA\TEST\REDO02_B.LOG      NO

6 rows selected.

SQL>spool off