Renaming an Oracle database

Follow below steps to rename database name from UATDB to UATNEWDB.

Step1: In UATDB, connect to SQLPLUS and issue command "alter database backup controlfile to trace;". this command generate a script containg a create controlfile

command and stored it in trace (Udump) directory. you can find location as USER_DUMP_DEST paramenter in parameter file.

Step2: The trace file output looks like below.

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATDB/system01.dbf',
  '/oracle/oradata/UATDB/sysaux01.dbf',
  '/oracle/oradata/UATDB/users01.dbf',
  '/oracle/oradata/UATDB/undotbs0.dbf',
  '/oracle/oradata/UATDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

Step3: Shutdown the UATDB database

Step4: Change all locations to new DB name in Spfile, tnsnames and listener etc..

Step5: Change the database name in trace file that pointing to new DB name "UATNEWDB" like below

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATNEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATNEWDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATNEWDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATNEWDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATNEWDB/system01.dbf',
  '/oracle/oradata/UATNEWDB/sysaux01.dbf',
  '/oracle/oradata/UATNEWDB/users01.dbf',
  '/oracle/oradata/UATNEWDB/undotbs0.dbf',
  '/oracle/oradata/UATNEWDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

Step6: Run the above script to create new controlfiles and open database in resetlogs option.

Practical demonstration for renaming database name from UATDB to UATNEWDB:

$ export ORACLE_SID=UATDB

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 18:19:31 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> create pfile from spfile;
File created.

SQL> show parameter dump;  -- find the udump location where trace control file created

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Modify the trace file like below

$ cat UATDB_ctrl.sql

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "UATNEWDB" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 '/oracle/oradata/UATNEWDB/redo01.log'  SIZE 50M,
  GROUP 2 '/oracle/oradata/UATNEWDB/redo02.log'  SIZE 50M,
  GROUP 3 '/oracle/oradata/UATNEWDB/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/UATNEWDB/system01.dbf',
  '/oracle/oradata/UATNEWDB/sysaux01.dbf',
  '/oracle/oradata/UATNEWDB/users01.dbf',
  '/oracle/oradata/UATNEWDB/undotbs0.dbf',
  '/oracle/oradata/UATNEWDB/DATA01.dbf'
CHARACTER SET WE8ISO8859P1
;

$

Modify prameters in initUATDB.ora file and change file to initUATNEWDB.ora

$ mv initUATDB.ora initUATNEWDB.ora

Just backup of old controlfiles

$ mv control01.ctl control01.ctl_bkp
$ mv control02.ctl control02.ctl_bkp
$ mv control03.ctl control03.ctl_bkp
$

$ export ORACLE_SID=UATNEWDB
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 19:10:39 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.
SQL>
SQL> @UATDB_ctrl.sql
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2020960 bytes
Variable Size             285215136 bytes
Database Buffers         1224736768 bytes
Redo Buffers               14753792 bytes

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select file_name from dba_data_files;

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> exit

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 19:15:06 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area 1526726656 bytes
Fixed Size                  2020960 bytes
Variable Size             369101216 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL>

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
$

Finally create Temporary tablespace, make it as default and create password file for new DB "UATNEWDB"

Create tempfile and make aa a default:

SQL> create temporary tablespace TEMP01 tempfile '/oracle1/oradata/uatnewdb/temp01.dbf' size 1024M autoextend on;

SQL> alter database default temporary tablespace TEMP01;


make an entry to /etc/oratab:

$ vi  /etc/oratab

CREATE PASSWORD FILE FOR NEW DB:

orapwd force=y file=orapwUATNEWDB password=sys entries=5

Thats It!!!

Hope this will help you...:)

Best Regards,