Davis Apps DBA

When a SQL process is running for a long time/running more than usual time, we may handle this situation by running sql tuning advisor for that particular query. Based on the report generated by SQL Tuning advisor, DBA's shall advise the technical team to tune the query.  Below is the step by step approach to run SQL Tuning advisor for a sql process. select sesion.sid,        sesion.username,        optimizer_mode,        hash_value,        address,        cpu_time,        elapsed_time,        sql_text   from v$sqlarea sqlarea, v$session sesion   where sesion.sql_hash_value = sqlarea.hash_value    and sesion.sql_address     = sqlarea.address    and sesion.username is not null    order by...

My Environment: DB - 12.1.0.1 APPS - R12.1.3 OS - IBM AIX 6.1 Normally conc.requests will access the temporary folder during concurrent process. If there is no space available (100% filled) in temporary location then all the requests will fail and the issue will be clearly addressed in the log file like no space available in tmp location. Normally it would be /usr/tmp, sometimes /var/tmp In general deleting the files under temporary location will clear some space, but sometimes it wont happen. Even after you deleted the files, space will not be recovered and it will be still 100% filled. Reason for such situation is that the files are deleted but the process that access that file is still running and hence the space is logically occupied by this process.  Now killing the process is the only safer way and a quick way to fix the issue. The best way is to use lsof command, get the pid of the process, do a small check and then kill the process using the pid you identified...

Masking mail id in WFM is nothing but null the value for the parameter 'EMAIL_ADDRESS' in two tables. Below are the step by step process to do the same. Stop the WFM services and perform the below steps to mask the email address. Once completed, start the WFM services and proceed with the testing. If email address needs to be unmasked, restore the table from the backup.                           ****************************நன்றி****************************

Tried to cancel some long running requests in my environment and faced the issue 'Could not lock request'. Below are the steps I followed to fix this issue and to cancel those long running/hanging requests. SELECT inst_id, DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM GV$LOCK  WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request; Set Pages 1000 lines 1000 Set head on Column Manager    Format A12 Column Request    Format 999999999 Column Program    Format A30 Column User_Name Format A15 Column Started    Format A15 Column FNDLIBR   Format A9 prompt Managers that is running a request and FNDLIBR PROCESS; select substr(Concurrent_Queue_Name, 1, 12) Manager,        Request_Id Request,        User_name,      ...

My Environment DB - 12.1.0.2.0 Apps - R12.2.5 Type - Single Instance Both DB and Apps resides in the same box. Issue: While running autoconfig on both DB and APPS faced the below issue. AC-50480: Internal error occurred: java.sql.SQLException: ORA-28112: failed to execute policy function We checked the autoconfig log of both the tiers and found the similar issue issues recorded in both the log files. DB Tier Autoconfig Log:                    Generate Tns Names ##########################################################################                 Classpath                   : :/ebstst/oracle/SMOKETST/12.1.0/jdbc/lib/ojdbc6.jar:/ebstst/oracle/SMOKETST/12.1.0/appsutil/java/xmlparserv2.jar:/ebstst/oracle/SMOKE...

To identify the existing keystore and key passwords run the following SQL script connected as the APPS user: SQL> set serveroutput on declare spass varchar2(30); kpass varchar2(30); begin ad_jar.get_jripasswords(spass, kpass); dbms_output.put_line(spass); dbms_output.put_line(kpass); end; / This will output the passwords in the following order: store password (spass) key password (kpass)                             ****************************நன்றி****************************