This discussion is archived
7 Replies Latest reply: Nov 16, 2012 1:05 PM by Osama_Mustafa RSS

Bringing copy of production schema to Development environment

orausern Explorer
Currently Being Moderated
Hi Experts,

We are on Oracle version 11.2.02 on Solaris 10. We are having many issues where some data change related scripts that are sent by developers , when they are applied to Produciton , they fail. They may work fine in development environment data, but fail in production environment.

How best to deal with this issue? Can we just have a copy of prodution data in our environment and use that to test? We mask any sensitive data from production and then use this data to test the script so as to avoid the error. Is that the best way or are there some other approach that is better?

Thanks,
  • 1. Re: Bringing copy of production schema to Development environment
    sb92075 Guru
    Currently Being Moderated
    orausern wrote:
    Hi Experts,

    We are on Oracle version 11.2.02 on Solaris 10. We are having many issues where some data change related scripts that are sent by developers , when they are applied to Produciton , they fail. They may work fine in development environment data, but fail in production environment.

    How best to deal with this issue? Can we just have a copy of prodution data in our environment and use that to test? We mask any sensitive data from production and then use this data to test the script so as to avoid the error. Is that the best way or are there some other approach that is better?

    Thanks,
    my car fails.
    how to make my car go.


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Bringing copy of production schema to Development environment
    user296828 Expert
    Currently Being Moderated
    They may work fine in development environment data, but fail in production environment.
    Not sure how easy it is for you to get the production data copy to your local environment. If it is easy/small size data go for it.

    Ideally the script written in development should be tested in production equivalent environment.
  • 3. Re: Bringing copy of production schema to Development environment
    orausern Explorer
    Currently Being Moderated
    >
    my car fails.
    how to make my car go.


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
    Well my question is not so much on fixing one specific sql and why it failed etc but on the approach. Is it ok to copy production data to development for testing purpose? That is the my doubt.
  • 4. Re: Bringing copy of production schema to Development environment
    orausern Explorer
    Currently Being Moderated
    11g wrote:
    They may work fine in development environment data, but fail in production environment.
    Not sure how easy it is for you to get the production data copy to your local environment. If it is easy/small size data go for it.

    Ideally the script written in development should be tested in production equivalent environment.
    The data is not huge but opinions on whether this should be done or not are different in the different dbas. We have another group of dba that handle production and they are against giving a copy of prod schema to dev even after scrubbing the sensitive data. So I am wondering whether they are right or whether this approach is fine of copying the data from prod to dev.

    Thanks,
  • 5. Re: Bringing copy of production schema to Development environment
    user296828 Expert
    Currently Being Moderated
    We have another group of dba that handle production and they are against giving a copy of prod schema to dev even after scrubbing the sensitive data.
    So I am wondering whether they are right or whether this approach is fine of copying the data from prod to dev.
    Actually who is right or wrong can be only told by your manager or the processes being followed at your place.

    It seems like a Data Centre Operation where productions are managed by separate group. In such environment usually production DBA have their production equivalent databases where they test the changes approved by Change Management.

    If you are after open opinion, I could say they are right.
  • 6. Re: Bringing copy of production schema to Development environment
    rp0428 Guru
    Currently Being Moderated
    >
    Well my question is not so much on fixing one specific sql and why it failed etc but on the approach. Is it ok to copy production data to development for testing purpose? That is the my doubt.
    . . .
    The data is not huge but opinions on whether this should be done or not are different in the different dbas. We have another group of dba that handle production and they are against giving a copy of prod schema to dev even after scrubbing the sensitive data. So I am wondering whether they are right or whether this approach is fine of copying the data from prod to dev.
    >
    Well answer this question: does Oracle know if data is 'production' or 'dev'? Of course not.

    You said your problem was this
    >
    We are having many issues where some data change related scripts that are sent by developers , when they are applied to Produciton , they fail. They may work fine in development environment data, but fail in production environment.
    >
    You don't say what 'fail' means and it can mean a lot of things. If an insert statement tries to insert a record for a child table and the parent table record does not exist it will 'fail'. It doesn't fail because you are in the production environment; it fails because the parent table record does does exist.

    So for that example you need to determine why the parent record did not exist when the child was inserted. That can also have more than one possible cause. Perhaps the parent record was inserted but the insert failed. Perhaps the parent record was supposed to exist but had been deleted by a user before you ran your update script.

    To be able to give you a better answer you need to provide better information about what your 'data change related' scripts are supposed to do. Obviously a script that is hard-coded to include a production key value (e.g. your social security number) will not work if the data does not include that value.

    But the usual cause of problems like yours is the lack of test data that conforms to the business rules of the application.

    The solution is to have a test database whose data is known to obey the business rules of the application. The data itself doesn't need to be production data but could be scrubbed data.

    Since a 'test' of your deployment will likely modify the data in this test database the procedure is generally:

    1. reload the test database from a backup or import
    2. run the tests
    3. if there are no issues you are done - go to step 6
    4. identify any issues and resolved them
    5. go back to step 1
    6. backup the current state of the test database - this is the starting point for the next release

    Step #2 above might add new tables, new data or update some lookup tables. But then step #6 saves the new state of the test database and data to use as the starting point for the next release.

    You might wind up keeping multiple copies of the test database if you ever need to go back and test something against a previous release.

    Use production-copy in dev? NO! It isn't necessary and you don't need production volumes of data to do development work.
  • 7. Re: Bringing copy of production schema to Development environment
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    You could use more than one option :
    -Clone Database
    -export/import
    -expdp/impdp
    -RMAN

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points