Changing the APPS password in 11i

Steps to change apps password 

1. Backup entire system
2. Stop concurrent manager

  cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME

  adcmctl.sh stop apps/<apps password>

3. Backup FND_ORACLE_USERID table

  sqlplus apps/<apps password>

  drop table FND_ORACLE_USERID_BAK; (if there is any table like this)

  create table FND_ORACLE_USERID_BAK as select * from FND_ORACLE_USERID; 

  exit;

4. Change apps password

  $FND_TOP/bin/FNDCPASS apps/<old apps password> 0 Y system/<system password> SYSTEM APPLSYS <new apps password>

5. Change apps password in configuration files

  $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app

  $IAS_ORACLE_HOME/listener/cfg/wdbsvr.app

  $ORACLE_HOME/listener/cfg/wdbsvr.app

  $FND_TOP/resource/wfmail.cfg

  $OA_HTML/bin/appsweb.cfg

  $AD_TOP/admin/template/CGIcmd.dat

Known Issues 

1. Symptom : Can not login to Oracle Applications

  Cause   : Invalid apps password in $IAS_HOME/Apache/modplsql/cfg/wdbsvr.app

  Solution: Change apps password in $IAS_HOME/Apache/modplsql/cfg/wdbsvr.app

How do we make trace file readable by using TKPROF

How do we make trace file readable by using TKPROF?

TKPROF is Oracle utility to convert the Orace trace file into readable format. It is one of the most useful utility available to DBAs for diagnosing performance issues

TKPROF syntax is:

tkprof filename1 filename2 [waits=yes/no] [sort=option] [print=n]
[aggregate=yes/no] [insert=filename3] [sys=yes/no] [table=schema.table]
[explain=user/password] [record=filename4] [width=n]

Here is the example to use the TKPROF utility.

tkprof dmdprod_ora_11154.trc traceoutput.txt sys=no

TKPROF command line options.
---------------------------------------
Print – This option will list number SQL statement in trace file. We can use this command when we want to list only specific number sql statement. This command is very useful when combining SORT option to list only top N statement by CPU, disk access etc.

Aggregate – When it is YES, then combine the statistics from multiple user execution for the same SQL statement.

Waits - It is used to record summary for any wait events found in the trace file

Insert – Create file to load the trace info into table for further processing. This will be useful for future analysis.

Sys – If it is NO, then it will exclude SQL statement which is executed by SYS schema. The default value is YES.

Table – It is used to load the explain plan info into table. We need to specify the schema name and table name to load the explain plan info. The table old data will be deleted when we use this option. If the table does not exist, then oracle will create the table to load the data.

Record – It is used to store all the non recursive SQL statement from the trace file. This statement can be used if we want to log the SQL statement in separate file.

Explain – It will be used if we want to generate the pre determined execution plan in trace file. When you use this option, you will see execution plan as well as Real plan in trace file. Execution plan is pre execution plan(like asking direction to home), Real Plan(like showing direction how you got home. In trace file, Real Plan will be displayed as Row source generation).

Sort – It will be useful if we want to see the top SQL which are consumed the most resources. Resource like CPU usage, disk usage etc.

The following are the data elements available for sorting:

. prscnt – The number of times the SQL was parsed.
· prscpu – The CPU time spent parsing.
· prsela – The elapsed time spent parsing the SQL.
· prsdsk – The number of physical reads required for the parse.
· prsmis – The number of consistent block reads required for the parse.
· prscu – The number of current block reads required for the parse.
· execnt – The number of times the SQL statement was executed.
· execpu – The CPU time spent executing the SQL.
· exeela – The elapsed time spent executing the SQL.
· exedsk – The number of physical reads during execution.
· exeqry – The number of consistent block reads during execution.
· execu – The number of current block reads during execution.
· exerow – The number of rows processed during execution.
· exemis – The number of library cache misses during execution.
· fchcnt – The number of fetches performed.
· fchcpu – The CPU time spent fetching rows.
· fchela – The elapsed time spent fetching rows.
· fchdsk – The number of physical disk reads during the fetch.
· fchqry – The number of consistent block reads during the fetch.
· fchcu – The number of current block reads during the fetch.
· fchrow – The number of rows fetched for the query.

TKPROF output

update COLLECTION set body=:1, control_no=:2, STATE=:3,
created_dt=:4, author=:5
where
id=:6
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- -------------------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 55.99 145.55 1057 9495719 8238 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- -------------------
total 2 55.99 145.56 1057 9495719 8238 1

Misses in library cache during parse: 1

Optimizer goal: FIRST_ROWS
Parsing user id: 67
Rows Row Source Operation
------- --------------------------------------------------------------------------------
1 UPDATE
1 TABLE ACCESS BY INDEX ROWID MWS_COLLECTION
1 INDEX UNIQUE SCAN PK_MWS_COLLECTION (object id 139890)
********************************************************************************

1. Parse – Parse does syntax checks, permissions, and all object dependencies.
2. Execute – The actual execution of the statement.
3. Fetch – The number of rows returned for a SELECT statement

count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call

V$SQLPLAN Versus EXPLAIN PLAN

EXPLAIN PLAN and v$sql_plan are providing same type of information. EXPLAIN PLAN command is used to display the execution plan of a SQL statement without actually executing it. But v$sql_plan dictionary view is used to show the execution plan of the SQL statement that has been compiled into a cursor in the cache.

What should DBA look in tkprof output file?
1. Compare the number of parses to number of executions. A Well defined system will have one parse per n executions.
2. Check any SQL do not use bind variables
3. Check if there is any Full table scan, Multiple disk reads, high CPU consumption.

Crontab – Quick Reference

Setting up cron jobs in Unix and Solaris

Cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.  Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.
This document covers following aspects of Unix cron jobs
1. Crontab Restrictions
2. Crontab Commands
3. Crontab file – syntax
4. Crontab Example
5. Crontab Environment
6. Disable Email
7. Generate log file for crontab activity

1. Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands
export EDITOR=vi ;to specify a editor to open crontab file.
crontab -e    Edit your crontab file, or create one if it doesn’t already exist.
crontab -l      Display your crontab file.
crontab -r      Remove your crontab file.
crontab -v      Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file
Crontab syntax :
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

*     *     *   *    *        command to be executed -     -     -   -    - |     |     |   |    | |     |     |   |    +----- day of week (0 - 6) (Sunday=0) |     |     |   +------- month (1 - 12) |     |     +--------- day of month (1 - 31) |     +----------- hour (0 - 23) +------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Notes
A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.
B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30     18     *     *     *         rm /home/someuser/tmp/*
Changing the parameter values as below will cause this command to run at different time schedule below :

min	hour	day/month	month	day/week	Execution time
30	0	1	1,6,12	*	– 00:30 Hrs  on 1st of Jan, June & Dec.
0	20	*	10	1-5	–8.00 PM every weekday (Mon-Fri) only in Oct.
0	0	1,10,15	*	*	– midnight on 1st ,10th & 15th of month
5,10	0	10	*	1	– At 12.05,12.10 every Monday & on 10th of every month
:

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment
cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user’s-home-directory
LOGNAME=user’s-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh
Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email
By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .
>/dev/null 2>&1

7. Generate log file
To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log