Restore a schema from full expdp backup

Import schema from full db expdp backup:

In some situations you might want to restore a single schema from entire EXPDP backup. In this example I want to explain how to import a single schema from full DB expdp backup.





Lets backup the full database using the EXPDP:

F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8

Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

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
Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8

.
.
.
.
.
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
. . exported "SCOTT"."DEPT"                             4.976 KB       4 rows
. . exported "SCOTT"."EMP"                              5.625 KB      14 rows
. . exported "SCOTT"."SALGRADE"                         4.890 KB       5 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
.
.
.
.
.
Master table "ATOORPU"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_FULL_01 is:
  C:\DPUMP\FULLDB_01.DMP
  C:\DPUMP\FULLDB_02.DMP
  C:\DPUMP\FULLDB_03.DMP
  C:\DPUMP\FULLDB_04.DMP
  C:\DPUMP\FULLDB_05.DMP
  C:\DPUMP\FULLDB_06.DMP
  C:\DPUMP\FULLDB_07.DMP
  C:\DPUMP\FULLDB_08.DMP
Job "ATOORPU"."SYS_EXPORT_FULL_01" successfully completed at Tue May 17 14:27:13 2016 elapsed 0 00:05:36

Restore schema into a temp SCOTT1 schema:


Now that I have completed my full DB backup. I want to restore just SCOTT schema from full backup into a temp schema called SCOTT1. For this I will need to pass two parameters
1. schemas = Schema name you want to import
2. remap_schema= This is to remap that schema to a temp SOCTT1 schema


F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT remap_schema=SCOTT:SCOTT1

Import: Release 11.2.0.4.0 - Production on Tue May 17 14:57:21 2016

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

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
Master table "ATOORPU"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT remap_schema=SCOTT:SCOTT1
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCOTT1"."DEPT"                             4.976 KB       4 rows
. . imported "SCOTT1"."EMP"                              5.625 KB      14 rows
. . imported "SCOTT1"."SALGRADE"                         4.890 KB       5 rows
. . imported "SCOTT1"."BONUS"                                0 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 17 14:57:34 2016 elapsed 0 00:00:08


Restore SCOTT into SCOTT schema and replace exiting tables :

If you want to restore it with same schema SCOTT and replace existing tables use this


impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT ABLE_EXISTS_ACTION=REPLACE


If you want to restore it with same schema SCOTT but append (add) rows to existing tables use this
impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT ABLE_EXISTS_ACTION=APPPEND

restore single table from expdp full backup

Lets take a full Backup:


F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8


Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016

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

Password:

UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

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

Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8


.

.

.

.

.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

. . exported "SCOTT1"."DEPT"                             4.976 KB       4 rows

. . exported "SCOTT1"."EMP"                              5.625 KB      14 rows

. . exported "SCOTT1"."SALGRADE"                         4.890 KB       5 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX

.

.

.

.

.

Master table "ATOORPU"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for ATOORPU.SYS_EXPORT_FULL_01 is:

  C:\DPUMP\FULLDB_01.DMP

  C:\DPUMP\FULLDB_02.DMP

  C:\DPUMP\FULLDB_03.DMP

  C:\DPUMP\FULLDB_04.DMP

  C:\DPUMP\FULLDB_05.DMP

  C:\DPUMP\FULLDB_06.DMP

  C:\DPUMP\FULLDB_07.DMP

  C:\DPUMP\FULLDB_08.DMP

Job "ATOORPU"."SYS_EXPORT_FULL_01" successfully completed at Tue May 17 14:27:13 2016 elapsed 0 00:05:36


****************************************************************************************************************

FROM THIS FULL BACKUP I WOULD LIKE TO RESTORE A TABLE FROM SCHEMA: SCOTT & TABLE: BONUS FULL BACKUP INTO SCHEMA.

****************************************************************************************************************

F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS


Import: Release 11.2.0.4.0 - Production on Tue May 17 16:04:51 2016


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

Password:


UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days


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

Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT"."BONUS"                                 0 KB       0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 17 16:05:05 2016 elapsed 0 00:00:08


****************************************************************************************************************

FROM THIS FULL BACKUP I WOULD LIKE TO RESTORE A TABLE FROM FULL BACKUP AND RESTORE IT FROM SCHEMA : SCOTT INTO ANOTHER SCHEMA : SCOTT1.

****************************************************************************************************************

F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS REMAP_SCHEMA=scott:scott1


Import: Release 11.2.0.4.0 - Production on Tue May 17 16:20:31 2016


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

Password:


UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days


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

Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log tables=SCOTT.BONUS REMAP_SCHEMA=scott:scot

1

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT1"."BONUS"                                0 KB       0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 17 16:20:45 2016 elapsed 0 00:00:08

Exporting partitions from a Partitioned tables

Exporting few partitions from a Partitioned table:

There might scenerios where you dont want to export the entire partitioned table for backup purposes. In this situations you can use simple expdp to do that.. Here is an example of

 Here is a sample expdp cmd to export part of entire partitioned tables.

I have a table named AUDIT_LOGONS in my database, which has partitions as below image ,

I want to export a part of it. partitions AUDIT_LOGONS1,AUDIT_LOGONS2 only


oracle@Linux1 $ expdp username/password directory=DPUMP dumpfile=AUDIT_PART1.dmp logfile=AUDIT_PART1.log TABLES=arvind.AUDIT_LOGONS:AUDIT_LOGONS4,arvind.AUDIT_LOGONS:AUDIT_LOGONS3


Export: Release 11.2.0.4.0 - Production on Mon Oct 12 16:29:59 2015

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

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 "arvind"."SYS_EXPORT_TABLE_01":  arvind/******** directory=DPUMP dumpfile=AUDIT_PART1.dmp logfile=AUDIT_PART1.log TABLES=arvind.AUDIT_LOGONS:AUDIT_LOGONS4,arvind.AUDIT_LOGONS:AUDIT_LOGONS3
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 120 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
:
:
:
:
Dump file set for arvind.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/AUDIT_PART1.dmp
Job "arvind"."SYS_EXPORT_TABLE_01" successfully completed at Mon Oct 12 16:32:02 2015 elapsed 0 00:01:56

Export table data with filtered clause - Query based export (oracle expdp)

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For Example: 

If you want to query all records of employees for a particular department you will use:

SELECT *
FROM HR.EMPLOYEES
WHERE dept = 10;


EXPDP UTILITY CMD :

$ expdp HR/XXXXX@ORCL directory=DPUMP dumpfile=TEST_EMP.dmp logfile=TEST_EMP.log include="HR.EMPLOYEES:\" in (where dept =10)\""



NORMAL EXPORT UTILITY CMD :

$ exp HR/XXXXX@ORCL file=/u01/app/oracle/dpump/TEST_EMP.dmp log=/u01/app/oracle/dpump/TEST_EMP.log tables=HR.EMPLOYEES query='"where dept =10"'

ORA-31633: unable to create master table ".SYS_IMPORT_FULL_05"

Today I encountered a problem while importing a scehma into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error.

 ORA-31633: unable to create master table

[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

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
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges

I tried again and again same error. Then I checked may be it is missing some grants, I havegranted  sysdba privillage to SAM now and tried.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

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> grant sysdba to SAM;

I tried again.

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2

Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014

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
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.



Then I realized that it might be missing create table permission and granted that permission to user SAM.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014

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> grant create table to SAM;

Grant succeeded.

Now tried again :

[oracle@orcl dpump]$ impdp SAM/SAM  directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2


 Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014

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
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01":  sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Possible Solutions :

•  It might be a simple missing of create table if you are trying to import it through a user.

• Try to check if there is already a table with the name oracle is trying to create table.

• Check if the job name being used is already created in database.

IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE

 

IMPDP  TABLE_EXISTS_ACTION PARAMETER EXPLAINED

 Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.

When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.

$ impdp help=y

    TABLE_EXISTS_ACTION
    Action to take if imported object already exists.
    Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

    SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.

    APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.

    TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump

    REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the  CONTENT=DATA_ONLY for the impdp.

   
Method to Import only rows does not exist in the target table
See some examples here.

In this example lets use abc table in my schema (ATOORPU)

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

I took the data pump dump export EXPDP of employee table.

oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc

Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC"                             5.921 KB       6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36


oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014

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


   ------ TABLE_EXISTS_ACTION=SKIP  ------

In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.

oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42


    ------     TABLE_EXISTS_ACTION=APPEND    ------

I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.

SQL> delete from employee;

4 rows deleted.

SQL> insert into abc (select * from abc_bak);

4 rows created.

SQL> commit;
SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12

$  impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42

Now 4 more rows appended to the table. Lets verify that

SQL> select * from abc;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ARVd                                   70 20-DEC-12
ARVIND2                                69 20-DEC-12
ATOORPUe                               64 19-DEC-12
BIf                                    63 19-DEC-12
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12

8 rows selected.

        ------ TABLE_EXISTS_ACTION=TRUNCATE     ------ 

    Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.

$   impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46


SQL >  select * from abc


USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
eds                                    44 19-DEC-12
rew                                    33 19-DEC-12
ARV                                    70 20-DEC-12
ARVIND                                 69 20-DEC-12
ATOORPU                                64 19-DEC-12
BI                                     63 19-DEC-12


        ------ TABLE_EXISTS_ACTION=REPLACE    ------


This option drop the current table in the database and the import recreate the new table as in the dumpfile.

$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01":  atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC"                             5.921 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07


Now if you check the last_ddl_time for the table it would be the same as the import time.


SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';


OBJECT_NAME                    CREATED          
--------------------------             -------------------
ABC                                        24-10-2014 09:42:06

Export/Zip and Unzip/Import using UNIX Pipes

Export/Zip and Unzip/Import using UNIX Pipes

If you are creating and using large dump (.dmp) files with Oracle's export (exp) and import (imp) utilities, you can compress and uncompress the dump file as needed using gzip and gunzip (from GNU) or the unix compress and uncompress utilities.  One problem with this is that you will still have to have the large dump file on your disk at some point, which you may or may not have room for, and you may run up against the 2 Gig file size limit on some machines and Oracle versions.  Instead of doing the export and zip separately, creating an interim dump file (or doing unzip and import), unix has the ability to pipe the output from one program (such as exp) as input to another program (such as gzip) as they both run in parallel, without having to run the programs sequentially and without having to create interim files.
To do this, you would create a pipe (using the unix mknod command), run the programs (in either order), with the first one in the background (using "&" at the end), which use that pipe like a file, and then remove the pipe.   Below shows a full export to a zipped dump file:


Below shows a full export with that zipped dump file:

mknod /u02/backup/dpump/exp_pipe p
gzip -cNf </u02/backup/dpump/exp_pipe>prod.dmp.gz &
exp system/oracle file=/u02/backup/dpump/exp_pipe full=y compress=n log=prod.dmp.log
rm -f /u02/backup/dpump/exp_pipe


Below shows a full import with that zipped dump file:

    mknod /u02/backup/dpump/imp_pipe p
    gunzip -c prod.dmp.gz >/u02/backup/dpump/imp_pipe &
    imp system/oracle file=/u02/backup/dpump/imp_pipe fromuser=scott touser=scott ignore=y buffer=1024000 commit=y log=prod_imp.log
    rm -f /u02/backup/dpump/imp_pipe

Example Data Pump Export script for Linux/Solaris

Data Pump Export script for Linux/Solaris


Data Pump is a command-line utility for importing and exporting objects like user tables and pl/sql source code from a Oracle database. It’s new since Oracle 10g, and it’s a better alternative for the “old” exp/imp utilities. However, do not use Data Pump to replace a full physical database backup with RMAN. Complete point-in-time recovery is not possible with Data Pump. Therefore, it should only be used for data migrations or in conjunction with RMAN.
In this blog post, I will show you how you can create a script to execute and schedule a full Data Pump export on Linux.
First, we need to define a directory object. This is an alias for a file system folder that we will need in the Data Pump script. Execute with user SYS as SYSDBA:

create directory expdir as '/expdir';
select * from dba_directories;

Note: make sure user “oracle” has write permissions on the file system folder used for the Data Pump export files (in this case: /expdir).
Next, we will create a database user that will be used for the export. This user will at least need the “EXP_FULL_DATABASE” role, CREATE SESSION and CREATE TABLE rights, and read/write access on the directory object we previously created.

Note: do NOT use “SYS as SYSDBA” for the export, this user should only be used when requested by Oracle support!

CREATE USER EXPORT
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE "UNLIMITED PASSWORD EXPIRATION"
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO EXPORT;
GRANT CREATE TABLE TO EXPORT;
ALTER USER EXPORT QUOTA UNLIMITED ON USERS;
GRANT EXP_FULL_DATABASE TO EXPORT;
GRANT READ, WRITE ON DIRECTORY EXPDIR TO EXPORT;

Now we will create the Linux shell script:

$ vi ora_expdp_full.sh 

 #!/bin/sh

# script to make full export of Oracle db using Data Pump

STARTTIME=`date`
export ORACLE_SID=oratst
export ORACLE_HOME=`cat /etc/oratab|grep ^${ORACLE_SID}:|cut -d':' -f2`
export EXPLOG=expdp_${ORACLE_SID}.log
export EXPDIR=/expdir
export PATH=$PATH:$ORACLE_HOME/bin
DATEFORMAT=`date +%Y%m%d`
STARTTIME=`date`

# Data Pump export
expdp export/password content=ALL directory=expdir 

dumpfile=expdp_`echo $ORACLE_SID`_%U_`echo $DATEFORMAT`.dmp 

filesize=2G full=Y logfile=$EXPLOG nologfile=N parallel=2

ENDTIME=`date`
SUBJECT=`hostname -s`:$ORACLE_SID:`tail -1 $EXPDIR/$EXPLOG`
echo -e "Start time:" $STARTTIME "\nEnd time:" $ENDTIME | mail -s "$SUBJECT" dba@mydomain.com

This script will create 2GB export files and dynamically append the date to them. So, in this case, the first file will be “expdp_oratst_01_20120503.dmp”, the second one “expdp_oratst_02_20120503.dmp”, and so on. Finally, a mail will be sent to the DBA with as mail subject the last line of the export log file, and as mail body the start and end time.
Note: You need to replace the ORACLE_SID and EXPDIR variables in the script by the ones suitable for your environment.
Make sure only the owner of the script has read and write access on it:
$ chmod 700 ora_expdp_full.sh
Finally, we can schedule the script with the Linux utility cron:
$ crontab -e
Add the following lines:

# Daily logical export
00 23 * * * /home/oracle/scripts/export/ora_expdp_full.sh 1>/dev/null 2>&1

expdp full backup thru scheduler

Scheduler and data pump expdb

Oracle Enterprise Edition 11.2.0.2
Linux x86_64

I would like to routinely export some or all of my database as part of my DR strategy. I have always used crontab to call my expdp script on a routine basis. Now I want to change that from using crontab to using Oracle Scheduler.

First I will review the shell script and parfile I use. Then I will use Oracle Scheduler to schedule the execution of this shell script.

This is my full export but with subtle modifications I also export specific schemas on a more frequent basis. I am using csh for this shell script.

This is expdp_full.sh

#!/bin/csh
# set the environment
setenv ORACLE_HOME /app/oracle/product/11.2.0.2
setenv ORACLE_SID grims
setenv ORACLE_BASE /app/oracle
setenv LOGFILE expdp_full.log
setenv DUMPFILE expdp_full.dmp
setenv DMPDIR /scripts/expdp

# expdp does not like files it wants
# to create lying around so I will clean# any up just in case.

# check for existing logfile and
# remove if found.
if ( -e ${DMPDIR}/${LOGFILE} ) then
rm ${DMPDIR}/${LOGFILE}
endif

# check for existing dump file and
# remove if found.if ( -e ${DMPDIR}/${DUMPFILE} ) then
rm ${DMPDIR}/${DUMPFILE}
endif

# change to the working directory
chdir ${DMPDIR}

# call expdp and use the full path to it.
/app/oracle/product/11.2.0.2/bin/expdp parfile=expdp_full.par DUMPFILE=${DUMPFILE} LOGFILE=${LOGFILE} DIRECTORY=DMPDIR

# once the expdp is complete zip it to save space.
# You can use the compress command in UNIX.
zip ${DUMPFILE}.`date +%m%d`.Z ${DUMPFILE}
# Get the log status for email.
set STATUS=`grep Job ${DMPDIR}/${LOGFILE}`
# mail results.
mailx -s "${ORACLE_SID} on `uname -n` FULL export '$STATUS'" email@company.com < ${DMPDIR}/${LOGFILE}

# delete the existing dump file. Zip does not remove it.
if ( -e ${DMPDIR}/${DUMPFILE} ) then
rm ${DMPDIR}/${DUMPFILE}
endif

# rename the log file to preserve it.
if ( -e ${DMPDIR}/${LOGFILE} ) then
mv ${DMPDIR}/${LOGFILE} ${DMPDIR}/${LOGFILE}.`date +%m%d`
endif
exit


The par files. This is both the full expdp and another with specific schemas.
expdp_full.parUSERID="/ as sysdba"
FULL=Y

expdp_users.par
USERID="/ as sysdba"SCHEMAS=SCOTT,HR,APPS

Of course you can stop here and use crontab to execute expdp_full.sh, but I am going to now use the Oracle Scheduler.

First I will create the scheduled job to run every morning at 2:05am. Once it runs successfully for a few days and I like the out put I will update the schedule to run this task once a week.

From sqlplus enter the new scheduled job.

BEGIN
dbms_scheduler.CREATE_JOB (
job_name => 'expdp_full',
job_type => 'EXECUTABLE',
job_action => '/oracle/scripts/expdp/expdp_full.sh',
start_date => to_date('08/14/2013 14:00:00','mm/dd/yyyy hh24:mi:ss'),
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=5;BYSECOND=0',
end_date => null,
enabled => TRUE,
comments => 'expdp full database');
END;

To update the frequency interval to once a week, Friday morning at 2:05am:

BEGIN
sys.dbms_scheduler.disable( '"SYS"."EXPDP_FULL"' );
sys.dbms_scheduler.set_attribute( name => '"SYS"."EXPDP_FULL"', attribute => 'repeat_interval', value => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=2;BYMINUTE=5;BYSECOND=0');
sys.dbms_scheduler.enable( '"SYS"."EXPDP_FULL"' );
END;

Here are some useful queries to examine the scheduler jobs.

SQL> SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name = 'EXPDP_FULL';
JOB_NAME STATE
------------------------------ ---------------
EXPDP_FULL SCHEDULED

SQL> SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;
no rows selected

The following will show the history of a specific job and whether it succeeded or not. Just because the job succeeded does not mean the script ran without errors. The next query is an example where this first query said SUCCEEDED yet the script failed to run.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name,
job_class, operation, status FROM USER_SCHEDULER_JOB_LOG
WHERE job_name = 'EXPDP_FULL' ORDER BY log_date;

Show more detail about an job history. This is the output before I gave the full path in my shell script to extdp.

SELECT to_char(log_date, 'DD-MON-YY HH24:MM:SS') TIMESTAMP, job_name, status,
SUBSTR(additional_info, 1, 40) ADDITIONAL_INFO
FROM user_scheduler_job_run_details
WHERE job_name = 'EXPDP_FULL'
ORDER BY log_date;

14-AUG-13 14:02:02 EXPDP_FULL SUCCEEDED STANDARD_ERROR="expdp: Command not found

datapump basic's

CONN / AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;

Note. Data Pump is a server-based technology, so it typically deals with directory objects pointing to physical directories on the database server. It does not write to the local file system on your client PC.


Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
For example output files see expdpEMP_DEPT.log and impdpEMP_DEPT.log.

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.


Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
For example output files see expdpSCOTT.log and impdpSCOTT.log.


Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log




CONTENT parameter:

expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp CONTENT=METADATA_ONLY


The following example shows an export operation that is assigned a job name of exp_job:

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=exp_job
NOLOGFILE=y


The following is an example of using the PARALLEL parameter:

> expdp hr/hr DIRECTORY=dpump_dir1 LOGFILE=parallel_export.log
JOB_NAME=par4_job DUMPFILE=par_exp%u.dmp PARALLEL=4


datapump network link:

expdp hr/hr DIRECTORY=dpump_dir1 NETWORK_LINK=source_database_link
  DUMPFILE=network_export.dmp LOGFILE=network_export.log


The contents of the emp_query.par file are as follows:

QUERY=employees:'"WHERE department_id > 10 AND salary > 10000"'
NOLOGFILE=y
DIRECTORY=dpump_dir1
DUMPFILE=exp1.dmp


The following is an example of using the SCHEMAS parameter. Note that user hr is allowed to specify more than one schema because the EXP_FULL_DATABASE role was previously assigned to it for the purpose of these examples.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe



The following is an example of using the STATUS parameter.

> expdp hr/hr DIRECTORY=dpump_dir1 SCHEMAS=hr,sh STATUS=300

This example will export the hr and sh schemas and display the status of the export every 5 minutes (60 seconds x 5 = 300 seconds)



The following is an example of using the TABLESPACES parameter. The example assumes that tablespaces tbs_4, tbs_5, and tbs_6 already exist.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp
TABLESPACES=tbs_4, tbs_5, tbs_6


The following is an example of using the TRANSPORT_FULL_CHECK parameter. It assumes that tablespace tbs_1 exists.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.log


The following is an example of using the TRANSPORT_TABLESPACES parameter in a file-based job (rather than network-based). The tablespace tbs_1 is the tablespace being moved. This example assumes that tablespace tbs_1 exists and that it has been set to read-only.

> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.log



Syntax and Description

CONTINUE_CLIENT

In logging mode, status is continually output to the terminal. If the job is currently stopped, then CONTINUE_CLIENT will also cause the client to attempt to start the job.

Example

Export> CONTINUE_CLIENT
EXIT_CLIENT

Purpose

Stops the export client session, exits Export, and discontinues logging to the terminal, but leaves the current job running.

Syntax and Description

EXIT_CLIENT

Because EXIT_CLIENT leaves the job running, you can attach to the job at a later time. To see the status of the job, you can monitor the log file for the job or you can query the USER_DATAPUMP_JOBS view or the V$SESSION_LONGOPS view.

Example

Export> EXIT_CLIENT



Example 2-1 Performing a Table-Mode Export

expdp hr/hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=y



Estimating Disk Space Needed in a Table-Mode Export

Example 2-3 shows the use of the ESTIMATE_ONLY parameter to estimate the space that would be consumed in a table-mode export, without actually performing the export operation. Issue the following command to use the BLOCKS method to estimate the number of bytes required to export the data in the following three tables located in the human resource (hr) schema: employees, departments, and locations.

Example 2-3 Estimating Disk Space Needed in a Table-Mode Export

> expdp hr/hr DIRECTORY=dpump_dir1 ESTIMATE_ONLY=y TABLES=employees,
departments, locations LOGFILE=estimate.log


Example 2-5 shows a full database Export that will have 3 parallel worker processes.

Example 2-5 Parallel Full Export

> expdp hr/hr FULL=y DUMPFILE=dpump_dir1:full1%U.dmp, dpump_dir2:full2%U.dmp
FILESIZE=2G PARALLEL=3 LOGFILE=dpump_dir1:expfull.log JOB_NAME=expfull