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

This article mainly introduces "how Oracle connects to the configuration database". In the daily operation, I believe many people have doubts about how to connect to the configuration database with Oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to connect to the configuration database by Oracle". Next, please follow the editor to study!

Start with the end of the connection.

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/base

LISTENER1 =

(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 successfully

Lsnrctl 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.

Note: 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 service

NAME 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 sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:51:00 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL >

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 =

(ADDRESS = (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-> listener2

NSN1521, iZbp1d4tisi44j6vxze02fZ, 1521, orcl1400-> listener1

Two aliases point to two different listener, connection tests:

# sqlplus sys/password@nsn1521 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:58:51 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL >

# sqlplus sys/password@nsn1522 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 30 20:58:55 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL >

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!

At this point, the study on "how to connect to the configuration database by Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.