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: {
11. When we encounter the statement above apply the logfiles path
Specify log: {
/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
