Connecting to the Oracle database and troubleshooting

Connecting to the Oracle database and troubleshooting solutions

This article is a summary of my connection to Oracle, especially suitable for program developers and Oracle cainiao. HowConfigurationTo connect the client to the database: To enable a client machine to connect to the Oracle database, you must install the Oracle client software on the client machine. The only exception is that the JDBC thin mode can be used when Java connects to the database, you do not need to install Oracle client software. If you have installed an Oracle database on the machine, you do not need to install the Oracle client on the machine alone, because the Oracle client is automatically installed when the Oracle database is installed. New users who have used SQL Server databases and then use Oracle may have the following questions: What do I have to install SQL server clients when I use SQL server? The reason is very simple. SQL Server is also Microsoft. It is integrated into the SQL Server client in the operating system. If Microsoft and Oracle have a protocol, the Oracle client is also integrated into the operating system, so we don't have to install the Oracle client software on the client machine.Number of shardsWhy is it true that Jack Meng has sent a letter about his/her attention? Some people may ask: why does the SQL server have no listening port? in OracleConfigurationListening port? In fact, SQL Server also has a listening port, but Microsoft has fixed the listening port to 1433 and does not allow you to modify it at will. This gives you the illusion that SQL server does not have a listening port, microsoft encapsulates too many things into a black box, which also brings side effects. The listening port in Oracle is directlyConfigurationIn the file, you can modify it as needed, except that it must be consistent with the listening port set on the Oracle server. Well, let's get down to the point. How can we connect the client to the Oracle database? A. Install related software B. perform appropriate operationsConfiguration A. Install the appropriate software in the appropriate location: On the client: 1. Install Oracle's Oracle Net Communication Software on the client machine, which is included in the Oracle client software. 2. CorrectConfigurationSqlnet. ora file: Names. directory_path = (tnsnames ,....) Names. default_domain = db_domain Generally, the parameter names. default_domain is not required. If you want to comment out or delete this parameter with #, for names. use the default value for the directory_path parameter. the default_domain parameter sometimes needs to be commented out, which is explained in detail below. 3. CorrectConfigurationNowTnsname. Ora File Servers: 1. Ensure that the listener has been started 2. Make sure 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 to start the database If the listener is not started, use: LSNRCTL start [listener name] LSNRCTL status [listener name] Command to start listener B. perform appropriate operationsConfiguration CorrectConfigurationTnsname. Ora file: Oracle Net configuration assistant or Oracle Net manager graphics can be used on client machinesConfigurationTool for the clientConfiguration,ConfigurationThe tool actually modifies the tnsnames. ora file. Therefore, we can directly modify the tnsnames. ora file. The following uses directly modifying the tnsnames. ora file as an example: The file is located :... /Network/admin/tnsnames. ora (for Windows) ... /Network/admin/tnsnames. ora (for UNIX) Here, assume that the server name is testserver, the service name is orcl.testserver.com, and the listening port used is 1521. Then, a test network service name (Database alias) in the tnsnams. ora file is: Test = (Description = (Address_list = (Address = (Protocol = TCP) (host = testserver) (Port = 1521 )) ) (CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com) ) ) The smiling face here is ). The red content is the content that needs to be modified according to the actual situation. The explanation is as follows: Protocol: The Protocol for communication between the client and the server, which is generally TCP. You do not need to change the content. Host: The name or IP address of the machine where the database listens. The database listens on the same machine as the database. Therefore, 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 (for Windows) 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 database listens on the listening port. 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> show parameter SERVICE_NAME command after logging on to the system. How to UseConfigurationThe Network Service name of is connected to the database: Use the sqlplus program to test the network service name, such as sqlplus system/manager @ test. If you cannot connect to the databaseTnsnameAdd the db_domain parameter value of the Oracle database to the name of the test network service (net service) in the. ora file and run the sqlplus> show parameter db_domain command. Here, the db_domain parameter value is testserver.com and is added to the name of the network service.TnsnameThe Network Service name in. ora is as follows: Test.testserver.com = (Description = (Address_list = (Address = (Protocol = TCP) (host = testserver) (Port = 1521 )) ) (CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com) ) ) The smiling face here is ). Use the sqlplus program to pass the test.testserver.com network service name test, such as sqlplus system/manager@test.testserver.com. For the reason why db_domain parameter is added after the network service name, we need to understand the principle of SQL * Plus connecting to the database. I will give a detailed description in the troubleshooting section 12154. If the above moves do not work, you have to move them away. Add the network service name of the client Test.testserver.com = (Description = (Address_list = (address = (Protocol = TCP) (host = testserver) (Port = 1521 )) ) (CONNECT_DATA = (SERVICE_NAME = orcl.testserver.com) ) ) The smiling face here is ). Copy to the tnsnames. ora file on the server. Then the server connects to the database with sqlplus system/manager@test.testserver.com. If the connection succeeds, the network between your client and the server is faulty. If the connection fails, use the previous section to check whether the network service name is correct, if you are sure that the network service name is correct and that all clients cannot connect to the database, the system TCP/IP or Oracle system may be faulty. We recommend that you reinstall the database. Common troubleshooting methods: TNS-12154 (ORA-12154 ):TNS: cocould not resolve service name This error indicates that the network service name used for connection is in tnsnames. the ora file does not exist. the Network Service name in ora is only test. If you use sqlplus system/manager @ test1 during connection, a TNS-12154 error is returned. Note that sometimes, even if the tnsnames. ora file contains a corresponding network service name, an error occurs when you use this network service name for connection. This is a typical caseConfigurationAs follows (on the client 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 smiling face here is ). Basic mechanism of SQL * Plus operation: After you enter sqlplus system/manager @ test, the sqlplus program automatically goes to sqlnet. find names in the ora file. default_domain parameter. If this parameter exists, the value in this parameter is taken out and added to the name of the network service, in this example, your input is automatically changed from sqlplus system/manager @ test to sqlplus system/manager@test.server.com, and then to tnsnames. the Network Service name test.server.com cannot be found in the ora file, because the file contains only the test network service name, so an error is returned. The solution is to comment out the names. default_domain parameter in the sqlnet. ora file, for example, # names. default_domain = server.com. Suppose names. if the default_domain parameter does not exist, the sqlplus program directly goes to tnsnames. find the name of the test network service in the ora file, and then extract the host, port, TCP, and SERVICE_NAME from the file to send the connection request to the correct database server. In principleConfigurationIt is case-insensitive, but I do have a case-sensitive situation, so it is best to compare the network service used with tnsnames. oraConfiguration. ORA-12514:TNS: Listener cocould 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 SERVICE_NAME on the server. The solution is to modify SERVICE_NAME in tnsnames. ora. Confusing terms: Db_name:The unique identifier of a database (Oracle database), which is the Oracle database described in Chapter 1. This expression is sufficient for a single database, but with the popularization of distributed databases composed of multiple databases, this method of Command database creates a certain burden on database management, because the names of various databases may be the same, resulting in management confusion. To solve this problem, the db_domain parameter is introduced. In this way, the database ID is determined by the two parameters db_name and db_domain, avoiding management confusion caused by database name duplication. This is similar to managing machine names on the Internet. We connect the parameters db_name and db_domain with '.' to indicate a database, and the database name is global_name, that is, it extends db_name. The db_name parameter can only contain letters, numbers, '_', '#', and '$', and can contain a maximum of 8 characters. Db_domain:Define the domain where a database is located. The domain name has nothing to do with the 'region' on the Internet. It is determined by the database administrator to better manage the distributed database. Of course, to facilitate management, it can be equal to the domain of the Internet. Global_name:For the unique identifier of a database (Oracle database), we recommend that you use this method to command the database. This value is determined when you create a database. The default value is db_name. db_domain. Any modifications to the parameters of db_name and db_domain In the parameter file will not affect the value of global_name. To modify global_name, you can only use the alter database rename global_name to <db_name.db_domain> command to modify it, modify the parameters. SERVICE_NAME:This parameter is newly introduced by Oracle8i. Before 8i, we used Sid to identify an instance of the database. However, in the parallel environment of Oracle, a database corresponds to multiple instances, so that multiple network service names are required, configuration is cumbersome. To facilitate the setting in the parallel environment, the SERVICE_NAME parameter is introduced, which corresponds to a database rather than an instance, and has many other advantages. The default value of this parameter is db_name. db_domain, which is equal to global_name. A database can correspond to multiple service_names for more flexibleConfiguration. This parameter has no direct relationship with Sid, that is, the service name must be the same as the SID. Net service name:The Network Service name, also known as the database alias (Database alias ). Is a client programNumber of shardsThe database needs to block the details of how the client connects to the server, achieving the database location transparency.