Thursday 17 April 2014

How to change service name in oracle 11g database

Before change the service name
SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba
SQL>

TNSENTRY

bash-3.2$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /data1/ora11g/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_DBA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))


DBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dba)
    )
  )

Steps to change service name:

==>change service_name in TNS ENTRY and restart listener====> NOT WORKING.
DBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dba.domain.com)
    )
  )

SQL> alter system set service_names='dba.domain.com' scope=spfile;

System altered.

SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2138521600 bytes
Fixed Size                  2161024 bytes
Variable Size            1006634624 bytes
Database Buffers         1107296256 bytes
Redo Buffers               22429696 bytes
Database mounted.
Database opened.

Checks after service_names parameter changed to 'dba.domain.com'

SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba.domain.com
SQL>
Now connection is working fine using service name.

5 comments:

  1. SQL> startup
    ORA-00119: invalid specification for system parameter LOCAL_LISTENER
    ORA-00132: syntax error or unresolved network name 'LISTENER_UPATCHDB'

    Can you please resolve issue?

    ReplyDelete
    Replies
    1. https://stackoverflow.com/questions/39683556/ora-00119-invalid-specification-for-system-parameter-local-listener

      Delete

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

RMAN Backup failed. After Deleting Archive log files in the filesystem, RMAN backup getting failed with below error. RMAN-00571: ======...