控制文件丢失恢复(二)

 (二)控制文件全部丢失恢复
 如果控制文件全部丢失，也可以有两种解决办法:创建控制文件恢复和使用备份的控制文件恢复
下面模拟这两种方法
 1)使用创建控制文件来恢复
 a)先备份控制文件
SQL> alter database backup controlfile to trace;    --备份的控制文件在udmp目录下

Database altered.
SQL> alter database backup controlfile to trace as 'c:\ctl.txt';  --备份控制文件到指定目录

Database altered.
b)模拟所有控制文件损坏(删除所有控制文件)
 数据库处于关闭状态
 恢复过程： 
c)启动到nomount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  570425344 bytes
Fixed Size                  1250188 bytes
Variable Size             201329780 bytes
Database Buffers          360710144 bytes
Redo Buffers                7135232 bytes
d)创建控制文件

由于日志文件没有损坏，不需重建redo log,使用noresetlogs来创建控制文件
创建完控制文件数据库自动启到mount状态

问题：如果控制文件备份后数据文件增加了怎么办?创建是否还会成功?
e)恢复数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
SQL> recover database;  --恢复数据库
Media recovery complete.
f)打开数据库
SQL> alter database open;

Database altered.

 2)从备份中恢复
     准备工作：
      1.先备份控制文件

SQL> alter database backup controlfile to 'c:\control01.ctl';

Database altered.
      2.备份完后创建一个表插入数据来测试
SQL> create table t1(id int,name varchar2(5));

Table created.

SQL> insert into t1 values(1,'a');

1 row created.

SQL> commit;

Commit complete.
查看当前日志文件
SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         86          1 INACTIVE
         2         85          1 INACTIVE
         3         87          1 CURRENT
  切换日志
  SQL> alter system switch logfile;

System altered.

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

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         86          1 INACTIVE
         2         88          1 CURRENT
         3         87          1 ACTIVE
  插入数据
SQL> insert into t1 values(2,'b');

1 row created.

SQL> commit;

Commit complete.
切换
SQL> alter system switch logfile;

System altered.

SQL> select group#,sequence#,members,status from v$log;  --当前日志文件是89

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         89          1 CURRENT
         2         88          1 ACTIVE
         3         87          1 ACTIVE
SQL> insert into t1 values(3,'c');

1 row created.

SQL> commit;

Commit complete.
SQL> alter system switch logfile;

System altered.

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

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1         89          1 ACTIVE
         2         88          1 ACTIVE
         3         90          1 CURRENT
  继续插入数据
SQL> insert into t1 values(4,'d');    --在90号当前日志

1 row created.

SQL> commit;

Commit complete.

  3.关闭数据库，模拟控制文件丢失(删除全部控制文件)
    开始恢复
     4.把备份控制文件的拷回原来的位置
     5.启动到mount状态
SQL> startup mount;
ORACLE instance started.

Total System Global Area  570425344 bytes
Fixed Size                  1250188 bytes
Variable Size             188746868 bytes
Database Buffers          373293056 bytes
Redo Buffers                7135232 bytes
Database mounted.
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7008532
         2            7008532
         3            7008532
         4            7008532
         5            7008532
         6            7008532
         7            7008532
         8            7008532
         9            7008532

9 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7010506
         2            7010506
         3            7010506
         4            7010506
         5            7010506
         6            7010506
         7            7010506
         8            7010506
         9            7010506

9 rows selected.

     6.恢复数据库
SQL> recover database using backup controlfile;
ORA-00279: change 7008694 generated at 01/18/2014 11:32:55 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1_   --需要87号归档日志文件
87_%U_.ARC
ORA-00280: change 7008694 for thread 1 is in sequence #87

Specify log: {=suggested | filename | AUTO | CANCEL}   --回车

ORA-00279: change 7010506 generated at 01/18/2014 11:41:29 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1_    --需要88号归档日志文件
88_%U_.ARC
ORA-00280: change 7010506 for thread 1 is in sequence #88
ORA-00278: log file
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1    --87号归档不再需要
_87_9FMXZ989_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7010575 generated at 01/18/2014 11:44:09 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1_
89_%U_.ARC
ORA-00280: change 7010575 for thread 1 is in sequence #89
ORA-00278: log file
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1
_88_9FMY49RF_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7010754 generated at 01/18/2014 11:46:28 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1_
90_%U_.ARC
ORA-00280: change 7010754 for thread 1 is in sequence #90
ORA-00278: log file
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1
_89_9FMY8OGN_.ARC' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1
_90_%U_.ARC'
ORA-27041: unable to open file      --序列号为90的归档日志文件,90号还没有归档,没有自动去找当前在线日志去恢复
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

SQL> select group#,sequence#,status from v$log;   --恢复的过程中日志序列号没有发生变化

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         86 INACTIVE
         3         87 CURRENT
         2         85 INACTIVE
  SQL> select sequence#,first_change#,first_time,next_change#,next_time from v$archived_log;  --90还没归档

 SEQUENCE# FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------ ------------ ------------
        87       7008531 18-JAN-14         7010506 18-JAN-14
        88       7010506 18-JAN-14         7010575 18-JAN-14
        89       7010575 18-JAN-14         7010754 18-JAN-14

3 rows selected.
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7010754
         2            7010754
         3            7010754
         4            7010754
         5            7010754
         6            7010754
         7            7010754
         8            7010754
         9            7010754

9 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7010754
         2            7010754
         3            7010754
         4            7010754
         5            7010754
         6            7010754
         7            7010754
         8            7010754
         9            7010754

9 rows selected.
继续恢复
SQL> recover database using backup controlfile;
ORA-00279: change 7010754 generated at 01/18/2014 11:46:28 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_01_18\O1_MF_1_
90_%U_.ARC
ORA-00280: change 7010754 for thread 1 is in sequence #90

Specify log: {=suggested | filename | AUTO | CANCEL}
D:\oracle\oradata\orcl\redo03.log    --手动指定90号归档即在线的3号日志组,恢复完成
Log applied.
Media recovery complete.
SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7010811
         2            7010811
         3            7010811
         4            7010811
         5            7010811
         6            7010811
         7            7010811
         8            7010811
         9            7010811

9 rows selected.

SQL> select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            7010811
         2            7010811
         3            7010811
         4            7010811
         5            7010811
         6            7010811
         7            7010811
         8            7010811
         9            7010811

9 rows selected.

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

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         86 INACTIVE
         3         87 CURRENT
         2         85 INACTIVE
     7.必须以resetlogs打开数据库
 SQL> alter database open resetlogs;

Database altered.
日志文件被重新创建
SQL> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          0 UNUSED
         2          0 UNUSED
         3          1 CURRENT
  88，89，90三组日志又被归档
  SQL> select sequence#,first_change#,first_time,next_change#,next_time from v$archived_log;

 SEQUENCE# FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME
---------- ------------- ------------ ------------ ------------
        87       7008531 18-JAN-14         7010506 18-JAN-14
        88       7010506 18-JAN-14         7010575 18-JAN-14
        89       7010575 18-JAN-14         7010754 18-JAN-14
        89       7010575 18-JAN-14         7010754 18-JAN-14
        88       7010506 18-JAN-14         7010575 18-JAN-14
        90       7010754 18-JAN-14         7010812 18-JAN-14

6 rows selected.
     8.重新备份数据库

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

转载于:http://blog.itpub.net/26937943/viewspace-1072480/