【實驗】【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 Transaction Query功能實踐
- 【Flashback】Flashback Query功能實踐
- 【Flashback】Flashback Table功能實踐
- 【實驗】【Flashback】Flash Version Query功能實踐
- 【Flashback】Flashback Drop閃回刪除功能實踐
- 【Flashback】Flashback Database閃回資料庫功能實踐Database資料庫
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- Flashback Drop閃回刪除功能實踐
- [Flashback]Flashback Database閃回資料庫實驗Database資料庫
- oracle實驗記錄 （flashback）Oracle
- oracle 10g flashback version query 和 flashback transaction query實驗Oracle 10g
- oracle10g之flashback version query 和flashback transaction query 實驗Oracle
- Flashback Drop閃回刪除功能實踐（基於回收站）
- Oracle的flashback功能Oracle
- Oracle 10g FLASHBACK DATABASE 實驗記錄Oracle 10gDatabase
- oracle實驗記錄 （flashback,physical standby resetlogs）Oracle
- 【Flashback】啟用Flashback Database閃回資料庫功能Database資料庫
- Physical Standby上開啟flashback database實驗日誌Database
- 啟用flashback database 功能Database
- flashback技術之---flashback query
- flashback技術之---flashback drop
- flashback技術之---flashback table
- flashback技術之---flashback databaseDatabase
- 使用dbms_flashback工具包實現閃回查詢功能
- oracle實驗記錄 (oracle 10G dataguard(10)flashback 與dg)Oracle
- 【FLASHBACK】Oracle flashback data archive 介紹OracleHive
- flashback系列文章三（flashback database）Database
- [Flashback]Oracle flashback儲存過程Oracle儲存過程
- flashback技術之---flashback Transaction Query
- flashback技術之---flashback version query
- Oracle -- flashback database基於ARC+flashback_log還是flashback_logOracleDatabase
- 開啟oracle的flashback閃回功能Oracle
- Flashback_oracle閃回功能的使用Oracle
- oracle flashback特性(2.1)--Flashback Table之RECYCLEBINOracle
- oracle flashback特性(3.2)--Flashback Database操作示例OracleDatabase
- flashback總結三之Flashback_DROP
- flashback總結一之Flashback_DatabaseDatabase
- flashback總結四之Flashback_Query