How to connect Oracle Database and Fault Solution - Summary Trying to recommend that I am connected to Oracle
Summary, especially suitable for program developers with oracle rookie
How to configure the client to connect to the database:
To enable a client machine to connect to the Oracle database, you need to install Oracle's client software on the client machine, and the only exception is the Java connection database, you can use JDBC Thin mode, not to load Oracle's client software. Joining your Oracle database is installed on the machine, you don't need to install the Oracle client separately on the machine, because the Oracle client is automatically installed because the Oracle database is installed.
I have used the SQL Server database and then use Oracle's novice may have such questions: ask what I don't need to install SQL Server when using SQL Server? The reason is very simple, SQL Server is also Microsoft, which integrates the SQL Server client in the operating system. If Microsoft has a protocol, the Oracle client is also integrated into the operating system, then we don't have to put it in the client machine. Oracle clients can access the database, but this seems to be impossible.
Some people will ask: Why don't you listen to port in SQL Server, and configure the listening port in Oracle? In fact, SQL Server also has a listening port, but Microsoft fixed the listening port to 1433, not allowing you to change casually, so that you have no listening port in SQL Server, cough, microsoft put too much things package In the black box, it also brings to side effects while it is convenient. And the listening port in Oracle is directly changed in the configuration file, but in any case, it is consistent with the listening port set with the Oracle server.
Ok, missing is correct, how can we make the client machine to connect to an Oracle database?
A. Install the relevant software
B. Appropriate configuration
A. Install the appropriate software in the appropriate location:
In the client machine:
1. Install Oracle's Oracle Net Communication software on the client machine, which is included in the client software of Oracle.
2. Correctly configured SQLNET.ORA files:
Names.directory_path = (tnsnames, ....)
Names.default_domain = db_domain
In general, we don't have to use names.default_domain parameters. If you want to use the parameter with # 注 注 or to remove the parameter, you can use the default value for the Names.Directory_path parameter, and for the names.default_domain parameter, you can sometimes need to be annotated, which is explained in detail below.
3. Correctly configured TNSName.ora file
On the server side machine:
1. Guaranteed Listener has started
2. Guaranteed that the database has been started.
If the database is not started, use:
Oracle 9i:
DOS> SQLPLUS "/ as sysdba"
SQLPlus> Startup
Oracle 8i:
DOS> SVRMGRL
SVRMGRL> Connect Internal
SVRMGRL> Startup
Command launch database
If Listener is not started, use:
LSNRCTL START [LISTENER NAME]
Lsnrctl status [listener name] command launched Listener
B. Appropriate configuration
How to properly configure Tnsname.ora files:
The client can be configured using the Oracle Net Configuration Assistant or the Oracle Net Manager graphics configuration tool on the client machine, which actually modifies the TNSNames.ora file. So we can directly modify the TNSNames.ora files, and directly modify the TNSNames.ora file as an example:
The location of this file is: ... / network / admin / tnsnames.ora (for Windows)
... / network / admin / tnsnames.ora (for UNIX)
Here, it is assumed that the server is called TestServer, the service name is orcl.testserver.com, the listening port used is 1521, and a TEST network service name in the tnsnams.ora file (database alias) is:
Test =
(Description =
(Address_list =
(Address = (protocol =
TCP) (Host =
TestServer) (port =
1521)
)
(Connect_Data = (Service_name =
Orcl.testServer.com)
)
)
The smile here is).
The red content is to be modified according to the actual situation, and now explain the following:
Protocol: The protocol of the client and server communication, generally TCP, which is generally not changed.
Host: Database listening to the machine name or IP address of the machine, database inspection is generally on the same machine with the database, so when I say database listening, the machine is usually referred to as the machine where the database is located. Under UNIX or Windows, you can get the machine name by using the hostname command by a command prompt of the database listening to the machine, or you get the IP address via the ipconfig (for the for "or ifconfig (for UNIX) command. It should be noted that regardless of the machine name or IP address, you must use the ping command PING Tong database to listen to the machine name of the machine, otherwise you need to add the machine name of the machine in the HOSTS file. Analysis.
Port: Database listens to the port that is listening, can view the server-side listener.ora file or the command prompt of the machine where the database is listening is checked. The value of this Port is always the same as the database listening to the port that is listening.
Service_name: After logging in with the system user, the SQLPlus> show parameter service_name command is viewed.
How to connect to the database using the configuration network service name:
Use the SQLPLUS program to test through the Test network service name, such as SQLPlus System / Manager @ Test. If you cannot connect to the database, add the DB_DOMAIN parameter value of the Oracle database after the Test Network Service Name (Net Service) in the TNSName.ora file, view it with the SQLPLUS> Show Parameter DB_DOMAIN command. Here the db_domain parameter value is TestServer.com, add it to the network service name, the content of the network service name is:
Test.testServer.com = (Description =
(Address_list =
(Address = (protocol = tcp) (Host = TestServer) (port = 1521)))
)
(Connect_Data = (Service_name = Orcl.TestServer.com)
)
)
The smile here is).
Use the SQLPLUS program to test through Test.TestServer.com network service name, such as SQLPlus System/Manager@test.testServer.com.
With regard to the network service name, add the DB_DOMAIN parameters, you need to understand the principle of the SQL * Plus connection database, and I have given a detailed description in the 12154 common failure.
If the above tricks don't work, I have to use the Qiankun big.
Send the client's network service name
Test.testServer.com =
(Description =
(Address_List = (Address = (protocol = TCP) (host = testserver) (port = 1521))
)
(Connect_Data = (Service_name = Orcl.TestServer.com)
)
)
The smile here is).
Copy to the server's TNSNames.ora file. Then connect to the database with SQLPlus System/Manager@test.testServer.com.
If you can connect success, you have a problem with your client and server network.
If the connection is unsuccessful, check if the part of the network service name is correct, if it is confident that the network service name is correct and all clients can connect the database, it may be a problem with the system TCP / IP or ORACLE system, it is recommended to re- Install the database.
Common fault solution:
TNS-12154 (ORA-12154): TNS: COULD NOT RESOLVE service NAME
This error represents the network service name for the connection does not exist in the TNSNames.ora file. If the network service name in TNSNames.ora is only TEST, if the user is connected to SQLPlus System / Manager @ Test1 when the user is connected, it will give it. TNS-12154 error.
It should be noted that sometimes there is a corresponding network service name even in the TNSNames.ora file, it will be wrong when using the network service name, and the typical configuration of this situation is as follows (on the client's machine):
SQLNET.ORA file:
Names.directory_path = (tnsnames, ....)
Names.default_domain = server.com
TNSNAMES.ORA file:
Test =
(Description =
(Address_List = (Address = (protocol = TCP) (host = testserver) (port = 1521))
)
(Connect_Data = (Service_name = Orcl.TestServer.com)
)
)
The smile here is).
SQL * PLUS Basic Mechanism:
After the user enters SQLPLUS System / Manager @ Test, the SQLPLUS program will automatically find the names.default_domain parameter in the SQLNET.ORA file. If this parameter exists, the value in this parameter is taken out, and it is added to the back of the network service name, ie In this case, your input is automatically changed by Sqlplus System / Manager @ Test to SQLPlus System/Manager@test.server.com, and then find the Test.Server.com network service name in the TNSNames.ora file, which is of course not found. , Because there is only a Test network service name in this file, so an error is reported. The solution is to comment out the names.default_domain parameters 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 to the TNSNames.ora file, then remove the Host, Port, TCP, Service_name, and use this information to send the connection request to the correct database server. . In addition, in principle, the configuration in TNSNames.ora is not case sensitive, but I have encountered case sensitive situations, so it is best to use the network service to be exactly the same as TNSNames.ora.
ORA-12514: TNS: Listener Could Not Resolve Service_name Given In Connect Descriptor.
This error represents the network service name can be found in TNSNames.ora, but the service_name specified in TNSNames.ora is inconsistent with the server_name of the server side. The solution is to modify the service_name in TNSNames.ora.
Easy to confuse terms:
DB_NAME: The unique identifier of a database (Oracle Database), which is the Oracle Database, which is said in the first chapter. This representation is sufficient for a single database, but with the popularity of a distributed database composed of a plurality of databases, this command database has a certain burden on the management of the database, because the names of each database may be the same, resulting in management The confusion. In order to solve this, the DB_DOMAIN parameters are introduced so that the identity of the database is determined by two parameters of db_name and db_domain, avoiding the confusion of management because of the database reign. This is similar to the management of machine names on the interconnection. We connect DB_NAME and DB_DOMAIN to two parameters to connect, represent a database, and call the name of the database to global_name, which extends DB_NAME. The DB_NAME parameter can only be composed of letters, numbers, '_', '#', '$', and up to 8 characters.
DB_DOMAIN: Defines the domain where a database is located. The domain is named with the 'domain' of the Internet, but the database administrator is determined according to the actual situation in order to better manage distributed databases. Of course, it is convenient to manage, it can be equal to the field of the Internet.
Global_name: Unique identifier for a database (Oracle Database), Oracle recommends this method to command the database. This value is that the database is determined, the default value is db_name. Db_domain. In the future, any modification of DB_NAME and DB_DOMAIN parameters will not affect the value of global_name. If you want to modify global_name, you can only modify with the alter Database Rename Global_name to
NET Service Name: Network service name, can also be called Database Alias. It is desired when the client program accesses the database, shields how the client connects to the server-side details, realizes the characteristics of the location transparent database.
Source address:
http://www.cnoug.org/viewthread.php?tid=15661&highlight=
