oracle閃回技術總結之閃回數據庫
[實施步驟]
步驟一、設置如下的初始化參數:
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.
閃回數據庫完成
