Re: Wanted: Procedure to clone DB

In article <3120hj$5p_at_crcnis1.unl.edu> nolan_at_helios.unl.edu (Michael Nolan) writes:

   dsf_at_netcom.com (Dave Faulkner) writes:

>I am running two Oracle 7 database servers - a production & a development
>each with it's own Oracle license. What is the smoothest method of
>"cloning" my development db to the production machine now that I'm
>ready to deploy a new application. I imagine that export/import is
>the way to go, but how much do I export and how would I work around
>integrity contraints during the import?

I'd like to expand on the problem definition and see what other people are doing.

Replicating the development environment invloves moving the following db objects:

	tables
	  indexes
	  constraints
	  triggers
	  grants
	views
	synonyms
	sequences
	packages, procedures and functions
	grants (on objects other than tables)
	roles
	(probably something else I can't think of now...)

Options for moving this stuff are:

#1 Export

        In user mode it will export all objects, including the ones you *don't* want, such as the Oracle CASE views, etc. So you could do a user export, then import and delete the unwanted stuff.

        In table mode, only specific tables (and their directly associated objects) can be specified. This leaves you with moving the other items 'by hand'. If you're going to move everything else by another method, why move the tables with export?

#2 SQL*Net

        Create a DB Link between the instances and then copy what you can. However, there are still many items that have to be re-created, such as sequences, grants, synonyms, stored procedures, etc. Very messy.

#3 Oracle CASE

        If you've built your app with CASE and been diligent about keeping the CASE dictionary up to date, you can just generate DDL and then execute that in the instance of your choice. But, if you need data from your development environment, you'll have to use #1 or #2 to move that. Also, the CASE DDL is likely not 100% if you've had to 'fool' CASE to get it to work correctly.

In summary, my question is: Is anyone using any tools that allow for better management of DB objects and moving them among instances?

Grant Johnson                                 Internet: grantj_at_llnl.gov
Lawrence Livermore National Lab	           PacBell Net: (510) 423-0406