Oracle daily Patrol ____oracle

Patrol content 1. Check database basics In this section, the basic state of the database is examined primarily, including: checking Oracle instance status, checking Oracle service processes, and checking the Oracle Listener process for a total portion. 1.1. Check Oracle Instance status sql> select Instance_name,host_name,startup_time,status,database_status from V$instance; instance_name host_name startup_time STATUS database_status------------------------------------------------------- ----------
CkDB AS14 2009-5-7 9:3 OPEN ACTIVE where "status" indicates the current instance state of Oracle, must be "OPEN", "Database_status" indicates the status of the Oracle current database and must be active. Sql> select Name,log_mode,open_mode from V$database; NAME log_mode open_mode--------------------------------------ckdb archivelog READ WRITE where "Log_mode" represents Oracle's current archiving method. "Archivelog" means that the database is running in archive mode and "Noarchivelog" means that the database is running in non-archive mode. In our system the database must be running in the archive mode. 1.2. Check Oracle service process $ps-ef|grep ora_|grep-v grep&&ps-ef|grep ora_|grep-v grep|wc-l Oracle 2960 1 0 May07? 00:01:02 ora_pmon_ckdb Oracle 2962 1 0 May07? 00:00:22 ora_psp0_ckdb Oracle 2964 1 0 May07? 00:00:00 ora_mman_ckdb Oracle 2966 1 0 May07? 00:03:20 ora_dbw0_ckdb Oracle 2968 1 0 May07? 00:04:29 ora_lgwr_ckdb Oracle 2970 1 0 May07? 00:10:31 ora_ckpt_ckdb Oracle 2972 1 0 May07? 00:03:45 ora_smon_ckdb Oracle 2974 1 0 May07? 00:00:00 ora_reco_ckdb Oracle 2976 1 0 May07? 00:01:24 ora_cjq0_ckdb Oracle 2978 1 0 May07? 00:06:17 ora_mmon_ckdb Oracle 2980 1 0 May07? 00:07:26 ora_mmnl_ckdb Oracle 2982 1 0 May07? 00:00:00 ora_d000_ckdb Oracle 2984 1 0 May07? 00:00:00 ora_s000_ckdb Oracle 2994 1 0 May07? 00:00:28 ora_arc0_ckdb Oracle 2996 1 0 May07? 00:00:29 ORA_ARC1_CKDB Oracle 3000 1 0 May07? 00:00:00 ora_qmnc_ckdb Oracle 3625 1 0 May07? 00:01:40 ora_q000_ckdb Oracle 31594 1 0 Jul20? 00:00:00 ora_q003_ckdb Oracle 23802 1 0 05:09? 00:00:33 ORA_J000_CKDB 19 After checking Oracle's process command output, the output display should include at least some of the following processes:. Oracle writes the data file the process, the output displays as: "Ora_dbw0_ckdb". Oracle writes the log file process, the output is displayed as: "Ora_lgwr_ ckdb". Oracle listens for the process of instance state, the output is shown as: "Ora_smon_ ckdb". Oracle listens for the process state of the client connection process, and the output appears as: "Ora_pmon_ ckdb". Oracle is archiving the process, the output is displayed as: "Ora_arc0_ ckdb". Oracle checkpoint process with output displayed as: "Ora_ckpt_ ckdb". Oracle recovery process, output displayed as: "Ora_reco_ ckdb"
1.3. Check Oracle Listening status/home/oracle>lsnrctl status lsnrctl for Linux:version 10.2.0.2.0-production on 23-jul-2009 14:11: Copyright (c) 1991, +, Oracle. All rights reserved. Connecting to (Address= (PROTOCOL=TCP) (host=) (port=1521)) STATUS of the LISTENER------------------------Alias LISTENER Version Tnslsnr for linux:version 10.2.0.2.0-production Start Date 07-may-2009 09:35:52 Uptime 4 hr. Min. 0 SEC Trace level off security on:local OS authentication SNMP off Listener Parameter FILE/DATA/ORACLE/PRODUCT/10.2.0/NETW Ork/admin/listener.ora listener Log file/data/oracle/product/10.2.0/network/log/listener.log Listening Endpoints Summary ... (Description= (address= (protocol=tcp) (HOST=AS14) (port=1521)) Services Summary ... Service "CKDB" has 1 instance (s). Instance "CkDB", Status READY, has 1 handler (s) for the This service ... Service "Ckdbxdb" has 1 instance (s). Instance "CkDB", Status READY, has 1 handler (s) for the This service ... Service "CKDB_XPT" has 1 instance (s). InstanCE "CkDB", Status READY, has 1 handler (s) for the This service ... The command completed successfully "Services Summary" item indicates which database instances are being monitored by the Oracle Listener process, and should be at least "ckdb" in the output display. Check if the listener process exists: [oracle@as14 ~]$ ps-ef|grep lsn|grep-v grep oracle 2954 1 0 May07? 00:01:17/data/oracle/product/10.2.0/bin/tnslsnr Listener-inherit 2. Check system and Oracle log files in this section, check the relevant log files, including: Check the operating system log files, check the Oracle log files, check the Oracle Core dump directory, check the root and Oracle users email, a total of four parts.
2.1. Check the operating system log file # Cat/var/log/messages |grep failed to see if an error message is associated with an Oracle user. 2.2. Check Oracle log files [oracle@as14 ~]$ cat/data/oracle/admin/ckdb/bdump/alert_ckdb.log |grep ora-[oracle@as14 ~]$ Cat/data /oracle/admin/ckdb/bdump/alert_ckdb.log |grep Err [oracle@as14 ~]$ cat/data/oracle/admin/ckdb/bdump/alert_ckdb.log | grep fail Oracle will record some of the database operations in the warning log file (alert_sid.log) during Runtime: Database startup, shutdown, Non-default parameters at startup, database redo log switching, record time for each switch, and if because of checkpoints ( Checkpoint) operation does not perform the completion of the cause can not switch, records can not switch reasons; some operations on the database, such as creating or deleting tablespaces, adding data files, database errors such as insufficient tablespaces, bad blocks, database internal errors (ORA-600), etc. Regularly check log files, according to the problems found in the log in a timely manner: problem handling startup parameter not check initialization parameter file The redo log cannot be switched because the checkpoint operation or the archive operation is not complete if this happens frequently, consider increasing the redo log filegroup ; Find ways to improve the efficiency of checkpoints or archival operations; human unauthorized deletion of tablespace check database security issues, whether the password is too simple, if necessary, undo some user's system permissions appear bad block check whether it is a hardware problem (such as disk native bad block), if not, check that the database object appears a bad block, There is not enough space to rebuild this object the data file to the corresponding table space appears ORA-600 according to the contents of the log file to view the corresponding TRC file, if it is an ORACLE bug, to timely play the corresponding patch listener log: $ORACLE _home/ Network/log 2.3. Check the ORACLE core dump directory $ls $ORACLE _base/admin/ckdb/cdump/*.trc|wc-l $ls $ORACLE _base/admin/ckdb/udump/*.trc|wc-l If the results of the above command are growing every day,Indicates that the Oracle process often occurs with a core dump. This indicates that some user processes or database daemon exits unexpectedly because of the inability to handle them. Frequent core dumps, especially the core dumps of database daemon processes, cause the database to terminate abnormally. 2.4. Check email for root and Oracle users #tail-n 200/var/mail/root #tail-n 200/var/mail/oracle View no Oracle user-related error messages. ORACLE OCM Master QQ Group: 2878026 190467784 Welcome Senior DBA Network Cooperation 3. Check Oracle Object status in this section, check the status of the related Oracle objects, include: Check Oracle control file status, check Oracle online log status, check Oracle tablespace status, check Oracle data file status, check Oracle table, index , stored procedures, triggers, packages, and so on, to check the status of the Oracle rollback segment, a total of six parts. 3.1. Check Oracle Control File status sql> select Status,name from V$controlfile; STATUS NAME---------------------------------------------------------------------------------------/data/oradata /ckdb/control01.ctl/data/oradata/ckdb/control02.ctl/data/oradata/ckdb/control03.ctl output should be more than 3 (including 3 records, "STATUS" should be empty. The status is null to indicate that the control file status is normal. 3.2. Check Oracle Online log status sql> select Group#,status,type,member from V$logfile; group# STATUS TYPE member-----------------------------------3 online/data/oradata/ckdb/redo03.log 2 Online/data/ora Data/ckdb/redo02.log 1 Online/data/oradata/ckdb/redo01.log 4 Online/data/oradaTa/ckdb/redo04.log 5 Online/data/oradata/ckdb/redo05.log 6 online/data/oradata/ckdb/redo06.log 6 rows selected Output should be more than 3 (including 3 records, "STATUS" should be Not "INVALID", not "DELETED". Note: the "STATUS" Display as null indicates normal. 3.3. Check the status of the Oracle tablespace sql> select Tablespace_name,status from Dba_tablespaces; Tablespace_name STATUS---------------------------------------SYSTEM online UNDOTBS1 online sysaux online TEMP online US ERS online SJ1 online adm_index online home_data online home_index online photo_data online photo_index online .... The status should all be online in the output result. 3.4. Check Oracle Data File status sql> select Name,status from V$datafile; NAME STATUS---------------------------------------------------------/data/oradata/ckdb/system01.dbf system/data/ ORADATA/CKDB/UNDOTBS01.DBF online/data/oradata/ckdb/sysaux01.dbf online/data/oradata/ckdb/users01.dbf Online/data /ORADATA/CKDB/SJ.DBF online/data/oradata/ckdb/home_data1.dbf online/data/oradata/ckdb/home_index1.dbf Online/data /ORADATA/CKDB/PHOTO_DATA1.DBF online/data/oradata/ckdb/pHOTO_INDEX1.DBF online/data/oradata/ckdb/blog_data1.dbf online/data/oradata/ckdb/blog_index1.dbf online/data/ ORADATA/CKDB/AUDIO_DATA1.DBF online/data/oradata/ckdb/audio_index1.dbf online/data/oradata/ckdb/video_data1.dbf ONLINE/DATA/ORADATA/CKDB/VIDEO_INDEX1.DBF online/data/oradata/ckdb/sys_data1.dbf Online/data/oradata/ckdb/sys_ INDEX1.DBF online/data/oradata/ckdb/adm_data1.dbf online/data/oradata/ckdb/adm_index1.dbf online/data/oradata/ CKDB/PERFSTAT.DBF Online output results in the "STATUS" should be "online." Or: sql> select File_name,status from Dba_data_files; file_name STATUS------------------------------------------------------/data/oradata/ckdb/users01.dbf AVAILABLE ORACLE OCM Master QQ Group: 2878026 190467784 Welcome to Senior DBA Network cooperation/DATA/ORADATA/CKDB/SYSAUX01.DBF AVAILABLE/DATA/ORADATA/CKDB/UNDOTBS01 . dbf available/data/oradata/ckdb/system01.dbf available/data/oradata/ckdb/sj.dbf available/data/oradata/ckdb/ PERFSTAT.DBF available/data/oradata/ckdb/home_data1.dbf available/data/oradata/ckdb/home_inDEX1.DBF available/data/oradata/ckdb/photo_data1.dbf AVAILABLE output results, "STATUS" should be "AVAILABLE".
3.5. Check for invalid object Sql>select Owner,object_name,object_type from dba_objects where status!= ' VALID ' and owner!= ' SYS ' and owner != ' SYSTEM '; No rows selected if the record returns, there is an invalid object. If these objects are related to the application, then the object needs to be recompiled or: SELECT owner, object_name, object_type from dba_objects where status= ' INVALID ';
3.6. Check the rollback segment status Sql> select Segment_name,status from Dba_rollback_segs; Segment_name STATUS----------------------------------------------SYSTEM online _syssmu1$ online _syssmu2$ Online _ syssmu3$ online _syssmu4$ online _syssmu5$ online _syssmu6$ online _syssmu7$ online _syssmu8$ ONLINE _syssmu9$ online _sys smu10$ Online One rows selected the "STATUS" of the rollback segment in the output result should be "ONLINE".
4. Check the use of Oracle-related resources in this section, the main review of Oracle-related resource usage, including: Check Oracle initialization file related parameter values, check database connectivity, check system disk space, check Oracle table space usage, Examine the objects of the extended exception, examine the contents of the system table space, and examine the maximum extended value of the object's next extension to the table space, totalling seven parts.
4.1. Check the associated parameter values in the Oracle initialization file sql> Select Resource_name,max_utilization,initial_allocation, limit_value from V$resource _limit; Resource_name max_utilization initial_allocat limit_value-------------------------------------------------------- ---------processes 162 Sessions 555 555 Enqueue_locks 136 6930 6930 enqueue_resources a 2660 unlimited Ges_ Procs 0 0 0 ges_ress 0 0 Unlimited ges_locks 0 0 Unlimited ges_cache_ress 0 0 Unlimited ges_reg_msgs 0 0 UNLIM