Restore/import tables from unsuccessful database dump file

dumporacleoracle-11g

I'm in an urgent need of restoring a backup dump taken three weeks ago which was not completed successfully. I'm using Oracle 11g database in nonarchive log mode. The export command used was as follows. 

expdp "sys/********@TEST AS SYSDBA" DUMPFILE=Backup_01022017.dmp LOGFILE=BakupLog_01022017.log FULL=y

The backup log shows that the process terminated with the following error. 

ORA-39097: Data Pump job encountered unexpected error -1089
ORA-39065: unexpected master process exception in MAIN
ORA-01089: immediate shutdown in progress - no operations are permitted

But, fortunately the necessary tables are seen as exported. Are there any ways by which I can restore these tables or can I import these tables?

Best Answer

It depends, lets test it.

[oracle@server1 Desktop]$ expdp system/oracle directory=DATA_PUMP_DIR schemas=jay dumpfile=jay.dmp

Export process has started and I tried to shut the database down in a separate session.

SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.

And I noticed the following error in the export log.

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JAY"."MY_OBJECTS"                          98.58 MB 1017888 rows
. . exported "JAY"."TBL2OBJECTS"                         98.58 MB 1017888 rows
. . exported "JAY"."TBLOBJECTS1"                         8.225 MB   84824 rows
. . exported "JAY"."FOOD"                                6.875 KB       6 rows

UDE-01089: operation generated ORACLE error 1089
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
Process ID: 28921
Session ID: 36 Serial number: 45

Now I tried to import one of the tables which was exported.

[oracle@server1 Desktop]$ impdp jay/jay directory=DATA_PUMP_DIR tables=my_objects dumpfile=jay.dmp

Import: Release 11.2.0.4.0 - Production on Wed Mar 22 04:47:15 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JAY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "JAY"."SYS_IMPORT_TABLE_01":  jay/password directory=DATA_PUMP_DIR tables=my_objects dumpfile=jay.dmp 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JAY"."MY_OBJECTS"                          98.58 MB 1017888 rows
Job "JAY"."SYS_IMPORT_TABLE_01" successfully completed at Wed Mar 22 04:47:50 2017 elapsed 0 00:00:33

And it succeeded.

But sometimes you may end up with the following errors.

ORA-39002: invalid operation ORA-39059: dump file set is incomplete

ORA-39246: cannot locate master table within provided dump files

Conclusion: You can restore exported tables as far as the dump file contains the export job's master table - a table which is created during the startup of export process to keep the location of database objects within a dump file. You could see that master table called SYS_IMPORT_TABLE_01 is loaded before importing the table specified at the starting line of impdp log in the above section.

Related Question