Shulou(Shulou.com)06/03 Report--

Blogger QQ:819594300

Blog address: http://zpf666.blog.51cto.com/

Friends who have any questions can contact the blogger, the blogger will help you answer, thank you for your support!

We learned how to install oracle and create a database. If you want to connect the client to the server, you need to learn the connection configuration of the database. In the process of starting the database, we need to open the control file, and use the control file to open the data file and redo the log file, so as to open the database. This document introduces you to the connection configuration, as well as the principle and operation of log files, and how to use and manipulate the data dictionary view.

1. Communication mechanism between Oracle client and server.

After the installation of Oracle products, both the server and the client need network configuration to achieve network connection. The server configures the listener and the client configures the network service name.

1. Oracle net protocol

Oracle realizes the connection and data transmission between client and server through oracle net protocol. Oracle net is a software layer that resides on both the server and the client. It encapsulates TCP/IP and is responsible for establishing and maintaining the connection between the client application and the database server. If there is a protocol, there must be a corresponding port, so oracle NET has a default listening port 1521.

The request sent by the client is first converted into information that can be transmitted through the network through the oraclenet protocol, and the request is transmitted to the database server through the tcp/ip network. After receiving the request from the client, the server converts the request into local instructions that the database can interpret and execute, and executes it on the server, and transmits the results to the client for display through tcp/ip and oraclenet protocols.

2. The connection process between the client and the server

The Oracle server receives connection requests from the client through a component called the oracle net listener and listens to a background process on the server side. The client connection request sent is first listened to by the server listener and handed over to the corresponding database instance, thus the connection between the client and the server is established. After the connection is established, the client and the server can communicate directly without the participation of the listener. In order to realize the listener's listening to the customer request, the listener needs to be configured.

The Oracle network service name is an identifier that represents the configuration information of the client connection server.

The connection process between the client and the server is as follows:

1) there is a resident listener on the server side to listen for connection requests issued by the client.

2) the user uses (Enterprise Manager or sql tool) on the client side, enter a user name, password and network service name, or enter similar on the sql command line

Request for "CONNECT username/password@net_service_name".

3) the client views the network service profile tnsnames.ora file and maps the network service name to a connection descriptor containing the oracle server address, listening port, and global database name.

4) the client locates the listener according to the connection descriptor and passes it to the listener through the network connection information.

5) the listener queries the listening configuration file listener.ora to find out the server to which you want to connect.

6) the client and server begin to communicate.

3. The common tools for configuring and managing Oracle network configuration are as follows:

1) netca mode

View the listening file:

Open a new listener:

Check the status of the listener:

2) netmgr mode

3) 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:

Solution: listen to the sequence of startup and database instance startup

According to the previous knowledge, after starting the database instance, start listening, when connecting to the database, only with both, oracle can provide services to the outside.

The sequence of monitoring and instance launch will have an impact on the coordination between the two, as shown below:

Start listening first, and then start the instance: at this time, it is normal for the remote client to connect to the instance, and there will be no problem, because the listening starts first. When the instance is started, the background process PMON will listen to the registration server. When the user requests for service, the coordination between the two is ready.

Start the instance first, and then start listening: if the listener immediately connects to the instance, it may report an error that "the listener does not recognize the service requested in the connection descriptor" because PMON has not yet had time to register the instance service with the listener. But this problem will only exist for a short period of time, and there will be no problem to connect again. Why wait for a while? Because PMON will see if there are any services that need to register with the monitor from time to time, if the monitoring has been enabled, the PMON can register successfully, and the coordination between the two is ready.

Is there any way to solve this problem in a very short period of time? The answer is yes. In the way of static registration, the service description of the instance is added to the listener.ora, so that as soon as the listening is started, the corresponding service is registered in the listening. At this point, as long as the instance is started normally, the server can be provided to the outside world. Just add the following code to the Listener.ora code.

4. Client connection

1) installation

The next step is all the way to the installation. When the installation is complete, the oracle folder will be generated under the c: disk.

2) find

C:\ oracle\ product\ 11.2.0\ client_lite\ network\ admin\ tnsnames.ora file, and modify the result as follows:

3) Note: parsing needs to be added to the hosts file.

If you encounter an error situation:

The solution is:

4) Open a named line on the win7 client and execute the command connection

Remotely connected to the oracle server

II. Management and control documents

The control file is a very important document, mainly mastering the following contents:

Understand the role of control files in database startup

Get control file information

View information about what is stored in the control file

Store multiple control files

Backup and restore control files

1. The role of control files in database startup

For dna, the oracle database control file is a very important file. It is a binary file automatically generated when the database is created, which records the status information of the database. No other user can modify the control file, only when the database is running, the database instance can modify the information in the control file. The control file mainly includes the following contents:

Database name, a control file can only belong to one database.

Database creation time.

Name, location, online and offline status information of the data file.

The name, location, and archive information of the redo log file.

All tablespace information.

The current log serial number.

Recent checkpoint information.

The control file is read during the mount phase of database startup. The relationship between database startup and control file is shown in the following figure:

When the database is started, the default rule is used to find and open the parameter file. The parameter file contains the location information of the control file. After opening the control file, the database will be opened through the location of the various database files recorded in the control file, thus starting the database to the available location, so after the database is started, during the operation of the database The database server can constantly modify the contents of the control file, so when the database is opened, the control file must be readable and writable, but no other user can modify the control file. Only the database instance can modify the information in the control file.

2. Obtain control file information

1) you can view the control file information, including the control file name, from the V$CONTROLFILE view.

2) you can also view it from the V$PARAMETER view

3. View the information of the contents stored in the control file

From the screenshot, you can see that the control file stores information about the creation of the database, redo log information, data files and archived log records and other information. This valuable information is used for data maintenance and management, and many data dictionary views are information obtained from control files.

4. Store multiple control files

Because the control file is very important, it requires that there can be not only one control file, usually more than 3 control files in the database, and stored on different disks. This method of using control files is also known as the multiplexing of control files. One way to achieve multiple is to copy the control file to multiple locations and modify the CONTROL_FILES parameter in the initialization parameter file to include all the control file names. 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 listed in CONTROL_FILES.

An example is given to illustrate the method of multiplexing control files.

1) if the database is open, modify the CONTROL_FILES parameter in SPFILE.

2) close the database

3) use operating system commands to copy files to a new location

5. Backup and restore control files

1. Backup control files

There are two ways to back up: one is to back up as binary files, the other is to back up as script files, and the commonly used method of backing up binary files is to copy control files.

6. Backup and restore control files

1. Backup control files

There are two ways to back up: one is to back up as binary files, the other is to back up as script files, and the commonly used method of backing up binary files is to copy control files.

2. Restore the control file (to make the experiment more convincing, close the oracle database first)

1) Delete the control file control01.ctl (simulated corruption)

2) start the database (error report)

3) execute the command to restore

4) after executing the above recovery command, the oracle database starts automatically. In this case, the control file is not restored by control03.ctl,control01.ctl. We can manually copy a copy of control03.ctl and manually change it to control01.ctl.

Or: (here is the easiest way to do it)

If the control01.ctl is missing, you can simply replace the control01.ctl with control03.ctl, or make a copy and rename it to control01.ctl.

Manage redo log files

The redo log file, also known as the online redo log, is designed to recover data.

During the operation of the database, the data changed by the user will be temporarily stored in the high-speed buffer of the database. In order to improve the speed of writing to the database, it is not necessary to write the changed data to the database file as soon as the data changes. Frequently reading and writing disks will reduce the efficiency of the database system, so when the data in the database high-speed buffer reaches a certain amount or meets certain conditions, the DBWR process will write the changed data to the data file. In this case, if there is an outage before DBWR writes the changed changes to the data file, all data in the database cache will be lost. It is obviously not possible if the data changed by this part of the user cannot be recovered after the database server is restarted.

The redo log is to save the changed data first, in which the LGWR process is responsible for writing the user-changed data to the redo log file first, so that when the database is restarted, the database system reads the changed data from the redo log file, submits the user-changed data to the database, and writes to the data file.

To improve disk efficiency and prevent redo log file corruption, oracle introduces a redo log file structure, as shown in the figure:

As can be seen in the figure above, there are three redo log groups, each containing two redo log members. When the first log group is full, it stops writing and moves to the second log group. When the second log group is full, it goes to the third log group, and the third log group is written to the first log group. Oracle uses the redo log group in this cycle.

Oracle stipulates that each database has at least two log groups, and each group contains at least one or more log members.

Before using the new redo log, the DBWR process needs to write all data changes to the data file. If the database is in archive mode, when a group of days switch occurs, the archiving process ARCH copies the data from the currently full redo log file to the archive log.

1. Read the redo log file information

The data dictionary view V$LOG records the log group number of the current database, the log sequence number, the size of each log file, the number of members of each log group, whether it is archived, and the current status of the log group.

1) use V$LOG to view the information of the redo log file

2) the data dictionary view V$LOGFILE records the current log group number, the status, type, and log group membership information of the log group.

Use V$LOGFILE to view redo log group information

3) create a redo log group

Add redo log members and distribute the redo log members of each log group on different disks to improve disk efficiency and prevent damage to redo log files.

Syntax:

ALTER DATABASE [database_name]

ADD LOGFILE [GROUP group_number]

Filename [SIZE size_integer [K\ M]] [REUSE]

Explanation: database_name: database instance name

Group_number: log group number

Filename: path and name of log file

4) Delete the redo log group

The restrictions for deleting a filegroup are as follows:

The current log group cannot be deleted

To delete the current log group, you need to switch the current log group first.

Use the command for alter system switch logfile

The active log group cannot be deleted

A log group that is not archived cannot be deleted (provided it is already running in archive mode)

5) add / delete redo log files

In the figure above, it is found that the newly added redo33.log is invalid because the log members of the three log groups are not yet full, so the redo33.log is invalid. You only need to make two forced log switches to find the ONLINE status as follows:

Found that redo033.log is already online.

Delete the redo log file redo033.log

If it cannot be deleted, it is estimated that group 3 is the currently used state (that is, group 3 is currrnt state). You need to altersystem switch logfile and then delete it!

6) Log switching and checkpoint events

Log switching is to stop the current log group and write another new log group. The system can switch automatically or manually. When the log switch occurs, the system will complete the checkpoint operation in the background to reduce the recovery time of the instance.

A checkpoint is an event that reduces the time for instance recovery. When a checkpoint occurs, it triggers the DBWR process to write the changed data in the data cache to the data file, while the checkpoint process updates the control file and the data file header to keep them consistent. A checkpoint is actually a background process that ensures that everything in the modified database buffer is written to the database file.

The more frequent the checkpoint time, the less data in the reuse logs that need to be recovered in the event of a database failure (because at the time of recovery, you do not have to check the data in the reuse logs before the recovery checkpoint). So the time for instance recovery is reduced.

Startup time:

Every three seconds (or more frequently)

Log switch occurred

When the immediate option closes the routine

Manual request

Responsible for the following matters:

Update the data file title with checkpoint information

Update control files with checkpoint information

Signal to DBWn at the completion checkpoint

The command to force log switching is as follows:

The force checkpoint event (manual) command is as follows:

4. Manage archived log files

1. Introduction to archived logs

There are two operation modes of Oracle database, ARCHIVELOG mode and non-NO ARCHIVELOG mode. In non-archived mode, the previous redo log files are directly overwritten during log switching, and no archived logs are generated. The database runs in archived mode, and after the log switch, the ARCH process will archive the redo log files that it is full of. By default, oracle runs in a non-archived mode, mainly because the archiving mode will bring some performance problems to the system. The ARCH process exists only when the database is running in archived mode. The ARCH process is an optional background process for oracle, archiving the logs so that all changes made to the database can be saved in time in case the data file disk is damaged. The database administrator can also restore the database to the state it was in when the failure occurred.

1) archiving process

Is an optional background process

Online redo log files are automatically archived when ARCHIVELOG mode is set for the 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 redo log information to be overwritten.

These copies are also known as "archive logs".

2. Configure database archive log steps:

Query the database archiving mode to make sure that it is not currently in archiving mode

Close the database and start the database to the MOUNT state

Set the database to bit archive mode and verify

Use the alter database archivelog command to set the database to archive mode

3. Obtain archive log information

The data dictionary view helps users get database configuration information.

Users obtain archive log information by using the V$ARCHIVE_DEST and V$ARCHIVE_LOG views.

V$ARCHIVE_DEST: displays the current storage location of all archive logs and their status.

V$ARCHIVE_LOG: displays historical archive log information (that is, information about archived log files).

How to view all valid archive log file storage directories:

Description: get the information of the archived log files, and the archiving process copies the online redo log files to an archived log file after each log switch, resulting in a series of continuous log files that can be used to restore a backup. The name and location of these log files are controlled by several initialization parameters.

5. Data dictionary

1. Data dictionary definition

A data dictionary is a collection of tables and views in which oracle stores key information. Is a description of the database, containing the names and properties of all objects in the database. The Oracle process maintains these tables and views in the SYS schema, which means that the owner of the data dictionary is the SYS user and the data is stored in the SYSTEM tablespace.

The data dictionary describes how the actual data is organized, such as the creator information of a table, creation time information, tablespace information, user access information, and so on. They can be queried like other database tables or views, but no modifications can be made.

Oracle data dictionaries are usually created when data is created and installed.

Oracle data dictionary is the foundation of oracle database system.

Without the support of the data dictionary, the oracle database system cannot do any work.

2. The composition of data dictionary.

Data dictionary is divided into data dictionary table and data dictionary view. The tables in the data dictionary cannot be accessed directly, but the views in the data dictionary can be accessed. Data dictionary views are divided into two categories: static data dictionary view and dynamic data dictionary view.

1) data dictionary table

The data in the data dictionary table is the system data stored in the oracle system, while the ordinary table stores the user's data. To make it easy to distinguish between these tables, the names of these tables end with $, and they belong to the SYS user.

In order to facilitate the query of data dictionary tables, oracle establishes user views for these data dictionaries respectively, which is not only easy to remember, but also hides the relationship between data dictionary tables.

2) static data dictionary view

Views in static data dictionaries are divided into three categories, each of which consists of three prefixes: USER_*, ALL_*, and DBA_*.

USER_*: this view stores information about objects owned by the current user (that is, all objects in that user mode).

ALL_*: this view stores information about objects that the current user can access (compared to USER_*, ALL_* does not need to own the object, only needs to have access to the object).

DBA_*: this view stores information about all objects in the database (provided that the current user has access and generally must have administrator privileges).

3. Commonly used data dictionary view

1) user_tables: describes the information of all tables owned by the current user, including table name, table space name, cluster name, etc.

From this view, you can clearly see which tables are owned by the current user.

2) query which indexes the user has

SELECT index_name FROM user_indexes

3) query which views the user has

4) query which database objects the user owns, including tables, views, stored procedures, triggers, packages, indexes, sequences, etc.

SELECT object_name FROM user_objects

5) query the information of the current user, including current user name, account id, account status, creation time, etc.

SELECT * FROM user_users

6) query all tables, procedures, functions, etc., that the current user can access.

7) the DICTIONARY data dictionary view contains the names and descriptions of all objects in the data dictionary

SELECT * FROM dictionary

8) you don't need to remember all the data views to use the data dictionary. Just follow these steps:

Understanding data dictionaries can be divided into three categories (user_*, all_*, dba_*). Commonly, user_*, can query the description of a specific view through the following statement.

Then find the data dictionary view you need according to the query results, for example, you can query user_table if you want to know the table, user_view if you want to know the view, user_procedure if you want to know the stored procedure, and user_tab_columns if you want to know the columns in the table.

VI. Dynamic data dictionary view and use

1. Concept

Except for the three types of views in the static data dictionary, the other dictionary views are mainly V$ views, so named because they all start with V$ or GV$, and these views are constantly updated to reflect the health of the current instance and database. Dynamic performance table is used to record the activity of the current database, which only exists during the operation of the database. The actual information is taken from memory and control files. Dba can use dynamic views to monitor and maintain the database. Here are a few dynamic data dictionary views that are commonly used in dba maintenance databases.

2. Enumerate common dynamic data dictionary views

1) query information related to log files

All the dynamic data dictionary views related to the log file are listed in the figure above. If you want to know more about the log file, you can use the v$log view and the v$logfile view

2) View log group status information

3) View redo log file information

4) query the information about the redo log files currently in use.

As you can see from the results, the log group currently in use in the database is group 2, the database is running in non-archive mode, and the log group has a log member and the storage directory is / opt/oracle/oradata/orcl/redo02.log.

5) View instance information through v$instance view

As shown in the figure above, the instance name is orcl, the hostname is oracleserver, the version number is 11.2.0.1.0, the instance startup time is December 14, 2016, and the status is allowed.

6) View the information of the current database

The database, named orcl, was created on December 14, 2016 and is running in archive mode.

The dynamic data dictionary view well reflects the running state information of the current database and provides information support for database performance tuning and judging the bottleneck of the system. You can also view information about control files, data files, and tablespaces through the dynamic data dictionary view.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.