Sunday, 6 April 2014

error ORA-27154: post/wait create failed - while starting ASM instance

Objective:

Got the following issue while starting up ASM instance.

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

Cook Book:

Checked the sysctl.conf file and found the following entry

kernel.sem = 275  100

Updated the above with the following and executed command "sysctl -p"

kernel.sem = 250 32000 100 256

Started the ASM instance and it came up :)





Wednesday, 22 August 2012

how to delete files in UNIX which contains special characters like " , ( , ',)

Objective: If the file in Unix contains special characters in its name, then a simple "rm" command will not work, so what is the solution!

  Cook Book:

 We can use the escape character "\" to remove such files. 

Following is an example


[oracle@TESTServer]>ls -ltrh
total 129

-rw-r--r--   1 oracle   oinstall       0 Aug 21 14:54 to_date('22-FEB-2012')"
-rw-------   1 oracle   oinstall     723 Aug 21 14:55 nohup.out

[oracle@TESTServer]>rm to_date\(\'22-FEB-2012\'\)\"


[oracle@TESTServer]>ls -ltrh
total 110
-rw-------   1 oracle   oinstall     723 Aug 21 14:55 nohup.out

Thursday, 17 November 2011

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 <spool /erptestdb/home/scripts/check_wf_mailer.log

set lines 1000
set head off
# column tablespace_name format a50
select COMPONENT_STATUS
from apps.FND_SVC_COMPONENTS
where component_type='WF_MAILER'
/

spool off
exit
!
#SCRIPT_DIR=/test/appl/homes/oratest/scripts

if [ -s $SCRIPT_DIR/check_wf_mailer.log ]; then
#COUNT=`cat check_ts.log | wc -l `
COUNT=0
STATUS=`cat $SCRIPT_DIR/check_wf_mailer.log | awk '{print $1}'`
VSTATUS='RUNNING'
echo "v status is: " $VSTATUS
echo "file is present"
echo "Script Dir: " $SCRIPT_DIR
echo "status is " $STATUS
if [ $STATUS != $VSTATUS ] ; then
echo "status is in the if stmt " $STATUS
mailx -s "Oracle Workflow Mailer is down in APPS R12, please check " appsdba@test.com < /erptestdb/home/scripts/check_wf_mailer.log

fi
fi

Thursday, 13 October 2011

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!

Saturday, 9 July 2011

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.

Thursday, 30 June 2011

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);

---------