All apps dba Blog is the blog contributed by Doyensys Employees, With the view to share the knowledge out of their experience.
Doyensys Is a Fast Growing Oracle Technology Based Solutions Company Located in the US And Offshore Delivery Centers in India. With rich experience In Oracle E-Business, Oracle Database, Oracle Application Express (APEX), Oracle J Developer, Oracle OBIEE and E-Business Suite
select ctime "Date" , decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type , bsize "Size MB" from (select trunc(bp.completion_time) ctime , backup_type , round(sum(bp.bytes/1024/1024),2) bsize from v$backup_set bs, v$backup_piece bp where bs.set_stamp = bp.set_stamp and bs.set_count = bp.set_count and bp.status = 'A' group by trunc(bp.completion_time), backup_type) order by 1, 2;
Query to get the latest application version in oracle Apps
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done", BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL
Query to see the top 10 io datafiles for write activity
select * from ( select substr(f.name,8,(instr(f.name,'/',9,1) -8)) fsmtpt, substr(f.name,(instr(f.name,'/',-1,1)+1)) fname, s.phyrds, s.phywrts,s.lstiotim,s.avgiotim from v$datafile f, v$filestat s where f.file# = s.file# order by 4 desc) where rownum < 11;
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, -- B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRIVERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID AND C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID AND A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '&patch_number')) ORDER BY 3;
Query to know what changes a patch has made on the
Query to check changes made by the patch in oracle application as follows,.
Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G, AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '&Patch_Number')) GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR, D.FILENAME, E.ACTION_CODE;