Oracle network configuration and connection management

Oracle network configuration and connection management oracle network configuration, for the use of PL/SQL Developer to connect to Oracle errors, especially when the network environment changes caused by connection errors. The listener. ora, sqlnet. ora, and tnsnames. ora configuration files are all stored in the $ ORACLE_HOME \ network \ admin directory. 1. sqlnet. ora -- uses a file similar to linux or other unix nsswitch. conf. This file determines how to find a connection string that appears in the connection. For example, if we enter sqlplus sys/oracle @ orcl on the client, assume that my sqlnet. ora is like SQLNET. AUTHENTICATION_SERVICES = (ETS) NAMES. DIRECTORY_PATH = (TNSNAMES, HOSTNAME), the client will first in tnsnames. orcl records in the ora file. if there is no corresponding record, try to use orcl as a host name, resolve its ip address through the network, and then connect to the instance GLOBAL_DBNAME = orcl on this ip address, of course, here, orcl is not a host name. If I look like this, www.2cto.com NAMES. DIRECTORY_PATH = (TNSNAMES) then the client will only start from tnsnames. ora searches for orcl records. There are other options in brackets, such as LDAP, which are not commonly used.
2. tnsnames. ora -- this file is similar to a unix hosts file. The tnsname provided corresponds to the host name or ip address, only when sqlnet. NAMES. DIRECTORY_PATH = (TNSNAMES), that is, the client will try to use this file only when TNSNAMES is in the order in which the client parses the connection string. PROTOCOL: the PROTOCOL for communications between the client and the server, which is generally TCP. HOST: The name or IP address of the machine on which the database listens. The database listens on the same machine as the database, so when I say that the machine where the database listens is generally the machine where the database is located. In UNIX or WINDOWS, you can use the hostname command at the command prompt of the machine on which the database listens, or use ipconfig (forWINDOWS) or ifconfig (for UNIX) command to obtain the IP address. Note that, whether using the machine name or IP address, you must ping the Host Name of the machine where the database is listening on the client, otherwise, the host name of the machine where the database listening is located must be added to the hosts file. PORT: the PORT on which the database listens. You can view the listener. ora file on the server or run the lnsrctl status [listener name] command at the command prompt on the machine where the database listens. The Port value must be the same as the Port on which the database listens.
SERVICE_NAME: on the server side, run the sqlplus> showparameter service_name command after logging on to the system. The local machine corresponding to ORCL, another IP address corresponding to SALES, it also defines whether to use the master server or the Shared Server Mode for connection # Enter TNSNAMEORCL = (DESCRIPTION = (ADDRESS_LIST = # below is the host corresponding to this TNSNAME when you want to connect, PORT, PROTOCOL (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521, if not, it is automatically adjusted according to the SERVER mode (SERVER = DEDICATED) # corresponding to service_name, SQLPLUS>; showparameter service_name; for viewing (SERVICE_NAME = orcl) www.2cto.com )) # The following is similar to SALES = (DESCRIPTION = (ADDRESS_LI ST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.188.219) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sales) Note: if the database server uses MTS, it is best to specify the dedicated direct connection mode when the client program needs to use database link. Otherwise, there will be many oraclebugs related to the distributed environment. Generally, direct connection to the database server is better, unless your real-time database connection is close to 1000.
3. The listener. ora -- listener process configuration file will not be said about the listener process. It accepts remote database access requests and transfers them to the server process of oracle. Therefore, if you do not use a remote connection, the listener process is not required. Similarly, disabling the listener process does not affect the existing database connection. Listener. ora file example # listener. ora Network Configuration File: # E: \ oracle \ product \ 10.1.0 \ Db_2 \ NETWORK \ ADMIN \ listener. ora # Generated by Oracle configuration tools. # The following defines the instance for which the LISTENER process provides services. ORCL is used here, and the corresponding ORACLE_HOME and GLOBAL_DBNAME, GLOBAL_DBNAME is not required unless # Use HOSTNAME for database connection SID_LIST_LISTENER = (SID_LIST = www.2cto.com (SID_DESC = (GLOBAL_DBNAME = boway) (ORACLE_HOME = E: \ oracle \ product \ 10.1.0 \ Db_2) (SID_NAME = ORCL )))
# Listener name. A database can have more than one listener. # The Listener protocol, ip address, and port are listened to below. The tcp1521 port is used here, in addition, use the HOST name LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = boway) (PORT = 1521 ))) the above example is the simplest, but also the most common. A listener process provides services for an instance (SID. The Operation Command $ ORACLE_HOME/bin/lsnrctl start of the listener, and other operations such as stop and status. After you have typed an lsnrctl, you can view the help information. The three files mentioned above can be configured through the graphical Configuration tool $ ORACLE_HOME/netca wizard in the form of $ ORACLE_HOME/netmgr I prefer netmgr, profile is configured with sqlnet. ora, that is, the name resolution method. The service name is configured with tnsnames. the listeners file in ora is configured with listener. ora file, that is, the listener process
For more information about the configuration, see the configuration file. In this way, the overall structure is available. When you enter sqlplussys/oracle @ orcl, 1. query sqlnet. ora looks at the name resolution method and finds that it is TNSNAME2. then it queries tnsnames. find the orcl record from the ora file, and find the host name, port, and service_name3. if there is no problem with the listener process, establish a connection with the listener process. 4. Depending on different server modes, such as dedicated server mode or shared server mode, listener takes the next action. The default mode is dedicated server. If there is no problem, the client will connect to the server process of the database. 5. At this time, the network connection has been established, and the historical mission of the listener process has been completed. # ----- Commands used for several Connections 1. sqlplus/as sysdba this is a typical operating system authentication and does not require the listener process 2. sqlplus sys/oracle connection method can only connect to the local database, the listener process is also not required 3. sqlplus sys/oracle @ orcl requires the listener process to be available. The most common connection is through a network. The preceding connection method does not require the database to be available, but does not require the database to be available for operating system authentication, because the common user is a database authentication, therefore, the database must be in the open state. The Influence of Remote_Login_Passwordfile in init. ora on Identity Authentication
Three Optional values: www.2cto.com NONE: Default Value, indicating that the Oracle system does not use password files. Privileged users who perform identity authentication through the operating system have the permissions of SYSORA and sysoper exclusive: 1. indicates that only one database instance can use a password file. 2. grant SYSORA and SYSOPER permissions to users other than sys shared: 1. indicates that multiple database instances can use password files. 2. the SYSORA and SYSOPER permissions cannot be assigned to other users except SYS. Therefore, if you want to log on as an operating system, Remote_Login_Passwordfile should be set to NONE. When the login user is not a member of the ORA_DBA and ORA_OPER groups, to log on to the database, you must create the same user name as the current operating system user in Oracle. If the current user is a domain user, the user name is domainname \ yourname. If the user is a local computer, the user name is: computername \ yourname creation method: create "domainname \ yourname" identified externally; Operating System, modify HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOME0 and add AUTH_PREFIX_DOMAIN. The value is set to FALSE, when creating an Oracle user, you can ignore the domain name. In this way, init. A parameter in ora affects how the database matches a windows user and an Oracle user OS _authent_prefix = "". The default value is null. this parameter is unavailable before Oracle8i, and OPS $ is used as the username prefix. (the maximum length of the Oracle user name is 30 characters)
Domain Name (host name) Resolution/etc/hosts (UNIX) or windows \ hosts (WIN98) winnt \ system32 \ drivers \ etc \ hosts (WIN2000) the client needs to write the correspondence between the IP address of the database server and the host name. 127.0.0.1 localhost 192.168.0.35 oracledb 192.168.0.45 tomcat 202.84.10.193 bj_db when we configure step 1, The tnsping Database Server alias is displayed as successful, but sqlplususername/password @ servicename is unavailable, when jdbc thinlink fails, www.2cto.com must not forget to perform this step on the client, probably because the DNS server does not set the ing between the Server IP address and the host name. If there are both private IP addresses and public IP addresses on the Internet, private IP addresses are written first, and public IP addresses are written later. It is recommended to keep a backup before editing. When adding a line, you should also copy and paste it to avoid the error of editing the hosts space-time grid or tab character. For ORACLE multi-database environments in UNIX, the OS client needs to configure the following two environment variables ORACLE_SID = appdb; export ORACLE_SID TWO_TASK = appdb; export TWO_TASK to specify the default target database author pplsunny