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

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;
  CREATE CONTROLFILE REUSE DATABASE "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01565: error in identifying file "/database/oradata/skyread/redo03.log"

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3
  SQL>CREATE CONTROLFILE REUSE DATABASE "SKYREAD" RESETLOGS 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;
  重建控制文件后，重置日志打开数据库，但是丢失之前插入的数据：

SQL> alter database open resetlogs;

Database altered.

SQL> select * from tb1;

此时tb1的表中插入的1不存在更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12oracle Log Buffer内部机制以及常见等待事件Oracle用老的控制文件备份恢复新建的数据文件相关资讯 Oracle日志文件

Oracle手动切换日志文件和清空日志（03/13/2015 18:41:05）
Oracle删除日志文件（03/13/2015 18:37:56）
Oracle 联机日志文件损坏的几种场（05/19/2014 19:22:23）

Oracle创建日志文件（03/13/2015 18:39:37）
Oracle日志文件的状态current/ （09/17/2014 17:29:21）
批量迁移Oracle数据文件，日志文件（01/30/2013 08:05:16）

本文评论查看全部评论（0）