Wednesday, October 21, 2009

Oracle Data Pump for data refreshes part 2

In part 1 tables were created and populated with data. The following UNIX steps assume the user on the server has appropriate privileges to execute Oracle binaries and has the environment set correctly. We now use the data pump to export the whole schema:

ORACLE_SID=prod
expdp test_schema/testing DIRECTORY=test_dp_folder DUMPFILE=prod_full_%U.dmp LOGFILE=prod_full_exp.log SCHEMAS=test_schema

Get the dump files to the target server and then run the import:

ORACLE_SID=test
impdp test_schema/testing DIRECTORY=test_dp_folder DUMPFILE=prod_full_%U.dmp LOGFILE=prod_full_imp.log SCHEMAS=test_schema EXCLUDE=USER

The EXCLUDE=USER assumes the schema owner user already exists in the target database and avoids the annoying error.

The tables, data, constraints, triggers and sequences have been populated. One thing to notice, however: the NEXTVAL for the sequence is 141 and not the 124 from the production system. The reason for this is default CACHE value (20) used when the sequence was created. The sequence imported will always be a multiple of the CACHE setting plus the start value. If your application cannot deal with gaps in the sequence value then use the NOCACHE setting when creating the sequence otherwise every shutdown will introduce gaps and every refresh of test data will introduce gaps.

The next part will document how to refresh data.

No comments: