How to Change DB_UNIQUE_NAME for a RAC database

this ensures that the pfile you are about to edit contains all the current settings. It is saved in a non-standard location for safety.

Remove the database, instance and service resources from CRS, this does NOT affect database itself.

Instance RACDBDEV1 is running on node rac1

Instance RACDBDEV2 is running on node rac2

oracle@rac1.localdomain(RACDBDEV1):
					srvctl config database -d RACDBDEV
					
				

2) Start the database and change the db_unique_name at database level.

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      RACDBDEV

					

show parameter db_domain

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_domain                            string      localdomain

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     FALSE

Backup Database and the the pfile

show parameter spfile;

NAME TYPE VALUE

———————————— ———– ——————————

spfile string +DATA/RACDBDEV/PARAMETERFILE/spfileRACDBDEV1.ora

create pfile=’$ORACLE_HOME/dbs/initRACDBDEV_may8.ora’ from spfile;

this ensures that the pfile you are about to edit contains all the current settings. It is saved in a non-standard location for safety.

--- alter system set db_domain=TEST.abc.com scope=spfile sid='*'; << only required when db_domain needs to be changed. SQL> alter database rename global_name to test_test.abc.com; 

3) Now add the database resource to cluster-level.

(As grid or oracle)

srvctl status database -d RACDBDEV

Instance RACDBDEV1 is not running on node bt1

Instance RACDBDEV2 is not running on node bt2

srvctl add database -d RACDBDEV -i RACDBDEV1 -o $ORACLE_HOME -n bt1

#$ srvctl add database -d myowndb -o $ORACLE_HOME -p $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora

Please note that, for 11.2, it is no longer required to modify at instance level, it should be done at database level:

5) Create database service: the following example add service with both instances as preferred 

srvctl add service -d RACDBDEV -s RACDBDEVsrv -r " RACDBDEV1, RACDBDEV2"

srvctl config database -d RACDBDEV

• Re-register amdbh12 – Administrator-managed RAC database having instances on host01, host02
  
  • Spfile: On ASM
    
  • Password file: On ASM
    
  • Database type-specific options / actions
    
    • -dbtype RAC: To specify RAC database type
      
    • Add instances separately using srvctl add instance
      

[oracle@host02 root]$ srvctl add database -d amdbh12 -oraclehome

/u01/app/oracle/product/12.1.0/dbhome_1 -dbtype RAC -spfile

+DATA/AMDBH12/PARAMETERFILE/spfile.301.928600475 -pwfile

+DATA/AMDBH12/PASSWORD/pwdamdbh12.291.928599609

[oracle@host02 root]$ srvctl add instance -i amdbh122 -n host02 -d amdbh12

– Check the database is now registered with clusterware

[root@host01 trace]# crsctl stat res -t |grep newdb

Demo – How to Rename a single / RAC Database

Method 1: Recreate control file with the new DB name, the old tried and tested method.

Method 2: Use dbnew utility called NID

Node 2: bt2.localdomain SID: RACDBDEV2 DB_NAME: RACDBDEV

                         New DB_NAME: BTRAC

How to find out if you’re accessing a RAC database?

You can tell if it is a cluster database by looking to see if the cluster database parameter is set:

1)
Query
v$parameter to confirm that it’s a cluster database::

select name, value from v$parameter where name=’cluster_database’; 

NAME                  VALUE 
——————— ——————— 
cluster_database      TRUE 

If the cluster_database is set to “TRUE”, then the instance is RAC enabled.

2)
Execute dbms_utility.is_cluster_database:

set serveroutput on 
 BEGIN 
  IF dbms_utility.is_cluster_database THEN 
      dbms_output.put_line(‘Running in SHARED/RAC mode.’); 
  ELSE 
      dbms_output.put_line(‘Running in EXCLUSIVE mode.’); 
  END IF; 
END; 
/ 

3)
Confirm if RAC is active

Query V$ACTIVE_INSTANCES – to tell how many instances are active:

col INST_NAME FOR a30

select dbid, name from v$database;

SELECT * FROM V$ACTIVE_INSTANCES; 

INST_NUMBER INST_NAME                        CON_ID

———– —————————— ——–

         $1 bt1.localdomain:RACDBDEV1               $0

         $2 bt2.localdomain:RACDBDEV2               $0

show parameter CLUSTER_DATABASE;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

If cluster_database is set to “TRUE” instance is RAC is enabled.

Using the ps command to query for the RAC background processes: 

Here we can see whether we are running RAC –  query for the RAC background processes using the ps command:

ps -ef | grep lck

grid      4692     1  0 12:47 ?        00:00:00 asm_lck1_+ASM2

grid      4716     1  0 12:47 ?        00:00:01 asm_lck0_+ASM2

oracle   14992     1  0 14:29 ?        00:00:01 ora_lck1_RACDBDEV2

oracle   15028     1  0 14:29 ?        00:00:00 ora_lck0_RACDBDEV2

oracle   20711  4542  0 14:44 pts/0    00:00:00 grep lck

Steps to rename RAC Databases using the dbnew NID utility:

I) To change only DBID just invoke:                    

    nid target=username/pass

II) To change both DBID and DBNAME invoke:   
    nid target=username/pass DBNAME=new_database_name

III)To change only DBNAME invoke:                     
    nid target=username/pass DBNAME=new_database_name SETNAME=y

1. Backup Database and the the pfile
   

oracle@bt1.localdomain(RACDBDEV1):
sqlplus / as sysdba

create pfile=’/home/oracle/initold_RACDBDEV1.ora’ from spfile;

this ensures that the pfile you are about to edit contains all the current settings. It is saved in a non-standard location for safety.

1. Stop rac database
   

oracle@bt1.localdomain(RACDBDEV1):
srvctl status database -d RACDBDEV

Instance RACDBDEV1 is running on node bt1
Instance RACDBDEV2 is running on node bt2

srvctl stop database -d RACDBDEV

1. startup mount the database:
   

oracle@bt1.localdomain(RACDBDEV1):
sqlplus / as sysdba

startup mount

exit

1. Exit and run the “nid” utility – we only need change the db_name
   

Incase of RAC, we need to set cluster_database=FALSE to change DB name. Then stop and mount the DB again

alter system set cluster_database=FALSE scope=spfile;
shutdown immediate;

startup mount

exit

nid target=/ DBNAME=BTRAC SETNAME=y logfile=BTRAC_Instance.log

more BTRAC_Instance.log

DBNEWID: Release 12.1.0.2.0 – Production on Wed May 3 15:45:36 2017

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

Connected to database RACDBDEV (DBID=940509235)

Connected to server version 12.1.0

Control Files in database:

    +DATA/RACDBDEV/CONTROLFILE/current.281.942892467

    +FRA/RACDBDEV/CONTROLFILE/current.256.942892467

Changing database name from RACDBDEV to BTRAC

    Control File +DATA/RACDBDEV/CONTROLFILE/current.281.942892467 – modified

    Control File +FRA/RACDBDEV/CONTROLFILE/current.256.942892467 – modified

    Datafile +DATA/RACDBDEV/DATAFILE/system.278.94289238 – wrote new name

    Datafile +DATA/RACDBDEV/DATAFILE/sysaux.277.94289233 – wrote new name

    Datafile +DATA/RACDBDEV/DATAFILE/undotbs1.280.94289242 – wrote new name

    Datafile +DATA/RACDBDEV/DATAFILE/undotbs2.285.94289261 – wrote new name

    Datafile +DATA/RACDBDEV/DATAFILE/users.279.94289242 – wrote new name

    Datafile +DATA/RACDBDEV/TEMPFILE/temp.284.94289247 – wrote new name

    Control File +DATA/RACDBDEV/CONTROLFILE/current.281.942892467 – wrote new name

    Control File +FRA/RACDBDEV/CONTROLFILE/current.256.942892467 – wrote new name

    Instance shut down

Database name changed to BTRAC.

Modify parameter file and generate a new password file before restarting.

Succesfully changed database name.

DBNEWID – Completed succesfully.

The instance will also have been shut down at the end of the nid command.

1. Copy the old initRACDBDEV1.ora as initnew1.ora, update the DB_NAME, instance_name parameter to reflect the new name.Then startup mount
   

Copy  and edit the newly created /home/oracle/initnew_sid.ora to $ORACLE_HOME/dbs/initnew_sid.ora then edit the new file and change the db_name parameter.

cp /home/oracle/initold_RACDBDEV1.ora /home/oracle/initRACDBDEV1.ora

sqlplus / as sysdba

startup mount;

ORACLE instance started.

Total System Global Area   ######## bytes

Fixed Size                 ######## bytes

Variable Size              ######## bytes

Database Buffers           ######## bytes

Redo Buffers               ######## bytes

ORA-01103: database name ‘BTRAC’ in control file is not ‘RACDBDEV’

SYS @ RACDBDEV1 03:57:28> show parameter spfile

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      +DATA/RACDBDEV/PARAMETERFILE/spfi

                                                 le.288.942892669

create pfile=’/home/oracle/initRACDBDEV1.ora’ from spfile;

exit

Edit the pfile:

vi /home/oracle/initrac1.ora

   Change 

*.db_name=’BTRAC’

*.cluster_database=true

sqlplus / as sysdba

shutdown immediate

startup mount pfile=’/home/oracle/initRACDBDEV1.ora’

1. Resetlog open the database.
   

ALTER DATABASE OPEN;
 ??? RESETLOGS;

select open_mode from v$database;

OPEN_MODE
——————– 
READ WRITE

1. Create spfile
   

create spfile=’+DATA/RACDBDEV/PARAMETERFILE/spfileRACDBDEV1.ora’ from pfile=’/home/oracle/initRACDBDEV1.ora’;

— alter system set cluster_database=TRUE scope=spfile;

exit

NOTE: Step 8 for RAC only. For single node DB, steps are over.

1. Add DB to the cluster
   

oracle@bt1.localdomain(RACDBDEV1): 

srvctl add database -d BTRAC
-o $ORACLE_HOME -r primary -s OPEN -p +DATA/RACDBDEV/PARAMETERFILE/spfileRACDBDEV1.ora

srvctl add instance -d BTRAC -i RACDBDEV1 -n bt1

srvctl add instance -d BTRAC -i RACDBDEV2 -n bt2

srvctl config database -d BTRAC -a

srvctl add database -d NEW -o $ORACLE_HOME -r primary -s OPEN -p +DATA/RACDBDEV/PARAMETERFILE/spfileRACDBDEV1.ora

srvctl add instance -d NEW -i NEW1 -n bt1
srvctl add instance -d NEW -i NEW2 -n bt2
srvctl config database -d NEW -a

Database unique name:NEW

Database name:
Oracle home: /home/oracle/product/11.2
Oracle user: oracle
Spfile: +DG_SYS01/NEW/spfileNEW.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: NEW
Database instances: NEW1,NEW2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is enabled
Database is administrator managed