Beginner! I am a newbie in the Oracle database field! Complete its configuration today

First of all, I apologize to you for your lack of stability due to work reasons, so my blog has not been updated in time. Today, I finally took the time to update my blog and write the results I got in two hours.

 

Recently, the development platform of a new company is very different from the one I used.

Language: C # → Java

Database: MSSQL → oracle10

Developed software: Miscellaneous → Industrial Software

Development framework: the original framework written by myself → A framework written by the company's original cool people.

 

Since the environment has changed, we have to study hard to adapt to the environment. My colleagues configured the environment at the time of entry, so I have never had the opportunity to learn how to configure the environment myself. Today, I finally handed down oracle10. Install it, and then find out how to configure it.

Introduce my installation environment:

In this example, an XP Virtual Machine is enabled, and 1 GB memory is allocated to the virtual machine ). The Oracle10g server is installed on this virtual machine, and PLSQL and Oracle client lite versions are installed on host Vista.

 

Everything is installed successfully and configuration starts.

To be honest, I have no idea about Oracle configuration. There is no idea, and I do not know what keywords to use for searching on the Internet. It seems that I have heard of a word called listening ...... Configure listening for Oracle ......

Then the next thing is easy to do.

 

Use Net configuration assistant to configure "listeners" and "local net service name configuration ". After configuration, I wanted to modify the disk: \ oracle \ ora90 \ Network \ admin \ tnsnames. ora file on the client (that is, Vista) based on my memory. But after the modification, I don't know where the error occurs or my server is not correctly configured. When I use PLSQL to connect to the server, I cannot connect to the server ......

 

Therefore, a tnsnames file is copied from the disk: \ oracle \ product \ 10.2.0 \ db_1 \ Network \ admin \ directory on the server. ora file to the client Disk: \ oracle \ ora90 \ Network \ admin \ tnsnames. ora. If you connect to the database using PLSQL again, it still does not work.

 

Suddenly, does the firewall block port 1521? So shut down the firewall in the XP Virtual Machine (in fact, it is also possible to enable port 1521), and then connect again, OK!

 

Now, it will be configured. Now you can use it ......

========================================================== ======================================

The reference materials are as follows (the specific website cannot be remembered, sorry ):

 

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, then we can access the database without installing the Oracle client software on the client machine. However, this seems impossible.
Some may also ask: why does the listener port need to be configured in Oracle without being added to SQL Server? 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 directly in the configuration file and can be changed at will, 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 proper configuration

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. The sqlnet. ora file is correctly configured:
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. The tnsname. ora file is correctly configured.

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 proper configuration
How to correctly configure the tnsname. ora file:
You can use the Oracle Net configuration assistant or Oracle Net manager graphical Configuration tool on the client machine to configure the client. The configuration 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 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 (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 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> show parameter SERVICE_NAME" command after logging on to the system.

How to Use the configured network service name to connect 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 database. add sqlnet. names. the value of the default_domain parameter. Here, my parameter value is testserver.com. Add it to the name of the network service and the modified tnsname. the Network Service name in ora is as follows:

Under what circumstances will Oracle automatically set the names. default_domain parameter?
In this case, the typical environment is the Windows client's "my computer", "properties", "computer name", and "others... À the computer's primary DNS suffix 'sets 'Primary DNS suffix', because in this case, when the client is installed. names is automatically set in the ora file. the default_domain parameter may also occur when the computer is added to the domain to install the Oracle client. If you have any conditions, try it.

When setting the Oracle client, I usually manually modify tnsnames. ora file, but many people prefer to use a graphical tool to configure it. The graphic tool eventually modifies tnsnames. ora file, but it sometimes causes other problems:
When you configure the network service name using the Oracle graphics software 'net aser' or 'net configuration asis', if you have set 'Primary DNS suffix ', however, the name of the network service written in the graphic configuration software does not contain 'Primary DNS suffix '. For example, if you only write test, then, the graphic configuration software automatically adds 'Primary DNS suffix 'to test.testserver.com and has tnsnames. ora, regardless of your sqlnet. whether names exists in the ora file. default_domain parameter. In this case, the connection is tested using a graphical tool, but if sqlnet. the ora file does not contain names. default_domain parameter, you should use the network service name in tnsnames. test.testserver.com in ora, instead of the test you typed in the graphic configuration software. Solution:
<1> you can set names. default_domain = testserver.com in the sqlnet. ora file. In this case, you can use test or test.testserver.com to connect to the database.
<2> do not set the names. default_domain parameter in the sqlnet. ora file. Remove .testserver.com from test.testserver.com in the tnsnames. ora file. In this case, you can use test to connect to the database.

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: 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 in tnsnames. the ora file contains the corresponding network service name, but an error occurs when you use this network service name to connect to the file. The following is a typical configuration (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 principle, the configuration in tnsnames. ora is case-insensitive, but I do have a case-sensitive situation, so it is best to use the network service exactly the same as the configuration in tnsnames. ora.

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). This database is the Oracle database mentioned 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: defines 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 in order to better manage the distributed database. Of course, to facilitate management, it can be equal to the domain of the Internet.

Global_name: 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 flexible configuration. This parameter has no direct relationship with Sid, that is, the service name must be the same as the SID.

Net service name: the name of the network service, also known as the database alias (Database alias ). It is required when the client program accesses the database. It shields the details of how the client connects to the server and achieves the location transparency of the database.