Periodically, we refresh our QAS environment with Production data. We are on R3 4.6B, Oracle 8.0.5, and Solaris 2.5.6. So far, I've been doing client copy thru scc8 to generate client export file, and then use "tp import ..." from OS level. Since last successful client copy was 6 month ago, the file under trans_dir/data was 1.7GB. This time I implemented FILESPLIT into the source TMS configuration. Indeed, it generated 4 files about 7.3 GB. It took about 10 hours to do the "tp import..." As the Production system kept growing, Is there a better way to do this? BTW, our production database is about 34GB.
Yes there is a much better way to do this. What I prefer to do is copy the entire database to the target system, and then rename it. First copy the contents of database source to database target (everything under /oracle/SID/sapdata*, /oracle/SID/origlog*, /oracle/SID/mirrlog*), the easiest way to do this is when database source is down with a 'shutdown normal'!
I then rename the target database to the new SID. This can be achieved by first taking a control file trace on the source database (SVRMGR> alter database backup controlfile to trace resetlogs;, make sure you haven't added any datafiles since the copy was made). This will create a "template" of all the database essentials in a file you can alter and then execute to rename the target database (datafile locations, log file locations, etc.). The trace file should be saved in /oracle/SID/saptrace/usertrace and be named something like ora_17245_sid.trc You then need to edit this template file before you proceed:
1. Delete everything before the STARTUP NOMOUNT
2. Change all occurrences of the OLD SID to the NEW SID.
3. Change the "CREATE CONTROLFILE REUSE DATABASE "SID" RESETLOGS ARCHIVELOG" in the file to "CREATE CONTROLFILE SET DATABASE "SID" RESETLOGS ARCHIVELOG".
4. Delete the line that says, "RECOVER DATABASE USING BACKUP CONTROLFILE"
5. Save the file as "rename.sql"
6. You can then execute this rename.sql on the target database using svrmgrl. Keep in mind that you will have to mount the target database as /oracle/NEWSID.
Some things to remember:
1. Although this procedure has worked well for me, I can't guarantee that it will work well for everyone.
2. Thoroughly and repeatedly test this before implementing in "production."
3. I am relatively certain this method is NOT supported by SAP or Oracle.