Introduction
Database monitoring is a critical part of any application’s maintenance. Finding database issues in time can help the application remain healthy and accessible. Without solid monitoring in place, database outages can go unnoticed until it’s too late and the business is losing money and customers.
Like any operational monitoring, databases can be proactively or reactively monitored, with proactive monitoring favored by most people. Proactive monitoring seeks to identify any issues before they become major problems. It’s done by looking at database metrics and alerting teams or individuals when values are abnormal.
Reactive database monitoring is done after an incident occurs. It’s usually done for security breach investigation, performance troubleshooting, or major incident reporting.
Monitoring Tools
There are plenty of monitoring tools out there on the market both free and commercial.
Some of the database monitoring tools includes , but not limited to:
- Oracle Enterprise Manager Cloud Control 13C
- SQL*Plus
- PL/SQL Developer
- SQL Developer
- WinSCP
Below, We shall see the list of daily tasks that the DBA should monitor in oracle database.
Instance Availability
- Create an SSH connection using either Putty, Xshell or Mobaxterm into all database servers (Production, Standby, UAT, SIT).
- Check if process monitor is running:
oracle@dbsrv1:~$ ps -ef | grep pmon
grid 2370 1 0 Nov 01 ? 7:59 asm_pmon_+ASM1
oracle 15682 1 0 Nov 01 ? 13:29 ora_pmon_ProdDB
oracle 14265 13802 0 11:10:51 pts/2 0:00 grep pmon
oracle@dbsrv1:~$
- Check if system monitor is running:
oracle@dbsrv1:~$ ps -ef | grep smon
grid 2411 1 0 Nov 01 ? 1:25 asm_smon_+ASM1
root 2449 1 0 Nov 01 ? 470:42 /u01/app/12.1.0/grid/bin/osysmond.bin
oracle 15744 1 0 Nov 01 ? 0:56 ora_smon_ProdDB
oracle 14338 13802 0 11:13:15 pts/2 0:00 grep smon
oracle@dbsrv1:~$
- Check if TNS process is running:
oracle@dbsrv1:~$ ps -ef | grep tns
grid 2765 1 0 Nov 01 ? 1:55 /u01/app/12.1.0/grid/bin/tnslsnr MGMTLSNR -no_crs_notify -inherit
grid 3021 1 0 Nov 01 ? 12:06 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid 3031 1 0 Nov 01 ? 1:53 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
grid 3010 1 0 Nov 01 ? 1:54 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN1 -no_crs_notify -inherit
grid 3040 1 0 Nov 01 ? 1:51 /u01/app/12.1.0/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
oracle 15303 13802 0 11:27:18 pts/2 0:00 grep tns
oracle@dbsrv1:~$
- Check Cluster status, if the database is based on ASM storage/RAC database:
grid@dbsrv1:~$ crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE dbsrv1 STABLE
ora.FRA.dg
ONLINE ONLINE dbsrv1 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE dbsrv1 STABLE
ora.asm
ONLINE ONLINE dbsrv1 Started,STABLE
ora.net1.network
ONLINE ONLINE dbsrv1 STABLE
ora.ons
ONLINE ONLINE dbsrv1 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE dbsrv1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE dbsrv1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE dbsrv1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE dbsrv1 159.234.34.203,STABL
E
ora.cvu
1 ONLINE ONLINE dbsrv1 STABLE
ora.mgmtdb
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.dbsrv1.vip
1 ONLINE ONLINE dbsrv1 STABLE
ora.dbsrv2.vip
1 ONLINE INTERMEDIATE dbsrv1 FAILED OVER,STABLE
ora.oc4j
1 ONLINE ONLINE dbsrv1 STABLE
ora.scan1.vip
1 ONLINE ONLINE dbsrv1 STABLE
ora.scan2.vip
1 ONLINE ONLINE dbsrv1 STABLE
ora.scan3.vip
1 ONLINE ONLINE dbsrv1 STABLE
ora.test.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
2 OFFLINE OFFLINE STABLE
ora.ProdDB.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE STABLE
--------------------------------------------------------------------------------
grid@dbsrv1:~$
- Check ASM status:
grid@dbsrv1:~$ srvctl status ASM ASM is running on dbsrv1 grid@dbsrv1:~$
- Check Listener status:
grid@dbsrv1:~$ srvctl status listener Listener LISTENER is enabled Listener LISTENER is running on node(s): dbsrv1 grid@dbsrv1:~$
- Check database status:
grid@dbsrv1:~$ srvctl status database -d ProdDB Instance ProdDB1 is running on node dbsrv1 Instance ProdDB2 is running on node dbsrv2 grid@dbsrv1:~$ OR oracle@dbsrv1:~$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 21 11:46:56 2022 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> select status, database_status from v$instance; STATUS DATABASE_STATUS ------------ ----------------- OPEN ACTIVE SQL>
Monitoring alert log entries
Chronological log of messages and errors are written out in alert log file. Typical messages found in this file are: database startup, shutdown, log switches, space errors etc.
oracle@dbsrv1:/u01/app/oracle/diag/rdbms/Prodb/Prodb1/trace$ tail -f alert__Prodb1.log
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.54.27)(PORT=38873))
If there is any error or issues in the database, please refer to the alert log files.
Checking Available Space
The DBA as part of the daily monitoring is required to check if there are enough Disk Space, FRA, Tablespace, and Tape Size.
- Disk Space: use a df –h Solaris command to check if there is enough space in root pool, u01, share, var, and backup disk in all database (production instance1, production instance2, standby, card db, Recon).
oracle@dbsrv1:~$ df -h
Filesystem Size Used Available Capacity Mounted on
rpool/ROOT/solaris-1 294G 16G 173G 9% /
/devices 0K 0K 0K 0% /devices
/dev 0K 0K 0K 0% /dev
ctfs 0K 0K 0K 0% /system/contract
proc 0K 0K 0K 0% /proc
mnttab 0K 0K 0K 0% /etc/mnttab
swap 39G 2.6M 39G 1% /system/volatile
objfs 0K 0K 0K 0% /system/object
sharefs 0K 0K 0K 0% /etc/dfs/sharetab
fd 0K 0K 0K 0% /dev/fd
rpool/ROOT/solaris-1/var
294G 497M 173G 1% /var
swap 39G 12M 39G 1% /tmp
rpool/VARSHARE 294G 6.1G 173G 4% /var/share
rpool/export 294G 320K 173G 1% /export
rpool/export/home 294G 336K 173G 1% /export/home
rpool/export/home/grid
294G 400K 173G 1% /export/home/grid
rpool/export/home/oracle
294G 13G 173G 8% /export/home/oracle
rpool/export/home/sol
294G 307M 173G 1% /export/home/sol
rpool 294G 384K 173G 1% /rpool
rpool/VARSHARE/zones 294G 288K 173G 1% /system/zones
rpool/VARSHARE/pkg 294G 304K 173G 1% /var/share/pkg
rpool/VARSHARE/pkg/repositories
294G 288K 173G 1% /var/share/pkg/repositories
u01 147G 111G 35G 76% /u01
oracle@dbsrv1:~$
- ASMCMD: Is a command-line utility that you can use to easily view and manipulate files and directories within Automatic Storage Management (ASM) disk groups. It can list the contents of disk groups, perform searches, create and remove directories and aliases, display space utilization, and more.
grid@dbsrv1:~$ asmcmd ASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 6289446 1189784 0 1189784 0 N DATA/ MOUNTED EXTERN N 512 4096 1048576 511591 298274 0 298274 0 Y FRA/ ASMCMD> ls DATA/ FRA/ ASMCMD>
- v$asm_diskgroup: we can also use v$asm_diskgroup view object to manage ASM disk spaces.
- Tablespace: To get the amount of free space in table space uses the DBA_DATA_FILES and DBA_FREE_SPACE. If the amount of table space used has passed the threshold add space. To decide the threshold value you have to make get the daily growth of the database. This helps to proactively manage and monitor the space of tablespaces.
Check CPU and Memory Usage
On oracle Solaris use the TOP Command to monitor CPU Usage and free memory.
oracle@dbsrv1:~$ top load averages: 39.8, 34.9, 32.2; up 39+02:24:19 14:32:33 753 processes: 712 sleeping, 1 running, 1 stopped, 39 on cpu CPU states: 17.9% idle, 75.5% user, 6.6% kernel, 0.0% iowait, 0.0% swap Kernel: 182784 ctxsw, 50804 trap, 135011 intr, 90514 syscall, 1229 flt Memory: 64G phys mem, 2350M free mem, 48G total swap, 43G free swap PID USERNAME NLWP PRI NICE SIZE RES STATE TIME CPU COMMAND 7172 oracle 1 30 0 38G 38G cpu/40 3:48 2.11% oracle 7180 oracle 1 0 0 38G 38G cpu/36 3:45 2.11% oracle 7201 oracle 1 0 0 38G 38G cpu/4 3:09 2.09% oracle 7205 oracle 1 0 0 38G 38G cpu/23 3:05 2.09% oracle 7141 oracle 1 40 0 38G 38G cpu/34 4:28 2.09% oracle 7167 oracle 1 50 0 38G 38G cpu/47 4:06 2.08% oracle
User Monitoring
Daily the DBA must check all user account that should be open must be and account that should be locked must be locked. It’s the job of the DBA to change System Users before they are expired.
We can use the SQL script below to monitor the status of database users:
Select Username,account_status,expiry_date,default_tablespace from dba_users where default_tablespace !='USERS';
Invalid Objects
The DBA should look up if any invalid objects exists and take proper measure to validate the objects founds.
- To check invalid objects:
SQL> select object_name, status from dba_objects where status’INVADLID’;
And use the ALTER…COMPILE SQL command to fix invalid objects.
Clearing File system and FRA files
Before the server runs low on disk space, the DBA must clear disk spaces by removing files that are no longer necessary.
- Remove old FRA archive logs:
---Connect through RMAN oracle@dbsrv1:~$ rman Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 21 14:44:02 2022 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ProdDB(DBID=66272031) --- List archive logs older than three days. RMAN> list archivelog all completed before 'sysdate-3'; ---Delete archive log older than three days. RMAN> delete archivelog all completed before 'sysdate-3'; ---Crosscheck archive log RMAN> crosscheck archivelog all; ---Remove expired archive log RMAN> delete expired archivelog all;