Solved

Creating production database environment from development environment in Oracle

Posted on 2014-09-09
14
436 Views
Last Modified: 2014-09-10
I have a question about our Production database environment. We have been developing our application and database in a DEV environment. Once the lead developer felt he was ready to move the application to the next step, we did a backup and restore of the DEV database and moved it to TEST envrionment. Now we have people testing the app and we are fixing any issues, etc.... My question is, what is the best way to move our latest database code to our PROD server? I have my own suggestions but the developer has another and I would like to know what the Experts think?

100s of tables
100s of procedures
100s of views
etc....
0
Comment
Question by:jknj72
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40312688
The "best" option for you will depend on:
1. The size of your database
2. What backup software and/or source control software (if any) that you have available
3. What features your storage system (assuming you have a NAS or a SAN) may offer you for cloning/replication
4. How much time you are allowed for this process
5. Whether you want a clean Prod environment that does not include lots of empty or fragmented space from changed or deleted files, test records, etc.

You will have to decide which of those considerations are the most important to you, and rank them, then it should be obvious which method(s) and/or tool(s) you should use for this process.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 40314144
if it's a new database ... rman duplicate database from active is very easy

if you need to add to an existing db use expdp and impdp
conversions can be done on the fly, including move the data across tablespaces
0
 

Author Comment

by:jknj72
ID: 40314300
The bottom line is I did a copy of the db from DEV to TEST and now that its almost ready I wanted to get the Test environment ready and do a copy from Test to Prod while the developer is saying he would rather script every object out and then we would have to pass it to the DBAs to run and I think this is a waste of time. His argument is that the SEQUENCES and CACHEs will get all screwed up and hes really making a big stink about it. I know we can reset these but can someone put his concerns to rest. Below is what he wrote in an email so if anyone can help me with this I would appreciate it. By the way I can NOT STAND this guy, hes the most arrogant @sshole you ever met andhe fights with everyone and likes to make people feel inferior. I know everyone has a guy like this at work but I assure you he is the worst EVER..... Im not an Oracle DBA but Id really like to shut this guy up so please help.....
Thanks


Email:
First, in my experience I have faced SEQUENCE objects being reset after using Recovery Manager. My research concluded that this is due to the REDO logs in Oracle and the CACHE value in SEQUENCES.
After a restore, the previous redo.log is lost, which pointed to the cached value of the next (whatever CACHE is set at), for example CACHE=20. This means the next 20 values are cached, and the system uses the cache value. If the CACHE is flushed, then there are gaps in the sequence.

If CACHE was set to 20
And NEXTVAL would normally pull the number 1500, the cache represents 1500 to 1519.
When the cache is cleared the Cache would set to 1520 – 1539, even if 1501 was never used.. These are Gaps in the sequences. This can happen on a cold reboot of the system too.  Apps can deal with this as the unique numbers move forward, but the data experiences gaps in the sequences.

During a BackUp Restore, the REDO log file that was being managed by the previous instance is lost, and a new redo log file becomes current. The CACHED values for the sequences is lost, so the SEQUENCE starts over. This causes duplicate NEXTVAL calls from the previous instance.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40314305
I prefer to keep sql scripts that have all the necessary DDL.  This lends itself very well to source control software.

If you need to make changes, check out the necessary sql scripts, make the change, execute the script.  When the patch/new DDL is completed, check it back in.

You can generate the scripts using DBMS_METADATA.GET_DDL for all the objects you have.

Then just execute the scripts on the production system.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40314310
Looks like I was typing when you posted.

>>then there are gaps in the sequence.

Sequences will almost always have gaps.  If your apps cannot live with gaps, then don't use sequences.

Creating them is easy:
insert into some_table values(sequence.nextval);
rollback;

Gap created.

Since he mentions the app can handle gaps, then gaps should not be an issue in any way.

>>The CACHED values for the sequences is lost, so the SEQUENCE starts over

Not sure I've ever experienced this one but I don't do complete restores every day.

Cached values lost, sure but not sure I believe they start over.  I would need proof of that one.
0
 

Author Comment

by:jknj72
ID: 40314434
So slight, is that the way you would promote all your objects to a PROD environment?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40314453
OK, maybe your developer has more technical skills than tact, but there are some advantages to doing a complete system build from scratch as opposed to doing a clone or backup/restore.  In my opinion, the possible problems with sequences from a clone or backup/restore approach are minor.  Yes, there will likely be some gaps in some sequences.  But I have never observed a problem with duplicate values froma sequence in this case.  I'm not saying that is not possible - just that I haven't ever observed that following a backup/restore.  (Following an export and restoring from that, yes.)

My concern with the backup/restore option is fragmentation in the system tablespace (from test objects that were created, used and then dropped) or fragmentation in user tablespaces (for the same reason) or fragmentation of freespace within tables from test data that has been deleted.  These are all causes of performance penalties that may remain for the lifetime of the instance.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40314463
>>So slight, is that the way you would promote all your objects to a PROD environment?

To be honest, I never find myself in your position.  I create scripts from day 1.

If I'm working on a new project, I open up a text editor and start writing the DDL.

Test my scripts in unit testing.  When it is time for string testing, just run the script.  When ready for production, just run a script.

Once the new project is in production, I merge the new scripts into a master script.

If I never need to 'clone' a version, build the database (from a DBCA template I created), run a couple of scripts and I'm done.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 40314556
your developer obviously forget to think about a RAC.
using a sequence with multiple instances and cache can even cause non-consecutive values ordered by date
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 167 total points
ID: 40314686
It looks like slightwv and I use the same approach (maybe because we have both been working with Oracle for at least a couple decades?)  I also start with DDL scripts that I generate in a text editor, and combine into multiple scripts that get run from a master script when the project is ready to promote to production.  And yes, I also use DBCA templates to create a new database when needed.  I don't usually clone a non-production database to create a production one.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 167 total points
ID: 40314702
Forgot to mention the pretty obvious elephant in the room:
In development, as a developer, I create a DBA level account.
If you 'clone' the database into production, see the problem?

Developers and testers typically have elevated permissions for their own respective duties.  They should really never have production access.  If they do need it, it shouldn't be elevated access!
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 166 total points
ID: 40314793
i always wonder why so many state "developers in a production db" being dangerous
devs are asked to deploy to prd because apps depend on db objects being in place on the right time for the right version

dba's can do far worse than any developer can
i consider a halve awake dba (that's before i had coffee) way more dangerous

but then again ... someone needs to maintain the production db ... and accidents do happen

a dba is just a developer who can fix the accidents too (or should be able to)
0
 

Author Closing Comment

by:jknj72
ID: 40314836
Im just going to split the points as you all made valid points. Thanks everyone
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40314843
Three words:  Seperation of duties

http://en.wikipedia.org/wiki/Separation_of_duties

A lot depends on your shop and individual roles given to each position.

Smaller shops, roles tend to overlap.  Larger environments it is very rare and commonly forbidden.

Developers typically live in a very loose environment where anything goes.  Production needs to be tightly controlled.

Think about a Payroll system.  Do you want the development team to be able to see everyone's SSN or Salary information?

>>a dba is just a developer who can fix the accidents too (or should be able to)

A Production DBA is soooooooo much more.  At least they should be!  Production DBA's rarely develop application code.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now