Go to Oracle server connection and switch to oracle Server

Go to Oracle server connection and switch to oracle Server
1. Two Connection Methods for the Oracle server

Oracle database connections can be divided into two categories based on whether the client and server are running on the same machine. One is local connection, and the other is through network connection.

• Local connection. As the name implies, the client program and the server program run on the same machine. When installing Oracle, the client program sqlplus is automatically installed on the server machine, so this method is always available.
• Remote connection. The client and server run on different machines.

In addition, Oracle has a unique feature in connection, that is, when the Oracle instance is not running, it can still be connected through the client. The reason is that the server is not responsible for listening to the Oracle instance itself, but another independent listener program tnslsnr.

2 Local Connection

The local connection does not require the listener to work. A local connection can be connected to a running instance or an empty instance (the instance is not yet running ). During local connection, sqlplus first finds the oracle executable file based on $ ORACLE_HOME, then starts a dedicated server process, and runs the program. Then, determine the instance to be connected based on the environment variables $ ORACLE_HOME and $ ORACLE_SID. Therefore, you must set the numbers before connection.

2.1 connect to a running instance

Currently, an instance with the SID of orcl12c is running. At this time, sqlplus runs in ORACLE as the installation user. This user belongs to the OSDBA group, so oracle does not need to verify the user name and password. While"As sysdba" is forced to use SYSLog on as a user.

[oracle@centos192 ~]$ env | grep ORACLEORACLE_SID=orcl12cORACLE_HOME=/opt/app/oracle/product/12.1.0/dbhome_1[oracle@centos192 ~]$ sqlplus / as sysdba;SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 01:55:33 2014Copyright (c) 1982, 2011, 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>

WhenORACLE ignores the username and password provided on the command line when connecting with sysdba.But directly log on to the SYS account. In this case, the oracle operating system account executes sqlplus, so the password does not need to be verified, so you can log on normally. As follows:

[oracle@centos192 ~]$ sqlplus randomusername/randompassword as sysdba;SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 02:28:03 2014Copyright (c) 1982, 2011, 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>

If the as sysdba is used, and sqlplus is not run by the operating system account of oracle, the correct SYS account password must be provided; otherwise, the account cannot be logged on.

[root@centos192 ~]# sqlplus sys/wrongpassword  as sysdba;SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 03:49:36 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.ERROR:ORA-01017: invalid username/password; logon deniedEnter user-name:

Do not use the as sysdba connection. Use the system user to connect.

[oracle@centos192 ~]$ sqlplus system/systempassword;SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 02:22:48 2014Copyright (c) 1982, 2011, 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>

2.2 connection when the instance has not been started

In fact, this situation is quite special. No tnslsnr listener or instance is started. That is to say, oracle-related software is not running yet. In this way, the task of establishing a server connection falls completely on sqlplus. In fact, whether the instance runs has little to do with the local connection of sqlplus. sqlplus still starts a dedicated server process first, but this process cannot communicate with the instance (the instance is not running yet ).

[oracle@centos192 ~]$ sqlplus / as sysdba;SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 03:59:24 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL>

If it is not run as an oracle user, you must provide the correct user name and password. The problem is that the database instance has not been started and the user password cannot be verified by yourself. The solution is that the server process verifies the user according to the password file. By default, the installed password file is located in the $ ORACLE_HOME/dbs/directory. Only SYS users can log on to the system.

[root@centos192 ~]# sqlplus sys/syspassword as sysdba;SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 5 04:19:04 2014Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL>

3. remote connection

The biggest difference between a remote connection and a local connection is that the listener program tnslsnr must be started, and the dedicated server process is also generated by this listener. Another difference is how to find the specified instance. The local connection determines the instance based on $ ORACLE_SID, the remote connection depends on the service name published by the listener (the Internal Service name of tnslsnr corresponds to the instance SID ). In addition, because it is a remote connection, there is no operating system installation user free of authentication, all login must verify the user name and password.

The following are several examples.

3.1 When the instance has been started, for the sake of clarity, this time from a Windows machine to run sqlplus.

C:> sqlplus system/systempassword@172.16.2.192/orcl12c. xy. comSQL * Plus: Release 12.1.0.2.0 Production on Friday December 5 13:51:39 2014 Copyright (c) 1982,201 4, Oracle. all rights reserved. last Successful Logon Time: Friday, September 11, 2014 03:37:41 + connect 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 When the instance is not started

When the instance is not started, the password verification still needs to pass the password file, which is the same as the local connection.

C: \> sqlplus sys/syspassword@172.16.2.192/orcl12c.xy.com as sysdba; SQL * Plus: Release 12.1.0.2.0 Production on Friday December 5 13:57:16 2014 Copyright (c) 1982,201 4, Oracle. all rights reserved. ERROR: ORA-12514: TNS: The Listener currently cannot identify the Service requested in the connection descriptor. Enter the User Name:

This login failed because the tnslsnr listener did not publish the orcl12c.xy.com service. This is because the service is automatically registered with the listener after the instance is started. Now the instance has not been started, and of course it has not been registered. The solution is to perform static registration in advance. Modify the file $ ORACLE_HOME/network/admin/listener. ora and 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 automatically publishes the service named orcl12c and corresponds to the orcl12c instance. Use lsnrctl stop; lsnrctl start; after the tnslsnr is restarted, the service is displayed through lsnrctl status. For example:

[oracle@centos192 ~]$ lsnrctl statusLSNRCTL for Linux: Version 12.1.0.2.0 - Production on 05-DEC-2014 04:47:25Copyright (c) 1991, 2014, Oracle.  All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))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 14 hr. 24 min. 6 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.oraListener Log File         /opt/app/oracle/diag/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, has 1 handler(s) for this service...[oracle@centos192 ~]$

Although the instance whose SID is orcl12c is not started, the listener has released the services it provides. Now you can connect to it remotely, even though it is still an empty instance.

C: \> sqlplus sys/syspassword@172.16.2.192/orcl12c as sysdba; SQL * Plus: Release 12.1.0.2.0 Production on Friday December 5 14:07:53 2014 Copyright (c) 1982,201 4, Oracle. all rights reserved. already connected to the idle routine. SQL>

4. Summary

For clarity, the following table lists the features of local and remote connections.

Connection Method	Who is responsible for generating the dedicated server process	How to determine the instance to be connected	How to verify logon users
Local Connection	Sqlplus	$ ORACLE_HOME and $ ORACLE_SID Environment Variables	1. Whether the operating system account running sqlplus is the owner of the oracle software installed 2. Password File 3. The database itself
Remote connection	Tnslsnr listening process	Service name provided by the client	1 Password File 2 database itself

Another note is the special account SYS and the special identity as sysdba.The account name SYS must be used as sysdba.In turn, this is not true, because SYS can also log on to the system as another identity.