Oracle Data Pump Import Log File Analysis

In most (or all) cases , when you fully import the database using oracle data pump or the old “imp” import utility , you will get some errors. Some of them are expected and could be ignored and some need close attention to investigate and fix.

For big databases, the import log file may be huge and it may take a lot of time to review. In this blog I’ll show you a quick way to analyze the errors exist in the import log file.

For Unix Platforms:

Execute the following two commands:

tail -20 import.log

This will report the last 20 lines in the import log file, with the number of errors, if any.

For example:

1

 

If you executed the following command:

awk ‘($1 ~ /ORA-/)’ TMS_full_import.log|awk ‘{print $1}’|sort|uniq -c|sort –fr

you will get a summary report of all errors in the import log file and how many times they occurred. For example:

2

From here, you can drill down and investigate why each category of the above errors occurred.

For example, for the erros “ORA-31681” which was reported 32 times during the import:

ORA-31684: Object type ROLE:”AQ_ADMINISTRATOR_ROLE” already exists

So, we are importing an object that is already exist in the target database è could be ignored safely.

 

For Windows Platforms:

I found a similar commands developed for Windows Power Shell by “Marco Mischke”.

So, you may move import log file to the windows and run the following two commands from the power shell:

Get-Content import.log -tail 20

3

Get-Content import.log | %{$_.split(‘:’)[0]} | Select-String -pattern ^ORA- | Group-Object | Format-Table count, name

 

4

You will get a similar results.

Advertisements

One thought on “Oracle Data Pump Import Log File Analysis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s