Change RAC database Name using NID

Scenario: A RAC database "HOMEDB" name is needed to be changed to "HOMEATT"
Environment: 2 node Oracle RAC 11gR2 11.2.0.3 (Windows 2008R2)


1) Shutdown RAC DB and remove it from cluster
D:\app\11.2.0.3\grid\BIN>srvctl stop database -d homedb
D:\app\11.2.0.3\grid\BIN>srvctl remove database -d homedb
Remove the database homedb? (y/[n]) y

2) Mount the database (as single instance) , create the pfile for single instance and remove all cluster parameters. You could take the current spfile contents (to edit) as pfile to start it as single instance using create pfile=yourlocation from spfile.

inithomedb.ora
large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\inam\admin\homedb\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+dbdata/homedb/controlfile/control01.ctl'
db_block_size=8192
db_domain=''
db_name='homedb'
db_recovery_file_dest='+DBFLASH'
db_recovery_file_dest_size=10G
diagnostic_dest='D:\app\inam'
remote_login_passwordfile='exclusive'#homedb1.instance_number=1
#homedb2.instance_number=2
#cluster_database=true
#cluster_database_instances=2

C:\Users\inam>set oracle_sid=homedb

C:\Users\inam>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 28 13:27:16 2013

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

Connected to an idle instance.

SQL> startup mount pfile=D:\rmanbackup\inithomedb.ora
ORACLE instance started.

Total System Global Area 1553379328 bytes
Fixed Size                  2255464 bytes
Variable Size            1224738200 bytes
Database Buffers          318767104 bytes
Redo Buffers                7618560 bytes
Database mounted.
SQL>

SQL> exit

3) run nid utility
nid TARGET=sys/ORACLE11g dbname=homeatt
Target=> source DB being changed
dbname=> new name for source db (Target)

C:\Users\inam>nid TARGET=sys/ORACLE11g dbname=homeatt

DBNEWID: Release 11.2.0.3.0 - Production on Tue May 28 13:46:30 2013

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

Connected to database homedb (DBID=1547250382)

Connected to server version 11.2.0

Control Files in database:
    +DBDATA/homedb/controlfile/control01.ctl

Change database ID and database name homedb to homeatt? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1547250382 to 2444112951
Changing database name from homedb to homeatt
    Control File +DBDATA/homedb/controlfile/control01.ctl - modified
    Datafile +DBDATA/homedb/datafile/system.326.81660863 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/sysaux.325.81660863 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/undotbs1.322.81660926 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/users.343.81660818 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/example.321.81660927 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/undotbs2.319.81660964 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/homelog.324.81660863 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/testt_netbkup.344.81660819 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/recop1.297.81660964 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/homelog.340.81660777 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/home.323.81660926 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/home.320.81660964 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/home.341.81660818 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/home.338.81660776 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/home.345.81660863 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/undotbs1.342.81660818 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/datafile/undotbs2.339.81660777 - dbid changed, wrote new name
    Datafile +DBDATA/homedb/tempfile/temp.336.81661574 - dbid changed, wrote new name
    Control File +DBDATA/homedb/controlfile/control01.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to homeatt.
Modify parameter file and generate a new password file before restarting.
Database ID for database homeatt changed to 2444112951.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

4) Create parameter file for the new name (db, see parameters audit_file_dest,control_files,db_name
create/rename necessary folders on OS level. Don't enable the cluster parameters at this point.
inithomeatt.ora
large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\inam\admin\homeatt\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+dbdata/homeatt/controlfile/control01.ctl'
db_block_size=8192
db_domain=''
db_name='homeatt'
db_recovery_file_dest='+DBFLASH'
db_recovery_file_dest_size=10G
diagnostic_dest='D:\app\inam'
remote_login_passwordfile='exclusive'
#homedb1.instance_number=1
#homedb2.instance_number=2
#cluster_database=true
#cluster_database_instances=2

5) Rename the related directories in ASM first (for homeatt db) and then modify controlfile to have these changes in it also.
-- Rename  directory from "homedb" to "homeatt".
C:\Users\inam>set oracle_sid=+ASM1
C:\Users\inam>set oracle_home=D:\app\Inam\product\11.2.0.3\dbhome_1

C:\Users\inam>sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 28 14:27:14 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> ALTER DISKGROUP DBDATA RENAME DIRECTORY '+dbdata/homedb' TO '+dbdata/homeatt';
Diskgroup altered.
SQL>


-- Rename files in controlfile
a) First create the new  instance for homeatt
C:\Users\inam>oradim -new -sid homeatt
Instance created.
b) mount Database with modified parameter file
C:\Users\inam>set oracle_sid=homeatt
C:\Users\inam>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 28 14:35:55 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.SQL> startup mount pfile=D:\rmanbackup\inithomeatt.ora
ORACLE instance started.

Total System Global Area 1553379328 bytes
Fixed Size                  2255464 bytes
Variable Size            1224738200 bytes
Database Buffers          318767104 bytes
Redo Buffers                7618560 bytes
Database mounted.

c) Check the datafils and rename them
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
+DBDATA/homedb/datafile/system.326.816608633
+DBDATA/homedb/datafile/sysaux.325.816608635
+DBDATA/homedb/datafile/undotbs1.322.816609269
+DBDATA/homedb/datafile/users.343.816608189
+DBDATA/homedb/datafile/example.321.816609273
+DBDATA/homedb/datafile/undotbs2.319.816609645
+DBDATA/homedb/datafile/homelog.324.816608635
+DBDATA/homedb/datafile/testt_netbkup.344.816608191
+DBDATA/homedb/datafile/recop1.297.816609649
+DBDATA/homedb/datafile/homelog.340.816607773
+DBDATA/homedb/datafile/home.323.816609267
NAME
-----------------------------------------------------------------------
+DBDATA/homedb/datafile/home.320.816609643
+DBDATA/homedb/datafile/home.341.816608185
+DBDATA/homedb/datafile/home.338.816607769
+DBDATA/homedb/datafile/home.345.816608631
+DBDATA/homedb/datafile/undotbs1.342.816608187
+DBDATA/homedb/datafile/undotbs2.339.816607771

now rename files

SQL> Alter database rename file '+DBDATA/homedb/datafile/system.326.816608633' to '+DBDATA/homeatt/datafile/system.326.8
16608633';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/system.326.816608633' to '+DBDATA/homeatt/datafile/system.326.8
16608633';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/sysaux.325.816608635' to '+DBDATA/homeatt/datafile/sysaux.325.8
16608635';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/users.343.816608189' to '+DBDATA/homeatt/datafile/users.343.816
608189';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/example.321.816609273' to '+DBDATA/homeatt/datafile/example.321
.816609273';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/homelog.324.816608635' to '+DBDATA/homeatt/datafile/homelog.324
.816608635';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/testt_netbkup.344.816608191' to '+DBDATA/homeatt/datafile/testt
_netbkup.344.816608191';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/recop1.297.816609649' to '+DBDATA/homeatt/datafile/recop1.297.8
16609649';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/homelog.340.816607773' to '+DBDATA/homeatt/datafile/homelog.340
.816607773';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/home.323.816609267' to '+DBDATA/homeatt/datafile/home.323.81660
9267';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/home.320.816609643' to '+DBDATA/homeatt/datafile/home.320.81660
9643';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/home.341.816608185' to '+DBDATA/homeatt/datafile/home.341.81660
8185';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/home.338.816607769' to '+DBDATA/homeatt/datafile/home.338.81660
7769';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/home.345.816608631' to '+DBDATA/homeatt/datafile/home.345.81660
8631';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/undotbs1.342.816608187' to '+DBDATA/homeatt/datafile/undotbs1.3
42.816608187';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/undotbs2.339.816607771' to '+DBDATA/homeatt/datafile/undotbs2.3
39.816607771';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/undotbs1.322.816609269' to '+DBDATA/homeatt/datafile/undotbs1.3
22.816609269';

Database altered.

SQL> Alter database rename file '+DBDATA/homedb/datafile/undotbs2.319.816609645' to '+DBDATA/homeatt/datafile/undotbs2.3
19.816609645';

Database altered.

d) rename tempfile also
SQL> select name from v$tempfile;
NAME
-------------------------------------------------
+DBDATA/homedb/tempfile/temp.336.816615741

SQL> Alter database rename file '+DBDATA/homedb/tempfile/temp.336.816615741' to '+DBDATA/homeatt/tempfile/temp.336.816615741';
Database altered.

e) rename logfiles
SQL> select member from v$logfile;

MEMBER
-------------------------------------------------
+DBFLASH/homedb/onlinelog/group_1.2877.816611313
+DBFLASH/homedb/onlinelog/group_2.2868.816611315
+DBFLASH/homedb/onlinelog/group_3.2867.816611319
+DBFLASH/homedb/onlinelog/group_4.3658.816611323


SQL> Alter database rename file '+DBFLASH/homedb/onlinelog/group_1.2877.816611313' to '+DBFLASH/homeatt/onlinelog/group_
1.2877.816611313';

Database altered.

SQL> Alter database rename file '+DBFLASH/homedb/onlinelog/group_2.2868.816611315' to '+DBFLASH/homeatt/onlinelog/group_
2.2868.816611315';

Database altered.

SQL> Alter database rename file '+DBFLASH/homedb/onlinelog/group_3.2867.816611319' to '+DBFLASH/homeatt/onlinelog/group_
3.2867.816611319';

Database altered.

SQL> Alter database rename file '+DBFLASH/homedb/onlinelog/group_4.3658.816611323' to '+DBFLASH/homeatt/onlinelog/group_
4.3658.816611323';

Database altered.

6) Open databaase (homeatt) with resetlogs
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

7) Now make single instance (homeatt) as clustered database.

a) Enable cluster parameters
inithomeatt.ora
large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\inam\admin\homeatt\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+dbdata/homeatt/controlfile/control01.ctl'
db_block_size=8192
db_domain=''
db_name='homeatt'
db_recovery_file_dest='+DBFLASH'
db_recovery_file_dest_size=10G
diagnostic_dest='D:\app\inam'
remote_login_passwordfile='exclusive'
homeatt1.instance_number=1
homeatt2.instance_number=2
cluster_database=true
cluster_database_instances=2

b) Create spfile on shared storage for homeatt
SQL> create spfile='+DBDATA' from pfile='D:\rmanbackup\inithomeatt.ora';
File created
shutdown DB (single instance which was run by pfile in point 5b)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
c) Add the homeatt database now to cluster
D:\app\11.2.0.3\grid\BIN>srvctl add database -d homeatt -o D:\app\Inam\product\11.2.0.3\dbhome_1

d) Add the instances for homeatt
D:\app\11.2.0.3\grid\BIN>srvctl add instance -d homeatt -i homeatt1 -n or-11
D:\app\11.2.0.3\grid\BIN>srvctl add instance -d homeatt -i homeatt2 -n or-12

e) Create the parameter files on both nodes, first check the parameter file name via asmcmd
ASMCMD> pwd
+dbdata/homeatt/parameterfile
ASMCMD> ls
spfile.337.816682401

Now create inithomeatt1.ora and inithomeatt2.ora on both nodes with below line
on locatin D:\app\Inam\product\11.2.0.3\dbhome_1\database
SPFILE='+dbdata/homeatt/parameterfile/spfile.337.816682401'

f) Now start the database and check  on both nodes
D:\app\11.2.0.3\grid\BIN>srvctl start database -d homeatt
D:\app\11.2.0.3\grid\BIN>srvctl config database -d homeatt
Database unique name: homeatt
Database name:
Oracle home: D:\app\Inam\product\11.2.0.3\dbhome_1
Oracle user: nt authority\system
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: homeatt
Database instances: homeatt1,homeatt2
Disk Groups: DBDATA,DBFLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed

C:\Users\inam>set oracle_sid=homeatt1
C:\Users\inam>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 29 08:21:32 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
SQL> select Inst_id,instance_name from gv$instance;

   INST_ID INSTANCE_NAME
---------- ----------------
         1 homeatt1
         2 homeatt2

SQL>