Datapump in parallel with a shell script

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

I recently came across an issue while exporting a huge partitioned table for a data migration. The export took years without any obvious reason.

After some research I found that the parallelism wasn’t working, I could only see one datapump worker at a time, then I found this note explaining the behaviour (Doc ID 1467662.1) .

A table with BASICFILE lobs cannot be exported in parallel, oracle advise to convert those BASICFILES to SECUREFILES; this was not possible for me.

The second solution was to run multiple export jobs in parallel, each one exporting a fraction of the table rows. In the example, oracle use a query using a range of the primary key to do an export, this was ok but I needed a more automatic method to divide the rows based on the degree of parallelism I wanted to apply, then I found this blog post from Morten Jensen.

He uses the modulo and the rowid to create an equal division of the rows based on a parallelism parameter, that was exactly what I was looking for. He used a perl script to achieve that, I preferred to make a shell script to do it because I have only a little knowledge of Perl. I created one shell script to create the export, in my case it was to export a specific partition but the script supports tables as well, and one script to import the dumpfiles generated in parallel.

Datapump in parallel scripts

and the import script:

This solution is a workaround for the issue mentioned previously but it can also be used in standard edition to use parallelism with datapump which is only accessible to EE users otherwise.
To use these scripts you will have to change the DIRECTORY to match the one you are using.
You can also customize the parfile options, on my side I used the following:

  • TRANSFORM=LOB_STORAGE:SECUREFILE (to transform BASICFILES to SECUREFILES while importing)
  • TABLE_EXISTS_ACTION=APPEND (because I was importing partitions and the table existed already)
  • DATA_OPTIONS=DISABLE_APPEND_HINT (this is mandatory when using multiple jobs importing in the same table at the same time as mentioned in the oracle note)

Thanks for reading.

11 thoughts on “Datapump in parallel with a shell script

  1. Hi, how to do the connection string in a container/pluggable database. Assume the plugggable dbs is called mypdb.
    The connect sring “/ as sysdba”@mypdb : this syntac does not work.
    The connect sring “/@mypdb as sysdba” : this syntac does not work.
    It will work only if I put explicitly a user not the “/”
    The connect sring “system/password@mypdb as sysdba” : this syntac will work.
    But I don’t want to use neither sys nor system nor any other user. I need to use “/ as sysdba”, how to make work with “pluggable names string”
    Thanks in advance

  2. Cyrille great job…can you take a schema backup in parallel mode?And if yes what is the command

    I have tried :
    Script_name.sh schema_name 5
    Not working :)… am on SE

    1. Hi,

      This script was made to export a table or a table partition but it’s possible to adapt it to export a whole schema, you will to add a step to loop through the different tables.

      Thanks

  3. Your code is too sophisticated for me too add a single line.. I will be most greatful if you could change the code…I would be really really appreciative

Leave a Reply

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