Oracle: DBA's day out: February 2015

Using GMail's SMTP and IMAP servers in Oracle Notification Mailer (Workflow Mailer )

Using GMail's SMTP and IMAP servers in Oracle Notification Mailer (Workflow Mailer )

Hi All ...
Here the step by step guide how to use Gmail account with Oracle Workflow mailer.
Tested it in couple of customer sites and it working perfect...

Overview
GMail offers free, reliable, popular SMTP and IMAP services, because of which many people are interested to use it. GMail can be used when there are no in-house SMTP/IMAP servers for testing or debugging purposes. This blog explains how to install GMail SSL certificate in Concurrent Tier, testing the connection using a standalone program, running Mailer diagnostics and configuring GMail IMAP and SMTP servers for Workflow Notification Mailer Inbound and Outbound connections.
GMail servers configuration

SMTP server
Host Name    smtp.gmail.com
SSL Port        465
TLS/SSL required    Yes
User Name    Your full email address (including @gmail.com or @your_domain.com)
Password    Your gmail password

IMAP server
Host Name    imap.gmail.com
SSL Port    993
TLS/SSL Required     Yes
User Name    Your full email address (including @gmail.com or @your_domain.com)
Password    Your gmail password
GMail SSL Certificate Installation

The following is the procedure to install the GMail SSL certificate
Copy the below GMail SSL certificate in to a file eg: gmail.cer

>> Install the SSL certificate into the default JRE location or any other location using below command
Installing into a dfeault JRE location in EBS instance
        # keytool -import -trustcacerts -keystore $AF_JRE_TOP/lib/security/cacerts -storepass changeit -alias gmail-lnx_chainnedcert -file gmail.cer

Install into a custom location
        # keytool -import -trustcacerts -keystore <customLocation> -storepass changeit -alias gmail-lnx_chainnedcert -file gmail.cer
       <customLocation> -- directory in instance where the certificate need to be installed

After running the above command you can see the following response
        Trust this certificate? [no]: yes
        Certificate was added to keystore

Running Mailer Command Line Diagnostics

> Run Mailer command line diagnostics from conccurrent tier where Mailer is running, to check the IMAP connection using the below command
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=imap -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=imap.gmail.com -Dport=993 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailImapTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer

> Run Mailer command line diagnostics from concurrent tier where Mailer is running, to check the SMTP connection using the below command
$AFJVAPRG -classpath $AF_CLASSPATH -Dprotocol=smtp -Ddbcfile=$FND_SECURE/$TWO_TASK.dbc -Dserver=smtp.gmail.com -Dport=465 -Dssl=Y -Dtruststore=$AF_JRE_TOP/lib/security/cacerts -Daccount=<gmail username> -Dpassword=<password> -Dconnect_timeout=120 -Ddebug=Y -Dlogfile=GmailSmtpTest.log -DdebugMailSession=Y oracle.apps.fnd.wf.mailer.Mailer
Standalone program to verify the IMAP connection

> Run the below standalone program from the concurrent tier node where Mailer is running to verify the connection with GMail IMAP server. It connects to the GMail IMAP server with the given GMail user name and password and lists all the folders that exist in that account. If the GMail IMAP server is not working for the Mailer check whether the PROCESSED and DISCARD folders exist for the GMail account, if not create manually by logging into GMail account.

Sample program to test GMail IMAP connection
The standalone program can be run as below
$java GmailIMAPTest GMailUsername GMailUserPassword
Standalone program to verify the SMTP connection

>Run the below standalone program from the concurrent tier node where Mailer is running to verify the connection with GMail SMTP server. It connects to the GMail SMTP server by authenticating with the given user name and password and sends a test email message to the give recipient user email address.

Sample program to test GMail SMTP connection

The standalone program can be run as below
$java GmailSMTPTest GMailUsername GMailPassword recipientEmailAddress

Warnings
As gmail.com is an external domain, the Mailer concurrent tier should allow the connection with GMail server
Please keep in mind when using it for corporate facilities, that the e-mail data would be stored outside the corporate network

Recover deleted procedure

Recover deleted procedure

Example :-

SQL> select text from dba_source where name='XXXX' order by line;
TEXT
--------------------------------------------------------------------------------
PACKAGE      XXXX
PACKAGE BODY      XXXX
AS
AS
PROCEDURE XXXX(
   PROCEDURE XXXX (retcode            OUT VARCHAR2,
      retcode               OUT      VARCHAR2,
                                         errbuff            OUT VARCHAR2,
8 rows selected.

SQL> drop procedure upd_sales;
Procedure dropped.

SQL> connect / as sysdba
Connected.

SQL> select text from dba_source as of timestamp systimestamp - interval '60' minute where name='XXXX' order by line;
TEXT

--------------------------------------------------------------------------------
PACKAGE      XXXX
PACKAGE BODY      XXXX
AS
AS
PROCEDURE XXXX(
   PROCEDURE XXXX (retcode            OUT VARCHAR2,
      retcode               OUT      VARCHAR2,
                                         errbuff            OUT VARCHAR2,
8 rows selected.

ORA-04021: timeout occurred while waiting to lock object

ORA-04021: timeout occurred while waiting to lock object

Through SYS user :-

SELECT s.sid,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%&package_name%'
AND    l.lock_type = 'Body Definition Lock'
/

NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sql
check what is doing this session

SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program,
       t.sql_text
FROM   v$session s,
       v$sqltext t,
       v$process p
WHERE s.sql_hash_value = t.hash_value
AND    s.paddr = p.addr
AND    s.sid = &session_id
AND    t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/
Run kill command from the output of first script

alter system kill session 'SID,SERIAL#' immediate;

An error occurred while attempting to establish an Applications File Server connection OPP Files

An error occurred while attempting to establish an Applications File Server connection OPP Files

After copying all the files we updated fnd_concurrent_requests table to open old log and out files from R12 applications.

Then users are now able to open their 11i standard Log & Out files. But users are not able to open their Concurrent PDF output that is generated by Output post processor. Users when try to launch PDF out files they get below error.

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_<oldserver>. There may be a network configuration problem, or the TNS listener on node FNDFS_<oldserver> may not be running. Please contact your system administrator.

Solution:-
It was found that OPP will store file generayion details like file type,nodename, filename in table FND_CONC_REQ_OUTPUTS instead of FND_CONCURRENT REQUESTS. After updating these tables manually as below users are able to open OPP PDF output files.

update FND_CONC_REQ_OUTPUTS set
FILE_NAME=replace(FILE_NAME,'/u01/oracle/testcomn/admin/out/PROD_test','/u01/oracle/test/inst/apps/PROD_test/logs/appl/conc/out/PROD_tes');

update FND_CONC_REQ_OUTPUTS set FILE_NODE_NAME='<NewServer>'
where FILE_NODE_NAME='<OldServer>';

FNDFS Error- unable to view Concurrent program LOG and OUT files

FNDFS Error- unable to view Concurrent program LOG and OUT files

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_APPLTOP_ . There may be a network configuration problem, or the TNS listener on node FNDFS_APPLTOP_ may not be running.Please contact your system administrator.

Problem : In R12, After running a Concurrent request, Unable to view LOG and OUT files of the Concurrent request and returns above error message.

Cause : The RRA is Not Enabled in the Profile Option.

Solution : -
In the Profile Option –>system –> RRA ->RRA: Enabled is set as No in Site Level. Make it to Yes.
Save and Logout and Retest the Issue.

Patch failed due to prerequists patch is missing

Patch failed due to prerequists patch is missing

1. Shutdown the workers using "adctrl" utility.

a. adctrl
-- It will ask some prompts and enter those
Select option "Tell worker to shutdown/quit"

2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema

a. sqlplus applsys/
b. create table fnd_Install_processes_back as select * from fnd_Install_processes;

c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes_back; select count(*) from fnd_Install_processes;

3. Backup the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS;

4. 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.
a. cd $APPL_TOP/admin/
b. mv restart restart_orig
c. mkdir restart

5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;

6. Apply the new patch.

7. Restore the .rf9 files located in $APPL_TOP/admin//restart_back directory.
a. cd $APPL_TOP/admin/
b. mv restart restart_
c. mv restart_orig restart

8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema.
a. sqlplus applsys/
b. create table fnd_Install_processes as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes; select count(*) from fnd_Install_processes_back;

9. Restore the AD_DEFERRED_JOBS table.
a. sqlplus applsys/passwd
b. create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS;

10. Re-create synonyms
a. sqlplus apps/apps
b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

All dependencies of specified object

All dependencies of specified object

SET VERIFY OFF
SET LINESIZE 255
SET PAGESIZE 1000
BREAK ON referenced_type SKIP 1

COLUMN referenced_type FORMAT A20
COLUMN referenced_owner FORMAT A20
COLUMN referenced_name FORMAT A40
COLUMN referenced_link_name FORMAT A20

SELECT a.referenced_type,
       a.referenced_owner,
       a.referenced_name,
       a.referenced_link_name
FROM   all_dependencies a
WHERE  a.owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
AND    a.name  = UPPER('&2')
ORDER BY 1,2,3;

SET VERIFY ON
SET PAGESIZE 22

cache hit ratio

SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
Sum(Decode(a.name, 'db block gets', a.value, 0)) -
Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
(Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
 Sum(Decode(a.name, 'db block gets', a.value, 0))))
 *100,2) "Hit Ratio %"
FROM   v$sysstat a;

All active database sessions.

All active database sessions

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE  s.paddr  = p.addr
AND    s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

SET PAGESIZE 14

Lists all objects being accessed in the schema

Lists all objects being accessed in the schema

SET LINESIZE 255
SET VERIFY OFF

COLUMN object FORMAT A30

SELECT a.object,
       a.type,
       a.sid,
       b.serial#,
       b.username,
       b.osuser,
       b.program
FROM   v$access a,
       v$session b
WHERE  a.sid    = b.sid
AND    a.owner  = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))
AND    a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))
ORDER BY a.object;

Affected module by a application patch

Affected module by a application patch

> Query to find out modules which will be affected by a specific patch.

SQL> select distinct aprb.application_short_name as "Affected Modules" from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_patch_run_bugs aprb where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and apr.patch_run_id = aprb.patch_run_id and aprb.applied_flag = 'Y' and aap.patch_name in ('7654736','9440370','7666111','9466179')

> Query to find out patches nodewise

SQL> select aap.patch_name, aat.name, apr.end_date,apr.SUCCESS_FLAG from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_appl_tops aat where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and aat.appl_top_id = apr.appl_top_id and aap.patch_name like '%4562325%'

OBIA: How to Find Software Versions and Patches in an Oracle Business Intelligence Applications Environment

OBIA: How to Find Software Versions and Patches in an Oracle Business Intelligence Applications Environment

MOS 1519745.1

The Products versions can be found as below:

Oracle Business Intelligence Enterprise Server (OBIEE):

Provide the version.txt file from the following directory

for OBIEE 10g <BI_Home>\OracleBI\

for OBIEE 11g <BI_Home>\Oracle_BI1\bifoundation

OBIEE Applied Patches:

Patches applied can be found through

for OBIEE 10g, please review Note 1135013.1 - How to Keep Track of OBIEE 10g Patches Applied? How To Handle Version.Txt Files When You Have Multiple Patches? Are Patches Cumulative?

for OBIEE 11g, use "opatch lsinventory". For more information, review Note 1220799.1 - OBIEE 11g: How to Apply Patches Using Opatch

Oracle Business Intelligence Applications (OBIA):

Provide the version_apps.txt file from the following directory

for OBIEE 10g <BI_Home>\OracleBI\Document

for OBIEE 11g <FMW_Home>\Oracle_BI1\bifoundation\biapps

This can be obtained only from the machine where you have installed Oracle Business Intelligence Administration Tool and where you ran the Oracle Business Intelligence Applications installer to install the Oracle Business Intelligence Applications metadata.

Informatica Admin Console:

Start > Programs > Informatica PowerCenter > Server > Launch Admin Console

Click on the 'About' link

Informatica Designer:

Start > Programs > Informatica PowerCenter > Client > PowerCenter Designer

From the menu options; select Help > Informatica Designer

Provide the 'Version' information from this dialog window

For example:

Product: Informatica PowerCenter Administration Console

Version:

Build Date:

Build Number:

Informatica Server:

Provide the version.txt from your <INFA_HOME>\PowerCenterxxx\server directory.

For example:

Version:

HotFixNumber:

Product: Informatica PowerCenter Server

PreviousInstalledVersion:

Informatica Java:

Provide the Java version installed for Informatica from <INFA_HOME>/java/bin directory and run "./java -version"

Data Warehouse Administration Console (DAC) Client:

Provide the DAC version (DAC Client > Help > About DAC)

DAC Server:

On the DAC server machine, execute "serverinfo" (.bat or .sh) and provide results.

DAC Applied Patches:

DAC Build version usually looks like "DAC Build # 10.1.3.4.1.patch.20120711.0516"

This Shows the DAC version "10.1.3.4.1" and also shows the Build date of the Patch applied "20120711".

"20120711" shows that DAC Patch 14306642 - "DAC 10.1.3.4.1 CUMULATIVE PATCH FOR BI APPS - JUL 2012" is applied.

DAC Container:

The DAC Container (DAC Client > Design > which container in drop down list)

Also confirm if this is a Custom DAC Container.

Application Source System Type:

Provide the Application Source System Type and Version.

For example: EBS R12.1.3 or Siebel 8.x or PeopleSoft

Source OLTP Database:

Provide the type and full database version (including fix packs and Patches) of the Source OLTP system.

Target OLAP Database:

Provide the type and full database version (including fix packs and Patches) of the Target OLAP system.

Reference - MOS 1519745.1

Oracle: DBA's day out

Using GMail's SMTP and IMAP servers in Oracle Notification Mailer (Workflow Mailer )

Recover deleted procedure

ORA-04021: timeout occurred while waiting to lock object

An error occurred while attempting to establish an Applications File Server connection OPP Files

FNDFS Error- unable to view Concurrent program LOG and OUT files

Patch failed due to prerequists patch is missing

All dependencies of specified object

cache hit ratio

cache hit ratio

All active database sessions.

Lists all objects being accessed in the schema

Affected module by a application patch

OBIA: How to Find Software Versions and Patches in an Oracle Business Intelligence Applications Environment

Topics

Total Visitors