Oracle server connection

Oracle server connection
1 Oracle server two types of Connection Methods Oracle database connection can be divided into two categories, 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. The Listener is not required for local connection. 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 oracle. This user belongs to the OSDBA group, so oracle does not need to verify the user name and password. However, "as sysdba" is forced to log on as a SYS 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>

 

When connecting with sysdba, ORACLE simply ignores the username and password provided on the command line, but directly logs on to the system 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 is not started is actually quite special. No tnslsnr listener or instance is started, that is, the oracle-related software is not running at present. 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. 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 responsible for 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.com SQL * 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 D Real Application Testing options SQL> 3.2 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 does not currently recognize the Service requested in the connection descriptor. Enter the User name: This login failed because the tnslsnr listener has not published 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 for clarity, the following table lists the features of local and remote connections. Another note is the special account SYS and the special identity as sysdba. As long as it is an as sysdba identity, it must be the account name SYS. In turn, this is not true, because SYS can also log on to the system as another identity. Logon verification flowchart: December 31, December 6, 2014 correction: follow the instructions in the Oracle documentation to log on with an identity with administrative permissions (as sysdba, as sysoper, as syskm, as sysbackup, as sysdg ), it does not go to the database dictionary for verification. It can only be verified by external means such as password files and operating systems. The following is the authentication flowchart provided on the official website. These special management permission identities are special. When they are used for connection, session users and the current schema are specific. However, through actual experiments, I found that if the instance has been started, then remote connection, authentication is first a database dictionary rather than a password file. My simple experiment is sqlplus sysbackup/systembackuppassword@172.16.2.192/orcl12c as sysbackup; if the instance is started, the connection will fail because the sysbackup account is locked, if the instance has not been started, the connection will succeed. Therefore, the official documents may be incorrect and we hope you will continue to explore them.