Upgrade Latest version of JRE Native on Server and Disable Java-auto update on Client Machine

High Level Steps
***************

1. Download the latest Java patch on Windows platform(Clients platform) from (Doc ID 1439822.1) and unzip the patch. Which consist of jre-7u67-windows-i586.exe file

19295660

Oracle JRE 7 Update 67 with Auto Update Off

05-AUG-2014

                     2. Rename the downloaded JRE Native Plugin file to j2se<version>.exe

For example:

jre-7u67-windows-i586.exe or jre-7u67-windows-x64.exe would be renamed j2se17067.exe

           3. Move the j2se170xx.exe file to the web application tier and place it in the following directory:

$[COMMON_TOP]/webapps/oacore/util/jinitiator

                    4. If not applied previously, apply the JRE Plug-in interoperability Patch 4377566 through AutoPatch by following its readme.

                    5. Run the $FND_TOP/bin/txkSetPlugin.sh script against the web node of the application tier.

# su - applmgr
$ cd $FND_TOP/bin
$ sh txkSetPlugin.sh 17067



Now you have upgraded JRE Native version to latest....So whenever client access the application form's function, it will prompt for latest version java download...


References
**********

Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12 (Doc ID 393931.1)
All Java SE Downloads on MOS (Doc ID 1439822.1)

How to find table DML locks and remove those locks

SQL> set linesize 1500
SQL> set pagesize 500
SQL> select * from dba_dml_locks;

SESSION_ID OWNER                          NAME                           MODE_HELD     MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ----------------------------------------
        40 APPLSYS                        FND_CONCURRENT_REQUESTS        Row-X (SX)    None                    45 Not Blocking
       558 AP                             AP_INVOICES_ALL                Row-X (SX)    None                    29 Not Blocking
       676 APPS                           XXICAR_EMP_QUAL_STG            Row-X (SX)    None                   712 Not Blocking
       989 APPLSYS                        AQ$_WF_JAVA_DEFERRED_T         Row-X (SX)    None                   186 Not Blocking
       989 APPLSYS                        AQ$_WF_JAVA_DEFERRED_H         Row-X (SX)    None                   186 Not Blocking
       989 APPLSYS                        AQ$_WF_JAVA_DEFERRED_I         Row-X (SX)    None                   186 Not Blocking
       989 APPLSYS                        WF_JAVA_DEFERRED               Row-X (SX)    None                   186 Not Blocking
      1211 APPLSYS                        FND_CONCURRENT_QUEUES          Row-X (SX)    None                    18 Not Blocking

8 rows selected.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

       558      64621

       676       5315

       989       8413

      1211      30149

SQL> alter system kill session '558,64621';

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

        40         55

       676       5315

       989       8413

      1211      30149

SQL>  alter system kill session '40,55' immediate;

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

       319      63245

       676       5315

       989       8413

      1211      30149

SQL> alter system kill session '319,63245';

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

       676       5315

       989       8413

      1211      30149

SQL> alter system kill session '676,5315' immediate;

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

       989       8413

      1211      30149

SQL> alter system kill session '989,8413' immediate;

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

      1211      30149

SQL> alter system kill session '1211,30149' immediate;

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

        40         63

SQL> alter system kill session '40,63';

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

       SID    SERIAL#

---------- ----------

       554      17471

SQL> alter system kill session '554,17471' immediate;

System altered.

SQL> SELECT SID,SERIAL# FROM V$SESSION WHERE SID IN (SELECT SESSION_ID FROM DBA_DML_LOCKS);

no rows selected

Finally we have removed all those DML locks successfully... :)

How to check SMTP Server is working or not in Linux/Unix

bash-3.2# telnet mail.iasri.res.in 25
Trying...
Connected to mail.iasri.res.in.
Escape character is '^]'.
220 mail.iasri.res.in ESMTP Smtpd; Mon, 13 Oct 2014 15:40:47 +0530
EHLO  mail.iasri.res.in
250-mail.iasri.res.in Hello [192.168.101.25], pleased to meet you
250-ENHANCEDSTATUSCODES
250-PIPELINING
250-8BITMIME
250-SIZE 10485760
250-DSN
250-AUTH LOGIN PLAIN DIGEST-MD5 CRAM-MD5
250-STARTTLS
250-DELIVERBY
250 HELP
MAIL FROM:fmsprod@iasri.res.in
250 2.1.0 fmsprod@iasri.res.in... Sender ok
RCPT TO:gunasekarappsdba@gmail.com
250 2.1.5 gunasekarappsdba@gmail.com... Recipient ok
DATA
354 Enter mail, end with "." on a line by itself
Subject: test message sent from manual telnet session

Hello ,

This is a test message sent from a manual telnet session.

Yours truly,
SMTP administrator

.
250 2.0.0 s9DAAldq022476-s9DAAldr022476 Message accepted for delivery
QUIT
221 2.0.0 mail.iasri.res.in closing connection
Connection closed.
bash-3.2#

Oracle Applications Database frequently used scripts

FIND DATABASE SIZE
****************************

select 'DATA_n_INDEX: ' || sum(bytes)/1024/1024/1024 || ' GBytes' DATABASE_SIZE from dba_data_files
union
select 'TEMP: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from dba_temp_files
union
select 'REDO LOGS: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from v$log
union
select 'CONTROLFILE: ' || sum(FILE_SIZE_BLKS*BLOCK_SIZE)/1024/1024 || ' MBytes' from v$controlfile;


CREATE NEW DATABASE USER
*****************************************

create user <username> identified by <password> default tablespace <tablespace_name> temporary tablespace <tablespace_name> profile <profile_name>;

grant create session to <username>;


alter user <username> password expire;

CREATE DYNAMIC SQL SCRIPTS TO COMPILE INVALID OBJECTS
**********************************************************************************

spool recompile_list.sql;
select 'ALTER '||
decode(substr(object_type,1,4),'PACK','PACKAGE ',object_Type||' ')||
owner || '.' || decode(object_type,'JAVA CLASS','"') || object_name ||
decode(object_type,'JAVA CLASS','" RESOLVE ','INDEX','REBUILD',' COMPILE ') ||
DECODE(object_type,'PACKAGE BODY','BODY', NULL)||';' OBJECTS_TO_COMPILE
from dba_objects
where object_type in ('PACKAGE','PROCEDURE','PACKAGE BODY','FUNCTION',
'TRIGGER','VIEW','JAVA CLASS','INDEX')
and status = 'INVALID'
order by object_name, object_type;

spool off;

FIND ALL ACTIVE DATABASE SESSIONS
****************************************************

select event, program, module, sid, SECONDS_IN_WAIT from v$session where status = 'ACTIVE'
and module is not null order by 2 desc;

OR

select sid, serial#, username, osuser, logon_time, program, module 

from v$session where username is not null and status = 'ACTIVE';

FIND EBS USER NAME FROM DATABASE SESSION ID
********************************************************************

column "User Name" format a10
column "OS_PID" format a8

select d.user_name "User Name", 
b.sid SID,b.serial# "Serial#", c.spid "srvPID", a.SPID "OS_PID",
to_char(START_TIME,'DD-MON-YY HH:MM:SS') "STime"
from fnd_logins a, v$session b, v$process c, fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1)

and b.sid = &sid;

FIND ORPHAN PROCESSES
***********************************

select spid from v$process where not exists (select 1 from v$session where paddr=addr);

FIND DETAILS WHEN CONCURRENT PROGRAM WAS RUN IN LAST 10 DAYS
***********************************************************************************************

select REQUEST_ID,
REQUEST_DATE,
REQUESTED_START_DATE,
RESUBMIT_END_DATE,
ACTUAL_START_DATE,
ACTUAL_COMPLETION_DATE
from fnd_concurrent_requests
where CONCURRENT_PROGRAM_ID = &Concurrent_Prog_ID
and ACTUAL_START_DATE between trunc(sysdate) - 10 and sysdate

order by REQUESTED_START_DATE;

FIND ALL FORMS RELATED SESSIONS
*************************************************

col CLIENT_IDENTIFIER format a10
col MODULE format a25
col MACHINE format a10

select sid, serial#, logon_time, client_identifier, module, status, machine, seconds_in_wait
from gv$session
where program like 'frmweb%'

order by logon_time;

FIND USER_ID FROM USER_NAME
********************************************

select USER_ID, USER_NAME
from apps.FND_USER

where USER_NAME = upper('&1');

Configure Parallel Concurrent Processing (PCP) in EBS R12

Configure Parallel Concurrent Processing (PCP) in EBS R12

Parallel Concurrent Processing (PCP):

PCP is the method by which we configure the Concurrent Manager in a multi tier environment with 2 or more concurrent nodes. This allows concurrent processing load to be distributed across the nodes and provides high availability in case of node failure. Managers migrate to surviving node (failover)when one of the concurrent nodes goes down and migrate back (failback) when the failed node comes back.

Each node with concurrent managers may or may not be running an ORACLE instance. The concurrent manager(s) connect via sqlnet to database using tns alias specified by TWO_TASK in adcmctl.sh and gsmstart.sh on each concurrent node.

When the primary node fails, the ICM will restart the manager on the secondary node. If the ICM’s node fails, an Internal Monitor on surviving node can spawn a new ICM on that node.

Services/Managers move back to their primary nodes when those nodes come back up.

What is the role of ICM in PCP

Internal Manager (ICM) monitors, activates and deactivates all managers.

ICM migrates managers during node and/or instance failures and needs to be active for failover/failback to work.

ICM uses the Service Manager (FNDSM) to spawn and terminate all concurrent manager processes, and to manage GSM services like Workflow mailer, Output Post Processor, etc.

ICM will contact the APPS TNS Listener on each local and remote concurrent processing node to start the Service Manager on that node.

ICM will not attempt to start a Service Manager if it is unable to TNS ping the APPS TNS Listener

One Service Manager is defined for each application node registered in FND_NODES.

Each service/manager may have a primary and a secondary node. Initially, a concurrent manager is started on its primary node. In case of node failure, all concurrent managers on that node migrate to their respective secondary nodes.

Service Manager and PCP

Service manager (FNDSM process) is used to manage services/managers on each concurrent node. It is a requirement in all concurrent processing environments and is therefore an integral part of PCP. PCP cannot be implemented without Service manager.

The Service Manager is spawned from the APPS TNS Listener

The APPS TNS Listener must be started on every application node in the system, and started by the user that starts ICM (e.g. applmgr)

TNS Listener spawns Service Manager to run as agent of ICM for the local node

The Service Manager is started by ICM on demand when needed. If no management actions are needed on a node, Service Manager will not be started by ICM until necessary. When ICM exits its Service Managers exit as well.

The Service Manager environment is set by gsmstart.sh and APPSORA.env as defined in listener.ora

Internal Monitors and PCP
The only function of Internal Monitor (FNDIMON process) is to check if ICM is running and restart failed ICM on local node.

Internal Monitors are seeded on every registered node by default by autoconfig.

Activate Internal Monitor on each concurrent node where the ICM can start in case of a failure. By default, Internal Monitor is deactivated.

If the ICM goes down, the Internal Monitor will attempt to start a new ICM on the local node.

If multiple ICMs are started, only the first will stay active. The others will gracefully exit.

Basic steps to configure parallel concurrent Processing (PCP) in EBS R12 :

A) Backup all .ora files

Copy the existing tnsnames.ora, listener.ora and sqlnet.ora files, where they exist, under the 10.1.2 and 10.1.3 ORACLE_HOME locations on the each node to preserve the files

B) Edit Context file

Stop the application on all nodes. Edit the applications context file via Oracle Applications Manager

Set the value of the variable

> APPLDCP to ON
> s_applcsf to <shared location between apps servers>
> s_appltmp to <shared location between apps servers>

C) Edit parameters in spfile (for the transaction manager)

By user having sysdba privilege

alter system set “_lm_global_posts”=true scope=spfile;
alter system set “_immediate_commit_propagation”=true scope=spfile;

D) Edit utl_file_dir variable

From database node as sysdba

ALTER SYSTEM SET utl_file_dir='<shared temp location’ SCOPE=BOTH sid=’*';

E) Bounce database to reflect the changes made

Using grid user from any database node

srvctl stop database -d <db_name>
srvctl start database -d <db_name>

F) Execute AutoConfig

Execute AutoConfig by running the following command on all concurrent processing nodes:

$INST_TOP/admin/scripts/adautocfg.sh

G) Check the tnsnames.ora and listener.ora configuration files

Check the tnsnames.ora and listener.ora configuration files, located in $INST_TOP/ora/10.1.2/network/admin. Ensure that the required FNDSM and FNDFS entries are present for all other concurrent nodes.

H) Start the Applications

Log on to Oracle E-Business Suite Release 12 using the SYSADMIN account, and choose the System Administrator Responsibility.

• Navigate to Install > Nodes screen, and ensure that each node in the cluster is registered.
• Set up the primary and secondary node names

Navigate to Concurrent > Manager > Define, and set up the primary and secondary node names for all the concurrent managers according to the desired configuration for each node workload.

• Verify that the Internal Monitor and Service Manager for each node is defined properly, with correct primary node specification, and work shift details. For example, Internal Monitor: Host1 must have primary node as host1. Assign a standard work shift with one process to both managers. Also ensure that the Internal Monitor manager is activated: this can be done from Concurrent > Manager > Administrator.
• set Concurrent: TM Transport Type profile

Navigate to Profile > System, change the profile option ‘Concurrent: TM Transport Type’ to ‘QUEUE’, and verify that the transaction manager works across the Oracle RAC instance.

• If any of the transaction managers are in deactivated status, activate them from Concurrent > Manager > Administrator

What happens if we don’t assign any primary/secondary node

Managers with no primary node assignment will be assigned a default target node. In general this will be the node where the ICM is currently running.

I) Checks

>> In the tnsnames.ora file of each Concurrent Processing node ensure that there is an alias that matches the instance name from GV$INSTANCE of each Oracle instance on each RAC node in the cluster.

>> Ensure that the Applications Listener is active on each node in the cluster where Concurrent or Service processes will execute.

>> Stop and restart the Concurrent Manager processes on their primary node(s) and verify that the managers are starting on their appropriate nodes. On the target (secondary) node in addition to any defined managers you will see an FNDSM process (the Service Manager), along with the FNDIMON process (Internal Monitor).

Differences between Release 11i Release 12

Differences between Release 11i Release 12
=====================================
a) Plugin for Forms
11i Uses Jinitiator as the default Plugin
R12 Replaces Jinitiator with Sun Java Plugin

b) Forms Mode
Default mode for Forms in 11i is Socket.
In R12 its Servlet

c) Reports Server
No Reports Server in R12

d) Apache Version
11i Apache Version: 1.3.19
R12 Apache Version: 1.3.31

e) Forms Version
11i Forms Version : 6i
R12 Forms Version : 10g

f) JDK Version
R12 uses JDK 1.5 for web & concurrent processing

g) Servlet Container
11i Uses JServ
R12 Uses OC4J (OC4J is Oracles J2EE container)

h) ORACLE_HOME (s)
11i : 8.0.6-based Oracle_Home
R12 : OracleAS 10g 10.1.2 for Forms & Reports Services

11i : 8.1.7-based Oracle_Home provided by iAS 1.0.2.2 in 11i
R12 : OracleAS 10g 10.1.3 for Oracle Containers for Java (OC4J)



R12 APPL_TOP, COMMON_TOP , ORACLE_HOMES can now be read only

R12 introduces INST_TOP which also contains the startup shutdown scripts and with it New Commonly Used Environment Variables :
<apps_base>/inst/apps/<context_name> ($INST_TOP)

INST_TOP contains
==================================================
/admin/scripts ($ADMIN_SCRIPTS_HOME)
/appl ($APPL_CONFIG_HOME)
/fnd/12.0.0/secure ($FND_SECURE)
/certs
/logs ($LOG_HOME)
/ora ($ORA_CONFIG_HOME)
/10.1.2
/10.1.3 ($ORACLE_CONFIG_HOME)
/pids
/portal

Oracle Apps R12 Components Startup Shutdown
Order of Startup Shutdown
————————————–
As in Oracle Apps 11i , the order for startup is
1) Start Database Tier Services
–Start Database Listener
–Start Database
Then
2) Start Application/Middle Tier Services
– adstrtal.sh

Order for shutdown in Oracle Apps R12 is
1) Stop Application/Middle Tier Services
– adstpall.sh
Then
2) Stop Database Tier Services
–Stop Database
–Stop Database Listener


Database Tier Scripts in R12
————————————–
For Database tier you need to start database and database listener. Scripts are located in Database_Install_Dir/db/tech_st/10.2.0/appsutil/scripts/$CONTEXT_NAME-

For Database
Use script addbctl.sh- For Database Listener
Use script addlnctl.sh

Alternatively login as the Database User

lsnrctl start/stop listener_name (For Database Listener)
sqlplus “/as sysdba”
SQL> startup / shutdown immediate

Middle/Application Tier Scripts in R12
————————————————-
Scripts for Application Tier services in R12 are located in $INST_TOP/admin/scripts“

i) adstrtal.sh
Master script to start all components/services of middle tier or application tier. This script will use Service Control API to start all services which are enabled after checking them in context file (SID_HOSTNAME.xml or CONTEXT_NAME.xml)

ii) adstpall.sh
Master script to stop all components/services of middle tier or application tier.

iii) adalnctl.sh
Script to start / stop apps listener (FNDFS and FNDSM require the Apps Listener). This listener will file will be in 10.1.2 ORACLE_HOME (i.e. Forms & Reports Home)
listener.ora file will be in $INST_TOP/apps/$CONTEXT_NAME/ora/10.1.2/network/admin directory

iv) adapcctl.sh
Script to start/stop Web Server or Oracle HTTP Server. This script uses opmn (Oracle Process Manager and Notification Server) with syntax similar to opmnctl [startstop]proc ohs

v) adcmctl.sh
Script to start / stop concurrent manager (This script in turn calls startmgr.sh )

vi) adformsctl.sh
Script to start / stop Forms OC4J from 10.1.3 Oracle_Home. This script will also use opmnctl to start/stop Forms OC4J like
opmnctl stopproc type=oc4j instancename=forms

vii) adformsrvctl.sh
This script is used only if you wish to start forms in socket mode. Default forms connect method in R12 is servlet.
If started this will start frmsrv executable from 10.1.2 Oracle_Home in Apps R12

viii) adoacorectl.sh
This script will start/stop oacore OC4J in 10.1.3 Oracle_Home. This scripts will use opmnctl (similar to adapcctl & adformsctl) to start oacore instance of OC4J ie it calls
opmnctl startproc type=oc4j instancename=oacore

ix) adoafmctl.sh
This script will start/stop oafm OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to above) to start oacore instance of OC4J ie.
opmnctl startproc type=oc4j instancename=oafm

x) adopmnctl.sh
This script will start/stop opmn service in 10.1.3 Oracle_Home. opmn will control all services in 10.1.3 Oracle_Home like web server or various oc4j instances. If any services are stopped abnormally opmn will/should start them automatically.

xi) jtffmctl.sh
This script will be used to start/stop one to one fulfilment server.

xii) mwactl.sh
To start / stop mwa telnet server where mwa is mobile application.


Log File Location for Startup Shutdown Services in R12
———————————————————————-
Log files for startup/shutdown scripts for application/mid tier in R12 are in $INST_TOP/logs/appl/admin/log
(adalnctl.txt, adapcctl.txt, adcmctl.txt, adformsctl.txt, adoacorectl.txt, adoafmctl.txt, adopmnctl.txt, adstrtal.log, jtffmctl.txt )


What’s new in Oracle Apps R12

Key points to Note:
Oracle Applications Release 12 is the latest release in the chain of E-Business Suite Releases by Oracle.
This release came up with the new file system model
Autoconfig will not write anything in APPL_TOP, COMMON_TOP area in R12.
All instance specific configurations, log files are written in INST_TOP area. This home provides the ability to share Applications and technology stack.

R12 new features
Applications Server 9i is replaced by 10g (10.1.3.X)
Forms & Reports Version 6i (8.0.6) are replaced by Forms & Reports Version 10g i.e. 10.1.2.X
mod_jserv is replaced by oc4j
Java/ JDK version 1.3.X or 1.4.X will be replaced by JDK 1.5.X

Techstack Components Changes
Database (RDBMS_ORACLE_HOME) - 10.2.0.2
FORMS ORACLE_HOME (8.0.6 ORACLE HOME equivalence) - 10.1.2
OC4J_ORACLE_HOME (iAS ORACLE_HOME equivalence) - 10.1.3

File system level changes
A new top INSTANCE_TOP is introduced in Release 12 for configuration and log files along with the other TOP's in existing in 11i.
All instance specific configurations, log files are written in INST_TOP area.
This home provides the ability to share Applications and technology stack.

What is INSTANCE TOP
Instance home is the top-level directory for an applications instance which is known as Instance Home and is denoted the environment variable $INST_TOP.
This contains all the config files, log files, SSL certificates etc.

Advantages of new INSTANCE HOME
The additional Instance Home makes the middle tier easier to manage and organized since the data is kept separate from the config files.
The Instance Home also has the ability to share the Applications and Technology stack code across multiple instances.
Another advantage of the Instance Home is that the autoconfig writes only in INST_TOP so APPL_TOP and ORACLE_HOME can also be made read only file system if required.

Enabling Audit Trail in oracle database 11g

AUDIT_TRAIL

Property	Description
Parameter type	String
Syntax	AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }
Default value	none
Modifiable	No
Basic	No

AUDIT_TRAIL enables or disables database auditing.

Values:

• none
  Disables database auditing.
• os
  Enables database auditing and directs all audit records to the operating system's audit trail.
• db
  Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).
• db, extended
  Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXTCLOB columns of the SYS.AUD$ table.
• xml
  Enables database auditing and writes all audit records to XML format OS files.
• xml, extended
  Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.

You can use the SQL AUDIT statement to set auditing options regardless of the setting of this parameter.