Loss of one or more datafiles
1.创建基础数据
SQL> create tablespace test datafile '+RAC_GROUP/hhpen1/datafile/test.dbf' size 500M autoextend on next 50M maxsize unlimited;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+RAC_GROUP/hhpen1/datafile/system.267.857093505
+RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509
+RAC_GROUP/hhpen1/datafile/sysaux.263.857093507
+RAC_GROUP/hhpen1/datafile/users.265.857093511
+RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701
+RAC_GROUP/hhpen1/datafile/test.dbf

6 rows selected.

SQL> create user dayong identified by dayong account unlock default tablespace test; --创建模拟表空间
User created.
SQL> grant connect,resource to dayong; --创建模拟用户
Grant succeeded.
SQL> conn dayong/dayong;
Connected.

SQL> create table mytable(id number primary key,name varchar2(10)); --创建模拟表
Table created.
SQL> insert into mytable values(1,'dayong');
1 row created.
SQL> insert into mytable values(2,'xiaoru');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from mytable;
ID NAME
---------- ----------
1 dayong
2 xiaoru
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile; --切换日志，使其归档
System altered.

2.模拟故障，删除test表空间数据文件
[oracle@rac1 log]$ export ORACLE_SID=+ASM1
[oracle@rac1 log]$ asmcmd
ASMCMD> cd RAC_GROUP/HHPEN1/DATAFILE

ASMCMD> ls
SYSAUX.263.857093507
SYSTEM.267.857093505
TEST.283.857127777
UNDOTBS1.266.857093509
UNDOTBS2.259.857093701
USERS.265.857093511
test.dbf

ASMCMD> rm test.dbf (据说这是一个bug，既然不然联机删除，我们就关闭数据库实例后再执行删除操作)
ORA-15032: not all alterations performed
ORA-15028: ASM file '+RAC_GROUP/HHPEN1/DATAFILE/test.dbf' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

[oracle@rac2 scripts]$ srvctl stop database -d HHPEN1 --停止两节点的实例
[oracle@rac2 scripts]$ srvctl status database -d HHPEN1
Instance HHPEN11 is not running on node rac1
Instance HHPEN12 is not running on node rac2

ASMCMD> rm -rf test.dbf --删除test数据文件
ASMCMD> ls
SYSAUX.263.857093507
SYSTEM.267.857093505
UNDOTBS1.266.857093509
UNDOTBS2.259.857093701
USERS.265.857093511

3.查看是否能够打开数据库
SQL> startup
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 159384832 bytes
Database Buffers 373293056 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+RAC_GROUP/hhpen1/datafile/test.dbf' --丢失数据文件

4.对数据库进行恢复
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
HHPEN12 MOUNTED

SQL> alter database datafile 6 offline; --将数据文件6进行脱机设置
Database altered.
SQL> alter database open;
Database altered.

[oracle@rac2 rac1_arch]$ ls
[oracle@rac2 rac1_arch]$ scp rac1:/u01/app/rac1_arch/* /u01/app/rac1_arch/ --将节点1生成的归档日志传输到节点2上创建的相同目录下
1_3_857093606.dbf 100% 63KB 62.5KB/s 00:00
1_4_857093606.dbf 100% 938KB 937.5KB/s 00:01
2_69_857093606.dbf 100% 3697KB 3.6MB/s 00:00
[oracle@rac2 rac1_arch]$ ls
1_3_857093606.dbf 1_4_857093606.dbf 2_69_857093606.dbf

[oracle@rac2 ~]$ export ORACLE_SID=HHPEN12
[oracle@rac2 ~]$ rman target /

RMAN> restore datafile 6; --对数据文件进行修复

Starting restore at 01-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 instance=HHPEN12 devtype=DISK

creating datafile fno=6 name=+RAC_GROUP/hhpen1/datafile/test.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 01-SEP-14

RMAN> recover datafile 6; --对数据文件进行恢复

Starting recover at 01-SEP-14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 01-SEP-14
5.恢复完整之后进行验证

SQL> select instance_name,status from v$instance; --打开数据库
INSTANCE_NAME STATUS
---------------- ------------
HHPEN12 OPEN
SQL> alter database datafile 6 online; --将数据文件6设置为联机模式
Database altered.

SQL> select name from v$datafile; --查看数据库数据文件信息
NAME
--------------------------------------------------------------------------------
+RAC_GROUP/hhpen1/datafile/system.267.857093505
+RAC_GROUP/hhpen1/datafile/undotbs1.266.857093509
+RAC_GROUP/hhpen1/datafile/sysaux.263.857093507
+RAC_GROUP/hhpen1/datafile/users.265.857093511
+RAC_GROUP/hhpen1/datafile/undotbs2.259.857093701
+RAC_GROUP/hhpen1/datafile/test.dbf

6 rows selected.
SQL> conn dayong/dayong;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MYTABLE TABLE
SQL> select * from mytable;
ID NAME
---------- ----------
1 dayong
2 xiaoru

批注：验证结果可知，数据库已经完成了恢复操作

ITPub博客

[WK-T]ORACLE RAC +ASM Backup and Recovery(二)

最新文章