Oracle client server that little thing

Oracle Network Configuration
Three configuration Files Listener.ora, Sqlnet.ora, Tnsnames.ora, are placed in the $oracle_home\network\admin directory.

1. Sqlnet.ora-----function similar to Linux or other Unix nsswitch.conf files, this file to determine how to find a connection in the connection string.
For example, our client input
Sqlplus Sys/[email protected]
If my sqlnet.ora is like this,
Sqlnet. Authentication_services= (NTS)
NAMES. Directory_path= (Tnsnames,hostname)
Then, the client will first find the ORCL record in the Tnsnames.ora file. If there is no corresponding record, try to use ORCL as a hostname, through the network to resolve its IP address and then to connect the IP GLOBAL_DBNAME=ORCL this instance, Of course, I'm orcl here. Not a host name
If I were this way
NAMES. Directory_path= (TNSNames)
Then the client will only look for ORCL records from Tnsnames.ora, and there are other options in parentheses, such as LDAP, which are not commonly used.
2. Tnsnames.ora------This file is similar to the UNIX hosts file, providing tnsname to host name or IP correspondence, only if Sqlnet.ora is similar
NAMES. Directory_path= (TNSNames) In this case, that is, the client parses the connection string in the order of TNSNames is, will try to use this file.
PROTOCOL: The client and server-side communication protocol, generally TCP, the content is generally not changed.
HOST: The machine name or IP address of the machine on which the database is listening, the database is listening generally to the same machine as the database, so when I say that the database is listening to the machine, it is also the machine where the database is located. Under UNIX or WINDOWS, you can get the machine name by using the hostname command at the command prompt on the machine where the database is listening, or get the IP address through the ipconfig (for WINDOWS) or ifconfig (for UNIX) command. Note that, regardless of the machine name or IP address, the client must ping the database to listen to the machine name on the machine, otherwise you need to add the machine name of the machine on which the database listens in the Hosts file.
Port: The database listens on the port being listened to, can look at the server side of the Listener.ora file or at the command prompt of the machine where the database is listening through the LNSRCTL status [listener name] command. The value of the port here must be the same as the database listening on the listening port.
SERVICE_NAME: On the server side,,sqlplus> show parameter service_name command after logging in with system user.
ORCL corresponding to the local, sales corresponding to another IP address, which also defines the use of the primary server or shared server mode to connect

#你所要连接的时候输入得TNSNAME
ORCL =
(DESCRIPTION =
(Address_list =
#下面是这个TNSNAME对应的主机, ports, protocols
(ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
)
(Connect_data =
#使用专用服务器模式去连接需要跟服务器的模式匹配, if not, automatically adjusts according to the server's mode
(SERVER = dedicated)
#对应service_name, sqlplus>;show parameter service_name; To view
(service_name = ORCL)
)
)
#下面这个类似
SALES =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.188.219) (PORT = 1521))
)
(Connect_data =
(SERVER = dedicated)
(service_name = sales)
)
)
Note: If the database server is using MTS, the client program needs to explicitly indicate that the client is using the dedicated direct connection, otherwise it will encounter many Oracle bugs related to the distributed environment. In general, the database server will be better off with a direct connection, unless your real-time database connections are close to 1000.

3. Listener.ora------The configuration file for the listener listener process
As for the listener process, it is not much to say, accept remote access to the database and transfer the server process to Oracle. So if you are not using a remote connection, the listener process is not required, as is the case if shutting down the listener process does not affect the database connection that already exists.
Examples of Listener.ora files
#listener. Ora Network Configuration File: #E: \oracle\product\10.1.0\db_2\network\admin\listener.ora
# Generated by Oracle configuration tools.
#下面定义LISTENER进程为哪个实例提供服务 here is ORCL, and it corresponds to Oracle_home and global_dbname where global_dbname is not required unless
#使用HOSTNAME做数据库连接

Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = Boway)
(Oracle_home = E:\oracle\product\10.1.0\Db_2)
(Sid_name = ORCL)
)
)
#监听器的名字, a single database can have more than one listener
#再向下面是监听器监听的协议, IP, port, etc., use the tcp1521 port here, and make # with the host name
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = Boway) (PORT = 1521))
)
The above example is one of the simplest examples, but it is also the most common. A listener process provides services for a instance (SID).
Action commands for listeners
$ORACLE _home/bin/lsnrctl Start, others such as Stop,status. After a specific knock on a lsnrctl see Help.
The three files mentioned above can be configured with a graphical configuration tool.
$ORACLE _HOME/NETCA Wizard-style
$ORACLE _home/netmgr
I am more accustomed to netmgr,
Profile configuration is the way Sqlnet.ora is called name resolution
Service name is configured with the Tnsnames.ora file
Listeners is configured with the Listener.ora file, which is the listener process
Specific configuration you can try it and then look at the configuration file.
As a result, the overall structure is
When you enter Sqlplus Sys/[email protected]
1. Query Sqlnet.ora look at the name of the parsing method, found that is Tnsname
2. Then query the Tnsnames.ora file, find the ORCL record from the inside, and find the hostname, port and Service_Name
3. If there is no problem with the listener process, establish a connection to the listener process.
4. Depending on the server mode, such as dedicated server mode or shared server mode, listener takes the next action. The default is the private server mode, and the client connects to the database's server process if there is no problem.
5. Now that the network connection has been established, the historical mission of the listener process has been completed.
#---------------
Forms of command used in several connections
1.sqlplus/as SYSDBA This is a typical operating system certification and does not require a listener process
2.sqlplus Sys/oracle This connection method can only connect to the native database, also does not need to listener process
3.sqlplus Sys/[email Protected] This approach requires the listener process to be in a usable state. Most commonly connected over the Internet.
The above connection mode uses the SYS user or other user authentication through the password file does not need the database to be available, the operating system authentication also does not need the database to be available, the ordinary user because is the database authentication, therefore the database must be in the open state.
The impact of Remote_login_passwordfile in Init.ora on authentication
Three optional values:
NONE: The default value indicates that the Oracle system does not use a password file, and privileged users who authenticate through the operating system have Sysora and Sysoper permissions
EXCLUSIVE:
1. Indicates that only one DB instance can use a password file
2. Allow Sysora and Sysoper permissions to be assigned to users other than Sys
SHARED:
1. Indicates that multiple DB instances can use a password file
2. Sysora and Sysoper permissions are not allowed to be assigned to users other than Sys
Therefore, if you want to log on as the operating system, Remote_login_passwordfile should be set to none
When the logged-on user is not a member of the ORA_DBA Group and the Ora_oper group, the logon database needs to create the same user name as the current operating system user in Oracle, if the current user is a domain user, the name is: Domainname\yourname, if it is a local computer user, The name is: Computername\yourname
To create a method:
Create "domainname\yourname" identified externally;
Operating system, modify HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 add Auth_prefix_domain below, the value is set to False, the domain name can be ignored when creating ORACLE user
In this way, there is a parameter in Init.ora that will affect how the database matches a Windows user and Oracle User Os_authent_prefix = ""
The default is null, oracle8i previously, without this parameter, and ops$ as the user name prefix. (Oracle user name maximum length is limited to 30 characters)

About domain name (host name) resolution
/etc/hosts (UNIX)
or windows\hosts (WIN98) winnt\system32\drivers\etc\hosts (WIN2000)
The client needs to write a correspondence between the database server IP address and the host name.

127.0.0.1 localhost
192.168.0.35 OracleDB OracleDB
192.168.0.45 Tomcat Tomcat
202.84.10.193 bj_db bj_db

Sometimes after we have configured the first step, the Tnsping database server alias display is successful,
But Sqlplus Username/[email protected], JDBC thin link also does not pass the time,
Do not forget to do this step on the client, the reason may be that the DNS server does not set the server IP address and host name of the corresponding relationship.
If there is both a private IP and a public IP on the Internet, the private IP is written in front and the public IP is written back.
It is best to leave a backup before editing, and it is best to use copy and paste to avoid editing the hosts space-time lattice or tab character errors.

For Oracle Multi-database environments under UNIX, OS clients need 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

#-------------
It might be used in a wrong line.
1. Lsnrctl Status View the state of the server-side listener process
Lsnrctl>help
The following operations is available
An asterisk (*) denotes a modifier or extended command:
Start Stop status
Services version Reload
Save_config Trace Change_password
Quit Exit set*
show*
Lsnrctl>;status
: Em11:
2. Tnsping View the configuration of client Sqlnet.ora and Tnsname.ora files correctly, and the status of the corresponding server's listener process.
C:\>tnsping ORCL
TNS Ping Utility for 32-bit windows:version 10.1.0.2.0-production on 1 June-August-
2005 09:36:08
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
E:\oracle\product\10.1.0\Db_2\network\admin\sqlnet.ora
Used TNSNames Adapter to resolve the alias
Attempting to contact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP)
(HOST = 127.0.0.1) (PORT = 1521))) (Connect_data = (SERVER = dedicated) (Service_
NAME = ORCL)))
OK (msec)
3.
Sql>show SGA To see if instance has been started
Sql>;select Open_mode from V$database; View whether the database is open or mount.
Open_mode
----------
READ WRITE

Basic mechanism of sql*plus operation:
After the user enters Sqlplus System/[email protected], the Sqlplus program will automatically find the Sqlnet.ora parameter in the Names.default_domain file, if the parameter exists, the value in the parameter is removed, Appended to the Network service name, in this case your input is automatically changed from Sqlplus System/[email protected] to sqlplus System/[email protected], Then to the Tnsnames.ora file to find the Test.server.com Network service name, which of course can not find, because the file only test network service name, so error. The workaround is to comment out the Names.default_domain parameter in the Sqlnet.ora file, such as #NAMES. Default_domain = server.com. If the Names.default_domain parameter does not exist, the Sqlplus program will find the test network service name directly into the Tnsnames.ora file, and then remove the host,port,tcp,service_name from it, Use this information to send connection requests to the correct database server.
In addition, in principle, the configuration in Tnsnames.ora is not case-sensitive, but I do encounter case sensitivity, so it is best to use a network service exactly as configured in Tnsnames.ora.
Ora-12514:tns:listener could not resolve service_name given in Connect descriptor.
This error indicates that the network service name can be found in Tnsnames.ora, but the service_name specified in Tnsnames.ora is inconsistent with the server-side service_name. The solution is to modify the service_name in the Tnsnames.ora.
Easy to confuse terminology introduction:
Db_name: A unique identifier for a database (Oracle DB) that is Oracle database. This representation is sufficient for a single database, but with the popularization of a distributed database consisting of multiple databases, this method of command-database poses a burden on the management of the database, because the names of each database may be the same, resulting in administrative confusion. In order to solve this situation, the Db_domain parameter is introduced, so that the identity of the database is determined by the combination of the db_name and db_domain two parameters, which avoids the management confusion because of the database duplicate name. This is similar to the management of the machine name on the interconnection network. We will db_name and db_domain two parameters with '. ' Connected, represents a database, and the name of the database is called Global_name, that is, it extends the db_name. The db_name parameter can only consist of letters, numbers, ' _ ', ' # ', ' $ ', and a maximum of 8 characters.
Db_domain: Defines the domain in which a database resides, and the name of the domain is not related to the ' domain ' of the Internet, but is determined by the database administrator in order to better manage the distributed database. Of course, for ease of management, it can be equal to the Internet domain.
Global_name: A unique identifier for a database (Oracle DB), which Oracle recommends to command the database in this manner. The value is determined at the time the database was created, and the default value is Db_name. Db_domain. Any subsequent modification of the db_name and Db_domain parameters in the parameter file does not affect the value of global_name, and if the global_name is to be modified, it can only be modified with the alter DATABASE RENAME global_name to command. then modify the corresponding parameters.
SERVICE_NAME: This parameter is newly introduced by Oracle8i. Before 8i, we used SIDS to represent an instance of the identity database, but in Oracle's parallel environment, a database corresponds to multiple instances, which requires multiple network service names and is cumbersome to set up. In order to facilitate the setting in the parallel environment, the SERVICE_NAME parameter is introduced, which corresponds to a database, not an instance, and there are many other benefits to this parameter. The default value for this parameter is db_name. Db_domain is equal to Global_name. A database can correspond to multiple service_name for a more flexible configuration. This parameter is not directly related to the SID, which means that the service name must be the same as the SID.
NET Service Name: The network services name, which can also be called a database alias. is the client program to access the database needs, shielding the client how to connect to the server-side details, the implementation of the database location transparent characteristics.
How to connect to the database with the configured network service name:
Test with the Sqlplus program using the test network service name, such as Sqlplus System/[email protected]. If you cannot connect to the database, add the Db_domain parameter value of the Oracle database to the test Network service name (NET service) in the Tnsname.ora file, by using sqlplus> show parameter db_ Domain command. Here the Db_domain parameter value is testserver.com, which is appended to the network service name

Http://linux.chinaunix.net/techdoc/database/2008/06/20/1012282.shtml

Http://www.linuxidc.com/Linux/2011-09/44123.htm

Http://www.jb51.net/article/44668.htm

Oracle client server that little thing