Database Refresh From Prod to Test

jitender

Well Known Member
Hello All

We need to Refresh data from Prod to test( business data and control tables).

After searching on oracle knowledge garden we found a UBE R98403 which can do this process.

After Setting the Values in PO as defined in the document from knowledge garden.we ran th report in Proff mode first to see the results.

But in Final mode its not running at all showing copy failure error.

Debugging the report found that this report check s for a flag in F9860 table if this flag(which tell whether to copy or not the data) is set to "Y".

Can you please provide me the neccesary datails to run this report or is there any other process with which i can refresh my Test data from the Prod data.

Release : XE,SP23_S1
SQL SERVER version : 2005.


Note : Please find the Pdf attached when it ran in proof mode.
Regards
Jitender
 

Attachments

  • 151602-R98403_proofmode.doc
    155.5 KB · Views: 114
Best alternative is, take the backup of PD database and restore to DV. Change the owner names from PD to DV. It can be done in 30-60 min max based on the size of the DB
 
Double check the user names and passwords for TESTDTA and TESTCTL work properly in SQL Server.
 
Hi JDE_CNCADMIN

how can i change the owner names of the database.and also the schemas attached.

like in PROD. we have PRODDTA.Tablename , PRODCTL.tablename

to be changed to CRPDTA.Tablename and CRPCTL.tablename


Any help will be highly apprciated.

Regards
Jitender
 
Hi,

You can change that with sp_changeowner
After you change the owner, you'll probably have to
check table permissions.
Finally, there's no need to have CRPCTL, CRPDTA on
JDE_CRP, PRODDTA and PRODCTL on JDE_PROD, etc
You can have all Control Tables with owner CTL,
all Business Data with owner DTA, etc... so, next time
you just have to backup/restore and no need to change
owners or permissions.
 
As has been stated, a database copy is a much faster way to go. There are gotchas to watch for. If you haven't already, I recommend searching JDEList for R98403 and again for "Database Copy" to get a good understanding. For example, when I did my first copy here, everything went great until I tried to use JDE. It turns out we had an application with triggers on the JDE tables that used table owners I did not know about.

These are the things that make the List more valuable than Oracle Support when searching.

Jer
 
Look at this thread which was completed a couple of years ago. Consider using BCP and some of the other methods listed. Lots of information there.

At SQL Server customers, I've created a script that used BCP to extract all the tables into individual binary files - on a customer with 150Gb production database, the entire time on an HP EVA5000 took less than 30 minutes to extract (running all BCP extracts in parallel). Then, I had another script that would go out and clear all the tables in CRP or DV and import the BCP files back again in parallel - total time about 1 hour (excluding indexes). Then the script would reindex. Point was that the refresh took 90 minutes from the users perspective.

Why do this ? Well, by extracting tables one at a time gives a lot more flexibility when it comes to a developer making a request to "just restore Fxxx table from last wednesday". I'd prefer not to go to tape to try and restore a single table. With BCP, its possible to pull out a single table and restore it wherever the developer/business analyst wants it.

Now, thats not the most efficient way to restore CRP/DV from Production. Actually, the most efficient way was to use snapshot technology to instantly "snapshot" production data across to CRP/DV - and then use the stored procedures to rename the owners. You have to also be very careful about table triggers, and ensuring that you have the structure right - otherwise things don't work.

Also, don't use BCP INSTEAD of a regular full backup - I ran a full backup AND the BCP extracts in parallel. Worked fine. That customer has never had a moment of downtime, and has always been able to restore as much or as little data as they desired.

So, for SQL Server, consider BCP as an alternative option....
 
[ QUOTE ]
Hi JDE_CNCADMIN

how can i change the owner names of the database.and also the schemas attached.

like in PROD. we have PRODDTA.Tablename , PRODCTL.tablename

to be changed to CRPDTA.Tablename and CRPCTL.tablename


Any help will be highly apprciated.

Regards
Jitender

[/ QUOTE ]

run this exact script from JDE_Development database. Once for proddta, then change proddta to prodctl and change testdta to testctl. Works like a champ.


DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old = 'proddta'
, @new = 'testdta'
, @sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql
 
Hello all
Thanks a lot for your valuable solutions provided.

we did the data refresh sucessfully by taking the backup of Production data and restoring it in the test environment and changing the schemas to that of test environment.

Thanks once again

Regards
Jitender
 
Glad the list helped. Perhaps a donation to JDEList might be a good way to express your companies gratitude ?
 
Back
Top