Oracle Configuration Management

Oracle client-to-server 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.

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.

..

Client-to-server 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.
..

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

..
1) NETCA mode

..

..

..

..

..

..

..

..

..

Viewing a listening file
Cat/opt/oracle/product/11.2/db_1/network/admin/listener.ora
.
Open a new listener
Lsnrctl Start Listener1
.
View listener Status
Lsnrctl Status Listener1
.
Netmgr Way

Add listeners to
.
Command mode
Modify Vim/opt/oracle/product/11.2/db_1/network/admin/listener.ora
Add a new listener with the following code:

.
Open a new listener
.
Lsnrctl Start Listener1
.

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.

..

Connections to clients
Clients connecting to the database server must know some information, such as the host name or IP address of the database server, the communication protocol used, the port number, and the corresponding database service name, which need to be set on the client
..
1) Install the Oracle client software on Win7

Oracle-product-11.2.0-client_lite-network-admin

The Oracle folder will be generated under the C: disk when the installation is complete:

..

2) Locate the C:\oracle\product\11.2.0\client_lite\network\admin\tnsnames.ora file and modify the results as follows:

..
3) Note: You need to add parsing in the Hosts file
Windosws-system32-arivers-etc

..
4) Open the named line on the Win7 client and execute the command connection

..

has been remotely connected to an Oracle server

..

Management control Files
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

..

The role of control files in 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 table space information.
The current log sequence number.
The most 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.
..

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

..
You can also view it from the V$parameter view

..
View information about what is stored in the control file

..
From there, you can see information such as database creation, redo log information, data files, and archived log file records in the control file. These valuable information is used for data maintenance and management, and many data dictionary views are information obtained from control files.
..

Storing multiple control files
Because the control file is very important, so require control files can not only one, usually more than 3 control files in the database, and stored on different disks, this method of using control files is also known as control file multiplexing. One way to achieve multiple is to include all control file names by copying the control file to multiple locations and modifying the Control_files parameter in the initialization parameter file. It is important to note that when there are multiple control files, Oracle updates all control files at the same time, but only reads the first control file enumerated in Control_files.

..
Examples of ways to implement a multiplexed control file:
1) The database status is open and the Control_files parameter in SPFile is modified.

..
2) Close the database

..
3) Copy the files to the new location using the operating system command

..
4) Restart the instance

..

Backup and recovery control files
Backup control files
There are two ways to back up: one is to back up as a binary file, and the other is to back up as a script file, the usual way to back up a binary file (backup binaries are copied for the control file)

..
Recovery control files
Delete control file Control01.ctl (simulated corruption)

..
Start Database (Error)
Startup
..
Perform command recovery
Alter system set control_files= '/opt/oracle/oradata/orcl/control03.ctl ' scope=spfile;
..
See all three files are back (because as long as a file is not lost, you can execute the above command, and then generate an additional two files), the database can also be started.
Startup.
..

Or:
If Control01.ctl is lost, you can either use Control02.ctl to replace the control01.ctl, or copy a name to Control01.ctl.
..

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.
..
Read Redo log file information
The data dictionary view v$log records the current database's log group number, the log sequence number, the size of each log file, the number of members per log group, whether it is archived, and the current state of the log group.
Using V$log to view redo log files

..
There are 3 log groups in the output, and each log group has one log file. The log group 3-bit is currently in use by the Redo log group, which becomes the current log group.
..
The data dictionary view v$logfile records the current log group number, the status of the log group, the type, and the log group membership information.

..
Create Redo Log Group
Add redo log members and distribute redo log members for each log group on different disks, improving disk efficiency and preventing corruption of redo log files.

..
Grammar:
ALTER DATABASE [database_name]
ADD LOGFILE [GROUP Group_number]
Filename [SIZE Size_integer [k\m]] [reuse]

Instance:

..

The two redo log files in are the same group, and the two redo log members are online.
Note: The log file size is generally more appropriate in 10m-50m.
..
Delete Redo Log Group
The following restrictions apply to deleting filegroups:
The current log group is not deleted
To delete the current log group, you need to switch the current log group first
Use the command for the alter system switch logfile;
Active log groups are not deleted
Log groups that are not archived are not deleted (if they are already running in archive mode)

..
Add Delete redo log file

..

..

..

The newly added redo11.log is found to be invalid because the log members of the three log groups are not yet full, so redo11.log is not valid, as long as two forced log switching can be found to be online status, the method is as follows:

..
Perform a few more times to view the file again:

Discover that Redo11.log is already online.
..
Delete Redo log Files Redo11.log

The hint cannot be deleted because Redo03.log is currently in use and the status is current.
After a forced switchover, it can be deleted.
..

..

..

Log switching and checkpoint events
Log switching is to stop the current log group, in turn to write another new log group, the system can automatically switch, or manually switch, when the log switch occurs, the system will complete the operation of the checkpoint in the background to reduce the recovery time of the instance.
A checkpoint is an event that reduces the time that an instance recovers, and when a checkpoint occurs, it triggers the DBWR process to write data that has changed in the data cache to the file, while the checkpoint process updates the control file and the data file header to keep them consistent. The checkpoint is actually a background process that guarantees that everything that has been modified in the database buffer is written to the database file.
The more frequent the checkpoint, the less data in the reuse log that requires database recovery once the database has failed (because it is not necessary to check the data in the reuse log before the recovery checkpoint is restored), so it reduces the time for instance recovery.
Start time:
Every three seconds (or more frequently)
Log switchover occurs
Immediate option to close the routine when
Manual Request
Responsible for the following matters:
Update the data file title with checkpoint information
Updating control files with checkpoint information
Signal to DBWN at completion checkpoint
..

The Force log Switch command is as follows:

The force-generated Checkpoint event (manual) command is as follows:

..
Manage Archive log files

Archive Log Introduction
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.
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
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".

..

..

To configure the database archive log step:
Querying the Database archive method to determine whether it is currently not 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;

..
1) Query the database archiving method, determine the current is not in the archive mode.

..
2) Close the database and start the database to mount mode.

..
3) Use the ALTER DATABASE Archivelog command to set the database to archive mode and verify that the archive mode has changed.

..

Get archived log information
The data dictionary view helps you get database configuration information. Users get archived log information by using the V$archive_dest and V$archive_log views.
V$archive_dest: Displays all current archive log storage locations and their status.
V$archive_log: Displays historical archive log information.
How to view all valid archive log file storage directories:

..

Gets the information of the archived log file, which copies the online redo log files to an archive log file after each log switchover, resulting in a sequential sequence of log files that can be used to restore a backup. The names and locations of these log files are controlled by a number of initialization parameters.

..

Data dictionary
Data dictionary Definitions
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.

..

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.
..

Data dictionary Table
The data in the Data dictionary table is the system data stored by the Oracle system, and the ordinary table holds the user's data. To facilitate the distinction between these tables, the names of these tables are terminated with $, and the tables belong to the SYS user.
To facilitate querying of data dictionary tables, Oracle has created a user view of these data dictionaries, which is easy to remember and hides the relationships between data dictionary tables.
..
static Data dictionary View
The views in the static data dictionary are divided into 3 categories, each consisting of 3 prefixes: USER*,all, Dba_.
User*: This view stores information about objects owned by the current user (all objects in that user mode).
All: The view stores information about the objects that the current user is able to access, ( all * does not need to own the object when compared to User_, only requires permission to access the object).
DBA*: This view stores information about all objects in the database (provided that the current user has access rights and generally must have administrator privileges).

..

Common Data dictionary views
User_tables: Describes information about all the tables currently owned by the user, including table names, table space names, cluster names, and so on.
This view provides a clear picture of what tables are owned by the current user.
SELECT table_name, tablespace_name from User_tables;

..

..
Show Results:

..
Query which indexes the user has
SELECT index_name from User_indexes;

The results appear as follows:

3) Query What views the user has

..
The results appear as follows:

Query which database objects the user owns, including tables, views, stored procedures, triggers, packages, indexes, sequences, and so on.
SELECT object_name from User_objects;

..
The query results are as follows:

Query the current user's information, mainly including the current user name, account ID, account status, creation time, and so on.
SELECT * from User_users;

Query results, I only display the user name and ID.
..

Queries all tables, procedures, functions, and so on that the current user has access to.

..

DICTIONARY Data dictionary view contains the names and descriptions of all objects in the data dictionary
SELECT * from dictionary;
..

..

The output results are as follows.

..
Using a data dictionary does not require remembering all data views. Just follow these steps:
Understand the data dictionary is roughly divided into 3 categories (user*,all, Dba_), commonly used is user_*, you can use the following statement to query the description of the specific view.
..

Then according to the query results to find the necessary data dictionary view, for example: to know the table can query user_table, want to know the view can query User_view, want to know the stored procedure can query user_procedure, want to know the table column in the case can query user_ Tab_columns.
..
Dynamic Data dictionary View and usage

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.
..

Enumerate common Dynamic Data dictionary views
1) Query and log file related information

Lists all the Dynamic Data dictionary views related to log files, and you can use the V$log view and the V$logfile view if you want to learn more about log files.
..

2) View Log Group status information

..
3) View Redo log file information

..
4) Query the information for the redo log file that is currently in use.

..
As can be seen from the results, the current database is in use by group 2, the database is running in non-archive mode, the log group has a log member, the storage directory is/opt/oracle/oradata/orcl/redo02.log

5) View instance information through the V$instance view

The results show that, of course, the instance name is ORCL, the host name is Oracleserver, the version number is 11.2.0.1.0, the instance start time is November 27, 2016, and the status is allowed.
..
6) View information for the current database

..
The database is named ORCL and is created on November 18, 2016, and the database is running in archive mode.

The Dynamic Data dictionary view is a good reflection of the current database running state information, which provides information support for database Performance tuning and judging system bottleneck. The Dynamic Data dictionary view also allows you to view information about the control file, information about the data file, and tablespace information.

Oracle Configuration Management