How to Check Incompatibilities for Current Program From Applications

How to Check Incompatibilities for Current Program From Applications:

Navigation:

Application Developer --> Program --> Incompatibilities.

Imp Queries on Troubleshooting concurrent request / manager issues

Query to check the scheduled concurrent Requests/Programs:

SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, cr.resubmit_interval,
NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
) schedule_type,
DECODE (NVL2 (cr.resubmit_interval,
'PERIODICALLY',
NVL2 (cr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')
),
'PERIODICALLY', 'EVERY '
|| cr.resubmit_interval
|| ' '
|| cr.resubmit_interval_unit_code
|| ' FROM '
|| cr.resubmit_interval_type_code
|| ' OF PREV RUN',
'ONCE', 'AT :'
|| TO_CHAR (cr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcr.class_info
) schedule,
fu.user_name, requested_start_date
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcr
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fcr.release_class_id(+) = cr.release_class_id
AND fcr.application_id(+) = cr.release_class_app_id;


Query to check the duplicated schedules of the same program with the same arguments


SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS (
SELECT 1
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:'
|| cr.description,
cp.user_concurrent_program_name
) NAME,
argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id =
cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME
AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME



Query to check average pending time per request:

Note: Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) / Total requests

A Request can be in Pending state for variety of reasons like conflict with other requests, improperly tuned managers (sleep seconds / cache size / number of managers etc)

We can schedule this script to gather data regularly for historical analysis as we normally purge the concurrent requests regularly.
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END
)
)
* 24
* 60
* 60
)
)
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'), concurrent_queue_name
ORDER BY 2


Query to checking which manager is going to execute a program:

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'



Query to see all the pending / Running requests per each manager wise :



SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5


Query to checking the incompatibilities between the programs:


SELECT a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type,
'P', 'Program',
'S', 'Request set',
'UNKNOWN'
) incompatible_type
FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1,
apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2,
apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US'
AND a2.language = 'US'
AND b1.language = 'US'
AND b2.language = 'US'

Forms URL in R12

Forms URL in R12

When using Forms Servlet Mode:

http://.:/forms/frmservlet
Note: Modify the context variable parameter (s_appserverid_authentication) from SECURE to OFF and run autoconfig, then forms will startup without that authorization error.
When using Forms Socket Mode:
http://.:/OA_HTML/frmservlet
Query to get URL in Applications:

1. select profile_option_value from fnd_profile_option_values where profile_option_value like '%http%';

2. select home_url from icx_parameters;

Query to find Patch Level for a product searching with application Name

Query to find Patch Level for a product searching with application Name(Product)/application (Product) short name

select fav.application_name,fav.application_id,fav.application_short_name,fpi.status,fpi.patch_level from fnd_application_vl fav,
fnd_product_installations fpi
where fpi.application_id=fav.application_id and fav.application_short_name ='&x'

Steps to find password for an application user in oracle applications

Steps to find password for an application user in oracle applications:


1. Create get_pwd package

CREATE OR REPLACE PACKAGE get_pwd AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2; END get_pwd;
/


2. Create get_pwd package body

CREATE OR REPLACE PACKAGE BODY get_pwd AS FUNCTION decrypt ( KEY IN VARCHAR2 ,VALUE IN VARCHAR2 ) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd;
/

3. Query to get password for apps user.

SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL))

4. Query to get password for any application user.

SELECT usertable.user_name , (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD')) FROM DUAL)), usertable.encrypted_foundation_password) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD') ,1 , INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/') - 1 ) FROM DUAL)))) ,usertable.encrypted_user_password) FROM DUAL) AS encrypted_user_password FROM fnd_user usertable WHERE usertable.user_name LIKE UPPER ('&username')

How to change an Oracle password temporarily

In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select username,password from dba_users where username='GEORGE';

USERNAME PASSWORD
-------- ----------------
GEORGE F894844C34402B67

SQL> alter user george identified by welcome123;


Now login with the following credentials: george/welcome(old passwd)
After doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user george identified by values 'F894844C34402B67';

Server usage CMDS in Solaris

Some Imp Cmd's in Solaris:

1. CPU Speed
/usr/sbin/psrinfo -v -- CPU SPEED -- 1800 MHZ

2. RAM Size
prtconf | grep Memory -- RAM Size -- 64 Gb

3. No. of CPU's,O.S.Version,Kernel Version etc.,
uname -X

VLOOKUP function in M.S. Excel with example

http://www.timeatlas.com/mos/5_Minute_Tips/General/Learning_VLOOKUP_in_Excel/

Error while running adbldxml.pl after upgrading database to 11.1.0.7 from 9.2.0.8

Error while running adbldxml.pl after upgrading database to 11.1.0.7 from 9.2.0.8
The following error was encountered while running adbldxml.pl on the database tier to create the context file.


====================================================================
Could not Connect to the Database : ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Connecting to the wish database instance...

Connection paramaters values:
Database server hostname ==> SPINNER.LINKPC.NET
Database listener port ==> 1523
Database SID ==> wish
Database schema name ==> apps

Could not Connect to the Database : ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory


AC-40000: Error: Exception - java.sql.SQLException: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
====================================================================


The problem was that the database was not registered with the listener.




SQL> show parameter local_listener;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string




The value for LOCAL_LISTENER is missing, so automatic instance registration is not taking place
We can correct this by setting the LOCAL_LISTENER parameter:


SQL> alter system set local_listener='(ADDRESS =(PROTOCOL=TCP)(HOST=host.domain.com)(PORT=port_number)(SID=sid_name))';

System altered.

SQL> alter system register;

System altered.


SQL> exit

Check TNS_ADMIN value. This should point to new O-H Path i.e., 11.1.0.6

Now adbldxml runs successfully.

Queries to verify OATM Migration

select migration_status,error_text from FND_TS_MIG_CMDS where migration_status ='ERROR'


select * from all_objects where object_name like 'FND_TS%' and object_type='TABLE'

select * from FND_TS_MIG_STATUS

select * from FND_TS_SIZING

select IS_NEW_TS_MODE from fnd_product_groups (o/p should be “Y”

Query to find type of objects available in a tablespace

select ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments ds,dba_objects do where ds.tablespace_name='TOOLS'

Query to count no. of segments in each t.s.

select tablespace_name,count(*) from dba_segments group by tablespace_name;

Query to get workflow using WFLOAD

WFLOAD apps/W1tty 0 Y DOWNLOAD <<(d/w file)I2PATCARD_prd.wft>> <<(original file) PATCARD>>

tkprof Execution

tkprof orcl_ora_4930.trc report.out SYS=NO EXPLAIN=SCOTT/TIGER

Queries to find Database Size

The database mainly comprises of datafiles, temp files and redo log files.
The biggest portion of a database’s size comes from the datafiles.

To find out how many megabytes are allocated to all datafiles:

SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;

To get the size of all TEMP files:

SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

To get the size of the on-line redo-logs:

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

Finally, summing up the three above, total database size can be found:

SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;

Query to find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

Query to find the space occupied by all the users in a database. This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY total_size DESC;

Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

Login to Oracle Applications directly thru f60cgi

Accessing f60cgi is disabled in 11.5.10
----------------------------------------

With increased security in Oracle EBusiness Suite 11.5.10, the ability to
connect directly to forms via f60cgi has been disabled. By default, a user will
see the following error after entering their username and password:

APP-FND-01542: This Applications Server is not authorized to access this database.

This is expected functionality.


Enabling f60cgi direct login
------------------------------

It is possible to login however this method should only be used when
debugging problems.

1. Backup and open $APPL_TOP/admin/_.xml context file

2. Update the context variable:
s_appserverid_authentication

By default in 11.5.10, this is set to SECURE.
In previous 11i versions, this was set to OFF.
For debug purposes, you can use ON or OFF.

Modes:
- ON : Partial
- SECURE : activates full server security (SECURE mode)
- OFF : deactivates server security

3. Run Autoconfig to instantiate the change.

You should now be able to access forms directly again using the f60cgi call.

4. After you have finished your Forms debugging, please reset
s_appserverid_authentication to SECURE and re-run Autoconfig.


Alternative option
---------------------

Running Autoconfig is the preferred method of updating
s_appserverid_authentication.

If you are unable to run Autoconfig during troubleshooting, you can run the
the following commands instead from $FND_TOP/secure directory:

Disable:

java oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION OFF DBC=host_visdemo1.dbc

Enable:

To activate basic server security, from the command line, enter:

jre oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION ON DBC=

To activate full server security (SECURE mode), from the command
line, enter:

jre oracle.apps.fnd.security.AdminAppServer apps/apps \
AUTHENTICATION SECURE DBC=

Check the status:

java oracle.apps.fnd.security.AdminAppServer apps/apps \
STATUS DBC=host_visdemo1.dbc

Reference:
552301.1 How To Access Forms Directly In Oracle Applications R12

Query to find the responsibilty for which form was assigned

SELECT fu.user_name,fu.description,furg.start_date,
frvl.responsibility_name,fff.function_name,ff.form_name
FROM fnd_user fu,fnd_user_resp_groups furg,fnd_responsibility_vl frvl,
fnd_compiled_menu_functions fcmf,fnd_form_functions_vl fff,fnd_form_vl ff
WHERE fu.user_id = furg.user_id
AND furg.responsibility_id = frvl.responsibility_id
AND frvl.menu_id = fcmf.menu_id
AND fff.function_id = fcmf.function_id
AND fff.form_id = ff.form_id
AND (fu.end_date IS NULL OR fu.end_date >= SYSDATE)
AND (furg.end_date IS NULL OR furg.end_date >= SYSDATE)
AND (frvl.end_date IS NULL OR frvl.end_date >= SYSDATE)
AND form_name IN ('FORMNAME')

How to find Components version in R12

Apache Version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v



Perl Version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

OATM Conversion Steps

http://www.scribd.com/doc/6813095/OATM-Conversion

Basic Unix Shell scritps for DBA

http://www.bijoos.com/ora7/oracle_unix.htm

Following are the shell scripts available:

1. Alert Log
2. To ping other servers on the network
3. To tnsping all Oracle databases on the network
4. To compare and verify if all instances mentioned in /etc/oratab is up and running
5. Alert for potential space problems and invalid objects, triggers
6. Script to identify the top 20 longest running oracle sessions
7. Useful if you have multiple versions of oracle on the same box
8. This script may be used as a cronjob to coalesce the tablespaces periodically

and many more

Basic Unix commands 4 DBA

http://www.bijoos.com/oracle/unix4dba1.htm

Queries on maintenance mode

Query to know status of maintenance mode:
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;



Query to ENABLE of maintenance mode:
sqlplus -s &un_apps/***** @/tcme3i/applmgr/1159/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLE


Query to DISABLE of maintenance mode:
sqlplus -s &un_apps/***** @/tcme3i/applmgr/1159/ad/11.5.0/patch/115/sql/adsetmmd.sql DISABLE

Steps to resolve locks on database

Error Message:Could Not Reserve Records Due to Database Record Lock

1. Identify the Oracle serial ID, SID ID and terminate without shutting the
database down.

1.1 Make sure that the user is logged off.

1.1.1 Type ps -ef |grep

1.1.2 Kill all processes related to that user.

1.2 Identify SID, serial#

select distinct
acc.object, ses.osuser, ses.process,
ses.sid, ses.serial#
from v$access acc,
v$session ses
where (acc.owner != 'SYS'
or acc.object = 'PLAN_TABLE')
and acc.sid = ses.sid
and ses.status != 'INACTIVE'
and ses.type != 'BACKGROUND'
and acc.object not in ('V$ACCESS','V$SESSION')
and ses.audsid != userenv('SESSIONID')
order by 1,2,3
/

1.3. Double-check the identified SID and serial ID:

SELECT osuser,
username,
process,
sid,
serial#,
status,
to_char(logon_time,'DD-MON HH24:MI:SS') logon_time,
machine,
program
FROM v$session
WHERE sid = &SID_NUM
/

1.4. ALTER SYSTEM KILL SESSION '&SID_NUM,&SERIAL_NUM';
Alternatively use the following scripts to identify the blocking session:

-- check for locked tables
select a.object_id, a.session_id, substr(b.object_name, 1, 40)
from v$locked_object a,
dba_objects b
where a.object_id = b.object_id
order by b.object_name ;

--find_blocked.sql
select sid,
decode(block ,0,'NO','YES') BLOCKER,
decode(request,0,'NO','YES') WAITER
from v$lock
where request > 0 or block > 0 order by block desc
/

SELECT 'alter system kill session '''||vs.sid||','||vs.serial#||'''' ,al.
object_name, al.object_type, vs.status,
fu.user_name,vs.process,vs.osuser,vs.username,
to_char(vs.logon_time,'DD-MON HH24:MI:SS') logon_time, vs.program
FROM fnd_logins fl, fnd_user fu, all_objects al, v$lock vl, v$session vs
WHERE fl.pid = vl.sid
AND vl.id1 = al.object_id (+)
AND fl.user_id = fu.user_id
AND to_char (start_time, 'DD-MON-RR') = to_char (sysdate, 'DD-MON-RR')
and vs.sid=vl.sid
and vl.sid = &sid

'ALTERSYSTEMKILLSESSION'''||VS.SID||','||VS.SERIAL#||''''


Reference :

Metalink Note ID: 140969.1

Query to find free space in temporary tablesapce:

SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

Script to find tablespace free space in a database

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
and a.tablespace_name='SYSTEM'
ORDER BY a.tablespace_name;

Script to find Table size in a database

Script to find Table size in a database.

select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where
SEGMENT_NAME = 'TABLE_NAME';

Note: Need to execute as owner of the table.

(OR)

select sum(BYTES/1024/1024) as TOTAL_GIG from dba_segments where SEGMENT_NAME='FND_TS_MIG_CMDS'

Default Permissions in Unix

Default Permissions in Unix:

1. For a File -- 644

2. For a Directory -- 755

3. user home directory at the time user creation -- 700

eg: useradd -g dba -d /home/user1

Here directory "user1" have 700 permissions by default.

Best site for DBA Monitoring Scripts

http://www.oracle-base.com/dba/DBACategories.php

Diff bet ad_bugs and ad_appiled_patches tables

Diff bet ad_bugs & ad_appiled_patches tables

AD_BUGS - AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie.patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES - gives information only abt the bugs/patches those we applied after installation
- holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

AutoPatch modes, arguements and options

Filed under: Oracle Application R12, Oracle Applications 11i

Tags: adpatch, adpatch options, patching options

Here are the various options for applying the patch in Oracle apps 11i and R12. ADpatch comes with lots of option that can be used, especially when we are applying the patch in production.

Modes of ADPATCH

If we talk about the mode of applying patch, there are 3 modes

1) Pre-Install Mode

Pre-install mode is used to update AD utilities before an upgrade and to apply family consolidated upgrade packs.
AutoPatch Pre-AutoInstall mode allows you to apply patches when your installation is missing database information and/or filesystem information that AutoPatch requires to run in normal mode.

Examples of when you would run AutoPatch in Pre-AutoInstall mode (and cannot run it in normal mode) include:

Prior to installing Oracle Applications for the first time
Prior to upgrading Oracle Applications to the latest release.
During an upgrade (to apply a bug fix that stopped your upgrade)

Applying patch in pre-install mode performs following tasks:

1.Version checking
2.File copy actions
3.Relink FND and AD executables
4.Save patch history information to file system
5.AutoPatch in pre-install mode will NOT:
6.Run SQL of EXEC command
7.Generate files
8.Read product driver files
9.Apply maintenance pack

To apply patch in pre-install mode, run

$adpatch preinstall=y



2) Test Mode

AutoPatch provides a test mode in which it tells you everything it would have done in applying a patch, but doesn’t actually apply the patch.
To run AutoPatch in Test Mode, you must include ‘apply=no’ on the AutoPatch command line.

For example:
$ adpatch apply=no

Instead of performing an action, AutoPatch indicates that it is not performing the action because “Apply=No”. In general, AutoPatch lists each file it would have copied, generated, relinked, or executed. This shows you exactly what actions it would have performed.

AutoPatch test mode works the same as normal mode, with the following exceptions:

1.It does not copy any files from your patch directory into your installation area.
2.It does not copy any files from your APPL_TOP to JAVA_TOP or OAH_TOP.
3.It does not archive any object modules into your product libraries.
4.It does not generate any forms or reports.
5.It does not relink any executables.
6.It does not run any ’sql’ or ‘exec’ commands.
7.It does not update the release version in the database.
8.It does not update the patch history file.

AutoPatch asks you the same initial questions in test mode as in normal mode. It performs the following actions to determine what it would have done if run in normal mode:

1.Reads and validates the patch driver file.
2.Reads product file driver files.
3.Extracts object modules from your product libraries (so it can perform version checking on the object modules it extracts).
4.Performs version checking.
5.Looks in the database to determine what ’sql’ and ‘exec’ comands it would have run.

Its a good practice to run the patch in test mode and analyze the things before applying the patch in normal mode.

3) Non-Interactive Mode

Starting in Release 11.5, you can run AutoPatch non-interactively.

Creating a defaults file

Before you can run AutoPatch non-interactively, you must first create an AutoPatch defaults file for your current environment.

Here is a simple way to create an AutoPatch defaults file for your current environment:


1. Specify defaultsfile= on the AutoPatch command line. The defaults file must be located under $APPL_TOP/admin/.

For example:
adpatch defaultsfile=$APPL_TOP/admin/testdb1/my_def.txt

2. Run AutoPatch up to the point where it asks you for the directory where your Oracle Applications patch has been unloaded. Then type ‘abort’ at this prompt.

3. Verify that your defaults file exists.
Once you have an AutoPatch defaults file for your current environment, you can run AutoPatch non-interactively.

Applying a single patch driver file non-interactively

Before applying any Oracle Applications patch, either interactively or non-interactively, you should read the README file (usually called readme.txt) supplied with the patch. You should also read the documentation supplied with the patch (if any).

It is possible to apply just a single patch driver file non-interactively using AutoPatch. Here is an example:

Assume the following:

1. defaults file is $APPL_TOP/admin/spin/def.txt
2. Applying copy driver for patch 123456, which is located in directory $APPL_TOP/patch/123456.
3. Using three parallel workers
AutoPatch log file name is cpy123456.log

The AutoPatch command line would be:

adpatch defaultsfile=$APPL_TOP/admin/spin/def.txt \
logfile=cpy123456.log \
patchtop=$APPL_TOP/patch/123456 \
driver=c123456.drv \
workers=3 \
interactive=no

If we dont give any of the mode as mentioned above and apply the patch simply using adpatch command then its a normal mode of patch application.

Having seen the modes of patch application, now we will see various arguements for applying patch.

1) defaultsfile
Purpose: This option is used when we are running the patch in non interactive mode. In that case we create defaults file and provide that file as an option for running patch in non-interactive mode.
Default: none. No default file read or written.

2) logfile
Purpose: This is the name of adpatch log file which it will write during patch application.
Default: none. Adpatch prompts for this value.

3) workers
Purpose: Specifies the number of workers to run. This value depends on number of CPU and other factors.
Default: none. Adpatch prompts for this value.

4) patchtop
Purpose: Top-level directory for the current patch. This is the directory after unzipping the patch. This directory will a patch number.
Default: none. Adpatch prompts for this value.

5) driver
Purpose: Name of the patch driver file. This comes with the patch and is present in patch directory.
Default - none. Adpatch prompts for this value.

6) restart
Purpose: To restart an existing session. Only valid when interactive=no is also specified
Default: No

7) localworkers
Purpose: Used in Distributed AD to specify the number of workers to be run on the current machine. If you have multi node instance (example RAC and shared APPL_TOP), then you can utilize this paramter to run the patch parallely in multiple nodes. You can start few workers on node 1, few on node 2 and so on. The way this can be done is that, you can start adpatch on one node with localworker=. Then run adctrl on other node in distributed mode and start some mode workers. This will speed up the process and utilized the resources effectively.
Default: Value specified for workers.

8. printdebug
Purpose: To display extra debugging information.
Default: No.

Now lets consider some common options that can be used with adpatch options=

1) checkfile
Purpose: To skip running exec, SQL, and exectier commands if they are recorded as already run. Indicates that Autopatch should run the command *only* if a certain file is newer than the version of it that was last run. The idea behind it is to reduce the duration of an Autopatch session by skipping actions that don’t really need to be performed. When used in the right manner, it can dramatically improve Autopatch performance, especially for big patches and/or long running actions.
Default: checkfile (use ‘nocheckfile’ to skip)

2) compiledb
Purpose: To compile invalid objects in the database after running actions in the database driver.
Default: compiledb (use ‘nocompiledb’ to skip)

3) compilejsp
Purpose: To compile out-of-date JSP files, if the patch has copy actions for at least one JSP file.
Default: compilejsp (use’nocompilejsp’ to skip)

4) copyportion
Purpose: To run commands found in a copy driver. This will copy the higher version files from patch to product top.
Default: copyportion (Use ‘nocopyportion’ to skip. Use it only when mentioned in readme of patch)

5) databaseportion
Purpose: To run commands found in a database driver. This portion includes applying the files (like sql, pls etc) to database.
Default: databaseportion (use ‘nodatabaseportion’ to skip. Use it only when mentioned in readme of patch)

6) generateportion
Purpose: To run commands found in a generate driver. This portion will generate new executable files from the copied code of patch. For example if will generate new forms files (fmx) from new .fmb files.
Default: generateportion (use ‘nogenerateporation’ to skip)

7) integrity
Purpose: To perform patch integrity checking. Tells adpatch whether to perform patch integrity checking, which verifies that the version of each file referenced in a copy action matches the version present in the patch.
Default: nointegrity (By default the integrity is not checked)

8. maintainmrc
Purpose: To maintain the MRC schema after running actions found in the database driver.
Default: maintainmrc (use ‘nomaintainmrc’ to skip)

9) autoconfig
Purpose: Tells adpatch to run Autoconfig after patch installation.
Default: autoconfig (use ‘noautoconfig’ to skip)

10) parallel
Purpose: To run actions that update the database or actions (like SQL) that generate files in parallel (like genform).
Default: parallel (use ‘noparallel’ to skip)

11) prereq
Purpose: Tells adpatch whether to perform prerequisite patch checking prior to running patch driver files that contain actions normally found in the copy driver.
Default: prereq (use ‘noprereq’ to skip)

12) validate
Purpose: To connect to all registered Oracle Applications schemas at the start of the patch. Adpatch validates the passwords for each schema.
Default: novalidate (use ‘validate’ to validate schema passwords)

Following flags can be passed to adpatch:

1) hidepw
Purpose: This argument is used to hide the passwords in log files
Default: nohidepw

2) trace
Purpose: Tells the adpatch utility whether to log all database operations to a trace file
Default: notrace

3) logging
Purpose: Tells the adpatch utility whether to create indexes using the logging or nologging mode.
Default: logging

Hope this helps !!

Important tables for ADPATCH

Filed under: Oracle Application R12, Oracle Applications 11i
Tags: ad tables, adpatch tables, etrm

Here are some of the important tables used by and updated by ADPATCH utility.

AD_APPL_TOPS

This table holds the various APPL-TOP’s in the Oracle Applications installation that have ever been patched.


AD_APPLIED_PATCHES

AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.

AD_BUGS

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_PATCH_DRIVERS

This table holds information about the patch drivers that comprise a patch.

AD_FILE_VERSIONS
This table holds the various versions of Oracle Applications files (real files, not “pseudo-files”), that have ever been patched or executed in the Oracle Applications installation.

AD_FILES

AD_FILES is the “files repository”. It contains information about the various files that have been patched in the Oracle Applications installation.
Some entries are “pseudo-files” and not real files, (eg. directories) in which case some of the columns are not applicable and would then hold the value “DUMMY”

AD_PATCH_DRIVER_LANGS

NLS patches (or more specifically, NLS patch drivers) pertain to a language or multiple languages. This table holds that language (or multiple languages).

AD_PATCH_DRIVER_MINIPKS

This table holds information about the various Mini Packs contained in a patch (driver)

AD_PATCH_RUN_BUG_ACTIONS

holds the various actions present in “applied” bug (fix). If Autopatch determined not to apply a bug (fix), then this table will not hold any records for that “unapplied” bug fix.

AD_PATCH_RUN_BUG_ACTIONS

Even though a patch may have been applied on an Oracle Applications installation, some actions in some of its included bugs (fixes) may not have got executed if the “Autopatch” utility determined that it was not necessary to execute those actions. In such cases, EXECUTED_FLAG is set to N.

AD_PATCH_RUN_BUGS

This table holds information about the bugs fixed in a specific run of Autopatch.
AD_PATCH_RUN_BUGS holds information about the various bugs fixed in a specific run of Autopatch.
Even though a patch may have been applied on an Oracle Applications installation, some bugs (fixes) contained in it may not get applied due to some reason. In such cases, the REASON_NOT_APPLIED column holds the reason.

AD_PATCH_RUNS

AD_PATCH_RUNS holds information about the various invocations of Autopatch for applying Oracle Applications patches to a specific release of an Oracle Applications installation.
If multiple drivers are run in one invocation of Autopatch, they result in multiple records in this table. These multiple records will all have the same SESSION_ID (because they arose from one Autopatch invocation), but different TASK_NUMBER’s. The TASK_NUMBER’s in this case will be numbered sequentially as 1, 2, 3, etc.
Note that when the database driver of a Maintenance Pack is applied, it bumps up the release version by creating a new record in AD_RELEASES, which is then pointed to by the UPDATED_TO_RELEASE_ID column of the old record.

AD_RELEASES

AD_RELEASES holds the various Oracle Applications releases that an installation of Oracle Applications has gone through in its entire life cycle.
It should be noted that START_DATE_ACTIVE, END_DATE_ACTIVE and BASE_RELEASE_FLAG are loosely-maintained informational columns and are not accurately maintained, and therefore should not be relied upon heavily.

AD_PATCH_COMMON_ACTIONS

This table holds distinct information about the various actions that are (often repeatedly) performed by Autopatch as part of applying patches.

References:

Metalink ETRM DIRECT ACCESS DOCUMENT 150230.1

Hope this helps!!

EBS Forms problem in Windows Vista / Server 2008

This is the problem with JInitiator. The DLL (Dynamic Link Libraries) file present in the JInitiator is not supported by VISTA / Windows Server 2008. Need to replace them with the latest one.
1. Go to (JInitiator installed location)\bin\hotspot
(Default JInitiator installed location: C:\Program Files\Oracle\JInitiator 1.1.8.16)
2. Find ‘jvm.dll’ file
3. Replace this with the latest one

Latest version of this file can be found here.
1. If you have latest Java / JRE installed on your system, go to 
(JRE installed location)\bin\client -> here you can find the jvm.dll file, copy this file and paste it in \bin\hotspot, click yes, when prompts for replace.
(Default JRE installed location: C:\Program Files\Java\jre1.6.0_07)

Note: Update the jre to the latest version before replacing jvm.dll file to ensure that you are replacing with the latest one.
 

Invalid range "a-Z" in transliteration operator

Error while running Pre-Clone Script.

Environment : Linux A.S.4
Bit : 32
Version : 11.5.10


[applspin@spinner SPIN_spinner]$ ./adpreclone.pl appsTier
Invalid range "a-Z" in transliteration operator at /d02/applspin/spinora/iAS/Apache/perl/lib/5.00503/vars.pm line 17.
Compilation failed in require at /d02/applspin/spinora/iAS/Apache/perl/lib/5.00503/File/Copy.pm line 13.
BEGIN failed--compilation aborted at /d02/applspin/spinora/iAS/Apache/perl/lib/5.00503/File/Copy.pm line 13.
Compilation failed in require at ./adpreclone.pl line 36.
BEGIN failed--compilation aborted at ./adpreclone.pl line 36.


Solution:

Open $IAS_ORACLE_HOME/Apache/perl/lib/5.00503/vars.pm and change (line 17):

if ($sym =~ tr/A-Za-Z_0-9//c) {

change to
if ($sym =~ tr/A-Za-z_0-9//c) {

(The change is the single character Z->z)

[applspin@spinner SPIN_spinner]$ ./adpreclone.pl appsTier
Completed Successfully


Reference: From Forums.

https://metalink2.oracle.com/metalink/plsql/f?p=200:27:7385154415821451567::::p27_id,p27_show_header,p27_show_help:677071.993,1,1