一、首先查看主库的数据文件信息:
SQL>set sqlprompt"_user'@'_connect_identifier>"
SYS@primarydb> select file#,name from v$datafile;
FILE# NAME
1 +DATADG/primarydb/datafile/system.270.785597071
2 +DATADG/primarydb/datafile/sysaux.269.785597073
3 +DATADG/primarydb/datafile/undotbs1.264.785597075
4 +DATADG/primarydb/datafile/users.262.785597077
5 +DATADG/primarydb/datafile/ggs.261.785597131
6 +DATADG/primarydb/datafile/ggs.259.785597479
二、模拟故障发生
直接进入 asmcmd
ASMCMD>cd +datadg/primarydb/datafile
ASMCMD> rm -f users.262.785597077
ORA-15032: not all alterations performed
ORA-15028: ASM file '+datadg/primarydb/datafile/users.262.785597077' not dropped;
currently being accessed (DBD ERROR: OCIStmtExecute)
ASM的文件保护机制还是挺强的,由于数据库还是 open状态,无法直接 rm操作, 关闭数据库后重新删除。
SYS@primarydb>shutdown immediate
ASMCMD> rm -f users.262.785597077
重新尝试打开数据库
SQL> startup
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2227072bytes
Variable Size 364905600bytes
Database Buffers 46137344bytes
Redo Buffers 4276224bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATADG/primarydb/datafile/users.262.785597077'
找不到数据文件了
尝试能不能从重建数据文件恢复:
SQL> alter database create datafile
'+DATADG/primarydb/datafile/users.262.785597077';Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '+DATADG/primarydb/datafile/ users.262.785597077'
SQL>recover datafile 4;
ORA-00279: change 15286generated at 06/09/2012 18:39:26 needed for thread 1
ORA-00289: suggestion: +FRAD
ORA-00280: change 15286 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '+FRADG'
ORA-17503: ksfdopn:2 Failed to open file +FRADG
ORA-15045: ASM file name '+FRADG' is not in reference form
users表空间是系统默认表空间,无法实现通过重建数据文件,再 recover的方式恢复数据文件;
三、利用 DG备库恢复
通过拷贝 DG对应数据文件的方式恢复损坏的数据文件。
RMAN>backup as copy datafile 4 format '/oracle/users.262.785597077';
Starting backup at 2012-06-19 19:29:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK channel ORA_DISK_1:
starting datafile copy
input datafile file number=00004
name=+DATADG/standbydb/datafile/users.261.785540219
output file name=/oracle/users.262.785597077 tag=TAG20120619T192943 RECID=1 STAMP=786396584
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2012-06-19 19:29:44
将 users.262.785597077从备库 ftp到主库
ASMCMD> cp /home/oracle/users.262.785597077 users.262.785597077
copying /home/oracle/users.262.785597077 ->
+datadg/primarydb/datafile/users.262.785597077
ASMCMD-08016: copy source->'/home/oracle/users.262.785597077' and
target->'+datadg/primarydb/datafile/users.262.785597077'failed ORA-15056: additional error message
ORA-15046: ASM filename '+datadg/primarydb/datafile/users.262.785597077' is not in single-file creation form
ORA-06512: at"SYS.X$DBMS_DISKGROUP", line 410
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)
ASMCMD> cp /home/oracle/users.262.785597077 users
copying /home/oracle/users.262.785597077 ->+datadg/primarydb/datafile/users
ASM不允许手动创建后面跟 9位随机数字的文件,没办法只有重命名了
重定向数据文件(如果是文件系统应就没这个问题了)
SQL> alter database rename file '+DATADG/primarydb/datafile/users.262.785597077' to '+DATADG/primarydb/datafile/users';
Database altered.
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
数据库恢复完毕!
如果是开启归档之后创建的表空间,数据文件损坏后使用重建数据文件恢复(保证归档不丢)
SQL>set sqlprompt "_user'@'_connect_identifier>"
SYS@primarydb>createtablespace test datafile '/oracle/test01.dbf' size 10m;
Tablespace created.
SYS@primarydb>create user test identified by test default tablespace test;
User created.
SYS@primarydb>grantconnect,resource to test;
Grant succeeded.
TEST@primarydb>conn test/test
Connected.
TEST@primarydb> create table job ( name varchar2(80),offer date,department varchar2(80) ) tablespace TEST;
Table created.
TEST@primarydb>insert into job values('john','09/09/2009','系统集成部');
1 row created.
TEST@primarydb> commit;
TEST@primarydb> col name format a10
TEST@primarydb> set linesize 200
TEST@primarydb> select * from job;
NAME OFFER DEPARTMENT
john 0009-09-20 09:00:00 系统集成部
TEST@primarydb>conn /as sysdba
Connected.
SYS@primarydb>alter system switch logfile;
System altered.
在备份库查看数据是否同步过来
SQL>setsqlprompt "_user'@'_connect_identifier>"
SYS@standbydb>conn test/test
Connected.
TEST@standbydb>col name format a10
TEST@standbydb>set linesize 200
TEST@standbydb>select * from job;
NAME OFFER DEPARTMENT
john 0009-09-20 09:00:00 系统集成部
主库上更新表
TEST@primarydb>update job set OFFER='2009-09-09 08:00:00';
TEST@primarydb> commit;
TEST@standbydb>select * from job;
NAME OFFER DEPARTMENT
john 2009-09-09 08:00:00 系统集成部
制造故障,将新建的表空间数据文件 DD掉
rac1:/oracle$dd if=/dev/zero of=/oracle/lixz01.dbf bs=1024k count=10
查看 JOB表:
TEST@primarydb> select * from job;
ERROR at line 1:
ORA-01578: ORACLE datablock corrupted (file # 7, block # 138) ORA-01110: data file 7:'/oracle/test01.dbf'
出现数据文件损坏报错
重建数据文件:SYS@primarydb>alter database create datafile '/oracle/test01.dbf';
alter database createdatafile '/oracle/test01.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery ORA-01110: data file 7:'/oracle/test01.dbf'
两种处理办法:
第一种:
SQL> alter database datafile 7 offline;
Database altered.
SQL> alterdatabase create datafile'/oracle/test01.dbf';
SQL> recover datafile 7;
Log applied.
Media recovery complete.
第二种:
SYS@primarydb>shutdown immediate
ORA-01122: database file 7 failed verification check ORA-01110: data file7: '/oracle/test01.dbf'
ORA-01210: data file header is media corrupt
SYS@primarydb> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2227072bytes
Variable Size 364905600bytes
Database Buffers 46137344bytes
Redo Buffers 4276224bytes
Database mounted.
SQL> alterdatabase create datafile '/oracle/test01.dbf';
Database altered.
SQL> recover datafile 7;
ORA-00279: change519308 generated at 06/19/2012 20:39:43 needed for thread 1
ORA-00289: suggestion :+FRADG/primarydb/archivelog/2012_06_19/thread_1_seq_65.413.786401525
ORA-00280: change 519308 for thread 1 is in sequence #65
Specify log:{<RET>=suggested | filename | AUTO | CANCEL} AUTO
Log applied.
Media recovery complete.
SQL>alter database open;
Database altered.
SYS@primarydb> conn test/test
Connected.
SQL>alter database rename file '+DATADG/primarydb/datafile/users.262.785597077' to '+DATADG/primarydb/datafile/users';
Database altered.
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database open;
TEST@primarydb>select * from job;
NAME OFFER DEPARTMENT
john 2009-09-09 08:00:00 系统集成部