Useful SQL script for Data guard environment Check the data guard information on primary and Standby SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE Check the error or…
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…
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…
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…
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 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” ,…
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…
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…