ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 控制文件丢失恢复实例(1) - 控制文件备份后物理结构发生变化

控制文件丢失恢复实例(1) - 控制文件备份后物理结构发生变化

原创 Linux操作系统 作者:victorymoshui 时间:2011-08-02 22:34:24 0 删除 编辑

一般备份控制文件的原则是:      在数据库物理结构发生变化后备份控制文件。

这里测试在备份控制文件后, 继续加入了两个数据文件,导致物理结构发生变化。 


SQL> insert into test04 values (25,'CMM');

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter database backup controlfile to 'd:\control.bak' ;

数据库已更改。

SQL> insert into test04 values (26,'CMM');

已创建 1 行。

SQL> insert into test04 values (27,'CMM');

已创建 1 行。

SQL> insert into test04 values (28,'CMM');

已创建 1 行。

SQL> commit;

提交完成。

SQL>


SQL> alter tablespace users add datafile 'D:\oracle\product\10.2.0\oradata\test\
USERS02.DBF'  size 10M;

表空间已更改。


SQL> insert into test04 values (29,'CMM');
SQL> insert into test04 values (30,'CMM');
SQL> insert into test04 values (31,'CMM');

SQL> COMMIT;

SQL> alter system switch logfile;

系统已更改。


SQL> insert into test04 values (32,'CMM');
SQL> insert into test04 values (33,'CMM');
SQL> insert into test04 values (34,'CMM');

SQL> COMMIT;

SQL> alter system switch logfile;

SQL> alter tablespace users add datafile 'D:\oracle\product\10.2.0\oradata\test\USERS03.DBF'  size 10M;


SQL> alter system switch logfile;

SQL> insert into test04 values (35,'CMM');
SQL> insert into test04 values (36,'CMM');

SQL> COMMIT;

测试丢失控制文件。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> host del  D:\oracle\product\10.2.0\oradata\test\CONTROL01.CTL

SQL> host del  D:\oracle\product\10.2.0\oradata\test\CONTROL02.CTL

SQL> host del  D:\oracle\product\10.2.0\oradata\test\CONTROL03.CTL

SQL>


SQL> startup
ORACLE 例程已经启动。

Total System Global Area  524288000 bytes
Fixed Size                  1249920 bytes
Variable Size             159387008 bytes
Database Buffers          356515840 bytes
Redo Buffers                7135232 bytes
ORA-00205: ?????????, ??????, ???????

开始恢复。


SQL> host copy d:\CONTROL.BAK  D:\oracle\product\10.2.0\oradata\test\CONTROL01.C
TL
已复制         1 个文件。

SQL> host copy d:\CONTROL.BAK  D:\oracle\product\10.2.0\oradata\test\CONTROL02.C
TL
已复制         1 个文件。

SQL> host copy d:\CONTROL.BAK  D:\oracle\product\10.2.0\oradata\test\CONTROL03.C
TL
已复制         1 个文件。

SQL>


SQL> recover database;
ORA-00283: ??????????
ORA-01610: ?? BACKUP CONTROLFILE ??????????


SQL> recover database using backup controlfile ;
ORA-00279: ?? 593538 (? 04/18/2011 09:07:24 ??) ???? 1 ????
ORA-00289: ??:
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00002_0748774692.001
ORA-00280: ?? 593538 (???? 1) ??? #2 ?


指定日志: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: ??????????
ORA-01244: ????????????????????
ORA-01110: ???? 5: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF'


ORA-01112: ???????

 

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNAMED00005

 

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNA
MED00005'  as  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF' ;

Database altered.

 

 


SQL> recover database using backup controlfile;
ORA-00279: change 593823 generated at 04/18/2011 09:13:31 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00002_0748774692.001
ORA-00280: change 593823 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 593919 generated at 04/18/2011 09:15:49 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00003_0748774692.001
ORA-00280: change 593919 for thread 1 is in sequence #3
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00002_0748774692.001' no longer
needed for this recovery


ORA-00279: change 593978 generated at 04/18/2011 09:18:12 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00004_0748774692.001
ORA-00280: change 593978 for thread 1 is in sequence #4
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00003_0748774692.001' no longer
needed for this recovery


ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS03.DBF'


ORA-01112: media recovery not started


SQL>

 

SQL> select name from v$datafile;

NAME
----------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS02.DBF
D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNAMED00006

6 rows selected.

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\UNNAMED00006'  as 

'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS03.DBF' ;

 

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile; (控制文件中取出)

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             593087
         2             593087
         3             593087
         4             593087
         5             594564
         6             594564

6 rows selected.

 

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header; (数据文件头中取出)

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             594729
         2             594729
         3             594729
         4             594729
         5             594564
         6             594564

6 rows selected.

 


SQL> recover database using backup controlfile ;
ORA-00279: change 594564 generated at 04/18/2011 09:19:00 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001
ORA-00280: change 594564 for thread 1 is in sequence #5


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001'
ORA-27041: unable to open file
OSD-04002: ???????
O/S-Error: (OS 2) ????????????????


ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001'
ORA-27041: unable to open file
OSD-04002: ???????
O/S-Error: (OS 2) ????????????????


找不到最后一个归档文件(其实是需要在线日志来恢复)。手工apple online log .


SQL>  recover database using backup controlfile ;
ORA-00279: change 594564 generated at 04/18/2011 09:19:00 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00005_0748774692.001
ORA-00280: change 594564 for thread 1 is in sequence #5


Specify log: {=suggested | filename | AUTO | CANCEL}
D:\oracle\product\10.2.0\oradata\test\REDO01.LOG
Log applied.
Media recovery complete.
SQL>

 

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             593087
         2             593087
         3             593087
         4             593087
         5             594729
         6             594729

6 rows selected.   

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;   (数据文件中scn都一致了)

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             594729
         2             594729
         3             594729
         4             594729
         5             594729
         6             594729

6 rows selected.

 

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             594731
         2             594731
         3             594731
         4             594731
         5             594731
         6             594731

6 rows selected.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             594731
         2             594731
         3             594731
         4             594731
         5             594731
         6             594731

6 rows selected.

SQL>


SQL> select * from tony.test04;

        ID DEPT
---------- --------------------
        20 CMM
        21 CMM
        22 CMM
        23 CMM
        24 CMM
        25 CMM
        26 CMM
        27 CMM
        28 CMM
        29 CMM
        30 CMM
        31 CMM
        32 CMM
        33 CMM

        ID DEPT
---------- --------------------
        34 CMM
        35 CMM
        36 CMM

17 rows selected.

SQL>

可以看到是完全恢复,虽然使用了resetlogs打开数据库。

最后全备数据库。

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

请登录后发表评论 登录
全部评论

注册时间:2009-08-26

  • 博文量
    173
  • 访问量
    303302