R12 : All Concurrent Request are Running Normal Forever not Completing

I face one internal issue that all standard concurrent requests in EBS R12.1.3  are not completing and its continuously in status "Running Normal" forever. This issue was reported on of their recently cloned TEST Environment.

Environment details:

EBS : 12.1.3

DB   : 11.1.0.7

OS   : IBM-AIX -7.1 (64-bit)

If we check the log file for any standard concurrent request there is no error reported. Below is the concurrent manager logfile for Active users:

 The concurrent request will stuck with no further progress.

As a workaround tried restarting database, concurrent manager, restarted all Application services and re linking libraries. But none of them worked.

Cause:

To troubleshoot further tried executing Standard Concurrent request "Active Users" from CLI.

$ $APPLORC userid=apps/ap**** mode=character report=$FND_TOP/reports/US/FNDSCURS.rdf batch=yes destype=file desname=FNDSCURS.txt desformat=$FND_TOP/reports/HPL.prt 

REP-3000: Internal error starting Oracle Toolkit.
REP-3000: Internal error starting Oracle Toolkit.

Report Builder: Release 10.1.2.3.0 - Production on Wed Feb 7 12:22:28 2018

Copyright (c) 1982, 2005, Oracle. All rights reserved.

REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-3000: Internal error starting Oracle Toolkit.

Note :-
==========
Also, could you check the user permissions and dba/os group for the $APPLCSF/APLLOUT and APPLLOG directories.

Solution:

The problem is with VNCSERVER files.

Usually Rep - 3000 errors occur when the DISPLAY is not set corrrectly.

Please verify if this is the correct value set for DISPLAY = erptestappldb 0.0
++++++++++++++++++++++++++++

Perform the below action plan

1. Login in to putty as applmgr user in the AIX machine which is running the EBS application. On this machine a vncserver software has to be installed.

2. Start a new vnc server process as applmgr user with a new port eg. vncserver :3 or ebis:0.0

3. Start the vncviewer (from your client desktop) and login as applmgr user on the new port

4. Check echo $DISPLAY to confirm the display value

5. Type xclock and check whether clock is displayed

6. Once all the above actions are successful, open the applications context file on all nodes and change the s_display variable to the new value displayed on vncviewer

7. Save the changes

8. Shutdown the application services and run autoconfig on all nodes

9. Edit the . .profile file to change the DISPLAY to new value and save it

10. Restart the application services and retest the issue by running the report and confirm if the REP - 3000 error occurs still.

How to Recover Drop/Truncate table from database

If by mistakenly droped any table from database and our database size in TB then how to recover that droped table then we can use given below steps.

Step 1
We need valid backup of droped/Truncated table on Source.

Step 2.
Backup Transfer to another Test/Dev/UAT server.

Step 3. We make the Fresh new instance for recovery purpose and doing some entry in below file.
ORACLE_USER$vi /etc/oratab (Make the Entry in oratab file dummy Entry).

Step 4.
ORACLE_USER$. oraenv (then provide the dummy SID name that made the enty).

Step 5.
ORACLE_USER$rman target /

Step 6.
RMAN>startup nomount force ----command on rman.

Step 7.
RMAN>restore spfile from 'Backup Path'.

Step 8.
SQL>create pfile from spfile; ---command.

Step 9.
ORACLE_USER$ vi $ORACLE_HOME/dbs/init_SID(changes the some parameter values.i.e controlefile,datafile,redolog etc.)

Step 10.
SQL>create spfile from pfile.

Step 11.
ORACLE_USER$ kill -9 (pmon)

Step 12.
ORACLE_USER$. oraenv
ORACLE_USER$rman target /

Step 13.
RMAN>startup nomount.

Step 14.
restore controlfile from 'Backup Path'.

Step 15.
RMAN>alter database mount.

RMAN>run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/prod/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/prod/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/prod/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/prod/users01.dbf';
restore tablespace system, sysaux, undotbs1, users;
switch datafile all;
sql "alter database datafile 1,2,3,4 online";
recover database skip forever tablespace TEMP,APEX_1680608425593888,APEX,example;
}

--------if directory same then no need to rename of redo otherwise  rename the redo log---------------
sql "alter database rename file ''/u01/app/oracle/oradata/prod/REDO01.LOG'' to ''/u01/app/oracle/oradata/REDO01.LOG''";
sql "alter database rename file ''/u01/app/oracle/oradata/prod/REDO02.LOG'' to ''/u01/app/oracle/oradata/REDO02.LOG''";

Step 16.
SQL> alter database open resetlogs.

Step 17.
use expdp/impdp