Oracle Configuration Management

Preface: We learned to install Oracle and create a database, if you want to connect the client to the server, this requires learning the connection configuration of the database. You open the database by opening the control file during the startup of the database and opening the data file and redo the log file with the control file. This document introduces you to the connection configuration, as well as the principle and operation of the log file, and how to use and manipulate the data dictionary view.

First, the Oracle client and server-side communication mechanism

After the Oracle product installation is complete, both the server and the client need network configuration to enable network connectivity, server-side configuration listeners, and the client to configure the network service name.

1. Oracle NET Protocol
Oracle NET protocol enables client-to-server connectivity and data transfer. Oracle NET is a software layer that resides both on the server side and on the client, which encapsulates TCP/IP and is responsible for establishing a connection to the database server that maintains the client application. There is a protocol that will have a corresponding port, so Oracle NET has a default listening port of 1521.

The request made by the client is transformed by the Oracle NET protocol into the information that can be transmitted over the network, and the request is transferred to the database server side through the TCP/IP network, and the server receives the client request and transforms through the Oracle NET protocol. Converting a request to a database interprets the local instructions executed and executes on the server side and transmits the results to the client through TCP/IP and the Oracle NET protocol.

2, the client and the server side of the connection process
The Oracle server receives a connection request from the client through a component named Oracle Net Listener, listening to a background process on the server side. The client connection request that is sent is first listened to by the server's listener and is handed to the corresponding DB instance to establish a client-server connection. After the connection is established, the client and the server can communicate directly without the need for listeners to participate. To enable listeners to listen to customer requests, the listener needs to be configured.
The Oracle Network service name is an identifier that represents the configuration information for the client to connect to the server.

The client-to-server connection process is as follows:
1) There is a resident listener on the server side that listens for connection requests made by the client.
2) The user is using the client (Enterprise Manager or SQL Tools), enter the user name, password, and network service name, or enter a request similar to "CONNECT username/[email protected]_service_name" on the SQL command line.
3) The Client views the Network service profile Tnsnames.ora file and maps the network service name to the connection descriptor that contains the Oracle server address, listening port, and global database name.
4) The client locates the listener based on the connection descriptor and passes the network connection information to the listener.
5) The Listener queries the listener configuration file Listener.ora to find the server to which you want to connect.
6) The client and the server begin to communicate.

3. The tools for configuring and managing Oracle Network configurations are common in the following categories:

The code in the red box above the listener supports no services shows that no service is registered to the listener, which is related to the boot sequence of the listener and DB instance.

WORKAROUND: Listen for startup and the order in which the DB instance starts
Based on the previous knowledge, when you start the database instance, the listener is started, and when you connect to the database, only both of these are available for Oracle to provide services outside.
The sequence of monitoring and instance initiation will have an impact on the coordination of work between the two, as follows:

Start the listener first, then start the instance: At this point the remote client connection instance is normal, there is no problem, because the listener starts first, when the instance is started, the background process Pmon listens to the registration server, when the user requests the service, the coordination of the two is ready.
Start the instance first, then start listening: If the listener has just started, the user immediately connects to the instance and may report the "Listener is currently unable to recognize the service requested by the connection descriptor" because Pmon has not yet been able to register the instance service with the listener. But this problem will only exist for a short period of time, then the connection will not have a problem, why wait a while? Because Pmon every once in a while will see the service need to listen to register, at this time if the monitoring has been started, Pmon can register successfully, the coordination of the two work is ready.
Is there a way to solve this problem within a short period of time? The answer is certain. is to static registration, the service description of the instance is added to the Listener.ora, so as long as the start of monitoring, the corresponding service in the listening register. This is as long as the instance starts to complete properly, it can provide the server externally. Add the following code in the Listener.ora code.

II. Management and control documents

Control files are very important files that master the following:
Understanding the role of control files in database startup
? get control File information
? View information about what is stored in the control file
? storing multiple control files
? backup and Recovery control files

1, the role of control files in the database startup
For DNA, the Oracle database control file is a very important file, which is a binary file that is automatically generated when the database is created, which records the status information of the database. No other user can modify the control file, and the database instance may modify the information in the control file only while the database is running. The control file mainly includes the following content:
? database name, a control file can belong to only one database.
? database creation time.
? The name, location, online, and offline status information for the data file.
The name, location, and archive information of the redo log file.
? All tablespace information.
? The current log sequence number.
? Recent checkpoint information.

The control file is read in the Mount phase of the database startup, and the relationship between the database startup and the control file is as follows:

When the database starts with the default rules to find and open the parameter file, in the parameter file containing the control file location information, open the control file, the control file will be recorded in the location of the various database files opened the database to start the database to the available location, so after the database started, While the database is running, the database server can constantly modify the contents of the control file, so the control file must be read-write during the opening of the database, but no other user can modify the control file, and only the database instance may modify the information in the control file.

2. Get control File information
1) Control file information can be viewed from the V$controlfile view, including the control file name.

Iii. managing redo Log files

The redo log file is also called an online redo log to recover data.
As the database runs, the data that the user changes is temporarily stored in the database's buffer zone. In order to improve the speed of writing a database, it is not necessary to write the changed data into the database file once the data changes. Frequent read and write disks can reduce the efficiency of the database system, so the DBWR process will write the changed data to the data file until the data in the database buffer zone reaches a certain amount or meets certain conditions. In this case, the data in the database cache will be lost if DBWR has crashed before writing the changed changes to the data file. It is obviously not possible to recover data changed by this part of the user after the database server restarts.
Redo the log is to save the changed data first, wherein the LGWR process is responsible for the user changed data written to the redo log file, so that the database restarts, the database system from the Redo log files read the changed data, the user changed data submitted to the database, write data files.
To improve disk efficiency and prevent redo log file corruption, Oracle introduces a redo log file structure,

You can see that there are 3 redo log groups, each containing two redo log members. When the first log group is full, it stops writing, turns to the second log group, the second one is full, turns to the third-day group, and the third one is written to the first log group, and Oracle uses the Redo log group in this circular manner.
Oracle requires at least two log groups per database, with at least one or more log members per group.
Before using the new redo log, the DBWR process needs to write all of the data changes to the data file. If the database is in archive mode, when the group switchover occurs, the archive process arch will copy the data from the currently full redo log file to the archive log.

Iv. management of archived log files

1. Introduction of archive Log
The Oracle database operates in two ways, archive (ARCHIVELOG) and non-archived (NO ARCHIVELOG). In non-archive mode, the log switch overwrites the previous redo log files directly and does not produce an archive log. The database runs under archive mode, and after the log switchover, the arch process will archive its own full redo log file. By default, Oracle uses non-archive operation, mainly because the archiving method will bring some performance problems, only when the database is running in the archive mode, the arch process exists, the arch process is Oracle's optional background process, the log archive, To save all modifications made to the database so that the database administrator can restore the database to the state at the time of the failure when the data file disk is damaged.
1) Archive process
? is an optional background process
? online redo log files are automatically archived when you set Archivelog (archive) mode for a database
? Keep a record of all changes to the database
2) Archive log files
The database generates a copy of the online redo log group before allowing overwriting of the redo log information.
? These replicas are also known as "archive Logs".

2. Configure Database archive log steps:
? Query the Database archive method to determine if it is not currently in the archive mode
? Close the database and start the database to Mount state
? Set the database to the bit archive mode and verify
Sql>archive log list;
Sql>shutdown immediate;
Sql>startup Mount;
Sql>alter database Archivelog;
Sql>alter database open;
Sql>archive log list;

V. Data dictionary

1. Data dictionary definition
A data dictionary is a collection of tables and views where Oracle stores critical information. is a description of the database that contains the names and properties of all objects in the database. The Oracle process maintains these tables and views in the SYS mode, that is, the owner of the data dictionary is the SYS user, and the data resides in the system table space.
The data dictionary describes how the actual data is organized, such as the creator information of a table, the creation of time information, the information about the table space, the user access rights information, and so on. They can query as if they were working with other database tables or views, but they cannot make any modifications.
Oracle data dictionaries are typically created when data is created and installed.
Oracle data dictionaries are fundamental to the work of Oracle database systems.
Without the support of a data dictionary, the Oracle database system cannot do any work.

2, the composition of the data dictionary
Data dictionaries are divided into data dictionary tables and data dictionary views. Tables in a data dictionary cannot be accessed directly, but they can access the views in the data dictionary. The Data dictionary view is divided into two categories: static data dictionary view and Dynamic Data dictionary view.

Vi. Dynamic Data dictionary View and usage

1. Concept
In addition to the 3 class views in the static data dictionary, the other dictionary views are primarily v$ views, and are named because they are all beginning with v$ or gv$, and these views are constantly updated to reflect the health of the current instance and database. Dynamic performance tables are used to record the activity of the current database, only during the database run, where the actual information is taken from memory and control files, and the DBA can use dynamic views to monitor and maintain the database. Here are a few examples of the Dynamic Data dictionary views that DBAs use frequently in maintaining databases.

Oracle Configuration Management