rac完全恢复学习

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.