Oracle 8 and changing DB id's

Oracle 8 and changing DB id's

Post by Glen Upret » Sun, 31 Dec 1899 09:00:00



I had posted this a while back and just wanted to see if anyone has had
the same problem.

I have multiple database instances which I copy/clone into other
database instances on the same machine.  I basically copy the datafiles
from one database and then run a create controlfile script which I
extract from the source database.  Although I would prefer doing it a
cleaner way (export/import) the time it takes (5-6 hours) for that vs.
the cloning method (10-20min) does not make it feasible.

The problem is that because I am using a straight datafile copy the
clone is really a clone of the original, and in Oracle 8 the dbid is not
reset which means I can't use rman because rman cannot handle having
multiple database instances with the same dbid.  

Is there any way to manually change the dbid? Is that OK or will oracle
chase me down and beat me in the streets as a example of what not to do?

Any help with this is greatly appreciated,
Glen Upreti

--
|-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-|
Glen Upreti
Senior Oracle DBA
Northern Arizona University
Phone (520)523-8393    Fax(520)523-7407

  Glen.Upreti.vcf
< 1K Download
 
 
 

Oracle 8 and changing DB id's

Post by Your Nam » Sun, 31 Dec 1899 09:00:00


How about this. You'll find more on the oracle metalink.  I've used this
method before and it works fine.

 Document ID:        106373.655
Title:              Changing dbname or ORACLE_SID
Creation Date:      07 September 1994
Last Revision Date: 07 September 1994  
Revision Number:    0
Product:            RDBMS
Product Version:    7
Platform:           UNIX
Information Type:   ADVISORY
Impact:             MEDIUM
Abstract:           This bulletin describes how to change the dbname for a
                    database, or the ORACLE_SID for an instance, without
                    having to recreate the database.
Keywords:           SID;DBNAME;CHANGING;ORACLE_SID;INSTANCE;CONTROLFILE
-----------------------------------------------------------------------

Modifying a database to run under a new ORACLE_SID
--------------------------------------------------

1.  Shutdown instance
2.  Backup all control, redo and data files.
3.  Go thru the .profile, .cshrc, .login, oratab, tnsnames.ora(for net v2),

    and redefine the environment variable ORACLE_SID to a new value.
    ie  search thru disks and do a grep ORACLE_SID *
4.  cd $ORACLE_HOME/dbs  and rename the following files:
    o   init<sid>.ora    (or use pfile to point to the init file.)
    o   control file(s)  This is optional if you don't rename any of the  
           controlfiles, and the control_files parameter is
           used.  control_files would be set in the initSID.ora
           file or in a file it references with the ifile
           parameter.  Make sure control_files doesn't point to
           any old file names, if you renamed them.
    o   crdb<sid>.sql & crdb2<sid>.sql       This is optional.  These are
        only used at database creation.
5.  cd $ORACLE_HOME/rdbms/admin      and rename the file:  
    o   startup<sid>.sql      This is optional.
 (On some platforms, this file may be in $ORACLE_HOME/rdbms/install.)
 Make sure the contents of this file do not reference old initSID.ora
 files that have been renamed.  This file simplifies the process to
 "startup exclusive" your database.
6.  To rename the database files and redo log files, you would follow the
    instructions in the bulletin:  98863.723.
7.  Change the ORACLE_SID environment variable to the new value.
8.  start up database and verify it works.  Once you have done this,
shutdown
    the database and take a final backup of all control, redo and data
files.
9.  When the instance is started, the control file gets updated with the
    current ORACLE_SID.  
============================================================================
=

Changing the dbname for a database
----------------------------------

 1.  sqldba
 2.  connect internal
 3.  alter database backup controlfile to trace;
     This will write in a trace file, the CREATE CONTROLFILE command that
     would recreate the controlfile as it currently exists.
 4.  Exit and go to the directory where your trace files are located.
     They are usually in the $ORACLE_HOME/rdbms/log directory.
     If user_dump_dest is set in the initSID.ora, then go to the directory

     listed in the user_dump_dest variable.
     The trace file will have the form "ora_NNNN.trc with NNNN being a
number.
 5.  Get the CREATE CONTROLFILE command from the trace file and put it in a

     new file called something like ccf.sql.
 6.  Edit the ccf.sql file and modify the CREATE CONTROLFILE command.
     Just change the word "REUSE" to "SET",and "NORESETLOGS" to
"RESETLOGS",
     and modify the dbname.
     Old line:
         CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ...
     New line:
         CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS ...
     Then save the ccf.sql file.
 7.  Rename the old control files for backup purposes and so they are not
in  
     the way of creating the new ones.
 8.  Edit initSID.ora so that db_name="newdbname".
 9.  sqldba
10.  connect internal
11.  startup nomount

13.  alter database open;
14.  Make sure the database is working.  Shutdown and backup the database.

References
----------
ORACLE7 SERVER, SQL Language Reference Manual

____________________________________________________________________________
____
                                         Oracle Worldwide Customer Support



> I had posted this a while back and just wanted to see if anyone has had
> the same problem.

> I have multiple database instances which I copy/clone into other
> database instances on the same machine.  I basically copy the datafiles
> from one database and then run a create controlfile script which I
> extract from the source database.  Although I would prefer doing it a
> cleaner way (export/import) the time it takes (5-6 hours) for that vs.
> the cloning method (10-20min) does not make it feasible.

> The problem is that because I am using a straight datafile copy the
> clone is really a clone of the original, and in Oracle 8 the dbid is not
> reset which means I can't use rman because rman cannot handle having
> multiple database instances with the same dbid.  

> Is there any way to manually change the dbid? Is that OK or will oracle
> chase me down and beat me in the streets as a example of what not to do?

> Any help with this is greatly appreciated,
> Glen Upreti

> --
> |-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-|
> Glen Upreti
> Senior Oracle DBA
> Northern Arizona University
> Phone (520)523-8393    Fax(520)523-7407



 
 
 

Oracle 8 and changing DB id's

Post by Glen Upret » Sun, 31 Dec 1899 09:00:00


This doc is for v7, the problem that I am having is with v8.  I use
basically the same steps, except I do a reuse for the controlfile.  and
the problem that I am having is not being able to reset the database id
the new (in v8) unique identifier for each database.  I don't know if
taking the reuse out of the create controlfile script will be different
because the reuse is only supposed to say reuse the file not any of the
data within the file, thereby logically the dbid is stored in the data
dictionary not the control file.  I will give it a try next time though.
Thanks
--Glen


> How about this. You'll find more on the oracle metalink.  I've used this
> method before and it works fine.

>  Document ID:        106373.655
> Title:              Changing dbname or ORACLE_SID
> Creation Date:      07 September 1994
> Last Revision Date: 07 September 1994
> Revision Number:    0
> Product:            RDBMS
> Product Version:    7
> Platform:           UNIX
> Information Type:   ADVISORY
> Impact:             MEDIUM
> Abstract:           This bulletin describes how to change the dbname for a
>                     database, or the ORACLE_SID for an instance, without
>                     having to recreate the database.
> Keywords:           SID;DBNAME;CHANGING;ORACLE_SID;INSTANCE;CONTROLFILE
> -----------------------------------------------------------------------

> Modifying a database to run under a new ORACLE_SID
> --------------------------------------------------

> 1.  Shutdown instance
> 2.  Backup all control, redo and data files.
> 3.  Go thru the .profile, .cshrc, .login, oratab, tnsnames.ora(for net v2),

>     and redefine the environment variable ORACLE_SID to a new value.
>     ie  search thru disks and do a grep ORACLE_SID *
> 4.  cd $ORACLE_HOME/dbs  and rename the following files:
>     o   init<sid>.ora    (or use pfile to point to the init file.)
>     o   control file(s)  This is optional if you don't rename any of the
>            controlfiles, and the control_files parameter is
>            used.  control_files would be set in the initSID.ora
>            file or in a file it references with the ifile
>            parameter.  Make sure control_files doesn't point to
>            any old file names, if you renamed them.
>     o   crdb<sid>.sql & crdb2<sid>.sql       This is optional.  These are
>         only used at database creation.
> 5.  cd $ORACLE_HOME/rdbms/admin      and rename the file:
>     o   startup<sid>.sql      This is optional.
>  (On some platforms, this file may be in $ORACLE_HOME/rdbms/install.)
>  Make sure the contents of this file do not reference old initSID.ora
>  files that have been renamed.  This file simplifies the process to
>  "startup exclusive" your database.
> 6.  To rename the database files and redo log files, you would follow the
>     instructions in the bulletin:  98863.723.
> 7.  Change the ORACLE_SID environment variable to the new value.
> 8.  start up database and verify it works.  Once you have done this,
> shutdown
>     the database and take a final backup of all control, redo and data
> files.
> 9.  When the instance is started, the control file gets updated with the
>     current ORACLE_SID.
> ============================================================================
> =

> Changing the dbname for a database
> ----------------------------------

>  1.  sqldba
>  2.  connect internal
>  3.  alter database backup controlfile to trace;
>      This will write in a trace file, the CREATE CONTROLFILE command that
>      would recreate the controlfile as it currently exists.
>  4.  Exit and go to the directory where your trace files are located.
>      They are usually in the $ORACLE_HOME/rdbms/log directory.
>      If user_dump_dest is set in the initSID.ora, then go to the directory

>      listed in the user_dump_dest variable.
>      The trace file will have the form "ora_NNNN.trc with NNNN being a
> number.
>  5.  Get the CREATE CONTROLFILE command from the trace file and put it in a

>      new file called something like ccf.sql.
>  6.  Edit the ccf.sql file and modify the CREATE CONTROLFILE command.
>      Just change the word "REUSE" to "SET",and "NORESETLOGS" to
> "RESETLOGS",
>      and modify the dbname.
>      Old line:
>          CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ...
>      New line:
>          CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS ...
>      Then save the ccf.sql file.
>  7.  Rename the old control files for backup purposes and so they are not
> in
>      the way of creating the new ones.
>  8.  Edit initSID.ora so that db_name="newdbname".
>  9.  sqldba
> 10.  connect internal
> 11.  startup nomount

> 13.  alter database open;
> 14.  Make sure the database is working.  Shutdown and backup the database.

> References
> ----------
> ORACLE7 SERVER, SQL Language Reference Manual

> ____________________________________________________________________________
> ____
>                                          Oracle Worldwide Customer Support



> > I had posted this a while back and just wanted to see if anyone has had
> > the same problem.

> > I have multiple database instances which I copy/clone into other
> > database instances on the same machine.  I basically copy the datafiles
> > from one database and then run a create controlfile script which I
> > extract from the source database.  Although I would prefer doing it a
> > cleaner way (export/import) the time it takes (5-6 hours) for that vs.
> > the cloning method (10-20min) does not make it feasible.

> > The problem is that because I am using a straight datafile copy the
> > clone is really a clone of the original, and in Oracle 8 the dbid is not
> > reset which means I can't use rman because rman cannot handle having
> > multiple database instances with the same dbid.

> > Is there any way to manually change the dbid? Is that OK or will oracle
> > chase me down and beat me in the streets as a example of what not to do?

> > Any help with this is greatly appreciated,
> > Glen Upreti

> > --
> > |-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-|
> > Glen Upreti
> > Senior Oracle DBA
> > Northern Arizona University
> > Phone (520)523-8393    Fax(520)523-7407


--
|-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-|
Glen Upreti
Senior Oracle DBA
Northern Arizona University
Phone (520)523-8393    Fax(520)523-7407

  Glen.Upreti.vcf
< 1K Download
 
 
 

1. How to modify fields linked to id's when the id's change

I have a bunch of Access databases that are being dumped into a master Sql
database.  I have 3 tables:  Client, FamilyMembers, Cash
Client has an identity key, which FamilyMembers and Cash both refer to the
ClientID field.  Simple.  However, each Access db most likely will have the
same ClientID's.  I've been trying to figure out how I can easily update
FamilyMembers and Cash to reflect the new ClientID, but have yet to be
successful.  Can anyone come up with a query or two that would do this?

2. Trouble with Pdox 4.5 DOS

3. change oracle-user and group id's

4. Identifing blank fields

5. WHILE ID='::ID::' has me confused

6. Cube process - Multi threading

7. Linking Unix Process Id's back to Session Id's

8. Pb avec Sub SELECT et Count(*) ?

9. 'Changed DB Context' Error

10. Problem Changing ID's

11. Approach Changing ID's of linked records

12. Rebuilding Master DB is not changing Default Sort Order ID

13. Problem with ID'S in multiple DB