Best practice of full database export/import (data pump)

Export/import, data pump or regular, is always my least favorite and last "choice" for database migration. I don't blame Oracle too much on this, as it's logical extract of the full database so there are so many things could go wrong, plus there are as many variables on the destination database as well.

Recently, I had to do this full database export/import as database migration because Oracle can't handle data files containing undo segments such as system for migrations between different Endian operation systems (http://tomasks.blogspot.ca/2012/10/oracle-cross-platform-migration-not.html).


Here are some of the objects I found went missing after full database export/import:

1. SYS/System object grants
Because we usually exclude SYS/SYSTEM and other system schema for full database export/import, the grants with those objects will not be exported. You can't blame Oracle for this one though. Generally run a statement similar to this on the original database to get a list of statements to be run in the destination database after the import - change the query criteria to suit your database:

select 'GRANT '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs
where grantee not in ('SQLTXPLAIN', 'PUBLIC','SYS', 'SYSTEM', 'OUTLN', 'MDSYS', 'ORDSYS', 'DBSNMP', 'WMSYS', 'APPQOSSYS', 'ORDDATA', 'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'ORACLE_OCM', 'XS$NULL', 'MDDATA', 'DIP', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR')
and owner in ('SYS','SYSTEM')
and grantee not in (select role from dba_roles)
order by grantee
;

2. Materialized views if the "remote host" is not accessible on the target database. So it's better to make sure all TNSNAMES required by materialized views are valid on the target database before import. Otherwise, you need to check the log file and recreate the materialized views later.


CREATE MATERIALIZED VIEW ......
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error:
ORA-04052: error occurred when looking up remote object xxxxxx
ORA-00604: error occurred at recursive SQL level 4
ORA-12154: TNS:could not resolve the connect identifier specified

3. Synonyms for SYS/SYSTEM etc, which were excluded from export/import. This is similar to item 1.

select 'create synonym '||owner||'.'||synonym_name||' for '||table_owner||'.'||table_name||';'
from dba_synonyms where table_owner in ('SYS','SYSTEM','CTXSYS','DBSNMP','MDSYS','XDB','ORDSYS','WMSYS','SQLTXPLAIN')
and owner not in ('PUBLIC','SYS','SYSTEM','CTXSYS','DBSNMP','MDSYS','XDB','ORDSYS','WMSYS','SQLTXPLAIN')
;

4. Materialized views and potentially other objects, if the object's DDL can't be extracted by DBMS_METADATA for whatever reason. I spent quite some time to figure this out as I found missing materialized views but found no error messages regarding them in the export and import log files.

DBMS_METADATA complains:
“-- Unable to render MATERIALIZED VIEW DDL for object xxx.xxxxxx with DBMS_METADATA”

5. Public synonyms. This is probably an extreme case related to Oracle Spatial. There are still some mysteries but I just don't want to pursue further after spending lots of time with Oracle Support. The full database export/import left me a "phantom" object for the public synonym in sys.obj$ in the target database, but nothing shows in dba_objects for that synonym. As a result, I can neither create such synonym, nor dropping it. Eventually compiling the base object removed the row from sys.obj$. But it's still kind of a mystery. Details here: http://lazydbashop.blogspot.ca/2012/11/weird-phantom-object-cant-create-but.html


So, as my best practice, what I do to make sure there's no missing objects, is to bring data in dba_objects from the original database to the target database, renamed to orig_dba_objects, then run this query after doing the import:

select owner,object_name,object_type from sys.orig_objects
where owner not in ('SYS','SYSTEM','CTXSYS','DBSNMP','MDSYS','XDB','ORDSYS','WMSYS')
and object_name not like 'SYS%'
minus
select owner,object_name,object_type from dba_objects;

I also use it to make sure there's no "new" invalid objects in the target database. One thing need to be noted is, sometimes the "valid" status of objects in the original database could be fake. For example, a materialized view won't become invalid if the "host" information changed like tnsnames changed or the target database not available etc. So generate a compile script for all the "new" invalid objects in the target database, then run it against original database to make sure they are really "valid" in the first place.