Oracle 恢复一案例

本案例主要模拟数据库由于异常宕机，导致部分数据文件和备份控制文件丢失，重建控制文件后，后续操作出现的错误，及解决办法。
假设目前数据共有6个数据文件。
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/zhoul/system01.dbf
/oradata/zhoul/undotbs01.dbf
/oradata/zhoul/sysaux01.dbf
/oradata/zhoul/users01.dbf
/oradata/zhoul/undotbs02.dbf
/oradata/zhoul/zhoul01.dbf

6 rows selected.

SQL> alter database backup controlfile to trace;

Database altered.
假设现在数据库异常宕机，导致控制文件和数据文件部分丢失
SQL> shutdown abort
ORACLE instance shut down.

[oracle@node1 zhoul]$ mv control01.ctl control01.ctl.bak
[oracle@node1 zhoul]$ mv zhoul01.dbf zhoul01.dbf.bak

在数据库nomount之后，采用脚本重建控制文件。
CREATE CONTROLFILE REUSE DATABASE "ZHOUL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/zhoul/redo01.log'  SIZE 50M,
  GROUP 2 '/oradata/zhoul/redo02.log'  SIZE 50M,
  GROUP 3 '/oradata/zhoul/redo03.log'  SIZE 50M,
  GROUP 4 '/oradata/zhoul/redo2_01.log'  SIZE 50M,
  GROUP 5 '/oradata/zhoul/redo2_02.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oradata/zhoul/system01.dbf',
  '/oradata/zhoul/undotbs01.dbf',
  '/oradata/zhoul/sysaux01.dbf',
  '/oradata/zhoul/users01.dbf',
  '/oradata/zhoul/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;

重建完成后，在mount状态可以看到zhoul01.dbf丢失。
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/zhoul/system01.dbf
/oradata/zhoul/undotbs01.dbf
/oradata/zhoul/sysaux01.dbf
/oradata/zhoul/users01.dbf
/oradata/zhoul/undotbs02.dbf

进行介质恢复，在mount状态看不到zhoul01.dbf状态信息
SQL> recover database;
Media recovery complete.
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/zhoul/system01.dbf
/oradata/zhoul/undotbs01.dbf
/oradata/zhoul/sysaux01.dbf
/oradata/zhoul/users01.dbf
/oradata/zhoul/undotbs02.dbf
将数据库读写打开后，由于数据字典存在zhoul01.dbf信息，可以看到MISSING00006文件
SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/zhoul/system01.dbf
/oradata/zhoul/undotbs01.dbf
/oradata/zhoul/sysaux01.dbf
/oradata/zhoul/users01.dbf
/oradata/zhoul/undotbs02.dbf
/app/oracle/product/10.2.0/db_1/dbs/MISSING00006

6 rows selected.

后台alert日志显示，可以推断出Oracle从数据字典中读取相关信息，反向同步至controfile中。
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'ZHOUL' #6 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #6 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00006' in the controlfile.
Dictionary check complete
由于数据字典并不记录该文件的create checkpoint，create datafile size，所以再次创建该文件错误。
SQL>  alter database create datafile 6 as '/oradata/zhoul/zhoul01.dbf';
alter database create datafile 6 as '/oradata/zhoul/zhoul01.dbf'
*
ERROR at line 1:
ORA-01178: file 6 created before last CREATE CONTROLFILE, cannot recreate
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/app/oracle/product/10.2.0/db_1/dbs/MISSING00006'

如果此时有6号文件备份，事情将变得很简单。
SQL> alter database rename file '/app/oracle/product/10.2.0/db_1/dbs/MISSING00006' to '/oradata/zhoul/zhoul01.dbf.bak';

Database altered.

SQL> recover datafile 6 ;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.