Complete manual for daily maintenance of Oracle DBA database

During the Oracle database operation, the DBA should monitor the database's running log and table space usage, and discover the problems in the database as early as possible.

Oracle Warning log file monitoring

Oracle will log some of the database operations in the warning log file (alert_sid.log) during the run:

The database is started, shut down, not default parameter when starting;

Database redo log switching, recording the time of each switch, and if because the checkpoint (checkpoint) operation did not perform the completion of the switch, will record the reason for not switching;

Some operations on the database, such as creating or deleting table spaces, adding data files;

Database errors such as insufficient table space, bad blocks, internal database errors (ORA-600).

The DBA should periodically check the log files and process them according to the problems found in the logs.

Problem handling:

The startup parameter does not check the initialization parameter file;

Redo logs cannot be switched because the checkpoint operation or archive operation is not complete if this happens frequently, consider adding a redo log filegroup; Try to improve the efficiency of the checkpoint or archive operation;

Unauthorized deletion of table space check the database security issue, whether the password is too simple, if necessary, revoke some user's system permissions;

A bad block check is a hardware problem (such as a disk has a bad block), if not, check that the database object appears a bad block, to reconstruct the object;

The table space is not enough to increase the data file to the corresponding table space;

Appear ORA-600 according to the contents of the log file to see the corresponding TRC file, if it is an Oracle bug, to timely play the corresponding patch.

Second, the use of database table space Monitoring (Dictionary management table space)

After the database has been running for some time, due to the constant creation and deletion of objects on the tablespace, a large amount of fragmentation is generated on the tablespace, and the DBA should be aware of the fragmentation and free space in the tablespace to determine whether to defragment or add data files to the tablespace. The following are the referenced contents:

Select Tablespace_name, 
count (*) chunks, 
max (bytes/1024/1024) max_chunk from 
dba_free_space 
Group by Tablespace_name; 
The above SQL lists the free blocks of each table space in the database, as follows: The following is a reference:
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

Where the chunks column represents how many free blocks are available in the tablespace (each free block is made up of successive Oracle blocks), and if there are too many idle blocks, such as an average of more than 100 on each data file, the fragmentation of the tablespace is more serious. You can try the following SQL command to engage adjacent fragments of a table space:

alter tablespace table space name coalesce;

Then execute the SQL statement that looks at the table space fragment to see if there is any reduction in the fragmentation of the tablespace. If there is no effect, and the fragmentation of the tablespace has seriously affected the operation of the database, consider rebuilding the table space.

The result of the Max_chunk column is the largest available block size on the table space, and if the object on the table space needs to allocate more space (next value) than the available block size, the ORA-1652, ORA-1653, ORA-1654 error messages are prompted. DBAs should expand the space in the tablespace in time to avoid these errors.

The expansion of the table space expands the size of the data file in the table space, or adds data files to the table space, as shown in the Storage Management section.

Third, view the connection of the database

The DBA will periodically check the connection to the database to see if the number of sessions established with the database is normal, and if too many connections are established, the resources of the database are consumed. At the same time, some "hang dead" connections may require manual cleanup by the DBA.

The following SQL statement lists the sessions established by the current database: The following are references:

Select Sid,serial#,username,program,machine,status from 
v$session; 
The output is: The following is the referenced content:
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 SYS sqlplus. EXE Workgroup\work3 ACTIVE One 
      5 dbsnmp dbsnmp.exe Workgroup\work3 INACTIVE

Comments:

The ID number of the SID conversation (session);

The serial number of the serial# session, together with the SID, to uniquely identify a session;

USERNAME the user name to establish the session;

Program This session is using what tools to connect to the database;

Status of the current session, active indicates that the session is performing certain tasks, inactive that the current session is not performing any action.

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

Alter system kill session ' sid,serial# ';

Note that in the example above, a session with Sid 1 to 7 (username column null) is a background process for Oracle, and do not take any action on those sessions.