Renaming an Oracle database 11G

This article presents Renaming Database Name

So, Sometime we need to change Database name, Actually its uncommon issue however again its importat for us. There are two methots

Lets begin step by step.

1. First methot Renaming Database Name

First lets look at our database_name will be renamed.

SQL> select name from v$database;

 NAME

---------

ORCL

1.Close your database

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2.Make database mounted

SQL> startup mount

ORACLE instance started.

Total System Global Area 1121554432 bytes

Fixed Size 1336008 bytes

Variable Size 889195832 bytes

Database Buffers 218103808 bytes

Redo Buffers 12918784 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Write new dbname

[oracle@mhm /]$ nid target=sys/Oracle_4u@ORCL dbname=TEST_ORCL

DBNEWID: Release 11.2.0.1.0 - Production on Sun Oct 23 22:34:28 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database ORCL (DBID=1272787738)

Connected to server version 11.2.0

Control Files in database:

+DATA/orcl/controlfile/current.260.745007003

+FRA/orcl/controlfile/current.256.745007003

Change database ID and database name ORCL to TEST_ORCL? (Y/[N]) => Y

Go to SQL*plus with SYS User

And set new database name

SQL> ALTER SYSTEM SET DB_NAME=test_orcl SCOPE=SPFILE;

Cloese database

SQL> shu abort;

Now create new password

 [oracle@mhm /]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/pwdtest_orcl.ora

password=password entries=10

Create pfile from spifle

SQL>sqlplus / as sysdba

SQL>create pfile=’inittest_orcl.ora’ from spfile;

Open the initnew.ora and change the DBNAME as new

Again cloese the database.

SQL> Shutdown immediate;

Now export new name

oracle@mhm /]$ ORACLE_SID=test_orcl

oracle@mhm /]$ export ORACLE_SID

Now for TNS and Listener to be know new database Lsnrtl reloaded

Lsnrctl reload

And now open Database at Resetlogs;

SQL> startup mount;

SQL> alter database open resetlogs;

You finished so check your new database name.

SQL> select name from v$database;

 NAME

---------

TEST_ORCL

2.Second methot renaming database

SQL> select name from v$database; NAME

---------

ORCL

Now backup control file as a trace.

SQL> Alter database backup controlfile to trace;

Save the trace as script .

[oracle@orcl ~]$ mv orcl_ora_2884.trc back_cntrl_script.sql

Now open the script and delete until.

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS NOARCHIVELOG

Now change “REUSE” to “SET” and write new database name.

CREATE CONTROLFILE SET DATABASE “TEST_ORCL” RESETLOGS NOARCHIVELOG

And delete the

RECOVER DATABASE USING BACKUP CONTROLFILE;

Your script should be like following

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TEST_ORCL" 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 32767M;

 Shutdown your database;

SQL> shutdown immediate;

Backup your  init<SID>.ora and open it, change  db_name, instance_name parameters as a new database_name save it.

Now run the script (back_cntrl_script.sql)

SQL> @/oracle/ back_cntrl_script.sql

You finished it Now check your new database name

SQL> SELECT NAME FROM V$DATABASE;

SQL> select name from v$database;

 NAME

---------

TEST_ORCL