未归档的活动在线日志损坏，正常修复

SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 YES        964724         26 INACTIVE
         2   52428800 NO         965120         27 CURRENT
         3   52428800 YES        964714         25 INACTIVE

SQL> truncate table t1;

Table truncated.

insert into t1
      select dbms_flashback.get_system_change_number
        from (select rownum from dba_objects where rownum <= 1000) a,
  4               (select rownum from dba_objects where rownum <= 1000) b;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 YES        964724         26 INACTIVE
         2   52428800 NO         965120         27 CURRENT
         3   52428800 YES        964714         25 INACTIVE

insert into t1
      select dbms_flashback.get_system_change_number
        from (select rownum from dba_objects where rownum <= 1000) a,
  4               (select rownum from dba_objects where rownum <= 1000) b;

1000000 rows created.

SQL> SQL>
SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 YES        964724         26 INACTIVE
         2   52428800 NO         965120         27 CURRENT
         3   52428800 YES        964714         25 INACTIVE

insert into t1
      select dbms_flashback.get_system_change_number
        from (select rownum from dba_objects where rownum <= 1000) a,
  4               (select rownum from dba_objects where rownum <= 1000) b;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 YES        964724         26 INACTIVE
         2   52428800 NO         965120         27 ACTIVE
         3   52428800 NO         967222         28 CURRENT

SQL> select min(scn) ,max(scn) from t1;

  MIN(SCN)   MAX(SCN)
---------- ----------
    965736     967293                      说明跨越了2 3日志文件组

SQL> select GROUP#,substr(member,1,60) from v$logfile;

    GROUP# SUBSTR(MEMBER,1,60)
---------- ------------------------------------------------------------
         3 /u01/app/oracle/oradata/shujukuai/redo03.log
         2 /u01/app/oracle/oradata/shujukuai/redo02.log     破坏她
         1 /u01/app/oracle/oradata/shujukuai/redo01.log

SQL> host rm -rf /u01/app/oracle/oradata/shujukuai/redo02.log

SQL> host ls /u01/app/oracle/oradata/shujukuai/
control01.ctl  control03.ctl  redo02.bak  sysaux01.dbf  temp01.dbf     undotbs01.dbf
control02.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.bak  users01.dbf

SQL> select min(scn) ,max(scn) from t1;

  MIN(SCN)   MAX(SCN)
---------- ----------
    965736     967293

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;                 

System altered.             晕，10G下居然可以切换

SQL> startup force
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             138413528 bytes
Database Buffers          138412032 bytes
Redo Buffers                6303744 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:
'/u01/app/oracle/oradata/shujukuai/redo02.log'

SQL> SELECT GROUP#,BYTES,ARCHIVED,FIRST_CHANGE#,SEQUENCE#,STATUS FROM V$LOG;

    GROUP#      BYTES ARC FIRST_CHANGE#  SEQUENCE# STATUS
---------- ---------- --- ------------- ---------- ----------------
         1   52428800 NO         967580         29 CURRENT
         3   52428800 NO         967222         28 INACTIVE
         2   52428800 NO         965120         27 INACTIVE     晕，怎么这里的状态也不一样，9I下不是UNUSED么

SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance shujukuai (thread 1) needs to be archived
ORA-00312: online log 2 thread 1:
'/u01/app/oracle/oradata/shujukuai/redo02.log'

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL> select min(scn) ,max(scn) from t1;

  MIN(SCN)   MAX(SCN)
---------- ----------
    965736     967293

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-667991/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-667991/