Database , Application and Fusion Technology : December 2019

Friday, December 20, 2019

AQ$_FND_CP_GSM_OPP_AQTBL table created huge mess ....

Issue : OPP given us issue and it got stuck .

Root cause identified that table AQ$_FND_CP_GSM_OPP_AQTBL huge size .

Followed below steps:

1. Shutdown application services

2. select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

3. Sys user :

DECLARE
po dbms_aqadm.aq$_purge_options_t;
BEGIN
po.block := FALSE;
DBMS_AQADM.PURGE_QUEUE_TABLE(
queue_table => 'APPLSYS.FND_CP_GSM_OPP_AQTBL',
purge_condition => NULL,
purge_options => po);
END;
/

4. select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

5. Run cmclean.sql ( For 12.2 environment use OAM )

6. Startup the services

Our issue got fixed.

Thanks

Thursday, December 19, 2019

Suddenly users got huge old workflow notification mails

Issue : Users complained they got huge number of old notification mails

Action -

Very old items still there in wf_notification_out table.

1) SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',
3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT from applsys.WF_NOTIFICATION_OUT wfe group by wfe.corrid, wfe.state;
2 3

CORRID STATE COUNT
-------------------------------------------------- ------------- ----------
APPS:POAPPRV:EMAIL_PO_PDF 234 3 = Exception 1
APPS:ALR: 0 = Ready 123
APPS:REQAPPRV:PO_REQ_APPROVED_JRAD 3 = Exception 1625
APPS:POAPPRV:PO_PO_HAS_BEEN_REJECTED 3 = Exception 380
APPS:OECHGORD:ISO_CANCEL_MSG 3 = Exception 1
APPS:POAPPRV:PO_PO_APPROVE_PDF_OAF 2 = Retained 59
APPS:CS_MSGS:FYI_MESSAGE 3 = Exception 7
APPS:ASOAPPRV:OA_FYI_TO_REQUESTER 3 = Exception 339
APPS:UMXLHELP:LOGIN_HELP_MSG_PWD 3 = Exception 11
APPS:FNDCMMSG:SIMPLE_REQ_COMPLETION 2 = Retained 6
APPS:ASOAPPRV:OA_REQ_APPR_BY_ALL_APPR 3 = Exception 284

2) Stop Workflow notification mailer services

3) Rebuild the Queue , Prior rebuild Q , please check all Mail status and OPEN status.

$ cd $FND_TOP/patch/115/sql/
$ ls -ltr wfntfqup.sql
-rwxrwxr-x 1 prodappl dba 11862 Mar 25 2010 wfntfqup.sql
$ sqlplus apps/*****

SQL> @wfntfqup.sql apps ****** applsys

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95

Commit complete.

Elapsed: 00:00:00.01
**** TEMPORARY TABLES / AQs created ****

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.96
**** Messages backed up ****

Commit complete.

Elapsed: 00:00:00.00
**** wfaqback.sql completed, executing SQLs from wfntfqup

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.18
*** invoking wfaqrenq.sql ******
*** Re-enqueing messages

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

Commit complete.

Elapsed: 00:00:00.00
***** Re-enqueue OF Alerts completed *****

4) After that Q only having new items:

SQL> select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid, decode(wfe.state,0,'0 = Ready',1,'1 = Delayed',2,'2 = Retained',
2 3,'3 = Exception',to_char(substr(wfe.state,1,12))) State,
count(*)COUNT from applsys.wf_notification_out wfe group by wfe.corrid, wfe.state; 3

CORRID STATE COUNT
-------------------------------------------------- ------------- ----------
APPS:ALR: 0 = Ready 6
APPS:WFERROR:DEFAULT_EVENT_ERROR 0 = Ready 2
APPS:OMERROR:OMERROR_MSG 0 = Ready 2
APPS:REQAPPRV:PO_REQ_APPROVE_JRAD 0 = Ready 4

Thanks,

Thursday, December 12, 2019

Install Apache Tomcat on Linux

Install Tomcat on Linux :

1) Downloaded apache-tomcat-9.0.8.tar.gz for Linux server

2) Untar it on some location

[root@stand soft]# tar -xvf apache-tomcat-9.0.8.tar.gz -C /u02/apache/
apache-tomcat-9.0.8/conf/
apache-tomcat-9.0.8/conf/catalina.policy

3) Used tomcat user for this so change the ownership of the directory

chown tomcat:tomcat /u02/apache/apache-tomcat-9.0.8

Also edit bash_profile as

PATH=$PATH:$HOME/bin:$CATALINA_HOME/bin
CATALINA_HOME=/u02/apache/pache-tomcat-9.0.8
export CATALINA_HOME
export PATH

Source the environment variable :

. ./bash_profile

4) We want to start it on port 8006

so modified the value as below:

-bash-4.1$ pwd
/u02/apache/apache-tomcat-9.0.8/conf

-bash-4.1$ grep -i 8006 server.xml

<Connector port="8006" protocol="HTTP/1.1"

5) Start tomcat

-bash-4.1$ sh $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_HOME: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_TMPDIR: /u02/apache/apache-tomcat-9.0.8/temp
Using JRE_HOME: /usr
Using CLASSPATH: /u02/apache/apache-tomcat-9.0.8/bin/bootstrap.jar:/u02/apache/apache-tomcat-9.0.8/bin/tomcat-juli.jar
Tomcat started.

6) Launch the url http://stand.som.com:8006

Thanks,

Linux - Port is being used netstat or lsof

Linux - Port is being used.

Use command

netstat -tuplen

[oracle@stand ~]$ netstat -tuplen

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State User Inode PID/Program name

tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 0 13395 -

tcp 0 0 0.0.0.0:51985 0.0.0.0:* LISTEN 29 13946 -

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 0 15491 -

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 0 14484 -

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 0 54988 -

tcp 0 0 :::42156 :::* LISTEN 501 19541 3987/ora_d000_prod1

tcp 0 0 :::111 :::* LISTEN 0 13400 -

tcp 0 0 :::8080 :::* LISTEN 501 19814 3858/tnslsnr

tcp 0 0 :::1522 :::* LISTEN 501 19034 3858/tnslsnr

tcp 0 0 :::22 :::* LISTEN 0 15495 -

tcp 0 0 ::1:631 :::* LISTEN 0 14483 -

tcp 0 0 ::ffff:127.0.0.1:8005 :::* LISTEN 91 85049 -

tcp 0 0 :::50471 :::* LISTEN 29 13954 -

tcp 0 0 :::8009 :::* LISTEN 91 84985 -

udp 0 0 0.0.0.0:750 0.0.0.0:* 0 13184 -

udp 0 0 0.0.0.0:111 0.0.0.0:* 0 13390 -

udp 0 0 0.0.0.0:631 0.0.0.0:* 0 14487 -

udp 0 0 192.168.245.154:123 0.0.0.0:* 38 55000 -

[oracle@stand ~]$ lsof -i :8080 | grep LISTEN
tnslsnr 3858 oracle 14u IPv6 19814 0t0 TCP *:webcache (LISTEN)

[oracle@stand ~]$ ps -ef|grep 3858
oracle 3858 1 0 14:39 ? 00:00:01 /u04/app/oracle/11g/bin/tnslsnr LISTENER -inherit
oracle 20110 17768 0 18:44 pts/0 00:00:00 grep 3858

Thanks,

Tomcat Apache issue - Exception in thread "main" java.lang.UnsupportedClassVersionError: org/apache/catalina/startup/Bootstrap : Unsupported major.minor version 52.0

Tomcat / Apache Issue:

Exception in thread "main" java.lang.UnsupportedClassVersionError: org/apache/catalina/startup/Bootstrap : Unsupported major.minor version 52.0

Fix for the issue - We have upgraded JDK to latest version.

-bash-4.1$ sh $CATALINA_HOME/bin/startup.sh
Using CATALINA_BASE: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_HOME: /u02/apache/apache-tomcat-9.0.8
Using CATALINA_TMPDIR: /u02/apache/apache-tomcat-9.0.8/temp
Using JRE_HOME: /usr
Using CLASSPATH: /u02/apache/apache-tomcat-9.0.8/bin/bootstrap.jar:/u02/apache/apache-tomcat-9.0.8/bin/tomcat-juli.jar
Tomcat started.

Thanks,

Install latest Java on Linux machine

Please download latest java on linux machine:

-rw-r--r-- 1 root root 319890464 Apr 12 17:23 jdk-9.0.4_linux-x64_bin.rpm
[root@stand soft]# rpm -ivh jdk-9.0.4_linux-x64_bin.rpm
Preparing... ########################################### [100%]
1:jdk-9.0.4 ########################################### [100%]
Unpacking JAR files...
plugin.jar...
javaws.jar...
deploy.jar...

[root@stand soft]# java -version
java version "9.0.4"
Java(TM) SE Runtime Environment (build 9.0.4+11)
Java HotSpot(TM) 64-Bit Server VM (build 9.0.4+11, mixed mode)

[root@stand soft]# rpm -q --whatprovides java
java-1.6.0-openjdk-1.6.0.0-1.50.1.11.5.el6_3.x86_64
java-1.5.0-gcj-1.5.0.0-29.1.el6.x86_64
jdk-9.0.4-9.0.4-ga.x86_64

Thanks,

Untar a file to another location

Untar a file to another location:

from /u02/soft to untar on /u02/apache

[root@stand soft]# tar -xvf apache-tomcat-9.0.8.tar.gz -C /u02/apache/

Thanks,

Linux OS user is facing issue "This account is currently not available"

Linux OS User issue:

Fix for any OS user facing below issue:

[root@stand ~]# su - tomcat
This account is currently not available.

[root@stand ~]# cat /etc/passwd | grep tomcat
tomcat:x:91:91:Apache Tomcat:/usr/share/tomcat6:/sbin/nologin

Above /sbin/nologin is issue , it means no shell assigned to user:
Check the shell using below command.

[root@stand ~]# cat /etc/shells
/bin/sh
/bin/bash
/sbin/nologin
/bin/tcsh
/bin/csh
/bin/dash
/bin/zsh
/bin/mksh
/bin/ksh

[root@stand ~]# chsh -s /bin/bash tomcat
Changing shell for tomcat.
Shell changed.

[root@stand ~]# su - tomcat

-bash-4.1$

Thanks,

AIX - During adstrtal.sh getting "Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong"

In AIX machine - We have encountered an issue where while starting application services we are getting issue

Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong

We did below things:

1) PING is working fine from APPS to DB and DB to APPS

2) TNSPING is working fine.

3) Able to login sqlplus from application node using /nolog and apps user, so apps user password is not an issue.

4) Did DB and Listener bounce

5) Autoconfig on DB and Apps

6) DBC file also looks OK

In our case issue was with /tmp, someone did something and applmgr user unable to create any file on this location:

$ touch aa

touch: 0652-048 Cannot change the modification time on aa.

Chnage the permission of /tmp to 777 and then user applmgr able to write it.

Application came up fine.

Thanks,

R12.2 Log files location

Below are the path of logfiles in R12.2:

Admin server - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/AdminServer/logs/AdminServer.log

oacore logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.log

oacore out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.out

oacore diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1-diagnostic.log

oafm logfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.log

oafm outfile - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.out

oafm diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1-diagnostic.log

form server log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.log

form server access log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/access.log

form server out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1.out

form server diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms_server1/logs/forms_server1-diagnostic.log

forms-c4ws_server1 log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.log

forms-c4ws_server1 out file - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1.out

forms-c4ws_server1 diagnostic log - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/forms-c4ws_server1/logs/forms-c4ws_server1-diagnostic.log

BR,

Friday, December 20, 2019

Thursday, December 19, 2019

Thursday, December 12, 2019

Database connection could not be established. Either the database is down or the APPS credentials supplied are wrong

Translate