IBM Support

Error 'ORA-01917: user or role 'FTIQA' does not exist' when importing Controller Oracle database

Troubleshooting


Problem

Oracle DBA tries to import Controller user/schema into Oracle database, using a command similar to the following: imp system/password@databasename file=exp_SOURCEUSERNAME.dmp log=DESTINATION_USERNAME-imp.log fromuser=SOURCEUSERNAME touser=DESTINATION_USERNAME DBA receives error message.

Symptom

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by ADMIN_USERNAME, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing SOURCEUSERNAME's objects into DESTINATION_USERNAME
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_GTCSMMTN_ALS" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'FTIQA' does not exist
IMP-00017: following statement failed with ORACLE error
1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_GTCSMMTN_SM" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'FTIQA' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_GTCSMMTN_SMD" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'FTIQA' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "CCOJ_ICMPNJRNLS_JRNLS_ALS" TO "FTIQA""
IMP-00003: ORACLE error 1917 encountered...

Cause

The environment from where the original backup (export / .DMP) file was created is using the (optional) Oracle feature which creates additional grants on objects in the Controller schema.

Resolving The Problem

Delete the schema, then re-create a blank schema. Finally, import the user (into the new schema) but this time use the optional switch:
grants=no.

Steps:
Modify your import script, to something similar to:

    imp system/password@databasename file=exp_SOURCEUSERNAME.dmp log=DESTINATION_USERNAME-imp.log fromuser=SOURCEUSERNAME touser=DESTINATION_USERNAME grants=no

[{"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;8.3;10.1;10.1.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1039437

Document Information

Modified date:
15 June 2018

UID

swg21371533