Creating production database environment from development environment in Oracle

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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.
Geert GOracle dbaCommented:
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
jknj72Author Commented:
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.....

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
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.
slightwv (䄆 Netminder) Commented:
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);

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.
jknj72Author Commented:
So slight, is that the way you would promote all your objects to a PROD environment?
Mark GeerlingsDatabase AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
Geert GOracle dbaCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
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)
jknj72Author Commented:
Im just going to split the points as you all made valid points. Thanks everyone
slightwv (䄆 Netminder) Commented:
Three words:  Seperation 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.