Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "what is the method of expdp export based on flashback_scn". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

When using Oracle data pump after 10g to export data, we can use the flashback_scn parameter to specify the point in time to export, at this time

Oracle uses flashback query queries to export scn data, and flashback query uses undo, so there is no need to turn on the flashback database feature.

That is, as long as the undo information is not overwritten, flashback_scn-based export can still be performed even if the database is restarted.

-- testing with scott users

Oracle@wang:/home/oracle$sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 15 07:43:24 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > show user

USER is "SCOTT"

SQL > select current_scn from v$database

CURRENT_SCN

-

21870773 (marked as the 1st time point)

SQL > create table t (num number)

Table created.

SQL > insert into t values (1)

1 row created.

SQL > commit

Commit complete.

SQL > select current_scn from v$database

CURRENT_SCN

-

21870796 (marked as the 2nd time point)

SQL > insert into t values (2)

1 row created.

SQL > commit

Commit complete.

SQL > select current_scn from v$database

CURRENT_SCN

-

21870805

SQL > conn / as sysdba

Connected.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

SQL > select current_scn from v$database

CURRENT_SCN

-

21871307 (marked as the 3rd time point)

SQL > conn scott/tiger

Connected.

SQL > insert into t values (3)

1 row created.

SQL > commit

Commit complete.

SQL > select current_scn from v$database

CURRENT_SCN

-

21871340 (marked as the 4th time point)

SQL > select * from t

NUM

-

one

two

three

-- now start to do expdp export

(point in time No. 1)

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t flashback_scn=21870773

(point in time 2)

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t flashback_scn=21870796

(point in time 3)

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t flashback_scn=21871307

(time point on the 4th)

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t flashback_scn=21871340

Oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t

Flashback_scn=21870773

Export: Release 11.2.0.4.0-Production on Fri Mar 15 07:52:18 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT". "SYS_EXPORT_TABLE_01": scott/* directory=DATA_PUMP_DIR dumpfile=t1.dmp tables=t flashback_scn=21870773

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "SCOTT". "T" 4.984 KB 0 rows

Master table "SCOTT". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/ u01/app/oracle/admin/DBdb/dpdump/t1.dmp

Job "SCOTT". "SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:52:28 2019 elapsed 0 00:00:08

Oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t

Flashback_scn=21870796

Export: Release 11.2.0.4.0-Production on Fri Mar 15 07:52:34 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT". "SYS_EXPORT_TABLE_01": scott/* directory=DATA_PUMP_DIR dumpfile=t2.dmp tables=t flashback_scn=21870796

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "SCOTT". "T" 5 KB 1 rows

Master table "SCOTT". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/ u01/app/oracle/admin/DBdb/dpdump/t2.dmp

Job "SCOTT". "SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:52:44 2019 elapsed 0 00:00:07

Oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t

Flashback_scn=21871307

Export: Release 11.2.0.4.0-Production on Fri Mar 15 07:52:54 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT". "SYS_EXPORT_TABLE_01": scott/* directory=DATA_PUMP_DIR dumpfile=t3.dmp tables=t flashback_scn=21871307

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "SCOTT". "T" 5.007 KB 2 rows

Master table "SCOTT". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/ u01/app/oracle/admin/DBdb/dpdump/t3.dmp

Job "SCOTT". "SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:53:03 2019 elapsed 0 00:00:07

Oracle@wang:/home/oracle$ expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t

Flashback_scn=21871340

Export: Release 11.2.0.4.0-Production on Fri Mar 15 07:53:12 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT". "SYS_EXPORT_TABLE_01": scott/* directory=DATA_PUMP_DIR dumpfile=t4.dmp tables=t flashback_scn=21871340

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "SCOTT". "T" 5.015 KB 3 rows

Master table "SCOTT". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/ u01/app/oracle/admin/DBdb/dpdump/t4.dmp

Job "SCOTT". "SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 15 07:53:22 2019 elapsed 0 00:00:07

Oracle@wang:/home/oracle$

-- now start to do impdp export

(point in time No. 1)

Drop table t purge

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t1.dmp

(point in time 2)

Drop table t purge

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t2.dmp

(point in time 3)

Drop table t purge

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t3.dmp

(time point on the 4th)

Drop table t purge

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=t4.dmp

This is the end of the content of "what is the method of exporting expdp based on flashback_scn". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.