Check List before DB is ready for Performance Test 1) Init Parameters properly configured or not.2) sessions Monitoring script.3) OS Watchers running or not.4) AWR Snapshots configured or not.5) CPU’s Count.6) SGA Size7) OEM (Grid) 8) Keep handy Monitoring Scripts (Check Load Test Monitoring in… Read More
If the Oracle Home is filled and the DB gets hanged, then the first thing we need to check is— Audit File / Network Logs / dump locations — Check the file system using the below commanddf -h | grep oracle (IF THE OUTPUT HAS 100% USAGE)… Read More
1. EXPLAIN PLAN FOR SELECT <STATEMENT> set linesize 300SELECT * FROM TABLE(dbms_xplan.display); 2. FROM MEMORY – Get the execution plan which is generated by CBO Use DBMS_XPLAN package:select * from table (dbms_xplan.display_cursor (‘&sqlid’) ) ; For the above, you need to have SQL ID. To… Read More
cat g_stats_1_test.sh sqlplus / as sysdba <<EOF set head off conn / as sysdba set serveroutput on set timing on set echo on spool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool off set timing off set serveroutput off exit;… Read More
set linesize 200 set pagesize 500 spool /tmp/Oct29.html set markup html on set numwidth 40 select * from emp; set long 100000 select sql_id,sql_text from DBA_HIST_SQLTEXT where sql_id in (select sql_id from v$session); spool off
Ask DB details and query details before start working on it. When did this query last took 2 hours?What is the change in volume of data?How many times this query runs each day?Start and end time of this query execution for past 1 month.Any DDL changes… Read More
for i in srvctl config; do echo $i; srvctl status database -d $i; done; for i in srvctl config; do echo $i; srvctl status service -d $i; done;
for ORA_SID in $(cat /var/opt/oracle/oratab |grep -vi asm |grep -v ‘#’|grep -vi agent |grep -vi orcl7 |awk -F”:” ‘{ print $1}’) do ORACLE_SID=$ORA_SID export ORACLE_SID sqlplus -s “/ as sysdba” <<! startup; exit; ! done
$ cat /u01/app/oracle/admin/SEPQA01/scripts/archiveclean.sh #CURRDATETIME=date ‘+%Y%m%d-%H%M%S’ ORACLE_SID=ORCL1;export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/12102;export ORACLE_HOME LOGDIR=/var/opt/monitoring/oracle/logs LOGFILE=/var/opt/monitoring/oracle/logs/archiveclean.log $ORACLE_HOME/bin/rman <<EOF | tee -a /var/opt/monitoring/oracle/logs/archiveclean.log connect target delete archivelog until time ‘sysdate-1’ ; exit EOF ORACLE_SID=ORCL2;export ORACLE_SID $ORACLE_HOME/bin/rman <<EOF | tee -a /var/opt/monitoring/oracle/logs/archiveclean.log connect target delete archivelog until time ‘sysdate-1’ ; exit EOF… Read More
/u01/app/oracle/DBA/rmanbackup.sh #!/bin/ksh CURRDATETIME=date ‘+%Y%m%d-%H%M%S’a ORACLE_SID=ORCL1;export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/12.1.0.2;export ORACLE_HOME LOGDIR=/var/opt/oracle/logs LOGFILE=${LOGDIR}/${ORACLE_SID}_rman_${CURRDATETIME}.log RMANBACKUPDIR=/backup/ORCL1/jul16 $ORACLE_HOME/bin/sqlplus dbsnmp/password <<EOF | tee -a $LOGFILE SET LINESIZE 200 COL FILE_NAME FOR A70 SET FEEDBACK OFF SET PAGESIZE 100 COL MEMBER FOR A60 SELECT FILE_NAME,BYTES/1024/1024 MBYTES FROM DBA_DATA_FILES; SELECT FILE_NAME,BYTES/1024/1024 MBYTES FROM DBA_TEMP_FILES;… Read More
