Netsoftmate

Tag: DB level export and import

  • Data Pump expdp/impdb Scenarios

    Overview

    Datapump introduce in 10g which is very powerful utility to perform the both load and unload data using external dump files.Oracle Data Pump technology consists of two components: the Data Pump Export utility, to unload data objects from a database, and the Data Pump Import utility, to load data objects into a database. You access the two Data Pump utilities through a pair of clients called expdp and impdp.As their names indicate, the first of these corresponds to the Data Pump Export utility and the latter
    to the Data Pump Import utility. You can control both Data Pump Export and Import jobs with the help of several parameters.


    In this article we will demonstrate different Data Pump (expdp/impdb) scenarios.

    Prerequisites

    • Creating a Database Directory
    • Create a directory named expdp_dir and
      specifies that it is to map to the filesystem/location and physical location on
      disk:
    SQL> create
    directory expdp_dir as ‘ /orahm/app/oracle/admin/db01/dpdump’;

    • Granting Access to the Directory
    • Grant
      permissions on the database-directory object to a user that wants to use Data
      Pump:-
    SQL> grant
    read, write on directory expdp_dir to sys;

    Table level


    Export Source DB

    SQL> select
    * from dba_directories;
    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    —————————–
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
    logfile=exp-tab.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp  oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $impdp
    directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
    logfile=exp-tab.log



    Schema level
    Below example
    to take the schema level export and import.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
    $expdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    $impdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log

    Check invalid
    object and compile if required.

    select
    object_name from dba_objects where owner=’ABC’ and status=’INVALID’ ;
    compiling
    invalid object.

    ALTER PROCEDURE
    <PROCEDURE_NAME> COMPILE;

    Database level
    Below example
    for full DB export and import.


    Export Source DB

    SQL> select
    * from dba_directories;
    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
    Check the
    tablespace name and size to match same on target db

    select    a.TABLESPACE_NAME,
    ROUND(a.BYTES/1024000)
    “Total (MB)”,
    ROUND(b.BYTES/1024000)
    “Free (MB)”,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% USED”,
    100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% FREE”
    from
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES
    from    dba_data_files
    group   by TABLESPACE_NAME
    )
    a,
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES ,
    max(BYTES)
    largest
    from    dba_free_space
    group   by TABLESPACE_NAME
    )b
    where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
    and
    a.TABLESPACE_NAME like ‘%’
    order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

    $expdp
    directory= DATA_PUMP_DIR full=y dumpfile=exp-full.dmp logfile=exp-full.log

    scp /orahm/app/oracle/admin/db01/dpdump/
    exp-full.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/


    Import Target DB


    Make target db
    contain all the tablespace and available free space to accommodate the source
    object into target DB

    Sql> select    a.TABLESPACE_NAME,
    ROUND(a.BYTES/1024000)
    “Total (MB)”,
    ROUND(b.BYTES/1024000)
    “Free (MB)”,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% USED”,
    100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% FREE”
    from
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES
    from    dba_data_files
    group   by TABLESPACE_NAME
    )
    a,
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES ,
    max(BYTES)
    largest
    from    dba_free_space
    group   by TABLESPACE_NAME
    )b
    where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
    and
    a.TABLESPACE_NAME like ‘%’
    order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

    $impdp
    directory= DATA_PUMP_DIR full=y dumpfile=exp-full.dmp logfile=imp-full.log

    Check the
    invalid object and compile.
    Sql>select
    object_name,owner,status from dba_objects where status=’INVALID’;
    Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
    Sql>select
    object_name,owner,status from dba_objects where status=’INVALID’;

    Using Parameter file
    Below export
    and import example using parameter file.


    Export Source DB

    SQL> select
    * from dba_directories;
    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
    Check the
    tablespace name and size to match same on target db

    select    a.TABLESPACE_NAME,
    ROUND(a.BYTES/1024000)
    “Total (MB)”,
    ROUND(b.BYTES/1024000)
    “Free (MB)”,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% USED”,
    100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% FREE”
    from
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES
    from    dba_data_files
    group   by TABLESPACE_NAME
    )
    a,
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES ,
    max(BYTES)
    largest
    from    dba_free_space
    group   by TABLESPACE_NAME
    )b
    where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
    and
    a.TABLESPACE_NAME like ‘%’
    order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;
    Create
    parameter file as below under data pump directory.

    vi full_db.par
    directory=DATA_PUMP_DIR
    full=y
    dumpfile=exp-full.dmp
    logfile=exp-full.log

    Take export
    using par file.
    $ expdp
    parfile=full_db.par

    scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp  oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/


    Import Target DB


    Make target db
    contain all the tablespace and available free space to accommodate the source
    object into target DB

    Sql> select    a.TABLESPACE_NAME,
    ROUND(a.BYTES/1024000)
    “Total (MB)”,
    ROUND(b.BYTES/1024000)
    “Free (MB)”,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% USED”,
    100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% FREE”
    from
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES
    from    dba_data_files
    group   by TABLESPACE_NAME
    )
    a,
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES ,
    max(BYTES)
    largest
    from    dba_free_space
    group   by TABLESPACE_NAME
    )b
    where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
    and
    a.TABLESPACE_NAME like ‘%’
    order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;
                                   
    Create
    parameter file as below under data pump directory.


    vi full_db.par
    directory=DATA_PUMP_DIR
    full=y dumpfile=exp-full.dmp
    logfile=imp-full.log


    Perform import using parameter file.
    $impdp parfile=full_db.par

    Check the
    invalid object and compile.
    Sql>select
    object_name,owner,status from dba_objects where status=’INVALID’;
    Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
    Sql>select
    object_name,owner,status from dba_objects where status=’INVALID’;


    Data Pump Export/Import more examples

    • Using parallel option
    Using parallel
    option we can make export faster. It generate more dump files depends on
    parallel option during export.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile=exp-schema_%Udmp logfile= exp-schmea.log
    parallel=2

    scp /orahm/app/oracle/admin/db01/dpdump/exp-schema_%U.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    $impdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema_%U.dmp logfile=imp-schema.log

    Check invalid
    object and compile if required.

    select
    object_name from dba_objects where owner=’ABC’ and status=‘INVALID’;
    Compiling
    invalid object.

    ALTER PROCEDURE
    <PROCEDURE_NAME> COMPILE;

    • Using compress option
    Using compress
    option we can reduce the size of dump files.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR compression=all schemas=ABC dumpfile= exp-schema.dmp
    logfile= exp-schmea.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    $impdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log

    Check invalid
    object and compile if required.

    select
    object_name from dba_objects where owner=’ABC’ and status=‘INVALID’;
    Compiling
    invalid object.

    ALTER PROCEDURE
    <PROCEDURE_NAME> COMPILE;


    Creating DDL file
    Using Sqlfile
    option we can only extract the DDL without data.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
    logfile=exp-tab.log sqlfile=ddl-script.sql

    scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp
    ddl-script.sql oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    Import only DDL
    $impdp
    directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
    logfile=exp-tab.log sqlfile=ddl-script.sql

    • Cloning user
      (remap_schema)
    Using
    Remap_schema option we can import the object from one schema to other schema.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR compression=all schemas=ABC dumpfile= exp-schema.dmp
    logfile= exp-schmea.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    $impdp
    directory= DATA_PUMP_DIR remap_schema=ABC:XYZ dumpfile= exp-schema.dmp logfile=imp-schema.log

    Check invalid
    object and compile if required.

    select
    object_name from dba_objects where owner=’XZY’ and status=‘INVALID’;

    Compiling
    invalid object.

    ALTER PROCEDURE
    <PROCEDURE_NAME> COMPILE;

    • Cloning table (remap_table)
    Remap_table
    option is to create the table with new name during import to avoid overwriting
    existing table in target DB.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR tables=ABC.table1 dumpfile=exp-tab.dmp logfile=exp-tab.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    Remap table to
    new name.


    $impdp
    directory= DATA_PUMP_DIR tables=ABC.table1 remap_table= ABC.table1:table2 dumpfile=exp-tab.dmp
    logfile=exp-tab.log

    • Importing when objects Already Exist
    Using the TABLE_EXISTS_ACTION
    option we can import the object if the object already exist on target.

    TABLE_EXISTS_ACTION=[SKIP
    | APPEND | TRUNCATE | REPLACE]
    SKIP leaves the
    table as is and moves on to the next object. This is not a valid option if the CONTENT
    parameter is set to DATA_ONLY.
    APPEND loads
    rows from the source and leaves existing rows unchanged.
    TRUNCATE
    deletes existing rows and then loads rows from the source.
    REPLACE drops
    the existing table and then creates and loads it from the source. This is not a
    valid option if the CONTENT parameter is set to DATA_ONLY.

    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile= exp-schmea.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import


    $impdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log
    table_exists_action==[SKIP | APPEND | TRUNCATE | REPLACE]

    Check invalid
    object and compile if required    .

    select
    object_name from dba_objects where owner=’ABC’ and status=’INVALID’ ;
    compiling
    invalid object.

    ALTER PROCEDURE
    <PROCEDURE_NAME> COMPILE;

    • Exclude option
    EXCLUDE
    parameter is used, all objects except those specified by it will be included in
    the export/import


    Export Source DB

    SQL> select
    * from dba_directories;
    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump
    Check the tablespace
    name and size to match same on target db

    select    a.TABLESPACE_NAME,
    ROUND(a.BYTES/1024000)
    “Total (MB)”,
    ROUND(b.BYTES/1024000)
    “Free (MB)”,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% USED”,
    100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% FREE”
    from
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES
    from    dba_data_files
    group   by TABLESPACE_NAME
    )
    a,
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES ,
    max(BYTES)
    largest
    from    dba_free_space
    group   by TABLESPACE_NAME
    )b
    where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
    and
    a.TABLESPACE_NAME like ‘%’
    order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;

    Create
    parameter file as below under data pump directory.

    vi full_db.par
    directory=DATA_PUMP_DIR
    full=y
    dumpfile=exp-full.dmp
    logfile=exp-full.log
    exclude=schema:”IN
    (‘SYS’,’SYSTEM’,’ANONYMOUS’,’DBSNMP’,’DIP’,’EXFSYS’,’ORACLE_OCM’,’OUTLN’,’WMSYS’,’XDB’)”

    Take export
    using par file.

    $ expdp
    parfile=full_db.par

    scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    Make target db
    contain all the tablespace and available free space to accommodate the source
    object into target DB

    Sql> select    a.TABLESPACE_NAME,
    ROUND(a.BYTES/1024000)
    “Total (MB)”,
    ROUND(b.BYTES/1024000)
    “Free (MB)”,
    round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% USED”,
    100-round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)
    “% FREE”
    from
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES
    from    dba_data_files
    group   by TABLESPACE_NAME
    )
    a,
    (
    select  TABLESPACE_NAME,
    sum(BYTES)
    BYTES ,
    max(BYTES)
    largest
    from    dba_free_space
    group   by TABLESPACE_NAME
    )b
    where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
    and
    a.TABLESPACE_NAME like ‘%’
    order      by ((a.BYTES-b.BYTES)/a.BYTES) desc;
                                   
    Create
    parameter file as below under data pump directory.

    vi full_db.par
    directory=DATA_PUMP_DIR
    full=y
    dumpfile=exp-full.dmp
    logfile=imp-full.log

    Perform import using parameter file.
    $impdp parfile=full_db.par

    Check the
    invalid object and compile.
    Sql>select
    object_name,owner,status from dba_objects where status=’INVALID’;
    Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
    Sql>select
    object_name,owner,status from dba_objects where status=’INVALID’;

    • Include option
    INCLUDE
    parameter is used, only those objects specified by it will be included in the
    export/import.


    Export Source DB

    SQL> select
    * from dba_directories;

    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR schemas=ABC 
    include=TABLE:”IN(“EMP’,’DEPT’) dumpfile= exp-schema.dmp logfile= exp-schmea.log

    scp /orahm/app/oracle/admin/db01/dpdump/exp-schema.dmp
    oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    $impdp
    directory= DATA_PUMP_DIR schemas=ABC dumpfile= exp-schema.dmp logfile=imp-schema.log

    Check invalid
    object and compile if required.

    select
    object_name from dba_objects where owner=’ABC’ and status=’INVALID’ ;
    compiling
    invalid object    .

    ALTER PROCEDURE
    <PROCEDURE_NAME> COMPILE;

    • Creating a Consistent
      Export
    CONSISTENT=Y
    parameter to indicate the export should be consistent to a point in time


    Export Source DB


    SQL> select
    * from dba_directories;
    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    $expdp
    directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
    logfile=exp-tab.log consistent=y

    scp /orahm/app/oracle/admin/db01/dpdump/exp-tab.dmp  oracle@targetdb:/orahm/app/oracle/admin/db01/dpdump/

    Import Target DB


    $impdp
    directory= DATA_PUMP_DIR tables=owner.table_name dumpfile=exp-tab.dmp
    logfile=exp-tab.log

    • Network based import
    NETWORK_LINK
    parameter identifies a database link to be used as the source for a network
    export/import.


    Export Source DB


    SQL> select
    * from dba_directories;
    OWNER       DIRECTORY_NAME        DIRECTORY_PATH
    ————————————————————————-
    SYS         DATA_PUMP_DIR        /orahm/app/oracle/admin/db01/dpdump

    Create the db
    link on on local db which is point to source db for export task.It will export
    the source db and put the dumpfile in dump directory

    sql>CREATE DATABASE
    LINK remote_expdp CONNECT TO sys IDENTIFIED BY sys123 USING ‘DB01’;

    $expdp
    directory= DATA_PUMP_DIR tables=owner.table_name network_link=remote_expdp dumpfile=exp-tab.dmp
    logfile=exp-tab.log

    Import Target DB


    Create the db
    link on local DB which is point to source db. Here it will directly import the
    data using netwok link option without dumpfile of source db.

    sql>CREATE DATABASE
    LINK remote_impdp CONNECT TO sys IDENTIFIED BY sys123 USING ‘DB01’;

    $impdp
    directory= DATA_PUMP_DIR tables=owner.table_name logfile=exp-tab.log network_link=remote_impdp


    Conclusion


    In this article we have learnt different Data Pump (expdp/impdb) scenarios.