Modify / Change DB_DOMAIN GLOBAL_NAME of the Database

Here We are going to change the DB_UNIQUE_NAME of the RAC database. Changing the Global Name doesn't need a database bounce but db_domain parameter needs database bounce.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check Present Values
#########################################

SQL> sho parameter uniq
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      db01
SQL> sho parameter dom
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
db01

=====================================================================================================================
 Here Domain Name is Set to Null, So Global Name just retrieves as db01
=====================================================================================================================

#########################################
# 2) check for the Database service
#########################################

PMON register the instance in the listener whenever a database comes to MOunt stage. Here we can see that LISTENER is listener to a service called "db01"

[oracle@host01 ~]$ lsnrctl  services | grep db01
Service "db01" has 1 instance(s).
  Instance "db011", status READY, has 1 handler(s) for this service...
Service "db01XDB" has 1 instance(s).
  Instance "db011", status READY, has 1 handler(s) for this service...
[oracle@host01 ~]$

=====================================================================================================================

#########################################
# 3) Modify Database Parameters
#########################################

Now we Will alter the DB_Domain Parameter in spfile as this is a STATIC parameter.

Alter system set db_domain="stepintooracledba.com" scope=spfile sid='*';

SQL> Alter system set db_domain="stepintooracledba.com" scope=spfile sid='*';
System altered.

Lets see Listener Status after Parameter change. Still it remains same.

oracle@host01 ~]$ lsnrctl services | grep db01
Service "db01" has 1 instance(s).
  Instance "db011", status READY, has 1 handler(s) for this service...
Service "db01XDB" has 1 instance(s).
  Instance "db011", status READY, has 1 handler(s) for this service...

=====================================================================================================================

#########################################
# 4) Shutdown / Startup Database
#########################################

[oracle@host01 ~]$ srvctl stop database -d db01

[oracle@host01 ~]$ srvctl start database -d db01

[oracle@host01 ~]$ lsnrctl services | grep db01
Service "db01.stepintooracledba.com" has 1 instance(s).
  Instance "db011", status READY, has 1 handler(s) for this service...
Service "db01XDB.stepintooracledba.com" has 1 instance(s).
  Instance "db011", status READY, has 1 handler(s) for this service...
[oracle@host01 ~]$ 

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
db01.stepintooracledba.com

=====================================================================================================================
 Now We can See that PMON has registered the instance service name as "db01.stepintooracledba.com"
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-12154: TNS: could not resolve the connect identifier specified

##########################
## Error
##########################

ORA-12154: TNS: could not resolve the connect  identifier specified

### Full Error

Mon Nov 04 21:01:00 2013
Error 12154 received logging on to the standby

##########################
#  Error Occurred
##########################

Error occured while Primary database is trying to connect to a standby database for transfering archive logs in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Primary trying to transfer Archive logs to Standby

##########################
## Issue Description / Troubleshooting
##########################

### 11.2.0.3.0

#########################################
# 1) Check Whether archive destination is showing any errors
#########################################

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     NONE
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  oralin_stdby                                        ERROR     ORA-12154:      oralin_stdby
                                                                                                             TNS:could not
                                                                                                             resolve the
                                                                                                             connect
                                                                                                             identifier
                                                                                                             specified

#########################################
# 2) TNS Names Used to Connect
#########################################


oralin_stdby.example.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = kstdbylorap08)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oralin_stdby.example.com)
    )
  )

#########################################
# 3) Check Service & Domain parameters
#########################################

In standby :

SQL> sho parameter servi

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      oralin_stdby
db_domain                            string

##########################
## Solution
##########################

From Troubleshooting 2) output, we can see from TNSNAMES that service name is oralin_stdby and Domain is example.com

From Troubleshooting 2) output, Service name is correct but there is no domain set at the database level.

So the problem is with the domain name. I have removed Domain (example.com) from TNSNAMES and the connection to database was successful.

Modified TNSNAMES :


oralin_stdby.example.com =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = kstdbylorap08)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oralin_stdby)
    )
  )