Sunday, February 16, 2014

Move a Database from Oracle Enterprise 11gR2 to Oracle Standard 11gR2

This morning I tried to move a schema from an ORA Ent 11gR2 server to another one that is running the standard edition 11gR2. Thought it would be a piece of cake since I have done it numerous times in the past (only that it was between servers running the same edition of Oracle).
Yet, I got stumbling in a lot of errors that cost me about some hours to resolve.

The two db servers are on the same subnet, so they communicate directly. The easiest way to transfer the schema is using NETWORK. The idea is to create a database link on the target db, accompanied by a directory for the temporary files and execute datapump from there. Here's how to do it:

Connect to target db as system and create the database link:

#sqlplus /nolog
sqlplus> connect system
enter password:(********* password is not visible while writing)
Connected.
sqlplus> create directory dmpdir as '/u01/app/dmpdir';
sqlplus> create database link db1 connect to schema-to-move identified by password using 'ora1';

In the above, dmpdir is the directory that will store all temporary files as well as the import log. It can be named anyway you want. 
db1 is the name of the database link to be created and it can have any name you like.
'ora1' is the tnsnames.ora header for the source database. (in order to work the response of tnsping for ora1 must be valid (eg 10 msec).
schema-to-move as the name implies, is the name of the schema to be transferred. 
password is the password of the schema-to-move owner at the source database.

The second and final act is to call impdp utility at the target database to transfer the schema.
Please notice that the schema-to-move does not exists currently on the target database.

Then you run the following:

#impdp system/(password of system at target db) directory dmpdir network_link db1 schemas schema-to-move version=11.1 TRANSFORM=SEGMENT_ATTRIBUTES:N:INDEX TRANSFORM=SEGMENT_ATTRIBUTES:N:CONSTRAINT

Hopefully the transfer will be done without errors and then you can use the new schema on the target db server as you like. 
The errors I was facing, disappeared by the use of version=11.1 and TRANSFORM=SEGMENT_ATTRIBUTES:N:INDEX, TRANSFORM=SEGMENT_ATTRIBUTES:N:CONSTRAINT clauses. The cause of the errors was the difference in features between versions (enterprise and standard)
Hope this saves some of your time.