Script required ............

[seshib]

Hi Gurus,

I would like to change the name of the database. Can any one send the script or tips/link ?

[davey23uk]

what OS?

[davey23uk]

for NT / 2000

http://asktom.oracle.com/pls/ask/f?p...D:634671569269

[seshib]

Unix

[davey23uk]

Make cold backup of OLD_DB database, shutdown it, rename all directories which have string OLD_DB in its names, update parameter file, create new control files for the database with a new name - NEW_DB, remove control files with old database name - OLD_DB.


Make a backup of the old database - OLD_DB.

Create script (cr_control.sql) to compose control file of the database with a new name NEW_DB.

Set enviroment variable ORACLE_SID to OLD_DB;

Run svrmgrl and execute the next command:

$ svrmgrl
SVRMGR> connect internal
SVRMGR> alter database backup controlfile
to trace resetlogs;
SVRMGR> exit

Find our trace file ora_*.trc, it should be the latest at USER_DUMP_DEST directory (see parameter file).

Copy ora_*.trc to cr_control.sql

edit cr_control.sql file:

- remove header

- replace old name on a new one: OLD_DB on NEW_DB

- replace the next line:

CREATE CONTROLFILE REUSE ...

on

CREATE CONTROLFILE SET ...

- comment the next line:

RECOVER DATABASE USING BACKUP CONTROLFILE

Shutdown database (OLD_DB).

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> exit

Create parameter file for NEW_DB database - copy it from OLD_DB.
Update this file - replace string OLD_DB on NEW_DB.

Set environment variable ORACLE_SID to NEW_DB;

Create control files with the new database name - NEW_DB.

$ svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> @cr_control.sql

ATTENTION!
If the database was not created:
- make shutdown abort
$ shutdown abort
- fix errors
- depending on the error you may have to remove just created control and log files
- try to recreate control files again


In case of success:


Change the GLOBAL_NAME of the database.

$ svrmgrl
SVRMGR> connect internal
SVRMGR> ALTER DATABASE RENAME GLOBAL_NAME TO NEW_DB;
SVRMGR> exit

Remove control files with old database name OLD_DB


but TEST first if this is for a production machine

[seshib]

Thank you very much...I got idea and script...can i have your mail id ?

[abhaysk]

What's the purpose of renameing DB,
cant make if you just create new instance(name you prefer over old instance name) and point to this DB?

[adrianomp]

Further: if the change is needed because of a user or application request, you can even change the service name to a proper name (edit TNSNAMES.ORA) and leave instance/database as it is.