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:

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:

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

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

You will get a similar results.

Thanks
Ahmed