Renaming the existing database

Here is a step by step guide on how to change an existing database name. Ths guide is done by one of the Innotiiveasia's consultant.I hope it helps you all and if you have any comments, feel free to do it in this blog.Hapy reading.

Renaming the existing database

Connect to the database we wanted to change.

SQL> select name from v$database;

NAME
---------
PROD

The existing database name is prod, changing the database name to test. First force a log switch.

SQL> alter system switch logfile;

System altered.

Then backup the controlfile to trace

SQL> alter database backup controlfile to trace resetlogs;

Database altered.
Note: This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.

4. shutdown the database using shutdown immediate command.
SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

5. Find the trace file in udump and modify it as below

cd $ORACLE_HOME/admin/prod/udump
[oracle@localhost udump]$ ls -lrt
total 1024

-rw-r----- 1 oracle dba 695 Jun 12 13:41 prod_ora_6567.trc
-rw-r----- 1 oracle dba 2094 Jun 12 13:41 prod_ora_6569.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:42 prod_ora_6604.trc
-rw-r----- 1 oracle dba 666 Jun 12 13:42 prod_ora_6580.trc
-rw-r----- 1 oracle dba 795 Jun 12 13:42 prod_ora_6605.trc
-rw-r----- 1 oracle dba 638 Jun 12 13:43 prod_ora_6619.trc
-rw-r----- 1 oracle dba 695 Jun 12 13:43 prod_ora_6642.trc
-rw-r----- 1 oracle dba 6161 Jun 12 15:29 prod_ora_6644.trc
-rw-r----- 1 oracle dba 695 Jun 12 16:25 prod_ora_7693.trc
-rw-r----- 1 oracle dba 666 Jun 12 16:25 prod_ora_7668.trc
-rw-r----- 1 oracle dba 10260 Jun 12 16:40 prod_ora_7694.trc

6. Check the latest trace file and use vi to edit the file. Delete all the statement in trace file until the file set 2 STARTUP NOMOUNT.

Below is the trace file, which i deleted the unnecessary statement until the startup nomount. STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top

7. Edit the trace file. Change the word 'REUSE' to 'set' and the 'olddbname' to ‘newdbname’.

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS

This is how the file will look after modification

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/oradata/prod/redo01.log' SIZE 50M,
GROUP 2 '/u02/oradata/prod/redo02.log' SIZE 50M,
GROUP 3 '/u02/oradata/prod/redo03.log' SIZE 50M

-- STANDBY LOG FILE
DATAFILE
'/u02/oradata/prod/system01.dbf',
'/u02/oradata/prod/undotbs01.dbf',
'/u02/oradata/prod/sysaux01.dbf',
'/u02/oradata/prod/users01.dbf'
CHARACTER SET WE8ISO8859P1;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
"prod_ora_7694.trc" 136L, 5750C 1,0-1 Top

Note: SAVE the modified trace file to a new name.sql such as “rename.sql”

8. Connect to sqlplus and startup the database in nomount stage and check whether the database using spfile or pfile.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218944 bytes
Variable Size 79693440 bytes
Database Buffers 192937984 bytes
Redo Buffers 2973696 bytes

SQL> show parameter spfile;
NAME TYPE VALUE
----------- ------------------------------
spfile string

SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string prod

9. Change the db_name in spfile to the new database name

SQL> alter system set db_name=test scope=spfile;
System altered.

SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_name string test
10. Shutdown the database, rename the existing controlfile and run the script

[oracle@localhost udump]$ cd /u02
[oracle@localhost u02]$ cd oradata/prod
[oracle@localhost prod]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
control02.ctl redo02.log system01.dbf users01.dbf
control03.ctl redo03.log temp01.dbf
[oracle@localhost prod]$ mv control01.ctl con1.ctl
[oracle@localhost prod]$ mv control02.ctl con2.ctl
oracle@localhost prod]$ mv control03.ctl con3.ctl

[oracle@localhost prod]$ lscon1.ctl con3.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf control2.ctl redo01.log redo03.log system01.dbf undotbs01.dbf

11. Run The Script
SQL>@$ORACLE_HOME/admin/prod/udump/rename

ORA-00308: cannot open archived log 'ARCH:
'ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested filename AUTO CANCEL}

11. When we encounter the statement above apply the logfiles path

Specify log: {=suggested filename AUTO CANCEL}
/u02/oradata/prod/redo02.log
Log applied.
Media recovery complete.

12. Once the media recovery complete, open the database with resetlogs
SQL> alter database open resetlogs;
Database altered.

13. The global database name may also need to be changed

SQL> alter database rename global_name to test;
Database altered.

14. To confirm the database name already been changed,

select the database name from v$database.

SQL> select name from v$database;
NAME
---------
TEST