2

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?

Improve this question
2
  • With incomplete dump files you are waddling into risky territory. It may be beneficial to report such errors back to whoever is sending them to you and ask for a consistent set of dumps. Just my opinion.
    – Raj
    Mar 22, 2017 at 13:57
  • Did you try restarting export job ? If export was interrupted due to an unknown error, Master table should still be in the main server. So you can find the job name (from dba_dtapump_jobs) , attach to it (expdp attach=job_name), and resume it
    – a1ex07
    Mar 22, 2017 at 15:30

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • Tried using the impdp command you suggested, but ended up with the following error. With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-39059: dump file set is incomplete ORA-39246: cannot locate master table within provided dump files
    – akr
    Mar 22, 2017 at 9:17
  • Oh, the same error that I mentioned. As far as I know you can't import the tables without master table.
    – atokpas
    Mar 22, 2017 at 9:19
  • Our requirement is to restore this dump to another instance which has the configuration as that of the server from which the dump is exported.
    – akr
    Mar 22, 2017 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.