prompt
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:'
prompt
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT 'Request id: '||request_id, 'Trace id: '||oracle_Process_id, 'Trace flag: '||req.enable_trace, 'Trace Name: '||dest.value||' '||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc', 'Prog. Name: '||prog.user_concurrent_program_name, 'File name: '||execname.execution_file_name||execname.subroutine_name , 'Status :'||decode(phase_code, 'R', 'Running')||' '||'-'||decode(status_code, 'R', 'Normal'), "SID Serial: "||ses.sid||" , "||ses.serial#, "Module : "||ses.module
from fnd_concurrent_requests req,
v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm,
fnd_concurrent_programs_v1 prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id =
prog.concurrent_program_id
and req.program_application_id =
prog.application_id
and prog.application_id =
execname.application_id
and
prog.executable_id=execname.executable_id;
Friday, November 30, 2012
Find out long running Concurrent Requests
Using the below query we can find out which concurrent requests are running from a long time than expected time.
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,a.outfile_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC
The above query gives the spid of the request through which we can pull the respective details
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '2475'
ORDER BY a.spid, c.piece
Then find the reason for the long running and then kill the process through OS level if need
Thursday, November 22, 2012
FNDLOAD
It can be defined as a concurrent program that can move Oracle Applications data between database and text file representations.Or this can be defined as FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.
How it works
Conversion between database format and text file format is specified by a configuration file. Oracle does provide configuration file and it is important to just pass the configuration file name and then just call the loader and leave everything .The schematic visual representation of the loader is like:
What can be done?
These are the extensive list which can be done through FNDLOAD
Concurrent Programs, Executables
Request Groups, Request Sets
Profile Options
Key and Descriptive Flexfields
Menus and Responsibilities
Forms and Form Functions
Attachments
Messages
Value Sets and Values
Lookup Types
User Responsibilities
Printer Definitions
FND Dictionary
Help Configuration
Document Sequences
Concurrent Manager Schedules
what are advantages when using FNDLOAD are :
Because downloaded data is stored in a text file, version administration is possible
There is nothing to worry to go to purchase because..Investment = 0$
No learning curve. this is relief for developer/dbas
Fully supported and recommended by Oracle
Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
Pin-point when something happened and where (database) easily
Your AOL data migration process is now simplified and streamlined.
Some Disadvantages also :
Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
UPLOAD_MODE=REPLACE only for menus
No validation against migrating database/instance sensitive data
The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1¦..] As you can see, some info is needed.- First you will need to know the Apps password.
The mode is either DOWNLOAD or UPLOAD.
The configfile is the file that Fndload needs to download on upload data.
The data file is the output file, in which the downloaded data is written
The entity is the entity you want to download,
Parameters van is passed to download a certain Alert
Modes of Operation
This is important because it would drive the whole flow, and it always be two mode ..Upload and Download
Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=<application_short_name>
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter WHAT IS FNDLOADLoad Options:
Application level OR single entity level download
(Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
Entire OR Partial upload of a data file
(Example) Upload the entire myfile.ldt or just a single enity indicated by - and mode UPLOAD or UPLOAD_PARTIAL
Entity name required for UPLOAD_PARTIAL mode
Custom mode force update
To override Oracle's upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
UPLOAD_MODE= REPLACE (only for menus)
Support for NLS uploads
NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS
Then ..Where is Config File Located
By default Oracle delivers most of configuration files you can use to download certain entities.
Configuration files with extension.lct
On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
Data files with extension .ldt
and..FNDLOAD File Structures is
The config files (.lct) are delivered and maintained by Oracle
It has entity definitions, parent-child relationships and user input parameters identified by :NAME
Downloading a parent automatically downloads all children - (Example) Concurrent Program download
Take a note FNDLOAD File Structures
The data files (.ldt) have both entity definition and the data
It also shows the version and the location of the config file (.lct) that was used
Without the config file, data file is useless
Without the data file, config file is meaningless
..Remember regarding FNDLOAD Files
Key files: .lct and .ldt
You must run the FNDLOADas apps user not as applsys or any other, otherwise you will receive Ora-6550
Both are easily readable, editable and portable
DO NOT MODIFY Oracle's .lct files
Use your favorite editor (I would suggest Textpad or Editplus) to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions faster (Why can't I access that? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
Partial string searches (which value set has Priority something in its where clause? Etc)
Sample Script Code for these Objects :
1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name" 2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name" 3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name" 4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name" 5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" 6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name" 7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name" 8 - Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod" 9 - Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod" 10 - Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set" 11 - Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility 12 - Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name" 13 - Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt 14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER [<USER>]
References:
Oracle Applications Systems Administrator's Guide
Metalink Notes: 117084.1, 228614.1 232029.1 , 274667.1
https://sites.google.com/site/oracledb009/
How it works
Conversion between database format and text file format is specified by a configuration file. Oracle does provide configuration file and it is important to just pass the configuration file name and then just call the loader and leave everything .The schematic visual representation of the loader is like:
What can be done?
These are the extensive list which can be done through FNDLOAD
Concurrent Programs, Executables
Request Groups, Request Sets
Profile Options
Key and Descriptive Flexfields
Menus and Responsibilities
Forms and Form Functions
Attachments
Messages
Value Sets and Values
Lookup Types
User Responsibilities
Printer Definitions
FND Dictionary
Help Configuration
Document Sequences
Concurrent Manager Schedules
what are advantages when using FNDLOAD are :
Because downloaded data is stored in a text file, version administration is possible
There is nothing to worry to go to purchase because..Investment = 0$
No learning curve. this is relief for developer/dbas
Fully supported and recommended by Oracle
Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
Pin-point when something happened and where (database) easily
Your AOL data migration process is now simplified and streamlined.
Some Disadvantages also :
Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
UPLOAD_MODE=REPLACE only for menus
No validation against migrating database/instance sensitive data
The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1¦..] As you can see, some info is needed.- First you will need to know the Apps password.
The mode is either DOWNLOAD or UPLOAD.
The configfile is the file that Fndload needs to download on upload data.
The data file is the output file, in which the downloaded data is written
The entity is the entity you want to download,
Parameters van is passed to download a certain Alert
Modes of Operation
This is important because it would drive the whole flow, and it always be two mode ..Upload and Download
Example of download:
FNDLOADapps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=<application_short_name>
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter WHAT IS FNDLOADLoad Options:
Application level OR single entity level download
(Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
Entire OR Partial upload of a data file
(Example) Upload the entire myfile.ldt or just a single enity indicated by - and mode UPLOAD or UPLOAD_PARTIAL
Entity name required for UPLOAD_PARTIAL mode
Custom mode force update
To override Oracle's upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
UPLOAD_MODE= REPLACE (only for menus)
Support for NLS uploads
NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS
Then ..Where is Config File Located
By default Oracle delivers most of configuration files you can use to download certain entities.
Configuration files with extension.lct
On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
Data files with extension .ldt
and..FNDLOAD File Structures is
The config files (.lct) are delivered and maintained by Oracle
It has entity definitions, parent-child relationships and user input parameters identified by :NAME
Downloading a parent automatically downloads all children - (Example) Concurrent Program download
Take a note FNDLOAD File Structures
The data files (.ldt) have both entity definition and the data
It also shows the version and the location of the config file (.lct) that was used
Without the config file, data file is useless
Without the data file, config file is meaningless
..Remember regarding FNDLOAD Files
Key files: .lct and .ldt
You must run the FNDLOADas apps user not as applsys or any other, otherwise you will receive Ora-6550
Both are easily readable, editable and portable
DO NOT MODIFY Oracle's .lct files
Use your favorite editor (I would suggest Textpad or Editplus) to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions faster (Why can't I access that? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
Partial string searches (which value set has Priority something in its where clause? Etc)
Sample Script Code for these Objects :
1 - Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name" 2 - Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name" 3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name" 4 - Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name" 5 - Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" 6 - Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name" 7 - Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name" 8 - Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod" 9 - Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod" 10 - Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set" 11 - Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility 12 - Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name" 13 - Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt 14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER [<USER>]
References:
Oracle Applications Systems Administrator's Guide
Metalink Notes: 117084.1, 228614.1 232029.1 , 274667.1
https://sites.google.com/site/oracledb009/
EBS Table types
The different Table-types and names used in Oracle Apps are
_ALL are multi-org tables.
_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
_F these are date tracked tables which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.
_B these are the BASE tables.
They are very important and the data is stored in the these table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.
_V are views.
_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').
_S are sequences, used for finding new values for the primary key of a table.
_A are Audit Shadow Tables.
_AVn and _ACn are Audit Shadow Views (when data was changed, and with what values).
_ALL are multi-org tables.
_TL are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
_F these are date tracked tables which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap.
_B these are the BASE tables.
They are very important and the data is stored in the these table with all validations.
It is supposed that these table will always contain the perfect format data.
If anything happens to the BASE table data, then it is a data corruption issue.
_V are views.
_VL are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').
_S are sequences, used for finding new values for the primary key of a table.
_A are Audit Shadow Tables.
_AVn and _ACn are Audit Shadow Views (when data was changed, and with what values).
Monday, November 19, 2012
Query to get the Application Modules available
select f.application_id,f.application_short_name,f.basepath,fl.application_name
from fnd_application f,fnd_application_tl fl
where fl.application_id = f.application_id and fl.language='US'
group by f.application_id,f.application_short_name,f.basepath,
fl.application_name,fl.language order by f.application_id
/
Sample output would be like
APPLICATION_ID APPLICATION_SHORT_NAME BASEPATH APPLICATION_NAME
0 FND FND_TOP Application Object Library
1 SYSADMIN FND_TOP System Administration
3 AU AU_TOP Application Utilities
40 EMS EMS_TOP Environment Management System
41 PTX PTX_TOP Patch Tracking System
50 AD AD_TOP Applications DBA
60 SHT SHT_TOP Applications Shared Technology
99 OAM OAM_TOP Oracle Applications Manager
101 SQLGL GL_TOP General Ledger
140 OFA FA_TOP Assets
160 ALR ALR_TOP Alert
168 RG RG_TOP Application Report Generator
170 CS CS_TOP Service
172 CCT CCT_TOP Telephony Manager
174 ECX ECX_TOP XML Gateway
175 EC EC_TOP e-Commerce Gateway
177 POS POS_TOP iSupplier Portal
178 ICX ICX_TOP Oracle iProcurement
185 XTR XTR_TOP Treasury
186 QRM QRM_TOP Risk Management
190 AZ AZ_TOP Application Implementation
191 BIS BIS_TOP Applications BIS
200 SQLAP AP_TOP Payables
201 PO PO_TOP Purchasing
202 CHV CHV_TOP Supplier Scheduling
203 AME AME_TOP Approvals Management
204 XLE XLE_TOP Legal Entity Configurator
Friday, October 5, 2012
Unable to authenticate session in Oracle apps R12 : Login Issue
-Clear the Browser cache.-Stop all Browser sessions.
-Start a new Browser session and logon to the Instance.
If not resolved
Check if the APPS_TS_ARCHIVE Tablespace is full.
If not resolved
Have tried clearing the cache (_pages). Have tried recompiling jsps and bouncing however the problem still persists.
Cleared all the Apache and related logs Bounce the Apache Web Server. In order to do so, run the following scripts:
1. adapcctl.sh stop
2. adapcctl.sh start
If not resolved
bring down services of application then complile jsps
cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 2
Run autoconfig on both db and apps tiers
Bring up the services and test login
Cleared internet cache(browser cache)..
Friday, September 7, 2012
To Check whether trace has been enabled on any concurrent request or not
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;
To Check whether Internal Logging is enabled or not
SELECT OWNER,NAME,TYPE FROM DBA_SOURCE WHERE TEXT LIKE '%Internal logging is enabled%';
OWNER NAME TYPE
APPS JAI_CMN_DEBUG_CONTEXTS_PKG PACKAGE BODY
Just show this to your functional team or product support team and they should be able to disable the logging.
To check the Profile options
set linesize 100
set pagesize 999
column today new_value _date noprint
select to_char(SYSDATE,'DD-MON-RR') today from dual;
ttitle CENTER 'Debug and Trace Profile Options Set to YES' -
RIGHT _date ' Page:' FORMAT 999 sql.pno SKIP 2 -
Column app_short format a6 heading "APP"
column optname format a35 heading "PROFILE"
column d_level format a36 heading "WHO HAS IT SET"
column optval format a6 heading "SET TO"
column updated format a10 heading "UPDATED ON"
select distinct
a.application_short_name app_short,
user_profile_option_name optname,
decode(level_id,
10001,'SITE',
10002,'APP : '||a2.application_short_name,
10003,'RESP: '||r.responsibility_key,
10004,'USER: '||u.user_name,
'Unknown') d_level,
profile_option_value optval,
v.last_update_date updated
from fnd_profile_options_vl o,
fnd_profile_option_values v,
fnd_application a,
fnd_application a2,
fnd_responsibility r,
fnd_user u
where (
upper(o.user_profile_option_name) like '%DEBUG%' or
upper(o.user_profile_option_name) like '%TRACE%'
--or upper(o.user_profile_option_name) like '%LOGG%'
)
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
-- Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = 'Y'
order by 2,1,3,4;
Wednesday, August 22, 2012
Oracle Temporary Tablespace
Temporary Tablespace Usage
Oracle uses Temp tablespace for operations like sorting and also used in join operations. A database can
have multiple Temp tablespaces but only one of them can be assigned as
the default tablespace.
So before getting into Temp tablespace monitoring stuff, remember you wont get that information from DBA_FREE_SPACE view. Instead use V$TEMP_SPACE_HEADER as shown below
Finding the default temporary tablespace:
So before getting into Temp tablespace monitoring stuff, remember you wont get that information from DBA_FREE_SPACE view. Instead use V$TEMP_SPACE_HEADER as shown below
Finding the default temporary tablespace:
select property_name,property_value from database_properties where property_name='default_temp_tablespace';
List all temporary tablespaces in the instance:
select tablespace_name, status,contents from dba_tablespaces where contents='TEMPORARY';
Create temporary tablespace:
create temporary tablespace <TEMP NAME> tempfile '/oracle/datafile/tempfile01.dbf' size 1G;
Change the default temporary tablespace of the database:
alter database default temporary tablespace <NEW TEMP NAME>;
Change the default temporary tablespace of the user:
alter user <USER NAME> default temporary tablespace <TEMP NAME>;
Find the Allocated, Used and Free MB in temporary tablespace:
select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name);
Enter value for temp_tsname: TEMP
Find the Allocated, Used and Free MB in temporary tablespace:
select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name);
Enter value for temp_tsname: TEMP
(OR)
select tablespace_name,bytes_used/1024/1024 used_mb,bytes_free/1024/1024 free_mb from v$temp_space_header;
List the datafiles under the temporary tablespace:
select file_name, tablespace_name, bytes/1024/1024 size_mb,autoextensible from dba_temp_files;
Increase temporary tablespace:
To Add: alter tablespace TEMP add tempfile '/oracle/datafile/tempfile02.dbf' size 1G;
To Resize: alter database tempfile '/oracle/datafile/tempfile01.dbf' resize 1G;
Drop the temporary tablespace:
drop tablespace <TEMP NAME> including contents and datafiles;
Identify who is filling up the Temporary Tablespace (ORA-1652)
You have just noticed that the TEMP tablespace is filling up fast.
Now you want to know which user and SQL statement is causing that then use the below mentioned scriptselect s.sid || ',' || s.serial# sid_serial, s.username,
o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, s.status,
o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name
order by 3 desc;
Actually, Oracle performs the sort/hash operations in PGA memory. However, if that sort operation is too large to fit into PGA memory area then it starts using temporary tablespace. If even the temporary tablespace gets all filled up then oracle user will get this error 'ORA-1652: unable to extend temp segment error'. No new sessions or queries would be possible in that case.
Thus, one of the first things you must do is to review the current value set for the PGA_AGGREGATE_TARGET initialization parameter and see if bumping it up will help.
Query to find the duplicate/redundant columns from all tables in a schema in Oracle DB
We can find the same columns appearing in multiple tables of a particular schema
SELECT column_name,data_type,data_length,nullable,table_name
FROM dba_tab_columns
WHERE column_name IN
(
SELECT column_name
FROM dba_tab_columns
GROUP BY
column_name
HAVING COUNT(1) > 1 -- more than one value
)
and owner = 'USER' -- enter the schema name
AND COLUMN_NAME LIKE '%TIME%'
ORDER BY column_name
Monitor progress of currently running RMAN backup
select SID,username, opname,to_char(START_TIME,'dd mm yyyy hh:mm:ss'),TOTALWORK, sofar, (sofar/totalwork) * 100 done,TIME_REMAINING/3600/24,
to_char(sysdate + TIME_REMAINING/3600/24,'dd mm yyyy hh:mm:ss') end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
To check whether the backup has been completed or not?
SELECT end_time, status, session_key,
session_recid, session_stamp,command_id, start_time,
time_taken_display, input_type,
output_device_type,
input_bytes_display, output_bytes_display,
output_bytes_per_sec_display
FROM v$rman_backup_job_details WHERE
end_time = (select max(end_time) from v$rman_backup_job_details)
To stop/kill the Rman Backup
Find out the sid, serial# from the below query
SELECT p.SPID, s.sid, s.serial#,
sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT, sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s,
V$PROCESS p
WHERE s.client_info LIKE 'rman%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR;
once we find the sid and serial#, issue the command on sql prompt
alter system kill session 'sid,serail#'
Query to find if the Datafiles have been backed up or not in the last 24 hours
SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up",
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from v$datafile),
(select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1),
(select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from v$backup_spfile
where completion_time > sysdate - 1);
cfiles "Control Files backed up", spfiles "SPFiles backed up"
FROM (select count(*) numfiles from v$datafile),
(select count(*) dbfiles from v$backup_datafile a, v$datafile b
where a.file# = b.file# and a.completion_time > sysdate - 1),
(select count(*) cfiles from v$backup_datafile
where file# = 0 and completion_time > sysdate - 1),
(select count(*) spfiles from v$backup_spfile
where completion_time > sysdate - 1);
Query to find Tablespace Fragmentation
Use the below query to find out the level of tablespace fragmentation.
select tablespace_name, count(*) free_chunks, decode (round((max(bytes) / 1024000), 2), null, 0,
round((max(bytes) / 1024000), 2)) largest_chunk,
nvl (round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )), 2), 0) fragmentation_index
from sys.dba_free_space
from sys.dba_free_space
group by tablespace_name
order by 2 desc, 1
order by 2 desc, 1
Query to find the Outstanding Database Alerts in Oracle DB
TO know what are the top database Alerts, use the below query
SELECT
To_Char(Creation_Time, 'DD-MM-YYYY HH24:MI')
Creation_Time,instance_name,object_type,message_type,message_level,reason,suggested_action
FROM
dba_outstanding_alerts
ORDER BY Creation_Time;
Queries to find the size of an Oracle database
To find how much space has been allocated to ALL datafiles:
select sum(bytes)/1024/1024 "MB" from dba_data_files;
select sum(bytes)/1024/1024 "MB" from dba_data_files;
Size of the online redo logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
Size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "MB" from dba_temp_files;
select nvl(sum(bytes),0)/1024/1024 "MB" from dba_temp_files;
Size of the control files usage,
select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MB" from v$controlfile;
Find all the details at a time
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MB" from v$controlfile;
Find all the details at a time
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
Heavy load on Server... Find the session details of the user
If the CPU utilization is too high on the Server, then run the below
command at OS level to find the Process Id and then use the same PID in
the next query to find the session details which is consuming the
resources
To find the PID of the top most resource consuming process
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10
%CPU PID USER COMMAND
9.8 22834 oraprod oraclePROD (LOCAL=NO)
9.7 22832 oraprod oraclePROD (LOCAL=NO)
8.9 21278 oraprod oraclePROD (LOCAL=NO)
8.8 21953 oraprod oraclePROD (LOCAL=NO)
8.5 20334 oraprod oraclePROD (LOCAL=NO)
6.7 22610 oraprod oraclePROD (LOCAL=NO)
4.7 18182 oraprod oraclePROD (LOCAL=NO)
43.6 10136 oraprod oraclePROD (LOCAL=NO)
40.4 20247 oraprod oraclePROD (LOCAL=NO)
To find the session details of the user, use the PID from the above command as the input for the below statement
SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);
To find the PID of the top most resource consuming process
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10
%CPU PID USER COMMAND
9.8 22834 oraprod oraclePROD (LOCAL=NO)
9.7 22832 oraprod oraclePROD (LOCAL=NO)
8.9 21278 oraprod oraclePROD (LOCAL=NO)
8.8 21953 oraprod oraclePROD (LOCAL=NO)
8.5 20334 oraprod oraclePROD (LOCAL=NO)
6.7 22610 oraprod oraclePROD (LOCAL=NO)
4.7 18182 oraprod oraclePROD (LOCAL=NO)
43.6 10136 oraprod oraclePROD (LOCAL=NO)
40.4 20247 oraprod oraclePROD (LOCAL=NO)
To find the session details of the user, use the PID from the above command as the input for the below statement
SELECT s.sid, p.spid "OS Pid", s.module, s.process, s.schemaname "Schema", s.username "Username",
s.osuser "OS User", s.program "Program", a.sql_id, substr(a.sql_text,1,550) "SQL Text"
FROM v$session s, v$sqlarea a, v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
and s.sid = (select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid = &p);
Subscribe to:
Posts (Atom)