Refreshing DV and PY data

I wish to refresh the data in our PY and DV environments with a copy of the production (PD) data. Oracle recommends using R98403, and do not support any other method. However, R98403 takes days to complete, and as we cannot shut production down for that long we get an inconsistent set of data across the tables.

Is there a better way than R98403, maybe something using database tools like export/import? Oracle refused to help with this, saying it was not standard pratice.

We are on 8.0 SP 22. Our database is Oracle 9i.

[ QUOTE ]
I wish to refresh the data in our PY and DV environments with a copy of the production (PD) data. Oracle recommends using R98403, and do not support any other method. However, R98403 takes days to complete, and as we cannot shut production down for that long we get an inconsistent set of data across the tables.

Is there a better way than R98403, maybe something using database tools like export/import? Oracle refused to help with this, saying it was not standard pratice.

We are on 8.0 SP 22. Our database is Oracle 9i.

[/ QUOTE ]

Welcome to the list.

Do some searching on the topic within the list and you will find quite a few pointers on this subject.

Yes, you are correct, there is a much faster method. At a high level:

From the database side -
Back up you PD business data.
Back up your PY business data.
Take PY business data offline.
Drop the PY tables.
Import PD data into PY.
Change owners.

At a more advanced level, there may be some data in PY that you wish to have preserved and restored. UDC tables, next numbers, specific business data in a table that was set up for an on-going project - that sort of thing. Take those in to consideration as well.

- Gregg

Welcome to JDEList!

Oracle's statement to you was ludicrous. R98403 is very useful during implementations and for other specialized uses. However once your production data reaches maturity its quickly evident that its not a production backup or data transfer tool as you found. I can only surmise that the response you received was from a level 1 tech support.

Export/Import is certainly a standard practice in Oracle DB shops. I would however strongly encourage you to upgrade your Database Version to at least 10.2, preferably higher. Starting with Oracle 10G - Export was retired in favor of the New Data Pump export/Import which is order's of magnitude faster and more flexible.

Our PRODDTA is ~ 100GB and it takes ~ 2 hours to refresh PY using Data Pump.