关于丢失控制文件、数据文件的用户管理模式恢复测试

couz74045599

于 2012-11-06 15:53:10 发布

阅读量77

点赞数

关于丢失控制文件、数据文件的手动恢复测试

首先创建数据文件：

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace test datafile 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\test.dbf' size 20M;

表空间已创建。

SQL> create tablespace tbs01 datafile 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\tbs01.dbf' size 20M;

表空间已创建。

此处删除新建的表空间test，为了验证恢复时此表空间信息还在控制文件，还需要恢复。（所以我们在数据库结构更改前做控制文件的备份）

SQL> drop tablespace test including contents;

表空间已删除。

SQL> create table t01 (x number) tablespace tbs01;

表已创建。

SQL> insert into t01 values (99);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select file_name from dba_data_files where tablespace_name='TBS01';

FILE_NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF

以下备份控制文件

SQL> alter database backup controlfile to trace as 'D:\ctl.sql' reuse;

数据库已更改。
关闭数据库
SQL> shutdown abort

关闭数据库后删除控制文件与新建的数据文件TBS01.DBF

C:\Documents and Settings\Administrator>set NLS_LANG=american_america.zhs16gbk

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Nov 6 14:42:51 2012

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 945803264 bytes
Fixed Size                  1348056 bytes
Variable Size             268439080 bytes
Database Buffers          671088640 bytes
Redo Buffers                4927488 bytes
ORA-00205: error in identifying control file, check alert log for more info

在启动时找不到控制文件报错
用备份的控制文件脚本ctl.sql来创建新的控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE "ZYH" NORESETLOGS ARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 1168
7 LOGFILE
8    GROUP 1 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\REDO01.LOG' SIZE 200M,
9    GROUP 2 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\REDO02.LOG' SIZE 200M,
10    GROUP 3 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\REDO03.LOG' SIZE 200M
11 DATAFILE
12    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF',
13    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF',
14    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF',
15    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF',
16    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF',
17    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF',
18    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF',
19    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF',
20    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF',
21    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF',
22    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF',
23    'D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF'
24 CHARACTER SET AL32UTF8
25 ;
CREATE CONTROLFILE REUSE DATABASE "ZYH" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

在创建控制文件时，会检查数据文件及头信息，因为tbs01.dbf数据文件无法找到报错
下面把脚本中数据文件文件TBS01.DBF删掉

Control file created.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
         1          1          4 209715200          1 NO     CURRENT                             2539583062 06-NOV-12
         3          1          3 209715200          1 NO     INACTIVE                            2539561443 06-NOV-12
         2          1          2 209715200          1 NO     INACTIVE                            2539542946 06-NOV-12

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS         CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
         1 SYSTEM                 2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF
         2 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF
         3 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF
         4 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF
         5 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF
         6 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF
         7 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF
         8 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF
         9 SYSTEM                 2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF
        10 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF
        11 RECOVER                2539583062 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF

11 rows selected.

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

     FILE# RECOVE FUZZY CHECKPOINT_CHANGE#
---------- ------ ------ ------------------
         1 YES    YES            2539583062
         2 YES    YES            2539583062
         3 YES    YES            2539583062
         4 YES    YES            2539583062
         5 YES    YES            2539583062
         6 YES    YES            2539583062
         7 YES    YES            2539583062
         8 YES    YES            2539583062
         9 YES    YES            2539583062
        10 YES    YES            2539583062
        11 YES    YES            2539583062

11 rows selected.

下面进行recover时，检查到数据文件12并通过介质恢复到控制文件。说明recover程序启动了
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 12: 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\TEST.DBF'

recover之前的CHECKPOINT_CHANGE#是2539583062，recover之后变为2539584003。说明已经在应用log日志了
SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS         CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
         1 SYSTEM                 2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF
         2 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF
         3 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF
         4 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF
         5 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF
         6 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF
         7 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF
         8 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF
         9 SYSTEM                 2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF
        10 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF
        11 RECOVER                2539584003 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF

12 rows selected.

SQL> select * from v$backup;

     FILE# STATUS                                  CHANGE# TIME
---------- ------------------------------------ ---------- ------------
         1 NOT ACTIVE                                    0
         2 NOT ACTIVE                                    0
         3 NOT ACTIVE                                    0
         4 NOT ACTIVE                                    0
         5 NOT ACTIVE                                    0
         6 NOT ACTIVE                                    0
         7 NOT ACTIVE                                    0
         8 NOT ACTIVE                                    0
         9 NOT ACTIVE                                    0
        10 NOT ACTIVE                                    0
        11 NOT ACTIVE                                    0

FILE# STATUS CHANGE# TIME
---------- ------------------------------------ ---------- ------------
12 FILE MISSING 0

12 rows selected.

创建需要恢复的数据文件12

SQL> alter database create datafile 'UNNAMED00012' as 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\TEST.DBF';

Database altered.

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

12 rows selected.

SQL> select * from v$log;

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
2539583062

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 13: 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF'

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS         CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
         1 SYSTEM                 2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF
         2 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF
         3 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF
         4 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF
         5 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF
         6 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF
         7 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF
         8 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF
         9 SYSTEM                 2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF
        10 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF
        11 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF

     FILE# STATUS         CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
        12 RECOVER                2539584303 D:\APP\ADMINISTRATOR\ORADATA\ZYH\TEST.DBF
        13 RECOVER                2539584300 C:\APP\USER\PRODUCT\11.1.0\DB_2\DATABASE\UNNAMED00013

13 rows selected.

SQL> alter database create datafile 'UNNAMED00013' as 'D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF';

Database altered.

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

     FILE# STATUS         CHECKPOINT_CHANGE#
---------- -------------- ------------------
         1 SYSTEM                 2539584303
         2 RECOVER                2539584303
         3 RECOVER                2539584303
         4 RECOVER                2539584303
         5 RECOVER                2539584303
         6 RECOVER                2539584303
         7 RECOVER                2539584303
         8 RECOVER                2539584303
         9 SYSTEM                 2539584303
        10 RECOVER                2539584303
        11 RECOVER                2539584303

     FILE# STATUS         CHECKPOINT_CHANGE#
---------- -------------- ------------------
        12 RECOVER                2539584303
        13 RECOVER                2539584300

13 rows selected.

SQL> select * from v$log;

SQL> select file#,recover,fuzzy from v$datafile_header;

     FILE# RECOVE FUZZY
---------- ------ ------
         1 YES    YES
         2 YES    YES
         3 YES    YES
         4 YES    YES
         5 YES    YES
         6 YES    YES
         7 YES    YES
         8 YES    YES
         9 YES    YES
        10 YES    YES
        11 YES    YES

     FILE# RECOVE FUZZY
---------- ------ ------
        12 YES    YES
        13 YES    NO

13 rows selected.

SQL> recover database;
Media recovery complete.
SQL> select file#,recover,fuzzy from v$datafile_header;

     FILE# RECOVE FUZZY
---------- ------ ------
         1 NO     NO
         2 NO     NO
         3 NO     NO
         4 NO     NO
         5 NO     NO
         6 NO     NO
         7 NO     NO
         8 NO     NO
         9 NO     NO
        10 NO     NO
        11 NO     NO

FILE# RECOVE FUZZY
---------- ------ ------
13 NO NO

12 rows selected.

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS         CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
         1 SYSTEM                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF
         2 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF
         3 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF
         4 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF
         5 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF
         6 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF
         7 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF
         8 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF
         9 SYSTEM                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF
        10 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF
        11 ONLINE                 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF

FILE# STATUS CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
13 ONLINE 2539597653 D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF

12 rows selected.

SQL>
SQL> alter database open;

Database altered.

SQL> select file#,recover,fuzzy from v$datafile_header;

     FILE# RECOVE FUZZY
---------- ------ ------
         1 NO     YES
         2 NO     YES
         3 NO     YES
         4 NO     YES
         5 NO     YES
         6 NO     YES
         7 NO     YES
         8 NO     YES
         9 NO     YES
        10 NO     YES
        11 NO     YES

FILE# RECOVE FUZZY
---------- ------ ------
13 NO YES

12 rows selected.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
2539617656

SQL> select FILE#,status,CHECKPOINT_CHANGE#,name from v$datafile;

     FILE# STATUS         CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
         1 SYSTEM                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM01.DBF
         2 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX01.DBF
         3 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\UNDOTBS01.DBF
         4 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\USERS01.DBF
         5 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA01.DBF
         6 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX01.DBF
         7 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA02.DBF
         8 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_IDX02.DBF
         9 SYSTEM                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSTEM02.DBF
        10 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\SYSAUX02.DBF
        11 ONLINE                 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\ECCH_DATA03.DBF

FILE# STATUS CHECKPOINT_CHANGE# NAME
---------- -------------- ------------------ ------------------------------------------------------------
13 ONLINE 2539617656 D:\APP\ADMINISTRATOR\ORADATA\ZYH\TBS01.DBF

12 rows selected.

SQL> select * from t01;

X
----------
99

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

转载于:http://blog.itpub.net/23891491/viewspace-748462/