ADPATCH Part-2

1. Using the adctrl utility, shutdown the workers. a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"
2. Backup the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema
sqlplus applsys/apps
create table fnd_Install_processes_back as select * from
fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
3. Backup the .rf9 files located in $APPL_TOP/admin//restart directory. At this point, the
adpatch session should have ended and the cursor should be back at the UNIX prompt.
cd $APPL_TOP/admin/DEVL
mv restart restart_back
mkdir restart
4. Drop the FND_INSTALL_PROCESSES table and the AD_DEFFERED_JOBS table.
sqlplus applsys/apps
drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;
5. Apply the new patch.
6. Restore the .rf9 files located in $APPL_TOP/admin//restart_back
cd $APPL_TOP/admin/DEVL
mv restart restart_3263588
mv restart_back restart
7. Restore the FND_INSTALL_PROCESSES/AD_DEFERRED_JOBS tables which is owned by the
APPLSYS schema.
sqlplus applsys/apps
create table fnd_Install_processes as select * from
fnd_Install_processes_back;
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;
create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;
8. Re-create synonyms
sqlplus apps/apps
create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;
9. Start adpatch, it will resume where it stopped previously.



How do you determine what patch set you are currently utilizing?
select pa_server_pkg.get_patch_level from dual;

• Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/ 
  
  select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = '' 
  
  To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id 
  
  select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3; 
  
  To find the base application version  
  
  select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y' 
  
  
   To find all available application version 
  
  select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES 
  
  
  To get file version of any application file which is changed through patch application
  
  select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME 
  
  To get information related to how many time driver file is applied for bugs
• select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = '' 
  
  To find latest patchset level for module installed
  
  select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME 
  
  To find what is being done by the patch
  
  select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE 
  
  
  To find Merged patch Information from database in Oracle Applications
  
  select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) ); 
  
  Second Query to know, what all has been done during application of PATCH
  
  Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
  AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and 
  B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID 
  and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and 
  I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and 
  G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
  in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
  GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR, 
  D.FILENAME, E.ACTION_CODE 
  
  
  Script to find out Patch level of mini Pack
  
  Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%'; 
  
  Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex. 
  AD,PA,…
• Script to find the patch timeings
  
  SELECT to_char(drivers.LAST_UPDATE_DATE, 'yyyy-mm-dd') install_date,
           bug.BUG_NUMBER,
           patches.patch_type,
           drivers.PLATFORM,
           langs.language,
           drivers.DRIVER_FILE_NAME,
           to_char(runs.start_date, 'HH24":"MI":"SS') start_time,
           to_char(runs.end_date, 'HH24":"MI":"SS') end_time,
           to_char(to_date('00:00:00','HH24:MI:SS') + (runs.end_date - runs.start_date), 'HH24:MI:SS') time_difference,
           (to_number(to_char(to_date('00','HH24')  + (runs.end_date - runs.start_date), 'HH24'))*60) +
           (to_number(to_char(to_date('00','MI')    + (runs.end_date - runs.start_date), 'MI'))) +
           (to_number(to_char(to_date('00','SS')    + (runs.end_date - runs.start_date), 'SS')) / 60) time_dif_min
    FROM apps.ad_bugs bug,
         apps.ad_patch_drivers drivers,
         apps.ad_patch_driver_langs langs,
         apps.ad_applied_patches patches,
         apps.ad_patch_runs runs
    WHERE langs.patch_driver_id = drivers.patch_driver_id AND
          runs.patch_driver_id=drivers.patch_driver_id AND
          patches.APPLIED_PATCH_ID=drivers.APPLIED_PATCH_ID AND
          patches.patch_name=bug.bug_number AND
          drivers.orig_patch_name = 'bug_' || bug.bug_number
          and bug_number like '%patch_number%'
    ORDER BY install_date ASC, start_time ASC;
  
  

to merge the patch

admrgpch -s /patches/madhu/M2/source -d /patches/madhu/M2/dest -merge_name M2P

Script to find long running requests

#!/bin/ksh
echo "Program Start Date: " `date`

if [ `whoami` != 'oracle' ]
then
echo "Error: You must be oracle to execute the script. Exiting."
exit
fi


########################################################################
# Get the list of programs running for more than 4 hours
########################################################################

export ORACLE_HOME=/opt/oracle/products/9.2.0.x
export ORACLE_SID=ERPPROD
export PATH=$ORACLE_HOME/bin:$PATH

ORA_TEST=`sqlplus "/as sysdba" <set linesize 200
set pagesize 60
set feed off
set head off
SELECT 'Concurrent Program: '||t.user_concurrent_program_name||' ,With Req ID: '||r.REQUEST_ID||' ,Started at: '||to_char(v.actual_start_date,'dd/mm/yy hh24:mi:ss')||' ,With Parameters: '||v.argument_text||' ,Running for: '||round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24))||'Hours'
FROM apps.fnd_concurrent_requests r , apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl t, apps.fnd_conc_req_summary_v v
WHERE r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate -1)
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.status_Code='R' and r.phase_code='R'
AND v.request_id=r.request_id
AND round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24)) > 4
order by v.actual_start_date
.
spool $ORACLE_HOME/rdbms/log/LongRunningJobs.lst
/
spool off
exit
EOF`

JobExists=`grep "Concurrent Program" $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | wc -l `

##########################################################################
# Send mail if there are programs which are running for more than 4 hours
##########################################################################


if [ $JobExists -ne 0 ]
then
cat $ORACLE_HOME/rdbms/log/LongRunningJobs.lst | /usr/bin/mailx -s "Long Running Jobs in ERPPROD" ...
fi

STATS PACK

•Statspack collects more data, including high-resource SQL.
•Statspack precalculates many ratios useful when performance tuning, such as cache hit ratios, rates, and transaction statistics. (Many of these ratios must be calculated manually when using BSTAT/ESTAT).
•Permanent tables owned by PERFSTAT store performance statistics. Instead of creating/dropping tables each time, data is inserted into the preexisting tables. This makes it easier to compare historical data.
•Statspack separates data collection from report generation. Data is collected when a snapshot is taken. The performance engineer then runs the performance report and views the data collected.
•Data collection is easy to automate using either DBMS_JOB or an operating system utility.
•Statspack considers a transaction to finish either with a COMMIT or a ROLLBACK, and so calculates the number of transactions as 'user commits' + 'user rollbacks.' BSTAT/ESTAT considers a transaction to complete with a COMMIT only, and so assumes that transactions = 'user commits.' For this reason, comparing statistics for each transaction between Statspack and BSTAT/ESTAT can result in significantly different ratios.
The installation steps for STATSPACK are relatively simple and straightforward. This chapter will walk you, step by step, through the installation and configuration of STATSPACK. We will be including a complete description of the following steps:
•Creating the perfstat tablespace
•Creating the perfstat user
•Creating the tables
•Installing the packages
•Collecting data
•Scheduling data collection snapshots with dbms_job
•Testing your installation
Let's begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy:
•spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts:
•spcsr.sql This script creates a user called PERFSTAT with the required permissions.
•spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user.
•spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures.
•spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour.
•spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts:
•spdusr.sql This script drops the PERFSTAT user.
•spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility.
•spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report.
•sprepins.sql This is the actual SQL that produces the STATSPACK report.
•sppurge.sql This is a script to delete older unwanted snapshots.
•spuexp.par This is a export parameter file to export all of the STATSPACK data.
•sptrunc.sql This is a script to truncate all STATSPACK tables.
•spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script.
•spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6

How to run the report
sql >variable snapno number; (eg :run the script at 6 clk )
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 1 if you are executing the
statspack procedure in the database for the first time..)

runit again as per time required time ( run it at 7 clk)
sql > begin :snapno:=statspack.snap; end;
sql>print snapno; ( this may give the value like 2 )

Now we have two snap numbers (these are called as begin and end snap numbers
which you will be entering when you are running the spreport.sql.)

sql >oracle_home/ora90/rdbms/admin/spreport.sql;

this will ask you to enter the begin and end snap numbers.(enter as 1 and 2
for the begin and end snap numbers..)

How does one determine the table and column name for a field displayed in a form

1. Use Help > Record History
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Record History
- The name of the base view used displays as Table Name:
- Go to the Sql*Plus
- Type desc , for example, desc mtl_system_items_fvl
- The columns of the table are displayed
- Review the columns and see if one matches the field that you are reviewing

2. Use Help > Diagnostics > Examine
- Query the records in a given form
- Place the cursor on a field that you are interested in
- Choose Help > Diagnostics > Examine
- The block and field name that contain the data in the form are displayed as
well as the value in the field.
- Many times the block name is similar to the table name in the database
- Many times the field name is similar to the column name in the database
- Also you can go back to the view in #1 and see if the column can be found

3. Use the eTRM
- Find the table name from the record history in #1
- You may still require additional information about the columns
- Goto http://metalink.oracle.com/ > Top Tech Docs > Applications Electronic
Technical Reference Manuals
- Choose your version (ie., 11.5.9)
- Type the view or table name as the search criteria
- Press search
- The view or table appears
- Press the link and get more details and descriptions about the columns in the table

4. Research the view
- Sometimes the steps in #1 and #2 lead you to a view
- Get more details about the view as well as the underlying tables and columns
with the following SQL:
set long 100000
col text format a70
set pages 100
spool view.lst
SELECT VIEW_NAME, TEXT
FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME')
and owner = 'APPS';
spool off

5. Use SYSTEM.LAST_QUERY
- Query the records in a given form
- Choose Help > Diagnostics > Examine > Block = SYSTEM, Field= LAST_QUERY
- The query run to get the data shown is displayed.
- You can look at the various columns and tables used and research further

6. Run a trace
- Open the form that you are interested in but do not run a query
- Choose Help > Diagnostics > Trace > Trace with binds
- The trace file name and location are listed. Note them down
- Query the records
- Immediately shut trace off with Help > Diagnostics > Trace > No Trace
- The trace file name and location are listed
- Go to the database machine and get the trace file
- Create a tkprof of the trace file with the command, tkprof sys=no
- The various queries run are displayed.
- You can look at the various columns and tables used and research further

7. Open the form in Oracle Developer
- Open the form that you are interested in the Oracle Applications
- Choose Help > About
- Note the form name
- You have to first configure Oracle Developer to open forms with their
associated libraries
- Once this is done Open Developer
- Open the form in question
- Review the block, pl/sql procedures and libraries associated to the form

Profile option for change of settings like color etc

Change the "Corporate Branding Image for Oracle Applications / FND_CORPORATE_BRANDING_IMAGE" profile option to achieve this.

Refer to the following notes for more details:

Note: 551795.1 - How to change the default branding on the homepage which shown as "E-Business Suite" ?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=551795.1

Note: 395445.1 - Oracle Application Framework Profile Options Release 12
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=395445.1

Note: 421636.1 - How to replace the default Oracle Logo with a Customized Logo?
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=421636.1

Note: 459350.1 - R12 - Image Size Problem On Login Page
https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=459350.1

Session Trace

How to enable trace at database level?
Ans : set init.ora parameter sql_trace

How to enable trace for a session?
Ans: Alter system set sql_trace=true;
Execute the sql query
Alter system set sql_trace=false;
This will create a trace file at $RDBMS_ORACLE_HOME/admin/contextname/udump with the spid of the current sql session.

How to enable trace for other session?
Ans : exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
Eg: To enable trace for sql session with sid 8
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,true);
PL/SQL procedure successfully completed.

To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(8,121,false);

What is the location of inint.ora ?
Ans : $RDBMS_ORACLE_HOME/dbs

What is that trace files contains and the utiliy used to read them?
Ans : Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

How to find trace file for a given concurrent request id?
Ans : Go to $RDBMS_ORACLE_HOME/admin//udump
Grep “ “ *

How does one enable trace in the Oracle Application screens / forms?
One can enable trace through the forms by using the Help menu, choosing the daignostics menu,
trace and then selecting the appropriate trace for your needs. Most commonly if debugging an error,
you should at least provide trace with binds.
When debugging a performance issue,
you may consider using trace with binds and waits.

For example, the following is the navigation to enable trace in a form:
Goto the Oracle Applications
Login
Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds
A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.
Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.

How does one enable trace for a concurrent program?
A simple way to enable trace in a concurrent program is
to review the concurrent program definition and select trace enabled.
This will enable trace without binds for all users that run the program.

For example, the following steps could be used.
Goto Sysadmin > Concurrent > Program > Define
Query the concurrent program
Check the trace box to enable trace

unix level performane check

vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 28872 8792 8 5 172 142 210 0 24 3 11 17 2 289 1081 201 14 6 80
0 0 0 102920 1936 1 95 1936 302 1264 235 12 1 0 3 240 459 211 0 2 97
0 0 0 102800 1960 0 0 0 0 0 464 0 0 0 0 0 107 146 29 0 0 100

Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.
• procs (Reports the number of processes in each of the following states)
• r : in run queue b : blocked for resources (I/O, paging etc.)
• w : runnable but swapped
• memory (Reports on usage of virtual and real memory)
• swap : swap space currently available (Kbytes)
• free : size of free list (Kbytes)
• page (Reports information about page faults and paging activity (units per second)
• re : page reclaims mf : minor faults pi : Kbytes paged in po : Kbytes paged out
• fr : Kbytes freed de : anticipated short-term memory shortfall (Kbytes)
• sr : pages scanned by clock algorith
• disk (Reports the number of disk operations per second for up to 4 disks
• faults (Reports the trap/interupt rates (per second)
• in : (non clock) device interupts si : system calls
• cs : CPU context switches
• cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
• us : user time si : system time cs : idle time
CPU Usage
sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time %usr %sys %wio %idle
11:57:31 72 28 0 0
11:57:41 70 30 0 0
11:57:51 70 30 0 0
11:58:01 68 32 0 0
11:58:11 67 33 0 0
11:58:21 65 28 0 7
11:58:31 73 27 0 0
11:58:41 69 31 0 0
Average 69 30 0 1

%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle
mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 6 8 0 438 237 246 85 0 0 21 8542 23 9 9 59
0 0 29 0 744 544 494 206 0 0 95 110911 65 29 6 0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU PID USER COMMAND
78.1 4789 oracle ora_dbwr_DDDS2
8.5 4793 oracle ora_lgwr_DDDS2
2.4 6206 oracle oracleDDDS2 (LOCAL=NO)
0.1 4797 oracle ora_smon_DDDS2
0.1 6207 oracle oracleDDDS2 (LOCAL=NO)
etc. etc. etc. etc.

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:
SELECT a.username,a.osuser,a.program,spid,sid,a.serial#
FROM v$session a,v$process b
WHERE a.paddr = b.addr AND spid ='&pid';



tar
The tar command can be used to backup and restore files to another filesystem or an offile storage device:
# Create archive.
cd /u01/app/oracle
tar -cvf /tmp/admin.tar admin

# Restore archive.
cd /tmp
tar -xvf admin.tar
If a full path is used during the archive creation the extract locations are fixed rather than relative. The process is similar when accessing a tape device except the destination is the mounted device:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew

# Create archive.
tar -cvf /dev/rmt/2m /u01/*

# Restore archive.
tar -xvf /dev/rmt/2m
dd
The dd command is similar to the tar command:
# Mount and rewind the tape.
mt -f /dev/rmt/2m rew

# Create archive.
dd if=/u01/app/oracle/* of=/dev/rmt/2m BS=32K

# Restore archive.
dd if=/dev/rmt/2m of=/u01/app/oracle BS=32K
cpio
The cpio command deals with the standard input so filesystem paths must be piped to it:
# Create archive.
cd /u01/app/oracle
find admin | cpio -oc > /tmp/admin.cpio

# Restore archive.
cd /tmp
cpio -idmv < admin.cpio
If a full path is used during the archive creation the extract locations are fixed rather than relative:
find /u01/app/oracle/admin | cpio -oc > /tmp/admin.cpio
vdump, rvdump, vrestore and rvrestore
Full level 0 backup of a local filesystem (/u01) to a local device (/dev/tape/tape1_d6):
/sbin/vdump -0 -u -f /dev/tape/tape1_d6 /u01
Full level 0 backup of a local filesystem (/u01) to a remote device (server2:/dev/tape/tape1_d6):
/sbin/rvdump -0 -u -f server2:/dev/tape/tape1_d6 /u01
Restore a vdump or rvdump archive from a local device (/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/vrestore -xf /dev/tape/tape1_d6 -D /u01
Restore a vdump or rvdump archive from a remote device (server2:/dev/tape/tape1_d6) to a local filesystem (/u01):
/sbin/rvrestore -xf server2:/dev/tape/tape1_d6 -D /u01

Important metalink

1. How to run OATM migration utility ---- Note:404954.1

2. 11.5.10 Oracle E-Business Suite Consolidated Update 2 (CU2)---- Note:316366.1

3. Upgrading Oracle Applications ---- Note:289788.1

4. E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

5. Step by Step Troubleshooting Guide to Solve APPS Invalid Objects ---- Note:113947.1

6. Compatibility Matrix for Export And Import Between Different Oracle Versions ----Note:132904.1

7. ORA-06550 Running Sys.Utl_recomp.Recomp_parallel(1) During ADPATCH or ADADMIN Session ---- Note:362727.1

8. Now Available: Oracle E-Business Suite Release 12.0.4 ---- Note:556312.1

9. Cloning Oracle Applications Release 11i with Rapid Clone ---- Note:230672.1

10.Upgrading Developer 6i with Oracle Applications 11i ---- Note:125767.1

11.The Basics About Report Review Agent (FNDFS) on 11i ---- Note:111383.1

12.How To Use FNDCPASS to Change The Oracle Users, APPS, APPLSYS and Application Module Passwords (INV, AR, AP, etc.) For Applications 11.5 in Unix ---- Note:159244.1

13.USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH) ---- Note:376756.1

14.Sharing the Application Tier File System in Oracle Applications 11i ----Note:233428.1

15.Shared APPL_TOP FAQ ---- Note:243880.1

16.Using a Staged Applications 11i System to Reduce Patching Downtime ---- Note:242480.1

17.Summary of Possible Reasons and Solutions for the Problem Where All Concurrent Requests Stuck in Pending Phase ---- Note:182154.1

18.Release 11i Adpatch Basics ---- Note:181665.1

19.How to Apply an 11i Patch When adpatch is Already Running ---- Note:175485.1

20.How to Create a Custom Concurrent Manager ---- Note:170524.1

21.Concurrent Manager Questions and Answers Relating to Generic Platform ----Note:105133.1

22.E-Business Suite Diagnostics Patch Installation Guide ---- Note:167000.1

23.**** Oracle Applications DBA SQL utilites ------------ 108207.1

Unix commands

How do you find files that are greater than 3GB in HP unix?
find / -size +3000000000c -exec ls -l {} \;
Check all logs for ORA- errors
grep ^ORA- *log |cut -f2 -d"-"|cut -f1 -d:|awk '{print "ORA-" $1}'|sort –u
How to Find out Top 10 Memory consuming processes?
UNIX95=1 ps -eo vsz,pid,args | sort +0n -1 | grep oracle | tail -10
Pass Database Name in places of SID
Find out mount points starts with oracle which exceeds 80%?
bdf|grep -e oracle grep '[7-9].%
How do you delete 3 days old log files?
find /location -name "*.log" -mtime +3 -exec rm -rf{} \;
Eg : find ./ -name "*.req" -mtime +4 -exec ls -ltr {} \;
Display latest 20 largest files/directories in current directory?
du -ka sort -n tail -20
How do you display/remove Specifice Month files in Unix?
rm `ls -l grep Jun awk '{print $9}'`
How do you find the files which contains a specific Word?
find /home/ganesh \( -type f \) -exec grep -l test {} \;

how to find all the soft links?
find . -type l -exec ll {} \; | grep ERPPROD

soft link
ln -s
eg : ln -s /u01/app/oracle/monitor.sql /u01/app/oracle/test.sql
monitor.sql orginal file

eg: ln -s /u01/app/oracle/monitor.sql /u01/app/oracle/test.sql
monitor.sql orginal file

How to get the OS is 32 bit or 64 bit

getconf HARDWARE_BITMODE

How to get the version of the os

Uname –vr
Eg: O/P--- 3 5

3-release
5- version

Important Tables

System Table Description
ALL_ARGUMENTS Arguments in object accessible to the user
ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS Comments on columns of accessible tables and views
ALL_CONSTRAINTS Constraint definitions on accessible tables
ALL_CONS_COLUMNS Information about accessible columns in constraint definitions
ALL_DB_LINKS Database links accessible to the user
ALL_ERRORS Current errors on stored objects that user is allowed to create
ALL_INDEXES Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS Description of LOBs contained in tables accessible to the user
ALL_OBJECTS Objects accessible to the user
ALL_OBJECT_TABLES Description of all object tables accessible to the user
ALL_SEQUENCES Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS Snapshots the user can access
ALL_SOURCE Current source on stored objects that user is allowed to create
ALL_SYNONYMS All synonyms accessible to the user
ALL_TABLES Description of relational tables accessible to the user
ALL_TAB_COLUMNS Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS Columns of user's tables, views and clusters
ALL_TAB_COMMENTS Comments on tables and views accessible to the user
ALL_TRIGGERS Triggers accessible to the current user
ALL_TRIGGER_COLS Column usage in user's triggers or in triggers on user's tables
ALL_TYPES Description of types accessible to the user
ALL_UPDATABLE_COLUMNS Description of all updatable columns
ALL_USERS Information about all users of the database
ALL_VIEWS Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL Database compatible parameter set via init.ora
DBA_DB_LINKS All database links in the database
DBA_ERRORS Current errors on all stored objects in the database
DBA_OBJECTS All objects in the database
DBA_ROLES All Roles which exist in the database
DBA_ROLE_PRIVS Roles granted to users and roles
DBA_SOURCE Source of all stored objects in the database
DBA_TABLESPACES Description of all tablespaces
DBA_TAB_PRIVS All grants on objects in the database
DBA_TRIGGERS All triggers in the database
DBA_TS_QUOTAS Tablespace quotas for all users
DBA_USERS Information about all users of the database
DBA_VIEWS Description of all views in the database
DICTIONARY Description of data dictionary tables and views
DICT_COLUMNS Description of columns in data dictionary tables and views
GLOBAL_NAME global database name
NLS_DATABASE_PARAMETERS Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS NLS parameters of the instance
NLS_SESSION_PARAMETERS NLS parameters of the user session
PRODUCT_COMPONENT_VERSION version and status information for component products
ROLE_TAB_PRIVS Table privileges granted to roles
SESSION_PRIVS Privileges which the user currently has set
SESSION_ROLES Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names
ad_patch_driver_langs to find what all languages patch has ben applied
fnd_web_sec....
AD_APPL_TOPS
AD_APPLIED_PATCHES
AD_BUGS
AD_PATCH_DRIVERS
AD_FILE_VERSIONS
AD_FILES
AD_PATCH_DRIVER_LANGS
AD_PATCH_DRIVER_MINIPKS
AD_PATCH_RUN_BUG_ACTIONS
AD_PATCH_RUN_BUGS
AD_PATCH_RUNS
AD_RELEASES
AD_PATCH_COMMON_ACTIONS ad_program_run_task_jobs

Httpd

$ httpd -version
Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)

There are some core files which control the behavior of the Oracle HTTP Server. Again most of these files are autoconfig managed , so changes to them should be via the Oracle Applications Manager and autoconfig must be run to propagate these changes to the respective config files.

httpd.conf
This is manin config file for the Apache based OHS. It contains all vital configuration information for the OHS like the port on which the server is running, the portocol used etc.A detailed list of derivates supported by the httpd.conf file can be found in the apache documentation. Again as mentioned before this file is also autoconfig managed.

httpds.conf
The httpds.conf file is similar to the httpd.conf. The httpds.conf filers comes into use when apache runs in the Secure Sockets Layer implementation(SSL).Another way make sure if httpds.conf is being used in your environment or not is to open up the apachectl file in the Apache/bin directory and check if the httpds executable is being used along with the httpd executale. something similar to below

HTTPD=/d01/sam/samsora/iAS/Apache/Apache/bin/httpds

access.conf
The access.conf file is natively used to specify the acess control files for the HTTP server. But in Oracle Implementation of Apache server this file is essentially kept empty and instead the AccessConfig directive in httpd.conf is used.

url_fw.conf
The URL firewall or the url_fw.conf file implementation is used to provide an additional layer of security on the external tier.This file comes into play once you define a node as external. After this is enabled only the urls specified in the url_fw.conf files will be accessible through the external tier in a DMZ architecture.Once enabled the httpd.conf file will have a link to include this file.

security.conf
This file contains the security configuration for the OHS.It has information regarding security auditing and also location of the security audit log file location.

oracle-apache.conf
This a file that stores the configuration information for various supported modules in Oracle applications like imeeting , OEM etc.It will contain entries to include these configuration files. These module specific configuration file like the imeeting.conf will also reside in the Apache/conf directory on the application server.

CRONTAB Part 2

Actually it is called 'cron daemon'. Cron is an automatic task machine. You will use it on your Unix or Linux operating systems for doing some tasks at specific intervals with out your intervention every time. You set the clock and forget. The cron daemon runs the work for you.
What is cron tab?
'Cron tab(CRON TABle)' is a text file that contains a series of cron functions.
What cron will do for you?
>>>If you want to send your email cources to your subscribers at 11.30 night, you will set the cron job on your server.And your cron manager sends the one email every day at 11.30 until all the emails will be finished.

If you want to send them on Sundays, you can schedule it with your cron.

>>>You can schedule it to delete your website members with expired accounts.

>>>You can schedule it to recieve an update on your subscribers from your mailing list manager.

>>>You can check your links on other websites in link exchange programms.
Have your webserver cron enabled?
Have you seen any icon like this?

OR

OR
Have you seen a text link like 'Cron Manager' or 'Cron jobs'?
If you see one, then you are cron enabled.
Ask your host about your cron availability. Some host allows cron with out graphical interface. You have to access it through telnet. If you are hosted on Virtual hosting domains then your system administrator has to set up your cron tabs for you.
What are the components of cron?
=Field====Value====Description=
minute====00-59====exact minute the cron executes
hour======00-23====hour of the day the cron executes(0 means midnight)
day=======01-31====day of the month the cron executes
month=====01-12====month of the year the cron executes
weekday===00-06====day of the week the cron executes(Sunday = 0, Monday = 1, Tuesday = 2, and so forth)
command===Special==complete sequence of commands to execute
Examples how to set cron:
If you have installed a cgi script in your cgi-bin directory called members.cgi and wanted to run this program each night as 11.30 PM as in above example.

You would setup the following crontab line:

30 23 * * * /home/username/www/cgi-bin/members.cgi

30--represents the minute of cron work
23--represents the hour of the day
The * represent every day, month, and weekday.

If you want to set the cron job every sunday at midnight 11.30 PM then it would be like:

30 23 * * 0 /home/username/www/cgi-bin/members.cgi
0--represents the Sunday.
If you want the cron job to run at 1:00 and 2:00 A.M then you can set it like:

* 1,2 * * * /home/username/www/cgi-bin/members.cgi

This runs your cron at 1 and 2 A.M every day, every month and every week.

If you want to run the above task only from Monday to Friday then set it like:

* 1,2 * * 1-5 /home/username/www/cgi-bin/members.cgi
Setting up the cron:
You will be in one of these situations.

ONE:This would be your first cron tab function.

SECOND:You already have cron tab file on your server running one or more cron functions for you.

In any of the above two situations you can further proceed by TWO METHODS.
METHOD ONE:Using Cron tab manager:
If you have a cron tab manager in your webpanel, it will be easy for you to set the cron tab function. You can click on the icon to open cron manager.
There will be some box like text area where you can enter the the perl file path that you want to run the cron job.



Your cron manager may be different but basically it is easy to set up cron job with your cron manager. I highly recommend this unless you want to get to know about the Second Telnet Method.
METHOD TWO:Uploading 'cron.txt' file and checking using telnet:
Cron Commands:

crontab filename
Install filename as your crontab file. On many systems, this command is executed simply as crontab filename (i.e., without the -a option).

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.)
How to do it?
Basically FOUR steps:
• Create cron.txt
• Upload
• Install the txt file as cron file with the command 'crontab cron.txt'
• Check your cron file
1. Open your notepad on your computer and write cron job following the guidelines explained above under 'What are the components of cron?'. For example - If you write your cron job like this:

30 23 * * * /home/username/www/cgi-bin/members.cgi

After writing the above cron job PRESS return key so that a blank line will be there below the cron job line.
2. Always use absolute path for the command line. Turn off the 'Word Wrap' feature with the Notepad.
3. Save the file as 'cron.txt'. Upload it to your root directory ('/'). Or ask your webhost where to upload the cron files. Upload in ASCII mode.

4. Now telnet into your server. (Access easy to follow telnet tutorial, cgi tutorial, cgi debugging tutorial, My sql tutorial and many more...)
5. At command prompt type -

crontab cron.txt

Press Enter.

You will be back at command prompt.

Then type (Small L):

crontab -l

Press Enter.

You will see the list of cron jobs that you have entered in cron.txt.

That's it!!
Cron emails you everytime it runs. How to stop it?
Add this tag at the end of your cron job:

>/dev/null 2>&1

Taking the above example your cron job looks like:

30 23 * * * /home/username/www/cgi-bin/members.cgi>/dev/null 2>&1
TIPS in setting up your cron jobs:
>>>Ask your host about the procedure if you are not sure of a thing.

>>>Check your cron once in a while.For example you set your cron to send your ezine at 11.30 PM. Then you subscribe yourself to see it is working or not. Later you can unsubscribe yourself.

>>>If you are creating cron file first time and uploading it to your server, do it in ASCII mode.

>>>If you are setting the script path in your schedule, the path SHOULD be from your hosts server root. NOT your domain path.

Ex:/home/user/www/cgi-bin/scriptname.cgi

is correct.

http://www.yourdomain.com/cgi-bin/scriptname.cgi

is incorrect.

>>>Script names are case sensitive on Unix. So be careful in entering the script name.

>>>If you are creating cron.txt file and uploading, you should not allow spaces within 1-5 components except between them. (For example 10,30 * * * * is correct not 10 ,30 * * * *)

DB Links

Tables dba_db_links, all_db_links, dbms_dblink
Syntax :
CREATE [SHARED] [PUBLIC] DATABASE LINK
CONNECT TO CURRENT_USER
USING '';
Service name
Ex:
conn_link =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = perrito2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orabase)
)
)
EX:
CREATE DATABASE LINK conn_user USING 'conn_link';
Closing the link
Alter session close the database link
Drop the link
Drop database link


set pages 0
spool drop_gcgx_links.sql;
select 'drop database link ' || db_link || ';' from dba_db_links where owner = 'GCGX';
spool off


drop public database link TCAS_QUOTE.WORLD ;
create public database link TCAS_QUOTE.WORLD connect to tcquote identified BY quote45 using 'TCASDEV.world'

ADPATCH

ADPATCH OptionsAfter having talked about the best practices related to applications patching. In today’s post i will talk about some frequently used options with adpatch. It is important to note here that the options described here are for information purposes only and you must understand them completely before using them on your system. Also any special instructions specified in the patch readme file automatically overrides the information cointained in this post.You can execute adpatch by logging in as the applications OS user and sourcing the appropriate environment file.
#su - applmgr $cd SAMAPPL
$. ./APPSORA.env
$adpatch
Running a patch in test modeYou can use the apply clause with adpatch to specify weather to run the patch in TEST mode or not, when you run the patch in test mode it does not do any changes but runs generates a log file with all the actions it would have performed. Applications should be maintenance mode.$adpatch apply=ny The default is apply=y
Pre-install ModeYou can also run a patch in pre install mode, this would be done normally during an upgrade or consolidated update. When a patch is applied in a preinstall mode the all the AD utilities are updated before the upgrade or update.$adpatch preinstall=yThe default is preinstall=n

AutoconfigYou can use the options=noautoconfig top specify autopatch that you do not wish to run autoconfig as a part of the patch application. This can be useful when applying a large number of patches when they are not merged. By default autoconfig is run as a part of adpatch. $adpatch options=noautoconfig

CheckfileThe chekfile option of adpatch tells adpathc to check for already executed exec, SQL, and exectier commands.You can use options=nocheckfile skips this check, however this can cause performance overheds so should be used only when specified.$adpatch options=nocheckfile

Compile DatabaseBy defaulty autopatch compiles the invalid objects after the patch application, in case you wish not to do so you can specify options=nocompiledb along with autopatch.$adpatch options=nocompiledb

Compile JSPBy defaulty autopatch compiles the java server pages (jsp) after the patch application, in case you wish not to do so you can specify options=nocompilejsp along with autopatch.$adpatch options=nocompilejsp

Copy PortionIf you wish adpatch not to execute the commands present in the copy driver portion of the patch you can use the options=nocopyportion.$adpatch options=nocopyportion

Database PortionIf you wish adpatch not to execute the commands present in the database driver portion of the patch you can use the options=nodatabaseportion.$adpatch options=nodatabaseportion

Generate PortionIf you wish adpatch not to execute the commands present in the generate driver portion of the patch you can use the options=nogenerateportion$adpatch options=nogenerateportion

Maintenance ModeIf you wish to apply a patch regardless of the system being in maintenance mode you can use options=hotpatch.$adpatch options=hotpatch

Integrity CheckIf you wish to check the integrity of the patch you can use the options=integrity. Since metalink patches are pre checked for their integrity it is generally not required to do an explicit check and the default value is nointegrity.$adpatch options=integrity

Maintain MRCYou can use the maintainmrc option to specify weather you wish adpatch to execute the Maintain MRC schema as a part of the patch application or not. By default maintain MRC is done for standard patches and is disbaled for tarnslation and documentation patches.$adpatch options=nomaintainmrc

Pre requisite Patch CheckIf you wish adpatch not to check for pre requisite patches before application of the main patch you can use options=noprereq.By default pre requsite checking is enabled.
$adpatch options=noprereq

Validate SchemasIf you wish adpatch to explicitly validate all the registed schems by making a connection you can use options=validate. By default this validation is not performed.$adpatch options=validate

Java ClassesIf you wish adpatch not to copy new java classes from the patch you can use options=nojcopy.By default java classes are copied.$adpatch options=nojcopy

Force CopyBy default adpatch copies the files without check the version of the existing files already present on the system.If you do not wish the newer version of the file to be replaced by the older version contained in the patch use options=noforcecopy.$adpatch options=noforcecopy

RelinkingIf you wish adpatch not do perform relinking you can use options=nolink.$adpatch options=nolink

Generate FormsIf you wish adpatch not to generate the forms files you can specify options=nogenform.$adpatch options=nogenform

Generate ReportsIf you wish adpatch not to generate the report files you can specify options=nogenrep.$adpatch options=nogenrepYou could specify multiple options at the command line using the , delimiter.$adpatch options=hotpatch,nojcopy

ADPATCH Utility Has An OPTIONS=NOREVCACHE. Will a Newer Version Of A Package Be Overwritten By An Older Version? [ID 150964.1]

The filesystem-level version checking will not overwrite a newer version of the 
file with an older version.  Using options=norevcache will cause the same 
packages to be re-created at their current revision, not create a lower 
revision of the packages.

Locations

Scripts location: $COMMON_TOP/admin/scripts/TRIAL_dba2
To start all the servers, script is adstrtal.sh apps/apps
To stop all the servers, script is adstpall.sh apps/apps

Apache
adapcctl.sh start/stop/status
1) adapcctl.sh status
2) ps –ef grep http
3) ps –ef grep –i apache

Conf file $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf
LOG file $IAS_ORACLE_HOME/Apache/Apache/conf/error.log, access.log
JServer
Uses apachectl script
Uses apache commands
$IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties
log file $IAS_ORACLE_HOME/Apache/Jserv/logs/mod_jserv.log
Mod PL/SQL
Uses apache ctl script
Uses apache commands
$IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app
log file $IAS_ORACLE_HOME/Apache/modplsql/log
Apps listener
adalnctl.sh start/stop/status
ps –ef grep lsnrgrep APPS_
invokes FNDFS executable located at $FND_TOP/bin (starts Report Reviewing Agent (RRA) responsible for viewing CM log and output files)
Forms
adfrmctl.sh start/stop/status
1) adfrmctl.sh status
2) ps –ef grep f60
3) ls –of grep
$COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg
log file
$ORACLE_HOME/forms60/log/TRIAL-dba or
$COMMON_TOP/admin/log/TRIAL-dba2
Reports
adrepctl.sh start/stop/status
1) adrepctl.sh status
2) ps –ef grep –i rep
3) ps –ef grep rwmts
$ORACLE_HOME/reports60/server/
CGIcmd.dat
Log file
$ORACLE_HOME/reports60/server/REP60_XX.log
or
$COMMON_TOP/admin/log/xxxx/rep60_xxx.txt
Concurrent Manager
adcmctl.sh start/stop/status apps/passwd
1) adcmctl.sh status apps/pwd
2) ps –ef grep FNDLIBR
Uses startmgr and batchmgr parameters
Located at $FND_TOP/bin

Discoverer
addisctl.sh start/stop/status
1) addisctl.sh status
2) ps –ef grep –i disco
Log file
$ORACLE_HOME/discwb4/util/locator.log
And
Oad.log
And
Osagent.log
Database Tier Scripts:

Scripts location: $ORACLE_HOME/appsutil/scripts/TRIAL_dba2
addbctl.sh stop
addbctl.sh start
ps –ef grep smon
Listener
adlnctl.sh start
adlnctl.sh stop
ps –ef grep tns grep –i

Oracle User

create user alfredo identified by alfredos_secret;
create user alfredo identified externally;
create user alfredo identified globally as 'external_name';
create user alfredoidentified by alfredos_secretdefault tablespace ts_userstemporary tablespace ts_temp;
grant connect to alfredo;
ALTER USER username [ WITH PASSWORD 'password' ] [ CREATEDB NOCREATEDB ] [ CREATEUSER NOCREATEUSER ] [ VALID UNTIL 'abstime' ]

ALTER USER myuser IDENTIFIED BY new_password;
ALTER USER myuser
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp
QUOTA 100M ON users QUOTA 0 ON my_data;
ALTER USER myuser ACCOUNT LOCK;
ALTER USER myuser ACCOUNT UNLOCK;
ALTER USER myuser PASSWORD EXPIRE;
ALTER USER mark WITH PASSWORD 'ml0215em'
ALTER USER mark VALID UNTIL 'Dec 24 2012'
DROP USER myuser CASCADE;

Object privileges allow a user to perform a specified action on a specific object. Other users can access user-owned objects by preceding the object name with the user name (username.object). Object privileges extend down to table columns.
GRANT {object privilege [, ... ] ALL [PRIVILEGES] } ON [schema.] objectTO { { user role PUBLIC } [, ... ] } [WITH GRANT OPTION];

GRANT {object privilege [, ... ] ALL [PRIVILEGES] } [(column [, ... ])] ON [schema.] objectTO { { user role PUBLIC } [, ... ] }[WITH GRANT OPTION];

Only INSERT, UPDATE and REFERENCES privileges can be granted at the column level.
To create users in Oracle whose authentication is done by the operating system or by password files, the DBA will use:

Method 1:
Step 1. Set the initSID.ora parameters as:
remote_os_authent=TRUE os_authent_prefix = "OPS$"
Step 2. Generate a new spfile
CREATE spfile FROM pfile='initorabase.ora';
3. Add the following to the sqlnet.ora
sqlnet.authentication_services = (NTS)
Method 2:
Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:
CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
GRANT create session TO ops$oracle;
Step 2: Create a user in the operating system named oracle if one does not already exist.
Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes).
Method 3:
Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:
CREATE USER "PC100USER" IDENTIFIED EXTERNALLY;
where PC100 is the name of the client computer. Then
GRANT CREATE SESSION TO "PC100USER";
Step 2: Create a user in Windows named USER.
Step 3: Log on Windows as USER and go to the C: command line. Object privileges allow a user to perform a specified action on a specific object. Other users can access user-owned objects by preceding the object name with the user name (username.object). Object privileges extend down to table columns.
GRANT {object privilege [, ... ] ALL [PRIVILEGES] } ON [schema.] objectTO { { user role PUBLIC } [, ... ] } [WITH GRANT OPTION];
GRANT {object privilege [, ... ] ALL [PRIVILEGES] } [(column [, ... ])] ON [schema.] objectTO { { user role PUBLIC } [, ... ] }[WITH GRANT OPTION];
Only INSERT, UPDATE and REFERENCES privileges can be granted at the column level.
To create users in Oracle whose authentication is done by the operating system or by password files, the DBA will use:
Method 1:
Step 1. Set the initSID.ora parameters as:
remote_os_authent=TRUE os_authent_prefix = "OPS$"
Step 2. Generate a new spfile
CREATE spfile FROM pfile='initorabase.ora';
3. Add the following to the sqlnet.ora
sqlnet.authentication_services = (NTS)
Method 2:
Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:
CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
GRANT create session TO ops$oracle;
Step 2: Create a user in the operating system named oracle if one does not already exist.
Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes).
Method 3:
Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:
CREATE USER "PC100USER" IDENTIFIED EXTERNALLY;
where PC100 is the name of the client computer. Then
GRANT CREATE SESSION TO "PC100USER";
Step 2: Create a user in Windows named USER.
Step 3: Log on Windows as USER and go to the C: command line.

Table Spaces

How to space for each schema
select sum(bytes),tablespace_name from dba_free_space where
tablespace_name not like 'TEMP%' and tablespace_name
not like 'RBS%' group by tablespace_name;

How to find the size of the database
select sum(BYTES)/1024/1024 from dba_free_space

Freespace/Largest Ext

select TABLESPACE_NAME,sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent from dba_free_space
group by TABLESPACE_NAME

Objects in SYSTEM TS

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME

SYSTEM TABLESPACE USAGE NOTES:

select USERNAME,CREATED,PROFILE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
from dba_users order by USERNAME

TABLESPACE USAGE NOTES:
select a.TABLESPACE_NAME,a.BYTES bytes_used,b.BYTES bytes_free, b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
( select TABLESPACE_NAME,sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
( select TABLESPACE_NAME,sum(BYTES) BYTES ,max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

TABLESPACE INFORMATION NOTES
select TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,STATUS,CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME
WAIT STATISTIC NOTES
select TABLESPACE_NAME,TOTAL_EXTENTS, EXTENTS_COALESCED, PERCENT_EXTENTS_COALESCED,TOTAL_BYTES, BYTES_COALESCED,
TOTAL_BLOCKS,BLOCKS_COALESCED, PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME

Table space with PCT free

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

FNDLOAD

FNDLOAD Options --

1 - Printer Styles
FNDLOAD apps/apps pwd 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 pwd O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND" LOOKUP_TYPE="lookup name"

FNDLOAD apps/apps pwd O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps pwd 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="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

4 - Key Flexfield Structures
FNDLOAD apps/apps pwd 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="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent program short name"

FNDLOAD apps/apps pwd O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt

SQL> select application_short_name from fnd_application;

6 - Value Sets
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

Select application_id,PROFILE_OPTION_NAME =PROFILE OPTION ID
USER_PROFILE_OPTION_NAME from FND_PROFILE_OPTIONS_VL
Where PROFILE_OPTION_NAME like ‘UPPER %profile option name ‘
Order by PROFILE_OPTION_NAME

FNDLOAD apps/apps pwd O Y $FND_TOP/patch/115/import/afscprof.lct file_name.ldt

9 - Request Groups
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

FNDLOAD apps/apps pwd O Y UPLOAD $FND_TOP/patch/115/import/afcprepqg.lct

10 - Request Sets
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SETAPPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"

SELECT request set name from fnd_requests_sets_vl where user_request_set_name=’user name ‘

FNDLOAD apps/apps pwd OY UPLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt

11 - Responsibilities
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

FNDLOAD apps/apps pwd O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt

12 - Menus
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

Select user_menu_name from fnd_menues_vl where menu_name=’xx’

FNDLOAD apps/apps pwd O Y UPLOAD $FND_LOAD/patch/115/import/afsload.lct file_name.ldt

13 – users
FNDLOAD apps/apps pwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscurp.lct file_name.ldt
FND_USER USER_NAME=”XYX”

Printers / Print queues / Executables Printers / Print queues / Executables.
Roles / Responsibilities / Forms Roles / Responsibilities / Forms.
Menus / Users / Request Sets Menus / Users / Request Sets.
Request Groups / Request Queues Request Groups / Request Queues.
Work shifts / Programs / Libraries Work shifts / Programs / Libraries.
Attachments / Help Files Attachments / Help Files.
Mime Types Mime Types.
Security Information.

Metalink Note id -- 274667.1


.ldt converted data file
.lct configuration file

SQls

Display the current cursor being executed by a session.
select a.sid, a.username, b.sql_textfrom v$session a, v$open_cursor b where a.saddr = b.saddr;

Display the entire SQL statement being executed by a session.
select a.sid, a.username, b.sql_textfrom v$session a, v$sqltext bwhere a.sql_address = b.addressand a.sql_hash_value = b.hash_valueorder by a.sid, a.username, b.piece;

Display each cursor of the shared cursor cache, including the times executed and loaded into the cache.
select sql_text, version_count, executions, loads from v$sqlarea;

Displays licensing information, including the maximum number of licensed concurrent or named users, the current number of active concurrent sessions, and the highest number of users that were concurrently logged on at anyone time since the last database startup.
select sessions_max, sessions_current, sessions_highwater, users_max from v$license;

Display all the init.ora parameters and the current values, along with signifying if the value is the default value.
select name, value, isdefault from v$parameter order by name;

Redo LogsDisplay details on the redo log files, including the filename, size, and archive status. select a.member, b.*from v$logfile a, v$log bwhere a.group# = b.group#;

Display a history of the archive logs created along with the file names.
select * from v$log_history;

Rollback SegmentsDisplay rollback information and determine if more segments are needed. If the waits to gets goes over one, then add more rollback segments.
select a.name, b.extents, b.rssize, b.xacts, b.waits, b.gets, optsize, statusfrom v$rollname a, v$rollstat bwhere a.usn = b.usn;

Determine the number of sessions for each user.
select username, count(*) from v$sessiongroup by username;
Display detail information about a session, including the operating system username and process, and terminal.
select sid, username, program, osuser, process, machine, terminal, typefrom v$session;

Display detailed statistics per session to determine a session’s resource usage.
select a.sid, a.username, b.name, c.valuefrom v$session a, v$statname b, v$sesstat cwhere a.sid = c.sidand b.statistic# = c.statistic#and a.username = upper(‘&username’)order by a.sid, a.username, b.name;

Determine the objects that are being accessed by each session.
select a.sid, a.username, b.owner, b.object, b.ob_typfrom v$session a, v$access bwhere a.sid = b.sid;

Display the processes for each session. If the process is a background process, then the operating system and identifier are retrieved from the process table.
select a.sid, decode(b.background,1,b.program,a.username) "user", b.pid,decode (b.background,1,b.spid,a.audsid) "os id"from v$session a, v$process bwhere a.paddr = b.addr;

How to get the Patch set level
Select patch_level from fnd_product_installation where patch_level like ‘%AD%’;

SELECT a.sid, a.username, b.sql_textFROM v$session a, v$sqltext bWHERE a.sql_address = b.addressAND a.sql_hash_value = b.hash_value
ORDER BY a.username, a.sid, b.piece

The following query can be used to determine all objects that are currently being accessed by a user.
SELECT a.sid, a.username, b.owner, b.object, b.ob_typFROM v$session a, v$access bWHERE a.sid = b.sid

What Indexes Are Available on a Table?
SELECT index_name, column_name, column_ positionFROM all_ind_columnsWHERE table_name = ‘ACCOUNT’ORDER BY index_name,column_position

How to get the responsibility of the users

SELECT UNIQUE u.user_id, u.user_name,r.responsibility_name,a.application_name FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and user_name like 'userid'
How to get concurrent request ... sid
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.phase_code = 'P'
AND a.request_id = xxxxxxxxxx;

select REQUEST_ID , PHASE_CODE , STATUS_CODE, CONCURRENT_PROGRAM_ID, PARENT_REQUEST_ID ,ORACLE_PROCESS_ID,
ORACLE_SESSION_ID, OS_PROCESS_ID,QUEUE_ID from fnd_concurrent_requests where REQUEST_ID=55243326;
How to kill the concurrent request
update fnd_concurrent_requests set status_code ='D', phase_code = 'C' where request_id= request number;
How to find the version of WF
select TEXT from WF_RESOURCES where NAME='WF_VERSION';

LocK
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SELECT s.sid,s. serial#, s.last_call_et, s.MODULE, s.status,
s.machine,p. spid, s.osuser FROM v$process p, v$session s
WHERE p.addr = s.paddr and s.module like '&MODULE' and
s.last_call_et>3600;

select SID,SERIAL# ,LAST_CALL_ET,MODULE,STATUS from v$session where LAST_CALL_ET>3600 and MODULE='&MODULE';
select * from v$lock where LMODE=6 and block=1 order by sid;

select * from sys.dba_dml_locks order by session_id;
dead lock.... select * from v$lock where lmode>0 and id1 in (select distint id1 from v$lock where request >0)

fnd_concurrent_program_vl

select s.SID,s.SERIAL#,l.TIME_REMAINING,s.SQL_HASH_VALUE from v$session_longops l,v$session s where s.sid=l.sid and s.serial#=l.serial# and s.sid=$sid; .... to get the time for the query

select sum(BYTES/1024/1024) from dba_free_space where TABLESPACE_NAME like %XYZ%';
to get the free space

select sum( BYTES/1024/1024) from dba_data_files where TABLESPACE_NAME like '%SDBD%';
to get the total space

select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 from dba_data_files where TABLESPACE_NAME like '% BOMX %’
toget the location of the datafile

alter database datafile '/busdata/rp01/eur0/_lgi/oracle/vol24/gld02.dbf ' resize 3072M.... to alter the datafile



How to check the NLS patchs applied or not

SELECT driver.driver_file_name
,TO_CHAR(run.start_date,'DD-Mon HH24:MI:SS') start_date
,TO_CHAR(run.end_date ,'DD-Mon HH24:MI:SS') end_date
,lang.language
FROM ad_patch_runs run
,ad_patch_driver_langs lang
,ad_patch_drivers driver
,ad_applied_patches applied
WHERE run.patch_driver_id = driver.patch_driver_id
AND driver.applied_patch_id = applied.applied_patch_id
AND applied.patch_name = '&p_patch_number'
AND lang.patch_driver_id = driver.patch_driver_id
ORDER BY 1,2,3;



Workfolw
==========
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE, fsc.COMPONENT_ STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_ queue_id( +)
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%';

Check Maintanance Mode
============ ========= =
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

Enable/Disable Maintanance Mode
============ ========= =========
sqlplus -s apps/@$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
sqlplus -s apps/@$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

Find invalid Object Count
============ ========= =
Select count(*) from dba_objects where object_Name= 'INVALID' ;

Compile invalid object
============ =======
Alter package APPS.MSC_CL_PRE_PROCESS compile body;

Compile all invalid objects
============ ========= ==
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Find Applications Release
============ ========= ==
select release_name from fnd_product_ groups;


Find Number of Languages installed in Oracle Applications
============ ========= ========= ========= ========= ====
select nls_language, language_code,installed_flag
from apps.fnd_languages where installed_flag in ('I','B');

Database Name and Version
============ ========= ===
select instance_name, version from v$instance;

Find Number of Nodes in instance
============ ========= ========
select node_name,support_cp from fnd_nodes;


Perl Version
==========
perl -version

Java Version
===========
$AFJVAPRG -version

Apache Server Version
============ ========
$IAS_ORACLE_ HOME/Apache/Apache/bin/httpd -version

Form Server Version
============ =======
f60gen |grep -i version

Reports Server Version
============ =========
rwcon60 |grep -version

Find workflow version
============ =======
exec $FND_TOP/sql/wfver.sql

Find apps password
============ =====
grep password $APACHE_TOP/modplsql/cfg/wdbsvr.app

Find URL of the isntance
============ =========
env |grep -i forms

select profile_option_ value from apps.fnd_profile_option_values
where profile_option_value like '%http%';

How to get the concurrent Manger Name from requested

SELECT b.user_concurrent_queue_name

FROM 

fnd_concurrent_processes a

,fnd_concurrent_queues_vl b

,fnd_concurrent_requests c

WHERE 1=1

AND a.concurrent_queue_id = b.concurrent_queue_id

AND a.concurrent_process_id = c.controlling_manager

AND c.request_id = &request_id 

FNDCPASS

FNDCPASS logon 0 Y system/password mode username new_password
Where MODE is SYSTEM/USER/ORACLE/ALLORACLE (Introduced with patch 4745998

Use this command to change the password of a schema provided by an individual product in Oracle Applications.
FNDCPASS apps/ 0 Y system/ ORACLE Ex: FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
3.To change all ORACLE schema passwords:Use this command to change the passwords of all schemas provided by Oracle Application products.
FNDCPASS apps/ 0 Y system/ ALLORACLE Ex: FNDCPASS apps/apps 0 Y system/manager ALLORACLE welcome
4.To change an Oracle Applications user’s password:You can use this command to change an individual Oracle Applications user’s password.
FNDCPASS apps/ 0 Y system/ USER Ex: FNDCPASS apps/apps 0 Y system/manager USER sysadmin welcome
FNDCPASS apps/gat5s 0 Y system/wynk00p USER st2008 oracle12

Changing passwords frequently helps ensure database security. Oracle Applications provides a command line utility,FNDCPASS, to set Oracle Applications schema passwords. This utility changes the password registered in OracleApplications tables and changes the schema password in the database. This utility can also change user passwords.
Note: You cannot change a schema name, such as APPLSYS or GL, after a product is installed, with FNDCPASS.
Attention: Ensure that the entire Oracle Applications system has been shut down before changing any schema passwords.
All users should log out and the Applications system should be down before running this utility.
If Oracle Applications user passwords are being changed then the relevant users should not be logged in.
Attention: Before changing any passwords, you should make a backup of the tables FND_USER and FND_ORACLE_USERID.
1.To change the APPS and APPLSYS schema password:Use this command to change passwords for schemas that are used by shared components of Oracle Applications.
FNDCPASS apps/ 0 Y system/ SYSTEM APPLSYS Ex: $FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS NEWPASSWORDAttention: Passwords for APPLSYS and the APPS schemas — including the MRC schema — must be the same.If you change the password for one, FNDCPASS automatically changes the others.This command does the following:
1. Validates APPLSYS.2. Re-registers password in Oracle Applications.3. Changes the APPLSYS and all APPS passwords (for multi-APPS schema installations) to the same password.Because everything with a Privilege Level [set to any of (’E', ‘U’, ‘D’)] in the FND_ORACLE_USERID tablemust always have the same password, FNDCPASS updates these passwords as well as APPLSYS’s password.For example, the APPS password will be updated when the APPLSYS password is changed.4. ALTER USER is executed to change the ORACLE password
for the above ORACLE users.
2.To change an Oracle Applications schema password (other than APPS/APPLSYS):Use this command to change the password of a schema provided by an individual product in Oracle Applications.
FNDCPASS apps/ 0 Y system/ ORACLE Ex: FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1
3.To change all ORACLE schema passwords:Use this command to change the passwords of all schemas provided by Oracle Application products.
FNDCPASS apps/ 0 Y system/ ALLORACLE Ex: FNDCPASS apps/apps 0 Y system/manager ALLORACLE welcome
4.To change an Oracle Applications user’s password:You can use this command to change an individual Oracle Applications user’s password.
FNDCPASS apps/ 0 Y system/ USER Ex: FNDCPASS apps/apps 0 Y system/manager USER sysadmin welcome
5.Post Step:
1. Use the FNDCPASS utility to change the password.
FNDCPASS APPS/ 0 Y SYSTEM/SYSTEM ORACLE ALLORACLE USER 2. Update configuration files.If you changed the APPS schema password (and APPLSYS) or the APPLSYSPUB password,update the following configuration files using AutoConfig (do not manually editthese files). They are used when connecting to Oracle Applications.If you changed the APPS (and APPLSYS) password, update the password in these files: iAS_TOP/Apache/modplsql/cfg/wdbsvr.appORACLE_HOME/reports60/server/CGIcmd.dat
If you changed the APPLSYSPUB password, update the password in these files: FND_TOP/resource/appsweb.cfg OA_HTML/bin/appsweb.cfgFND_TOP/secure/_.dbcNote: When changing APPS (or APPLSYS) and APPLSYSPUB passwords, do not restart thesystem until the entire password change process has been completed.
3. Verify the new password.
If you changed the password for APPS (and APPLSYS),restart all concurrent managers, then log on to Oracle Applications to test the new password.
passwd_chg.sh
#!/usr/bin/sh################################################################################# Program ID : passwd_chg.sh# Version : V1.0# Description: EBS 11.5.10 APPS passwd change# Reversion History# Date Version Name Description# ———- ——- ————– —————————————-# 2005.12.8 1.0 Bandari Huang Initial################################################################################
########## Initial Parameter ###############module_pass=”"old_apps_pass=”"new_apps_pass=”"system_pass=”"############################################
echo “GL password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle gl $module_pass
echo “AP password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle ap $module_pass
echo “AR password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle ar $module_pass
echo “FA password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle fa $module_pass
echo “INV password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle inv $module_pass
echo “PO password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle po $module_pass
echo “BOM password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle bom $module_pass
echo “WIP password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle wip $module_pass
echo “HR password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle hr $module_pass
echo “ZPGL password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxgl $module_pass
echo “ZPAP password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxap $module_pass
echo “ZPAR password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxar $module_pass
echo “ZPFA password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxfa $module_pass
echo “ZPINV password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxinv $module_pass
echo “ZPPO password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxpo $module_pass
echo “ZPBOM password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxbom $module_pass
echo “ZPWIP password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxwip $module_pass
echo “ZPHR password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxhr $module_pass
echo “ZPCST password : “$module_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass oracle oralnxcst $module_pass
echo “APPS password : “$new_apps_passFNDCPASS apps/$old_apps_pass 0 Y system/$system_pass system applsys $new_apps_pass
########## wdbsvr.app file update
cp $APACHE_TOP/modplsql/cfg/wdbsvr.app $APACHE_TOP/modplsql/cfg/wdbsvr.app.old
sed -e “s/$old_apps_pass/$new_apps_pass/g” $APACHE_TOP/modplsql/cfg/wdbsvr.app.old > $APACHE_TOP/modplsql/cfg/wdbsvr.app
rm $APACHE_TOP/modplsql/cfg/wdbsvr.app.old
########## CGIcmd.dat file update modified by huangbandari 07-09-19
cp $ORACLE_HOME/reports60/server/CGIcmd.dat $ORACLE_HOME/reports60/server/CGIcmd.dat.old
sed -e “s/$old_apps_pass/$new_apps_pass/g” $ORACLE_HOME/reports60/server/CGIcmd.dat.old > $ORACLE_HOME/reports60/server/CGIcmd.dat
rm $ORACLE_HOME/reports60/server/CGIcmd.dat.old
Note:159270.1, Note:159244.1, Note:398942.1

-->