Sunday, February 14, 2016

ADF : VO Query Dynamic WHERE Clause

Recently, I faced a scenario where I need to dynamically change the View Object's query WHERE clause. I followed below steps to address it.

Steps:
  • Override the buildWhereClause(StringBuffer stringBuffer, int i) VO method
  • Call super.buildWhereClause() to allow the framework processing. This call will return a Boolean indicator of whether a WHERE clause was appended to the query, indicated by the Boolean appended local variable.
  • The StringBuffer parameter that is passed to the method is the complete query SQL statement. Do your changes directly onto it. When done, make sure that you return appropriately a true/false boolean to to the framework to indicate whether a WHERE clause was appended to the query or not.

Example: Add/append additional WHERE condition when logged in user is not an ADMIN.

@Override
protected boolean buildWhereClause(StringBuffer stringBuffer, int i) {
        // TODO Implement this method      
      boolean hasWhereClause = super.buildWhereClause(stringBuffer, i);
      boolean isAdmin = ADFContext.getCurrent().getSecurityContext().isUserInRole("ADMIN_ROLE");

      if (hasWhereClause) {
          if (!isAdmin) {
              stringBuffer.append(" AND (USER_EMAIL =  ' " + ADFContext.getCurrent().getSecurityContext().getUserName() + "')" );
           }
        } else {
          if (!isAdmin) {
              stringBuffer.append(" WHERE (USER_EMAIL =  ' " + ADFContext.getCurrent().getSecurityContext().getUserName() + "')" );
              hasWhereClause = true;
            }
        }
        return hasWhereClause;
    }

SOA : WSM-00138: The path to the certificate is invalid

Recently, I integrated a secured web service with Oracle SOA and faced following error when I tried to test the web service call. I used OWSM policy  'oracle/wss10_message_protection_client_policy' to sign the request message.

Error:
<Feb 03, 2016 10:30:56 AM CST>  <Error> <oracle.webservices.service> <OWS-04086> <javax.xml.rpc.soap.SOAPFaultException: oracle.fabric.common.FabricInvocationException: Unable to invoke endpoint URI "https://abc.xyz.com/WebService" successfully due to: oracle.fabric.common.PolicyEnforcementException: WSM-00276 : Validation failed for the identity published in the WSDL endpoint "https://abc.xyz.com/WebService".

Caused By: oracle.wsm.security.SecurityException: WSM-00138 : The path to the certificate is invalid.
 Validation failed for the certificate "Subject DN:- CN=dummy_key, OU=abc, O=XYZ, C=US, Serial Number:- 1430987244, Issuer DN:- CN=dummy_key, OU=abc, O=XYZ, C=US"
 Certificates in cert path used for validation are:-
"Subject DN:- CN=dummy_key, OU=abc, O=XYZ, C=US, Issuer DN:- CN=dummy_key, OU=abc, O=XYZ, C=US"
        at oracle.integration.platform.blocks.soap.WebServiceEntryBindingComponent.generateSoapFaultException(WebServiceEntryBindingComponent.java:1279)

Fix:
Imported additional(intermediate/identity) certificates(X509) associated with secured web service into SOA's Keystore as trusted certificates and rebooted server(s).

Notes:
I enabled WSSE debug flags in Weblogic Server setDomainEnv.sh to find out exact cause. Don't forget to disable the flags, otherwise you will confront excessive logging issue.

EXTRA_JAVA_PROPERTIES="${EXTRA_JAVA_PROPERTIES} -Dweblogic.wsee.verbose=* -Dssl.debug=true"
export EXTRA_JAVA_PROPERTIES

DB: ORA-01688: unable to extend table

Recently, I encountered following error in Oracle Database 12c log files.

Error:
ORA-02002: error while writing to audit trail
ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0
ORA-01688: unable to extend table AUDSYS.CLI_SWP$8ab1e924$1$1 partition HIGH_PART by 1024 in tablespace SYSAUX partition by in tablespace

Then I logged into Database 12c as SYSDBA and ran following queries to verify tablespace quota.

Query 1:
========
SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"
FROM   (SELECT tablespace_name,round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
       (SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name FROM dba_segments GROUP BY tablespace_name) tu
WHERE  df.tablespace_name = tu.tablespace_name;

Tablespace                      Used MB    Free MB   Total MB  Pct. Free
------------------------------ ----------     ---------- ---------- ----------
SYSAUX                               5075         45       5120          1
USERS                               21082       3677      24759         15
SYSTEM                                728         12        740          2

Query 2:
========
SQL> SELECT * FROM dba_tablespace_usage_metrics;

TABLESPACE_NAME    USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
SYSAUX                             649784          655360   99.1491699
SYSTEM                              93312          655360   14.2382813
USERS                             2699032        14028248   19.2399792

Just to rewind, a tablespace is where the physical Oracle meets the logical Oracle and a tablespace is a bit like a file system for your database. It is a logical entity (this means the tablespace only exists within the Oracle database itself, not outside of it) that provides storage space so that users can create things like tables and indexes. And one datafile can only be assigned to one tablespace.

And I fixed this issue by adding data file to SYSAUX tablespace using below command.

> ALTER TABLESPACE SYSAUX ADD DATAFILE '/xyz/oradata/data01/sysaux01.dbf' SIZE 200M AUTOEXTEND ON NEXT 20M MAXSIZE 5120M;

Note that I used 200M to indicate that I wanted to create the tablespace 200 Megabytes in size and the tablespace will auto extend in increments of 20 Megabytes until it reaches a maximum size of 5120 Megabytes. It's recommended to use auto extend on all tablespaces for Production databases.

Note, if you want to resize data file, use alter database command.

> ALTER DATABASE DATAFILE '/xyz/oradata/data01/sysaux01.dbf' resize 500M