Oracle恢复实验（二）

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

场景：数据文件user01.dbf损坏，打开数据库情况下进行恢复。

具体步骤：
1、删除user01.dbf文件模拟数据文件损坏，关闭数据库，尝试打开。
!rm -f /home/oracle/oracle/product/oradata/orcl、users01.dbf
SQL> shutdown abort
ORACLE instance shut down.
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-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oracle/product/oradata/orcl/users01.dbf'
可以看到：数据文件4找不到，无法打开数据库，数据库启动到Mount状态

2、将损坏数据文件脱机
SQL> alter database datafile 4 offline;

Database altered.

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

Database altered.
脱机后，数据库可以正常打开。

4、复制数据文件，尝试将数据文件联机
SQL> !cp /disk2/bak/users01.dbf /home/oracle/oracle/product/oradata/orcl

SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/home/oracle/oracle/product/oradata/orcl/users01.dbf'
此时Oracle会提示需要介质恢复

5、介质恢复
recover tablespace users;

6、将表空间联机
SQL> select name,status from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ -------
/home/oracle/oracle/product/oradata/orcl/system01.dbf        SYSTEM
/home/oracle/oracle/product/oradata/orcl/undotbs01.dbf       ONLINE
/home/oracle/oracle/product/oradata/orcl/sysaux01.dbf        ONLINE
/home/oracle/oracle/product/oradata/orcl/users01.dbf         OFFLINE

SQL> alter database datafile 4 online;

Database altered.

7、检查数据
SQL> select count(*) from scott.recover_test;

  COUNT(*)
----------
       448

作者“隔壁老王”