Routine Oracle Database Maintenance Manual

Routine Oracle Database Maintenance Manual

When the Oracle database is running, the DBA should monitor the database running logs and table space usage to detect database problems as early as possible.

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