Modern Oracle DBA

Normally we are getting concurrent program running longer and concurrent request not picking jobs, etc. those are very frequent issue we expected from development team. Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance? Once asked above questions to development team in meanwhile we need to start basic sanity check in our environment. 1 Verify the status of the concurrent program select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’1234567’; 2 Find which concurrent manger ran a specific concurrent request select b.USER_CONCURR

Top Command Analysis Top command is used to show the Linux process This command shows the summary information of the system and the list of process which are currently managed by the kernel Top command we need to check two session summery session and task session Summery session System Time, Up time and user session Load average Tasks CPU usage Memory Usage System Time, Up time and user session top - 11:59:39 up 225 days, 22:31, 136 users, System time: 11:59:39 Up time: up 225 days, 22hrs:31min User session: 136 users connected. Load average Load average is the system load calculated over given period 1,5 and 15 minutes Load average have 3 sessions Load average: 4.43, 4.14, 4.03 Load average over last 1 minutes is 4.43 Load average over last 5 minutes is 4.14 Load average over last 15 minutes is 4.03 The high load average that system is overloaded many processes are waiting for CPU time Tasks Tasks session showing how many processes runni

What is ASH report? Active session history Sampled active session for into memory buffer every second (V$ACTIVE_SESSION_HISTORY) Collect active session data only Writing to disk by AWR snapshots (DBA_HIST_ACTIVE_SESS_HISTORY) Part of oracle 10g database   How ASH work? If example oracle standard program used to run in 5-second now which taking mort hen 10-min need to check that particular sql query why taking 10-min. For AWR report never show this type of performance issue. Awr report showing only top SQL We can find this kind of issues in ASH report. Using ASH report can perform detailed analysis V$ACTIVE_SESSION_HISTORY view and DBA_HIST_ACTIVE_SESS_HISTORY view   V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. For V$Activite_session_history table holed data for only one hour Whatever data in v$activie_session_history will write DBA_HIST_ACTIVE_SESS_HISTORY

Sometime after upgrade, cloning and patching front end login page is not opening showing blank page or showing “ java.lang.StringIndexOutOfBoundsException ” error. This issue was occurring due to cache is not cleared after upgrade/Cloning JSP Compilation steps Step 1: Shutdown the application services Step 2: Take backup for the _Page(class file) folders $ cd $OA_HTML/WEB-INF/classes $ ls -lrt $ mv _pages _pages_bkup<date> a $ cd $OA_HTML/WEB-INF/classes $ ls -lrt $ mv _pages _pages_bkup<date> $ mkdir _pages $ ls -lrt Step 3: Run the below command $ perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 99 Step 4: Start the application and clear browser cache. After completed the JSP compilation home page not coming please clear the image and style sheet cache also Cabo Images and style sheets can be corrupted or out of sync in the cabo caches, you may need to clear the related directories after backup: Step1: Shutdown the services

Before going to the Patch lets see the quick backgrounf information about application technology patches Application technology patches are includes. Oracle HTTP server (OHS) using Opatch Oracle weblogic server using BSU Oracle Forms (10.1.2 Oracle_HOME) using Opatch Thoes patches are we can apply on directly on Run edition later run the fsclone and sync the file system We can apply patch edition also start the Patching cycle apply the patches in Patch edition  Weblogic (BSU) Patch BSU is java utlity its used to apply the patch in weblogic server Locatation: $FMW_HOME/utils/bsu/bsu.sh Step 1: Download the Patch Oracle support Step 2: Unzip the Patch file Patch file have (JAR) file and readme file Step 3:  Place the Patches (For example Q3ZB.jar)in cache directory and no need to readme file [oracle@VIS cache_dir]$pwd /apps/VIS/r12/fs2/FMW_Home/utils/bsu/cache_dir Step 4: Source the Patch file system [oracle@VIS~]$. /apps/VIS/r12/fs2/EBSapps.env patch [oracle@VIS~]$echo $FI

  Awr report is automatic workload repository It’s introduced in oracle database 10G This report stores data in sysaux tablespace AWR report background process is MMON(Manageability monitor) and MMNL. AWR report collects, process and maintenance performance statists. It can’t give any recommendations. It’s retain the stats for 8-days by default. How to generate AER report cd $ORACLE_HOME/rdbms/admin Report Name SQL Scripts AWR Report awrrpt.sql Automatic DB Diagnostics Monitor report addmrpt.sql ASH Report ashrpt.sql AWR Diff periods Report awrddrpt.sql AWR Single SQL Statement Report awrsqrpt.sql AWR Global Report awrgrpt.sql AWR Global Diff Report awrgdrpt.sql   AWR report Analysis We can’t analysis the all details in AWR report because It’s contains much data I will be