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.
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.
SQL> startup
ReplyDeleteORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_UPATCHDB'
Can you please resolve issue?
https://stackoverflow.com/questions/39683556/ora-00119-invalid-specification-for-system-parameter-local-listener
DeleteNice Article..
ReplyDeletethanks for the tip
ReplyDeletevery useful!
ReplyDelete