Personal Understanding of Oracle databases, instances, and configuration files

Personal Understanding of Oracle databases, instances, and configuration files
Oracle configuration file
This section describes the network configuration files of Oracle: listener. ora, sqlnet. ora, and tnsnames. ora. They are all stored in the $ ORACLE_HOME/network/admin directory;
Tip: if you do not remember the $ ORACLE_HOME directory, you can use plsql dev to log on to the database and perform the Support Info sub-menu in the Help (Help) menu on the menu bar, for example:


Find the path information of the TNS File and find the directories of the three files to be introduced below.
1. sqlnet. ora ----- use this file to determine how to find a connection string that appears in the connection.
For example, we enter
L log on with SQL plus
Sqlplus plms/lyplms @ dedev
L or use PL/SQL DEV to log on

Assume that my sqlnet. ora is like the following:
SQLNET. AUTHENTICATION_SERVICES = (CNT)
NAMES. DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
Then, the client will first be in tnsnames. find the dbdev record in the ora file. if there is no corresponding record, try to treat dedev as a host name, resolve its ip address through the network, and then connect to the instance GLOBAL_DBNAME = dbdev on this ip address, of course, dbdev is not a host name.
If I look like this
NAMES. DIRECTORY_PATH = (TNSNAMES)
Then the client will only search for ora records from tnsnames. ora. There are other options in brackets, such as LDAP, which are not commonly used.
2. tnsnames. ora ------ this file is similar to the unix hosts file and provides a corresponding tnsname to the host name or ip address.
This file is used only when NAMES. DIRECTORY_PATH = (TNSNAMES) is similar to that in sqlnet. ora, that is, the client uses TNSNAMES to parse the connection string.
Example:
Dbdev =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.11) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbdev)
)
)
Dbdev: the service alias used by the client to connect to the server. Be sure to write at the top; otherwise, the Service alias cannot be recognized.
PROTOCOL: the PROTOCOL for communications between the client and the server, which is generally TCP.
HOST: the IP address or hostname of the ORACLE server. Make sure that the server listening starts properly.
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.
3. listener. ora ------ listener process configuration file
The listener process is not much said. The listener process accepts remote database access requests and transfers them to the oracle server process. Therefore, if you do not use a remote connection, the listener process is not required. Similarly, disabling the listener process does not affect the existing database connection.
Example:
# Listener. ora Network Configuration File: # E:/oracle/product/10.1.0/Db_2/NETWORK/ADMIN/listener. ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dbdev)
(ORACLE_HOME = G: \ oracle \ product \ 10.2.0 \ db_1)
(SID_NAME = dbdev)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 1521 ))
)
LISTENER: LISTENER name. Multiple listeners can be configured. The port numbers of multiple listeners must be separated.
GLOBAL_DBNAME: Global Database Name. Obtained through the select * from global_name; query
ORACLE_HOME: oracle software and Directory
SID_NAME: SID of the server (Local Machine)
PROTOCOL: Listener PROTOCOL, which generally uses TCP
HOST: the IP address of the Local Machine. Floating IP addresses are used for Dual Servers.
PORT: the listening PORT number. Use netstat-an to check whether the PORT is occupied.
When you enter sqlplus plms/lyplms @ dedev
1. query sqlnet. ora and check the name resolution method. It is TNSNAME.
2. query the tnsnames. ora file, find the dbdev record from the file, and find the host name, port, and service_name.
3. If there is no problem with the listener process, establish a connection with the listener process.
4. Depending on different server modes, such as dedicated server mode or shared server mode, listener takes the next action. The default mode is dedicated server. If there is no problem, the client will connect to the server process of the database.
5. At this time, the network connection has been established, and the historical mission of the listener process has been completed.
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.
References:
2. Databases and Instances
Database: a collection of physical operating system files or disks (such as data files, temporary files, redo log files, and control files ). When Oracle 10 Gb Automatic Storage Management (ASM) or RAW partition is used, the database may not be used as a separate file in the operating system, but the definition remains unchanged.
Instance: A group of Oracle background processes/threads and a shared memory zone. These memories are shared by threads/processes running on the same computer. Here, you can maintain easy-to-lose and non-persistent content (some can be refreshed and output to the disk ). Database instances can exist even if there is no disk storage. Maybe the instance is not the most useful thing in the world, but you can think of it as the most useful thing, which helps draw a line between the instance and the database.
These two words are sometimes interchangeable, but they have different concepts. The relationship between the instance and the database is that the database can be loaded and opened by multiple instances, and the instance can load and open a database at any time point. In fact, to be precise, the instance can load and open a database at most throughout its life!
The main tasks of an instance include:
1. Maintain various internal data structures that all processes need to access;
2. cache data on the disk, and cache data here before redoing data to the disk
3. Save resolved SQL plans
You have logged on to the DBA role and run the following command to view the database and instance.
SQL> show parameter service_name;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Service_names string dbdev

SQL> show parameter instance_name;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Instance_name string dbdev

SQL> show parameter db_name;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_name string dbdev