Tablespace Growth History and Forecast

To get information about full database growth history and forecast, please see my article and script by clicking  here.

Starting Oracle 10G, Oracle records tablespaces usage (allocated, used etc.) in AWR which can be retrieved by querying the data dictionary viewdba_hist_tbspc_space_usage. Following scripts can be used to view the history of tablespace(s) usage and predict the expected growth for the future. Growth forecast is based on daily growth in the past.

Things to note:
1) This script is based on AWR. If your AWR retention period is 7 days, this script can only tell the growth history of last 7 days and predict based on last 7 days growth. I would recommend to have AWR retention to at least 30 days - this will also be more helpful in case of performance tuning situation as you will have a longer window of the past to look into for performance comparisons.
2) You may edit this scrip according to your requirement to forecast for a period  which suites your requirements. By default it will predict expected growth for next 30, 60 and 90 days.
3) Log in as user SYS on SQLPLUS and copy and paste the following code. You will be prompted for the tablespace name

Script for Single Tablespace

##############################################
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_name varchar2(200) := UPPER('&Tablespace_Name');
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');

END;
/
##############################################

Sample Output

Enter value for tablespace_name: TEST


Tablespace Block Size: 8192
---------------------------


Summary
========
1) Allocated Space: 2048 MB (2 GB)
2) Used Space: 1558.44 MB (1.52 GB)
3) Used Space Percentage: 76.1 %


History
========
1) Allocated Space on 06-DEC-14: 2048 MB (2 GB)
2) Current Allocated Space on 10-JAN-15: 2048 MB (2 GB)
3) Used Space on 06-DEC-14: 1273 MB (1.24 GB)
4) Current Used Space on 10-JAN-15: 1558.44 MB (1.52 GB)
5) Total growth during last 35 days between 06-DEC-14 and 10-JAN-15: 285.44 MB (.28 GB)
6) Per day growth during last 35 days: 8.16 MB (.01 GB)


Expected Growth
===============
1) Expected growth for next 30 days: 244.66 MB (.24 GB)
2) Expected growth for next 60 days: 489.33 MB (.48 GB)
3) Expected growth for next 90 days: 733.99 MB (.72 GB)
If no data is displayed for this tablepace, it means AWR does not have any information for this tablespace
PL/SQL procedure successfully completed.


/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Script for All Tablespace (Except UNDO and TEMP)
set serverout on
set verify off
set lines 200
set pages 2000
DECLARE
v_ts_id number;
v_ts_block_size number;
v_begin_snap_id number;
v_end_snap_id number;
v_begin_snap_date date;
v_end_snap_date date;
v_numdays number;
v_ts_begin_size number;
v_ts_end_size number;
v_ts_growth number;
v_ts_begin_allocated_space number;
v_ts_end_allocated_space number;
cursor v_cur is select tablespace_name from dba_tablespaces where contents='PERMANENT';

BEGIN
FOR v_rec in v_cur
LOOP
BEGIN
SELECT ts# into v_ts_id FROM v$tablespace where name = v_rec.tablespace_name;
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_rec.tablespace_name;
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')))
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id;
v_numdays := v_end_snap_date - v_begin_snap_date;

SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_begin_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_size)*v_ts_block_size/1024/1024,2) into v_ts_end_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id;
SELECT round(max(tablespace_usedsize)*v_ts_block_size/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id;
v_ts_growth := v_ts_end_size - v_ts_begin_size;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space: '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Used Space: '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space Percentage: '||round(v_ts_end_size/v_ts_end_allocated_space*100,2)||' %');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('History');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('1) Allocated Space on '||v_begin_snap_date||': '||v_ts_begin_allocated_space||' MB'||' ('||round(v_ts_begin_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Current Allocated Space on '||v_end_snap_date||': '||v_ts_end_allocated_space||' MB'||' ('||round(v_ts_end_allocated_space/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Used Space on '||v_begin_snap_date||': '||v_ts_begin_size||' MB'||' ('||round(v_ts_begin_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('4) Current Used Space on '||v_end_snap_date||': '||v_ts_end_size||' MB'||' ('||round(v_ts_end_size/1024,2)||' GB)' );
DBMS_OUTPUT.PUT_LINE('5) Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||': '||v_ts_growth||' MB'||' ('||round(v_ts_growth/1024,2)||' GB)');
IF (v_ts_growth <= 0 OR v_numdays <= 0) THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('No data growth was found for this Tablespace');
ELSE
DBMS_OUTPUT.PUT_LINE('6) Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' MB'||' ('||round((v_ts_growth/v_numdays)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Expected Growth');
DBMS_OUTPUT.PUT_LINE('===============');
DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('2) Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*60)/1024,2)||' GB)');
DBMS_OUTPUT.PUT_LINE('3) Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*90)/1024,2)||' GB)');
END IF;
DBMS_OUTPUT.PUT_LINE('If no data is displayed for this tablepace, it means AWR does not have any data for this tablespace');
DBMS_OUTPUT.PUT_LINE('/\/\/\/\/\/\/\/\/\/\/\/ END \/\/\/\/\/\/\/\/\/\/\/\');

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_rec.tablespace_name||' Tablespace');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size);
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('Tablespace usage information not found in AWR');
NULL;
END;
END LOOP;
END;
/

RMAN Catalog Backup details from 11g-rman-respository-tables

• List databases registered with rman repository 

select NAME 
     , DBID
  from RC_DATABASE;

• List rman backup STATUS details with database name, Start time, End time , Processed MB/GB

SELECT db_name, row_type, start_time, end_time, operation, status, mbytes_processed
    FROM   rc_rman_status
    WHERE  db_name like  ‘PROD%’
      AND  start_time > SYSDATE – 1
    ORDER  BY END_TIME desc;

• List backup details of all registered RMAN databases 

SELECT  db_name
      , start_time
      , row_type
      , end_time
      , operation
      , status
      , mbytes_processed
   FROM rc_rman_status
  WHERE start_time > SYSDATE – 1
  ORDER BY DB_NAME
  , start_time;

• Show backups which are still RUNNING

SELECT  db_name
      , start_time
      , row_type
      , operation
      , status
  FROM rc_rman_status
  WHERE start_time > SYSDATE – 1
  AND STATUS = ‘RUNNING’
  ORDER BY DB_NAME
  , start_time;

• List db name, tablespace & datafiles backed up with size 

select DB_NAME
     , TABLESPACE_NAME
     , FILE#
     , BYTES/1000000000 SIZE_GB
     , NAME
  from RC_DATAFILE
 where DB_NAME = ‘PROD’;

• List RMAN backup configuration Parameters 

select 
DB_UNIQUE_NAME 
,NAME
,VALUE          
from RC_RMAN_CONFIGURATION
 where DB_UNIQUE_NAME =’PROD’
order by 1,2,3;
ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE      DISK TO 1
ARCHIVELOG DELETION POLICY                    TO NONE
BACKUP OPTIMIZATION                           OFF
CHANNEL                                       DEVICE TYPE DISK FORMAT   '/mnt/backup/Rman/PROD1/%d_%s_%p_%U'
CONTROLFILE AUTOBACKUP                        ON
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/Rman/PROD1/%F'
DATAFILE BACKUP COPIES FOR DEVICE TYPE        DISK TO 1
DEVICE TYPE                                   DISK PARALLELISM 3 BACKUP TYPE TO COMPRESSED BACKUPSET
MAXSETSIZE TO                                 UNLIMITED
SNAPSHOT CONTROLFILE NAME                     TO '/opt/app/oracle/product/11.2/db_1/dbs/snapcf_RMANTST.f'

• List the most recent FULL Database backup with STATUS=’COMPLETED’

select   a.db_name
Database
       , db.dbid DBID
       , a.end_time Latest_Backup
       , round ( a.output_bytes / 1000000000 ) GBytes_Processed
       , round ( ( end_time – start_time ) * 60 * 24 ) Minutes
    from rc_rman_status a
       , rc_database db
   where object_type in ( ‘DB FULL’
   , ‘DB INCR’ )
     and status = ‘COMPLETED’
     and operation = ‘BACKUP’
     and a.db_name = ‘&&DB_NAME’
     and end_time in ( select end_time
           from rc_rman_status b
          where b.db_name = a.db_name
            and b.db_key = a.db_key
            and object_type in ( ‘DB FULL’
          , ‘DB INCR’ )
            and status = ‘COMPLETED’
            and operation = ‘BACKUP’ )
     and db.db_key = a.db_key
   order by a.db_name
   , end_time desc;

• List Backups for a Specific Database Instance ‘&&DB_NAME’

select   a.db_name
Database
       , db.dbid DBID
       , a.end_time Latest_Backup
       , round ( a.output_bytes / 1000000000 ) GBytes_Processed
       , round ( ( end_time – start_time ) * 60 * 24 ) Minutes_Taken
    from rman.rc_rman_status a
       , rman.rc_database db
   where object_type in ( ‘DB FULL’
   , ‘DB INCR’ )
     and status = ‘COMPLETED’
     and operation = ‘BACKUP’
     and a.db_name = ‘&&DB_NAME’
     and end_time in ( select end_time
           from rman.rc_rman_status b
          where b.db_name = a.db_name
            and b.db_key = a.db_key
            and object_type in ( ‘DB FULL’
          , ‘DB INCR’ )
            and status = ‘COMPLETED’
            and operation = ‘BACKUP’ )
     and db.db_key = a.db_key
   order by a.db_name
   , end_time desc;
DATABASE       DBID LATEST_BA GBYTES_PROCESSED MINUTES_TAKEN
-------- ---------- --------- ---------------- -------------
PROD   4020163152 22-SEP-11                0            20
PROD   4020163152 21-SEP-11                0            19
PROD   4020163152 20-SEP-11                0            17
PROD   4020163152 19-SEP-11                0            16
PROD   4020163152 18-SEP-11                4            13
PROD   4020163152 17-SEP-11                0            15

• List Archivelog backups 

SELECT a.db_name “Database”
     , db.dbid “DBID”
     , a.end_time “Latest Backup”
     , ROUND ( a.output_bytes / 1000000 ) “MBytes Processed”
     , ROUND ( ( end_time – start_time ) * 60 * 24 ) “Minutes Taken”
  FROM rman.rc_rman_status a
     , rman.rc_database db
 WHERE object_type = ‘ARCHIVELOG’
   AND status = ‘COMPLETED’
   AND operation = ‘BACKUP’
   AND a.db_name = ‘&&DB_NAME’
   AND end_time IN ( SELECT end_time
         FROM rman.rc_rman_status b
        WHERE b.db_name = a.db_name
          AND b.db_key = a.db_key
          AND object_type = ‘ARCHIVELOG’
          AND status = ‘COMPLETED’
          AND operation = ‘BACKUP’ )
   AND db.db_key = a.db_key
 ORDER BY a.db_name
 , end_time desc;

• List all Database FULL backups done in last 30 days 

SELECT  SUBSTR ( TO_CHAR ( START_TIME
    , ‘DD-MON-YYYY HH24:MI’ )
        , 1
        , 20 )
      , SUBSTR ( OBJECT_TYPE
  , 1
  , 20 )
   FROM rman.rc_rman_status
  WHERE start_time > SYSDATE – 30
    AND object_type = ‘DB FULL’
  ORDER BY 1 DESC;

• Is RMAN Backup Still Running?

SELECT TO_CHAR ( start_time
        , ‘DD-MON-YY HH24:MI’ ) “BACKUP STARTED”
     , sofar
     , totalwork
     , elapsed_seconds / 60 “ELAPSE (Min)”
     , ROUND ( sofar / totalwork * 100
       , 2 ) “Complete%”
  FROM sys.v_$session_longops
 WHERE opname = ‘dbms_backup_restore’;

http://www.oracledbasupport.co.uk/finding-backup-details-from-11g-rman-respository-tables/

Apps Information

DB INFO

set pages 999 set linesize 160 col HOST_NAME format a40 col PLATFORM_NAME for a30 select INSTANCE_NAME,HOST_NAME,VERSION,STATUS,LOGINS,DATABASE_STATUS from gv$instance; select NAME,LOG_MODE,OPEN_MODE,PLATFORM_NAME from gv$database;

APPS INFO

set lines 160 set pages 300 col PLATFORM_CODE for a15 col VIRTUAL_IP for a30 select NODE_NAME "Node Name", to_char(CREATION_DATE, 'DD-MON-RR HH24:MI') "Creation Date", decode(STATUS,'Y','ACTIVE','INACTIVE') Status, decode(SUPPORT_CP,'Y', 'ConcMgr','No') ConcMgr, decode(SUPPORT_FORMS,'Y','Forms', 'No') Forms, decode(SUPPORT_WEB,'Y','Web', 'No') WebServer, decode(SUPPORT_ADMIN, 'Y','Admin', 'No') Admin, decode(SUPPORT_DB, 'Y','Rdbms', 'No') Database, PLATFORM_CODE, VIRTUAL_IP from apps.fnd_nodes where node_name != 'AUTHENTICATION';

Application version

select RELEASE_NAME from fnd_product_groups;

APPLICATION URL

select home_url from apps.icx_parameters;

languages installed

select NLS_LANGUAGE, INSTALLED_FLAG,LANGUAGE_CODE from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');

Shared appl_top or not

select SHARED, APPL_TOP_GUID,PATH from apps.fnd_appl_tops;

find MRC Enabled ??

select multi_currency_flag from fnd_product_groups;

find Multi-org enabled?

select multi_org_flag from fnd_product_groups;

Workflow Version

select TEXT from WF_RESOURCES where NAME='WF_VERSION';

workflow mailer status

select COMPONENT_NAME,COMPONENT_STATUS from FND_SVC_COMPONENTS where component_id = '10006';

workflow COMPONENT status

set lines 160 select COMPONENT_NAME,COMPONENT_STATUS from FND_SVC_COMPONENTS;

to findout the MODULE/PRODUCT is installed or not ?

col product for a10 col status for a15 col product_version for a10 col patchset for a30 col update_date for a15 select decode(nvl(a.APPLICATION_short_name,'Not Found'),'SQLAP','AP','SQLGL','GL','OFA','FA', 'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) Product, decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status, fpi.product_version, nvl(fpi.patch_level,'-- Not Available --') Patchset, to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update_Date" from apps.fnd_oracle_userid o, apps.fnd_application a, apps.fnd_product_installations fpi where fpi.application_id = a.application_id(+) and fpi.oracle_id = o.oracle_id(+) order by 2,1 /

Patching querys

select bug_number creation_date from ad_bugs where bug_number in ( '6272107', '6272107', '6272107', '6272107', '6272107', '6272107'); select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where BUG_NUMBER like '&patchno'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date between '11-FEB-11' AND '12-FEB-11'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date like '%11-FEB-11%'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where bug_number like '598704%'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date>'11-FEB-11'; select BUG_NUMBER,CREATION_DATE,LANGUAGE from apps.ad_bugs where creation_date<'11-FEB-11'; select PATCH_NAME,PATCH_TYPE,CREATION_DATE from AD_APPLIED_PATCHES where CREATION_DATE LIKE '%12-FEB-11%';

what are all the nodes patch has been applied

select aap.patch_name, aat.name, apr.end_date from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_appl_tops aat where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and aat.appl_top_id = apr.appl_top_id and aap.patch_name = '&PatchName';

What drivers are applied while applying patch??

select apd.driver_file_name, apd.DRIVER_TYPE_C_FLAG "C", apd.DRIVER_TYPE_D_FLAG "D", apd.DRIVER_TYPE_G_FLAG "G" , apdl.language from ad_patch_drivers apd, ad_patch_driver_langs apdl where apd.patch_driver_id=apdl.patch_driver_id and apd.driver_file_name like '%&a%';

distributed ad

on node 1 give the below command adpatch options=hotpatch workers=48 localworkers=24 on node 2 give the below command adctrl distributed=y

admerge

admrgpch -s /cfsdshr/ccld3/appccld3/temp/patches/oem/9444535_long -d /cfsdshr/ccld3/appccld3/temp/patches/oem/9444535_long/dest

Apache version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v

perl version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built

Java version

$AFJVAPRG -version

Client JRE version

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version

Forms Version

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

PL/SQL Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication Mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=