sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle数据库恢复之RMAN基于SCN的不完全恢复

时间:2016-10-31 22:26   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映遇到误删除操作,使用异机其于SCN进行不一全完恢复的全过程。

案例:基于scn的误table操作恢复

需要在生产数据库上面不可能做基于scn的恢复,但是常常异机基于scn的恢复,比如某个时间点被误删除表等操作。

1) 测试环境

SQL> select current_scn from v$database;
CURRENT_SCN
———–
1450438
在实际的生产环境中,scn要通过logmnr找出,这里只是实验

SQL> conn scott/tiger;
Connected.

SQL> select * from lx01;
ID
———-
111
222
333

SQL> truncate table lx01;
Table truncated.

SQL> insert into lx01 values(1);
1 row created.

SQL> insert into lx01 values(2);
1 row created.

SQL> insert into lx01 values(4);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from lx01;
ID
———-
1
2
4

2) 通过rman恢复


lx01 被truncate 之前的data

run {
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
set until scn 1450438;
restore database;
recover database;
alter database open resetlogs;
}

日志如下:
shutdown immediate
database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started

database mounted
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 96470416 bytes
Database Buffers 213909504 bytes
Redo Buffers 2973696 bytes

allocated channel: c1
channel c1: sid=157 devtype=DISK
allocated channel: c2
channel c2: sid=154 devtype=DISK
executing command: SET until clause
Starting restore at 19-AUG-11
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/prod/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/prod/users01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/prod/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/prod/index01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/prod/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/prod/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/prod/undo_tbs01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/prod/test02.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/prod/cuug01.dbf
restoring datafile 00010 to /u01/app/oracle/oradata/prod/lx01.dbf
restoring datafile 00011 to /u01/app/oracle/oradata/prod/perfstat01.dbf
channel c1: reading from backup piece /disk1/rman/prod/PROD_54.bak
channel c1: restored backup piece 1
piece handle=/disk1/rman/prod/PROD_54.bak tag=TAG20110819T013156
channel c1: restore complete, elapsed time: 00:01:07
Finished restore at 19-AUG-11
Starting recover at 19-AUG-11
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 19-AUG-11
database opened
released channel: c1
released channel: c2–告警日志信息:

Completed: alter database mount
Fri Aug 19 01:38:47 2011
Full restore complete of datafile 6 /u01/app/oracle/oradata/prod/test01.dbf. Elapsed time: 0:00:01
checkpoint is 1450186
Full restore complete of datafile 8 /u01/app/oracle/oradata/prod/test02.dbf. Elapsed time: 0:00:02
checkpoint is 1450186
Full restore complete of datafile 9 /u01/app/oracle/oradata/prod/cuug01.dbf. Elapsed time: 0:00:02
checkpoint is 1450186
Full restore complete of datafile 10 /u01/app/oracle/oradata/prod/lx01.dbf. Elapsed time: 0:00:01
checkpoint is 1450186
Fri Aug 19 01:39:10 2011
Full restore complete of datafile 2 /u01/app/oracle/oradata/prod/users01.dbf. Elapsed time: 0:00:23
checkpoint is 14501Oracle о 86
Full restore complete of datafile 4 /u01/app/oracle/oradata/prod/index01.dbf. Elapsed time: 0:00:27
checkpoint is 1450186
Full restore complete of datafile 5 /u01/app/oracle/oradata/prod/example01.dbf. Elapsed time: 0:00:29
checkpoint is 1450186
Full restore complete of datafile 7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf. Elapsed time: 0:00:30
checkpoint is 1450186
Fri Aug 19 01:39:24 2011
Full restore complete of datafile 11 /u01/app/oracle/oradata/prod/perfstat01.dbf. Elapsed time: 0:00:38
checkpoint is 1450186
Fri Aug 19 01:39:36 2011
Full restore complete of datafile 3 /u01/app/oracle/oradata/prod/sysaux01.dbf. Elapsed time: 0:00:51
checkpoint is 1450186
Fri Aug 19 01:39:49 2011
Full restore complete of datafile 1 /u01/app/oracle/oradata/prod/system01.dbf. Elapsed time: 0:01:03
checkpoint is 1450186
Fri Aug 19 01:39:52 2011
alter database recover datafile list clear
Fri Aug 19 01:39:52 2011
Completed: alter database recover datafile list clear
Fri Aug 19 01:39:52 2011
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11
Fri Aug 19 01:39:52 2011
alter database recover if needed
start until change 1450438
Media Recovery Start
Fri Aug 19 01:39:52 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Fri Aug 19 01:39:53 2011
Incomplete Recovery applied until change 1450439
Fri Aug 19 01:39:53 2011
Media Recovery Complete (prod)
Completed: alter database recover if needed
start until change 1450438
Fri Aug 19 01:39:56 2011
alter database open resetlogs

3) 验证


SQL> select * from scott.lx01;
ID
———-
111
222
333

本文固定链接: http://www.htz.pw/2014/09/04/rman%e5%9f%ba%e6%9c%acscn%e7%9a%84%e4%b8%8d%e5%ae%8c%e5%85%a8%e6%81%a2%e5%a4%8d.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle数据库恢复之RMAN基于SCN的不完全恢复

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1130.html

Oracle研究中心

关键词:

Oracle不完全恢复笔记

Oralce异机恢复误操作

Oracle基于SCN不完全恢复笔记

Oracle误删除数据后基于SCN的恢复案例