In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Sunday, January 22, 2012

Steps to Rename RAC instance name

Steps to Rename RAC instance name

In this demo, The RAC database name:- RACDB
I have renamed the database instance racdb1 to rac1db (HOSTNAME DC1)
and instance racdb2 to rac2db (HOSTNAME DC2)
1. take a backup of the initracdb1.ora and initracdb2.ora files.
[oracle@dc1 dbs]$ cp initracdb1.ora initracdb1.ora_bkp
[oracle@dc1 dbs]$ cat initracdb1.ora
SPFILE='+DATA/proddb/spfileracdb.ora'
2. create a new pfile from spfile
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/proddb/spfileracdb.ora
SQL> create pfile='$ORACLE_HOME/dbs/initrac2db.ora' from spfile;

File created.
3. Shutdown the instance on all nodes
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
4. Rename or Edit the racdb1 and racdb2 to rac1db and rac2db respectively.

before changing the instance names in init.ora file
racdb2.__db_cache_size=708837376
racdb1.__db_cache_size=708837376
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb1.__oracle_base='/u01/app/oracle'
racdb2.__oracle_base='/u01/app/oracle'
racdb2.__pga_aggregate_target=394264576
racdb1.__pga_aggregate_target=394264576
racdb2.__sga_target=1056964608
racdb1.__sga_target=1056964608
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=331350016
racdb1.__shared_pool_size=331350016
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
racdb2.thread=2
racdb1.thread=1
racdb2.undo_tablespace='UNDOTBS2'
racdb1.undo_tablespace='UNDOTBS1'
after editing
rac2db.__db_cache_size=708837376
rac1db.__db_cache_size=708837376
rac2db.__java_pool_size=4194304
rac1db.__java_pool_size=4194304
rac2db.__large_pool_size=4194304
rac1db.__large_pool_size=4194304
rac1db.__oracle_base='/u01/app/oracle'
rac2db.__oracle_base='/u01/app/oracle'
rac2db.__pga_aggregate_target=394264576
rac1db.__pga_aggregate_target=394264576
rac2db.__sga_target=1056964608
rac1db.__sga_target=1056964608
rac2db.__shared_io_pool_size=0
rac1db.__shared_io_pool_size=0
rac2db.__shared_pool_size=331350016
rac1db.__shared_pool_size=331350016
rac2db.__streams_pool_size=0
rac1db.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/proddb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/proddb/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
rac2db.instance_number=2
rac1db.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=150
*.remote_listener='dc-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1053818880
rac2db.thread=2
rac1db.thread=1
rac2db.undo_tablespace='UNDOTBS2'
rac1db.undo_tablespace='UNDOTBS1'
5.create a new password file on all the nodes on new instance SID names.
[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwrac1db password=oracle
[oracle@dc1 dbs]$ 
[oracle@dc2 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwrac2db password=oracle

6.Add an entry in the /etc/oratab file on all the nodes
example:-
on host machine DC1
vi /etc/oratab
rac1db:/u01/app/oracle/product/11.2.0/db_1:N
on host machine DC2
vi /etc/oratab
rac2db:/u01/app/oracle/product/11.2.0/db_1:N

7.Startup the database with new init.ora file and create a spfile using new pfile.
[oracle@dc1 dbs]$ . oraenv
ORACLE_SID = [rac2db] ? rac1db
[oracle@dc1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 21 06:05:54 2012

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             339740916 bytes
Database Buffers          708837376 bytes
Redo Buffers                4673536 bytes
SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile;

File created.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> 
8.Edit/add the spfile location in the initrac1db.ora and initrac2db.ora file
example:-
[oracle@dc2 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

[oracle@dc1 dbs]$ vi initrac2db.ora
SPFILE='+DATA/proddb/spfileracdb.ora'

9.remove the old instance name and add a new instance name using srvctl command.
[oracle@dc2 dbs]$ srvctl remove instance -i racdb1 -d racdb 
Remove instance from the database racdb? (y/[n]) y
[oracle@dc2 dbs]$ srvctl remove instance -i racdb2 -d racdb
Remove instance from the database racdb? (y/[n]) y

[oracle@dc2 dbs]$ srvctl add instance -i rac1db -d racdb -n dc1
[oracle@dc2 dbs]$ srvctl add instance -i rac2db -d racdb -n dc2
[oracle@dc2 dbs]$ srvctl start database -d racdb
10.Verify it.
SQL> select instance_name,status from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
rac2db           OPEN
rac1db           OPEN

SQL> select name from v$database;

NAME
---------
RACDB

[oracle@dc2 dbs]$ srvctl config database -d racdb
Database unique name: racdb
Database name: 
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: 
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: racdb
Database instances: rac1db,rac2db
Disk Groups: 
Services: 
Database is administrator managed

Thanks and Regards,
Rajesh Kumar Govindarajan.

5 comments:

umair nazir said...

Hello,

Your this solution helped me out yesterday when I converted my new PROD single instance to RAC with wrong instance names. I mistakenly set the parameter under ConvertToRAC.xml file to TEST(which I did in TEST environment), which created the instances to TEST1 and TEST2. then on searching I found your blog and it really worked. Thanks alot.

umair nazir said...

Your this post save my life ;) yesterday, when I mistakenly set the attribute under ConvertToRAC.xml file (to be used by rconfig) to "TEST" for my PROD database and as a result it created the instances as TEST1 and TEST2 instead of PROD1 and PROD2 obviously. But your post helped me out of the issue. Thanks alot. God bless you.


Regards,
Muhammad

umair nazir said...

Your post helped me out yesterday and saved my life, as i mistakenly set instances name for PROD database to TEST1 and TEST2 as I did on TEST. Thanks alot. God bless you.


Regards,
Muhammad

Anonymous said...

Rajeshkumar Govindarajan,

Hi! After followed the steps you post, I tried twice and found the same error ORA-29760: instance_number parameter not specified. Final, we found spfile changed and had to modify under clusterware service. Thanks!

Ajay Chaudhary said...

Thank you so much, its been really useful.

 
Share/Bookmark