Thursday, April 23, 2015

Change the Database SID


I am using Oracle Enterprise Linux 6.5 and Database version is 11.2.0.4
Initially My Database SID is ORCL  and I want it to change to ORCL2

Step 1 - You need to Back up the database using RMAN backup set or exp full back up

Step 2 -Mount it after clean shut down.
sql>shutdown immediate
sql>startup mount

Step 3 -Create pfile from the spfile;
sql>create pfile from spfile;

Step 4 -Use DBNEWID utility to change the DBNAME.Use SYSDBA privileges username and password and dbname parameter is the new DBname.
os>nid target=sys/oracle dbname=SAM2































Step 5 - shutdown the database
sql>shutdown immediate

Step 6 -Modify the DB_NAME parameter in the initialization parameter file
use either  one of methods

Method A
Directly edit initORCL.ora file  in $ORACLE_HOME\dbs location.
initORCL.ora
DB_NAME=ORCL2
startup mount pfile=' initORCL.ora'

Method B
The startup will result in an error but proceed anyway.
startup mount
alter system set db_name=ORCL2 scope=spfile;
shutdown immediate


Step 7 - Create a new password file:
os>orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/pwdORCL2.ora password=password entries=10

Step 8- Rename the spfile init file to match the new name.

Step 9-Change the environment for the new DB NAME
export ORACLE_SID=ORCL2;

Step 10 - Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

lsnrctl reload

ORCL2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL2)
    )
  )

 Step 11 -
Open the database with RESETLOGS:

startup mount
alter database open resetlogs;

Step 12 - Backup the database.

No comments:

Post a Comment