控制文件手工恢复

首页 > 代码库 > 控制文件手工恢复

2024-10-19 05:00:38 213人阅读

所有的控制文件坏了，有备份进行恢复

（1）备份control file
SQL> alter database backup controlfile to ‘/orcl_backup/hot/control.bin‘;

Database altered.

查看检查点情况
SQL> col name for a35
SQL> select file#,name ,checkpoint_change# from v$datafile;

FILE# NAME CHECKPOINT_CHANGE#
---------- ----------------------------------- ------------------
1 /orcl_backup/cold/system01.dbf 5088611
2 /orcl_backup/cold/sysaux01.dbf 5088611
3 /orcl_backup/cold/undotbs01.dbf 5088611
4 /orcl_backup/cold/users01.dbf 5088611
5 /orcl_backup/cold/tp01.dbf 5088611

执行日志切换
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;
select file#,name ,checkpoint_change# from v$datafile;

System altered.

SQL>
FILE# NAME CHECKPOINT_CHANGE#
---------- ----------------------------------- ------------------
1 /orcl_backup/cold/system01.dbf 5089022
2 /orcl_backup/cold/sysaux01.dbf 5089022
3 /orcl_backup/cold/undotbs01.dbf 5089022
4 /orcl_backup/cold/users01.dbf 5089022
5 /orcl_backup/cold/tp01.dbf 5089022

（3）把控制文件删除，当机
[oracle@evancao cold]$ rm -rf control*

SQL> shutdown abort
ORACLE instance shut down.

（4）根据告警日志中的信息把备份拷贝到相应的目录下面
日志内容：
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/orcl_backup/cold/control02.ctl‘
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/orcl_backup/cold/control01.ctl‘
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

拷贝：
[oracle@evancao hot]$ cp control.bin /orcl_backup/cold/control02.ctl
[oracle@evancao hot]$ cp control.bin /orcl_backup/cold/control01.ctl

（5）打开数据库到mount状态
SQL> alter database mount;

Database altered.

在查一把检查点再查询一把
SQL> select file#,name ,checkpoint_change# from v$datafile;

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

FILE# NAME CHECKPOINT_CHANGE#
---------- ----------------------------------- ------------------
1 /orcl_backup/cold/system01.dbf 5089026
2 /orcl_backup/cold/sysaux01.dbf 5089026
3 /orcl_backup/cold/undotbs01.dbf 5089026
4 /orcl_backup/cold/users01.dbf 5089026
5 /orcl_backup/cold/tp01.dbf 5089026

recover database 对数据文件，控制文件恢复

（6）SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
对控制文件不能直接recover database;

SQL> recover database using backup controlfile;
ORA-00279: change 5088952 generated at 12/30/2014 05:14:38 needed for thread 1
ORA-00289: suggestion : /app/oracle/archive_log/1_336_861876939.dbf
ORA-00280: change 5088952 for thread 1 is in sequence #336

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

报错：
ORA-00308: cannot open archived log
‘/app/oracle/archive_log/1_339_861876939.dbf‘
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

（7）查看归档日志

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /app/oracle/archive_log
Oldest online log sequence 334
Next log sequence to archive 336
Current log sequence 336
SQL> exit

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@evancao ~]$ cd /app/oracle/archive_log
[oracle@evancao archive_log]$ ls
1_317_861876939.dbf 1_325_861876939.dbf 1_333_861876939.dbf
1_318_861876939.dbf 1_326_861876939.dbf 1_334_861876939.dbf
1_319_861876939.dbf 1_327_861876939.dbf 1_335_861876939.dbf
1_320_861876939.dbf 1_328_861876939.dbf 1_336_861876939.dbf
1_321_861876939.dbf 1_329_861876939.dbf 1_337_861876939.dbf
1_322_861876939.dbf 1_330_861876939.dbf 1_338_861876939.dbf
1_323_861876939.dbf 1_331_861876939.dbf
1_324_861876939.dbf 1_332_861876939.dbf

发现339 没有归档日志

查看redo log
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 334 52428800 512 1 YES
INACTIVE 5040816 30-DEC-14 5067980 30-DEC-14

3 1 336 52428800 512 1 NO
CURRENT 5088610 30-DEC-14 2.8147E+14

2 1 335 52428800 512 1 YES
INACTIVE 5067980 30-DEC-14 5088610 30-DEC-14

原因：是v$log视图来源与备份的控制文件。所有之后切换日志是没有记录在里面的。

（8）自己指定filename
SQL> /orcl_backup/cold/redo03.log
SP2-0734: unknown command beginning "/orcl_back..." - rest of line ignored.
SQL> recover database using backup controlfile;
ORA-00279: change 5089026 generated at 12/30/2014 05:18:32 needed for thread 1
ORA-00289: suggestion : /app/oracle/archive_log/1_339_861876939.dbf
ORA-00280: change 5089026 for thread 1 is in sequence #339

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/orcl_backup/cold/redo03.log
Log applied.
Media recovery complete.

(9)尝试打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

失败，是因为在oracle的log中记录的还是之前的redo sequence number .所以打开会报错。

（10）alter database open resetlogs;这样会重置sequence

SQL> alter database open resetlogs;

Database altered.

再查看v$log

3.应用系统的数据文件
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 NO
CURRENT 5089094 30-DEC-14 2.8147E+14

2 1 0 52428800 512 1 YES
UNUSED 0 0

3 1 0 52428800 512 1 YES
UNUSED 0 0

可以观察到从1开始了

这样的话在切换日志，再观察归档日志
[oracle@evancao ~]$ cd /app/oracle/archive_log
[oracle@evancao archive_log]$ ls
1_1_867649920.dbf 1_324_861876939.dbf 1_333_861876939.dbf
1_2_867649920.dbf 1_325_861876939.dbf 1_334_861876939.dbf
1_317_861876939.dbf 1_326_861876939.dbf 1_335_861876939.dbf
1_318_861876939.dbf 1_327_861876939.dbf 1_336_861876939.dbf
1_319_861876939.dbf 1_328_861876939.dbf 1_337_861876939.dbf
1_320_861876939.dbf 1_329_861876939.dbf 1_338_861876939.dbf
1_321_861876939.dbf 1_330_861876939.dbf 1_339_861876939.dbf
1_322_861876939.dbf 1_331_861876939.dbf 1_3_867649920.dbf
1_323_861876939.dbf 1_332_861876939.dbf

1_1_867649920.dbf 的sequence已经从1开始了，但是后面的化生号不一样了，这里就需要每次使用reselogs打开数据库的时候在进行一次备份。

incarnation ======》化生号：描述当前数据库的标记。
查询化生号
SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 17-SEP-11 0
PARENT 762083164 0 NO

2 995548 25-OCT-14 1 17-SEP-11
PARENT 861876939 1 NO

3 5089094 30-DEC-14 995548 25-OCT-14
CURRENT 867649920 2 NO

控制文件手工恢复

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 控制文件手工恢复

控制文件手工恢复

看完仍有疑问？有类似问题直接问程序猿