ORACLE Alert

select file_name from dba_data_files; ------------------------------------- column file_name format a40 column tablespace format a15 column bytes format 999,999,999 select file_name, t.tablespace_name tablespace, bytes from dba_data_files d, dba_tablespaces t where t.tablespace_name = d.tablespace_name order by BYTES desc; ----------------------------------- alter system set db_create_file_dest='C:\ORA\PRODUCT\10.2.0\DB_1\ORADATA\ORCL\'; create tablespace test;
show parameter background_dump --alert logların yeri

Controlling the Size of Trace Files --MAX_DUMP_FILE_SIZE


ALTER SESSION SET SQL_TRACE TRUE;  -performans kaybına neden olabilir. defaultta false

Use the DBMS_SESSION or the DBMS_MONITOR packages if you want to control SQL tracing for a session.

DBA_THRESHOLDS;


SELECT metrics_name, warning_value, critical_value, consecutive_occurrences
FROM DBA_THRESHOLDS
WHERE metrics_name LIKE '%CPU Time%';

View	Description
DBA_THRESHOLDS	Lists the threshold settings defined for the instance
DBA_OUTSTANDING_ALERTS	Describes the outstanding alerts in the database
DBA_ALERT_HISTORY	Lists a history of alerts that have been cleared
V$ALERT_TYPES	Provides information such as group and type for each alert
V$METRICNAME	Contains the names, identifiers, and other information about the system metrics
V$METRIC	Contains system-level metric values
V$METRIC_HISTORY	Contains a history of system-level metric values

Monitoring Lock;

View	Description
V$LOCK	Lists the locks currently held by Oracle Database and outstanding requests for a lock or latch
DBA_BLOCKERS	Displays a session if it is holding a lock on an object for which another session is waiting
DBA_WAITERS	Displays a session if it is waiting for a locked object
DBA_DDL_LOCKS	Lists all DDL locks held in the database and all outstanding requests for a DDL lock
DBA_DML_LOCKS	Lists all DML locks held in the database and all outstanding requests for a DML lock
DBA_LOCK	Lists all locks or latches held in the database and all outstanding requests for a lock or latch
DBA_LOCK_INTERNAL	Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch

Process and Session Views;

View	Description
V$PROCESS	Contains information about the currently active processes
V$LOCKED_OBJECT	Lists all locks acquired by every transaction on the system
V$SESSION	Lists session information for each current session
V$SESS_IO	Contains I/O statistics for each user session
V$SESSION_LONGOPS	Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.
V$SESSION_WAIT	Lists the resources or events for which active sessions are waiting
V$SYSSTAT	Contains session statistics
V$RESOURCE_LIMIT	Provides information about current and maximum global resource utilization for some system resources
V$SQLAREA	Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution
V$LATCH	Contains statistics for nonparent latches and summary statistics for parent latches

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

CPU Alerts

CPU Overload Alert – We use vmstat to monitor for high run queue values and track periods when the Oracle database server is overloaded.

----------------------
display the number of CPUs on their Oracle server:
  

§ Linux Command:

cat /proc/cpuinfo|grep processor|wc –l

§ Solaris Command:

psrinfo -v|grep "Status of processor"|wc –l

§ AIX Command:

lsdev -C|grep Process|wc –l

§ HP/UX Command:

ioscan -C processor | grep processor | wc -l

win; tasklist /S %computername% /SVC /FO CSV > C:\tasklist.csv

RAM Alerts

High RAM Page and scan rate – In UNIX, we constantly monitor for RAM memory high page-in values and high scan rates. overloads and provide our clients with complete reports showing time when the Oracle database server experiences shortages of RAM memory.

vmstat 2

tail -f /var/log/messages

tail -f /var/log/messages | grep failed ile messages log dosyası içinde failed kelimesi arayabilirsiniz.

swapper: page allocation failure. order:0, mode:0×20

benzer mesajlar alıyorsanız sistem RAM i yetersiz demektir. Şu andaki RAM kullanımını görmek içinse:

# free -m
total used free shared buffers cached
Mem: 2010 1844 166 0 12 1290
-/+ buffers/cache: 541 1469
Swap: 3967 0 3967

Total başlığı sistemdeki toplam RAM miktarını, used başlığı ise kullanılan miktarı göteriyor. Ancak buradaki asıl önemli konu cache in ne kadarının kullanıldığı. Çünkü uygulamaların ne kadar hafıza kullandığı bu değerden anlaşılır. En iyi performans için, kullanılan cache miktarı toplam bellekten daha az olmalıdır. (541 <>

İşletim sistemleri RAM canavarlarıdır, sistemde ne kadar çok RAM varsa o kadar geniş geniş çalışırlar. Örneğin benim sistemimdeki 2 GB RAM’in 1.8 GB ı kullanılmış durumda. 1 GB iken de yaklaşık 900 MB. ı kullanımda idi. Yani işletim sistemi RAM kullanımında “ayağını yorganına göre uzatıyor”.

ps -aux komutu ile çalışan uygulamaların sistem kaynağı kullanımını (o an için) görebilirsiniz.

vmstat komutu takas belleği* kullanılıyorsa, ilgili istatistikleri görüntüler. (swap, Takas Bellek, sistem belleğinin yetişmemesi durumunda sabit disk üzerinde bir bölümün RAM bellek gibi kullanılması)

# vmstat 1 2
vmstat 1 2 -> 1 değeri 1 sn aralıkla, 2 ise 2 kez güncel değerleri getirir.

si (swap in) ve so (swap out) değerleri 2–3 interval in ardından 0 a inmeliler. bi (swap e giren byte, byte in) ve bo (byte out) değerleri ise çok büyük değerlerde olmamalı. Sondan bir önceki sütundaki id (CPU idle, cpu nun boşta olma zamanı) değeri ise 100 e ne kadar yakında o kadar iyidir. (İşlemcinin o kadar serbest olduğunu gösterir)
http://www.trsecurity.net/

Disk Alerts

Low free space in archived redo log directory - If your archived redo log directory is becoming full, we e-mail an alert to your DBA so they can add space before the Oracle database hangs.

UNIX mount point - The script checks all UNIX mount points for Oracle, including the UNIX Oracle home directory. Because most Oracle9i databases use the autoextend option for data files, we must be constantly alert for file systems that may not be able to extend. If the free space in any mount point is less than the specified threshold, an e-mail alert will be sent to the DBA.

Oracle Database Alerts

We use leading-edge technology to monitor every component of your Oracle database, and we offer the most comprehensive and sophisticated Oracle alert monitoring anywhere. We are proud that we detect potential problems before they cause a production outage.

Trace and Dump file Alert – We can immediately detect and e-mail Oracle trace or dump files for fast problem resolution.

Alert log messages - Every minute, we check for new alert log messages and e-mail them to your DBA staff.

Object cannot extend Alert - This report will alert the Oracle DBA whenever an Oracle table or index does not have room to take another extent

Tablespace > 95% free Alert - This report sends an e-mail alert whenever any tablespaces contain less space than specified and the datafile is not using the autoextend option.

Object > nnn extents Alert - This report detects tables and indexes that experience unexpected growth. Whenever a table or index exceeds the number defined, an e-mail alert will be sent to the DBA.

Hot File Alert - We report on all files whose read or write I/O are greater than (25 percent or 50 percent or 75 percent) of total I/O. This code compares the individual I/O for a file from stats$filestatxs with the overall I/O for the period in stats$sysstat.

Data Buffer Hit Ratio Alert - This report detects those times when the data buffer hit ratio falls below the preset threshold. This script also reports on all three data buffers, including the KEEP and RECYCLE pools, and it can be customized to report on individual pools.

High Disk Sorts Alert - We detect whenever a disproportional amount od disk sorts occurs. This report is very useful for monitoring the amount of activity against the TEMP tablespace, and it is also useful for ensuring that sort_area_size is set to an optimal level.

I/O Wait Alert - We detect any Oracle files with an excessive amount of wait activity. If the number of I/O waits appears excessive, we investigate the cause of the waits. High I/O waits on files are commonly associated with buffer busy waits, and may be caused by tables with too few freelists or freelist groups.

Buffer Busy Wait Alert - For non-ASSM datafiles, we detect high buffer busy waits.

Redo Log Space Requests Alert - If constantly monitor for high redo log space requests and make appropriate adjustments to the log_buffer parameter. A high number of redo log space requests indicates a high level of update activity, and the Oracle log buffer is having trouble keeping up with the volume of redo log images.

Chained Row Alert - We monitor for continued row fetches greater than 10,000/hr.

Shared Pool Contention Alert - We monitor for enqueue deadlocks that can indicate contention within the shared pool and locking related problems. Enqueue deadlocks are associated with the deadly embrace condition where one task is locking resources and another task that is holding resources requests a lock on the resources of the first task.

Full Table Scan Alert - We can monitor for large-table full-table scans within your library cache to alert you to potential SQL tuning.

Background Wait Alert - We monitor Oracle to find events with high waits. When background events experience more than 100 time-outs/hr, you may have a locking problem.

System Waits Alert - We monitor the Oracle event structures to locate events with excessive wait times. If you experience waits on latch free, enqueue, LGWR waits, or buffer busy waits, you need to locate the cause of the contention.

Library Cache Misses Alert - We monitor for excessive library cache miss ratios. When the library cache miss ratio is too high, we increase the shared_pool_size.

Database Writer Contention alert - We monitor Oracle for high values in summed dirty queue length, write requests, and DBWR checkpoints. When the write request length is greater than 3 (or your Database Writer checkpoint waits, we look at tuning the database writer processes.

Data Dictionary Miss Ratio Alert - We also monitor the Oracle data dictionary to compute data dictionary gets, data dictionary cache misses, and the data dictionary hit ratio.