Link to home
Start Free TrialLog in
Avatar of jknj72
jknj72

asked on

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
etc....
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

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.
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
Avatar of jknj72
jknj72

ASKER

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.
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.
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.
Avatar of jknj72

ASKER

So slight, is that the way you would promote all your objects to a PROD environment?
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.
>>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.
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
SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

ASKER

Im just going to split the points as you all made valid points. Thanks everyone
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.