Managing Long operations

select * from V$SESSION_LONGOPS;

select * from (

  select opname, target, sofar, totalwork,

         units, elapsed_seconds, message

  from v$session_longops

  ==sid||serial# in (39149)

  order by start_time desc)

where rownum <=1;

Version 1:

-----

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

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

  FROM v$session_longops

  WHERE opname LIKE 'RMAN%'

  AND opname NOT LIKE '%aggregate%'

  AND totalwork != 0

  AND sofar <> totalwork;

  

  If no progress:

  SELECT sid, seconds_in_wait AS sec_wait, event FROM v$session_wait

          WHERE wait_time = 0

          ORDER BY sid;

  

SQL> SELECT sid, serial#, opname, time_remaining

 FROM v$session_longops 

 Where sid||serial# in (39149)

 AND time_remaining > 0;  

 

Version 2:

-----

SELECT TO_CHAR(start_time,'DD-MON-YY HH24:MI') "Backup Started At",

DECODE(TRUNC(time_remaining/86400), 0, TO_CHAR(TO_DATE(time_remaining, 'SSSSS'), 'HH24:MI:SS'), 

TRUNC(time_remaining/86400) || ' Days + '

|| TO_CHAR(TO_DATE(time_remaining - (TRUNC(time_remaining/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) REMAINING,

DECODE(TRUNC(ELAPSED_SECONDS/86400), 0, TO_CHAR(TO_DATE(ELAPSED_SECONDS, 'SSSSS'), 'HH24:MI:SS'), 

TRUNC(ELAPSED_SECONDS/86400) || ' Days + '

|| TO_CHAR(TO_DATE(ELAPSED_SECONDS - (TRUNC(ELAPSED_SECONDS/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) 

ELAPSED,

elapsed_seconds/60 "ELAPSED_MIN",round(time_remaining/60) min_remaining,ROUND(SOFAR/TOTALWORK*100,2) "COMPLETE_%"

fROM v$session_longops

WHERE opname like 'RMAN%'

--WHERE opname like '%full datafile restore%'        --RMAN datafiles restore

and opname like '%RMAN: archived log restore%'        --RMAN Archivelog restore/recover

and sofar <> totalwork

and TOTALWORK != 0

select * from V$RECOVERY_PROGRESS;

 ORA-02394: exceeded session limit on IO usage error

This error comes, when the sessions exceeds the value of LOGICAL_READS_PER_SESSION defined in user profile. To fix it , We need to increase the value of LOGICAL_READS_PER_SESSION in the user profile.

 ORA-01102: Cannot Mount Database In EXCLUSIVE Mode

This error comes, when cluster_database parameter is set to false. Change it to TRUE and try to start again.

 OCI/DLL Not Found(Cannot load OCI DLL:<path to OCI.DLL>)

This problem commonly occurs when customers use a 64-bit Oracle client, which is not supported. Toad requires a 32-bit client.

If you have a 32-bit client, make sure that the Oracle BIN directory is in your system path. This directory will be ORAWIN\BIN, or ORANT\BIN, or something similar.

To check your path, Open a command line window, type PATH, and then press Enter.

 ORA-00020: maximum number of processes exceeded

ps -ef|grep pmon

kill -9 

startup nomount

SQL> alter system set processes=2000 scope=spfile; 

startup force

 Some of the ports specified for the patch file system are not available when running prepar

[root@proderp ~]# netstat -nlp | grep 10000/anyport

kill the process ID, if  it is other than EBS is using

 Nodes x are listed in the ADOP_VALID_NODES table but not in the FND_NODES table

This is how to fix it:

Logon to sqlplus as applsys: sqlplus applsys

On sqlplus:

truncate table fnd_oam_context_files;

truncate table fnd_nodes;

truncate table adop_valid_nodes;

Run autoConfig on the DB tier

Run autoconfig on the run file system

Logon to sqlplus as system: alter trigger ebs_logon disable;

Run Autoconfig on the patch file system

Logon to sqlplus as system: alter trigger ebs_logon enable;