Check the archive log status in Oracle Data guard Check the received and applied archive log on the Oracle Dataguard environment select ‘Last Log applied : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time…
How to find the maximum user doing commit operation or transaction in Oracle DB Script helpful in tracking the number of transactions in the database. Note: It used the AWR…
Find active transactions in oracle database Script can be used to find the active transactions. col name format a10 col username format a8 col osuser format a8 col start_time format a17…
Script to monitor the ASM disk usage in Oracle The following disk gives you an idea of the Total size, used size, and percentage of space used. SET LINESIZE 150…
Monitor lag in standby database using dgmgrl Script to monitor lag in standby database and send mail. #!/bin/bash export ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=primdb export PATH=$ORACLE_HOME/bin:$PATH echo -e “show database stydb”|${ORACLE_HOME}/bin/dgmgrl sys/orcl1234…
Monitor parallel queries running in Oracle Database Query to monitor currently running queries with parallel option: select s.inst_id, decode(px.qcinst_id,NULL,s.username, ‘ – ‘||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”, decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” ,…
Check execution time and plan for SQL ID from the history table in Oracle dba_hist_active_sess_history table is used to fetch the information about SQL id set linesize 150 col exec_start…
Check the Memory usage used by Oracle from Operating System Check the SGA_TARGET and PGA_TARGET size : SQL> select sum(value)/1024/1024/1024 from v$parameter where name in (‘sga_target’,’pga_aggregate_target’); SUM(VALUE)/1024/1024/1024 ————————- 1.5 If…
Check maximum query duration in Oracle for undo retention setting Check the maximum query duration in Oracle: SQL> select max(maxquerylen) from v$UNDOSTAT; SQL> select max(maxquerylen) from DBA_HIST_UNDOSTAT; Set the undo…
Steps to increase the size of SGA memory in Oracle Check the SGA parameter which show the size of SGA allocated in Oracle SQL> show parameter sga_ NAME TYPE VALUE…