To play the Oracle server connection

1 Two types of connectivity for Oracle servers

Oracle database connections are available in a number of ways, depending on whether the client and server are running on the same machine in two broad categories, one for local connections and two for network connectivity.

• Local connections. As the name implies, the client program and the server program are running on the same machine. When you install Oracle, the client program Sqlplus is automatically installed on the server machine, so this is always available.
• Remote connection. Client programs and server-side programs run on different machines.

In addition, Oracle has a unique connection aspect, that is, when an Oracle instance is not yet running, it can still connect through the client. The reason for this is that the server side is not listening to the Oracle instance itself, but another independent listener program, Tnslsnr.

2 Local Connection

A local connection does not require a listener to work. A local connection can connect to an already running instance or to an empty instance (the instance is not already running). On a local connection, Sqlplus first finds the ORACLE executable based on $oracle_home, then starts a dedicated server process and runs the program. Then, based on the two environment variables $oracle_home and $ORACLE_SID to determine which instance to connect to, you must set the number before connecting them.

2.1 Connecting to an already running instance

Currently an instance of Sid ORCL12C is running, and Sqlplus is running Oracle-installed user Oracle, which belongs to the OSDBA group, so Oracle does not need to verify the user name password. the "as SYSDBA" forces the user to log in as sys.

[Email protected] ~]$ env | grep oracleoracle_sid=orcl12coracle_home=/opt/app/oracle/product/12.1.0/dbhome_1[[email protected] ~]$ Sqlplus/as Sysdba Sql*plus:release 11.2.0.3.0 Production on Fri Dec 5 01:55:33 2014Copyright (c) 1982, (+), Oracle.  All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit productionwith The partitioning, OLAP, Adva nced Analytics and Real application testing optionssql>

When connected as SYSDBA, Oracle simply ignores the user name and password provided on the command line and logs in directly to the SYS account. And this is the Oracle operating system account execution Sqlplus, resulting in no need to verify the password, so you can log in normally. As shown below:

[Email protected] ~]$ sqlplus Randomusername/randompassword as SYSDBA; Sql*plus:release 11.2.0.3.0 Production on Fri Dec 5 02:28:03 2014Copyright (c) 1982, (+), Oracle.  All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit productionwith The partitioning, OLAP, Adva nced Analytics and Real application testing optionssql>

If you are using an as SYSDBA and not an Oracle operating system account running Sqlplus, you must provide the correct SYS account password, otherwise you will not be logged on.

[Email protected] ~]# sqlplus Sys/wrongpassword as  sysdba; Sql*plus:release 11.2.0.3.0 Production on Fri Dec 5 03:49:36 2014Copyright (c) 1982, (+), Oracle.  All rights reserved. Error:ora-01017:invalid Username/password; Logon Deniedenter User-name:

The following is a connection that does not use the as SYSDBA. Use the system user connection.

[Email protected] ~]$ sqlplus System/systempassword; Sql*plus:release 11.2.0.3.0 Production on Fri Dec 5 02:22:48 2014Copyright (c) 1982, (+), Oracle.  All rights reserved. Connected to:oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit productionwith The partitioning, OLAP, Adva nced Analytics and Real application testing optionssql>

2.2 Connection when the instance is not started

This is a very special situation, whether it is the Tnslsnr listener or the instance is not started, that is, Oracle-related software is not running at all. The task of establishing a server connection falls entirely on sqlplus. In fact, if the instance is not running with Sqlplus local connection, Sqlplus still starts a dedicated server process, but the process cannot communicate with the instance (the instance is not running yet).

[Email protected] ~]$ Sqlplus/as sysdba; Sql*plus:release 11.2.0.3.0 Production on Fri Dec 5 03:59:24 2014Copyright (c) 1982, (+), Oracle.  All rights reserved. Connected to an idle instance. Sql>

If you do not run as an Oracle user at this time, you must provide the correct user name and password, the problem is that the DB instance has not been started and of course it cannot validate the user password itself. The workaround is that the server process validates the user against the password file. The default installed password file is located in the $oracle_home/dbs/directory, only the SYS user, so only sys can log in.

[Email protected] ~]# sqlplus Sys/syspassword as SYSDBA; Sql*plus:release 11.2.0.3.0 Production on Fri Dec 5 04:19:04 2014Copyright (c) 1982, (+), Oracle.  All rights reserved. Connected to an idle instance. Sql>

3 Remote connections

The biggest difference between a remote connection and a local connection is that the Tnslsnr listener program must be started, and the dedicated server process at this point has the listener responsible for generating it. Another difference is how to find the specified instance, where the local connection determines the instance based on $oracle_sid, while the remote connection is the service name advertised by the listener (Tnslsnr internally is responsible for the service name and the instance SID). Additionally, because it is a remote connection, there is no authentication for the operating system installation user, and all logins must verify the user name and password.

Here are a few examples.

3.1 When the instance is started, for obvious reasons, run Sqlplus from a Windows machine this time.

C:\>sqlplus system/[email protected]/orcl12c.xy.comsql*plus:release 12.1.0.2.0 Production on Friday December 5 13:51:39 2014C Opyright (c) 1982, Oracle.  All rights reserved. Last successful logon time: Friday December 05 2014 03:37:41 +08:00 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.2 Instance is not yet started

When the instance has not started, the password authentication is still through the password file, which is the same as the local connection.

C:\>sqlplus Sys/[email protected]/orcl12c.xy.com as SYSDBA; Sql*plus:release 12.1.0.2.0 Production on Friday December 5 13:57:16 2014Copyright (c) 1982,, Oracle.  All rights reserved. Error:ora-12514:tns: The listener currently does not recognize the service requested by the connection descriptor, enter the user name:

This login failed because the Tnslsnr listener did not publicly orcl12c.xy.com the service. This is because the service is automatically registered with the listener after the instance is started, and now the instance is not started, and of course there is no registration for this service. The solution is to pre-static registration, the specific method is to modify the $oracle_home/network/admin/listener.ora this file, add static service items, as follows:

# Listener.ora Network Configuration File:/opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools. LISTENER =  (description_list = (    DESCRIPTION = (      ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521)) 
   
     (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))))  Sid_list_listener =   (sid_list = (     Sid_desc =       ( Oracle_home =/opt/app/oracle/product/12.1.0/dbhome_1)       (sid_name = orcl12c)))    
   

As a result, the listener is started and will automatically advertise the service named ORCL12C, and correspond to the ORCL12C instance of this SID. by Lsnrctl stop; Lsnrctl start; After restarting Tnslsnr, you will see this service through the Lsnrctl status. such as:

[[email protected] ~]$ lsnrctl statuslsnrctl for linux:version 12.1.0.2.0-production on 05-DEC-2014 04:47:25Copyri  Ght (c) 1991, Oracle. All rights reserved. Connecting to (Description= (address= (protocol=tcp) (host=localhost)) STATUS of the LISTENER------------------------Alias listenerversion Tnslsnr for Linux:version 12. 1.0.2.0-productionstart Date 04-dec-2014 14:23:19uptime 0 days hr. 6 min. sectra Ce level offsecurity on:local OS authenticationsnmp offlistener Param Eter File/opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.oralistener Log File/opt/app/oracle/di  ag/tnslsnr/centos192/listener/alert/log.xmllistening Endpoints Summary ...  (Description= (address= (protocol=tcp) (Host=localhost) (port=1521))) (Description= (address= (PROTOCOL=IPC) (key=extproc1521))) Services Summary ... Service "ORCL12C" has 1 instance(s). Instance "orcl12c", status UNKNOWN, have 1 handler (s) for the This service ... [[email protected] ~]$

Although the instance of Sid ORCL12C is not started, the listener has released the services it provides. At this point, you can connect to it remotely, although it is still an empty instance.

C:\>sqlplus Sys/[email protected]/orcl12c as SYSDBA; Sql*plus:release 12.1.0.2.0 Production on Friday December 5 14:07:53 2014Copyright (c) 1982,, Oracle.  All rights reserved. Connected to the idle routine. Sql>

4 Summary

For clarity, the following table summarizes the characteristics of local and remote connections.

Connection Mode	who is responsible for generating a dedicated server process	how to determine which instance to connect to	How to verify the logged in user
Local connection	Sqlplus	$ORACLE _home and $ORACLE_SID environment variables	1 The operating system account running Sqlplus is the 2 password file 3 database itself that installs the Oracle software owner
Remote connection	Tnslsnr Listening Process	The service name provided by the client	1 password file 2 database itself

Another thing to note is the special status of SYS, the special account and as SYSDBA. as long as SYSDBA identity, must be sys This account name , in turn this is not true, because SYS can also be logged into the system as a different identity.

To play the Oracle server connection