1、查看现有数据库文件
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/rac/datafile/system.259.866566403 +DATA/rac/datafile/undotbs1.260.866566407 +DATA/rac/datafile/sysaux.261.866566407 +DATA/rac/datafile/undotbs2.263.866566409 +DATA/rac/datafile/users.264.866566409
2、做一个完全备份
RMAN> backup as copy database ;
3、创建表空间
SQL> create tablespace zxm datafile size 2m; Tablespace created. SQL> create tablespace user01 datafile '+DATA' size 1M; Tablespace created. SQL> alter tablespace user01 add datafile size 1m; Tablespace altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/rac/datafile/system.259.866566403 +DATA/rac/datafile/undotbs1.260.866566407 +DATA/rac/datafile/sysaux.261.866566407 +DATA/rac/datafile/undotbs2.263.866566409 +DATA/rac/datafile/users.264.866566409 +DATA/rac/datafile/zxm.287.866732569 +DATA/rac/datafile/user01.284.866732649 +DATA/rac/datafile/user01.288.866732733 8 rows selected.
4、创建示例数据
SQL> create table test as select * from user_tables; Table created. SQL> create table test2 as select * from test; Table created. SQL> col TABLESPACE_NAME format a30 SQL> col SEGMENT_NAME format a30 SQL> select tablespace_name,segment_name from user_segments; TABLESPACE_NAME SEGMENT_NAME ------------------------------ ------------------------------ USER01 TEST USER01 TEST2
5、关闭数据库删除文件，模拟灾难场景
[oracle@rac1 admin]$ srvctl stop database -d rac [oracle@rac1 admin]$ export ORACLE_SID=+ASM1 [oracle@rac1 admin]$ asmcmd -p ASMCMD [+] > ls DATA/ RECV/ ASMCMD [+] > cd DATA ASMCMD [+DATA] > ls RAC/ TEST/ ASMCMD [+DATA] > cd RAC ASMCMD [+DATA/RAC] > ls ARCHIVELOG/ CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ spfilerac.ora ASMCMD [+DATA/RAC] > cd DATAFILE ASMCMD [+DATA/RAC/DATAFILE] > ls SYSAUX.261.866566407 SYSTEM.259.866566403 TEST.282.866646695 UNDOTBS1.260.866566407 UNDOTBS2.263.866566409 USER01.284.866732649 USER01.288.866732733 USERS.264.866566409 ZXM.287.866732569 ASMCMD [+DATA/RAC/DATAFILE] > rm USER01.284.866732649 ASMCMD [+DATA/RAC/DATAFILE] > rm USER01.288.866732733 ASMCMD [+DATA/RAC/DATAFILE] > rm ZXM.287.866732569 [oracle@rac1 admin]$ export ORACLE_SID=rac1 [oracle@rac1 admin]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 19 15:31:22 2014 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 1610612736 bytes Fixed Size 2280840 bytes Variable Size 416100984 bytes Database Buffers 1157627904 bytes Redo Buffers 34603008 bytes Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '+DATA/rac/datafile/zxm.287.866732569'
6、recover命令只能在已有的物理文件上进行恢复，所以需要先创建数据文件
SQL> alter database create datafile 6; Database altered. SQL> recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-01110: data file 6: '+DATA/rac/datafile/zxm.287.866732569' ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '+DATA/rac/datafile/zxm.287.866732569' 进入目录中看新创建的文件是：ZXM.287.866734565 ASMCMD [+DATA/RAC] > cd DATAFILE ASMCMD [+DATA/RAC/DATAFILE] > ls SYSAUX.261.866566407 SYSTEM.259.866566403 TEST.282.866646695 UNDOTBS1.260.866566407 UNDOTBS2.263.866566409 USERS.264.866566409 ZXM.287.866734565 而控制中的文件为： SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA/rac/datafile/system.259.866566403 +DATA/rac/datafile/undotbs1.260.866566407 +DATA/rac/datafile/sysaux.261.866566407 +DATA/rac/datafile/undotbs2.263.866566409 +DATA/rac/datafile/users.264.866566409 +DATA/rac/datafile/zxm.287.866732569 +DATA/rac/datafile/user01.284.866732649 +DATA/rac/datafile/user01.288.866732733 8 rows selected. 需要对数据文件进行改名，其实就是修改控制文件 SQL> alter database rename file '+DATA/rac/datafile/zxm.287.866732569' to '+DATA/rac/datafile/ZXM.287.866734565'; Database altered. 再次操作，这次恢复成功了 SQL> recover datafile 6; Media recovery complete.
7、恢复其他的数据文件
SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '+DATA/rac/datafile/user01.284.866732649' 只恢复了一个文件，刚总共删除了3个，那现在重新再将剩下的两个创建了 SQL> alter database create datafile '+DATA/rac/datafile/user01.284.866732649'; Database altered. SQL> recover datafile 7; ORA-00283: recovery session canceled due to errors ORA-01110: data file 7: '+DATA/rac/datafile/user01.284.866732649' ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '+DATA/rac/datafile/user01.284.866732649' 进入文件目录查看，发现文件名不是创建的那个 ASMCMD [+DATA/RAC/DATAFILE] > ls SYSAUX.261.866566407 SYSTEM.259.866566403 TEST.282.866646695 UNDOTBS1.260.866566407 UNDOTBS2.263.866566409 USER01.288.866735415 USERS.264.866566409 ZXM.287.866734565 SQL> alter database rename file '+DATA/rac/datafile/user01.284.866732649' to '+DATA/rac/datafile/USER01.288.866735415'; Database altered. SQL> recover datafile 7; Media recovery complete. SQL> alter database create datafile '+DATA/rac/datafile/user01.288.866732733'; ASMCMD [+DATA/RAC/DATAFILE] > ls SYSAUX.261.866566407 SYSTEM.259.866566403 TEST.282.866646695 UNDOTBS1.260.866566407 UNDOTBS2.263.866566409 USER01.284.866736519 USER01.288.866735415 USERS.264.866566409 ZXM.287.866734565 SQL> alter database rename file '+DATA/rac/datafile/user01.288.866732733' to '+DATA/rac/datafile/USER01.284.866736519'; SQL> alter database rename file '+DATA/rac/datafile/user01.288.866732733' to '+DATA/rac/datafile/USER01.284.866736519'; Database altered. SQL> recover datafile 8; Media recovery complete. 打开数据 SQL> alter database open; Database altered.