Announcement

Collapse
No announcement yet.

Refreshing DEV/TEST environments with PROD

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Refreshing DEV/TEST environments with PROD

    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

    Technical Specialist, DB2-Oracle DBA
    Database Management and Support
    Parker Hannifin Corporation

  • #2
    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.
    Joe Patton

    Technical Specialist, DB2-Oracle DBA
    Database Management and Support
    Parker Hannifin Corporation

    Comment


    • #3
      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

      Comment


      • #4
        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
        If my post was helpful please click on the Thanks! button

        MavenWire Hosting Admin
        15 years of OTM experience

        Comment


        • #5
          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,
          Joe Patton

          Technical Specialist, DB2-Oracle DBA
          Database Management and Support
          Parker Hannifin Corporation

          Comment


          • #6
            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.
            If my post was helpful please click on the Thanks! button

            MavenWire Hosting Admin
            15 years of OTM experience

            Comment


            • #7
              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

              Comment


              • #8
                Re: Refreshing DEV/TEST environments with PROD

                Originally posted by Shells View Post
                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.
                If my post was helpful please click on the Thanks! button

                MavenWire Hosting Admin
                15 years of OTM experience

                Comment


                • #9
                  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.
                  Joe Patton

                  Technical Specialist, DB2-Oracle DBA
                  Database Management and Support
                  Parker Hannifin Corporation

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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
                      Last edited by vikonline; January 27, 2011, 20:47.
                      Thanks
                      Vik

                      Comment


                      • #12
                        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,

                        Comment


                        • #13
                          Re: Refreshing DEV/TEST environments with PROD

                          Hello,

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

                          Thank you!
                          Let's connect on LinkedIn
                          Mobile: +91 973 049 6886
                          E-mail: [email protected]

                          If my post was helpful, then click on the Like button

                          Comment

                          Working...
                          X