Oracle E-Business Suite profile option values at different levels

 Oracle E-Business Suite profile option values at different levels

SELECT 

    p.profile_option_name AS short_name,                        -- Internal profile option name

    n.user_profile_option_name AS name,                         -- User-friendly profile name

    DECODE(v.level_id,                                          -- Level where the profile is set

           10001, 'Site',

           10002, 'Application',

           10003, 'Responsibility',

           10004, 'User',

           'UnDef') AS level_set,

    v.level_value AS level_val_id,                              -- ID of the level value (e.g., user_id, app_id)

    -- Get the actual name/key of the level value (e.g., user name, application short name)

    DECODE(v.level_id,

           10001, 'Site',

           10002, (SELECT application_short_name 

                   FROM fnd_application 

                   WHERE application_id = v.level_value),

           10003, (SELECT responsibility_key 

                   FROM fnd_responsibility 

                   WHERE responsibility_id = v.level_value 

                     AND application_id = v.application_id),

           10004, (SELECT user_name 

                   FROM fnd_user 

                   WHERE user_id = v.level_value),

           'UnDef') AS level_val_name,

    v.profile_option_value AS value                             -- Actual profile value set

FROM 

    fnd_profile_options p,

    fnd_profile_option_values v,

    fnd_profile_options_tl n

WHERE 

    p.profile_option_id = v.profile_option_id (+)

    AND p.profile_option_name = n.profile_option_name

    AND UPPER(n.user_profile_option_name) LIKE UPPER('%&ProfileName%');

Analyzing Redo Generation by Objects and SQL Using AWR Snapshots

 Analyzing Redo Generation by Objects and SQL Using AWR Snapshots

1. Redo Logs Generated by Objects in a Time Range

-- Shows objects generating the most redo (block changes) during a given time period

SELECT 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI') AS snap_time,

    dhsso.object_name,

    SUM(db_block_changes_delta) AS redo_blocks

FROM 

    dba_hist_seg_stat dhss,

    dba_hist_seg_stat_obj dhsso,

    dba_hist_snapshot dhs

WHERE 

    dhs.snap_id = dhss.snap_id

    AND dhs.instance_number = dhss.instance_number

    AND dhss.obj# = dhsso.obj#

    AND dhss.dataobj# = dhsso.dataobj#

    AND begin_interval_time BETWEEN 

        TO_DATE('2023_07_06 01', 'YYYY_MM_DD HH24') AND 

        TO_DATE('2023_07_06 07', 'YYYY_MM_DD HH24')

GROUP BY 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

    dhsso.object_name;

2. Redo Logs Generated by a Specific Object Over Time

-- Shows how much redo a specific object (by name) generated across AWR snapshots

SELECT 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI') AS snap_time,

    SUM(db_block_changes_delta) AS redo_blocks

FROM 

    dba_hist_seg_stat dhss,

    dba_hist_seg_stat_obj dhsso,

    dba_hist_snapshot dhs

WHERE 

    dhs.snap_id = dhss.snap_id

    AND dhs.instance_number = dhss.instance_number

    AND dhss.obj# = dhsso.obj#

    AND dhss.dataobj# = dhsso.dataobj#

    AND dhsso.object_name = 'XDMC_MS_MRP_IBP_DATA'   -- Replace with your object name

GROUP BY 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI');

3. SQLs Involving Specific Object That Generated Redo

-- Identify SQLs that involved a given object and may have generated redo

SELECT 

    TO_CHAR(begin_interval_time, 'YYYY_MM_DD HH24:MI') AS snap_time,

    DBMS_LOB.SUBSTR(sql_text, 4000, 1) AS sql_text,

    dhss.instance_number,

    dhss.sql_id,

    executions_delta,

    rows_processed_delta

FROM 

    dba_hist_sqlstat dhss,

    dba_hist_snapshot dhs,

    dba_hist_sqltext dhst

WHERE 

    UPPER(dhst.sql_text) LIKE '%XDMC_MS_MRP_IBP_DATA%'  -- Replace with object name

    AND dhss.snap_id = dhs.snap_id

    AND dhss.instance_number = dhs.instance_number

    AND dhss.sql_id = dhst.sql_id;

4. Snap ID and Runtime Info for a Given SQL ID

-- Get detailed runtime history for a specific SQL_ID using AWR

SELECT 

    a.instance_number AS inst_id,

    a.snap_id,

    a.plan_hash_value,

    TO_CHAR(begin_interval_time, 'DD-MON-YY HH24:MI') AS btime,

    ABS(EXTRACT(MINUTE FROM (end_interval_time - begin_interval_time)) + 

        EXTRACT(HOUR FROM (end_interval_time - begin_interval_time)) * 60 +

        EXTRACT(DAY FROM (end_interval_time - begin_interval_time)) * 24 * 60) AS minutes,

    executions_delta AS executions,

    ROUND(ELAPSED_TIME_delta / 1000000 / GREATEST(executions_delta, 1), 4) AS "avg duration (sec)"

FROM 

    dba_hist_sqlstat a,

    dba_hist_snapshot b

WHERE 

    a.sql_id = '&sql_id'

    AND a.snap_id = b.snap_id

    AND a.instance_number = b.instance_number

ORDER BY 

    snap_id DESC,

    a.instance_number;

Oracle EBS (E-Business Suite) Concurrent Request Monitoring Queries

 Oracle EBS (E-Business Suite) Concurrent Request Monitoring Queries

1. Check Currently Running Concurrent Requests

-- Display details of currently running concurrent requests

SET PAGES 1000 LINES 1000 PAGESIZE 1000;

COL program FOR a80;

SELECT 

    a.request_id,

    DECODE(a.phase_code, 'R', 'Running', 'Normal') AS Phase,

    TO_CHAR(a.actual_start_date, 'DD-MON-RRRR HH24:MI:SS') AS start_time,

    SUBSTR(b.user_concurrent_program_name, 1, 80) AS program

FROM 

    apps.fnd_concurrent_requests a,

    apps.fnd_concurrent_programs_tl b

WHERE 

    a.concurrent_program_id = b.concurrent_program_id

    AND a.status_code = 'R'

ORDER BY 

    start_time;

2. Check Running Requests With SID and Session Info

-- Show session and OS details of currently running concurrent requests

SET LINES 200

SET PAGES 1000

COL spid     FOR a10

COL process  FOR a10

COL pgmname  FOR a50

COL username FOR a15

COL args     FOR a50

COL sid      FOR 9999

SELECT 

    fcr.request_id,

    fcpt.user_concurrent_program_name AS pgmname,

    TO_CHAR(fcr.actual_start_date, 'DD-MON-YY HH24:MI:SS') AS stdate,

    s.sid,

    s.serial#,

    s.status,

    TO_CHAR(s.logon_time, 'DD-MON-YY HH24:MI:SS') AS logon,

    ROUND(s.last_call_et / 60) AS LCT, -- Last Call Time in minutes

    fu.user_name AS username

FROM 

    applsys.fnd_concurrent_requests fcr,

    applsys.fnd_concurrent_programs_tl fcpt,

    gv$session s,

    gv$process p,

    applsys.fnd_user fu

WHERE 

    fcr.phase_code = 'R'

    AND fcr.status_code IN ('R','T','W')

    AND s.process (+) = fcr.os_process_id

    AND s.inst_id = p.inst_id (+)

    AND s.paddr = p.addr (+)

    AND p.spid = fcr.oracle_process_id

    AND fcr.concurrent_program_id = fcpt.concurrent_program_id

    AND fcr.program_application_id = fcpt.application_id

    AND fcpt.language = USERENV('LANG')

    AND fcr.requested_by = fu.user_id

    AND fcpt.user_concurrent_program_name NOT IN ('Report Set', 'Request Set Stage')

ORDER BY 

    logon DESC;

3. Running Requests with Argument Details

-- Shows running requests along with their input arguments

SET LINES 200

SET PAGES 1000

COL program FOR a40

COL user_name FOR a20

SELECT 

    a.request_id,

    DECODE(a.phase_code, 'R', 'Running', 'Normal') AS Phase,

    TO_CHAR(a.actual_start_date, 'DD-MON-RRRR HH24:MI:SS') AS start_time,

    SUBSTR(b.user_concurrent_program_name, 1, 40) AS program,

    SUBSTR(a.argument_text, 1, 60) AS argument,

    c.user_name

FROM 

    apps.fnd_concurrent_requests a,

    apps.fnd_concurrent_programs_tl b,

    apps.fnd_user c

WHERE 

    a.concurrent_program_id = b.concurrent_program_id

    AND a.status_code = 'R'

    AND a.requested_by = c.user_id

ORDER BY 

    start_time;

4. Historical Completed Requests for a Specific Program

-- Show completed requests for a given program name within the last 7 days

SET LINES 200

SET PAGES 1000

COL user_name FOR a20

COL argument_text FOR a60

SELECT 

    a.request_id,

    TO_CHAR(a.actual_start_date, 'DD-MON HH24:MI') AS start_date,

    TO_CHAR(a.actual_completion_date, 'DD-MON HH24:MI') AS end_date,

    ROUND(((a.actual_completion_date - a.actual_start_date) * 24 * 60 * 60 / 60), 2) AS process_time_mins,

    a.phase_code AS P,

    a.status_code AS S,

    a.argument_text,

    d.user_name

FROM 

    apps.fnd_concurrent_requests a,

    apps.fnd_concurrent_programs b,

    apps.fnd_concurrent_programs_tl c,

    apps.fnd_user d

WHERE 

    a.concurrent_program_id = b.concurrent_program_id

    AND b.concurrent_program_id = c.concurrent_program_id

    AND a.requested_by = d.user_id

    AND TRUNC(a.actual_completion_date) >= TRUNC(SYSDATE - 7)

    AND c.user_concurrent_program_name LIKE '%&Concurrent_program_name%'

ORDER BY 

    start_date ASC;

5. Last 300 Days Completion Status for a Specific Program

-- Query to list executions of a specific program within the last 300 days with arguments

SELECT 

    TO_CHAR(actual_start_date, 'DD-MON-YY HH24:MI:SS') AS start_date,

    TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI:SS') AS end_date,

    phase_code,

    status_code,

    ROUND(((actual_completion_date - actual_start_date) * 24 * 60 * 60 / 60), 2) AS process_time_mins,

    request_id,

    TO_CHAR(request_date, 'DD-MON-YY HH24:MI:SS') AS request_date,

    requestor,

    argument_text,

    program

FROM 

    xxfmit.FM_SUMMARY_DETAILS

WHERE 

    program LIKE '%FM GSTR-2 Report Format-1%'

    AND TRUNC(actual_completion_date) >= TRUNC(SYSDATE - 300)

    AND argument_text LIKE '%27AAACF2630E1Z5, , NOV-22%'

ORDER BY 

    request_id DESC;

Monitoring long-running operations

 Monitoring long-running operations

set pages 1000 lines 1000

col start_time for a20

col opname for a20

col message for a20

SELECT SID,serial#,TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,

        opname, time_remaining, elapsed_seconds, message,

        totalwork sofar, (sofar / totalwork) * 100 pct_done

   FROM gv$session_longops

  WHERE totalwork > sofar

    AND opname NOT LIKE '%aggregate%' and sid='&SID';

SQL Tuning Task using the Oracle DBMS_SQLTUNE package

 SQL Tuning Task using the Oracle DBMS_SQLTUNE package

-- Set environment formatting for SQL*Plus

SET PAGES 1000

SET LINES 1000

SET LONG 600000                -- Allow large output for tuning report

SET PAGESIZE 9999

SET LINESIZE 155

SET VERIFY OFF                 -- Suppress variable substitution display

-- Define column format for the output

COLUMN recommendations FOR a150

-- Accept input from user for tuning task name

ACCEPT task_name -

       PROMPT 'Task_Name: '

-- PL/SQL block to create and execute the SQL tuning task

DECLARE

    ret_val VARCHAR2(4000);

BEGIN

    -- Create a tuning task for the specified SQL_ID and time limit

    ret_val := DBMS_SQLTUNE.create_tuning_task(

                  task_name    => '&&Task_name',      -- Task name passed by user

                  sql_id       => '&sql_id',          -- SQL ID to be tuned

                  time_limit   => &time_limit         -- Time limit for tuning in seconds

               );

    -- Execute the tuning task

    DBMS_SQLTUNE.execute_tuning_task('&&Task_name');

END;

/

-- Display tuning recommendations

SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations 

FROM dual;

-- Clear substitution variable

UNDEF task_name

Essential DBA Scripts

 

Essential DBA Scripts

Database Size check

-- Calculates total size of datafiles, redo logs, control files, and overall database size in MB

SELECT 

    DF.TOTAL / 1048576 AS "DataFile Size MB",

    LOG.TOTAL / 1048576 AS "Redo Log Size MB",

    CONTROL.TOTAL / 1048576 AS "Control File Size MB",

    (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL) / 1048576 AS "Total Size MB"

FROM 

    dual,

    (SELECT SUM(bytes) AS TOTAL FROM dba_data_files) DF,

    (SELECT SUM(bytes) AS TOTAL FROM v$log) LOG,

    (SELECT SUM((cffsz+1)*cfbsz) AS TOTAL FROM x$kcccf) CONTROL;

Database Uptime

-- Shows database instance startup time

SELECT 

    instance_name,

    TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') AS startup_time 

FROM 

    v$instance;

-- Displays the database name and PMON process start time (approximate DB start time)

COL database_name FOR a20

SELECT 

    database_name, 

    TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') AS pmon_start_time 

FROM 

    v$session 

WHERE 

    program LIKE '%PMON%';

Redo Log Details

-- Redo log group and member details with size and status

COL REDOLOG_FILE_NAME FORMAT a50

COL STATUS FOR a15

SELECT

    a.GROUP#,

    a.THREAD#,

    a.SEQUENCE#,

    a.ARCHIVED,

    a.STATUS,

    b.MEMBER AS REDOLOG_FILE_NAME,

    (a.BYTES / 1024 / 1024) AS SIZE_MB

FROM 

    v$log a

JOIN 

    v$logfile b ON a.GROUP# = b.GROUP#

ORDER BY 

    a.GROUP#;

-- Summary of redo log groups

SELECT 

    GROUP#,

    THREAD#,

    SEQUENCE#,

    BYTES / 1024 / 1024 AS SIZE_MB,

    MEMBERS,

    STATUS 

FROM 

    v$log;

Control File Details

-- Displays the location of control files from the parameter file

SHOW PARAMETER control_files;

-- Lists control file names from dynamic performance view

SELECT NAME FROM V$CONTROLFILE;

RMAN Backup Location

-- (Placeholder) You may run:

SHOW ALL;

-- or check the default backup destination

SELECT * FROM v$rman_configuration;

Daily Archive Log Generation

-- Archive log generation per day in GB and number of archives

SELECT 

    TRUNC(COMPLETION_TIME, 'DD') AS day,

    THREAD#,

    ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) AS GB,

    COUNT(*) AS archives_generated

FROM 

    v$archived_log

GROUP BY 

    TRUNC(COMPLETION_TIME, 'DD'), THREAD#

ORDER BY 

    day;

Hourly Archive Log Generation

-- Displays hourly archive generation in GB

SET PAGES 1000;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT 

    TRUNC(COMPLETION_TIME, 'HH') AS hour,

    THREAD#,

    ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) AS GB,

    COUNT(*) AS archives

FROM 

    v$archived_log

GROUP BY 

    TRUNC(COMPLETION_TIME, 'HH'), THREAD#

ORDER BY 

    hour;

Character Set of the Database

-- Check current NLS character set used in the database

COL PARAMETER FOR a30

COL VALUE FOR a50

SELECT 

    * 

FROM 

    nls_database_parameters 

WHERE 

    parameter = 'NLS_CHARACTERSET';

Query to display tablespace usage details: size, used space, free space, and percentage used

Query to display tablespace usage details: size, used space, free space, and percentage used

SELECT 

    Substr(df.tablespace_name, 1, 20) "Tablespace Name",   -- Tablespace name (first 20 characters)

    Substr(df.file_name, 1, 40) "File Name",               -- Datafile name (first 40 characters)

    Round(df.bytes / 1024 / 1024, 2) "Size (M)",           -- Total size of the datafile in MB

    Round(e.used_bytes / 1024 / 1024, 2) "Used (M)",       -- Used space in the datafile in MB

    Round(f.free_bytes / 1024 / 1024, 2) "Free (M)",       -- Free space in the datafile in MB

    Rpad(                                                   -- ASCII-based progress bar to represent % used

        ' ' || Rpad('X', Round(e.used_bytes * 10 / df.bytes, 0), 'X'), 

        11, 

        '-'

    ) "% Used"

FROM 

    DBA_DATA_FILES df                                        -- Datafile metadata from DBA view

    LEFT OUTER JOIN (

        -- Aggregating used space from dba_extents per file

        SELECT 

            file_id, 

            Sum(Decode(bytes, NULL, 0, bytes)) used_bytes

        FROM 

            dba_extents

        GROUP BY 

            file_id

    ) e ON e.file_id = df.file_id

    LEFT OUTER JOIN (

        -- Getting maximum free extent size per file from dba_free_space

        SELECT 

            Max(bytes) free_bytes, 

            file_id

        FROM 

            dba_free_space

        GROUP BY 

            file_id

    ) f ON f.file_id = df.file_id

ORDER BY 

    df.tablespace_name, 

    df.file_name;

ORDS Configuration Multiple database in single tomcat server.

 ORDS Configuration Multiple database in single tomcat server.

 

Create directories for ords configuration

 

Create configuration and logs directory

 

Cd /u01/test/ords

Mkdir -p  conf

Cd conf

Mkdir logs

 

Ords configuration database 1

 

Execute below script and mention that java home and ords path  correctly to configure ORDS.

 

export JAVA_HOME=/u01/app/tomcat_install/java/jdk-13.0.1 export PATH=/u01/app/tomcat_install/java/jdk-13.0.1/bin:$PATH export ORDS_HOME=/u01/test/ords export ORDS_CONFIG=/u01/test/ords/conf export ORDS_LOGS=${ORDS_CONFIG}/logs export DB_PORT=1523 export DB_SERVICE=test export SYSDBA_USER=SYS export SYSDBA_PASSWORD=oracle export ORDS_PASSWORD=**********     ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install \ --log-folder ${ORDS_LOGS} \ --admin-user ${SYSDBA_USER} \ --db-hostname ${HOSTNAME} \ --db-port ${DB_PORT} \ --db-servicename ${DB_SERVICE} \ --feature-db-api true \ --feature-rest-enabled-sql true \ --feature-sdw true \ --gateway-mode proxied \ --gateway-user APEX_PUBLIC_USER \ --proxy-user \ --password-stdin <<EOF ${SYSDBA_PASSWORD} ${ORDS_PASSWORD} EOF

 

Copy war file and apex images to tomcat webapps location

 

Create image directory in tomcat webapps location Cd /u01/test/tomcat/apache-tomcat-9.0.73/webapps/ Mkdir i   [oracle@tom ~]$ cd /u01/test/apex/images/ [oracle@tom images]$ cp -R * /u01/test/tomcat/apache-tomcat-9.0.73/webapps/i [oracle@tom images]$   [oracle@tom ords]$ cp ords.war /u01/test/tomcat/apache-tomcat-9.0.73/webapps/

 

create tomcat env and add java_opts variable.

 

export JAVA_HOME=/u01/app/tomcat_install/java/jdk-13.0.1 export CATALINA_HOME=/u01/test/tomcat/apache-tomcat-9.0.73 export CATALINA_BASE=$CATALINA_HOME export PATH=/u01/app/tomcat_install/java/jdk-13.0.1/bin:$PATH export ORDS_CONFIG=/u01/test/ords/conf export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms1024M -Xmx1024M"

 

 

Start the tomcat services. Verify the url.

 

Adding another ords connection in new database.

 

 

Mentioned that new database service name listener port correctly

 

export JAVA_HOME=/u01/app/tomcat_install/java/jdk-13.0.1 export PATH=/u01/app/tomcat_install/java/jdk-13.0.1/bin:$PATH export ORDS_HOME=/u01/test/ords export ORDS_CONFIG=/u01/test/ords/conf export ORDS_LOGS=${ORDS_CONFIG}/logs export DB_PORT=1524 export DB_SERVICE=loans export SYSDBA_USER=SYS export SYSDBA_PASSWORD=oracle export ORDS_PASSWORD=******     ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install \ --log-folder ${ORDS_LOGS} \ --admin-user ${SYSDBA_USER} \ --db-hostname ${HOSTNAME} \ --db-pool sample\ --db-port ${DB_PORT} \ --db-servicename ${DB_SERVICE} \ --feature-db-api true \ --feature-rest-enabled-sql true \ --feature-sdw true \ --gateway-mode proxied \ --gateway-user APEX_PUBLIC_USER \ --proxy-user \ --password-stdin <<EOF ${SYSDBA_PASSWORD} ${ORDS_PASSWORD} EOF

 

To check that new connection pool status

 

[oracle@tom loans]$ cd .. [oracle@tom databases]$ tree . ├── default │   ├── pool.xml │   └── wallet │       └── cwallet.sso └── sample ├── pool.xml └── wallet └── cwallet.sso   4 directories, 4 files

 

Now have two paths available, representing the two connections. The URL will be different, depending on if you configured ORDS

 

REF Link ---- https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-configure-multiple-databases-22-onward

 

 

http://192.168.106.128:9090/ords/  --

 

 

http://192.168.106.128:9090/ords/sample--

 

 

http://192.168.106.128:9090/ords/apxprd 

 

  

Sample Screens