APPS Blogs

What Do Oracle Audit Vault Collection Agents Do?

The Oracle Audit Vault is installed on a server, and collector agents are installed on the hosts running the source databases.  These collector agents communicate with the audit vault server. 

If the collection agents are not active, no audit data is lost, as long as the source database continues to collect the audit data.  When the collection agent is restarted, it will capture the audit data that the source database had collected during the time the collection agent was inactive.

There are three types of agent collectors for Oracle databases.  There are other collectors for third-party database vendors such as SAP Sybase, Microsoft SQL-Server, and IBM DB2.

Audit Value Collectors for Oracle Databases*

Audit Trail Type

How Enabled

Collector Name

Database audit trail

For standard audit records: AUDIT_TRAIL initialization parameter set to: DB or DB, EXTENDED.

For fine-grained audit records: The audit trail parameter of DBMS_FGA.ADD_POLICY procedure is set to: DBMS_FGA.DB or DBMS_FGA.DB + DBMS_FGA.EXTENDED.

DBAUD

Operating system audit trail

For standard audit records: AUDIT_TRAIL initialization parameter is set to: OSXML, or XML, EXTENDED.

For syslog audit trails, AUDIT_TRAIL is set to OS and the AUDIT_SYS_OPERATIONS parameter is set to TRUE.  In addition, the AUDIT_SYSLOG_LEVEL parameter must be set.

For fine-grained audit records: The audit_trail parameter of the DBMS_FGA.ADD_POLICY procedure is set to DBMS_FGA.XML or DBMS_FGA.XML + DBMS_FGA.EXTENDED.

OSAUD

Redo log files

The table that you want to audit must be eligible.  See "Creating Capture Rules for Redo Log File Auditing" for more information.

REDO

 *Note if using Oracle 12c; the assumption is that Mixed Mode Unified Auditing is being used

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Audit Vault, Oracle Database
Categories: APPS Blogs, Security Blogs

Oracle E-Business Suite Database 12c Upgrade Security Notes

When upgrading the Oracle E-Business Suite database to Oracle Database 12c (12.1), there are a number of security considerations and steps that should be included in the upgrade procedure.  Oracle Support Note ID 1524398.1 Interoperability Notes EBS 12.0 or 12.1 with RDBMS 12cR1 details the upgrade steps.  Here, we will document steps that should be included or modified to improve database security.  All references to steps are the steps in Note ID 1524398.1.

Step 8

"While not mandatory for the interoperability of Oracle E-Business Suite with the Oracle Database, customers may choose to apply Database Patch Set Updates (PSU) on their Oracle E-Business Suite Database ...".

After any database upgrade, the latest CPU patch (either PSU or SPU) should always be applied.  The database upgrade only has the latest CPU patch available at the time of release of the database upgrade patch.  In the case of 12.1.0.1, the database upgrade will be current as of July 2013 and be missing the latest five CPU patches.  Database upgrade patches reset the CPU level - so even if you had applied the latest CPU patch prior to the upgrade, the upgrade will revert the CPU patch level to July 2013.

From a security perspective, the latest PSU patch should be considered mandatory.

Step 11

It is important to note from a security perspective that Database Vault must be disable during the upgrade process.  Any protections enabled in Database Vault intended for DBAs will be disabled during the upgrade.

Step 15

The DMSYS schema is no longer used with Oracle E-Business Suite and can be safely dropped.  We recommended you drop the schema as part of this step to reduce the attack surface of the database and remove unused components.  Use the following SQL to remove the DMSYS user --

DROP USER DMSYS CASCADE;
Step 16

As part of the upgrade, it is a good time to review security related initialization parameters are set correctly.  Verify the following parameters are set -

o7_dictionary_accessibility = FALSE
audit_trail = <set to a value other than none>
sec_case_sensitive_logon = TRUE (patch 12964564 may have to be applied)
Step 20

For Oracle E-Business Suite 12.1, the sqlnet_ifile.ora should contain the following parameter to correspond with the initialization parameter sec_case_sensitive_login = true -

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

 

 

 

Oracle E-Business Suite, DBA
Categories: APPS Blogs, Security Blogs

What can the Oracle Audit Vault Protect?

For Oracle database customers the Oracle Audit Vault can protect the following:

  • SQL statements logs – Data Manipulation Language (DML) statement auditing such as when users are attempting to query the database or modify data, using SELECT, INSERT, UPDATE, or DELETE.
  • Database Schema Objects changes – Data Definition Language (DDL) statement auditing such as when users create or modify database structures such as tables or views.
  • Database Privileges and Changes – Auditing can be defined for the granting of system privileges, such as SELECT ANY TABLE.  With this kind of auditing, Oracle Audit Vault records SQL statements that require the audited privilege to succeed.
  • Fine-grained audit logs – Fine Grained Auditing activities stored in SYS.FGA_LOG$ such as whether an IP address from outside the corporate network is being used or if specific table columns are being modified.  For example, when the HR.SALARY table is SELECTED using direct database connection (not from the application), a condition could be to log the details of result sets where the PROPOSED_SALARY column is greater than $500,000 USD.
  • Redo log data – Database redo log file data.  The redo log files store all changes that occur in the database.  Every instance of an Oracle database has an associated redo log to protect the database in case of an instance failure.  In Oracle Audit Vault, the capture rule specifies DML and DDL changes that should be checked when Oracle Database scans the database redo log.

The Audit Vault also supports –

  • Database Vault – Database Vault settings stored in DVSYS.AUDIT_TRAIL$ such as Realm audit, factor audit and Rule Audit. 
  • System and SYS – Core changes to the database by privileged users such as DBAs as recorded by AUDIT_SYS_OPERATIONS.
  • Stored Procedure Auditing – Monitor any changes made to PL/SQL and stored procedures.  Standard reports are provided to stored procedure operations, deleted and created procedures as well as modification history.

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Audit Vault, Oracle Database
Categories: APPS Blogs, Security Blogs

What is the Oracle Audit Vault?

Oracle Audit Vault is aptly named; the Oracle Audit Vault is a vault in which data about audit logs is placed, and it is based on two key concepts.  First, Oracle Audit Vault is designed to secure data at its source.  Second, Oracle Audit Vault is designed to be a data warehouse for audit data. 

The Oracle Audit Vault by itself does not generate audit data.  Before the Oracle Audit Vault can be used, standard auditing needs to be first enabled in the source databases.  Once auditing is enabled in the source databases, the Oracle Audit Vault collects the log and audit data, but does not replicate, copy and/or collect the actual data.  This design premise of securing audit data at the source and not replicating it differentiates the Oracle Audit Vault from other centralized logging solutions. 

Once log and audit data is generated in source databases, Oracle Audit Vault agents are installed on the source database(s) to collect the log and audit data and send it to the Audit Vault server.  By removing the log and audit data from the source system and storing it in the secure Audit Vault server, the integrity of the log and audit can be ensured and proven that it has not been tampered with.  The Oracle Audit Vault is designed to be a secure data warehouse of information of log and audit data.

Application Log and Audit Data

For applications, a key advantage to the Audit Vault’s secure-at-the-source approach is that the Oracle Audit Vault is transparent.  To use the Oracle Audit Vault with applications such as the Oracle E-Business Suite or SAP, standard Oracle database auditing only needs to be enabled on the application log and audit tables.  While auditing the application audit tables might seem duplicative, the advantage is that the integrity of the application audit data can be ensured (proven that it has not been tampered with) while not having to replicate or copy the application log and audit data. 

For example, the Oracle E-Business Suite has the ability to log user login attempts, both successful and unsuccessful.  To protect the E-Business Suite login audit tables, standard Oracle database auditing first needs to be enabled.  An Oracle Audit Vault agent will then collect information about the E-Business Suite login audit tables.  If any deletes or updates occur to these tables, the Audit Vault would then alert and report the incident.  The Audit Vault is transparent to the Oracle E-Business Suite, no patches are required for the Oracle E-Business Suite to be used with the Oracle Audit Vault.

Figure 1 Secure At-Source for Application Log and Audit data

Figure 2 Vault of Log and Audit Data

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Audit Vault
Categories: APPS Blogs, Security Blogs

Speaking My Own Language for UKOUG Apps 14 Conference

David Haimes - Mon, 2014-12-01 10:27

Finally I will be at a conference where my British accent, specifically my North West of England accent will be understood.  This will be my first time presenting at the UK OUG Conference and what better place than Liverpool to do it?  Home of my beloved Everton F.C., hometown of my parents and less than 20 miles from where I grew up (People from Liverpool would call me a woollyback) just outside Wigan.  So I will try to remember to shift from the Californian drawl I have picked up over the last 14 years and into my finest scouse accent.

I’m going to be presenting two papers which will showcase not just the powerful features that can revolutionize how you run your business, but also the amazing use experience, mobile and social features available in our ERP Cloud.  Both are on Monday and one is right after the other, so I’m a little bit apprehensive about having 10 minutes to dash from one room to another, get set up and start again.

Here are the details of the sessions, or just search for ‘Haimes’ and you’ll find them. Add them to your agenda, because they are both ‘must not miss’ sessions.

First up, Monday December 8th, 2pm, Hall 11C

Oracle E-Business Suite Coexistence with Fusion Accounting Hub & Implementing a Global Chart of Accounts.

This is a great session with a lot of content to pack in but I know the area well and am very passionate about it and have seen first hand how big a deal this is for businesses.

Then 10 minutes to pack up and dash to Hall 1B for 3pm

Oracle ERP Cloud Service Social & Mobile Demonstrations.

Doing live demos, with multiple different devices to switch between and using a live cloud environment on a conference WiFi make this a logistical challenge.  However when you have a phenomenal user experience, the best thing to do is show it live, so bear with me because we have some pretty cool features to show.


Categories: APPS Blogs

Mandatory Auditing - Oracle 12c Always-On-Auditing

Certainly from an auditing and logging perspective, one of the best new features delivered by Oracle 12c is mandatory auditing of the administrative users such as SYSDBA.  This can be described as ‘always on auditing’.  By default, the following audit related activities are now mandatorily audited -

  • CREATE AUDIT POLICY
  • ALTER AUDIT POLICY
  • DROP AUDIT POLICY
  • AUDIT
  • NOAUDIT
  • EXECUTE of the DBMS_FGA PL/SQL package
  • EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package
  • All configuration changes that are made to Oracle Database Vault
  • ALTER TABLE attempts on the AUDSYS audit trail table (this table cannot be altered)
  • Top level statements by administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM, until the database opens.  When the database opens, Oracle Database audits these users using the audit configurations in the system.

The audit activity resulting from mandatory auditing can be found in SYS.UNIFIED_AUDIT_TRAIL. 

Note when the database is not writable (such as during database mounting), if the database is closed, or if it is read-only, then Oracle writes the audit records to external files in the $ORACLE_BASE/audit/$ORACLE_SID directory. 

Mandatory Auditing

Integrigy Framework Event

  • CREATE AUDIT POLICY
  • ALTER AUDIT POLICY
  • DROP AUDIT POLICY
  • EXECUTE of the DBMS_FGA PL/SQL package
  • EXECUTE of the DBMS_AUDIT_MGMT PL/SQL package
  • All configuration changes that are made to Oracle Database Vault
  • ALTER TABLE attempts on the AUDSYS audit trail table (remember that this table cannot be altered)

E12 - Modify audit and logging

  • Top level statements by the administrative users SYS, SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM until the database opens
  • AUDIT
  • NOAUDIT

E11 - Privileged commands

Note: Activity and be found in SYS.UNIFIED_AUDIT_TRAIL when in pure mode and to the traditional audit trails in mixed mode.

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Database
Categories: APPS Blogs, Security Blogs

What Is Oracle 12 Unified Auditing? The View UNIFIED_AUDIT_TRAIL with 94 Columns

What is Oracle 12c Unified Auditing? The short answer is the view UNIFED_AUDIT_TRAIL. This view consolidates all logging and auditing information into a single source. Regardless of using either Mixed Mode or Pure Unified Auditing, the SYS.UNIFIED_AUDIT_TRAIL can be used. 

The key column in SYS.UNIFIED_AUDIT_TRAIL is AUDIT_TYPE.  This column shows from which Oracle component the log data originated -

SYS.UNIFIED_AUDIT_TRAIL Component Sources

Column AUDIT_TYPE Value

Description

Number of Columns in Table

Standard

Standard auditing including SYS audit records

44

XS

Real Application Security (RAS)and RAS auditing

17

Label Security

Oracle Label Security

14

Datapump

Oracle Data Pump

2

FineGrainedAudit

Fine grained audit(FGA)

1

Database Vault

Data Vault(DV)

10

RMAN_AUDIT

Oracle RMAN

5

Direct path API

SQL*Loader Direct Load

1

 

Total

94

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Database
Categories: APPS Blogs, Security Blogs

Oracle 12c Unified Auditing - Mixed Mode

Next in our blog series on Oracle 12 Unified Auditing is a discussion of Mixed Mode. Mixed Mode is the default auditing mode for Oracle 12c.  Oracle describes Mixed Mode auditing as a means of becoming familiar with Unified Auditing prior to migrating to Pure Unified Auditing.  Mixed Mode allows for all traditional, pre-12c log and audit functionality to co-exist with Unified Auditing.  More importantly, Mixed Mode will support any current Syslog-based logging solution.

Mixed mode auditing provides the following key capabilities –

  • All existing (pre-12c) auditing initialization configurations and parameters are used such as AUDIT_TRAILAUDIT_FILE_DESTAUDIT_SYS_OPERATIONS, and AUDIT_SYSLOG_LEVEL
  • The format of the audit records remains the same as in Oracle Database 11g Release 2
  • Writes mandatory audit records to the traditional audit trails
  • If the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE, writes audit records only to the traditional audit trails

With Mixed Mode, audit data can be found both in the traditional locations as well as in SYS.UNIFIED_AUDIT_TRAIL.  This is because the Unified Auditing Policy ORA_SECURECONFIG is enabled by default.  ORA_SECURECONFIG audits the same default audit settings from Oracle Database Release 11g.  Integrigy recommends to either periodically purge Unified Auditing data or disable the policy.  To disable ORA_SECURECONFIG policy follow the instructions in Oracle Support Note Doc ID 1624051.1.

The following table shows the definition of the default policy ORA_SECURECONFIG.  Note the column ‘Common’ that shows that the policy is defined for all PDBs (tenant) databases.

Mixed Mode Default Unified Policy ORA_SECURECONFIG

Audit Option

Option Type

Common

Integrigy Framework

ADMINISTER KEY MANAGEMENT

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

ALTER ANY PROCEDURE

SYSTEM PRIVILEGE

YES

E13 – Objects

ALTER ANY SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

ALTER ANY TABLE

SYSTEM PRIVILEGE

YES

E13 – Objects

ALTER DATABASE

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

ALTER DATABASE LINK

STANDARD ACTION

YES

E13 – Objects

ALTER PLUGGABLE DATABASE

STANDARD ACTION

YES

E11 - Privileged commands

ALTER PROFILE

STANDARD ACTION

YES

E14 - Modify configuration settings

ALTER ROLE

STANDARD ACTION

YES

E8 - Modify role

ALTER SYSTEM

SYSTEM PRIVILEGE

YES

E14 - Modify configuration settings

ALTER USER

STANDARD ACTION

YES

E6 - Modify user account

AUDIT SYSTEM

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

CREATE ANY JOB

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ANY LIBRARY

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ANY PROCEDURE

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ANY SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

CREATE ANY TABLE

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE DATABASE LINK

STANDARD ACTION

YES

E13 – Objects

CREATE DIRECTORY

STANDARD ACTION

YES

E13 – Objects

CREATE EXTERNAL JOB

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE PLUGGABLE DATABASE

STANDARD ACTION

YES

E11 - Privileged commands

CREATE PROFILE

STANDARD ACTION

YES

E11 - Privileged commands

CREATE PUBLIC SYNONYM

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ROLE

STANDARD ACTION

YES

E7 - Create role

CREATE SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE USER

SYSTEM PRIVILEGE

YES

E5 – Create user account

DROP ANY PROCEDURE

SYSTEM PRIVILEGE

YES

E13 – Objects

DROP ANY SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E13 - Objects

DROP ANY TABLE

SYSTEM PRIVILEGE

YES

E13 – Objects

DROP DATABASE LINK

STANDARD ACTION

YES

E13 – Objects

DROP DIRECTORY

STANDARD ACTION

YES

E13 – Objects

DROP PLUGGABLE DATABASE

STANDARD ACTION

YES

E11 - Privileged commands

DROP PROFILE

STANDARD ACTION

YES

E14 - Modify configuration settings

DROP PUBLIC SYNONYM

SYSTEM PRIVILEGE

YES

E13 – Objects

DROP ROLE

STANDARD ACTION

YES

E8 - Modify role

DROP USER

SYSTEM PRIVILEGE

YES

E6 - Modify user account

EXEMPT ACCESS POLICY

SYSTEM PRIVILEGE

YES

E14 - Modify configuration settings

EXEMPT REDACTION POLICY

SYSTEM PRIVILEGE

YES

E14 - Modify configuration settings

GRANT ANY OBJECT PRIVILEGE

SYSTEM PRIVILEGE

YES

E9 - Grant/revoke user privileges

GRANT ANY PRIVILEGE

SYSTEM PRIVILEGE

YES

E9 - Grant/revoke user privileges

GRANT ANY ROLE

SYSTEM PRIVILEGE

YES

E9 - Grant/revoke user privileges

LOGMINING

SYSTEM PRIVILEGE

YES

E12 - Modify audit and logging

LOGOFF

STANDARD ACTION

YES

E2 - Logoff

LOGON

STANDARD ACTION

YES

E1 - Login

PURGE DBA_RECYCLEBIN

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

SET ROLE

STANDARD ACTION

YES

E11 - Privileged commands

TRANSLATE ANY SQL

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Database
Categories: APPS Blogs, Security Blogs

NetApp Plug-in for Oracle RMAN

Bas Klaassen - Thu, 2014-11-20 08:03
Nice feature for Oracle dba's to handle the backups using NetApp technology. Check this demo.. http://community.netapp.com/t5/FAS-Data-ONTAP-and-Related-Plug-ins-Articles-and-Resources/Video-NetApp-Plug-in-2-0-for-Oracle-RMAN-MML-Demo/ta-p/87711Bas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com4
Categories: APPS Blogs

Table TXK_TCC_RESULTS needs to be installed by running the EBS Technology Codelevel Checker (available as patch 17537119).

Vikram Das - Wed, 2014-11-19 12:06
I got this error while trying to apply a patch in R12.2:

 [EVENT]     [START 2014/11/19 09:18:39] Performing database sanity checks
   [ERROR]     Table TXK_TCC_RESULTS needs to be installed by running the EBS Technology Codelevel Checker (available as patch 17537119).

This table TXK_TCC-RESULTS is created in APPLSYS schema, by the latest version of the checkDBpatch.sh script delivered by 17537119.

So go ahead, download patch 17537119.  

Login as oracle user on your database node.
Source environment
cd $ORACLE_HOME/appsutil
unzip p17537119*
$ ./checkDBpatch.sh 

+===============================================================+ 
| Copyright (c) 2005, 2014 Oracle and/or its affiliates. | 
| All rights reserved. | 
| EBS Technology Codelevel Checker | 
+===============================================================+ 

Executing Technology Codelevel Checker version: 120.18 

Enter ORACLE_HOME value : /exampler122/oracle/11.2.0 

Enter ORACLE_SID value : exampler122

Bugfix XML file version: 120.0.12020000.16 

Proceeding with the checks... 

Getting the database release ... 
Setting database release to 11.2.0.3 

DB connectivity successful. 

The given ORACLE_HOME is RAC enabled. 
NOTE: For a multi-node RAC environment 
- run this tool on all non-shared ORACLE_HOMEs. 
- run this tool on one of the shared ORACLE_HOMEs. 


Created the table to store Technology Codelevel Checker results. 

STARTED Pre-req Patch Testing : Wed Nov 19 10:53:00 EST 2014 

Log file for this session : ./checkDBpatch_7044.log 

Got the list of bug fixes to be applied and the ones to be rolled back. 
Checking against the given ORACLE_HOME 


Opatch is at the required version. 

Found patch records in the inventory. 

All the required one-offs are present in Oracle Database Home 

Stored Technology Codelevel Checker results in the database successfully. 

FINISHED Pre-req Patch Testing : Wed Nov 19 10:53:03 EST 2014 

========================================================= 

1 select owner,table_name from dba_tables 
2* where table_name='TXK_TCC_RESULTS' 
SQL> / 

OWNER TABLE_NAME 
------------------------------ ------------------------------ 
APPLSYS TXK_TCC_RESULTS 

SQL> 

Once you have done this, restart your patch with adop with additional parameter restart=yes
Categories: APPS Blogs

Oracle 12c Unified Auditing - Pure Mode

Continuing our blog series on Oracle 12 Unified Auditing is a discussion of Pure  Mode. Mixed mode is intended by Oracle to introduce Unified Auditing and provide a transition from the traditional Oracle database auditing.  Migrating to PURE Unified Auditing requires the database be stopped, the Oracle binary linked to uniaud_on, and then restarted.  This operation can be reversed if auditing needs to be changed back to Mixed Mode. 

When changing from Mixed to pure Unified Audit, two key changes occur.  The first is the audit trails are no longer written to their traditional pre-12c audit locations.  Auditing is consolidated into the Unified Audit views and stored using Oracle SecureFiles.  Oracle Secured Files use a proprietary format which means that Unified Audit logs cannot be viewed using editors such vi and may preclude or affect the use of third party logging solutions such as Splunk or HP ArcSight.  As such, Syslog auditing is not possible with Pure Unified Audit.

Unified Audit Mixed vs. Pure Mode Audit Locations

System Tables

Mixed Mode

Pure Unified Audit Impact

SYS.AUD$

Same as 11g

Exists, but will only have pre-unified audit records

SYS.FGA_LOG$

Same as 11g

Exists, but will only have pre-unified audit records

The second change is that the traditional audit configurations are no longer used.  For example, traditional auditing is largely driven by the AUDIT_TRAIL initialization parameter.  With pure Unified Audit, the initialization parameter AUDIT_TRAIL is ignored.

Unified Audit Mixed vs. Pure Mode Audit Configurations

System Parameters

Mixed Mode

Pure Unified Audit Impact

AUDIT_TRAIL

Same as 11g

Exists, but will not have any effect

AUDIT_FILE_DEST

Same as 11g

Exists, but will not have any effect

AUDIT_SYS_OPERATIONS

Same as 11g

Exists, but will not have any effect

AUDIT_SYSLOG_LEVEL

Same as 11g

Exists, but will not have any effect

UNIFIED_AUDIT_SGA_QUEUE_SIZE

Same as 11g

Yes

If you have questions, please contact us at mailto:info@integrigy.com

Reference
Auditing, Oracle Database
Categories: APPS Blogs, Security Blogs

Mystery of java.sql.SQLRecoverableException: IO Error: Socket read timed out during adop/adpatch

Vikram Das - Tue, 2014-11-11 21:19
While applying the R12.2 upgrade driver, we faced the issue of WFXLoad.class failing in adworker log but showing up as running on adctrl

        Control
Worker  Code      Context            Filename                    Status
------  --------  -----------------  --------------------------  --------------
     1  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     2  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     3  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     4  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     5  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     6  Run       AutoPatch R120 pl                              Wait        
     7  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     8  Run       AutoPatch R120 pl  WFXLoad.class               Running      
     9  Run       AutoPatch R120 pl  WFXLoad.class               Running      
    10  Run       AutoPatch R120 pl                              Wait        

adworker log shows:

Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Socket read timed out
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:482)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:678)
        at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:238)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:567)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at oracle.apps.ad.worker.AdJavaWorker.getAppsConnection(AdJavaWorker.java:1041)
        at oracle.apps.ad.worker.AdJavaWorker.main(AdJavaWorker.java:276)
Caused by: oracle.net.ns.NetException: Socket read timed out
        at oracle.net.ns.Packet.receive(Packet.java:341)
        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:308)
        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1222)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:330)
        ... 8 more

This was happening again and again. The DBAs were suspecting network issue, cluster issue, server issue and all the usual suspects.  In Database alert log we saw these errors coming every few seconds:

Fatal NI connect error 12537, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.3.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
  Time: 11-NOV-2014 19:58:19
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12537

TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (26388) as a result of ORA-609


We tried changing the parameters in sqlnet.ora and listener.ora as instructed in the article:
Troubleshooting Guide for ORA-12537 / TNS-12537 TNS:Connection Closed (Doc ID 555609.1)

Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

However, the errors continued.  To rule out any issues in network, I also restarted the network service on Linux:

service network restart

One thing which I noticed was the extra amount of time that the connect was taking 4 seconds:

21:17:38 SQL> conn apps/apps
Connected.
21:17:42 SQL> 

Checked from remote app tier and it was same 4 seconds.

Stopped listener and checked on DB server that uses bequeath protocol:

21:18:47 SQL> conn / as sysdba
Connected.
21:18:51 SQL> conn / as sysdba
Connected.

Again it took 4 seconds.

A few days back, I had seen that connect time had increased after turning setting the DB initialization parameter pre_page_sga to true in a different instance.  On a hunch, I checked this and indeed pre_page_sga was set to true.  I set this back to false:

alter system set pre_page_sga=false scope=spfile;
shutdown immediate;
exit
sqlplus /nolog
conn / as sysdba
startup
SQL> set time on
22:09:46 SQL> conn / as sysdba
Connected.
22:09:49 SQL>

The connections were happening instantly.  So I went ahead and resumed the patch after setting:

update fnd_install_processes 
set control_code='W', status='W';

commit;

I restarted the patch and all the workers completed successfully.  And the patch was running significantly faster.  So I did a search on support.oracle.com to substantiate my solution with official Oracle documentation.  I found the following articles:

Slow Connection or ORA-12170 During Connect when PRE_PAGE_SGA init.ora Parameter is Set (Doc ID 289585.1)
Health Check Alert: Consider setting PRE_PAGE_SGA to FALSE (Doc ID 957525.1)

The first article (289585.1) says:
PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off. The advantage that PRE_PAGE_SGA can afford depends on page size.

The second article (957525.1) says:
Having the PRE_PAGE_SGA initialization parameter set to TRUE can significantly increase the time required to establish database connections.

The golden words here are "Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.".  That is exactly what happens when you do adpatch or adop.

Keep this in mind, whenever you do adpatch or adop, make sure that pre_page_sga is set to false.  It is possible that you may get the error "java.sql.SQLRecoverableException: IO Error: Socket read timed out" if you don't.  Also the patch will run significantly slower if pre_page_sga is set to true.  So set it to false and avoid these issues.



Categories: APPS Blogs

Accounting Hub Reporting Cloud Service

David Haimes - Thu, 2014-10-09 10:01

SunburstSo what is this new service and why was it such a big focus at Oracle OpenWorld this year?

  • It’s a very exciting opportunity to experience the powerful Financial Reporting innovations in our Cloud offerings without disruption to your existing ERP investments.
  • It’s a way to take advantage of the Simplified Financials Report Center, optimized for easy access to reports on your choice of mobile device
  • It includes the sunburst data visualization tool, which was my killer demo last week at OpenWorld (see screen shot)
  • It’s a way to move to cloud in an incremental manner, realizing business benefits quickly without disruption to your existing business processes and systems.
  • It has a companion EBusiness Suite feature (available on 12.1.3 and 12.2.4) that will push all your set up and GL Balances to your cloud service and generate reports automatically for you.  Giving you a zero configuration reporting solution for you EBS GL Balances data (watch out for more detailed posts on this soon)
  • It has web services to load General Ledger data from PeopleSoft, JDE Edwards or any other ERP system.
  • It’s a way to get your hands on the Oracle Social Network which is part of the platform our Cloud offerings are built on.

That’s a decent list to start with, but there are a few things that it isn’t which I should call out

  • It is not(yet) the Accounting Hub Integration Platform with all the rule based accounting transformations provided by Subledger Accounting Architecture (SLA)
  • It is not a new name designed to confuse you when we already have Financials Accounting Hub and Fusion Accounting Hub.

Look out for future posts going into more detail, or you can look at the cloud service page, which has important details such as pricing.


Categories: APPS Blogs

Focus on #WorkLifeBalance at Oracle OpenWorld

David Haimes - Mon, 2014-09-29 00:16

I promised my 9 year old son that I would run the school 5K fun run with him, little did I know it would clash with Oracle OpenWorld.  I had a 5k run at 10am in Belmont and then a presentation to the OAUG GL Special Interest Group at 11am 20 mile sNorth in San Francisco.  I was worried about the pace my son could do but we managed to average 9.5 minute miles even with a stop to tie a shoe lace and a lot of traffic of all ages and speeds.  We had just enough time snap the picture below and then he went for the pancake breakfast with the rest of his friends and I ran another half mile to my car and headed to the Moscone Center.  No time to change and straight up on stage, with 10 minutes to spare before I was due to speak.  After that I caught up with a colleague who’s here from the UK over lunch and was home in time to upgrade my son’s home minecraft server to 1.8, write a blog and check on my demos for tomorrow’s Oracle Applications User Experience (OAUX) EXPO.

Dolphin Dash

Only one of us got to stay for the pancake breakfast

OOW14

No time to change, straight on stage and waiting to present.

later this evening I was pleased to see this tweet from Steve Miranda, our EVP who is also balancing his heavy workload at OpenWorld with family commitments too.

Steve makes time for family too.

Steve makes time for family too.

So all in all a good day, here’s hoping the rest of the week is just as enjoyable.


Categories: APPS Blogs

oracle.ias.cache.CacheFullException: J2EE JOC-017 The cache is full

Vikram Das - Sat, 2014-09-27 11:40
Yesterday, the users of an EBS R12.2 instance got this error when they logged in:

Error Page
You have Encountered an unexpected error.  Please contact the System Administrator for assistance.

On checking the $EBS_DOMAIN_HOME/servers/oacore_server1/logs/oacore_server1.out, we found this error:



oracle.ias.cache.CacheFullException: J2EE JOC-017 The cache is full.
       at oracle.ias.cache.CacheHandle.findObject(CacheHandle.java:1680)
       at oracle.ias.cache.CacheHandle.locateObject(CacheHandle.java:1118)
       at oracle.ias.cache.CacheAccess.get(CacheAccess.java:877)
       at oracle.apps.jtf.cache.IASCacheProvider.get(IASCacheProvider.java:771)
       at oracle.apps.jtf.cache.CacheManager.getInternal(CacheManager.java:4802)
       at oracle.apps.jtf.cache.CacheManager.get(CacheManager.java:4624)
       at oracle.apps.fnd.cache.AppsCache.get(Unknown Source)
       at oracle.apps.fnd.functionSecurity.Grant.getGrantArray(Unknown Source)
       at oracle.apps.fnd.functionSecurity.Authorization.getFunctionSecurityGrantedMenusForGrantee(Authorization.java:829)
       at oracle.apps.fnd.functionSecurity.Authorization.getFunctionSecurityGrantedMenus(Authorization.java:744)
       at oracle.apps.fnd.functionSecurity.Authorization.getFuncSecGrants(Authorization.java:251)
       at oracle.apps.fnd.functionSecurity.Authorization.testMenuTreeFunction(Authorization.java:499)
       at oracle.apps.fnd.functionSecurity.Navigation.getMenuTree(Navigation.java:254)
       at oracle.apps.fnd.functionSecurity.Navigation.getMenuTree(Navigation.java:279)
       at oracle.apps.fnd.functionSecurity.Navigation.getMenuTree(Navigation.java:160)

We tried bouncing services and deleting $EBS_DOMAIN_HOME/servers/oacore_server1/cache.  None of those actions helped.  Things got back to normal only after the Xmx, Xms,and permsize startup parameters for the oacore JVM were changed in weblogic console on Gary's suggestion:

-XX:PermSize=512m -XX:MaxPermSize=512m –Xms4096m –Xmx4096m

I also changed it in the context file:

Old: s_oacore_jvm_start_options">-XX:PermSize=128m -XX:MaxPermSize=384m -Xms512m -Xmx512m
New: s_oacore_jvm_start_options">-XX:PermSize=512m -XX:MaxPermSize=512m –Xms4096m –Xmx4096m

The oacore_server1 and oacore_server2 were bounced after this.  We haven't seen that error ever since.


There is a support.oracle.com article: Receive Intermittent Error You Have Encountered An Unexpected Error. Please Contact Your System Administrator (Doc ID 1519012.1)
CauseThere are user accounts having extremely high numbers of FUN_ADHOC_RECI_XXXXXXX / FUN_ADHOC_INIT_XXXXXXX assigned.

Users have an extremely high number of (ADHOC) ROLES assigned to them, so when these attempt to login this fills the JOC ( Java object cache ) and causes it to run into it's limits resulting in the errors reported. Once bounce is done all is working fine until such an user logs in again.

While working in AGIS and creating and progressing batches, in the workflow there are several ad hoc roles created which remain on the system and do not get end dated or deleted. This can cause performance issues.
Ad Hoc Roles in WF_LOCAL_ROLES starting with FUN_ADHOC_RECI_XXXXXXX ; FUN_ADHOC_INIT_XXXXXXX with no expiration_date.

A. Run the following SQL to verify if there are accounts having extreme numbers of roles assigned

SQL> SELECT user_name, count(*) FROM wf_user_roles WHERE role_name <> user_name GROUP BY user_name ORDER BY 2;


B. Run following for particular user

SQL> SELECT distinct role_name FROM wf_user_roles
WHERE user_name = cp_user_name
or (user_name = (SELECT name FROM wf_local_roles wlr, fnd_user fusr
WHERE fusr.person_party_id = wlr.orig_system_id
AND wlr.orig_system = 'HZ_PARTY'
AND fusr.user_name = cp_user_name
AND rownum < 2))
AND role_name <> user_name;

Note: Replace cp_user_name with name of user having high number of ADHOC roles


SolutionTo implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Follow the steps given in document to purge the WF_LOCAL_ROLES for the AGIS transactions in 'COMPLETE' status.

AGIS: HOW TO DELETE AD HOC ROLES CREATED IN WORKFLOW (Doc ID 1446561.1)

3. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.



We had also logged SR with Oracle where they pointed us to the very same article and also asked us to do the following:


Action Plan
===========

1. How to find out the existing adhoc roles?

select name, start_date, start_date, expiration_date
from wf_local_roles
where orig_system = 'WF_LOCAL_ROLES'
order by name;

2. Define an expiration date for the ad hoc role:

exec WF_DIRECTORY.SetAdHocRoleExpiration (role_name=> >,expiration_date=>sysdate-1);


3. Periodically, purge expired users and roles in order to improve performance.

exec WF_PURGE.Directory(end_date);

This purges all users and roles in the WF_LOCAL_ROLES,WF_LOCAL_USER_ROLES, and WF_USER_ROLE_ASSIGNMENTS tables whose expiration date is less than or
equal to the specified end date and that are not referenced in any notification.

Parameter: end_date Date to purge to.

For more information, please refer to Oracle Workflow API Reference on page 2 – 128.
Use the workflow API's to purge the ad hoc roles:

NOTE:
After end dating the adhoc roles, the expired adhoc roles can also be purged by running the Purge Obsolete Workflow Runtime Data concurrent program. Make sure the "Core Workflow Only" parameter set to N.

Oracle also shared 3 open bugs (unpublished, can be read only Oracle employees) for this issue:

Bug 19025537 : ORACLE.IAS.CACHE.CACHEFULLEXCEPTION: J2EE JOC-017 THE CACHE IS FULL.
Bug 11772304 : JOC INVESTIGATION WITH 12.2
Bug 19582421 : R12.2 THE CACHE IS FULL; EXCEPTION IN OACORE_SERVER1 LOG.

Oracle finally shared the contents of Bug 19582421:

Action Plan
==========

Please review the following from Bug 19582421 : R12.2 THE CACHE IS FULL; EXCEPTION IN OACORE_SERVER1 LOG.


Workaround Steps:

1. Extract CacheDefaultConfig.xml from cache.jar


cd $FMW_HOME/oracle_common/modules/oracle.javacache_11.1.1
jar -xf cache.jar CacheDefaultConfig.xml

2. Edit CacheDefaultConfig.xml.
- diskCache size was the parameter that fixed it.
- changed the max-objects as well as per the bug.

Original:

xmlns="http://www.oracle.com/oracle/ias/cache/configuration11"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" max-objects="5000"
max-size="10" private="false" cache-dump-path="jocdump" system="false"
clean-interval="60" version="11.1.1.2.0" internal-version="110000">

init-retry-delay="2000" enable-ssl="false" auto-recover="false">
dedicated-coordinator="false" outOfProc="false">




default-level="SEVERE"/>




Modified:

xmlns="http://www.oracle.com/oracle/ias/cache/configuration11"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" max-objects="100000"
max-size="50" private="false" cache-dump-pa
th="jocdump" system="false" clean-interval="60" version="11.1.1.2.0"
internal-version="110000">

init-retry-delay="2000" enable-ssl="false" auto-recover="false">
dedicated-coordinator="false" outOfProc="false">





default-level="SEVERE"/>




3. Upload the changed file to the jar file

jar uf cache.jar CacheDefaultConfig.xml

4. Modify javacache.xml and make the same changes. This file is probably not
getting used. But made the changes anyway to keep the values in sync.
cd $EBS_DOMAIN_HOME/config/fmwconfig/servers/oacore_server1

5. Bounce apache and oacore.

These 5 steps resolved the issue.

Categories: APPS Blogs

Focus on Accounting Hub at Oracle OpenWorld

David Haimes - Thu, 2014-09-25 00:27

This is number three in my Focus on Oracle OpenWorld Series and this one is in on my home turf, it was the talk of Las Vegas earlier this year at Collaborate.  I have explained the difference between Fusion Accounting Hub and Financial Accounting Hub before, but now we have the Accounting Hub Reporting Cloud Service.  I’ll be talking about this briefly in the OAUG GL SIG meeting and there is also a session from my boss, Rob Zwiebach that will cover it (spoiler alert, we might use some of the same slides), but he will also have Alex from our internal finance org to give talk about their experience running the accounting hub for the last few years.  Rob’s session details are below:

Introducing Oracle Fusion Accounting Hub Reporting Cloud Service [CON8404]

Existing Oracle E-Business Suite customers now have an out-of-the-box coexistence strategy. With Oracle Fusion Accounting Hub Reporting Cloud Service, you can have the power of Oracle Fusion Reporting in the cloud. This session explains the benefits and details of Oracle Fusion Accounting Hub Reporting Cloud Service.

Alex SanJuan – VP Finance, Oracle
Rob Zwiebach, VP Financial Application Development, Oracle

Tuesday, Sep 30, 4:45 PM – 5:30 PM – Westin Market Street – Metropolitan II
I already have a number of customer meetings lined up to discuss accounting hub and a few more I’m trying to squeeze in, so it is shaping up to be an exciting new chapter for the Accounting Hub.


Categories: APPS Blogs

Focus on User Groups at Oracle OpenWorld

David Haimes - Tue, 2014-09-23 08:22

Anyone who reads my blog regularly might be tired of me praising user groups, but I believe it is worth repeating myself.  The type of information sharing you get from user groups is unique and very valuable, it is important for Oracle to support that, be aware of it, but not interfere.  I have been involved with the Oracle Apps User Group via the GL SIG for several years now but I will never push my agenda, I listen, provide information or presentations that are requested and I learn.  Many of my colleagues will tell you the same.  So Sunday is User Group day at OpenWorld and I look forward to seeing a lot of familiar faces and learning something new.  I have a bit of a schedule crunch this year, 12 months ago I committed to run the 5k “Dolphin Dash” School fundraiser with my 8 year old son, I didn’t think for a minute it would fall on the same date as OpenWorld.  So I have to run a 5k at 9am and then dash into San Francisco to present at the GL SIG for 11am, if I arrive in my running gear, still sweating please accept my apologies.  I will stay around for more of the sessions and always I will be active on twitter, so you can find me that way too.

At the GL SIG I’ll be talking about and briefly showing the new Accounting Hub Reporting Cloud Service, which I am very excited about.  You will also hear from Lakshmi Sampath from Dell about SLA on their upgrade to R12.  The full agenda is below, I hope to see a lot of you there.

—————————————————————————————–

Agenda GL-SIG @ OpenWorld 2014 on Sunday, September 28th, 11.00am
Location – Moscone W – 3005

Agenda

  1. Introduction to the SIG
  2. Proactive support presentation
  3. Sponsor message (Excel4Apps)
  4. Lakshmi (SLA on Upgrade @ Dell)
  5. David Haimes new Accounting Hub Cloud Reporting Service
  6. Open questions

We will be joined by our Oracle colleagues to tell us about ProActive support and their leading practices to deliver content and help to the Oracle user community.
Excel4Apps will be helping us by sponsoring the meeting at this conference.

In the past many oracle apps customers have looked elsewhere for their reporting needs – until now – see a preview of “Accounting Hub Reporting Cloud Service”. See how many finance users are using their favorite tools for reporting – Smartview, Cubes, and related functionality to get their financials.  Fusion Account Hub Reporting Cloud Service is a new subscription service provides out of the box integration with EBS R12 General Ledger for reporting.  This session will provide an introduction to the new service, how it connects, works with EBS data, the reporting capabilities available and what it does and does not support.  Come listen to David Haimes, Senior Director, Financials Product Development t alk about the new service.

Hear what Lakshmi Sampath from Dell has to say about their upgrade to R12. The presentation covers the R12 Upgrade Case-Study at Dell providing details on what happens during upgrade to R12 in various subledgers (PO, AP, AR, PA, FA) with respect to SLA. This presentation will also provide details on strategies for data conversion to SLA model during upgrade, and cover critical lessons learned during the upgrade @ DELL.

—————————————————————————————–

 


Categories: APPS Blogs

Focus on Oracle Social Network at OpenWorld

David Haimes - Fri, 2014-09-19 23:49

This is the first of a series of posts I am planning leading up to Oracle OpenWorld which starts in less than a week.  I have a few different focus areas this year, so I’ll write a little about each of them.

I’ve been talking about collaboration in ERP for quite some time and was also very flattered to have TheAppsLab and Ultan (@ultan) for the UX team cover what we have done in their blogs too.  I call it Socializing the Finance Department, it isn’t about more Pot Luck Lunches and after work drinks, it is about using social tools in a secure and efficient manner, embedded in your ERP system, tied to your transactions and business flows to make you more productive.

The Oracle Social Network(OSN) is part of the infrastructure we build our cloud applications on, so it is pervasive in our cloud apps.  There are a lot of good sessions, see here for the complete OSN list.  I will be on a panel discussing the best use cases for social in enterprise applications, Tuesday September 30th 5pm – 5:45pm  – Moscone West – 3022, full details here.

We won’t be doing a demo, but here is one video to give you a taste of what we will discuss, or check out my post Can chatting make us more productive? for another video.  If you catch during the #oow week, I’m usually happy to show this off, so feel free to ask me.


Categories: APPS Blogs

Can chatting make you more productive?

David Haimes - Thu, 2014-09-18 08:39

According to the CEB Corporate Leadership Council High Performance Survey, 2012, the average worker collaborates with 10 or more people to accomplish day to day tasks.  That is a lot of chatting.  We think that this chatting needs to be be tied to the business transaction you are working with and the best way to do that is by having the collaboration tool as part of your ERP application.  This is what you get with Oracle ERP Cloud.  I have discussed what social is in the ERP Cloud world in my earlier post Socializing the Finance Department that you can review to get a background.  The key message here is that the collaboration must be tied to a transaction.  Take a look at the video below to illustrate the point and see it in action.

I think you will agree this type of chatting, or as Alan Leposfsky (@alanlepo) of Constellation Research would call it, purposeful collaboration, clearly does make you and your organization more productive.


Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator - APPS Blogs