SYS_CONTEXT Basic Functions

The Oracle/PLSQL SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

Syntax :

SYS_CONTEXT( namespace, parameter [, length] )

Arguments Detail:

namespace

An Oracle namespace that has already been created. If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

parameter

A valid attribute that has been set using the DBMS_SESSION.set_context procedure.

length

Optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

Example Script:

SELECT SYS_CONTEXT ('userenv', 'AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,SYS_CONTEXT ('userenv', 'AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
SYS_CONTEXT ('userenv', 'CURRENT_SCHEMA') CURRENT_SCHEMA,
SYS_CONTEXT ('userenv', 'HOST') HOST,
SYS_CONTEXT ('userenv', 'INSTANCE_NAME') INSTANCE_NAME,
SYS_CONTEXT ('userenv', 'IP_ADDRESS') IP_ADDRESS,
SYS_CONTEXT ('userenv', 'LANG') LANG,
SYS_CONTEXT ('userenv', 'LANGUAGE') LANGUAGE,
SYS_CONTEXT ('userenv', 'MODULE') MODULE,
SYS_CONTEXT ('userenv', 'NETWORK_PROTOCOL') NETWORK_PROTOCOL,
SYS_CONTEXT ('userenv', 'NLS_CALENDAR') NLS_CALENDAR,
SYS_CONTEXT ('userenv', 'NLS_CURRENCY') NLS_CURRENCY,
SYS_CONTEXT ('userenv', 'NLS_DATE_FORMAT') NLS_DATE_FORMAT,
SYS_CONTEXT ('userenv', 'OS_USER') OS_USER,
SYS_CONTEXT ('userenv', 'SERVICE_NAME') SERVICE_NAME,
SYS_CONTEXT ('userenv', 'SESSIONID') SESSIONID,
SYS_CONTEXT ('userenv', 'SID') SID,
SYS_CONTEXT ('userenv', 'TERMINAL') TERMINAL
FROM DUAL;

FND_CONCURRENT_QUEUE Control Code Meaning

 In the FND_CONCURRENT_QUEUE table we have a column named control_code.

To get the details of the code present in the column we can use below script.

SQL> select lookup_code,meaning from apps.fnd_lookups where lookup_type = 'CP_CONTROL_CODE' order by lookup_code;

LOOKUP_CODE    MEANING
----------                    ------------------------------
A                               Activating
B                               Activated
D                               Deactivating
E                               Deactivated
H                               System Hold, Fix Manager before resetting counters
N                               Target node/queue unavailable
O                               Suspending concurrent manager
P                                Suspended
Q                               Resuming concurrent manager
R                               Restarting
T                               Terminating
U                               Updating environment information
V                               Verifying
X                               Terminated

FND_CONCURRENT_PROCESSES Table process_status_code columns meaning

 To get the details of the process_status_code column present in the FND_CONCURRENT_PROCESSES table we can use below script.

Query:
select lookup_code, meaning from  apps.fnd_lookups where lookup_type='CP_PROCESS_STATUS_CODE'

LOOKUP_CODE                    MEANING
------------------------------ ------------------------------
A                              Active
C                              Connecting
D                              Deactiviating
G                              Awaiting Discovery
K                              Terminated
M                              Migrating
P                              Suspended
R                              Running
S                              Deactivated
T                              Terminating
U                              Unreachable
Z                              Initializing

Concurrent Processing – Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) & Effected Tables

To avoid running out of space on your disk drives, you should periodically

delete Oracle Applications log files, output files and purge these tables

with the FNDCPPUR program.

The FNDCPUR purge program maintains the number of log and output files the

the operating system retains, and manage tables that store information about

concurrent requests and concurrent manager processes.

You can run the program FNDCPPUR once and automatically resubmit the program

for your specific time intervals.

- Every 30 days for normal usage.

- Every two weeks (14 days) for heavy usage.

- If are using the AGE mode, set the Mode Value to 7 or higher to retain the most recent days of concurrent request data, log files, and report output files. For optimum purging, the request could be scheduled to run on a daily basis off-hours using the AGE that best meets the business needs.

WARNING:When you purge concurrent request information, you lose audit details which are used by the Signon Audit Concurrent Requests report.

FNDCPPUR Program options:

ENTITY = AL : Purge of concurrent requests, concurrent managers, request log files, manager log files and report output files. The following tables are purged

-  Fnd_Concurrent_Processes

-  Fnd_Dual

-  Fnd_Concurrent_Requests,

-  Fnd_Run_Requests

-  Fnd_Conc_Request_Arguments

-  Fnd_Dual

-  Fnd_Context_Env

-  Deletes concurrent requests' log and out files from OS

ENTITY = MANAGER: Purge of concurrent managers and manager log files.

The following tables are purged

-  Fnd_Concurrent_Processes

-  Fnd_Dual

-  Deletes concurrent manager log files from OS

ENTITY = REQUEST: Purge of concurrent requests, request log files, and output files.

The following tables are purged

- Fnd_Concurrent_Requests,

- Fnd_Run_Requests

- Fnd_Conc_Request_Arguments

- Fnd_Dual

- Deletes concurrent requests' log and out files from OS

Mode:

 AGE: The number of days for which to save concurrent request history.

COUNT” Number of records

Mode Value: valid values are 1 - 9999999

User Name: application username

Oracle ID: Oracle ID

Program Application:   application

Program: program

Manager: application associated with the concurrent manager

Manager: concurrent manager

Resp. Application:   application associated with the responsibility

Responsibility:   responsibility or "All".

Report: No       Run the program but do not generate a report.

Yes      Run the program and generate a report.

Tables that are updated when Purge Concurrent Request and/or Manager Data Program Program( FNDCPPUR) is started:

FND_CONCURRENT_REQUESTS

This table contains a complete history of all concurrent requests. This table should ideally be kept at around 4 - 5k records and should be regularly purged as part of normal 'housekeeping' by running the standard FNDCPPUR program. This is a part of basic system administration and it is recommended that for the average instance, it should be run every 30 days or so. Allowing the table ( and others ) to increase in size can affect performance.

FND_RUN_REQUESTS

When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTS

This table records arguments passed by the concurrent manager to each program it starts running.

FND_DUAL

This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSES

This table records information about Oracle Applications and operating system processes.

FND_CONC_STAT_LIST

This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY

 This table contains the concurrent programperformance statistics generated by the Purge.

FND_ENV_CONTEXT

This table stores information about environment name and value for each of the concurrent process

Note:

The following tables can also be purged depending on the parameters being used:

FND_FILE_TEMP

FND_CRM_HISTORY

FND_TM_EVENTS

FND_TEMP_FILES

FND_CONFLICTS_DOMAIN

Also the linked tables of

FND_CONC_REQUEST_ARGUMENTS

FND_CONC_PP_ACTIONS

FND_RUN_REQ_PP_ACTIONS

For example, the 'Purge other' parameter has the following function:

No => Do not delete records from FND_DUAL.

Yes => Delete records from FND_DUAL.

Ref: Concurrent Processing - Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) (Doc ID 104282.1)

Using TAR Utility

tar.gz file is a combination of a .tar file and a .gz file. It is an archive file with several other files inside it.

Create TAR.gz

Simple TAR file of a Specific location

    

    [oracle@server]$                tar -cvf file_name.tar.gz /home/d01/oracle

Create simple TAR file of a Specific Location & save on /home/oracle/backup/ folder

    [oracle@server]$                tar -cvf /home/oracle/backup/file_name.tar.gz /home/d01/oracle

• c – creates a new archive
• v – verbose, meaning it lists the files it includes
• f – specifies the name of the file

Create compressed TAR file

    Add the -z option to create a compressed GNUZIP file

    [oracle@server]$    tar -cvzf file_name.tar.gz /home/d01/oracle

    [oracle@server]$    tar -cvzf /home/oracle/backup/file_name.tar.gz /home/d01/oracle

Extract TAR file

    You can unzip these files the same way you would unzip a regular zipped file:

Extract TAR file

    [oracle@server]$     tar -xvzf filename.tar

• x – instructs tar to extract the files from the zipped file
• v – to list out the files it’s extracting
• z – instructs tar to decompress the files
• f – tells tar the filename you want it to work on

Extract TAR file in specific /home/oracle/clone/db folder

    

    [oracle@server]$    tar –xvzf documents.tar.gz –C /home/oracle/clone/db/

Standard Purge Programs in Oracle Applications

 Purge Concurrent Request and/or Manager Data Purges Concurrent

requests and/or Concurrent Manager data and log/output files

Purge Signon Audit data Purges all Signon Audit
information ( FND_LOGINS, FND_LOGIN_RESPONSIBILITIES,
FND_LOGIN_RESP_FORMS ) created before a given date
GL Archive and Purge Archive and Purge Program

Purge Consolidation Audit Data Purge Consolidation Audit

Mass Additions Purge Report Mass Additions Purge
Report

Purge accounting tables Purge accounting tables

AP/PO Purge Abort Routine AP/PO Purge Abort Routine

AP/PO Purge Deletion Routine AP/PO Purge Deletion
Routine

AP/PO Purge Confirmation Routine AP/PO Purge Confirmation
Routine

AP/PO Purge Initiation (Selection) Routine AP/PO Purge Initiation
(Selection) Routine

AP/PO Purge Summarization Routine AP/PO Purge Summarization
Routine

Purge Report Listings Purge Report Listings

Autoinvoice Purge Program Purge processed
transactions in the AutoInvoice Interface tables

Purge Purge

Archive and Purge Archive and Purge

Purge Module Purge Module

Purge ABC information Purge ABC information

Purge transaction history Purge transaction history

Purge cycle count information Purge cycle count
information

Purge replenishment counts Purge replenishment
counts

Purge physical inventory information Purge physical inventory
information

Transaction Purge Transaction Purge

Purge Standard Cost History Purge Standard Cost
History

Purge Cost Information Purge Cost Information

Purge Configurations Purge Configuration Items

Purge Standard Cost History from SRS Purge Standard Cost
History from SRS

Purge Standard Cost Update History Purge Standard Cost
Update History

Engineering Change Order Purge program Engineering Change Order
Purge Program

Purge Designator Purge Designator

CRP Purge Bill of Resources Purge Capacity Bill of
Resources

Purge Simulation Sets Purge Simulation Sets

MIX Batch Process Validate,Transfer or
Purge MIX batches

MIX Batch Process (Purge) Purges all records
related to a batch

Purge Sales Orders Purge Sales Orders

Purge Completed Deals Purge Completed Deals

Archive/Purge Bank Statements Program – Archive/Purge

Order Purge Selection Order Purge Selection

Order Purge Order Purge

Purge Margin Analysis Run Purge Margin Analysis Run

WIP Purge WIP Purge

Payables Open Interface Purge Payables Open Interface
Purge

Purge Demand Interface Data Purge Demand Interface
Data

GL Archive and Purge Child Program Archive and Purge Child
Program

Purge Purchasing Open Interface Processed Data Purge Purchasing Open
Interface Processed Data Program

New Archive and Purge New Archive and Purge

Call New Archive and Purge Call New Archive and
Purge

Purge Credit Usages Purge Credit Usages

Not able to connect to Apps user in Database due to library cache locks

If there are many applications integrated with EBS then this issue arises, when we change apps password but the integrated applications keep trying to connect with old password causes library cache locks in the Database.

This prevent apps user from connecting to database.This scenario also arises when EBS instance is refreshed.
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.

We can observe  'Library cache lock' or 'row cache lock' when concurrent users login with wrong password to the database.
'row cache lock' is seen in 10.2 and 11.1
'library cache lock' is seen in 11.2.

Solution

1. Check for bad or incorrect password or login attack by running following sql:

select username,
os_username,
userhost,
client_id,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp < sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);

 
2. Set the below event in the spfile or init.ora file and restart the database:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

REF:Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

Script for Changing Oracle Application Users Password

 We can use the below API for changing the EBS(fnd_user) user password.

 
 apps.fnd_user_pkg.changepassword
 
Sample Script:
 
DECLARE
   v_user_name      VARCHAR2 (100) := 'HSINGH';
   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status         BOOLEAN := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);
 
   COMMIT;
   DBMS_OUTPUT.put_line (
      'Password is changed successfully for the user=> ' || v_user_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while restting password for user and the Error Detail is '
         || SQLERRM);
END;
 
The script can also be modified to reset bulk ebs user passworda. The below is a sample script, You need to modify as per you need.
 
Refer below syntax
 
DECLARE
   v_status   BOOLEAN;
   CURSOR user_name_list
   IS
      SELECT user_name
        FROM fnd_user
       WHERE     NVL (end_date, SYSDATE + 1) > SYSDATE
             AND user_name NOT IN ('SYSADMIN',
                                   'GUEST',
                                   'XML_USER',
                                   'APPSMGR',
                                   'PORTAL30',
                                   'PORTAL30_SSO');
BEGIN
   FOR user_name_list IN c_user
   LOOP
      BEGIN
         v_status :=
            fnd_user_pkg.ChangePassword (
               username      => user_name_list.user_name,
               newpassword   => 'welcome123');
      --  dbms_output.put_line('password successfully changed for' || user_name_list.user_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Error encountered while resetting password for users and the Error Detail is '
               || SQLERRM);
      END;
   END LOOP;
END;

Some Useful UNIX Commands

 1.Deleting files older than N number of days ? 

find . -name '*.trc' -mtime +[N in days]  -exec rm {} \;  
Command will delete files older then N days in that directory

2.Copying file keeping timestamp preserve

cp -ip file.txt file_txt_bkp

3.Listing files modified in last N days

find . -mtime -<ndays> -exec ls -lt {} \;

4.Sorting files based on Size of file ?

ls -l | sort -nrk 5  | more
du -sm *|sort -nr|head -10

5.Finding CPU & Memory detail 

->cat /proc/cpuinfo  (CPU)
->cat /proc/meminfo (Memory)
->topas

6.Finding if any service is listening on particular port or not ?

netstat -an | grep {port no} 

7.Finding Process ID (PID) associated with any port ?

lsof | grep {port no.} 
lsof should be installed and in path.Many times it will be installed eith root user.Make sure you have that permission.

9.Finding a pattern in some file in a directory ?

grep pattern  file_name  ( find pattern in particular file )
grep -i pattern  file_name (find pattern ignoring the case)

10.Create symbolic link to a file ?

ln -s  pointing_to_original_file  symbolic_link_name

example
 ln -s /home/text.txt test.txt

11.History of command executed in the unix box

fc -l
fc -e - ls ( Would execute the last ls command.) 
History command will also do the same
history

12. Compressing a bigfile

zip -r new.zip new
or
compress file_name

13.Creating a tar file

tar -cvwf file.tar myfile.txt

14.Extracting the files from a tar file

tar -xvwf myfile.tar(System would unarchive (untar) the myfile.tar file into the current directory.)

tar -xvwzf myfile.tar.gz(System would unarchive (untar) the myfile.tar.gz file in the current directory.)

14.Extracting a gz file?

Use guzip command as follows:
gunzip file.gz
OR
gzip -d file.gz

15.Extract a tar.gz or .tgz file?

Files with extension tar.gz or .tgz are tar files compressed with gzip. On Unix system extract them with following command:
gunzip < file.tar.gz | tar xvf -
gunzip < file.tgz | tar xvf -

If you have GNU tar (Linux system) you can use the z option directly:
tar xvzf file.tar.gz
tar xvzf file.tgz
 
16.Using mailx command

echo "This is going to be body of the mail" |mailx -s "Subject:Testing" "false@gmail.com"

17.Checking the folders size & used space

df -h