Learning Oracle: June 2017

Sunday, June 11, 2017

Oracle Shutdown Immediate Waiting On Active Process

Usually whenever we try to shutdown an oracle database with immediate option it will wait for some active process to get terminate. This may take long time and it also depends on the number of processes.

Use below command to find out the active sessions on database:

bash-3.00$ ps -ef | grep LOCAL=NO
oracle 17862 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17882 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17854 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17890 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17906 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17848 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17978 1 0 Jun 13 ? 0:03 oracletest (LOCAL=NO)
oracle 17908 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17918 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17910 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17852 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17866 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17930 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17914 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17874 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17932 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17986 1 0 Jun 13 ? 0:03 oracletest (LOCAL=NO)
oracle 17952 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17936 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17836 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17944 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17974 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17948 1 0 Jun 13 ? 0:05 oracletest (LOCAL=NO)
oracle 17898 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17878 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17876 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17900 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17924 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17902 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17892 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17894 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17956 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17966 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17940 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17834 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17960 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
oracle 17934 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)

We need to kill the process only with option LOCAL=NO.

The below is the handy UNIX command which can be used for killing these idle active session on database which are avoiding the database to shutdown with immediate option.

ps -ef|grep 'oracletest (LOCAL=NO)'|grep -v grep|awk '{print$2}'|xargs -i kill -9 {}

Now verify the processes again with ps -ef | grep LOCAL=NO it should not return any list. If these processes killed then the database will shutdown.

Sunday, June 4, 2017

No Concurrent Manager Is Defined To Process This Request, So It Cannot Be Processed.

APPLIES TO:

Oracle Inventory Management - Version 11.5.10.CU2 to 12.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.

SYMPTOMS

There are stuck transactions in mtl_transactions_interface table for ship confirm receiving transactions with the following error:

ERROR
No concurrent manager is defined to process this request, so it cannot be processed.

CAUSE

The concurrent manager log shows the following:

Starting RCVOLTM Concurrent Manager : 10-MAR-2016 06:56:27
Process monitor session ended : 10-MAR-2016 06:56:27
Process monitor session started : 10-MAR-2016 06:58:27
CONC-SM TNS FAIL
Call to PingProcess failed for FNDOPP

<the above continues to loop repeatedly for some time>
. . .
Routine AFPEIM encountered an error while starting concurrent manager
RCVOLTM.
The manager is not able to start properly.
The manager has been put on a SYSTEM HOLD to allow the system administrator to address the issue.
When addressed, the "Fixed" button on the Administer Managers screen can be pressed to restart the manager.

SOLUTION

The concurrent manager log shows there is a problem with this instance, the log shows errors similar to what is reported in Note 1312632.1, please engage your DBA and perform these steps:

Please perform the following steps:

1. Stop all middle tier services including the concurrent managers. Make sure no FNDLIBR, FNDSM, or other dead processes are running.

2. Stop and then restart the database.

3. Connect to SQLPLUS as APPS user and run the following :

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

4. Reference Note.260887.1 regarding the Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES.

5.. Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and Web tiers to repopulate the required system tables.

6. Connect to SQLPLUS as APPS user and run the following statement :

select CONCURRENT_QUEUE_NAME
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not return any value please do the following:

cd $FND_TOP/patch/115/sql

Connect to SQLPLUS as APPS user and run the following script :

SQL> @afdcm037.sql;

Reference Note 218893.1 How to Create The Service Manager 'FNDSM' on Oracle Applications to create libraries for FNDSM and create managers for preexisting nodes.

Check again that FNDSM entries now exist:

select CONCURRENT_QUEUE_NAME
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME like 'FNDSM%';

7. Please run the Concurrent Manager Recovery feature to address any Concurrent Manager / Concurrent Processing issues within the Oracle Application Manager.

8. Start the middle tier services including your concurrent manager.

9. Retest the issue.

Reference metalink Doc ID 2116530.1

RC-50221: Warning: Port Pool 50 is not free

Solution:

Means the port is used by some other program on your host. Use a different port pool number, or try to release the port which is already reserved

$ netstat -a | grep <port number>

Kill the process that use that port using "kill -9 <process id>" command or simply reboot the box (if possible).

Linux Important Files

/etc/passwd - User Settings

/etc/group - Group settings for users.

/etc/hosts - Host name lookup information

/etc/sysctl.conf - Kernel parameters for Linux.

/var/log/messages - Check System and error logs and messages

/etc/oratab - Oracle Registered instance (DBCA)

/etc/fstab - Files to check for File System entries

/home/oracle/.bash_profile - Oracle user profile setting file in Linux.

/proc/meminfo - To determine To determine the RAM size

/etc/redhat- release - get the OS release information

/etc/security/limits.conf - Specify process and open files related limits

/etc/selinux/config - Enable or disable security feature.

Delete large number of files - LINUX

Go to the folder location

cd < folder name>

A faster way of deleting files is the -delete flag:

find . -type f -delete

To show the filenames while deleting them:

find . -type f -print -delete

To show how many files will be deleted, then time how long it takes to delete them:

ls -1 | wc -l && time find . -type f -delete

The above commands will remove all the files in the folder, then you can use rm -rf to remove the folder.

How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent

APPLIES TO:

Oracle Workflow - Version 11.5.10.0 to 12.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.
Checked for relevance on 06-JUL-2013

GOAL

The Workflow mailer has not been running. Which may have caused a large number of e-mail notifications to accumulate in the queue. How does one prevent these from being sent when the mailer is started.

SOLUTION

Please take a backup before making any of these changes and try this on a Test instance first as direct table updates from sqlplus are not supported.

1. Verify the current status of each notifications found in the WF_NOTIFICATIONS table that has potential for being sent when the Java Mailer gets started.

SQL> select notification_id, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
where status in ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
order by notification_id;
Normally, only records where status = 'OPEN' and mail_status = 'MAIL' are notifications that would be sent, but there are programs that also can retry Canceled or Invalid notifications, so we included these as well.
This query should show which notifications are waiting to be e-mailed.

2) Use BEGIN_DATE in the where clause to help narrow down the emails not to get sent by the Mailer from a specific date range.

For example :

SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
from wf_notifications
Where Status In ('OPEN', 'CANCELED')
And Mail_Status In ('MAIL', 'INVALID')
and begin_date < sysdate-30 -- List only emails older than 30 days ago
order by notification_id;
3) To update a notification so that it will not get e-mailed, simply set the MAIL_STATUS = 'SENT', and rebuild the Mailer queue using wfntfqup.sql
The mailer will think the e-mail has already been sent and it will not send it again.
Note : Users can still reply to all these notifications from the worklist page in the applications.

Example:

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT'
where mail_status in ('MAIL','INVALID')
and Status In ('OPEN', 'CANCELED');

(Remember to include any other filters you want like begin_date < sysdate-30)
This will update all notifications waiting to be sent by the mailer to SENT, and therefore will not get emailed when the Mailer is restarted.

4) Run the script $FND_TOP/patch/115/sql/wfntfqup.sql to purge the WF_NOTIFICATION_OUT queue and rebuild it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications waiting in the queue to be sent. It will then populate the queue with the current data in the wf_notifications table.

Since you have changed the mail_status = 'SENT" it will not enqueue these messages again.. Only the ones where mail_status = 'MAIL' and status = 'OPEN' will be placed in the WF_NOTIFICATION_OUT queue and sent by the mailer. (or CANCELED and INVALID if certain concurrent reports are run)

Example :

$ sqlplus usr/passwd@db @$FND_TOP/patch/115/sql/wfntfqup.sql APPSusr APPSpw FNDusr

Example Syntax:

$ sqlplus apps/apps@db @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps applsys

5) Now start the Workflow Java Mailer.

Reference metalink Doc ID 372933.1

R12 E-Business Suite Configuring Workflow Mailer with TLS for Microsoft Office365, Gmail, or Cloud Based Email Services for 12.1.3 and 12.2

APPLIES TO:

Oracle Workflow - Version 12.1.3 to 12.2.4 [Release 12.1 to 12.2]
Information in this document applies to any platform.
GOAL

Setup the Oracle Workflow Mailer to connect to Email servers using TLS authentication.

SOLUTION

To resolve the issue test the following steps in a development instance and then migrate accordingly:

1. Download the following patches along with any required prerequisite patches per the readme instructions:

A. For 12.1.3, apply the following patches to configure the Workflow Mailer with TLS:

Patch 9452181 to upgrade javamail api to 1.4.3 (after applying patch 9452181 you must regenerate the jar files with force option using adadmin and restart middle tier(s))
Patch 20924889 : 1OFF:12.1.3:23192128:OPTION TO SELECT DIFFERENT SECURITY PROTOCOLS NONE/SSL/TLS/STARTTLS FOR MAILER
B. For 12.2, reference the following document and apply the latest AD/TXK patches which contain the changes for Workflow Mailer with TLS:

Note 1617461.1 - Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)
C. For the latest Cloud Email Server Patch information, review the following document and confirm all recommended patches are applied:

Note 2077434.1 - Configuring an Oracle Workflow Notification Mailer with Cloud E-Mail Servers (Doc ID 2077434.1)

2. Set the following mailer parameters by running $FND_TOP/sql/afsvcpup.sql script:

sqlplus apps/<pwd> @$FND_TOP/sql/afsvcpup.sql

Select component id 10006 - the default seeded mailer parameter.

Note: If there is more than one mailer configured then these actions will need to be done for each mailer component, which will have a unique ID.

2.A. For Outbound:

'INBOUND_THREAD_COUNT' parameter value to '0' - turns off the imap processing and allows us to configure SMTP and IMAP separately

'PROCESSOR_OUT_THREAD_COUNT': 1

Log level : 1

Debug Mail Session : Y

OUTBOUND_SERVER: smtp.office365.com

'Outbound Security Protocol': TLS

Note: This parameter delivered by Patch 20540463

'Outbound SSL Enabled' leave at null or set to N

2.B. For Inbound:

'INBOUND_THREAD_COUNT' parameter value to '1'

'IMAP_SECURE_PROTOCOL' value to 'SSL'

Note: Some customers may need to use TLS for this parameter. This parameter is set only from the back end. Possible values are SSL, TLS, and NONE (Default).

'INBOUND_SERVER_NAME' : outlook.office365.com:993

By default outlook uses port 993 but some customers may need to use 143 depending on mail proxies

Username : username@domain.com

password: xxxxx

'Inbound SSL Enabled' leave at null or set to N

'REPLYTO' : username@domain.om

'LOG_LEVEL' : 1

'DEBUG_MAIL_SESSION' : Y

MAILER_SSL_TRUSTSTORE: set to default jdk keystore: $AF_JRE_TOP/lib/security/cacerts

3) Stop the Notification Mailer component from OAM UI, and then restart it so that it picks up the new values.

4) Verify that Mailer is running, and now Inbound is enabled (and all the Inbound parameters are correctly set).

5) Setup the outbound first, and confirm the mailer starts with inbound turned off before configuring inbound.

Reference Doc ID 2051827.1

Thursday, June 1, 2017

To Unzip Multiple Files Using Single Quote

The syntax is as follows to unzip multiple files from Linux command line:

unzip '*.zip'

Type the following command as follows:
$ cd /disk2/images/
$ unzip '*.zip'
$ ls -l

Note: *.zip is put in between two single quotes so that shell will not recognize it as a wild card character.

To Check the load average in linux

1) loadavg file

oracle@testdb 11.1.0]$ cat /proc/loadavg

17.72 17.59 17.93 2/472 22465

2) “w” command

oracle@testdb 11.1.0]$ w

13:50:42 up 4 days, 5:20, 6 users, load average: 17.87, 17.64, 17.93

USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/1    :1.0             Sun11    2:28m 0.03s 1.66s gnome-terminal
root     pts/2    172.16.80.74     Wed10    2:43m 0.01s 0.00s -bash
root     pts/3    172.16.80.74     Wed10   52:53   0.04s 0.01s -bash
root     pts/4    172.16.80.74     Wed14    0.00s 0.02s 0.01s w

3) uptime command

oracle@testdb 11.1.0]$ uptime

13:50:30 up 4 days, 5:19, 6 users, load average: 17.82, 17.62, 17.93

4) top command

top - 13:58:29 up 4 days, 5:27, 6 users, load average: 16.98, 17.78, 17.95
Tasks: 457 total,   1 running, 456 sleeping,   0 stopped,   0 zombie
Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 82.0%id, 17.6%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 16104528k total, 16081828k used,    22700k free,    29076k buffers
Swap: 18481144k total,   345656k used, 18135488k free, 13289392k cached

To Check Concurrent Request And Its Database SID

I had a long running Concurrent request and I needed to monitor it from the Enterprise manager but I needed its Database SID, so I search on metalink and I found this query which helped me out.

Connect as apps user

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID
AND a.phase_code = 'R';

To Check Concurrent Manager With The No.of Requests In Pending/Running

SELECT a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'Q',1,0),0)) Pending_Standby,
sum(decode(b.PHASE_CODE,'P',decode(b.STATUS_CODE,'I',1,0),0)) Pending_Normal,
sum(decode(b.PHASE_CODE,'R',decode(b.STATUS_CODE,'R',1,0),0)) Running_Normal
FROM FND_CONCURRENT_QUEUES_VL a, FND_CONCURRENT_WORKER_REQUESTS b
where a.concurrent_queue_id = b.concurrent_queue_id
AND b.Requested_Start_Date<=SYSDATE
GROUP BY a.USER_CONCURRENT_QUEUE_NAME,a.MAX_PROCESSES;

To Check Particular Concurrent Manager Request Details

SELECT request_id, TO_CHAR( request_date, 'DD-MON-YYYY HH24:MI:SS' )
request_date, TO_CHAR( requested_start_date,'DD-MON-YYYY HH24:MI:SS' )
requested_start_date, TO_CHAR( actual_start_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_start_date, TO_CHAR( actual_completion_date, 'DD-MON-YYYY HH24:MI:SS' )
actual_completion_date, TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) - actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER('&p_request_id');

To Check Concurrent Manager Administer

Select a.USER_CONCURRENT_QUEUE_NAME Name,
       b.TARGET_NODE Node,
       b.MAX_PROCESSES Actual,
       b.TARGET_PROCESSES Target,
       (SELECT count(*) FROM apps.FND_CONCURRENT_WORKER_REQUESTS where REQUESTED_START_DATE < sysdate and CONCURRENT_QUEUE_ID = a.CONCURRENT_QUEUE_ID and phase_code = 'R') Running,
       (SELECT count(*) FROM FND_CONCURRENT_WORKER_REQUESTS WHERE CONCURRENT_QUEUE_ID = a.CONCURRENT_QUEUE_ID AND PHASE_CODE = 'P' AND HOLD_FLAG != 'Y' AND REQUESTED_START_DATE <= SYSDATE) Pending
from apps.FND_CONCURRENT_QUEUES_TL a,
     apps.FND_CONCURRENT_QUEUES b
where language = 'US'
and a.CONCURRENT_QUEUE_NAME = b.CONCURRENT_QUEUE_NAME
and b.ENABLED_FLAG= 'Y'
order by Name;

To Check Max Sessions Details

SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions';

(or)

SELECT STATUS , COUNT(*) FROM V$SESSION group by status;

To Check All The Tablespaces Size

select
   fs.tablespace_name                          "Tablespace",
df.totalspace                               "TOT_SIZE",
fs.freespace                                "TOT_FREE",
(round(100 * (fs.freespace / df.totalspace))) pct_Free
from
   (select      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name ) fs
where   (df.tablespace_name = fs.tablespace_name ) ORDER BY pct_free DESC ;

To Check DBA Recyclebin

select owner, object_name, original_name from dba_recyclebin;

Frequently Used Tables Query

We can find out which tables and indexes are in use through two different mechanisms.

V$SEGMENT_STATISTICS

Of course Oracle tracks the tables in use. In this case, we can see this information in the view V$SEGMENT_STATISTICS. This view contains a lot of information about any all of the different segments in your Oracle database. A [segment][seg] is the set of extents (data blocks) allocated to a single database object. The V$SEGMENT_STATISTICS view needs to be pivoted to get the information we need – it contains one row for reads, one for writes, etc.

This basic query will let us see the volume of activity on different segments in the database:

SELECT vss.owner,
        vss.object_name,
        vss.subobject_name,
        vss.object_type ,
        vss.tablespace_name ,
        SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END
            + CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS reads ,
        SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END) AS logical_reads ,
        SUM(CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS physical_reads ,
        SUM(CASE statistic_name WHEN 'segment scans' THEN value ELSE 0 END) AS segment_scans ,
        SUM(CASE statistic_name WHEN 'physical writes' THEN value ELSE 0 END) AS writes
FROM    v$segment_statistics vss
WHERE   vss.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY vss.owner,
        vss.object_name ,
        vss.object_type ,
        vss.subobject_name ,
        vss.tablespace_name
ORDER BY reads DESC;

This approach will show us information about reads, writes, and segment scans (full table scans) since the dynamic view was last cleared. Typically, this is going to show us information since the database was last started.

The downside of V$SEGMENT_STATISTICS is that it’s across the lifetime of that process. If you happen to have great database up time, a table or index could show up in V$SEGMENT_STATISTICS even though it hasn’t been used in weeks or months.

QUERY WAITS

In order to determine which queries are hitting which tables, we can start sampling the current waits in the system. V$SESSION_WAIT contains a bunch of information including the cryptic p1, p2, and p3 columns. For disk related waits, we only need the p1 and p2 waits. These refer to the file and block number that are a part of the wait. To see what’s going on, we can just do something like:

SELECT vs.username ,
        vsw.wait_class,
        vsw.EVENT AS wait_type ,
        vsw.WAIT_TIME_MICRO / 1000 AS wait_time_ms ,
        vsw.TIME_REMAINING_MICRO / 1000 AS time_remaining_ms ,
        vsw.STATE ,
        de.SEGMENT_NAME ,
        de.SEGMENT_TYPE,
        de.OWNER ,
        de.TABLESPACE_NAME
FROM    V$SESSION_WAIT vsw
        JOIN V$SESSION vs ON vsw.SID = vs.SID
        LEFT JOIN DBA_EXTENTS de ON vsw.p1 = de.file_id
                                    AND vsw.p2 BETWEEN de.BLOCK_ID AND (de.BLOCK_ID + de.BLOCKS)
WHERE   vsw.wait_class <> 'Idle'
        AND vs.username IS NOT NULL
ORDER BY wait_time_ms DESC;

This query will show us:

Who’s running a query
What their current wait is
If it’s a disk wait, which tablespace, object, and object type is being waited on.
The problem with this approach, though, is that it will only catch the queries that are running at the moment you sample. With enough samples, this can be effective, but sampling the system is going to put a small load on it.

ACTIVE SESSION HISTORY

If you’re licensed for the Oracle Diagnostic Pack, you’ll have access to the Active Session History. If you aren’t licensed for the Diagnostic Pack, keep reading, but don’t run these queries on any of your Oracle systems – your Oracle auditors will love you, but your accountants won’t.

Active Session History samples data over time and lets us know which queries have been running inside a given time window. The upside of this is that we can dig into the data collected and figure out if the tables we’re interested in have been used inside a given time window.

SELECT du.username,
        s.sql_text,
        MAX(ash.sample_time) AS last_access ,
        sp.object_owner ,
        sp.object_name ,
        sp.object_alias as aliased_as ,
        sp.object_type ,
        COUNT(*) AS access_count
FROM    v$active_session_history ash
        JOIN v$sql s ON ash.force_matching_signature = s.force_matching_signature
        LEFT JOIN v$sql_plan sp ON s.sql_id = sp.sql_id
        JOIN DBA_USERS du ON ash.user_id = du.USER_ID
WHERE   ash.session_type = 'FOREGROUND'
        AND ash.SQL_ID IS NOT NULL
        AND sp.object_name IS NOT NULL
        AND ash.user_id <> 0
GROUP BY du.username,
        s.sql_text,
        sp.object_owner,
        sp.object_name,
        sp.object_alias,
        sp.object_type
ORDER BY 3 DESC;

You can use this query as a basis to help you isolate who is using which tables and how frequently the queries are being run. If you’re looking for one particular table or index, you can start filtering through the query plan operations to find the object in question.

THREE WAYS TO FIND THE TRUTH

So, there you have it – three ways to find some version of the truth. Looking at V$SEGMENT_STATISTICS will let you understand the way tables have been used historically, but it won’t help you understand if a table has been used recently. V$SESSION_WAIT can be sampled to get a better idea of which tables and indexes are being used and who is using them. And if you need an accurate view, and you’re licensed for the Diagnostic Pack, you can use V$ACTIVE_SESSION_HISTORY to review how frequently a table or index has been used over time.

Identifying Which Session To Terminate

To identify the system identifier (SID) and serial number of a session, query the V$SESSION dynamic performance view

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE USERNAME = 'M.QADER';

SID SERIAL# STATUS
----- --------- --------
2 13 ACTIVE
4 23 INACTIVE

A session is ACTIVE when it is making a SQL call to Oracle Database.

ALTER SYSTEM KILL SESSION '4,23';

To Check Locked Objects In Applications

SELECT distinct 'alter system kill session'''||vs.sid||','||vs.serial#||''';'
       ,vs.USERNAME
       ,fu.user_name
       ,ACTION
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid   = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr   = vp.addr
AND vp.spid    = fl.process_spid(+)
AND vp.pid     = fl.pid(+)
AND fl.user_id = fu.user_id(+);

Troubleshoot Long Running Concurrent Request In Apps 11i/R12

Step 1 : Check Concurrent Request ID of long running concurrent request from front end

Step 2 : Find SID, SERIAL# and SPID by running SQL (given below)

Step 3 : Enable event 10046 trace with level 12 using oradebug ( for 15-20 minute)

Step 4 : Disable trace (once you are happy with trace size)

Step 5 : Convert raw trace to TKPROF using various sort options like fchela, prsela, execpu

Step 6 : Check TKPROF out file to find root cause of slow concurrent request

In Detail:

Step 1 : Check Request ID from Find Concurrent request screen
system administrator--> concurrent request --> select phase=Running and status=Normal
uncheck on long running request

(In my case Request ID is 31487467) when prompted

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b, v$process c, v$session d WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id AND b.session_id=d.audsid AND a.request_id = &Request_ID;

O/P:

REQUEST_ID    SID    SERIAL#    OSUSER    PROCESS    SPID

31487467           249     47495             oracle       12922           20732

Step 3.1 : Check and confirm SPID on Database Node

oracle@testdb trace]$ ps -ef|grep 20732
oracle   20732     1 14 16:03 ?        00:09:29 oracleJUNCLN (LOCAL=NO)
oracle   23967 9608 0 17:10 pts/2    00:00:00 grep 20732

Step 3.2 : Set OSPID (20732 in my case) for ORADEBUG

SQL> oradebug setospid 20732

Oracle pid: 58, Unix process pid: 20732, image: oracle@testdb.sapac.com.sa

SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

LEVEL 12 – Both Binds and Waits
LEVEL 8 – Only WAITS
LEVEL 4 – Only BIND Variables

Step 3.4 : Locate Trace file as

SQL> oradebug tracefile_name

/u01/oracle/JUNCLN/db/tech_st/11.1.0/admin/JUNCLN_testdb/diag/rdbms/juncln/JUNCLN/trace/JUNCLN_ora_20732.trc

Wait for 15-20 minutes

Step 4 : Disable trace

SQL> oradebug event 10046 trace name context off

Step 5: Create tkprof file like

[oracle@testdb trace]$ tkprof JUNCLN_ora_20732.trc JUNCLN_ora_20732_txt explain=apps/******* sort=(exeela,fchela) sys=no

Step 6 : Check TKPROF file to find root cause of slow concurrent request

Session Details Queries

### session by cpu-usage:

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
FROM v$session ss, v$sesstat se, v$statname sn
WHERE     se.STATISTIC# = sn.STATISTIC#
     AND NAME LIKE '%CPU used by this session%'
     AND se.SID = ss.SID
     AND ss.status = 'ACTIVE'
     AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

### session sorted by logon time:

select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time;

Top SQL's In Database

SELECT *
FROM ( SELECT username,
                 LAST_LOAD_TIME,
                 sql_text,
                 sql_id,
                 elapsed_time,
                 cpu_time,
                 user_io_wait_time
            FROM sys.v_$sqlarea s, dba_users u
           WHERE     u.USER_ID = s.PARSING_USER_ID
                 AND TRUNC (LAST_LOAD_TIME) = TRUNC (SYSDATE)
                 AND u.username =user
        ORDER BY 6 DESC)
WHERE ROWNUM < 10;

Pages