Apps and Database LOG FILE LOCATION

APPS LOG FILE and File LOCATION

$LOG_HOME= $INST_TOP/logs

Apache Logs in R12

Access Log :  $INST_TOP/logs/ora/10.1.3/Apache/ or $LOG_HOME/ora/10.1.3/Apache
Error Log    :  $INST_TOP/logs/ora/10.1.3/Apache/ or $LOG_HOME/ora/10.1.3/Apache

Concurrent Manager logs and out files in R12

$INST_TOP/logs/appl/conc
OR $LOG_HOME/appl/conc

Forms and Reports related logs (10.1.2 O_H in R12 is equivalent to 806 O_H in 11..5.10.2)

Forms log file    :  $LOG_HOME/ora/10.1.2/forms
Reports Log file :  $LOG_HOME/ora/10.1.2/reports

Startup scripts log file Location

$LOG_HOME/appl/admin

Adpatch Log file / Worker log file / adpatch informative log file i.e adpatch.lgi

$APPL_TOP/admin/SID/log/    e.g SID=PROD

Forms.fmb  Location : $AU_TOP/forms/US/
(All fmb's reside in AU_TOP/forms/US)

Apps Environment file Location : $APPL_TOP
name will be APPSSID_HOSTNAME.env e.g APPSPROD_DEMO.env

Application startup & shutdown scripts Location : $INST_TOP/admin/scripts

Applicaton context file i.e .xml file : $INST_TOP/appl/admin/
e.g SID_HOSTNAME.xml

adpreclone.pl script location     : $INST_TOP/admin/scripts
adcfgclone.pl script location      : $COMMON_TOP/clone/bin


Database log file and File Location

adpreclone.pl script location     : $RDBMS_O_H/appsutil/scripts/SID_HOSTNAME
adcfgclone.pl script location      : $RDBMS_O_H/appsutil/clone/bin

Database listener and database startup and shutdown scripts :

$RDBMS_O_H/appsutil/scripts/SID_HOSTNAME

Database alert.log file location in 11g:

$O_H/admin/SID_HOSTNAME/diag/rdbms/sid/sid/trace
alertlog file format  alter_sid.log

All .sql scripts location :

RDBMSO_H/rdbms/admin

Parameter file location i.e Init.ora or pfile or spfile file :

$RDBMS_O_H/dbs

DBA QUERYING TABLES & VIEWS TIPS

DBA - TIPS (Querying Tables & Views): Every DBA Must Know.

Checking Invalid Objects

SELECT 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
FROM dba_objects
WHERE object_type in ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER','VIEW') and status = 'INVALID' ORDER BY OBJECT_NAME;

Determine the Usuage of tablespaces

SELECT        *

FROM            dba_tablespace_usage_metrics

ORDER BY   used_percent desc;

Determine if the tablespace containing the object is AUTOEXTENSIBLE and has reached MAXSIZE

For Data Files

SELECT     file_name, bytes, autoextensible, maxbytes

FROM       dba_data_files

WHERE     tablespace_name='<tablespace name> ';

For Temp Files

SELECT     file_name, bytes, autoextensible, maxbytes

FROM       dba_temp_files

WHERE      tablespace_name='<tablespace name>’;

Check How Many Datafiles a Tablespace Has or what all datafiles a tablespace contains

SELECT file_name, tablespace_name

FROM dba_data_files

WHERE tablespace_name =’<tablespace name>’;

Find the TABLESPACE of a table.
SELECT tablespace_name
FROM   all_tables
WHERE  table_name = 'OURTABLENAME';

Find the Min Extent, Max Extent, BLOCKS, OWNER, BYTES, INITIAL_EXTENT of a Table        
SELECT *
FROM   dba_segments
WHERE  SEGMENT_NAME = 'FND_CONCURRENT_REQUESTS';        

Find Size of FND_CONCURRENT_REQUESTS TABLE 
SELECT (bytes/1024)/1024 "Size in KB"
FROM   dba_segments
WHERE  segment_name = 'FND_CONCURRENT_REQUESTS';

Query to find which user has locked the table
Select  *
from    dba_waiters --This will give you the sid of the blocker         
OR
You can run script utllockt.sql which is present in $ORACLE_HOME/rdbms/admin.
Running this gives you a nice tree-like report of who's waiting and who's blocking.
OR
SELECT   s.sid,s.serial#, s.username,l.os_user_name,s.terminal,
                 d.object_id,d.object_name
FROM      v$session s, dba_objects d, v$locked_object l
WHERE    s.sid = l.session_id and d.object_id = l.object_id;

Find total number of records in a table
SELECT  COUNT(*)
FROM     tablename;

What is my current session id ?

The username, program, machine, terminal, session id, serial # and more can be found the v$session   view. This view has a column audsid. When you join this coulum to your userenv('sessionid') value, you get the session information for your current session.

The query could be:

SELECT  username, sid, serial#, program
FROM     V$session
WHERE   audsid = userenv('sessoinid');

How to terminate a session ?
ALTER SYSTEM KILL SESSION 'sid, serial#';

Which database am I connected to? As which user?
SELECT  global_name
FROM      global_name;

To find the user, from SQLPLUS
SHOW USER

What is the SGA size ?
SHOW SGA 
Or
 SELECT  *
FROM      V$sga;

Where are my alert log and dump files written ?

The alert file is written to your BACKGROUND_DUMP_DEST. This variable is set in the config.ora   (init.ora) file. You can find the current values of the dump directories from the database. Invoke svrmgrl and connect. Issue command "SHOW PARAMETER DUMP". The SHOW PARAMETER command can be used to find the value of any database parameter value. For example if you want to find the block size and block buffers, issue command "SHOW PARAMETER BLOCK"

How to increase the size of a TABLESPACE ?
ALTER DATABASE DATAFILE 'filename' RESIZE nn M;
OR
You can add more physical data files to the tablespace by

ALTER TABLESPACE <tablespace-name>
ADD DATAFILE 'filename' SIZE nn M;
Make sure you specify the full path name for the filename

Is my database running in Archive log mode?
Where are the archived files written?
ARCHIVE LOG LIST

Purge Concurrent Request and/or Manager Data

Purge Concurrent Request and/or Manager Data 

One of the important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request to purge can be configured  the request data from the FND tables as well as the log files and output files on accumulate on disk.

When the tables FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES reaches above 3000-4000 rows, the performance diminishes.You have to run Purge Concurrent Request and/or Manager Data program on a regular basis depending on the amount of request being run.

Find Size in KB for FND_CONCURRENT_REQUESTS

sql> SELECT (BYTES/1024)/1024 "Size in KB" from dba_segments where SEGMENT_NAME='FND_CONCURRENT_REQUESTS';

O/P   Size in KB
          ----------
          3.65

SQL> SELECT (BYTES/1024)/1024 "Size in KB" from dba_segments WHERE SEGMENT_NAME='FND_CONCURRENT_PROCESSES';

O/P :      Size in KB
              ----------
              10.75

The Purge Concurrent Request and/or Manager Data program job can be used to purge Requests,Mgr Logs

and all requests depending on what is choosen.

Use the following options: Enter=ALL, Mode =Age, Mode Value=15 

How to Purge Concurrent Request and/or Manager Data

Loigin to frontend with sysadmin responsibility:

Navigate :

System Administrator -- Concurrent - Requests

Click on Submit a New Request -- Select Single Request click ok

Enter Name as Purge Concurrent Request and/or Manager Data

Entity - ALL / Request / Manager

ALL - Purges records from database tables that record history information for concurrent requests,concurrent managers, and purges request log files, manager log files, and report output files from the operating system.

Request - Purges records from database tables that record history information for concurrent requests, and purges request log files and report output files from the operating system.

Manager - Purges records from database tables that record history information for concurrent managers, and purges manager log files from the operating system. 

Mode - Age / Count

Age - Enter the number of days for which you want to save concurrent request history, log files, and report output files. The purge program deletes all records older (in days) than the number you enter. For example, if you enter "5", then all concurrent request history, log files, and report output files older than five days is purged.

Count - Enter the number of (most recent) records for which you want to save concurrent request history, log file, and report output files. The purge program starts from the most recent records, retains the number you enter, and purges all remaining records. For example, if you enter "5", then the five most recent concurrent request history records, request log files, manager log files, report output files are saved, and all remaining records are purged.

Mode Value - Value

Mode Value - Enter a value to define the number of days for Mode=Age or the number of records for Mode=Count. The valid values are 1 - 9999999

Then Submit.

The database tables that are affected by running the Purge Concurrent Request and/or Manager Data program are:

FND_CONCURRENT_REQUESTSThis table contains a complete history of all concurrent requests.

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_ARGUMENTSThis 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_PROCESSESThis 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_SUMMARYThis table contains the concurrent program performance statistics generated by the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to compute these statistics.