Oracle 日志文件和控制文件损坏的恢复

Oracle日志文件和控制文件损坏的恢复： 恢复步骤： 1.加入_allow_resetlogs_corruption=true，_corrupted_rollback_segments=tru

Oracle日志文件和控制文件损坏的恢复：
 
恢复步骤：
 
1.加入_allow_resetlogs_corruption=true，_corrupted_rollback_segments=true，_offline_rollback_segments=true，启动到nomount
 
2.利用控制文件的备份，，进行脚本重建，由于current状态redo损坏，所以要resetlogs重建
 
3.重建后通过alter database open resetlogs;打开数据库，这种情况会丢失在redo中相关事务的数据
 

 

备份控制文件并模拟故障：
 
SQL> set linesize 150
 
SQL> alter database backup controlfile to trace as '/home/oracle/ctl.ctl' reuse;
 
Database altered.
 
SQL> insert into tb1 values (1);
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL> select * from v$log;
 

 

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME
 
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
 
        1          1          1  536870912          1 YES INACTIVE            1.2269E+11 2013-05-03 16:08:03
 
        2          1          2  536870912          1 YES INACTIVE            1.2269E+11 2013-05-03 16:23:48
 
        3          1          3  536870912          1 NO  CURRENT            1.2269E+11 2013-05-11 07:26:45
 
        4          1          0  536870912          1 YES UNUSED                      0
 
        5          1          0  536870912          1 YES UNUSED                      0
 
SQL> shutdown abort;       
 
oracle@readerlogdb-> rm -f redo03.log
 
oracle@readerlogdb-> rm -f control01.ctl control02.ctl control03.ctl
 

 

通过加入相关参数启动到nomount状态：
 
SQL> conn /as sysdba
 
Connected to an idle instance.
 
SQL> startup;
 
ORACLE instance started.
 
Total System Global Area 5049942016 bytes
 
Fixed Size                  2090880 bytes
 
Variable Size            1375733888 bytes
 
Database Buffers        3657433088 bytes
 
Redo Buffers              14684160 bytes
 
ORA-00205: error in identifying control file, check alert log for more info
 

 

SQL> create pfile='/home/oracle/pfile.ora' from spfile;
 
在备份的pfile中加入以下三行：
 
*._allow_resetlogs_corruption=true
 
*._corrupted_rollback_segments=true
 
*._offline_rollback_segments=true
 

 

NORESETLOGS和RESETLOGS在此实验中的区别：
 
SQL>CREATE CONTROLFILE REUSE DATABASE "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG
 
    MAXLOGFILES 20
 
    MAXLOGMEMBERS 5
 
    MAXDATAFILES 1000
 
    MAXINSTANCES 8
 
    MAXLOGHISTORY 2337
 
LOGFILE
 
  GROUP 1 '/database/oradata/skyread/redo01.log'  SIZE 512M,
 
  GROUP 2 '/database/oradata/skyread/redo02.log'  SIZE 512M,
 
  GROUP 3 '/database/oradata/skyread/redo03.log'  SIZE 512M,
 
  GROUP 4 '/database/oradata/skyread/redo04.log'  SIZE 512M,
 
  GROUP 5 '/database/oradata/skyread/redo05.log'  SIZE 512M
 
DATAFILE
 
  '/database/oradata/skyread/system01.dbf',
 
  '/database/oradata/skyread/tbs_test.dbf',
 
  '/database/oradata/skyread/sysaux01.dbf',
 
  '/database/oradata/skyread/users01.dbf',
 
  '/database2/oradata/skyread/TBS_MRPMUSIC01.dbf',
 
  '/database/oradata/skyread/sf01.dbf',
 
  '/database2/oradata/skyread/undotbs02'
 
CHARACTER SET UTF8;