Refreshing DEV/TEST environments with PROD

[Joe Patton]

Hello All,

I need to get a process in place for refreshing our DEV & TEST environments with PROD data. I was just looking for ideas on how other users do it. My first thought was to simply export the OTM users/objects(glogowner, archive, glogdba...etc) from PROD and overlay the same objects in DEV or TEST using data pump. I've also started looking at wiping the destination database and using RMAN to duplicate PROD for restore into the target environment. I would appreciate any suggestions on the simplest/cleanest/safest method for getting this done.

Thanks,

Joe Patton
----------------------------------

Hardware/Software Environment:

OTM 5.5 CU4
Server OS: Redhat Enterprise Linux 4
Database: Oracle RDBMS 10.2.0.3
Application: Oracle Application server 10.1.3.3
Web: Apache/Tomcat

[Joe Patton]

Re: Refreshing DEV/TEST environments with PROD

Hi,

Just a quick update in case anyone else is interested: This process is covered in chapter 10 of the OTM admin guide. Both cloning and import/export methods are mentioned.

[chrisplough]

Re: Refreshing DEV/TEST environments with PROD

Joe,

I'm glad you found that. In practice, we use both full DB backups and the import/export process about equally.

--Chris

[nick.polites]

Re: Refreshing DEV/TEST environments with PROD

Joe,

Please be sure to change your external systems and contact email addresses so that no orders end up going to your customers.

Nick

[Joe Patton]

Re: Refreshing DEV/TEST environments with PROD

Thanks all for the suggestions. I mentioned the external systems and contacts issue to our application team. This isn't an issue for us yet, but will be soon. I added it to my notes on the environment refresh process. I decided on the schema export/import route for the refresh. I completed the refresh, but I have started noticing a recurring job execution failure in the DEV and TEST environments. Have you ever seen a trace file like this?(running Oracle 10.2 Redhat Enterprise 4):
************************************************** *****
*** ACTION NAME) 2008-12-24 00:00:01.333
*** MODULE NAME) 2008-12-24 00:00:01.333
*** SERVICE NAMESYS$USERS) 2008-12-24 00:00:01.333
*** SESSION ID552.1341 2008-12-24 00:00:01.333
*** 2008-12-24 00:00:01.333
ORA-12012: error on auto execute of job 3367
ORA-06550: line 1, column 96:
PLS-00904: insufficient privilege to access object GLOGOWNER.PKG_PURGE
ORA-06550: line 1, column 96:
PL/SQL: Statement ignored
************************************************** *****
I confirmed that the object privileges for GLOGOWNER.PKG_PURGE are the same between our PROD, DEV, and TEST environments, but I am seeing trace files like the above pop up regularly on my DEV and TEST database server since they were refreshed.
Thanks,

[nick.polites]

Re: Refreshing DEV/TEST environments with PROD

After the import did you rerun all of the sql scripts from the admin guide beginning from

1. Change to the <otm_install_path>/glog/oracle/script8 directory on the Oracle Transportation
Management Application server. In SQL*Plus, as user GLOGOWNER run:
@create_vpd_package.sql
@glogowner_grants.sql
@create_public_synonyms.sql
@create_logon_triggers (enter connection string when prompted)
2. In SQL*Plus, as user ARCHIVE run:
@archive_grants
3. In SQL*Plus, as user REPORTOWNER run:
@reportowner_grants.sql
@create_public_synonyms.sql
@recompile_invalid_objects.sql

These still need to be revalidated even after an import.

[Shells]

Re: Refreshing DEV/TEST environments with PROD

Just FYI - Our DBA decided to use DATAPUMP for a reorg on our 5.5 cu3 production db a couple of weekends ago without properly testing first. He did so with the system user. After the reorg was complete, we found several of the tables/rows did not get imported/exported (such as the finder_set tables) and we ended up having to do our first full db restore from tape backup in 7.5 years and over 20 db reorgs - causing an extra 10.5 hrs of production downtime. Just thought I would give a heads up since it was mentioned - if anyone decides to try out datapump - make sure it is tested properly first.

Thanks,


Shell

[nick.polites]

Re: Refreshing DEV/TEST environments with PROD

Just FYI - Our DBA decided to use DATAPUMP for a reorg on our 5.5 cu3 production db a couple of weekends ago without properly testing first. He did so with the system user. After the reorg was complete, we found several of the tables/rows did not get imported/exported (such as the finder_set tables) and we ended up having to do our first full db restore from tape backup in 7.5 years and over 20 db reorgs - causing an extra 10.5 hrs of production downtime. Just thought I would give a heads up since it was mentioned - if anyone decides to try out datapump - make sure it is tested properly first.

Thanks,


Shell

Please report this to Oracle support. Hopefully if enough people complain they will look into it or change the admin guide. The only way I know of to import a dump file without having data missing is when you import it with the sys user. Even though the admin guide states that you can import using system I have found that some tables such as finder sets do not get imported. I tried to get the admin guide updated but development still says that you can use system when you really can't.

[Joe Patton]

Re: Refreshing DEV/TEST environments with PROD

Hi again all,

Just one more question on the refresh process. I ran the scripts from chapter 3 in the admin guide as mentioned above after the import from PROD to DEV, but was wondering about the following operations that are mentioned just below in the admin guide:

Reset Sequences
----------------------
set serverout on size 1000000
Execute domainman.reset_sequence;

Setup Security Roles
----------------------
@insert_security_roles.sql

Should I run these two steps as well in addition to the ones mentioned above after an import? I did run them when I ran my first environment refreshes back in December. No problems were reported back to me from the application team.

[chrisplough]

Re: Refreshing DEV/TEST environments with PROD

Joe - yes, these should be run after each import. The last step is especially important because it updates the security configuration in the DB in relation to the app server's code.

--Chris

[vikonline]

Re: Refreshing DEV/TEST environments with PROD

Hi,
I did a refresh of Prod data into My QA environment. While doing so the QA-web servers link also got overwritten with Prod-Web server link. So, I am trying to change it back to Web-Server link in QA. Can anyone guide me on how to do it on OTM 5.5? In OTM 6.2 it can be done from a table but not sure how to revert the same in OTM 5.5

Thanks,
Vikram

[mcg]

Re: Refreshing DEV/TEST environments with PROD

I read the chapter 10 of admin guide and it explains as how to copy the DB data. However, I could not find the app and web server copy. So in this quest I tried and after some trial I was successful in cloning the OTM 6.2 DB from one node to other and APP and WEB from one node to other. The complete instructions are available in my blog, which can be accessible from this URL OTM 6.2


Hope this helps the community.

Thanks,

[Prasad Chandane]

Re: Refreshing DEV/TEST environments with PROD

Hello,

Please use Oracle provided document "Cloning Oracle Transportation Management Database Schemas [ID 1313815.1]"

Thank you!