ITPub博客

首页 > 数据库 > Oracle > 控制文件丢失恢复(二)

控制文件丢失恢复(二)

原创 Oracle 作者:sky850623 时间:2014-01-18 12:32:48 0 删除 编辑

 (二)控制文件全部丢失恢复
 如果控制文件全部丢失,也可以有两种解决办法:创建控制文件恢复和使用备份的控制文件恢复
下面模拟这两种方法
 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle闪回特性
请登录后发表评论 登录
全部评论

注册时间:2013-05-30

  • 博文量
    154
  • 访问量
    468939