Oracle Data Pump Import Shell Script

For how long you will keep googling each time you try to import data into an Oracle database?
In fact, I was doing the same too, because it's not easy to deal with a tool such as impdp, but the good news, is that a new script is now ready to make your life easier with import data tasks.

I know you are super busy! But before I take you through the process, please read the following disclaimer carefully:

THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS". I WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT.

In other words: You have to test this script "yourself" carefully on a test environment to get familiar with it before using it on production. I'm not intimidating you here. Yes, the script is handy and easy to use, but the common rule of thumb in IT world, is that nothing should go in production unless it's well tested.

Download the script:
https://www.dropbox.com/s/5qmfaeos9w4tj63/import_data.sh?dl=0

This script is also part of the "DBA Bundle", if you didn't hear about "DBA Bundle" term before then I recommend you to get out of the rock you are living under and explore it: 😀
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

In the same bundle there is a script can help you with exporting data as well:
http://dba-tips.blogspot.com/2020/02/new-script-for-exporting-data.html

Now, let's go through a brief demo:
I will demonstrate only the Full import mode, the rest of import modes (schema & table) are similar. 

First, you will be asked to accept the Agreement: [Too much paper work I know 👀]

Next, you will be requested to provide the Complete path of the directory that hosts the export dump file/files, the script can accept shortcuts like "." and "~": [Do not include the dump file name along with the path, file name will be provided next] 👇

Next, provide the dump file name, if it's more than one file, just separate them by "," or if they are multiple files in sequence just enter it one time replacing the sequence number with "*" dump*.dmp, in the below screenshot I've two dump files (EXPORT_FULL_DB_configdb_30-Dec-2020_01.dmp and EXPORT_FULL_DB_configdb_30-Dec-2020_02.dmp) instead of providing them by comma "which is allowed" I just replaced the sequence number at the end of file name with *:👇

 If you have more than one active DB on the same machine, the script will ask you to provide the DB "number" you want to run the import against:👇

Next, provide your Email if you want to receive an Email notification after the completion of the import job, or leave it blank:👇

Next, provide the import mode:👇

Next, if you want to import Specific schemas, then provide them in this section separating schema names by comma "," otherwise leave it blank to import all the schemas available in the export dump file:👇

Next, In case you want to change the name of the imported schema to something else other than source, then provide it in this section following the right format, otherwise leave it blank:👇

for example, if you want to change the name of two schemas from HR to HR_NEW and from SCOTT to SCOTT_NEW then enter it like this: HR:HR_NEW, SCOTT:SCOTT_NEW

Next, Same thing you can do with renaming tables, but when you remap a table make sure to qualify it with its SCHEMA_OWNER in the source DB: 👇

for example, if you want to change the name of two tables from EMPLOYEES to EMPLOYEES_NEW and from DEPARTMENTS to DEPARTMENTS_NEW and both are owned by HR (on the source DB) then specify the remap as this: (Do NOT specify the owner for the target table)
HR.EMPLOYEES:EMPLOYEES_NEW, HR.DEPARTMENTS:DEPARTMENTS_NEW

 

Next, if the schemas in the target DB are having different default TABLESPACE names than their counterparties in the source DB, then you have to remap the tablespace names to reflect the already exist tablespace names in the target DB. If the tablespace names are same between source & target DBs then leave it blank: 👇

 Next, you have to decide what shall the import do if it finds tables already exist on Target DB: 👇

1. SKIP leave the already exist tables' structure and data intact and ignore them.
2. APPEND Append the imported data to the already exist tables and leave the current exist data intact.
3. REPLACE Drop and recreate the already exist table and import the new data which will delete the current exist data.
4. TRUNCATE Delete the current data on the already exist tables and import the new data

If you choose to REPLACE or TRUNCATE then you will get this cute warning message:👇

 Next, specify the data import mode Data & DDL, DDL ONLY or Data only:👇

The Next INCLUDE option will allow you to import ONLY objects types or wild carded tables/indexes like TABLES, INDEXES,... or even if you have a special requirement like:👇

for example: Import all tables starting with keyword "PROD", you can specify it in the input like this: TABLE:"LIKE 'PROD%'"

Please note that if you specify anything in this section this means you are telling the script to (import only the following object types, or wild carded object names and DO NOT IMPORT ANYTHING ELSE). If you leave this section blank this means to import all objects under the schemas/tables you already specified in earlier sections.

Next, If you leave the above section (INCLUDE) section blank, then the next option will ask you to exclude object types or wild carded tables/indexes from this import: [INCLUDE & EXCLUDE are mutually exclusive, they cannot be used together]

For example: to exclude all CONSTRAINTS (except those are mandatory for successful data import) simply input: CONSTRAINT

But in my demonstration, I wanted to exclude all table starting with the name "SYS_EXPORT_FULL*" from this import job, so I've done it like this in the screenshot below:👇

Next, You will be asked if you want to import Object Privileges: [Default is YES] 👇

Note: If the INCLUDE option was already used above, then this option will not appear.

Next, You will be asked if you want to import Statistics: [Default is YES] 👇

Note: If the INCLUDE option was already used above, then this option will not appear.

Next, If your database edition supports Parallelism, you will be asked to provide the PARALLEL DEGREE if you want to run the import process in parallel:👇

Note: On the right side it will display the current CPU core count on the system to help you decide the right degree of parallelism to use, but if you don't want to use Parallelism then leave it blank.

In below screenshot, I've set the degree of parallelism to 2 to match my CPU count, if you want a deep dive on the best practice of setting Parallel degree then read this post.

Next, if you are running the import on a RAC database, you will be offered to balance the import load on all RAC nodes:👇

Note: In order to make this feature works, the dump file location should be shared between all RAC nodes, otherwise don't enable this feature, otherwise the import will fail!

Next, if the database version is 12c and higher, you will be offered to execute the import in NOLOGGING mode [TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y] where REDO data generation will be at the minimum:👇

Note: Only import operation will run in NOLOGGING not the whole database!

Next, if the database is in FORCE LOGGING mode you will be offered to disable it temporarily during the import operation.👇

Note: After the completion of the import operation, the script will automatically ENABLE the FORCE LOGGING mode.

Warning: It's not recommended to disable FORCE LOGGING mode if the database is data guarded by a Standby DB!

Next, If DB_BLOCK_CHECKING parameter is set to TRUE, you will be prompted to disable it temporarily during the import operation. This is crucial to speed up the import process.👇

Note: After the completion of the import operation, the script will automatically set the DB_BLOCK_CHECKING parameter to its original setting.

Next, If DB_BLOCK_CHECKSUM parameter is set to TRUE, you will be prompted to disable it temporarily during the import operation. This is crucial to speed up the import process.👇

Note: After the completion of the import operation, the script will automatically set the DB_BLOCK_CHECKSUM parameter to its original setting.

Next, you reach to the final stage, where you will confirm the import settings before the start of the data pump import:👇

Then provide your confirmation if you want to proceed:👇

Done.

You can make use of the following shown guidelines anytime during the import operation to have control over it while it's running, i.e. you can change the parallel degree of the import job or kill it:👇

The import operation will run in the background where you can disconnect from the current session anytime, also the log records will be displayed if you keep connecting to the same session.

Note: If you want to kill the import operation, kill it from impdp console as shown above, not by killing the import_data.sh script, in case you mistakenly killed import_data.sh script, then revert the database setting manually by executing all the lines below the following line in IMPORTSCRIPT.sh script.

Let me know your feedback.

More Reading: Speed up Data Pump import in 19c

GitHub Version: