|
[实施步骤] 步骤一、设置如下的初始化参数:
Sql>alter system set db_recovery_file_dest_size = 2G scope=both; System altered.
Sql>alter systemset db_recovery_file_dest='/u01/apps/oracle/flash_recovery_area'scope=both; System altered. 步骤二 设置重现保持目标为24小时：以分钟为单位 SQL> alter system setdb_flashback_retention_target = 1440 scope=both; Systemaltered. 步骤三 正常关闭数据库，启动到mount状态，激活闪回功能后打开数据库 19:27:38SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
19:28:19SQL> startup mount;
ORACLEinstance started.
TotalSystem Global Area 2415919104 bytes
FixedSize 2022768 bytes
VariableSize 553648784 bytes
DatabaseBuffers 1845493760 bytes
RedoBuffers 14753792 bytes
Databasemounted.
SQL> alter database archivelog; Database altered. SQL>!mkdir /u01/apps/archive_log SQL> alter system setlog_archive_dest_1='location=/u01/apps/archive_log'; Systemaltered. SQL> alter database flashback on; Databasealtered. SQL> alter database open; Databasealtered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES 步骤四 监控FRA信息 SQL>col name for a55 SQL> select name,space_limit,space_used, space_reclaimable fromv$recovery_file_dest;
NAME SPACE_LIMITSPACE_USED SPACE_RECLAIMABLE ------------------------------------------------------------------ ---------- ----------------- /u01/apps/oracle/flash_recovery_area 2147483648 8192000 0
1 rowselected. 步骤五 开启日志挖掘功能，日志挖掘logmnr ，scn和时间的挖掘 Sql>Alterdatabase add supplemental log data; 创建数据字典，疑问（数据字典什么时间点开始建，） SQL> execdbms_logmnr_d.build(options=>dbms_logmnr_d.store_in_redo_logs) PL/SQL proceduresuccessfully completed. 查看数据字典创作在哪个日志里 SQL> selectname,sequence#,first_change#,dictionary_begin,dictionary_end fromv$archived_log;
NAME SEQUENCE# FIRST_CHANGE# DIC DIC
/arch2/1_2_822244495.dbf 2 646310 YES YES
步骤六 开始有事务操作 SQL> conn scott/tiger SQL>create table test as select * from emp; insertinto test select * from emp; insert into test select * from emp; insert into test select * from emp; commit; update test set ename ='helei'; rollback; delete from test where deptno =20; commit;
SQL> create table emp2 as select * from emp; Tablecreated. 步骤七 此时不小心误删除了一张表，直到发现表被误删除 SQL> drop table emp2 purge; Tabledropped. SQL> set time on; 21:52:14SQL> 表被删除时间大概在21:52分左右 此时事务还在继续运行 22点00分左右发现表被删除 切了几个归档 22:00:38SQL> alter system switch logfile; System altered.
步骤八 开始日志挖掘出表变删除时的SCN及时间戳 再次查看归档日志信息及创建的数据字典信息 17:53:39 SQL> select name,sequence#,first_change#,to_char(first_time,'yyyy-mm-ddhh24:mi:ss'),dictionary_begin,dictionary_end from v$archived_log;
22:01:25SQL> select name,sequence#,first_change#,to_char(first_time,'yyyy-mm-ddhh24:mi:ss'),dictionary_begin,dictionary_end from v$archived_log;
NAME SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME, DIC DIC ----------------------------------------------------------------- ------------- ------------------- --- --- /u01/apps/archive_log/1_5_896643434.dbf 5 1011817 2015-11-24 19:38:27NO NO /u01/apps/archive_log/1_6_896643434.dbf 6 1018525 2015-11-24 21:39:09YES YES /u01/apps/archive_log/1_7_896643434.dbf 7 1019401 2015-11-24 21:39:11NO NO /u01/apps/archive_log/1_8_896643434.dbf 8 1020367 2015-11-24 22:00:36NO NO
开始正式日志挖掘： 22:03:13SQL> exec dbms_logmnr.add_logfile(logfilename =>'/u01/apps/archive_log/1_6_896643434.dbf',options=>dbms_logmnr.new)---添加包含数据字典的日志
PL/SQL proceduresuccessfully completed.
由于表被drop的时间大概是0点29分左右，drop可能包含sequence 7,8对其进行挖掘
22:04:15SQL> execdbms_logmnr.add_logfile(logfilename=>'/u01/apps/archive_log/1_7_896643434.dbf',options=>dbms_logmnr.addfile) PL/SQL proceduresuccessfully completed. 22:05:31SQL> execdbms_logmnr.add_logfile(logfilename=>'/u01/apps/archive_log/1_8_896643434.dbf',options=>dbms_logmnr.addfile) PL/SQL proceduresuccessfully completed. 22:06:05 SQL> exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_redo_logs);
PL/SQLprocedure successfully completed.
22:07:01 SQL> alter session set nls_date_format='yyyy_mm_ddhh24:mi:ss';
Sessionaltered.
18:08:38 SQL> col sql_redo for a40
挖掘完成，查看成果，找到被DROP时的确切信息 22:07:05SQL> select scn,timestamp,sql_redo from v$logmnr_contents Where seg_owner='SCOTT' and seg_name ='EMP2';
SCNTIMESTAMP SQL_REDO ----------------------------- ---------------------------------------- 1019443 2015_11_24 21:40:33 create tableemp2 as select * from emp; 10198002015_11_24 21:52:11 drop table emp2 purge; Sql>execdbms_logmnr.end_logmnr; 步骤九 开始闪回数据库 正常关闭数据库，保持数据文件的一致性 18:42:03 SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLEinstance shut down.
18:43:17 SQL> startup mount;
ORACLEinstance started.
TotalSystem Global Area 2415919104 bytes
FixedSize 2022768 bytes
VariableSize 553648784 bytes
DatabaseBuffers 1845493760 bytes
RedoBuffers 14753792 bytes
Databasemounted.
22:08:43SQL> flashback database to scn 1019800; Flashback complete.
或者flashback database to timestamp to_timestamp('2015-11-25 21:52:10','yyyy-mm-ddhh24:mi:ss');
Flashbackcomplete.
[oracle@aix224 ~]$ tail–f /u01/app/oracle/admin/test/bdump/alert_test.log
[oracle@HE1~]$ tail -f /u01/apps/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log FlashbackRestore Start FlashbackRestore Complete FlashbackMedia Recovery Start SerialMedia Recovery started FlashbackMedia Recovery Log /u01/apps/archive_log/1_5_896643434.dbf FlashbackMedia Recovery Log /u01/apps/archive_log/1_6_896643434.dbf FlashbackMedia Recovery Log /u01/apps/archive_log/1_7_896643434.dbf IncompleteRecovery applied until change 1019801 time 11/24/2015 21:52:11 FlashbackMedia Recovery Complete Completed:flashback database to scn 1019800 闪回成功，查看新数据文件和控制文件中的检查点信息 18:49:04 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
----------------------------
1 1019801
2 1019801
3 1019801
4 1019801
5 1019801
18:49:57 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
----------------------------
1 1019801
2 1019801
3 1019801
4 1019801
5 1019801
步骤十 以redo only 方式打开数据库 22:11:42 SQL> alter database open read only; Databasealtered. 22:11:49 SQL> conn scott/tiger Connected. 22:11:54 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE TEST TABLE 6 rowsselected.
步骤十一 导出scott用户下的emp2表 [oracle@HE1 bin]$ exp scott/tigerfile=/u01/apps/scott.ep2 log=scottep2.log indexes=n tables=emp2 Export:Release 11.2.0.1.0 - Production on Tue Nov 24 22:13:48 2015 Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options Exportdone in ZHS16GBK character set and AL16UTF16 NCHAR character set Note:indexes on tables will not be exported About toexport specified tables via Conventional Path ... . .exporting table EMP2 14 rows exported Exportterminated successfully without warnings.
步骤十二 正常关闭数据库，启动到mount状态对数据库进行完全恢复 22:14:43 SQL> shutdown immediate; Databaseclosed. Databasedismounted. ORACLEinstance shut down. 22:14:48 SQL> startup mount; ORACLEinstance started.
TotalSystem Global Area 780824576 bytes FixedSize 2217424 bytes VariableSize 465570352 bytes DatabaseBuffers 310378496 bytes RedoBuffers 2658304 bytes Databasemounted. 22:14:56 SQL> recover database; ORA-00279:change 1019801 generated at 11/24/2015 21:39:11 needed for thread 1 ORA-00289:suggestion : /u01/apps/archive_log/1_7_896643434.dbf ORA-00280:change 1019801 for thread 1 is in sequence #7
22:15:32Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO ORA-00279:change 1020367 generated at 11/24/2015 22:00:36 needed for thread 1 ORA-00289:suggestion : /u01/apps/archive_log/1_8_896643434.dbf ORA-00280:change 1020367 for thread 1 is in sequence #8
Logapplied. Mediarecovery complete. 跟踪日志中的信息 [oracle@HE1apps]$ tail -f /u01/apps/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log ALTERDATABASE RECOVER CONTINUE DEFAULT MediaRecovery Log /u01/apps/archive_log/1_8_896643434.dbf Recoveryof Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0 Mem# 0:/u01/apps/oracle/oradata/orcl/redo03.log Recoveryof Online Redo Log: Thread 1 Group 1 Seq 10 Reading mem 0 Mem# 0:/u01/apps/oracle/oradata/orcl/redo01.log Recoveryof Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0 Mem# 0:/u01/apps/oracle/oradata/orcl/redo02.log MediaRecovery Complete (orcl) Completed:ALTER DATABASE RECOVER CONTINUEDEFAULT 步骤十三 打开数据库，数据库正常开启 Mediarecovery complete. 22:15:52 SQL> alter database open;
Databasealtered.
22:16:57 SQL> conn scott/tiger Connected. 22:18:42 SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE TEST 查看scott用户下的表，emp2不存在，正常，因为在归档中记录了emp2被drop掉，恢复的时候使用归档完全恢复中，emp2还是会被drop掉
22:19:26 SQL> select file#,checkpoint_change#from v$datafile;
FILE# CHECKPOINT_CHANGE# ---------------------------- 1 1033196 2 1033196 3 1033196 4 1033196 5 1033196 22:19:27 SQL> select file#,checkpoint_change#from v$datafile_header;
FILE# CHECKPOINT_CHANGE# ---------------------------- 1 1033196 2 1033196 3 1033196 4 1033196 5 1033196 步骤十四 导入scott下emp2表 [oracle@HE1 bin]$imp scott/tigerfile=/u01/apps/scott.ep2 tables=emp2 fromuser=scott touser=scott ignore=y
Import:Release 11.2.0.1.0 - Production on Tue Nov 24 22:24:30 2015
Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options
Exportfile created by EXPORT:V11.02.00 via conventional path importdone in ZHS16GBK character set and AL16UTF16 NCHAR character set .importing SCOTT's objects into SCOTT . .importing table "EMP2" 14 rowsimported Import terminated successfully without warnings.
查询 22:26:08 SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE TEST TABLE
6 rowsselected. 基于SCN和时间戳的flash database 成功完成
2、基于restorepoint 还原点的闪回数据库 步骤一、创建restorepoint 先创建一张dept1表 SQL> createtable dept1 as select * from dept; Table created. 查看当前信息 SQL> select *from tab; TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE DEPT1 TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE 6 rows selected. 创建restorepoint SQL> create restore point rs_pt; Restore point created. 在创建一张dept2表 SQL> connscott/tiger SQL> createtable dept2 as select * from dept; Table created. 步骤二、不小心drop掉dept1 SQL> drop tabledept1 purge; Table dropped. SQL> select *from tab; TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE DEPT2 TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE 6 rows selected. 步骤三、开始基于restorepoint闪回数据库 SQL> col name fora20 SQL>col time for a35 SQL> selectname,scn,time from v$restore_point; NAME SCN TIME ------------------------------ ----------------------------------- RS_PT 103371424-NOV-15 10.27.58.000000000 PM SQL> shutdownimmediate SQL>startupmount SQL>flashbackdatabase to restore point rs_pt; #flashback databaseto timestamp sysdate-15/1440; (当前的系统时间之前的 15 分钟）
Flashback complete. 步骤四、以readonly 方式打开数据库 SQL> alterdatabase open read only; Database altered.
SQL> connscott/tiger Connected. SQL> select *from tab; TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE DEPT1 TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE 6 rows selected. 步骤五、导出dept1表 [oracle@HE1 bin]$exp scott/tiger file=/u01/apps/scott.dp1 log=scottdp1.logindexes=n tables=dept1 Export:Release 11.2.0.1.0 - Production on Tue Nov 24 22:33:53 2015 Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options Exportdone in ZHS16GBK character set and AL16UTF16 NCHAR character set Note:indexes on tables will not be exported About toexport specified tables via Conventional Path ... . .exporting table DEPT1 4 rows exported Exportterminated successfully without warnings. 22:34:51 SQL> shutdown immediate Databaseclosed. Databasedismounted. ORACLEinstance shut down.
22:35:13 SQL> startup mount; ORACLEinstance started.
TotalSystem Global Area 780824576 bytes FixedSize 2217424 bytes VariableSize 465570352 bytes DatabaseBuffers 310378496 bytes RedoBuffers 2658304 bytes Databasemounted. 步骤六、恢复数据库recoverdatabase 22:35:37 SQL>recover database; Mediarecovery complete. 步骤七、打开数据库 22:35:40 SQL>alterdatabase open;
Databasealtered.
22:35:47 SQL> conn scott/tiger Connected. 22:36:01 SQL> select *from tab;
TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE DEPT2 TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE TEST TABLE
7 rowsselected.
步骤八、导入dept1表 [oracle@HE1 bin]$impscott/tiger file=/u01/apps/scott.dp1 tables=dept1 fromuser=scott touser=scottignore=y Import:Release 11.2.0.1.0 - Production on Tue Nov 24 22:39:06 2015 Copyright(c) 1982, 2009, Oracle and/or its affiliates. All rights reserved Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bitProduction With thePartitioning, OLAP, Data Mining and Real Application Testing options Exportfile created by EXPORT:V11.02.00 via conventional path importdone in ZHS16GBK character set and AL16UTF16 NCHAR character set .importing SCOTT's objects into SCOTT . .importing table "DEPT1" 4 rowsimported Importterminated successfully without warnings.
22:36:05 SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------------- ---------- BONUS TABLE DEPT TABLE DEPT1 TABLE DEPT2 TABLE EMP TABLE EMP2 TABLE SALGRADE TABLE TEST TABLE
8 rowsselected. 闪回数据库完成
| 
QQ群⑧：
窥视卡
雷达卡
发表于 2015-11-26 08:57:52
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡