DDL误操作基于scn的不完全恢复
来源:互联网 发布:上网记录监控软件 编辑:程序博客网 时间:2024/05/19 05:34
不完全恢复(必须归档)的步骤:
1、先做全备份(最好是关库备份)。
2、通过logmnr找到误操作的时间点
3、转储所有数据文件。其他不用。
4、startup mount;
5、把数据文件恢复到错误发生的时间点。
6.开库resetlogs;
7.再全备份一次。
utl
1、首先创建一个ult目录。
[oracle@rtest oracle]$ mkdir utl
[oracle@rtest oracle]$ cd utl/
[oracle@rtest utl]$ pwd
/u01/app/oracle/utl
然后修改:
sys@TEST0910> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
sys@TEST0910> alter system set utl_file_dir='/u01/app/oracle/utl';
alter system set utl_file_dir='/u01/app/oracle/utl'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
2、使路径生效,shutdown immediate后startup
sys@TEST0910> alter system set utl_file_dir='/u01/app/oracle/utl' scope=spfile;
System altered.
sys@TEST0910> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string
sys@TEST0910> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST0910> startup
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2230952 bytes
Variable Size 587203928 bytes
Database Buffers 1895825408 bytes
Redo Buffers 20078592 bytes
Database mounted.
Database opened.
sys@TEST0910> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines string
utl_file_dir string /u01/app/oracle/utl
3、破坏试验之前,备份
冷备份:sys@TEST0910> @/u01/app/oracle/bak/cold_bak
热备份:sys@TEST0910> @/u01/app/oracle/bak/hot_bak
破坏性实验:ddl语句的不完全恢复
过程:删数据--查日志--logmnr--关库--转储--mount--不完全恢复--开库resetlogs
truncate误操作
sys@TEST0910> select count(*) from scott.test9;
COUNT(*)
----------
14
4、使logmnr激活,并且误操作truncate
sys@TEST0910> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
sys@TEST0910> truncate table scott.test9;
Table truncated.
没有undo日志啥的。
可以用闪回日志。
sys@TEST0910> select count(*) from scott.test9;
COUNT(*)
----------
0
5、查询当前日志组和归档日志。
sys@TEST0910> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 1 52428800 512 1 NO CURRENT 1885629 2013-09-22 12:57:15 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
sys@TEST0910> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/test0910/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/test0910/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/test0910/redo03.log NO
sys@TEST0910> select name from v$archived_log;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc
logminer官方参考:http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1009063
6、使用logminer抓取日志,获取误操作的时间点。
sys@TEST0910> EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -
> '/u01/app/oracle/utl', -
> DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
/
PL/SQL procedure successfully completed.
sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => '/u01/app/oracle/oradata/test0910/redo01.log', -
> OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc', -
> OPTIONS => DBMS_LOGMNR.ADDFILE);
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE); END;
*
ERROR at line 1:
ORA-01287: file /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc is from a different database incarnation
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
报错,是因为还没有归档。
sys@TEST0910> ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy hh24:mi:ss';
Session altered.
sys@TEST0910> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> DICTFILENAME =>'/u01/app/oracle/utl/dictionary.ora',OPTIONS => -
> DBMS_LOGMNR.DDL_DICT_TRACKING);
PL/SQL procedure successfully completed.
7、查询logminer内容,并结束logmnr
sys@TEST0910> select username,scn,timestamp,sql_redo from v$logmnr_contents
2 where seg_name='TEST9' order by 1;
USERNAME SCN TIMESTAMP SQL_REDO
---------- ---------- -------------------- --------------------------------------------------
SYS 1886381 22-sep-2013 13:09:55 truncate table scott.test9;
sys@TEST0910> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
8、模拟断电,然后转储所有的数据文件
sys@TEST0910> shutdown abort;
ORACLE instance shut down.
然后转储冷备份的数据文件。
转储所有的数据文件
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/users01.dbf /u01/app/oracle/oradata/test0910/disk1/users01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/example01.dbf /u01/app/oracle/oradata/test0910/example01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/lxtb01.dbf /u01/app/oracle/oradata/test0910/lxtb01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/sysaux01.dbf /u01/app/oracle/oradata/test0910/sysaux01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/system01.dbf /u01/app/oracle/oradata/test0910/system01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/testtb.dbf /u01/app/oracle/oradata/test0910/testtb.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/cold_bak/undotbs01.dbf /u01/app/oracle/oradata/test0910/undotbs01.dbf
9、起库到mount状态,然后基于scn的不完全恢复,最后resetlogs打开数据库
sys@TEST0910> startup mount;
ORACLE instance started.
Total System Global Area 2505338880 bytes
Fixed Size 2230952 bytes
Variable Size 587203928 bytes
Database Buffers 1895825408 bytes
Redo Buffers 20078592 bytes
Database mounted.
sys@TEST0910> recover database until change 1886381;
ORA-00279: change 1883694 generated at 09/22/2013 11:51:46 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc
ORA-00280: change 1883694 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1884037 generated at 09/22/2013 11:58:41 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc
ORA-00280: change 1884037 for thread 1 is in sequence #5
Log applied.
Media recovery complete.
sys@TEST0910> alter database open resetlogs;
Database altered.
sys@TEST0910> select count(*) from scott.test9;
COUNT(*)
----------
14
- DDL误操作基于scn的不完全恢复
- 基于SCN的不完全恢复
- rman基于SCN的不完全恢复
- RMAN备份与恢复—基于SCN的不完全恢复
- 使用RMAN的不完全恢复-基于时间/SCN/日志序列
- 使用RMAN的不完全恢复-基于时间/SCN/日志序列
- 小记基于控制文件的scn不完全恢复
- ARCHIVELOG模式下用户管理的不完全恢复—基于SCN的不完全恢复
- 【ORACLE备份与恢复】用户管理的:基于scn的数据库不完全恢复
- RMAN备份与恢复(7)——基于SCN的不完全恢复!
- 基于日志挖掘的误操作不完全恢复思路
- DML误操作基于时间点的不完全恢复
- ARCHIVELOG模式下用户管理的不完全恢复(2)——基于SCN的不完全恢复!
- 探索ORACLE不完全恢复之--基于SCN恢复 第一篇
- 基于cancel的不完全恢复
- 基于cancel的不完全恢复
- 基于Innobackupex的不完全恢复
- Oracle恢复(八)------rman基于scn的恢复
- STM32仿真停在0x08001460 BEAB BKPT 0xAB这个_sys_open代码下
- DML误操作基于时间点的不完全恢复
- 二值化方法总结——附录程序
- android WIFI学习总结
- leetcode_question_116 Populating Next Right Pointers in Each Node
- DDL误操作基于scn的不完全恢复
- 线性表的-顺序表
- 弹出层
- VMware下安装Redhat
- 【leetcode】Path Sum II
- XSS绕过
- 屌丝程序员之路!
- leetcode_question_117 Populating Next Right Pointers in Each Node II
- P2P穿透UDP/TCP原理