Oracle

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 …