控制文件、系统数据文件损坏（异常关闭），使用TRACE RESETLOGS控制文件恢复

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> host cp /u01/app/oracle/oradata/NEWAY/system01.dbf /u01/app/oracle/oradata/NEWAY/system01.bak

SQL> create table t1(a int);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to trace as '/u02/backup_files/ctl.sql' reuse;

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> host cp /u01/app/oracle/oradata/NEWAY/system01.bak /u01/app/oracle/oradata/NEWAY/system01.dbf

SQL> host vi /u02/backup_files/ctl.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NEWAY" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/oradata/NEWAY/redo01.log',
    '/u01/app/oracle/oradata/redo02.log',
    '/u01/app/oracle/oradata/redo01.log'
  ) SIZE 50M,
  GROUP 2 (
    '/u01/app/oracle/oradata/NEWAY/redo02.log',
    '/u01/app/oracle/oradata/redo002.log',
    '/u01/app/oracle/oradata/redo003.log'
  ) SIZE 50M,
  GROUP 3 (
    '/u01/app/oracle/oradata/NEWAY/redo03.log',
    '/u01/app/oracle/oradata/redo03.log'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/NEWAY/system01.dbf',
  '/u01/app/oracle/oradata/NEWAY/undotbs01.dbf',
  '/u01/app/oracle/oradata/NEWAY/sysaux01.dbf',
  '/u01/app/oracle/oradata/NEWAY/users01.dbf',
  '/u01/testing.dbf',
  '/u01/testing02.dbf',
  '/tmp/testing_lmt.dbf',
  '/u01/app/oracle/oradata/example01.dbf'
CHARACTER SET ZHS16GBK
;
SQL> @/u02/backup_files/ctl.sql
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2083368 bytes
Variable Size             146802136 bytes
Database Buffers          130023424 bytes
Redo Buffers                6303744 bytes

Control file created.

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

    GROUP#  SEQUENCE# STATUS           ARC FIRST_CHANGE#
---------- ---------- ---------------- --- -------------
         1          0 UNUSED           YES             0
         3          0 CURRENT          YES             0
         2          0 UNUSED           YES             0

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ------------------------------
         1 SYSTEM             1417949 /u01/app/oracle/oradata/NEWAY/
                                      system01.dbf

         2 RECOVER            1417697 /u01/app/oracle/oradata/NEWAY/
                                      undotbs01.dbf

         3 RECOVER            1417697 /u01/app/oracle/oradata/NEWAY/
                                      sysaux01.dbf

         4 RECOVER            1417697 /u01/app/oracle/oradata/NEWAY/
                                      users01.dbf

     FILE# STATUS  CHECKPOINT_CHANGE# NAME
---------- ------- ------------------ ------------------------------

         5 RECOVER            1417697 /u01/testing.dbf
         6 RECOVER            1417697 /u01/testing02.dbf
         7 RECOVER            1417697 /tmp/testing_lmt.dbf
         8 RECOVER            1417697 /u01/app/oracle/oradata/exampl
                                      e01.dbf

8 rows selected.

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# REC FUZ
---------- ------------------ --- ---
         1            1417949     YES
         2            1417697     YES
         3            1417697     YES
         4            1417697     YES
         5            1417697     YES
         6            1417697     YES
         7            1417697     YES
         8            1417697     YES

8 rows selected.

SQL> select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8193 1417949                  66
         2          4 1417697                  66
         3          4 1417697                  66
         4          4 1417697                  66
         5          4 1417697                  66
         6          4 1417697                  66
         7          4 1417697                  66
         8          4 1417697                  66

8 rows selected.

SQL> recover database using backup controlfile
ORA-00279: change 1418091 generated at 05/30/2010 00:53:31 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/archive1_67_719999089.dbf
ORA-00280: change 1418091 for thread 1 is in sequence #67

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/NEWAY/redo02.log
Log applied.
Media recovery complete.

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

    GROUP#  SEQUENCE# STATUS           ARC FIRST_CHANGE#
---------- ---------- ---------------- --- -------------
         1          0 UNUSED           YES             0
         3          0 UNUSED           YES             0
         2          0 CURRENT         YES             0

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
NAME
--------------------------------------------------------------------------------
         1 SYSTEM             1418610
/u01/app/oracle/oradata/NEWAY/system01.dbf

         2 ONLINE             1418610
/u01/app/oracle/oradata/NEWAY/undotbs01.dbf

         3 ONLINE             1418610
/u01/app/oracle/oradata/NEWAY/sysaux01.dbf

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
NAME
--------------------------------------------------------------------------------
         4 ONLINE             1418610
/u01/app/oracle/oradata/NEWAY/users01.dbf

         5 ONLINE             1418610
/u01/testing.dbf

         6 ONLINE             1418610
/u01/testing02.dbf

     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
NAME
--------------------------------------------------------------------------------
         7 ONLINE             1418610
/tmp/testing_lmt.dbf

         8 ONLINE             1418610
/u01/app/oracle/oradata/example01.dbf

8 rows selected.

SQL> select FILE#,checkpoint_change#,recover, fuzzy from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# REC FUZ
---------- ------------------ --- ---
         1            1418610     NO
         2            1418610     NO
         3            1418610     NO
         4            1418610     NO
         5            1418610     NO
         6            1418610     NO
         7            1418610     NO
         8            1418610     NO

8 rows selected.

SQL>  select checkpoint_change#,CONTROLFILE_CHANGE#,resetlogs_change# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# RESETLOGS_CHANGE#
------------------ ------------------- -----------------
           1418286             1418610           1418285

SQL>  select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

FILENUMBER     STATUS SCN                SEQUENCE
---------- ---------- ---------------- ----------
         1       8192 1418610                   1
         2          0 1418610                   1
         3          0 1418610                   1
         4          0 1418610                   1
         5          0 1418610                   1
         6          0 1418610                   1
         7          0 1418610                   1
         8          0 1418610                   1

8 rows selected.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE            1417949 30-MAY-10
         2 NOT ACTIVE            1375680 29-MAY-10
         3 NOT ACTIVE                  0
         4 NOT ACTIVE                  0
         5 NOT ACTIVE             550786 26-MAY-10
         6 NOT ACTIVE             550786 26-MAY-10
         7 NOT ACTIVE                  0
         8 NOT ACTIVE            1370592 29-MAY-10

8 rows selected.

SQL> alter database open resetlogs;

Database altered.

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

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