案例2：基于scn的误table操作恢复
需要在生产数据库上面不可能做基于scn的恢复，但是常常异机基于scn的恢复，比如某个时间点被误删除表等操作。
此案例来至于QQ群
中的《冰冻心灵》
|
SQL> select current_scn from v$database;
CURRENT_SCN ———– 1450438–在实际的生产环境中，scn要通过logmnr找出，这里只是实验SQL> conn scott/tiger; Connected.SQL> select * from lx01; ID ———- 111 222 333SQL> truncate table lx01; Table truncated. SQL> insert into lx01 values(1); SQL> insert into lx01 values(2); SQL> insert into lx01 values(4); SQL> commit; SQL> select * from lx01; |
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; }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 1450186 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 |
报歉！评论已关闭。