Re: [Q] Duplicate a database and changing the SID

On 7 Sep 1996 15:44:56 GMT, <eureka_at_is.co.za> wrote:

>Hello,
>
>I looking for a script or some advise
>To duplicate a database which is the a 'master'
>(dupicate the data_files the controle files ...)
>change the SID
>and start the copy of the master in oders words it is a clone of my master
>that i need to produce.
>
>(this is for a training databases i have to duplicate that master to a few
>copy on the same machine)
>
>The configuration is the follow:
>Oracle 7.1.4
>Digital OSF1
>
>Please reply as well bye eMail
>eureka_at_is.co.za
>
>THX Patrick

Patrick,

   Export your existing database first. Then create a new instance with the new SID and create your tablespaces. Then import the data from your original instance.

  Unless you have an exact copy of your production machine lying around, you will not be able to exactly copy the existing database, it's datafiles and control files (nor would you want to).

  To create the new instance, do the following:

  Create your directories. If you are using OFA standards, should be easy.

  Copy current $ORACLE_ADMIN to a new $ORACLE_ADMIN for the new instance. Example:    

  /oracle01/ORA-OLD/admin to /oracle01/ORA-NEW/admin

  Copy the init.ora (and config.ora if you use it) for your current instance and edit the instance name, dumpfile dests, archive_log info, etc. It should be in $ORACLE_HOME/dbs/init*.ora (same for config.ora)

  Copy the existing crdb$ORACLE_SID.sql and crdb2$ORACLE_SID.sql which should be in $ORACLE_HOME/rdbms/admin to match the new instance name, edit them for the new instance name and run them via SQL*DBA or ServerManager.

  Create your tablespaces to match the production machine, changing the datafile names where necessary and import your dump. You should have a copy of your existing database.

  To reload the copy with new data, issue the following in sqlplus and run the resulting scripts (one before and one after the import). Then you can import and tell the IMP utility to "ignore errors due to object existence".

  set pages 0
  set header off
  set termout off
  set echo off
  spool before.sql
  select 'alter table ' || owner || '.' || table_name ||

            ' disable constraint ' || constraint_name || ';'    from dba_constraints
   where owner='SCHEMA_NAME';
   select 'truncate table ' || owner || '.' || table_name || ';'    from dba_tables
   where owner='SCHEMA_NAME';
   select 'drop index ' || index_owner || '.' || index_name || ';'    from dba_indexes
   where index_owner='SCHEMA_NAME';
   spool after.sql
   select 'alter table ' || owner || '.' || table_name ||

            ' enable constraint ' || constraint_name || ';'    from dba_constraints
   where owner='SCHEMA_NAME';
   spool off
   exit;

   This will disable all constraints, truncate the existing tables in the schema, and get rid of current indexes (you want to rebuild them anyway). The import utility will recreate your indexes. The "after.sql" script will re-enable your constraints.

Hope this helps.

Jim Joslin Received on Mon Sep 09 1996 - 00:00:00 CEST