本实验是模拟了删除数据文件后,对数据文件的恢复。
前提:有恢复必备的备份及归档文件
本次删除数据文件13#(D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF),test3是该数据文件上的表。
向test3里插入数据
SQL> insert into test3 select * from test3;
前提:有恢复必备的备份及归档文件
本次删除数据文件13#(D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF),test3是该数据文件上的表。
向test3里插入数据
SQL> insert into test3 select * from test3;
已创建8192行。
SQL> commit;
提交完成。
SQL> select count(*) from test3;
COUNT(*)
----------
16384
SQL>shutdown immediate
干净关闭数据库后,删除掉数据文件D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF。
C:\Documents and Settings\zhangyouhai>sqlplus / as sysdba
----------
16384
SQL>shutdown immediate
干净关闭数据库后,删除掉数据文件D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF。
C:\Documents and Settings\zhangyouhai>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Oct 31 10:02:35 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 979283968 bytes
Fixed Size 1334040 bytes
Variable Size 226493672 bytes
Database Buffers 746586112 bytes
Redo Buffers 4870144 bytes
Database mounted.
SQL> select name from v$datafile;
Fixed Size 1334040 bytes
Variable Size 226493672 bytes
Database Buffers 746586112 bytes
Redo Buffers 4870144 bytes
Database mounted.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSTEM01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\USERS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_IDX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_IDX02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSTEM02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSAUX02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA03.DBF
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSTEM01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSAUX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\UNDOTBS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\USERS01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_IDX01.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_IDX02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSTEM02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\SYSAUX02.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\TEST_DATA03.DBF
NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\TEST.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF
D:\ROWCHAIN22.DBF
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\TEST.DBF
D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF
D:\ROWCHAIN22.DBF
14 rows selected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: 'D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF'
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01110: data file 13: 'D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF'
在rman下进行数据文件介质的恢复
C:\Documents and Settings\zhangyouhai>rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Wed Oct 31 10:05:01 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: MDLCNPRO (DBID=3079513499, not open)
RMAN> restore datafile 13;
Starting restore at 31-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=540 device type=DISK
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=540 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF
channel ORA_DISK_1: reading from backup piece D:\BACKUP\RMAN\147_FULL_MDLCNPRO_20121030_3553.BAK
channel ORA_DISK_1: piece handle=D:\BACKUP\RMAN\147_FULL_MDLCNPRO_20121030_3553.BAK tag=TAG20121030T114211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-OCT-12
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF
channel ORA_DISK_1: reading from backup piece D:\BACKUP\RMAN\147_FULL_MDLCNPRO_20121030_3553.BAK
channel ORA_DISK_1: piece handle=D:\BACKUP\RMAN\147_FULL_MDLCNPRO_20121030_3553.BAK tag=TAG20121030T114211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-OCT-12
查看当前log信息
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 370 209715200 2 NO CURRENT
2546490807 31-OCT-12
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------------
1 1 370 209715200 2 NO CURRENT
2546490807 31-OCT-12
3 1 369 209715200 2 YES INACTIVE
2546471038 31-OCT-12
2546471038 31-OCT-12
2 1 368 209715200 2 YES INACTIVE
2546449644 31-OCT-12
2546449644 31-OCT-12
SQL> set linesize 200
SQL> /
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 370 209715200 2 NO CURRENT 2546490807 31-OCT-12
3 1 369 209715200 2 YES INACTIVE 2546471038 31-OCT-12
2 1 368 209715200 2 YES INACTIVE 2546449644 31-OCT-12
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 370 209715200 2 NO CURRENT 2546490807 31-OCT-12
3 1 369 209715200 2 YES INACTIVE 2546471038 31-OCT-12
2 1 368 209715200 2 YES INACTIVE 2546449644 31-OCT-12
查看系统检查点
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
2546501753
------------------
2546501753
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
------------------ ------------
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
------------------ ------------
2546501753 2546501753
2546501753 2546501753
2546501753 2546501753
14 rows selected.
SQL> select checkpoint_change# from v$datfile_header;
select checkpoint_change# from v$datfile_header
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
select checkpoint_change# from v$datfile_header
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: 'D:\APP\ADMINISTRATOR\ORADATA\MDLCNPRO\ROWCHAIN.DBF'
SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------
13 ONLINE ONLINE 2546250165 30-OCT-12
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------
13 ONLINE ONLINE 2546250165 30-OCT-12
我们看到恢复的13号文件需要从2546250165开始恢复,下面查一下这个scn所属那个archive log
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'
2 ;
Session altered.
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,FIRST_TIME from v$archived_log where 2546250164 between FIRST_CHANGE# and next_CHANGE#;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME
---------- ------------- ------------ -------------------
358 2546248788 2546271458 2012-10-30 11:29:02
从上边的查询看出本次recover需要从358#日志恢复数据文件
---------- ------------- ------------ -------------------
358 2546248788 2546271458 2012-10-30 11:29:02
从上边的查询看出本次recover需要从358#日志恢复数据文件
SQL> RECOVER DATAFILE 13;
ORA-00279: change 2546250165 generated at 10/30/2012 11:50:51 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00358_0794846985.001
ORA-00280: change 2546250165 for thread 1 is in sequence #358
ORA-00279: change 2546250165 generated at 10/30/2012 11:50:51 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00358_0794846985.001
ORA-00280: change 2546250165 for thread 1 is in sequence #358
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 2546271458 generated at 10/30/2012 12:28:54 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00359_0794846985.001
ORA-00280: change 2546271458 for thread 1 is in sequence #359
ORA-00279: change 2546291601 generated at 10/30/2012 12:59:02 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00360_0794846985.001
ORA-00280: change 2546291601 for thread 1 is in sequence #360
ORA-00279: change 2546315757 generated at 10/30/2012 13:58:55 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00361_0794846985.001
ORA-00280: change 2546315757 for thread 1 is in sequence #361
ORA-00279: change 2546334055 generated at 10/30/2012 14:29:05 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00362_0794846985.001
ORA-00280: change 2546334055 for thread 1 is in sequence #362
ORA-00279: change 2546361678 generated at 10/30/2012 15:28:58 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00363_0794846985.001
ORA-00280: change 2546361678 for thread 1 is in sequence #363
ORA-00279: change 2546378388 generated at 10/30/2012 16:23:39 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00364_0794846985.001
ORA-00280: change 2546378388 for thread 1 is in sequence #364
ORA-00279: change 2546383271 generated at 10/30/2012 16:24:00 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00365_0794846985.001
ORA-00280: change 2546383271 for thread 1 is in sequence #365
ORA-00279: change 2546387490 generated at 10/30/2012 16:27:13 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00366_0794846985.001
ORA-00280: change 2546387490 for thread 1 is in sequence #366
ORA-00279: change 2546413409 generated at 10/30/2012 16:58:59 needed for thread 1
ORA-00289: suggestion : D:\ARCH\MDLCNPRO\MDLCNPRO_ARC00367_0794846985.001
ORA-00280: change 2546413409 for thread 1 is in sequence #367
Log applied.
Media recovery complete.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select * from v$recover_file;
no rows selected
SQL> select count(*) from test3;
COUNT(*)
----------
16384
查询结果得出的记录与之前的记录16384相同。恢复完成。
----------
16384
查询结果得出的记录与之前的记录16384相同。恢复完成。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23891491/viewspace-747983/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23891491/viewspace-747983/