IBM Support

"ORA-29283: invalid file operation ... ORA-06512: at "SYS.UTL_FILE", line 488" when running report using Optimise2

Troubleshooting


Problem

User runs a report which is configured to use Optimise2 (ERO). User receives error message.

Symptom

Information
Standard Error
Number: 5
Source: FrangoDirect.ExcelLinkD.FetchValuesBulk#ControllerProxyClient
Description: System.Web.Services.Protocols: Server was unable to process request. ---> System.Runtime.InteropServices.COMException (0x80040E14): ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 100 Server: <servername> Share: <sharename> User: <username> Psw: ***** Transfer: 1 Prefix:.......
<..........>
[OK]

Cause

Incorrect configuration of the Oracle database server.

More Information
The error "ORA-29283: invalid file operation" is an Oracle-generated error. The error occurs when the Oracle database server is trying to read from a file or directory but either:

  • the file/directory does not exist
  • or the file or directory access was denied by the operating system.

In this case, Controller's Optimise2 functionality has copied a file to a share (or FTP site) located on the Oracle server. Afterwards, Oracle server is trying to read this file, but it is failing.

The Oracle server:
  1. will look for his file inside the directory which is defined in the "SYS.UTL_FILE" Oracle database variable.
  2. requires read permissions to this folder
  3. will block access if the relevant Oracle schema has not been granted access to this folder.

Therefore, the error will occur if any of the following scenarios are true:
  • Scenario #1 - The Oracle database variable (parameter) "SYS.UTL_FILE" has been incorrectly defined
    • For example there is a typo mistake.
  • Scenario #2 - The operating system user (e.g. Windows user or UNIX user) which runs the Oracle database server does not have read permissions on this folder.
  • Scenario #3 - The Oracle schema (the one used by Controller, for example 'fastnet') does not have rights to read the folder.

Environment

Controller database hosted on Oracle database server.

Diagnosing The Problem

Workaround:

Disable the use of ERO ("Optimise2"), by removing the value 'Optimise2' from the 'Name' of cell A1 of the spreadsheet.

  • TIP: For more information, see separate IBM Technote #1347048.


Steps:
The following instructions are based on Excel 2007.
  • The instructions may need to be modified slightly if you are using a different version of Excel.

1. Launch Microsoft Excel
2. Open the 'bad' spreadsheet
3. Click the tab 'Formulas'
4. Click 'Name Manager'
5. Highlight the entry 'Optimise2' (which refers to Sheet1, cell A1)
6. Click 'Delete':

7. Save changes and test.

Resolving The Problem

Scenario #1
Check that the Oracle database variable (parameter) "SYS.UTL_FILE" has been correctly defined

  • It must point to the same folder that is used by Controller Configuration's "Enhanced Reporting Optimisation" setting on the Controller application server.
    Steps:

    1. Locate the value for the Oracle Initialization parameter "SYS.UTL_FILE" for your Controller application's database

    TIP: There are several different methods to check this value:

    • One method is to simply run the following Oracle query:
    SELECT value FROM v$parameter WHERE name = 'utl_file_dir';
    • Another method (suitable for Oracle 10G) is to use "Enterprise Manager Console" (see below):



    2. On the Controller application server, launch "Controller Configuration". Inside the section "Enhanced Reporting Optimization", choose the relevant database inside 'Select connection'. Check the values that you are using to connect to your Oracle server (for example see below):


    3. Check that the values you found in step 2 correctly relate to the folder location that you have defined in step 1.

    IMPORTANT:
    • For the avoidance of doubt, we are not saying that the values in step 2 should be exactly identical to the values in step 2. Instead, they have to logically relate to the same place/location.

Scenario #2
Check that the Oracle database server's operating system user has read permissions to this folder.
  • Windows: By default, Oracle database servers run using the local "System" Windows user account.
  • UNIX: Often Oracle database servers run using a UNIX user called "oracle".

IMPORTANT:
  • For the avoidance of doubt, the user account that we are talking about is the operating system user account which is running the Oracle database server process (not an Oracle schema).

Steps:

1. Check what is the username that is running your Oracle database server


    TIP: When running Oracle on Windows, this is the user who runs the "OracleService<databasename>" service, which (by default) is the Windows "Local System" account (see below):

    2. Ensure that the user (located in step 1) has read/write permissions to the folder (located in step 1).

    TIP:

    • If hosting Oracle on Windows, then simply check the NTFS permissions on that folder.
    • If using Linux/UNIX then one method of testing/diagnosing is to try the following:
      1. Check whether the NFS mount point for the folder <SYS.UTL_FILE> is available in the Oracle database machine. TIP: You can use Unix bdf command to check this.
      2. Check if there is any directory permission problem by creating a file manually from the shell of Oracle database machine. TIP: You can use the SQL command select hostcommand(‘<SYS.UTL_FILE> /<oracle_user>/apps/nfs/interface/testing.txt’) from dual; to create testing file from SQL*Plus or TOAD.
      3. Afterwards, grant necessary access for the database user (for example "oracle") if required by using the command "chmod". For example, to give full read/write access run the following UNIX command:
        chmod 777 /smb/optimise2

Scenario #3


Check that the Oracle schema used by Controller (specified inside "Database Connections" inside "Controller Configuration") has been given rights (inside Oracle itself) to read the relevant folder.
    Steps:

    Let us imagine a scenario where:

    • The Oracle database server is being hosted on Unix/Linux
    • The Oracle schema (used by Controller) is called "fastnet"
    • The location of the FTP site (or SAMBA share) is: /smb/optimise2

    1. Launch SQL Plus

    2. Logon as SYS (as SYSDBA)

    3. Run the following script:

      create or replace directory optimise2 as '/smb/optimise2'
      grant read, write on directory transfer to fastnet;

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"8.5.1;8.5;8.4","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21501368

Page Feedback