RMAN Queries

In this post, I’ve shared a set of RMAN-related SQL queries that help you quickly check backup details, identify issues, and verify restore sessions— directly from the database views.

1. Find RMAN Complete Percentage

set linesize 110

set pagesize 300

SELECT sid, serial#, context, sofar, totalwork , opname,

round(sofar/totalwork*100,2) "Complete"

FROM v$session_longops

WHERE opname LIKE 'RMAN%' and totalwork<>0 and round(sofar/totalwork*100,2)<>100;

--More Details

col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"

col input_mbytes       for 99,999,990.00 justify right head "READ_MB"

col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"

col output_device_type for a10           justify left head "DEVICE"

col complete           for 990.00        justify right head "COMPLETE %" 

col compression        for 990.00        justify right head "COMPRESS|% ORIG"

col est_complete       for a20           head "ESTIMATED COMPLETION"

col recid              for 9999999       head "ID"

select recid

     , output_device_type

     , dbsize_mbytes

     , input_bytes/1024/1024 input_mbytes

     , output_bytes/1024/1024 output_mbytes

     , (output_bytes/input_bytes*100) compression

     , (mbytes_processed/dbsize_mbytes*100) complete

     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete

  from v$rman_status rs

     , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 

 where status='RUNNING'

   and output_device_type is not null

/

2. Find RMAN session in Database

select b.sid, b.serial#, a.spid, b.client_info

from v$process a, v$session b

where a.addr=b.paddr and client_info like 'rman%';

3. Kill RMAN Sessions:

alter system kill session 'SID,SERIAL#' immediate;

alter system kill session 'SID,SERIAL#,@<Instance_id>' immediate;  -- In RAC

4. RMAN Restore Monitor:

SET LINE 132

SELECT opname, round(sofar/totalwork*100) "% Complete"

  FROM gv$session_longops

 WHERE opname LIKE 'RMAN%'

   AND totalwork != 0

   AND sofar <> totalwork

 ORDER BY 1;

5. RMAN Backup Details with Size:

set pages 2000 lines 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

select INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,

TO_CHAR(END_TIME,'mm/dd/yy hh24:mi') end_time,

ELAPSED_SECONDS/3600 hrs,

INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,

OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,

OUTPUT_DEVICE_TYPE

FROM V$RMAN_BACKUP_JOB_DETAILS

order by SESSION_KEY;

6. History of RMAN Backup Details:

select

  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,

  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,

  (j.output_bytes/1024/1024/1024) output_Gbytes, j.status, j.input_type,

  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',

                                     3, 'Tuesday', 4, 'Wednesday',

                                     5, 'Thursday', 6, 'Friday',

                                     7, 'Saturday') dow,

  j.elapsed_seconds, j.time_taken_display,

  x.cf, x.df, x.i0, x.i1, x.l,

  ro.inst_id output_instance

from v$RMAN_BACKUP_JOB_DETAILS j

  left outer join (select

                     d.session_recid, d.session_stamp,

                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,

                     sum(case when d.controlfile_included = 'NO'

                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,

                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,

                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,

                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L

                   from

                     v$BACKUP_SET_DETAILS d

                     join v$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count

                   where s.input_file_scan_only = 'NO'

                   group by d.session_recid, d.session_stamp) x

    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp

  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id

                   from Gv$RMAN_OUTPUT o

                   group by o.session_recid, o.session_stamp)

    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp

where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS

order by j.start_time desc

/

TFA-00104 Cannot establish connection with TFA Server. Please check TFA Certificates

Error Message

While trying to collect the diagnostic or checking the tfa status, it gives an below error message

FA-00104: Cannot establish connection with TFA Server. Please check TFA Certificates

Solution:

Perform the following steps as the root user:

1. Check if the TFA service is running

ps -ef | grep tfa

2. Stop the TFA service

tfactl stop

3. Start the TFA service

tfactl start

TFA Commands

Introduction:

Oracle Trace File Analyzer (TFA) is a powerful diagnostic tool included with AHF (Autonomous Health Framework). It helps Oracle DBAs and system administrators collect, analyze, and manage diagnostic data for Oracle Database, Grid Infrastructure, and ASM environments — especially during performance or crash issues.

1. Check TFA Status

tfactl status
tfactl start
tfactl stop

2. Collect Diagnostics:

Connect as root user,
Be sure to set the date and time to 2 hours before and after the issue occurred in node-wise if it is RAC.

tfactl diagcollect -from "Jan/01/2025 00:00:00" -to "Jan/01/2025 02:00:00" -noclassify

--It will take some time to collect all the data.

com.sun.deploy.net.FailedDownloadException: Unable to load resource

Fixing JAR File Download Error After EBS R12.2 Clone

When working with Oracle E-Business Suite R12.2, it’s common to encounter unexpected issues after performing a clone. Recently, after a clone activity, a few users faced errors while trying to access form pages.

Issue

Users were unable to load forms and encountered the following error:

ExitException[3]com.sun.deploy.net.FailedDownloadException:
Unable to load resource: http://xxxxxxxx.yyyyyy.zzz:xxxx/OA_JAVA/oracle/apps/fnd/jar/fndbalishare.jar
at sun.plugin2.applet.JNLP2Manager.downloadResources(Unknown Source)
at sun.plugin2.applet.JNLP2Manager.prepareLaunchFile(Unknown Source)
at sun.plugin2.applet.JNLP2Manager.loadJarFiles(Unknown Source)
at sun.plugin2.applet.Plugin2Manager$AppletExecutionRunnable.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)


Analysis

• Verified the Java exception list – it was working fine.

• Checked whether the application URL was added to the trusted sites – it was missing.

• Tested with SYSADMIN credentials in multiple browsers – the same issue persisted.

• Reviewed browser settings to confirm if insecure content was allowed – it was not.

Solution

The issue was resolved by making the following changes on the client side:

Add the application URL to trusted sites in the browser settings.
Allow insecure content for the application URL.
After applying these changes, users were able to access the forms without any errors.

Protocol Not Supported Error During OAuth IMAP Configuration

Issue:

While setting up OAuth for IMAP in Oracle E-Business Suite R12.2, you may encounter the following error during the IMAP connection test:

IMAP connection testing failed with: Protocol not supported

This typically occurs after following the Oracle documentation Doc ID 2884072.1 for OAuth configuration.

Solution:

1. To resolve this, explicitly instruct the JVM to use the Sun HTTPS handler and enforce TLS 1.2 for outbound connections by updating the application tier startup parameters.

-DUseSunHttpHandler=true -Dhttps.protocols=TLSv1.2

2. Apply the changes by stopping and starting the application services:

adstpall.sh & adstrtal.sh




Create a Custom Concurrent Manager in Oracle EBS R12.2

 Please follow the steps to create a custom concurrent Manager on the Oracle EBS R12.2 Application.

1. Stop the Concurrent manager services
2. Login to the application using sysadmin user.
3. Navigate to the Concurrent -> Manager -> Define
4. Create a custom manager as Custom Standard Manager.
- Set cache size as 50
- in Program Library -> Name: Library
- in Workshift -> mention 3 processes 
 - in Specialization rule -> include program's name as customizations and then save.
5. Exclude the concurrent program from the standard maanger
Concurrent-> Manager-> Define -> Standard Manager -> Specializations Rules -> Exclude all the above added concurrent programs.
6. Restart the concurrent Manager service
7. Activate the custom Concurrent Manager.

8. Submit the concurrent program and verify the same.

--Use the following query to identify which manager ran the concurrent request.

select distinct c.request_id, b.USER_CONCURRENT_QUEUE_NAME,e.USER_CONCURRENT_PROGRAM_NAME
from 
fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, 
fnd_concurrent_requests c,
fnd_concurrent_programs d,
FND_CONCURRENT_PROGRAMS_TL e
where  a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and    a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.concurrent_program_id=d.concurrent_program_id AND d.concurrent_program_id=e.concurrent_program_id 
and    c.request_id in ('1234567','1234567','1234567');

Query to Monitor RMAN Restoration Progress in Oracle

 SET LINE 132

SELECT opname, round(sofar/totalwork*100) "% Complete"

  FROM v$session_longops

 WHERE opname LIKE 'RMAN%'

   AND totalwork != 0

   AND sofar <> totalwork

 ORDER BY 1;

Note: For RAC database use, gv$session_longops in the above query.

Query to Update EBS Profile Values for Selective Users at the User Level

 SET SERVEROUTPUT ON SIZE UNLIMITED;

DECLARE

    l_success BOOLEAN;

    CURSOR uid IS

        SELECT DISTINCT fpov.level_value AS user_id

        FROM fnd_profile_option_values fpov

        JOIN fnd_profile_options fpo ON fpov.profile_option_id = fpo.profile_option_id

        WHERE fpo.profile_option_name = 'ICX_FORMS_LAUNCHER'

          AND fpov.level_id = 10004  -- User level

          AND fpov.profile_option_value IS NOT NULL;

    v_counter NUMBER := 0;  -- Counter for users processed

BEGIN

    FOR i IN uid LOOP

        l_success := FND_PROFILE.save(

            'ICX_FORMS_LAUNCHER',

            'http://xxx.xxxx.xxx:8000/forms/frmservlet?config=jws',

            'USER',

            i.user_id

        );

        v_counter := v_counter + 1;

        IF MOD(v_counter, 100) = 0 THEN  -- Print output every 100 users to avoid overflow

            DBMS_OUTPUT.PUT_LINE(v_counter || ' users updated...');

        END IF;

        COMMIT; -- Commit after each update

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Profile update completed successfully.');

END;

/

Query to Update EBS Profile Values for All Users at the User Level

set serveroutput on;

DECLARE

l_success  BOOLEAN;

    CURSOR uid IS

    SELECT user_id from fnd_user;

BEGIN

dbms_output.disable;

 dbms_output.enable(100000);

FOR i IN uid LOOP

        l_success := FND_PROFILE.save('ICX_FORMS_LAUNCHER','http://xxx.xxxx.xxx:8000/forms/frmservlet?config=jws','USER',i.user_id);

        IF l_success

THEN

 DBMS_OUTPUT.put_line('---');

 DBMS_OUTPUT.put_line('Profile Updated successfully ');

DBMS_OUTPUT.put_line('---');

 ELSE

  DBMS_OUTPUT.put_line('---');

 DBMS_OUTPUT.put_line('Profile Update Failed . Error:'||sqlerrm);

 DBMS_OUTPUT.put_line('---');

 END IF;

  commit;

 end loop;

end;

/

Protocol is not supported Error in IMAP While Configuring OAuth in EBS Workflow

When configuring OAuth for the EBS workflow application, you may encounter the following error message in IMAP:

Cause:

The error is triggered due to missing or incorrect protocol settings in the WebLogic Server configuration. By default, some WebLogic instances may not be set to use the required HTTP handler or TLS protocol version, leading to connectivity issues.

Solution:

1) Add -DUseSunHttpHandler=true -Dhttps.protocols=TLSv1.2 to the Server Start arguments for all OACORE servers and ADMIN server.

2) Restart the OACORE services.

Note: Not required to add parameters on DMZ environment.