OBIEE Out of Disk Space

Error Details

Error Codes: AAD5E5X3:OPR4ONWY:U9IM8TAC
Odbc driver returned an error (SQLFetchScroll).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46118] Out of disk space. (HY000)

We can overcome this issue by clearing up some cache files. 

1) WebLogic Server Log Files

Check your Admin and Managed server log directories and remove any old log files that you no longer exist:

[Middleware Home]/user_projects/domains/bifoundation_domain/servers/bi_server1/logs
[Middleware Home]/user_projects/domains/bifoundation_domain/servers/AdminServer/logs

2) Oracle BI System Log Files

Check all the sub-folders underneath the following directory and remove any old .log files:

[Middleware Home]/instances/bi_instance/diagnostics/logs

3) Oracle BI "tmp" Folder for Cache Entries

If an Oracle BI process dies unexpectedly for any reason it mean that tmp and cache files do not get cleared up.   So check all the folders and sub-folders in the following location and remove any old files:

[Middleware Home]/instances/bi_instance/tmp

4) Core Dump Files

Some processes will create core dump files/folders to be created if they ever die unexpectedly.     So it would be worth removing any "core_" files and folders which exist in the following locaiton:

[Middleware Home]/instances/bi_instance

5) Oracle BI Patch Files

After you have applied a patch to Oracle BI 11g, you can remove the extracted patch folders once they have been applied.   The patch files would have probably been extracted into sub-folders within the following locations:

[Middlware Home]/Oracle_BI1                   e.g. /app/oracle/middleware/Oracle_BI1/7843472

[Middlware Home]/Oracle_common

If issue still not resolved then we can change the workdirectory to the new mount point in NQSconfig.ini and restart the services.

Oracle Apps DB: Active Sessions

SELECT DISTINCT fu.user_name user_name              
               ,to_char(ic.first_connect, 'DD-Mon-YYYY HH12: MI: SS AM') "First conn"
                ,to_char(ic.last_connect, 'DD-Mon-YYYY HH12: MI: SS AM')"Last conn"
                ,decode((ic.disabled_flag), 'N', 'ACTIVE', 'Y', 'INACTIVE') status
                ,fvl.responsibility_name "Resp"
                ,ic.function_type
                ,ic.time_out
                ,fu.user_id
                ,ic.org_id
                ,fr.menu_id
  FROM fnd_user                   fu
      ,fnd_responsibility         fr
      ,icx_sessions               ic
      ,apps.fnd_responsibility_vl fvl
 WHERE fu.user_id = ic.user_id
   AND fr.responsibility_key = fvl.responsibility_key
   AND fr.responsibility_id = ic.responsibility_id
   AND ic.disabled_flag = 'N'
   AND ic.responsibility_id IS NOT NULL
   AND ic.last_connect > SYSDATE - (ic.time_out / 60) / 96;

NOTE: Copied Code. 

Oracle DB : TableSpace Used Details

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
--and rownum < 5
ORDER BY 1;

NOTE: Copied Code. 

Oracle DB: CPU Usage By USER

Displays CPU usage for each User. Useful to understand database load by user.


SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

NOTE: Copied Code. 

OBIEE 11g Configuration for MS SQL Database

Step 1: Apply the updated/recent ODBC driver.

Considered , OBIEE version: 11.1.1.7.1 and Linux 64 bit server. 

Need to download the latest Data driver 7.1.5 from https://support.oracle.com

Patch #: 21440314

Apply the patches as per the Oracle instructions. Post to that we need to manually copy and unzip the driver to below location.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant

Step 2: System DSN Setup.

Create a System DSN to connect to MS SQL database

Step 3: OPMN.XML file Configuration.

Navigate to below location to do the change for opmn.xml file.

/u01/BIUAT/OBIEE/instances/instance1/config/OPMN/opmn

Backup copies of opmn.xml. Make edits to opmn.xml.

Search for BI components in opmn.xml. These are XML elements, <ias-component>, with the id "coreapplication_obi<...>".

In each of these BI component elements, modify and make sure that these environment variables point to recent ODBC driver.    

 $LD_LIBRARY_PATH

 $PATH

Step 4: ODBC.INI file Configuration.

Navigate to below location to do the change for odbc.ini file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup of odbc.ini file.

Need to do below changes for [ODBC] tag. TraceDll and InstallDir need to be pointed to new data driver.

Need to modify the below mentioned line for MS SQL Data driver section.

·         Tag name should be same as System DSN name which we have created.

·         Driver path should point to 7.1.5 .

·         Database

·         EnableQuotedIdentifiers should set to 1.

·         HostName. ( Along with Instance if any)

·         PortNumber

Once this changes are done , we need to copy the file to new Data driver folder as well.

/u01/BIUAT/OBIEE/Oracle_BI1/common/ODBC/Merant/7.1.5

Step 5: USER.SH file Configuration.

Navigate to below location to do the change for user.sh file.

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Backup the user.sh file.

In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:

·         For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.

·         For HP-UX, the library path variable is SHLIB_PATH.

·         For AIX, the library path variable is LIBPATH.

Step 6: BI-INIT.SH file Configuration.

bi-init.sh file used to source env , should point to the DataDirect 7.1.5 driver

Back up bi-init.sh, usually located under

/u01/BIUAT/OBIEE/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

Make edit to bi-init.sh.

Modify the EPM_ODBC_DRIVER_DIR parameter to point it the location of the DD 7.1.5 ODBC driver library directory.

   For example: EPM_ODBC_DRIVER_DIR=$ORACLE_HOME/common/ODBC/Merant/7.1.5/lib

   Note that bi-init.sh is only needed only for running OBIEE applications directly from a shell command line.

Step 7: RPD Changes.

Create a new connection pool to connect to MS SQL database.

Disable function "NESTED_ORDERBY_SUPPORTED" in the rpd .

Step 8: Restart BI Services.

Restart all servers (AdminServer and all Managed server(s))

Enjoy and Stay Well !! :)

Enable CLOB Field in OBIEE

Today we are going to discuss how to enable CLOB field in OBIEE. Below are the steps which need to be followed.

Lets assume , ACTION_ITEM table has a column called ACTION_TAKEN which datatype is CLOB.

1. There is not CLOB datatype in OBIEE RPD. For CLOB please select LONGVARCHAR type in RPD physical layer.

 2.   Its mandatory to define a Keys for that Physical Table in RPD.

3.Open the same column in BMM layer. Select that particular column and click on Edit under “Column Source Type”.

4. Open that Column from Logical Table Source.

5. Need to use LOOKUP DENSE funcation.

Syntax : LOOKUP (DENSE  CLOB Column, PK Column)

6. Move it to Presentation layer and use the same in BI report.

Enjoy and Stay Well !! :)

Oracle AP Invoice Distributions Query

Oracle AP Invoice Distributions Query

/* Formatted on 12/28/2016 8:53:03 AM (QP5 v5.126.903.23003) */
SELECT   AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER,
         AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATED_BY,
         AP_INVOICE_DISTRIBUTIONS_ALL.LAST_UPDATE_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.CREATED_BY,
         AP_INVOICE_DISTRIBUTIONS_ALL.CREATION_DATE,
         AP_INVOICES_ALL.INVOICE_ID,
         AP_INVOICES_ALL.INVOICE_NUM,
         AP_INVOICES_ALL.INVOICE_CURRENCY_CODE,
         AP_INVOICES_ALL.VENDOR_ID,
         AP_INVOICES_ALL.VENDOR_SITE_ID,
         AP_INVOICES_ALL.INVOICE_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE,
         AP_INVOICES_ALL.INVOICE_RECEIVED_DATE,
         AP_INVOICES_ALL.CREATION_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.ORG_ID,
         PO_HEADERS_ALL.SEGMENT1,
         PO_LINES_ALL.LINE_NUM,
         PO_HEADERS_ALL.CREATION_DATE,
         AP_INVOICES_ALL.LAST_UPDATE_DATE,
         PO_HEADERS_ALL.LAST_UPDATE_DATE,
         PO_LINES_ALL.LAST_UPDATE_DATE,
         PO_LINES_ALL.ITEM_DESCRIPTION,
         AP_INVOICE_DISTRIBUTIONS_ALL.DESCRIPTION,
         AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE,
         AP_INVOICES_ALL.WFAPPROVAL_STATUS,
         AP_INVOICES_ALL.PAYMENT_STATUS_FLAG,
         AP_INVOICES_ALL.CANCELLED_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_DATE,
         PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG,
         AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID,
         AP_INVOICES_ALL.TERMS_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_DATE,
         AP_INVOICES_ALL.EXCHANGE_DATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE,
         AP_INVOICES_ALL.EXCHANGE_RATE_TYPE,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE,
         AP_INVOICES_ALL.EXCHANGE_RATE,
         AP_INVOICE_DISTRIBUTIONS_ALL.ACCTS_PAY_CODE_COMBINATION_ID,
         AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID,
         AP_INVOICES_ALL.SET_OF_BOOKS_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.QUANTITY_INVOICED,
         AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT,
         AP_INVOICE_DISTRIBUTIONS_ALL.BASE_AMOUNT,
         CASE
            WHEN AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE IS NULL
            THEN
               PO_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
            ELSE
               AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE
         END,
         AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_PRICE_VARIANCE,
         AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_VARIANCE,
         PO_VENDORS.RECEIPT_REQUIRED_FLAG,
         PO_VENDORS.INSPECTION_REQUIRED_FLAG,
         PO_VENDOR_SITES_ALL.PURCHASING_SITE_FLAG,
         PO_VENDORS.LAST_UPDATE_DATE,
         AP_INVOICES_ALL.SOURCE,
         AP_INVOICES_ALL.PROJECT_ID,
         AP_INVOICES_ALL.TASK_ID,
         AP_INVOICES_ALL.EXPENDITURE_TYPE,
         AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID,
         AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE,
         AP_INVOICES_ALL.PAYMENT_CROSS_RATE,
         AP_INVOICES_ALL.REQUESTER_ID,
         AP_INVOICE_LINES_ALL.LINE_NUMBER,
         AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE,
         AP_INVOICE_LINES_ALL.REQUESTER_ID,
         AP_INVOICE_LINES_ALL.DESCRIPTION,
         AP_INVOICES_ALL.LEGAL_ENTITY_ID,
         AP_INVOICE_DISTRIBUTIONS_ALL.DIST_MATCH_TYPE
  FROM   AP_INVOICE_DISTRIBUTIONS_ALL,
         AP_INVOICE_LINES_ALL,
         AP_INVOICES_ALL,
         PO_LINES_ALL,
         PO_HEADERS_ALL,
         PO_VENDORS,
         PO_VENDOR_SITES_ALL,
         PO_DISTRIBUTIONS_ALL
 WHERE   AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID = AP_INVOICES_ALL.INVOICE_ID
         AND AP_INVOICES_ALL.VENDOR_ID = PO_VENDORS.VENDOR_ID(+)
         AND AP_INVOICES_ALL.VENDOR_SITE_ID =
               PO_VENDOR_SITES_ALL.VENDOR_SITE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID =
               PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID =
               PO_HEADERS_ALL.PO_HEADER_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_HEADER_ID = PO_LINES_ALL.PO_HEADER_ID(+)
         AND PO_DISTRIBUTIONS_ALL.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID(+)
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID =
               AP_INVOICE_LINES_ALL.INVOICE_ID
         AND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_LINE_NUMBER =
               AP_INVOICE_LINES_ALL.LINE_NUMBER