Migrating everything to do with a schema from one Oracle DB server to another

migrationoracle-11g-r2schema

We have an old DB server, running Oracle 11g on Solaris.

We also have a new DB server, also running Oracle 11g, but on RHEL.

We want to move a schema to the new db – but we need everything it refers to moved too. The data /tables can be moved easily enough, but then there's sequences, stored procedures, db types, views and maybe other things I'm not aware of yet.

The original schema contains different objects assigned to specific tablespaces and we need the same assignments in the destination server too.

What's the best way to accomplish this?

It looks like expdp/impdp can export and import other object types, but I can't find a list of the types to include, or how to say "everything".

Also DBMS_METADATA.GET_DDL looks useful, but again I can only find examples for specific object types and I want "everything in the schema", ideally.

Paul

Best Answer

Datapump is one answer.

Look at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007524 - specifically "Transportable Table mode". Moves the metadata, but not the data. You can choose which schemas to include with the SCHEMAS switch, along with the INCLUDE/EXCLUDE parameters.

Related Solutions

When using expdp to create a snapshot of an oracle schema, how to ensure that no post-export additions are retained upon using impdp to revert

As always with Oracle, there are many ways to reach your goal. You might want to check out flashback database. This enables you to restore your database in a previously saved state and might be the easiest in your case. Using Flashback Database and Restore Points

Drop user cascade could also do the trick, followed by impdb as you described. What was the problem with the drop user action?

Try flashback database.

Trying to copy a schema using impdp

If you just want to get the job done and not worry about why it works on test but not on production then the old export/import methods will work. You don't mention an operating system so I assume Windows because that's what I work on. Something like:

REM from a DOS .bat script located on the server 

SET oracle_sid=YourDatabaseName 

REM  export the schema
C:\app\YourOraclePath\bin\exp SchemaOwner/SchemaPass@SchemaName \
   file=(C:\Dump\SchemaName01,C:\Dump\SchemaName02,C:\dump\SchemaName03) \
   filesize=1GB log=C:\dump\Logs\SchemaName.log

REM drop and recreate the new user if they have existing objects
REM otherwise comment out this line

sqlplus /nolog @C:\Dump\DropRecreateUsers.sql

imp NewSchemaOwner/NewSchemaOwnerPassword \
  LOG=C:\dump\logs\importNewSchemaOwner.log file=SchemaOwner01.dmp \
  fromuser=SchemaOwner touser=NewSchemaOwner

This does not address your error using the data pump which is likely to be connected to the difference between test and production. Nor does it address the wisdom of importing data sets into production from production. If this operation fails what are the consequences?

Best Answer

Related Solutions

When using expdp to create a snapshot of an oracle schema, how to ensure that no post-export additions are retained upon using impdp to revert

Trying to copy a schema using impdp

Related Question