Troubleshooting
Problem
Administrator logs onto the Controller application server, and launches 'Controller Configuration'. Administrator opens section 'Database connections for publish to datamart' and fills in the connection details. Administrator clicks 'save' and then receives error message #1. After acknowledging the error message, user attempts to perform a Data Mart publish (to the target 'Cognos BI'). User receives error message #2
Symptom
Error #1 (screen)
IBM Cognos 8 Controller Configuration
A unknown error has occured: System.Data.OleDb.OleDbException: ORA-01031: insufficient privileges
at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBacks, BindingFlags Flags)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateGet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack)
at Cognos.Controller.Configuration.ControllerConfiguration.toolbar_ButtonClick(Object sender, ToolbarButtonClickEventArgs e)
ORA-01031 : Insufficient privileges
OK
Error #2 (screen)
<template ID> Overflow
Error #2 (Event Log)
Event Type: Warning
Event Source: Cognos Controller
Event Category: None
Event ID: 0
Date: 9/25/2008
Time: 11:26:49 AM
User: N/A
Computer: SERVERNAME
Description:
Error occured at 9/25/2008 11:26:49 AM in Cognos Controller, Error No=6, Source=DMPublishD.PublishTemplate, Description=Overflow, HelpFile= HelpContext=0
Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 9/25/2008
Time: 11:26:49 AM
User: N/A
Computer: SERVERNAME
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 3920 ,Logged: Error occured at 9/25/2008 11:26:49 AM in FrSrvFunc, Error No=-2147217900, Source=Server Sub:Db_ExecuteSql(..) , Description=ORA-06550: line 1, column 254:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 254:
PL/SQL: SQL Statement ignored, HelpFile= HelpContext=0
Event Type: Warning
Event Source: VBRuntime
Event Category: None
Event ID: 1
Date: 9/25/2008
Time: 11:26:49 AM
User: N/A
Computer: SERVERNAME
Description:
The VB Application identified by the event source logged this Application FrSrvFunc: Thread ID: 3920 ,Logged: Error occured at 9/25/2008 11:26:49 AM in FrSrvFunc, Error No=-2147217900, Source=Server function:Dbp_ExecuteImmediate(..) , Description=ORA-06550: line 1, column 254:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 1, column 254:
PL/SQL: SQL Statement ignored, HelpFile= HelpContext=0
Cause
There are other potential causes for the 'Overflow' screen message described in Error #2 (for example the Data Mart is full or the Oracle client cannot resolve the database name).
- See separate Technotes referred to at the end of this article for help on these other possible causes..
This IBM Technote relates to the scenario where the message includes the phrase "ORA-01031 : Insufficient privileges".
- In this scenario, the cause is that the Oracle schema (user) used does not have sufficient Oracle permissions/roles.
NOTE: It is important that you check both the Controller 'application' schema (for example it amy be called "ControllerLIVE") and the 'data mart' schema (for example "ControllerLIVEdatamart").
Environment
Data mart target is Oracle 10G or 11G database.
Resolving The Problem
Modify the Oracle permissions of the Oracle user (schema) which you are using to ensure that they have the required permissions.
- TIP: For more information on the required Oracle permissions, see separate IBM Technote #1652173.
A) To locate your user / schema names:
- Logon to the Controller application server as an administrator
- Launch 'Controller Configuration' from the Start Menu
- Open the section 'Database connections'
- Click on the relevant database connection name (for example 'default')
- Locate the value for 'User ID' (for example 'controllerlive') - this is your application repository's Oracle schema name (user name)
- Open the section 'Database connections for publish to Data Mart'
- Change the value of 'Select database' to the relevant database connection name (for example 'default')
- Locate the value for 'User ID' (for example 'controllerlive_datamart') - this is your data mart's Oracle schema name (user name)
B) Alter an existing user / schema to have correct roles:
1. Logon to Oracle server as an administrator
2. Launch 'SQLPlus Worksheet'. TIP: If you cannot find the icon, by default (for Oracle 10G) this is 'C:\oracle\product\10.2.0\client_1\BIN\oemapp.bat worksheet'
3. Logon to the correct database using the administrative user 'system'
4. Execute the script (below) for the application repository's Oracle schema name (user name). NOTE: replace "schemaname" with the correct user (for example 'controllerlive_datamart'), "password" with the password, and "controller_table" with your preferred default tablespace name.
- create user schemaname
identified by password
default tablespace controller_table;
grant create session to schemaname;
grant alter session to schemaname;
grant create table to schemaname;
grant create database link to schemaname;
grant create sequence to schemaname;
grant create trigger to schemaname;
grant create view to schemaname;
grant create procedure to schemaname;
grant create materialized view to schemaname;
grant create synonym to schemaname;
grant create job to schemaname;
grant select_catalog_role to schemaname;
grant unlimited tablespace to schemaname;
grant execute on dbms_lock to schemaname;
5. Execute the same script (above), but this time for the data mart's Oracle schema name (user name).
Related Information
Historical Number
1041375
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21367322