Shulou(Shulou.com)05/31 Report--

This article shows you how to configure Oracle connections, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

1 how to connect to the database

The connection string of oracle consists of several parts. Here, take sqlplus as an example. A completed connection string follows the following format.

Sqlplus username / password @ host: Port number / SID optional as sysdba

Let's first talk about the situation where only listener does not have tns.

2 listener

To use listener connection, you need to configure complete connection information. There are two connection methods. Let's take a look at an example of listener:

(listener with sid uses netmgr to add datavase services to listener and the configuration of sid appears.)

LISTENER2 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = iZbp1d4tisi44j6vxze02fZ) (PORT = 1522)) SID_LIST_LISTENER2 = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = gdn1400) (ORACLE_HOME = / fdisk1/oracle1400/base/dbhome_1) (SID_NAME = orcl1400)) ADR_BASE_LISTENER2 = / fdisk1/oracle1400/baseLISTENER1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = iZbp1d4tisi44j6vxze02fZ) (PORT = 1521)) ) ADR_BASE_LISTENER1 = / fdisk1/oracle1400/base

There are two listener, the one with sid is called static listener, and the one without sid is called dynamic listener. There is a difference when viewing the status:

Lsnrctl status listener1...Services Summary...Service "orcl1400" has 1 instance (s). Instance "orcl1400", status READY, has 1 handler (s) for this service...Service "orcl1400XDB" has 1 instance (s). Instance "orcl1400", status READY, has 1 handler (s) for this service...The command completed successfullylsnrctl status listener2...Services Summary...Service "gdn1400" has 1 instance (s). Instance "orcl1400", status UNKNOWN, has 1 handler (s) for this service...The command completed successfully

Note that for lsnrctl, the name of service is global database name

We are looking at a connection string:

Sqlplus sys/password@iZbp1d4tisi44j6vxze02fZ:1521/orcl1400 as sysdba

The most important thing to pay attention to in this connection string is the service name, here is orcl1400.

Be careful! This service name must be provided by one of the listener, where the service name of listener2 provides gdn1400, while listener1 does not provide the service name. So how do you connect to the database? The answer is that the connection to listener1 will go to the dynamic query service name in the database (so it is called dynamic connection).

SQL > show parameter serviceNAME TYPE VALUE-- service_names string orcl1400

That's why you can connect to the database using orcl1400. Here we try using a static connection, which can also connect to the database (note the difference between port number and dynamic)

Sqlplus sys/password@iZbp1d4tisi44j6vxze02fZ:1522/gdn1400 as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:51:00 2019Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL >

3 TNS

So what is tns? Let's take a look at this connection string.

Sqlplus sys/password@iZbp1d4tisi44j6vxze02fZ:1521/orcl1400 as sysdba

There is a lot of information behind @, can you omit it? such as

Sqlplus sys/password@tns1400 as sysdba

This looks much simpler and easy to manage. TNS implements this function. Let's look at a configuration of tnsnames.ora:

NSN1522 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = iZbp1d4tisi44j6vxze02fZ) (PORT = 1522)) (CONNECT_DATA = (SERVICE_NAME = gdn1400) NSN1521 = (DESCRIPTION = (ADDRESS_LIST = (PROTOCOL = TCP) (HOST = iZbp1d4tisi44j6vxze02fZ) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl1400)

As you can see here, the leftmost NSN1522 and NSN1521 are the names of the services we can @. The internal mapping information points to listener, and the servicename here corresponds to the global database name above!

NSN1522, iZbp1d4tisi44j6vxze02fZ, 1522, gdn1400-> listener2NSN1521, iZbp1d4tisi44j6vxze02fZ, 1521, orcl1400-> listener1

Two aliases point to two different listener, connection tests:

# sqlplus sys/password@nsn1521 as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:58:51 2019Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL > # sqlplus sys/password@nsn1522 as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:58:55 2019Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL >

Note: tns relies on lsnrctl for use, which can be understood as a concept like hostname. Note that information such as tns's SERVICE_NAME must be associated with listener in order to connect!

The above is how to configure Oracle connections. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.