Differences between service name, database name, and Instance name in Oracle

1. The database name is the internal identifier of the Oracle database. It should not be changed after installation. Many physical storage directories of the database use the database name.

2. instance_name, oracle_sid, and database instance name are the names used when the database interacts with the operating system.

If you have created multiple databases on one machine and want to connect to one of the databases through sqlplus, you must specify oracle_sid:

Set oracle_sid = sidname

Sqlplus/As sysdba (this method can only be used in this instance and will be connected to the corresponding instance according to oracle_sid)

Generally, the 12560 error occurs because the Instance name is incorrectly modified or the service is not started.

Instance_name is a database parameter.

3. Service name: Service name. If the database has a domain name, it is equivalent to the global dB name. If not, it is equivalent to the database name, this is because after the database is started, the database name is automatically registered to the listener to become the service name, and the database parameter service_names is also registered as the service name, so a database can have multiple service names.

When you connect to an Oracle8i, 9i, or 10g server on an Oracle8i client, the host string should use the service name.

4. net service name: network service name, also known as TNS alias and network connection string. The name configured in tnsnames. ora, as shown in dbtns in the following example.
Sqlplus sys/sys @ orcl as sysdba (this connection will connect to the local machine or remote ORACLE based on the network service name configured in tnsnames. ora, which uses Network Communication)

Let's take a look at the database parameter settings:
SQL> show parameter instance_name
Name type value
-----------------------------------------------------------
Instance_name string orcl
SQL> show parameter service_names
Name type value
-----------------------------------------------------------
Service_names string orlinoleic, orclb
SQL> show parameter db_name
Name type value
-----------------------------------------------------------
Db_name string orcl
Service_names can also be changed to multiple values through alter system set service_names = orlinoleic, orclb.

When the listener is started, the available services are statically registered according to the information configured by listener. ora. After the database instance is started (pmon ),

Service_names and db_name values are dynamically registered to listener.

For example, if the service_names value is orlinoleic, orclb, and db_name is orcl, the following configuration is available in listener. ora:

(Sid_desc =
(Global_dbname = orclst)
(ORACLE_HOME = D: \ oracle \ product \ 10.2.0 \ db_1)
(Sid_name = orcl)
)

The value of SERVICE_NAME in tnsname. ora can be orlinoleic, orclb, orcl, orclst.

You can see the information of these services through lsnrdbms status: Status ready is dynamically registered (because it is registered to listener after the instance is started, so the status is ready ), status unknown is static registration (because it is started according to the configuration when the listener is started, it is unknown whether the instance is started, so it is unknown ).

After the connection, check the SERVICE_NAME in V $ session to determine whether the service name is static registration or dynamically registered. sys $ users is static.

Here, orclst is a static registration. You can view the session as follows:

SQL> select SERVICE_NAME from V $ session where Sid = (select distinct (SID) from V $ mystat );
SERVICE_NAME
----------------------------------------------------------------
Sys $ Users

Here, we use orclsa for Dynamic Registration. You can view the session as follows:

SQL> select SERVICE_NAME from V $ session where Sid = (select distinct (SID) from V $ mystat );
SERVICE_NAME
----------------------------------------------------------------
Orcia

Services summary...
Service "orcia" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...
Service "orclb" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...
Service "plsextproc" has 1 instance (s ).
Instance "plsextproc", status unknown, has 1 handler (s) for this service...
Service "orcl" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...
Service "orclxdb" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...
Service "orcl_xpt" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...
Service "orclst" has 1 instance (s ).
Instance "orcl", status unknown, has 1 handler (s) for this service...
The command completed successfully

By default, a listener of 1521 can be registered for dynamic registration. If the listening port is not 1521, You need:

1.Tnsname. ora on the server sideConfigure a TNS to indicate the port number. Only the address configuration does not have the CONNECT_DATA configuration item:

Lclsn =
(Description =
(Address = (Protocol = TCP) (host = PC-6753184) (Port = 1522 ))
)

2. Change the value of the database parameter local_listener to the lclsn configured by tnsname. ora on the server side:

Alter system set local_listener = lclsn;

Configuration in tnsname. ora:Obtain the SERVICE_NAME that can be used through lsnrdbms status, such as orlinoleic, orclb, orcl...

Dbtns =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 172.19.11.28) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = teldb)
)
)

In the preceding example, SERVICE_NAME = teldb can also be changed to SID = orcl.

Example of a Database Cluster:

Dbcluster =
(Description_list =

(Load_balance = Off)

(Failover = on)

(Description =

(Address_list =

(Load_balance = on)

(Failover = on)

(Address = (Protocol = TCP) (host = rdl701d001-oravip.test.com) (Port = 6191 ))

(Address = (Protocol = TCP) (host = rdl701d002-oravip.test.com) (Port = 6191 ))

(Address = (Protocol = TCP) (host = rdl701d003-oravip.test.com) (Port = 6191 ))

(Address = (Protocol = TCP) (host = rdl701d004-oravip.test.com) (Port = 6191 ))

(Address = (Protocol = TCP) (host = rdl701d005-oravip.test.com) (Port = 6191 ))

(Address = (Protocol = TCP) (host = rdl701d006-oravip.test.com) (Port = 6191 ))

)

(CONNECT_DATA =

(SERVICE_NAME = opofs_rd)

(Failover_mode =

(Type = session)

(Method = Basic)

(Retries = 120)

(Delay = 5)

)

)

)

)