Data Pump EXPDP FLASHBACK_SCN Parameter

In normal export utility (EXP) we have consistent=y option to ensure the data consistency of the dump. For Data Pump there is no consistent parameter whereas it has FLASHBACK_SCN and FLASHBACK_TIME parameters to achieve this goal. These two parameters are mutually exclusive. In FLASHBACK_SCN you have to pass the SCN number as the argument and in FLASHBACK_TIME you have to pass timestamp value.


Example:
Checking the SCN Number before the table creation
SQL> select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;
CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
  369457983                369457983

SQL> create table emp
(
empno number(4) not null,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7, 2),
comm number(7, 2),
deptno number(2)
);
Table created.

SQL> insert into emp values (7369, 'smith', 'clerk', 7902,to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20);
1 row created.
SQL> commit;
Commit complete.

Checking the SCN Number after the table creation and first row inserted.

SQL> select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;

CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
  369458034                369458034

SQL> insert into emp values (7499, 'allen', 'salesman', 7698,to_date('20-feb-1981', 'dd-mon-yyyy'), 1600, 300, 30);
1 row created.
SQL> commit;
Commit complete.

Checking the SCN Number after the table creation and second row inserted. Currently we have 2 rows in the table.
SQL> select CURRENT_SCN, dbms_flashback.get_system_change_number from v$database;

CURRENT_SCN GET_SYSTEM_CHANGE_NUMBER
----------- ------------------------
  369458041                369458041

SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 smith      clerk           7902 17-DEC-80        800                    20
      7499 allen      salesman        7698 20-FEB-81       1600        300         30

Now I am going try with EXPDP with each SCN and see the differences below.

  1. First I am going try with SCN number (369457983) before creating the table. In this expdp is not exporting the table even it has 2 rows in it. It is throwing error.

expdp directory=exp_dir dumpfile=flash_test.dmp logfile=flash_test.log flashback_scn=369457983 tables=scott.emp

Export: Release 11.2.0.2.0 - Production on Mon May 14 03:14:28 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release
. . .
. . .
. . .
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/export/flash_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 03:14:39

  1. Secondly I am trying with the scn number after inserting first row to the table and it will export only one row.

expdp directory=exp_dir dumpfile=flash_test.dmp logfile=flash_test.log flashback_scn=369458034 tables=scott.emp

Export: Release 11.2.0.2.0 - Production on Mon May 14 03:13:37 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release
. . .
. . .
. . .
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP"                             8.046 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/export/flash_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 03:13:53

  1. In last example I am going to try with the scn number after inserting 2nd row and it will export 2 rows.

expdp directory=exp_dir dumpfile=flash_test.dmp logfile=flash_test.log flashback_scn=369458041 tables=scott.emp

Export: Release 11.2.0.2.0 - Production on Mon May 14 03:15:14 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release
. . .
. . .
. . .
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EMP"                             8.093 KB       2 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/export/flash_test.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 03:15:26
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google