【實驗】【Flashback】Flashback EXP功能實踐
Flashback EXP功能實現了匯出某一個時間點或具體SCN點的資料，在備份和恢復某一個特定時間資料提供了可能。
這個功能得益於EXP工具提供的兩個引數：FLASHBACK_SCN和FLASHBACK_TIME，下面分別使用這兩個引數進行一下實踐：
1.建立實驗環境
sec@ora10g> set time on;
07:23:48 sec@ora10g> create table test_flashback_exp as select * from dba_objects where rownum<101;
Table created.
07:24:06 sec@ora10g> select count(*) from test_flashback_exp;
COUNT(*)
----------
100
07:24:25 sec@ora10g> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248325
07:24:31 sec@ora10g> delete from test_flashback_exp where rownum<51;
50 rows deleted.
07:24:52 sec@ora10g> commit;
Commit complete.
07:24:54 sec@ora10g> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248339
07:24:57 sec@ora10g> select count(*) from test_flashback_exp;
COUNT(*)
----------
50
07:26:01 sec@ora10g> delete from test_flashback_exp;
50 rows deleted.
07:26:11 sec@ora10g> commit;
Commit complete.
07:26:12 sec@ora10g> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248386
07:27:57 sec@ora10g> select count(*) from test_flashback_exp;
COUNT(*)
----------
0
透過上述過程模擬了這樣一個場景：
07:23:48（SCN：1248325）建立了測試用含有有100條記錄的測試表test_flashback_exp
07:24:31刪除其中的50條記錄
07:24:54（SCN：1248339）查詢該表中含有50條記錄
07:26:01刪除全部的資料
07:26:12（SCN：1248386）查詢該表中含有0條資料
好，到此測試環境已經準備好了，讓我們看看EXP基於時間點和SCN的匯出：
2.FLASHBACK_SCN對三個時間點的功能演示
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248325
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:06:41 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 100 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248339
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:07:02 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 50 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248386
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:07:13 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 0 rows exported
Export terminated successfully without warnings.
3.FLASHBACK_TIME對三個時間點的功能演示
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:24:54"'
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:16 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 100 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:25:54"'
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 50 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:26:54"'
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 0 rows exported
Export terminated successfully without warnings.
4.到此，演示結束，總結一下
1). FLASHBACK_SCN引數
這個引數指定了一個exp匯出的特定的SCN，匯出的所有資料將保持這個SCN的一致性。預設情況下是none，表示不使用flashback query功能
語法如下：
exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248325
2). FLASHBACK_TIME引數
這個參數列示匯出將基於"YYYY-MM-DD HH24:MI:SS"的一個時間戳，exp將找到最近的一個SCN來代替這個時間戳來進行匯出。 預設是none，表示不使用flashback query功能
格式如下：
exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:24:54"'
一定要注意時間中引號的使用寫成flashback_time='"2009-04-11 07:24:54"'和flashback_time="'2009-04-11 07:24:54'"都可以，但一定要同時有單引號和雙引號。不然會報錯滴~~
5.EXP幫助文件中FLASHBACK_SCN引數和FLASHBACK_TIME引數的位置
ora10g@linux5 /exp$ exp -help
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:57:06 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform. full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
-- The End --
這個功能得益於EXP工具提供的兩個引數：FLASHBACK_SCN和FLASHBACK_TIME，下面分別使用這兩個引數進行一下實踐：
1.建立實驗環境
sec@ora10g> set time on;
07:23:48 sec@ora10g> create table test_flashback_exp as select * from dba_objects where rownum<101;
Table created.
07:24:06 sec@ora10g> select count(*) from test_flashback_exp;
COUNT(*)
----------
100
07:24:25 sec@ora10g> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248325
07:24:31 sec@ora10g> delete from test_flashback_exp where rownum<51;
50 rows deleted.
07:24:52 sec@ora10g> commit;
Commit complete.
07:24:54 sec@ora10g> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248339
07:24:57 sec@ora10g> select count(*) from test_flashback_exp;
COUNT(*)
----------
50
07:26:01 sec@ora10g> delete from test_flashback_exp;
50 rows deleted.
07:26:11 sec@ora10g> commit;
Commit complete.
07:26:12 sec@ora10g> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1248386
07:27:57 sec@ora10g> select count(*) from test_flashback_exp;
COUNT(*)
----------
0
透過上述過程模擬了這樣一個場景：
07:23:48（SCN：1248325）建立了測試用含有有100條記錄的測試表test_flashback_exp
07:24:31刪除其中的50條記錄
07:24:54（SCN：1248339）查詢該表中含有50條記錄
07:26:01刪除全部的資料
07:26:12（SCN：1248386）查詢該表中含有0條資料
好，到此測試環境已經準備好了，讓我們看看EXP基於時間點和SCN的匯出：
2.FLASHBACK_SCN對三個時間點的功能演示
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248325
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:06:41 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 100 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248339
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:07:02 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 50 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248386
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:07:13 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 0 rows exported
Export terminated successfully without warnings.
3.FLASHBACK_TIME對三個時間點的功能演示
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:24:54"'
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:16 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 100 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:25:54"'
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 50 rows exported
Export terminated successfully without warnings.
ora10g@linux5 /exp$ exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:26:54"'
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:37:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table TEST_FLASHBACK_EXP 0 rows exported
Export terminated successfully without warnings.
4.到此，演示結束，總結一下
1). FLASHBACK_SCN引數
這個引數指定了一個exp匯出的特定的SCN，匯出的所有資料將保持這個SCN的一致性。預設情況下是none，表示不使用flashback query功能
語法如下：
exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_scn=1248325
2). FLASHBACK_TIME引數
這個參數列示匯出將基於"YYYY-MM-DD HH24:MI:SS"的一個時間戳，exp將找到最近的一個SCN來代替這個時間戳來進行匯出。 預設是none，表示不使用flashback query功能
格式如下：
exp sec/sec file=test_flashback_exp.dmp log=test_flashback_exp.dmp.log tables=test_flashback_exp flashback_time='"2009-04-11 07:24:54"'
一定要注意時間中引號的使用寫成flashback_time='"2009-04-11 07:24:54"'和flashback_time="'2009-04-11 07:24:54'"都可以，但一定要同時有單引號和雙引號。不然會報錯滴~~
5.EXP幫助文件中FLASHBACK_SCN引數和FLASHBACK_TIME引數的位置
ora10g@linux5 /exp$ exp -help
Export: Release 10.2.0.4.0 - Production on Sat Apr 11 08:57:06 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform. full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export
Export terminated successfully without warnings.
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-587038/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Flashback】Flashback Drop閃回刪除功能實驗
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- Flashback Drop閃回刪除功能實踐
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- flashback實現資料快速復原
- Flashback Query(轉)
- ORACLE Flashback Query偽列Oracle
- 2.6.2 Overview of Flashback PDB in a CDBView
- 在Oracle DG Standby庫上啟用flashback database功能OracleDatabase
- flashback query閃回資料
- Flashback Data Archive原理詳解Hive
- oracle 10g flashback databaseOracle 10gDatabase
- Oracle 備份恢復之 FlashbackOracle
- 20211317李卓桐 Exp6 MSF攻防實踐 實驗報告
- guarantee restore points-Flashback after RMAN restoreREST
- [20181002]DBMS_FLASHBACK與函式.txt函式
- Oracle閃回技術--Flashback Version QueryOracle
- 用flashback恢復儲存過程儲存過程
- [20180424]開啟表空flashback on.txt
- [20180724]Flashback query和子游標共享.txt
- 基於flashback_scn的expdp匯出
- [20210722]ORA-38760與flashback database.txtDatabase
- Flashback database必須要有之前的archivelog嗎？DatabaseHive
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- ORA-55507: Encountered mining error during Flashback Transaction Backout. functiError
- C++ 未初始化記憶體出現 flashbackC++記憶體
- 實驗四 CTF實踐
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- 螢幕錄影機(bb flashback pro 4)pjb v4.1.21
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- Go 泛型變更：約束太醜了，先移動到 x/exp 做實驗性功能Go泛型
- 刪使用者刪表空間的操作還能flashback回來嗎？
- GeminiDB Cassandra介面新特性FLASHBACK釋出：任意時間點秒級閃回
- Taro實踐 - 深度開發實踐體驗及總結
- 自定義限速功能實踐——Caffeine
- FlinkCDC 2.0使用實踐體驗
- 自定義限速功能實踐——Map 版本
- Redis在秒殺功能的實踐Redis
- 實驗 2 Scala 程式設計初級實踐程式設計