Infrastructure at your Service

Oracle Team

Oracle 12.2 – How to run a consistent full database export with dbms_datapump and parallel degree of 8

By William Sescu

Nowadays, since the cloud is becoming more and more important, the PL/SQL API’s become more and more important too. Fortunately, Oracle has quite a lot of them. E.g. How do you run a Data Pump export if you have no ssh connectivity to the server? You could use the old exp tool, which is still available even with Oracle 12.2, or you can use DBMS_DATAPUMP. The Data Pump API is quite good documented in the following books:

Database Utilities
https://docs.oracle.com/database/122/SUTIL/using-ORACLE_DATAPUMP-api.htm#SUTIL600

Database PL/SQL Packages and Types Reference
https://docs.oracle.com/database/122/ARPLS/DBMS_DATAPUMP.htm#ARPLS66050

But you might find some useful stuff in the $ORACLE_HOME/rdbms/admin/dbmsdp.sql as well.

In this little how to, I would like to show how to create a consistent full database export (parallel 8) with
the Data Pump API.

There are a only a few steps involved to get the job done.

1. Create a directory and grant the necessary privileges to user HR
2. Grant the DATAPUMP_EXP_FULL_DATABASE role to user HR
3. Execute the Data Pump job with DBMS_DATAPUMP
4. Monitor the Data Pump job
5. Optionally, do some cleanup

 

1.) Let’s start with the directory.

SQL> CREATE OR REPLACE DIRECTORY DATAPUMP_DIR AS '/u01/app/oracle/admin/DBIT122/dpdump';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY DATAPUMP_DIR TO HR;

Grant succeeded.

2.) Now we grant the DATAPUMP_EXP_FULL_DATABASE role to the HR user

SQL> GRANT DATAPUMP_EXP_FULL_DATABASE TO HR;

Grant succeeded.

Please be aware that the DATAPUMP_EXP_FULL_DATABASE role affects only export operations. It allows the user HR to run these operations:

  • Perform the operation outside of the scope of their schema
  • Monitor jobs that were initiated by another user
  • Export objects (for example, TABLESPACE definitions) that unprivileged users cannot reference

Without this role, you might run into the following error when doing a full export:

ERROR at line 1:
ORA-31631: privileges are required
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4932
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6844
ORA-06512: at line 6

 

3.) Now it’s time to run the Data Pump job. Be aware, that for a consistent export, you need to specify the FLASHBACK_TIME or FLASHBACK_SCN. In my case, I use the FLASHBACK_TIME and set it to the current SYSTIMESTAMP.

Ok. Let’s give it a try.

SQL> connect hr/hr
Connected.

SQL> @exp_datapump.sql
SQL> declare
  2        l_datapump_handle    NUMBER;  -- Data Pump job handle
  3        l_datapump_dir       VARCHAR2(20) := 'DATAPUMP_DIR';  -- Data Pump Directory
  4        l_status             varchar2(200); -- Data Pump Status
  5    BEGIN
  6        l_datapump_handle := dbms_datapump.open(operation => 'EXPORT',  -- operation = EXPORT, IMPORT, SQL_FILE
  7                                                job_mode =>'FULL',  -- job_mode = FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
  8                                                job_name => 'DBIT122 EXPORT JOB RUN 003',  -- job_name = NULL (default) or: job name (max 30 chars)
  9                                                version => '12'); -- version = COMPATIBLE (default), LATEST (dbversion), a value (11.0.0 or 12)
 10
 11            dbms_datapump.add_file(handle    => l_datapump_handle,
 12                               filename  => 'exp_DBIT122_%U.dmp',
 13                               directory => l_datapump_dir);
 14
 15        dbms_datapump.add_file(handle    => l_datapump_handle,
 16                               filename  => 'exp_DBIT122.log' ,
 17                               directory => l_datapump_dir ,
 18                               filetype  => DBMS_DATAPUMP.ku$_file_type_log_file);
 19
 20        dbms_datapump.set_parameter(l_datapump_handle,'CLIENT_COMMAND','Full Consistent Data Pump Export of DBIT122 with PARALLEL 8');
 21
 22            dbms_datapump.set_parameter(l_datapump_handle,'FLASHBACK_TIME','SYSTIMESTAMP');
 23
 24        dbms_datapump.set_parallel(l_datapump_handle,8);
 25
 26        dbms_datapump.start_job(handle => l_datapump_handle);
 27
 28        dbms_datapump.wait_for_job(handle => l_datapump_handle,
 29                                   job_state => l_status );
 30
 31        dbms_output.put_line( l_status );
 32
 33        end;
 34  /

PL/SQL procedure successfully completed.

SQL>

4.) In another window, you might want to monitor the status of your export job.

SQL> r
  1  select owner_name, job_name, rtrim(operation) "OPERATION",
  2         rtrim(job_mode) "JOB_MODE", state, attached_sessions
  3    from dba_datapump_jobs
  4   where job_name not like 'BIN$%'
  5*  order by 1,2

OWNER_NAME JOB_NAME                         OPERATION    JOB_MODE     STATE        ATTACHED_SESSIONS
---------- -------------------------------- ------------ ------------ ------------ -----------------
HR         DBIT122 EXPORT JOB RUN 003       EXPORT       FULL         EXECUTING                    1

Cool. If the job finished successfully, you will see 8 dump files, because we specified exp_DBIT122_%U.dmp as the file name, and one log file.

[email protected]:/u01/app/oracle/admin/DBIT122/dpdump/ [DBIT122] ls -l
total 4752
-rw-r----- 1 oracle oinstall  630784 Jun 13 10:29 exp_DBIT122_01.dmp
-rw-r----- 1 oracle oinstall 3321856 Jun 13 10:29 exp_DBIT122_02.dmp
-rw-r----- 1 oracle oinstall  180224 Jun 13 10:29 exp_DBIT122_03.dmp
-rw-r----- 1 oracle oinstall   57344 Jun 13 10:29 exp_DBIT122_04.dmp
-rw-r----- 1 oracle oinstall  430080 Jun 13 10:28 exp_DBIT122_05.dmp
-rw-r----- 1 oracle oinstall   20480 Jun 13 10:29 exp_DBIT122_06.dmp
-rw-r----- 1 oracle oinstall   28672 Jun 13 10:29 exp_DBIT122_07.dmp
-rw-r----- 1 oracle oinstall  176128 Jun 13 10:28 exp_DBIT122_08.dmp
-rw-r--r-- 1 oracle oinstall   11966 Jun 13 10:29 exp_DBIT122.log

5.) Finally, you might want to do some cleanup, in case you don’t need the dump files and the log files anymore. Or you start your export job with the REUSE_DUMPFILES=YES option. This option overwrites the destination dump files if they exist. In case you want to do the cleanup manually, you can use the ULT_FILE package.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122.log' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_01.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_02.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_03.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_04.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_05.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_06.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_07.dmp' );

PL/SQL procedure successfully completed.

SQL> exec utl_file.fremove( 'DATAPUMP_DIR', 'exp_DBIT122_08.dmp' );

PL/SQL procedure successfully completed.

SQL>

Conclusion

The PL/SQL API’s becomes more and more important, especially in cloud environments. It makes quite a lot of sense, from my point of view, to look closer into the one or the other. Especially the DBMS_DATAPUMP is an important one for moving data around.

 

 

7 Comments

  • Foued says:

    Thanks for the post

  • Hima says:

    Thank you for the post.
    However, I received the below errors. And it has all permissions; but not sure what’s wrong here. Appreciate your support..!!

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 902
    ORA-06512: at "SYS.DBMS_DATAPUMP", line 4758
     
     
    >select granted_role from dba_role_privs where grantee = 'XXXX';
    >
    GRANTED_ROLE
    --------------------------------------------------------------------------------------------------------------------------------
    CONNECT
    EXP_FULL_DATABASE
    IMP_FULL_DATABASE
    DATAPUMP_EXP_FULL_DATABASE
    RESOURCE
    DBA
    DATAPUMP_IMP_FULL_DATABASE
     
    7 rows selected.
     
    Elapsed: 00:00:00.02

  • Nanda says:

    Hi,

    I am unable to run the code as i am getting following error, I was trying to modify/rectify, but no luck

    ERROR at line 2:
    ORA-06550: line 2, column 3:
    PLS-00114: identifier ‘??L_DATAPUMP_HANDLE???’ too long
    ORA-06550: line 3, column 3:
    PLS-00114: identifier ‘??L_DATAPUMP_DIR????’ too long
    ORA-06550: line 3, column 58:
    PLS-00103: Encountered the symbol “VARCHAR2” when expecting one of the
    following:
    := . ( @ % ; not null range default character
    The symbol “:=” was substituted for “VARCHAR2” to continue.
    ORA-06550: line 4, column 3:
    PLS-00114: identifier ‘??L_STATUS??????’ too long
    ORA-06550: line 3, column 71:
    PLS-00103: Encountered the symbol “=” when expecting one of the following:
    . ( * % & = – + ; at in is mod remainder not rem
    or != or ~= >= <= and or like like2
    like4 likec between || multiset member submultiset
    ORA-06550: line 4, column 88:
    PLS-00103: Encountered the symbol “VARCHAR2” when expecting one of the
    following:
    := . ( @ % ; not null range default character
    The symbol “:=” was substituted for “VARCHAR2” to continue.
    ORA-06550: line 6, column 10:
    PLS-00103: Encountered the symbol “L_DATAPUMP_HANDLE” when expecting one of the
    following:
    := . ( @ % ;
    The symbol “.” was substituted for “L_DATAPUMP_HANDLE” to continue.
    ORA-06550: line 7, column 3:
    PLS-00114: identifier ‘??????????’ too long
    ORA-06550: line 7, column 3:
    PLS-00103: Encountered the symbol “??????????” when expecting one of the
    following:
    . ( ) , * @ % & = – + at in is mod remainder not rem =>
    or != or ~= >= <= and or like like2
    like4 likec between || multiset member submultiset
    The symbol “. was inserted before “??????????” to continue.
    ORA-06550: line 8, column 3:
    PLS-00114: identifier ‘??????????’ too long
    ORA-06550: line 8, column 3:
    PLS-00103: Encountered the symbol “??????????” when expecting one of the
    following:
    . ( ) , * @ % & = – + at in is mod remainder not rem =>
    or != or ~= >= <=

  • Hi all,

    concerning the errors

    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 79
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 902
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4758

    Make sure the user who is exporting has quota on his default tablespace (he needs to create a table temporarily there). E.g.

    alter user hr quota unlimited on users;

    Concerning the errors

    ERROR at line 2:
    ORA-06550: line 2, column 3:
    PLS-00114: identifier ‘??L_DATAPUMP_HANDLE???’ too long

    Make sure to copy/paste the PLSQL-block and remove the line-numbers. I.e. it should look like

    declare
    l_datapump_handle NUMBER; — Data Pump job handle
    l_datapump_dir VARCHAR2(20) := ‘DATAPUMP_DIR’; — Data Pump Directory
    l_status varchar2(200); — Data Pump Status
    BEGIN
    l_datapump_handle := dbms_datapump.open(operation => ‘EXPORT’, — operation = EXPORT, IMPORT, SQL_FILE
    job_mode =>’FULL’, — job_mode = FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
    job_name => ‘DBIT122 EXPORT JOB RUN 003′, — job_name = NULL (default) or: job name (max 30 chars)
    version => ’12’); — version = COMPATIBLE (default), LATEST (dbversion), a value (11.0.0 or 12)
    dbms_datapump.add_file(handle => l_datapump_handle,
    filename => ‘exp_DBIT122_%U.dmp’,
    directory => l_datapump_dir);
    dbms_datapump.add_file(handle => l_datapump_handle,
    filename => ‘exp_DBIT122.log’ ,
    directory => l_datapump_dir ,
    filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
    dbms_datapump.set_parameter(l_datapump_handle,’CLIENT_COMMAND’,’Full Consistent Data Pump Export of DBIT122 with PARALLEL 8′);
    dbms_datapump.set_parameter(l_datapump_handle,’FLASHBACK_TIME’,’SYSTIMESTAMP’);
    dbms_datapump.set_parallel(l_datapump_handle,8);
    dbms_datapump.start_job(handle => l_datapump_handle);
    dbms_datapump.wait_for_job(handle => l_datapump_handle,
    job_state => l_status );
    dbms_output.put_line( l_status );
    end;
    /

    Regards
    Clemens

  • Moldovan says:

    You also might want to grant quota on SYSTEM tablespace (if needed).

  • Kim Kirschenman says:

    I found this replay via a web search “dbms_datapump”:
    Thank you for this reply on using the dbms_datapump “utility”. We are moving to the cloud (AWS RDS) and I needed some guidance on how to convert my existing UNIX shell script that runs a expdp command to use DBMS_DATAPUMP:
    $ORACLE_HOME/bin/expdp \’/ as sysdba\’ schemas=aaa version=12.1 dumpfile=DATA_PUMP_2:${ORACLE_SID}_export_aaa_schema_${TODAY}.dmp logfile=EXPORT_LOG_DIR:${ORACLE_SID}_export_aaa_schema_dp_${TODAY}.log
    REMAP_DATA=AAA.ABC.IMAGE:AAA.remap.null_lob
    REMAP_DATA=AAA.CBS.IMAGE:AAA.remap.null_lob
    REMAP_DATA=AAA.NBC.IMAGE:AAA.remap.null_lob
    REMAP_DATA=AAA.ZZZ.IMAGE:AAA.remap.null_lob
    REMAP_DATA=AAA.FOX.IMAGE:AAA.remap.null_lob

    Your information has helped considerably in this conversion. Thanks again.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team