Oracle恢复实验（四）

环境：Oracle10g、Red Hat 4，Oracle运行在归档模式。

场景：所有控制文件和数据文件丢失。

具体步骤：
1、主机断电，导致数据文件和所有控制文件全部丢失
SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/*.ctl

SQL> !rm -f /home/oracle/oracle/product/oradata/orcl/*.dbf

SQL> !ls /home/oracle/oracle/product/oradata/orcl
redo01.log  redo02.log  redo03.log

SQL> shutdown abort
ORACLE instance shut down.

2、尝试打开数据库
SQL> startup
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              62917068 bytes
Database Buffers           62914560 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
提示找不到控制文件

3、拷贝数据文件和控制文件
SQL> !cp /disk2/oracle/coldbak/*.ctl /home/oracle/oracle/product/oradata/orcl/

SQL> !cp /disk2/oracle/coldbak/*.dbf /home/oracle/oracle/product/oradata/orcl/

4、尝试打开数据库
SQL> startup
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              62917068 bytes
Database Buffers           62914560 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-00338: log 2 of thread 1 is more recent than control file
ORA-00312: online log 2 thread 1:
'/home/oracle/oracle/product/oradata/orcl/redo02.log'
提示控制文件比较旧，记录的日志序号比redo日志的小。

5、获取重建控制文件的脚本
SQL> alter database backup controlfile to trace;

Database altered.
从udump目录中找到脚本。
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/oracle/product/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/home/oracle/oracle/product/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/home/oracle/oracle/product/oradata/orcl/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/home/oracle/oracle/product/oradata/orcl/system01.dbf',
  '/home/oracle/oracle/product/oradata/orcl/undotbs01.dbf',
  '/home/oracle/oracle/product/oradata/orcl/sysaux01.dbf',
  '/home/oracle/oracle/product/oradata/orcl/users01.dbf'
CHARACTER SET AL32UTF8
;

6、重启数据库到nomount状态、执行重建控制文件脚本
SQL> shutdown immediate

Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  130023424 bytes
Fixed Size                  1218100 bytes
Variable Size              62917068 bytes
Database Buffers           62914560 bytes
Redo Buffers                2973696 bytes

7、尝试打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oracle/product/oradata/orcl/system01.dbf'

8、介质恢复
SQL> recover database;
Media recovery complete.

9、打开数据库
SQL> alter database open;

Database altered.

SQL> conn scott/oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
RECOVER_TEST                   TABLE
RECOVER_TEST2                  TABLE

6 rows selected.

SQL> select count(*) from recover_test2;

  COUNT(*)
----------
     28672