Thursday, October 10, 2013

Issues with migrating data between oracle versions

A little background...
I implemented a complete backup and restore strategy for Alfresco and a custom application that have synchronized data (Alfresco holds repository documents, Application DB holds application specific data). This was done with Alfresco 4.1.2 on a clustered environment, Oracle backend for both database schemas, and SOLR for search subsystem; all running on two CentOS VMs. The database backups and recoveries were implemented with Oracle Datapump.   The database operations were all invoked remotely on the database server and used SSH Public Key Based Authentication for a completely automated solution.   The same backup and recovery scripts also work on a local Ubuntu environment with a default Alfresco 4.1.2 installation with Oracle; Oracle was installed on the same VM.

...and then I had an issue
I ran into a snag when I tried restoring the custom application database export on on a different server than where I took the backup.  The source expdp'ed dump file was created from "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production".  I needed to import it into my destination local Ubuntu environment which is "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production".

Notice the versioning difference.  I didn't think a sub-sub version would cause any problems, little did I know...

Steps to figure out the problem:
  1. I ran the following command on my Ubuntu destination server with the user that owned the schema.  I had to remap_schema and remap_tablespace since source and destination schema and tablespace names were different.
    impdp $SQL_USER/$SQL_PASSWORD@$SQL_CONN remap_schema=<source_schema>:$SQL_USER remap_tablespace=<source_tablespace>:$SQL_TABLESPACE full=N directory=datapump_dir dumpfile=$DUMP_FILENAME logfile=$DUMP_FILENAME.imp.log
    

  2. The import log split out the diagnoses below:
    ORA-39097: Data Pump job encountered unexpected error -1031
    ORA-39065: unexpected master process exception in DISPATCH
    ORA-01031: insufficient privileges
    THANKS A LOT ORACLE - sarcastically!  ORA-01031: insufficient privileges really tells me a whole lot (Mind you I had granted various permissions and my Alfresco restore had worked just fine with the same generic scripts).

  3. I then ran the import as sysdba.  I tried implementing this by granting the user dba priviledges but that did not work, perhaps I will try that again later...
    impdp \"/ as sysdba\" remap_schema=<source_schema>:$SQL_USER remap_tablespace=<source_tablespace>:$SQL_TABLESPACE full=N directory=datapump_dir dumpfile=$DUMP_FILENAME logfile=$DUMP_FILENAME.imp.log
    

  4. MUCH BETTER log file this time:
    ORA-39097: Data Pump job encountered unexpected error -30094
    ORA-39065: unexpected master process exception in DISPATCH
    ORA-30094: failed to find the time zone data file for version 11 in $ORACLE_HOME/oracore/zoneinfo

Solution:
This article helped me figure out how to solve the problem.  Since we are restoring from "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production" to "Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production", the timezone versions were off.  Specifically the source database was on version 11, where the destination database was on version 13 (You can check with SELECT version FROM v$timezone_file;).  This matters only because the database schema has column types of TIMESTAMP(6) WITH TIME ZONE.
  • Copy over the timezone version pair that was missing from the source to the destination $ORACLE_HOME/oracore/zoneinfo folder.  In this particular example, the missing files were timezlrg_11.dat timezone_11.dat.
  • Change the permissions of the files to match the other .dat files in the directory.
  • Restart Oracle.
The import will now work with the impdp command from Step 2 above!

7 comments:

I appreciate your time in leaving a comment!