A journey towards oracle.....!!!: Change DB NAME for RAC Database

Changing DB_NAME for a RAC database is no different from a single instance database. The RAC database is opened as single instance (with CLUSTER_DATABASE =FALSE), then the DB_NAME is changed just like we do in case of a single instance database and then put this single instance database back into RAC.I recently did this for one of my 11gR2 (11.2.0.1) database, so thought of documenting it.

[/tmpbackup/CM441758/scr]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CIDCQ1_DISK_GROUP_01.dg
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
ora.CIDCQ1_DISK_GROUP_02.dg
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
ora.LISTENER.lsnr
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
ora.asm
               ONLINE ONLINE       gvx0acidb01q             Started
               ONLINE ONLINE       gvx0acidb02q             Started
ora.eons
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
ora.gsd
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
ora.net1.network
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
ora.ons
               ONLINE ONLINE       gvx0acidb01q
               ONLINE ONLINE       gvx0acidb02q
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE ONLINE       gvx0acidb02q
ora.cidcq1.cidcq1_s1.svc
      1        ONLINE ONLINE       gvx0acidb01q
ora.cidcq1.cidcq1_s2.svc
      1        ONLINE ONLINE       gvx0acidb02q
ora.cidcq1.db
      1        ONLINE ONLINE       gvx0acidb01q             Open
      2        ONLINE ONLINE       gvx0acidb02q             Open
ora.cidcq1.infaadhoc.svc
      1        ONLINE ONLINE       gvx0acidb02q
      2        ONLINE ONLINE       gvx0acidb01q
ora.gvx0acidb01q.vip
      1        ONLINE ONLINE       gvx0acidb01q
ora.gvx0acidb02q.vip
      1        ONLINE ONLINE       gvx0acidb02q
ora.oc4j
      1        ONLINE ONLINE       gvx0acidb02q
ora.scan1.vip
      1        ONLINE ONLINE       gvx0acidb02q
oracle @ gvx0acidb01q:cidcq11
[/tmpbackup/CM441758/scr]$

I need to change the database name form cidcq1 to cidcq2. The cidcq1 database is running on a two node 11gR2 cluster.Below is the sequence of steps to be following to achieve this.

1) Create a backup pfile and controlfile

[/home/oracle]$ export ORACLE_SID=cidcq11
[/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 5 12:35:28 2011

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create pfile=’/tmp/initcidcq2.ora’ from spfile;

Change the following parameters in initcidcq2.ora.

*.cluster_database=FALSE
*.db_name='cidcq2'
*.control_files='+CIDCQ1_DISK_GROUP_01'

The OMF controlfile name is to be changed with the diskgroup.The reason this is required to be changed is because when you will create pfile from spfile, the control file will be written as OMF (like control_files=’+CIDCQ1_DISK_GROUP_01/CIDCQ1/CONTROLFILE/control.345.7654563') and when we are going to create controlfile later for changing db name, it will generate the new controlfile as OMF. This is not required if controlfile is not created as OMF in your database.
Accordingly changes other parameters (replace cidcq1 to cidcq2 whereever required)

Now, extract controlfile script using,

SQL> alter database backup controlfile to trace;

From Controlfile trace, extract the controlfile creation script and make the following change

REUSE to SET and datbase name from CIDCQ1 to CIDCQ2

CREATE CONTROLFILE REUSE DATABASE "CIDCQ1" RESETLOGS ARCHIVELOG
to
CREATE CONTROLFILE REUSE SET DATABASE "CIDCQ2" RESETLOGS ARCHIVELOG

Save the controlfile script as ctl.sql.

2) Shutdown the database from cluster
$ srvctl stop database –d cidcq1

3) Startup the database using changed initcidcq2.ora and re-create controlfile.

SQL> start nomount pfile=’/tmp/initcidcq2.ora’

SQL> @ctl.sql

This will create the controlfile and database name is changed.

SQL> alter database open resetlogs;

4) Add 2nd thread of online redologs.

SQL>alter database add logfile group 3 ‘+DISK_GROUP02’ thread 2;

SQL>alter database add logfile group 4 ‘+DISK_GROUP02’ thread 2;

SQL>alter database enable public thread 2;

5) Shutdown the database create spfile from pfile;

Shutdown the database and modify control_files parameters in /tmp/initcidcq2.ora to the actual controlfile name created.

*.control_files='+CIDCQ1_DISK_GROUP_01'

*.control_files='+CIDCQ1_DISK_GROUP_01/CIDCQ2/CONTROLFILE/control.400.7849384''

Change parameter cluster_database to TRUE.

SQL> startup nomount

SQL> create spfile=’+DISK_GROUP02/cidcq2/parameterfile/spfilecidcq2.ora’

6) Add database cidcq2 to cluster

$ srvctl add database –d cidcq2 –o /u01/app/oracle/product/11.2.0/db_home1 –p +DISK_GROUP02/cidcq2/parameterfile/spfilecidcq2.ora

$ srvctl add instance –i cidcq21 –n gvx0acidb01q

$ srvctl add instance –i cidcq22 –n gvx0acidb02q

7) Bring up the database in cluster

$ srvctl start database –d cidcq2

The database is now up as cidcq2. later the entries for cidcq1 can be removed and database can be registered in RMAN catalog for backup scripts to work.

Cheers…!!!

Rishi