Oracle maintenance: Daily Work

Check database status

Check that all INSTANCE statuses and listener statuses are normal. log on to all databases or routines and check the ORACLE background processes:

$ Ps-ef | grep ora

$ Lsnrctl status

SQL> select status from v $ instance;

Check the file system

If the remaining space of the file system is too small or increases rapidly, you need to confirm and delete unnecessary files to release the space.

$ Df-k

$ Df-h

Check the warning log file (alert_SID.log)

During the running process of Oracle, some running conditions of the database are recorded in the warning log file (alert_SID.log): the database is started and closed, and non-default parameters are used during startup; 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 table space, Bad blocks, internal database errors (ORA-600) regular check of log files, according to the problems found in the log for timely processing

Problem	Processing
The startup parameter is incorrect.	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 increase the redo log file group and find a way to improve the efficiency of checkpoint or archive operations;
The tablespace is deleted without authorization.	Check whether the password is too simple due to database security issues. If necessary, revoke the system permissions of some users.
Bad blocks	Check whether it is a hardware problem (such as a disk with a bad block). If not, check that the database object has a bad block and recreate the object.
Insufficient tablespace	Add data files to the corresponding tablespace
ORA-600 appears	View the corresponding TRC File Based on the log file content. If it is an Oracle bug, install the appropriate patch in time.

Alert_SID.log is usually in the $ ORACLE_BASE/admin/<SID>/bdump directory.

Run the Unix 'tail' or more command to view the log information. (to facilitate the query, the queried log is saved to the bak directory in the same directory as the log );

Regularly check warning logs, TRC files, and listener logs

If these logs are large and occupy a large amount of resource space, you can delete them regularly to release resources.

Normally, the related directories of these files exist:

Warning log: $ ORACLE_BASE/admin/<SID>/bdump

Or

Use the SQL> show parameter background_dump_dest parameter to view the storage location;

Trc file: $ ORACLE_BASE/admin/<SID>/udump

Or

Use the SQL> show parameter user_dump_dest parameter to view the storage location;

Listener log: $ ORACLE_HOME/network/log

View database connection information

Check the database connection status regularly 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. In addition, some "dead" connections may need to be manually cleared.

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

Select count (*) from v $ session;-view the number of current session connections

Select sid, serial #, username, program, machine, status from v $ session;

Sid serial # USERNAME PROGRAM MACHINE STATUS

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

1 3 oracle @ xz15saledb (PMON) xz15saledb ACTIVE

2 3 oracle @ xz15saledb (DBW0) xz15saledb ACTIVE

3 3 oracle @ xz15saledb (DBW1) xz15saledb ACTIVE

4 3 oracle @ xz15saledb (LGWR) xz15saledb ACTIVE

5 3 oracle @ xz15saledb (CKPT) xz15saledb ACTIVE

6 3 oracle @ xz15saledb (SMON) xz15saledb ACTIVE

7 3 oracle @ xz15saledb (RECO) xz15saledb ACTIVE

8 1 oracle @ xz15saledb (CJQ0) xz15saledb ACTIVE

9 3 oracle @ xz15saledb (ARC0) xz15saledb ACTIVE

10 3 oracle @ xz15saledb (ARC1) xz15saledb ACTIVE

11 11319 ZK AccPrtInv_svr @ xz15tuxedo2 (TNS V1-V3) xz15tuxedo2 INACTIVE

13 48876 ZG upload @ xz15saleap (TNS V1-V3) xz15saleap INACTIVE

17 20405 ZK AccCreateRpt @ xz15tuxedo1 (TNS V1-V3) xz15tuxedo1 INACTIVE

20 12895 ZK OweScanSvr @ xz15billdb (TNS V1-V3) xz15billdb 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 the following command: (this method is generally not recommended to kill the database connection, so that sometimes the session will not be disconnected. It is easy to cause dead connections. We recommend that you use the sid to check the spid of the operating system and use ps-ef | grep spidno to confirm that spid is not the background process of ORACLE. Use the kill-9 command of the operating system to kill the connection)

Alter system kill session 'sid, SERIAL #';

Note:

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

Check the validity of the database backup on the current day.

RMAN backup method: Check the backup logs of a third-party backup tool to determine whether the backup is successful.

EXPORT backup mode: Check the exp log file to determine whether the backup is successful

For other backup methods: Check the corresponding log files

Check the status of the data file

SQL> select file_name, status from dba_data_files;

SQL> select count (*) from dba_data_files;

SQL> select count (*) from dba_data_files where status = 'available ';

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.

Check the space usage

Select

F. tablespace_name,

A. total,

F. free,

Round (f. free/a. total) * 100) "% Free"

From

(Select tablespace_name, sum (bytes/(1024*1024) total from dba_data_files group by tablespace_name),

(Select tablespace_name, round (sum (bytes/(1024*1024) free from dba_free_space group by tablespace_name) f

WHERE a. tablespace_name = f. tablespace_name (+)

Order by "% Free"

Check database wait events

Set pages 80

Set lines 120

Col event for a40

Select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT from v $ session_wait where event not like 'SQL %' and event not like 'rdbms %'

If the database has been waiting for events such as latch free, enqueue, buffer busy waits, db file sequential read, and db file scattered read for a long time, you need to analyze them, the statement may be faulty.

Troubleshooting of table lock problems

Query the current Lock Object Information. For more details about how to handle the lock, see the script used in work.

Col sid for 999999

Col username for a10

Col schemaname for a10

Col osuser for a16

Col machine for a16

Col terminal for a20

Col owner for a10

Col object_name for a30

Col object_type for a10

Select sid, serial #, username, SCHEMANAME, osuser, MACHINE,

Terminal, PROGRAM, owner, object_name, object_type, o. object_id

From dba_objects o, v $ locked_object l, v $ session s

Where o. object_id = l. object_id and s. sid = l. session_id;

Unlock handling:

Alter system kill session '& sid, & serial #';

Check the database performance, and record the cpu usage, IO, buffer hit rate, and so on of the database.

Use commands such as vmstat, iostat, sar, and top to collect and check the information to determine resource usage.

Check whether there are dead processes

Select spid from v $ process where addr not in (select paddr from v $ session );

Some botnets are blocking other services.