Renaming the Oracle Database

Hi Friends,

Sometimes we need to change the name of our database. In this article, I’ll tell you step by step how to change the name of the Oracle database. For example, our existing database name “MYDB” and we want to change our name to “TALIPDB”.

1 – Backup control files on the operating system

$ Mv talip_ctl_1.ora talip_ctl_1.ora.old

$ Mv talip_ctl_2.ora talip_ctl_2.ora.old

$ Mv talip_ctl_3.ora talip_ctl_3.ora.old

2 – Connect sqlplus with SYS user and take trace file of controlfile
SQL> alter database backup controlfile to trace;

3 -Save as a script file created under “udump” folder

$ Mv mydb_ora_24273.trc controlfile_script.sql

4 – Open the generated script with “vi” and edit as below

A- Delete all of the rows until following line

Startup nomount

Controlfile CREATE REUSE DATABASE “MYDB” RESETLOGS NOARCHIVELOG

B- Replace REUSE statement with SET statement and write new database name as following line

Controlfile SET CREATE DATABASE “TALIPDB” RESETLOGS NOARCHIVELOG

C- Delete “RECOVER DATABASE USING BACKUP controlfile” line at the bottom of script

The final version of the script will look like the following script.

Startup nomount

Controlfile SET CREATE DATABASE “TALIPDB” RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MaxInstances 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘/ oracle/ora11g/oradata/redo01.log’ SIZE 50M blocksize 512,

GROUP 2 ‘/ oracle/ora11g / oradata/redo02.log’ SIZE 50M blocksize 512,

GROUP 3 ‘/ oracle/ora11g / oradata/redo03.log’ SIZE 50M blocksize 512,

DataFile

‘/ Oracle/ora11g/oradata/system01.dbf’,

‘/ Oracle/ora11g/oradata/sysaux01.dbf’,

‘/ Oracle/ora11g/oradata/undotbs01.dbf’,

‘/ Oracle/ora11g/oradata/users01.dbf’,

‘/ Oracle/ora11g/oradata/example01.dbf’,

‘/ Oracle/ora11g/oradata/myts01.dbf’

CHARACTER SET WE8ISO8859P9

;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD tempfile ‘/ oracle/ora11g/oradata/temp01.dbf’

SIZE 441450496 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767;

5 – The database is closed.

SQL> shutdown immediate;

6 – Backup init<SID>.ora file located in $ ORACLE_HOME / dbs directory. Open init<SID>.ora file with “VI” text editor and set db_name, instance_name parameters to new name. If you use spfile then you must create pfile from spfile before shutdown the database.

7 – Run the script file

SQL> @ / oracle / controlfile_script.sql

8 – Our database will open with new name

We can query our new database name;
SQL> SELECT NAME FROM V $ DATABASE;

Talip Hakan Ozturk