Thursday 22 July 2010

Environment refresh – ripper

So you have a oracle jde instance, say e1prod.  It’s 300GB and archive logging is enabled.

How are you going to refresh this sucker?  R98403 is going to take an epoch and also is going to generate too much redo.  row at a time, table at a time…  This is not going to be your friend.

so, I’ve created some scripts to get the job done, and generate no logging!  You cannot use data load or imp export because it forces redo logging when you do an owner remap.

I’ve developed a script that will create all of the metadata for you.  You need to create some ‘NOLOGGING’ clauses in some of the statements and change the owner in some of the statements.

so run this: script

then edit:

generateIndexes.sql

GeneratePKs.sql

change  “COMPUTE STATISTICS” to “COMPUTER STATISTICS NOLOGGING”

Change PRODDTA to UADTA

Then run something like this script:

Note that this script also sizes your tables properly, like an import export.

I did change the PCT_INCREASE for the INDEX and PK files to 30%, because these we set to the default amount.

No comments: