Thursday, January 7, 2016

How to create service name in oracle database or Add service name to single instance database


Service name can be different from database name or instance name (also know as SID). Default service name is created with same as database name. Service name is like an alias that allow us to connect the db, it can be recorded in tnsnames.ora file or may not be recorded in tnsnames.ora file, it depends what connection method we use to connect.
we can create more than one service name for single database. The way we create service name in single instance db is different from RAC. the method below shown is for single instance db.

Let me explain why did we required to do the below...  Most people may wonder why we do have more than 8 character size service name or instance name. Oracle may not recommends but does not stop from creating instance_name with more than 8 character length string. We have a db with more than 8 character length and users want to connect db with 8 character length string with no db downtime, hence we did the below.. and it works......

In real production systems, we use service names as it allows to manage resources, workloads more effectively.
*real strings are changed*

We can also use dbms_service package .

connect sys as sysdba

show parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      MYPRODUCTIONDB

show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      MYPRODUC


SQL> alter system set service_names='MYPRODUCTIONDB,MYPRODUC' scope=both;

System altered.

SQL> alter system register;

System altered.

SQL> sho parameter service_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      MYPRODUCTIONDB,MYPRODUC


SQL> !tnsping MYPRODUCTIONDB

It works as it has entry in tnsnames.ora file

SQL> !tnsping MYPRODUC

TNS-03505: Failed to resolve name

--- It did not work as we have no entry for this in our tnsnames.ora file.

We still conntect db with new service name

SQL> CONNECT system/passwordscret@myhost.com:1521/MYPRODUC
Connected.
SQL> CONNECT system/passwordscret@//myhost.com/MYPRODUC
Connected.
SQL> CONNECT system/passwordscret@//myhost.com/MYPRODUC
Connected.
SQL>

The method we used to connect db is known as EZCONNECT or JDBC connection . EZCONNECT is very easy connect naming method and does not require any edits in tnsnames.ora file.

No comments:

Post a Comment