I am trying to find a way to refresh my QA environment with Production data. Can any body suggest a fastest way to achieve this, assuming that I have space available on QA to handle production sizes.
Just in case you aren't using RMAN at this time...
Another option is to use exp and imp for these tasks. I'm assuming your database is under 50g, otherwise the imp will probably be too slow.
BIG ISSUE: If your tablespaces are names exactly the same in the QA database as they are in the PROD database, then you can do an export of the data from prod and simply import the data into a NEW/FRESH/EMPTY qa schema using the fromuser=ProdApp touser=QAApp Ignore=Y options. The ignore tells import to ignore create statements that fail because they already exist -- like your tablespaces. All you have to do is create a user with permissions - no objects - and let imp bring them all in for you.
If you have different tablespace names in QA than you have in PROD,
you can still use this technique, but you have to create all the schema objects first with the correct (qa) tablespace names. Imp will ignore the create failures due to existing objects and will start importing the data.
And assuming you have scripts to create the schema owner and all of the schema objects in the desired tablespaces....
1. Truncate all the data by dropping the schema owner user and then re-create the schema owner and the schema objects from your scripts.
e.g. drop user qa_app cascade;
2. Re-create the schema owner. See attached file cr_user.sql
3. Re-create the schema objects by running your create scripts while connected as the schema owner (qa_app or whatever).
3. Disable all foreign key constraints. See attached file mk_disable_enable_fks.sql which generates 2 scripts...
4. Export data from prod
exp userid=system/xxxx@prod file=prod.dmp buffer=1048576 compress=N full=Y
exp userid=system/xxxx@prod file=prod.dmp buffer=1048576 compress=N owner=PRODApp
5. If the sequence next values are important to you, then drop your fresh ones and let imp bring them in from prod at the nextval they were at then! The following query will generate the drop statemetns for you.
select 'drop sequence_name ' || sequence_name || ';'