How to connect Oracle Database and Fault Solution - Summary Trend This article is a summary of I connect Oracle, which is especially suitable for program developers and Oracle rookos to make clients to database: To enable a client machine to connect to Oracle Database You need to install Oracle's client software on the client machine. 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. Perform 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 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. The TNSName.ora file is configured correctly in the server-side machine: 1. Guaranteed that 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> connection command starting the database If Listener is not started, use: lsnrctl start [listener Name] The lsnrctl status [listener name] command launches Listener B. Conduct the appropriate configuration to configure TNSName.ora file correctly: You can configure the client using the Oracle Net Configuration Assistant or Oracle Net Manager Graphics Configuration Tool, which actually modifies the TNSNames.ora file. So we can directly modify the TNSNames.ora file, and directly modify the TNSNames.ora file as an example: the location of the file is: ... / network / admin / tnsnames.ora (for windows) ... / network / admin / tnsnames.ora For UNIX) Here, assume that the server is called TestServer, the service name is orcl.testserver.com, the listening port used is 1521, then a TEST network service name in the tnsnams.ora file (database alias) is: Test = Description = (address_list = (pecol = tcp) (host = testserver))) (service_data = (service_name = orcl.testserver.com))) The smile here is). The red content is to be modified according to the actual situation. Now the following: protocol: the protocol of the client and server-side communication, which is 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 viewed by the LNSRCTL Status [Listener Name] command. 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: Test with the SQLPLUS program, 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 contents of the network service name is: test.testServer.com = (deScription = (address = (address = (Protocol = TCP) (host = testserver))))) (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. Test.TestServer.com = (deScription = (address = tcp)) (host = test =))) (address = tcp)) (host_data =)) )) 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 indicates that the network service name used to connect does not exist in the TNSNames.ora file, such as network services in TNSNames.ora above The name is only TEST, if the user uses SQLPlus System / Manager @ TEST1 when the user is connected, the TNS-12154 error will be given.
It should be noted that sometimes there is a corresponding network service name even in the TNSNames.ora file, it will also be wrong when using the network service name, and the typical configuration of this situation is as follows (on the client): SQLNET. ORA file: names.directory_path = (tnsnames, ....) Names.default_domain = server.com tnsnames.ora file: test = (description = (address = (address = (protocol = tcp) (host = TestServer) (port = 1521 )))) (Service_name = orcl.testserver.com))) The smile here is). SQL * Plus running 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, added to Behind the network service name, that is, your input is automatically changed by Sqlplus System / Manager @ Test to SQLPlus System/Manager@test.server.com, then find Test.Server.com network service in tnsnames.ora file Name, this is certainly can't be 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 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 TNSNames.ora. Easy-confused terms: DB_NAME: The unique identifier for a database (Oracle Database), the database 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.
