error when doing full database import

[ddiamond]

I am trying to do a full export of one database instance and then import into another database instance on a different server (both oracle 8.0.5). I am getting the following error as soon as it starts importing privileges:

IMP-00017: following statement failed with ORACLE error 3113:
 "GRANT SELECT ON "REINSURANCE_CONTRACTS_VW" TO "REINSURANCE_VIEW""
IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00021: operating system error - error code (dec 2, hex 0x2)
IMP-00000: Import terminated unsuccessfully

IMP-00021: operating system error - error code (dec 2, hex 0x2)

Here is my export parfile

file=d:\Export\ExportFull.dmp
log=d:\oradata\REPT\Logs\ExportFull.log
direct=Y
full=Y
constraints=Y
consistent = Y

Import par file

full=Y
log=D:\Oradata\Rept\Log\ImportAll.log
file=E:\ExportFull.dmp
commit=Y
buffer=262144

I've tried this multiple times with different full export files, but always get the same error on the same statement. Any ideas?

 

[SantaMufasa]

DDiamond,

I highly recommend that you export, then import your source-database contents on a schema-by-schema basis. This way, you can complete your importing in "edible" modules, and you can isolate the schema(s) where problems are occurring.

I believe your error relates to "BUG:1033830", which throws an error if you attempt to import any packages that are not "VALID".

The workaround is, on the source database, to either recompile (to a "VALID" state) any packages, or drop invalid packages prior to export (then recreate them following the import on the target instance).

To identify invalid objects, you can issue this code:

col invalid_objects format a60
select status,owner||'.'||object_name||' ('||object_type||')' Invalid_Objects
  from dba_objects
 where status <> 'VALID'
 order by owner,object_type,object_name;

If you want to limit the listing to just invalid packages, you can add to the WHERE clause:

...AND OBJECT_TYPE LIKE 'PACKAGE%'...

If you follow the suggestion, above, of doing a schema-by-schema export, then you can ignore all of the invalid objects that may appear in Oracle-generated schemas (such as SYS, SYSTEM, APEX_PUBLIC_USER, BI, CTXSYS, DBSNMP, EXFSYS, IX, MDDATA, MDSYS, OE, OLAPSYS, ORACLE_OCM, ORDPLUGINS, ORDSYS, OUTLN, OWBSYS, PM, SCOTT, SI_INFORMTN_SCHEMA, SPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, SYSMAN, et cetera).

If you need a script that will automatically re-compile invalid objects, please post your need here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Thanks Dave.

I ran your query with the following results:

STATUS  INVALID_OBJECTS
------- -------------------------------------
INVALID PHULL.MYTYPES (PACKAGE)
INVALID PHULL.REFTEST (PACKAGE)
INVALID PHULL.MYTYPES (PACKAGE BODY)
INVALID PHULL.REFTEST (PACKAGE BODY)
INVALID PHULL.TESTBIPKG (PACKAGE BODY)

These packages are truly invalid, so they cannot be recompiled. On Monday I will ask PHULL if we can drop them. As for importing one schema at a time, that will be a fall back, but will take a long time because we have so many schemas.

 

[SantaMufasa]

As for importing one schema at a time, that...will take a long time because we have so many schemas.

Actually, DD, I have found schema-by-schema to be much faster since I can run them concurrently. Concurrent imports work so quickly since there generally is so much excess CPU availability during imports.


Let us know how things turn out.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

Do you suggest creating separate dump files for each schema instead of a full dump? I've found a full dump file it takes a while just to locate the schema within the dump file.

 

[ddiamond]

I exported user PHULL and then imported her. Did not get any errors. It successfully imported her invalid packages. Does that suggest that the invalid packages were not the problem?

As for importing the schemas concurrently, could there be an issue with grants because depending on the order that the imports complete, one import may try to grant privileges on objects that do not exist yet.

 

[SantaMufasa]

Do you suggest creating separate dump files for each schema instead of a full dump?

Yes.

It successfully imported her invalid packages. Does that suggest that the invalid packages were not the problem?

Possibly, but I believe that individual exps/imps make it easier to troubleshoot issues.

could there be an issue with grants because depending on the order that the imports complete, one import may try to grant privileges on objects that do not exist yet.

Actually, it is rare that an import grants privileges on an object that does not yet exist. More likely is that an import attempts to grant privileges on an already-imported object to a user/schema that does not exist yet. In either case, you can confirm proper GRANTs by creating a script of object-privilege GRANTs from the source database, then run that script on the target db once all of the imports are complete.




Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ddiamond]

More likely is that an import attempts to grant privileges on an already-imported object to a user/schema that does not exist yet.

I plan on running a script to create all of the users, schemas, and roles before I begin the import. Hopefully that will take care of it. Now when I say create the schemas, they would be empty schemas until the import.

 

[SantaMufasa]

Correct!

Let us know how it goes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”