Rename RAC Database

Here is the document to rename RAC database and next post we can see how to rename rac instance.

here is an example.

renamed RAC database proddb to racdb in servers dc1 and dc2.

SQL> select instance_name,host_name from gv$instance;

INSTANCE_NAME    HOST_NAME
---------------- -------------------------
proddb1          dc1.localdomain
proddb2          dc2.localdomain

SQL> select name from v$database;

NAME
---------
PRODDB

taken backup of pfile initproddb1.ora and initproddb2.ora on both nodes.

[oracle@dc1 dbs]$ cat initproddb1.ora
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc1 dbs]$ cp initproddb1.ora initproddb1.ora_orig 
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd $ORACLE_HOME/dbs
[oracle@dc2 dbs]$ cat initproddb2.ora 
SPFILE='+DATA/proddb/spfileproddb.ora'
[oracle@dc2 dbs]$ cp initproddb2.ora initproddb2.ora_bkp
[oracle@dc2 dbs]$ 

create pfile from spfile.
taken controlfile backup to rename rac database.

SQL> create pfile='/tmp/initproddb1.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --- or ----
SQL> 
SQL> alter database backup controlfile to trace as '/tmp/ctl_trc_bkp.txt';

Database altered.

stop the RAC database using srvctl command.

[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is running on node dc1
Instance proddb2 is running on node dc2
[oracle@dc1 ~]$ srvctl config database -d proddb
Database unique name: proddb
Database name: proddb
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/proddb/spfileproddb.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: proddb
Database instances: proddb1,proddb2
Disk Groups: DATA
Services: 
Database is administrator managed
[oracle@dc1 ~]$ 

[oracle@dc1 ~]$ srvctl stop database -d proddb
[oracle@dc1 ~]$ srvctl status database -d proddb
Instance proddb1 is not running on node dc1
Instance proddb2 is not running on node dc2
[oracle@dc1 ~]$ 

edit/replace proddb to racdb in the newly created pfile
and also the controlfile name.
change the value of the parameter cluster_database to false.

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
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'

startup the database in nomount mode with newly created pfile.

[oracle@dc1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 7 17:35:55 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/initproddb1.ora';
ORACLE instance started.

Total System Global Area 1054593024 bytes
Fixed Size                  1341196 bytes
Variable Size             276826356 bytes
Database Buffers          771751936 bytes
Redo Buffers                4673536 bytes
SQL> 

create a script using backup of controlfile trace to re-create controlfile(to rename database)  
CREATE CONTROLFILE SET DATABASE "RACDB" RESETLOGS  NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '+DATA/proddb/onlinelog/group_1.346.771671611'  SIZE 50M BLOCKSIZE 512,
GROUP 2 '+DATA/proddb/onlinelog/group_2.341.771671615'  SIZE 50M BLOCKSIZE 512
DATAFILE
'+DATA/proddb/datafile/system.256.771671395',
'+DATA/proddb/datafile/sysaux.282.771671401',
'+DATA/proddb/datafile/undotbs1.344.771671403',
'+DATA/proddb/datafile/users.343.771671403',
'+DATA/proddb/datafile/undotbs2.339.771671757'
CHARACTER SET WE8MSWIN1252
;

SQL> @'/tmp/create_ctl.sql';

Control file created.

SQL> sho parameter cluster_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1
cluster_interconnects                string
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

change the value of the parameter cluster_database to true and startup the database.

SQL> startup pfile='/tmp/initproddb1.ora';
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
Database mounted.
Database opened.

create new spfile using current pfile.

SQL> create SPFILE='+DATA/proddb/spfileracdb.ora' from pfile='/tmp/initproddb1.ora';

File created.

SQL> sho parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL> 
SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3,GROUP 4;

SQL> ALTER DATABASE OPEN RESETLOGS;

create a new init pfile in the dbs directory and copy and paste the spfile location.

[oracle@dc1 dbs]$ vi initracdb1.ora 
SPFILE='+DATA/proddb/spfileracdb.ora'
[oracle@dc1 dbs]$ scp initracdb1.ora dc2:$ORACLE_HOME/dbs/initracdb2.ora
initracdb1.ora                                100%   38     0.0KB/s   00:00    

create a new password file on both servers for database racdb.

[oracle@dc1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb1 password=oracle
[oracle@dc1 dbs]$ 

[oracle@dc2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@dc2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwracdb2.ora password=oracle
[oracle@dc2 dbs]$ 

add an entry in the /etc/oratab file on both servers
[oracle@dc2 ~]$vi /etc/oratab
racdb2:/u01/app/oracle/product/11.2.0/db_1:N

[oracle@dc1 ~]$vi /etc/oratab
racdb1:/u01/app/oracle/product/11.2.0/db_1:N
[oracle@dc2 ~]$ . oraenv
ORACLE_SID = [oracle] ? racdb2
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@dc2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 8 23:36:03 2012

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

Connected to an idle instance.

SQL> startup
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
Database mounted.
Database opened.
SQL> 

SQL> select name from v$database;

NAME
---------
RACDB

SQL> select instance_name,status from gv$Instance;

INSTANCE_NAME    STATUS
---------------- ------------
racdb1           OPEN
racdb2           OPEN

register the database with grid.

[oracle@dc1 dbs]$ srvctl status database -d racdb
PRCD-1120 : The resource for database racdb could not be found.
PRCR-1001 : Resource ora.racdb.db does not exist

[oracle@dc1 dbs]$ srvctl remove database -d proddb
Remove the database proddb? (y/[n]) y
[oracle@dc1 dbs]$ srvctl add database -d racdb -o $ORACLE_HOME
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb1 -n dc1
[oracle@dc1 dbs]$ srvctl add instance -d racdb -i racdb2 -n dc2

[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is not running on node dc1
Instance racdb2 is not running on node dc2

[oracle@dc1 dbs]$ srvctl start database -d racdb
[oracle@dc1 dbs]$ srvctl status database -d racdb
Instance racdb1 is running on node dc1
Instance racdb2 is running on node dc2

finally, Change the instance names in the tnsnames.ora and listener.ora files.

regards,
Rajesh Kumar Govindarajan.