Oracle database Patrol

Select Inst_id,status,count (*) from Gv$session GROUP by inst_id,status Order by status;

$ORACLE _home\rdbms\admin\spreport.sql

Oracle database Patrol

First, the operating system check

1. Operating system Resource Usage Top/topas/iostat/vmstat focus on cpu/memory/swap usage

2. Operating system space usage df-h/-g focus on database directory space (DU-SH *) (DU-SM *)

3. Operating system alarm Log

Hp:cat/var/adm/syslog/syslog.log

Aix:errpt | More

Linux:cat/var/log/messages

Sun:cat/var/log/messages

Windows: Event Viewer to view event content

4.uptime See if the operating system has rebooted especially if the alert log for restart focus check grid is identified for the reason

Second, cluster inspection

1. Check the cluster status crs_stat-t

2. Check the status of the database in the cluster CRSCTL status res-t

3. Check the service status (Grid:lsnrctl status), such as floating on a reload to the corresponding node (srvctl-h)

Srvctl relocate service-d <db_unique_name>-s <service_name> {i <old_inst_name>-t <new_inst_name > | -C <current_node>-n <target_node>} [-F]

4. Check cluster alert log 10g: $CRS _home/log/{hostname}/alert{hostname}.log11g: $ORACLE _home/log/{hostname}/alert{hostname}. Log

5.ASM disk space is normal

Select Name,total_mb,free_mb from V$asm_diskgroup;

6. Check the disk status

Select Name,path,state,mode_status from V$asm_disk;

Third, database inspection

1. Use of the key table space to ensure that the usage rate is within 85%

2. Resource Usage select * from V$resource_limit; (set linesize 300;set pagesize 300;)

3. Flash Back Usage

Select flashback_on from V$database;

Query view V$recovery_file_dest view to see its location and maximum size

SELECT * from V$recovery_file_dest;

Use V$flash_recovery_area_usage to determine file usage details in the Flashback recovery area

SELECT * from V$flash_recovery_area_usage;

4.11g New Customers Check if the new feature is off

Whether to open audit show parameter Audit_trail (db on none off)

If asked if there is a concession request can be closed

Alter system set Audit_trail=none scope=spfile sid= ' * ';

TRUNCATE TABLE sys.aud$;

Select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 MB from dba_segments where SEGMENT_TYPE= ' TABLE ' and segment_name= ' aud$ ';

5. Clean up the Listening log (win: > Listener.log server R2:echo "" > Log.xml)

10g and 10g ago: $ORACLE _home/network/log

11g later: $ORACLE _base/diag/tnslsnr/{hostname}/listener/trace

Lsnrctl Status View listening log directory

10g: $ORACLE _home/network/admin/log/11g:/oracle/gridbase/diag/tnslsnr/s1-11g/listener/alert/log.xml

Lsnrctl> Set

Lsnrctl> Set Current_listener Listener

Current Listener is Listener

Lsnrctl> set Log_status off

Lsnrctl> set Log_status on

Lsnrctl> exit

11g Listener_scan1

Lsnrctl> Set Current_listener listener_scan1

6. Check the database alarm log

10g ago: $ORACLE _base/admin/{sid}/bdump

D:\oracle\product\10.2.0\admin\{sid}\bdump

11g: $ORACLE _base/diag/rdbms/{db_name}/{sid}/trace

7. View Database Wait Events

--View the waiting events for the overall database

Select Event,count (*) from the gv$session_wait where wait_class<> ' Idle ' Group by event;

8. Collect AWR reports

(1) Get the AWR report

sql>@/rdbms/admin/awrrpt.sql (Exit Sqlplus to see the AWR report)

Ask the customer for the peak time and intercept the 1-2-hour report.

Iv. Backup Check

1. Determine if there is a logical or physical backup and view the backup status (check the backup log)

2. If there is Dbra or DG need to check the synchronization situation

Chezi log to see if the repository is properly applied

Select Thread#,max (sequence#) from V$archived_log Group by thread#; Host

Select Thread#,max (sequence#) from V$archived_log where applied= ' YES ' GROUP by thread#; Standby Library

Select Thread#,max (sequence#) from V$archived_log Group by thread#; Standby Library

Spare Space Check

3.ogg Synchronization Status Check

[Email protected] odc12]$ Ps-ef | grep Mgr

Oracle 8492 1 0 Mar17 00:02:29./mgr PARAMFILE/ODC12/DIRPRM/MGR.PRM reportfile/odc12/dirrpt/mgr.rpt PRO Cessid MGR Usesubdirs

Oracle 26085 1 0 Mar22 00:00:15./mgr PARAMFILE/ODC/DIRPRM/MGR.PRM reportfile/odc/dirrpt/mgr.rpt PROCESS ID MGR PORT 7809

Cd/odc12

./ggsci

Info All

Ggserr.log

Five, run patrol script

Vi. Information Collection and packaging

1. Operating system logs

2. Cluster alert log grid user (/oracle/grid/crs_1 ($ORACLE _home)/LOG/ZJRAC1)

3. Database Alert Log

4. Patrol script output content

Mkdir

Table Space Check

Set Feed off

Set Linesize 100

Set PageSize 200

Column "Used (MB)" format A10

Column "Free (MB)" format A10

Column "Total (MB)" format A10

Column Per_free format A10

--spool Tablespace.alert

SELECT F.tablespace_name,

To_char ((t.total_space-f.free_space), ' 999,999 ') "Used (MB)",

To_char (f.free_space, ' 999,999 ') "Free (MB)",

To_char (t.total_space, ' 999,999 ') "Total (MB)",

To_char ((ROUND ((f.free_space/t.total_space) *100), ' 999 ') | | '% ' per_free,

To_char (t.max_space, ' 999,999 ') "Maxextend (MB)"

From (

SELECT Tablespace_name,

ROUND (SUM (blocks* (SELECT value/1024

From V$parameter

WHERE NAME = ' db_block_size ')/1024)

) free_space

From Dba_free_space

GROUP by Tablespace_name

F

(

SELECT Tablespace_name,

ROUND (SUM (bytes/1048576)) Total_space,

ROUND (SUM (maxbytes/1048576)) Max_space

From Dba_data_files

GROUP by Tablespace_name

) T

WHERE F.tablespace_name = T.tablespace_name

ORDER BY per_free ASC;

Table Space:

Select Tpsname,status,mgr,maxsize,c_userd,max_used from (

SELECT d.tablespace_name tpsname,d.status Status,

D.segment_space_management Mgr, d.contents type,

To_char (NVL (Trunc (a.maxbytes/1024/1024), 0), ' 99g999g990 ') maxsize,

To_char (NVL (A.BYTES-NVL (f.bytes, 0)/a.bytes * 0), ' 990d00 ') C_userd,

To_char (NVL (A.BYTES-NVL (f.bytes, 0)/a.maxbytes * 0), ' 990d00 ') max_used

From Sys.dba_tablespaces D,

(SELECT tablespace_name,sum (bytes) bytes,sum (case autoextensible if ' NO ' then bytes when ' YES ' then maxbytes else nul L end) MaxBytes from Dba_data_files GROUP by Tablespace_name) A,

(SELECT tablespace_name,sum (bytes) bytes, MAX (bytes) Largest_free from Dba_free_space GROUP by Tablespace_name) F

WHERE d.tablespace_name = a.tablespace_name and D.tablespace_name = F.tablespace_name (+)

)

where max_used>80

ORDER BY max_used Desc;

Other:

Sql> select file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from Dba_data_files;

Oracle database Patrol