Re: Howto change db name ?

From: Guillaume Duwelz-Rebert <Guillaume.Duwelz-Rebert_at_alcatel.fr>
Date: Thu, 30 Sep 1999 17:36:55 +0200
Message-ID: <37F38397.E31FEC82@alcatel.fr>

Well this is how I do, but try on a simulation database, before doing it on a production database:

First type: alter database backup controlfile to trace resetlogs; and : shutdown

go to the user_dump_dest directory and find the last file <SID>_ora_*.trc
edit it, you'll have normally something like:

Dump file /oracle-sys2/oracle/admin/PROD/udump/prod_ora_14306.trc Oracle7 Server Release 7.3.4.4.0 - Production PL/SQL Release 2.3.4.4.0 - Production
ORACLE_HOME = /oracle-sys2/oracle/product/7.3.4 System name: SunOS

Node name:      kangoo
Release:        5.6
Version:        Generic_105181-11
Machine:        sun4d

Instance name: PROD
Redo thread mounted by this instance: 1 Oracle process number: 21
Unix process pid: 14306, image: oraclePROD

• SESSION ID:(22.495) 1999.09.30.17.18.04.000
• 1999.09.30.17.18.04.000
  # The following commands will create a new control file and use it
  # to open the database.
  # The contents of online logs will be lost and all backups will
  # be invalidated. Use this only if online logs are damaged.
  STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 ....

delete all the lines until "STARTUP NOMOUNT" so the file start now with "CREATE CONTROLFILE REUSE ..."

now replace the old SID with the new one (here PROD -> NEW_SID), and put the word SET before DATABASE like this:

old line: CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG
new line: CREATE CONTROLFILE REUSE SET DATABASE "NEW_SID" RESETLOGS NOARCHIVELOG go to the end of the file, and delete all the lines after the ";" after the datafiles
for exemple when you have something like:

...

 '/oradata_1/PROD/fnd_indx03.dbf',
  '/oradata_1/PROD/ax_data01.dbf',
  '/oradata_1/PROD/ax_indx01.dbf'

;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS; delete the five last lines to have:

...

 '/oradata_1/PROD/fnd_indx03.dbf',
  '/oradata_1/PROD/ax_data01.dbf',
  '/oradata_1/PROD/ax_indx01.dbf'

;

save this file as "control.sql"

now change in the init.ora the dbname, and every configuration file where the
dbname appear. Be careful if you have your init.ora named init<SID>.ora, you
have to rename it to init<NEW_SID>.ora

Set the ORACLE_SID=NEW_SID

the type : svrmgrl

SVRMGRL> connect internal
Connected

SVRMGRL> startup nomount;
ORACLE instance started.
...

SVRMGRL>@control
Statement processed

SVRMGRL> alter database open resetlogs; (it can take a long time, it depends on the size of the database)
Statement processed

SVRMGRL> alter database rename global_name to <NEW_SID>; Statement processed

SVRMGRL> shutdown
SVRMGRL> startup;

and it's OK.

toto wrote:

> 
> How to change database name without scratching it ?
> 
> Thank's

Received on Thu Sep 30 1999 - 10:36:55 CDT