shell script to check if the Workflow mailer is down in APPS R12

Introduction: We can use the following shell script and can schedule it with crontab. It is very handy, it will check the workflow mailer and if it is down it will send email to APPS DBA. Cookbook: Following is the script. #!/bin/sh ## ORACLE_HOME=`cat /etc/var/opt/oratab | grep ^TESTDB: | cut -f2 -d':'` ORACLE_HOME=/erptest/TST/db/tech_st/11.1.0 export ORACLE_HOME ORACLE_SID=ERPTEST export ORACLE_SID PATH=$PATH:$ORACLE_HOME/bin:. export PATH SCRIPT_DIR=/erptestdb/home/scripts export SCRIPT_DIR # check_ts.log $ORACLE_HOME/bin/sqlplus -s test_user/test_user <

APPS R12.1.1 post cloning for Database Tier error - ORA-12547: TNS:lost contact

Introduction: On post cloning of database tier of APPS R12.1.1 , it ended with errors "ORA-12547: TNS:lost contact" Resolution: Following were the errors which we got in ApplyDbTier. =============== LD_LIBRARY_PATH : /erptst/TST/db/tech_st/11.1.0/lib:/usr/dt/lib:/usr/openwin/lib:/erptst/TST/db/tech_st/11.1.0/ctx/lib:/erptst/TST/db/tech_st/11.1.0/lib32 Executable : /erptst/TST/db/tech_st/11.1.0/bin/sqlplus The log information will be written to /erptst/TST/db/tech_st/11.1.0/appsutil/log/TST_testmachine/adcrdb_TST.txt Creating the control file for TST_testm1 database ... SQL*Plus: Release 11.1.0.7.0 - Production on Mon Oct 10 16:00:59 2011 Copyright (c) 1982, 2008, Oracle. All rights reserved. ERROR: ORA-12547: TNS:lost contact ORA-12547: TNS:lost contact ERROR: ORA-12547: TNS:lost contact ORA-12547: TNS:lost contact SP2-0640: Not connected ============================== We tried to connect from sqlplus but after few seconds of wait it again gave the same error. We checked the inventory location and permission and it was all ok. We tried again the post clone, but this time we choose the different SID as CLN and gave the different port number, previously it was 01 and this time we chose 79. And this time the post clone went successful without any errors, this is so weird, but this is just what happened!

Oracle Application APEX admin user password reset by script

Introduction: If you dont know the password for admin user of Oracle Application APEX, and if version of APEX is 3 or higer then the password of admin can be reset by a script. CookBook: There is script in the directory of apex softwae and the script name is apxchpwd.sql, this can be run with sys user on sqlplus and it will ask the new password for admin, just type the new password and use it.

Oracle Application APEX admin user

Introduction:
How to create a second amdin user for Oracle Application APEX.

CookBook:

1- unlock the account FLOWS_030000 and login with FLOWS_030000 to Database where APEX was installed.

2- run the following procedure

begin
wwv_flow_api.set_security_group_id(p_security_group_id=>10);
wwv_flow_fnd_user_api.create_fnd_user(
p_user_name => 'admin2',
p_email_address => 'admin2@test.com',
p_web_password => 'admin2') ;
end;
/

3- test the new admin account from the url

http:/appserver.test.com:7778/pls/apex/apex_admin

it will ask the new admin username and password, in our case the username is "admin2" and password is "admin2", once logged in, the APEX will ask to change the old password.

4- in case you dont know the password for FLOWS_030000, you can change the password and once run the procedure in point 2, the FLOWS_030000 should be locked.

To find the sid and serial# of a Concurrent Request in Oracle Financials R 11i and R12

Objective: Some times we need to kill a hang concurrent request, and to kill it, we need to find the sid and serial# of that concurrent request. Cookbook: Following SQL query can be run with APPS user on APPS Database , it will need to provide the concurrent request id, and then it will give the sid and serial# of that concurrent requests, more information can also be obtained from v$session and v$sqlarea views. Following is the SQL query ---------- SELECT ses.sid, ses.serial# FROM v$session ses, v$process pro WHERE ses.paddr = pro.addr AND pro.spid IN (SELECT oracle_process_id FROM fnd_concurrent_requests WHERE request_id = &request_id); ---------

SQL Server 2008 error: The SQL Server failed to initialize VIA support library QLVipl.dll

Introduction:
The Network protocol VIA was enabled, and after that SQL Server 2008 didnt startup and on checking the event log file, the following error was in the log file.

SQL Server The SQL Server failed to initialize VIA support library QLVipl.dll

CookBook:
On disabling the network protocol VIA, when SQL Server 2008 was started from SQL Server Configuration Manager , it started successfully.

Oracle APPS R12 cloning issue

Introduction: Having successfully cloned the db Tier, when we tried to clone the APPS Tier, it gave the following errors. Uploading Metadata file /APPS/TST/apps/apps_st/appl/ad/12.0.0/admin/template/adctxinf.tmp Metadata file /APPS/TST/apps/apps_st/appl/ad/12.0.0/admin/template/adctxinf.tmp upload failed. Check log file created by FNDCPUCF program. ERRORCODE = 137 ERRORCODE_END .end std out. ld.so.1: FNDCPUCF: fatal: /APPS/TST/apps/tech_st/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 Killed ld.so.1: FNDCPUCF: fatal: /APPS/TST/apps/tech_st/10.1.2/lib/libclntsh.so.10.1: wrong ELF class: ELFCLASS64 Killed CookBook: When we checked the logs and error messages , we came to know that os user of the apps tier i.e. appltst doesnt have the permissions on the oraInventory location. Having made a new directory of oraInventory when we again ran the "perl adcfgclone.pl appsTier" it was all successful.