Routine Oracle Database Maintenance Manual

InOracle DatabaseDuring the operation, DBA should monitor the database running logs and table space usage, and detect problems in the database as soon as possible. The routine maintenance of Oracle databases is also very important. The following section describes the routine maintenance of Oracle databases. The content described below is very useful to database administrators.

1. Oracle warning Log File monitoring

Oracle records the running status of the database in the warning log file (alert_SID.log) during running:

Non-default parameter for database startup and shutdown;

Switchover of database redo logs, recording the time of each switchover, and recording the reason why the switchover fails because the checkpoint operation is not completed;

Some operations on the database, such as creating or deleting tablespaces and adding data files;

Database errors, such as insufficient tablespace, Bad blocks, internal database errors (ORA-600)

DBA should regularly check log files and handle problems found in logs in a timely manner

Troubleshooting

The startup parameter does not check the initialization parameter file

The redo log cannot be switched because the checkpoint operation or archive operation is not completed. If this happens frequently, you can consider adding a redo log file group. You can find a way to improve the efficiency of the checkpoint or archive operation;

The tablespace has been deleted without authorization to check the database security. The password is too simple. If necessary, revoke the system permissions of some users.

Check whether the bad block is a hardware problem (for example, if the disk has a bad block). If not, check that the database object has a bad block and recreate the object.

The tablespace is not enough to add data files to the corresponding tablespace.

The ORA-600 appears according to the contents of the log file to view the corresponding TRC file, if it is Oracle bug, to timely add the corresponding patch

Ii. Monitoring of database tablespace usage (dictionary management tablespace)

After the database has been running for a period of time, the constant creation and deletion of objects in the tablespace will generate a large number of fragments in the tablespace. the DBA should know the shards and available space in a timely manner, to determine whether to organize shards or add data files to the tablespace.

Select tablespace_name,

Count (*) chunks,

Max (bytes/1024/1024) max_chunk

From dba_free_space

Group by tablespace_name;

The preceding SQL statement lists the idle blocks of each tablespace in the database, as shown below:

TABLESPACE_NAME CHUNKS MAX_CHUNK

----------------------------------------

INDX 1 57.9921875

RBS 3 490.992188

RMAN_TS 1 16.515625

SYSTEM 1 207.296875

TEMP 20 70.8046875

TOOLS 1 11.8359375

USERS 67 71.3671875

The CHUNKS column indicates the number of available idle blocks in the tablespace (each idle block is composed of several consecutive Oracle data blocks). If there are too many idle blocks, for example, if there are more than 100 data files on average, the fragmentation of the tablespace is serious. You can use the following SQL command to join adjacent fragments of the tablespace:

Alter tablespace name coalesce;

Then execute the SQL statement to check whether the table space fragments have been reduced. If there is no effect, and the tablespace fragments have seriously affected the operation of the database, the tablespace should be rebuilt.

The result of the MAX_CHUNK column is the maximum available block size on the tablespace. If the space (NEXT value) to be allocated for the objects in the tablespace is greater than the available block size, an error message is prompted for the ORA-1652, ORA-1653, and ORA-1654. DBA should promptly expand the table space to avoid these errors.

Expand the table space to expand the data file size of the table space, or add data files to the tablespace. For details, see "Storage Management.

Iii. view database connections

DBAs should regularly check the database connection to check whether the number of sessions established with the database is normal. If too many connections are established, the database resources will be consumed. At the same time, DBA may need to manually clean up some "dead" connections.

The following SQL statements List the sessions created by the current database:

Select sid, serial #, username, program, machine, status

From v $ session;

Output result:

Sid serial # USERNAME PROGRAM MACHINE STATUS

-------------------------------------------------------

1 1 ORACLE. EXE WORK3 ACTIVE

2 1 ORACLE. EXE WORK3 ACTIVE

3 1 ORACLE. EXE WORK3 ACTIVE

4 1 ORACLE. EXE WORK3 ACTIVE

5 3 ORACLE. EXE WORK3 ACTIVE

6 1 ORACLE. EXE WORK3 ACTIVE

7 1 ORACLE. EXE WORK3 ACTIVE

8 27 sys sqlplus. EXE WORKGROUPWORK3 ACTIVE

11 5 DBSNMP dbsnmp.exe WORKGROUPWORK3 INACTIVE

Where,

ID of the SID session (session;

SERIAL # session SERIAL number, which is used together with SID to uniquely identify a session;

USERNAME: the user name used to establish the session;

What tool is used to connect to the database using the PROGRAM session;

STATUS: the STATUS of the current session. ACTIVE indicates that the session is executing some tasks. INACTIVE indicates that the current session has not executed any operations;

If the DBA wants to manually disconnect a session, execute:

Alter system kill session 'sid, SERIAL #';

Note: In the preceding example, sessions with SID ranging from 1 to 7 (the USERNAME column is empty) are the background processes of Oracle. do not perform any operations on these sessions.

Iv. control file backup

When the database structure changes, such operations as adding tablespaces, adding data files, or redoing log files will change the control file of the Oracle database, DBAs should back up the control files as follows:

Execute the SQL statement:

Alter database

Backup controlfile to '/home/backup/control. Bak ';

Or:

Alter database

Backup controlfile to trace;

In this way, the SQL command for creating the control file will be generated in the directory USER_DUMP_DEST (specified in the initialization parameter file.

5. Check the database file status

DBAs should promptly check the status of data files in the database (such as deleted by mistake) and decide how to process the data files according to the actual situation. The SQL statement used to check the status of the data files is as follows:

Select file_name, status

From dba_data_files;

If the STATUS column of the data file is not AVAILABLE, you must take appropriate measures, such as restoring the data file or recreating the tablespace where the data file is located.

The routine maintenance of Oracle databases is very important. Only after routine maintenance of Oracle databases is completed can the Oracle database work properly, I hope that you will be able to master the daily maintenance of Oracle databases after learning above, so that you can easily solve similar problems in your future work.