控制文件的恢复(二进制)

最新推荐文章于 2023-07-31 18:10:58 发布

原创最新推荐文章于 2023-07-31 18:10:58 发布 · 723 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #sql #thread #数据库 #file #insert

备份与恢复专栏收录该内容

26 篇文章

订阅专栏

本文详细介绍了如何在Oracle数据库中恢复二进制控制文件的完整过程，包括备份控制文件、创建表空间、插入数据、删除现有控制文件、启动数据库至挂载状态、恢复数据库并处理错误、重命名数据文件以及最终打开数据库的步骤。在恢复过程中，通过SQL命令和系统视图监控恢复状态，确保数据完整性和一致性。

1.备份二进制控制文件

SQL> alter database backup controlfile to '/tmp/c1.bin';

2.创建表空间

SQL> create tablespace app3 datafile '/u01/app/oracle/oradata/demo1/app3_01.dbf' size 10M autoextend on;

3.创建表并插入数据

SQL> create table t1 (id int,name varchar2(20)) tablespace app3;

SQL> insert into t1 values(0,'zhangning');

SQL> commit;

4.删除所有控制文件

[oracle@asm demo1]$ rm -f control0*

5.关闭数据库

SQL> shutdown abort

6.拷贝控制文件到原来位置

cp /tmp/c1.bin ./control01.ctl

cp /tmp/c1.bin ./control02.ctl

cp /tmp/c1.bin ./control03.ctl

7.启动数据库mount状态

SQL> startup mount

8.查看SCN号码

SQL> select file#,checkpoint_change# from v$datafile;（来自控制文件）

SQL> select file#,checkpoint_change# from v$datafile_header;（来自数据文件）

9.恢复数据库

SQL> recover database using backup controlfile;
ORA-00279: change 673673 generated at 01/30/2012 12:50:27 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/DEMO1/archivelog/2012_01_30/o1_mf_1_24_%u_.arc ----经查并无此文件
ORA-00280: change 673673 for thread 1 is in sequence #24

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

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00007'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00007'

10.查看视图

SQL> select * from v$recover_file;

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

11.恢复数据文件到原来位置

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00007' to '/u01/app/oracle/oradata/demo1/app3_01.dbf';

12.恢复数据库

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 674860 generated at 01/30/2012 13:01:31 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/DEMO1/archivelog/2012_01_30/o1_mf_1_24_%u_.a
rc
ORA-00280: change 674860 for thread 1 is in sequence #24

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/demo1/redo02.log
Log applied.
Media recovery complete.

13.打开数据库

SQL> alter database open resetlogs;

14.验证

SQL> select * from t1;

ID NAME
---------- --------------------------------------------------
0 zhangning