Renaming Database


Well Rename your database in a very easy way..without affecting the DBID or to the SID...



Step 1 :  Backup Controlfile to text

Step 2:  Create  a pfile of your database

Step 3:  Edit your controlfile with database name...keeping only resetlogs

Step 4:  Edit pfile with dbname and controlfile parameter..

Step 5:  Startup with nomount option with the updated pfile..

Step 6:  Create a new controlfile

Step 7:  Update the new controlfile in pfile and Startup with that pfile

Step 8:  Create a new Spfile from the updated Pfile....

              Thats it Database is renamed..........


Sql > alter database backup controlfile to trace as '/home/oracle/control.txt

[ oracle@localhost :~ ]  vi control.txt


CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/home/oracle/app/oracle/oradata/oracle/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/home/oracle/app/oracle/oradata/oracle/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/home/oracle/app/oracle/oradata/oracle/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/home/oracle/app/oracle/oradata/oracle/system01.dbf',
  '/home/oracle/app/oracle/oradata/oracle/sysaux01.dbf',
  '/home/oracle/app/oracle/oradata/oracle/undotbs01.dbf',
  '/home/oracle/app/oracle/oradata/oracle/users01.dbf',
  '/home/oracle/app/oracle/oradata/oracle/example01.dbf',
  '/home/oracle/app/oracle/oradata/oracle/recovery01.dbf'
CHARACTER SET WE8MSWIN1252
;

Update the file with 2 things...

1. Change REUSE to SET in the First Line.....

    like  CREATE CONTROLFILE SET DATABASE "ORACLE" RESETLOGS  ARCHIVELOG

2 . Update New Database Name :

  like CREATE CONTROLFILE SET DATABASE "ORA" RESETLOGS  ARCHIVELOG


Thats it....with controlfile..... save and exit that file ...



[ oracle@localhost :~ ] cd $ORACLE_HOME/dbs

[ oracle@localhost dbs ] vi initoracle.ora


oracle.__streams_pool_size=0
*.audit_file_dest='/home/oracle/app/oracle/admin/oracle/adump'
*.audit_trail='db'
*.compatible='11.2.0.1.0'
*.control_files='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/cntrloracle.dbf'
*.db_block_size=8192
*.db_domain=''
*.db_name='oracle'
*.diagnostic_dest='/home/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracleXDB)'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
~                            
Here also we have to update 2 things

1 . db_name =

  like *.db_name='oracle'  to *.db_name='ora'

2. Comment the controfile.....parameter...

  like *.control_files='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/cntrloracle.dbf'

    to #*.control_files='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/cntrloracle.dbf'...


Thats it with pfile....


Next : -


[oracle@localhost dbs]$ export ORACLE_SID=oracle
[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 14 16:12:21 2014

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

Connected to an idle instance.

SQL> startup nomount pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initoracle.ora
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             268437888 bytes
Database Buffers          146800640 bytes
Redo Buffers                6094848 bytes

SQL> @/home/oracle/controlfile
SP2-0310: unable to open file "/home/oracle/controlfile.sql"


SQL> @/home/oracle/control.txt

Control file created.

SQL>   Thats it....done

SQL> select * from v$controlfile
  2  ;

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/cntrloracle.dbf
NO       16384            614


Update this file in the pfile and startup the database with the new pfile and create spfile from that..


That's all Rename of Database is done....