Why do we have an ORA-16000 error when the DBA says privileges are correct?

[jonna]

Hi Community

Our source endpoint is Oracle but we get ORA-16000 when we try to connect - log entry below for info. Everything I can find in  Oracle community and support pages points towards this being mode in which the database is opened.

The DBAs have assured us the mode is correct and that all the requisite permissions from the Replicate Set Up User Guide have been granted. The DBAs are from a third party and explained that the  Oracle database is identical to another they have up and running of another organisation also using Replicate. 

Our initial thoughts are that this  is something on the database side, but thought we'd check in case anyone else has experienced it too.

Thanks very much

jonna

00018120: 2022-03-02T13:16:19:766704 [SERVER          ]T:  Going to execute the statement 'create or replace directory attrep_dir_test as 'attrep_dir_test'' (without select)  (oracle_endpoint_utils.c:3103)

00018120: 2022-03-02T13:16:19:813584 [SERVER          ]V:  Oracle error code is '16000'  (oracle_endpoint_utils.c:3113)

00018120: 2022-03-02T13:16:19:813584 [SERVER          ]T:  ORA-16000: database or pluggable database open for read-only access  [1022307]  (oracle_endpoint_utils.c:3113)

00018120: 2022-03-02T13:16:19:813584 [SERVER          ]T:  Create permission check failed for Oracle directory objects [1020103]  (oracle_endpoint_conn.c:514)

[Heinvandenheuvel · Accepted solution]

Is the source database an Oracle Dataguard configuration?

You'll get this error when connecting to a standby database.

Use the same connect information as the endpoint in SQLplus or SQLdeveloper and check: 

--- select open_mode from v$database;

I'm thinking this  is a DBA problem. If you need further help you may want tp export the task, download the json, and share the source endpoint definition from the databases section. Know that the exported password is hashed and considered safe, but obfuscate detailed values as needed.

Hope this Helps,

Hein.

[shashi_holla · Accepted solution]

In addition what Hien mentioned, please check with DBA team if the Oracle user which Replicate is connected has all the required permissions as per the user guide:

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/Oracle/OracleDBSource_Secu...

Thank you,

[jonna]

@yohei  & @shashi_holla 

Many thanks for getting back to me.  I have already questioned the DBA team wrt the permissions and the data base's OPEN MODE and I am waiting their response.

Your answers are gratefully received, so I will close the ticket down.

Cheers, jonna 

[qlikity_qlak]

We have a similar issue where our source is an Oracle DB that is the application's Disaster Recovery database.  The application does not allow outside access to the primary database, so we can only using Qlik Replicate to replicate data from the DR database.

When I run Hein's query:  select open_mode from v$database;

The result is: READ ONLY WITH APPLY

Also, I have confirmed that all the required permissions have been granted to the Replicate user as listed in the link provided by Shashi_holla: https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/Oracle/OracleDBSource_Secu...

My understanding is that Qlik Replicate can be configured to read from a "standby" database (in our case, the DR database) and believe we have done so properly, so we are confused as to why the application would still be trying to create a directory. 

Here is the error message below:

Cannot create Oracle directory name 'ATTUREP_45837ED102LS_ORA_LS_RO' with path '/oracle/product/db19c/dbhome_1/dbs'
OCI error 'ORA-16000: database or pluggable database open for read-only access'

This seems to be a very sporadic problem in our Dev/Test environment, so we would appreciate any additional advice or guidance from anyone who can help us to resolve this issue.

[Dana_Baldwin]

Hi @qlikity_qlak 

For the directory and subdirectories, Redo logs are in regular file system - we need to have a directory structure - hence the need for "Create Directory" that is why we either need the priveledge or DBA can create the directories for replicate.

Is it possible for the Replicate user to have CREATE ANY DIRECTORY?

Or if there are existing directory objects, can read permission be granted to the user defined in the Replicate source endpoint?

Thanks,

Dana

[qlikity_qlak]

Hi @Dana_Baldwin,

Our user (SVCQLIKRMSX) does have the CREATE ANY DIRECTORY privilege already.  I found a handy script to show all the permissions for our user.  See below:

ser => SVCQLIKRMSX has been granted the following privileges

====================================================================

ROLE => CONNECT which contains =>

SYS PRIV => CREATE SESSION grantable => NO

SYS PRIV => SET CONTAINER grantable => NO

ROLE => QLIK_KRO_READ which contains =>

SYS PRIV => ALTER ANY TABLE grantable => NO

SYS PRIV => CREATE ANY DIRECTORY grantable => NO

SYS PRIV => SELECT ANY DICTIONARY grantable => NO

SYS PRIV => SELECT ANY TABLE grantable => NO

SYS PRIV => SELECT ANY TRANSACTION grantable => NO

TABLE PRIV => EXECUTE object => SYS.DBMS_FILE_GROUP grantable => NO

TABLE PRIV => EXECUTE object => SYS.DBMS_FILE_TRANSFER grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_CATALOG grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_CONSTRAINTS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_CONS_COLUMNS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_ENCRYPTED_COLUMNS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_INDEXES grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_IND_COLUMNS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_LOG_GROUPS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_NESTED_TABLES grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_NESTED_TABLE_COLS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_OBJECTS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_TABLES grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_TAB_COLS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_TAB_PARTITIONS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_USERS grantable => NO

TABLE PRIV => SELECT object => SYS.ALL_VIEWS grantable => NO

TABLE PRIV => SELECT object => SYS.DBA_OBJECTS grantable => NO

TABLE PRIV => SELECT object => SYS.DBA_REGISTRY grantable => NO

TABLE PRIV => SELECT object => SYS.DBA_TABLESPACES grantable => NO

TABLE PRIV => SELECT object => SYS.ENC$ grantable => NO

TABLE PRIV => SELECT object => SYS.GV_$TRANSACTION grantable => NO

TABLE PRIV => SELECT object => SYS.OBJ$ grantable => NO

TABLE PRIV => SELECT object => SYS.V_$ARCHIVED_LOG grantable => NO

TABLE PRIV => SELECT object => SYS.V_$CONTAINERS grantable => NO

TABLE PRIV => SELECT object => SYS.V_$DATABASE grantable => NO

TABLE PRIV => SELECT object => SYS.V_$DATABASE_INCARNATION grantable => NO

TABLE PRIV => SELECT object => SYS.V_$LOG grantable => NO

TABLE PRIV => SELECT object => SYS.V_$LOGFILE grantable => NO

TABLE PRIV => SELECT object => SYS.V_$NLS_PARAMETERS grantable => NO

TABLE PRIV => SELECT object => SYS.V_$PARAMETER grantable => NO

TABLE PRIV => SELECT object => SYS.V_$STANDBY_LOG grantable => NO

TABLE PRIV => SELECT object => SYS.V_$THREAD grantable => NO

TABLE PRIV => SELECT object => SYS.V_$TIMEZONE_NAMES grantable => NO

TABLE PRIV => SELECT object => SYS.V_$TRANSPORTABLE_PLATFORM grantable => NO

ROLE => RESOURCE which contains =>

ROLE => SODA_APP which contains =>

TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_ADMIN grantable => NO

TABLE PRIV => EXECUTE object => XDB.DBMS_SODA_USER_ADMIN grantable => NO

TABLE PRIV => READ object => XDB.JSON$USER_COLLECTION_METADATA grantable =>

NO

SYS PRIV => CREATE CLUSTER grantable => NO

SYS PRIV => CREATE INDEXTYPE grantable => NO

SYS PRIV => CREATE OPERATOR grantable => NO

SYS PRIV => CREATE PROCEDURE grantable => NO

SYS PRIV => CREATE SEQUENCE grantable => NO

SYS PRIV => CREATE TABLE grantable => NO

SYS PRIV => CREATE TRIGGER grantable => NO

SYS PRIV => CREATE TYPE grantable => NO

SYS PRIV => CREATE SESSION grantable => NO

TABLE PRIV => EXECUTE object => SYS.ATTREP_DIR_TEST grantable => YES

TABLE PRIV => READ object => SYS.ATTREP_DIR_TEST grantable => YES

TABLE PRIV => WRITE object => SYS.ATTREP_DIR_TEST grantable => YES

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

I confirmed that all of these permissions match up with what is required in the user guide.  If you see anything that is missing, please let me know.  Thanks!

[john_wang]

Hello everyone, @jonna , @qlikity_qlak ,

Seems we are talking about 2 different topics: ORA-16000 (OPEN_MODE) & CREATE DIRECTORY. please allow me to add more comments (we are focusing on "Replicate Log Reader" but not "Oracle LogMiner", and the redo log is not ASM managed).

Qlik Replicate detects the Oracle source database role automatically, PRIMARY or STANDBY. Then if the source database is:

• PRIMARY role

            1. The database must be in read-write mode otherwise no apps or user programs access the database. Under this scenario Replicate should not get ORA-16000 (unless privilege issue). If got it, DBA need to alter the standalone database or the PDB open mode to read-write. See @Heinvandenheuvel SQL to verify the open mode.

            2. Replicate will try to find a DIRECTORY which maps to an Oracle redo log path (both ONLINE and ARCHIVED). If the directory exists already (created by DBA or other account), Replicate will use it; otherwise, Replicate will try to create such a directory. This is why CREATE ANY DIRECTORY privilege is required.

            3. If the account has *NOT* CREATE ANY DIRECTORY privilege, and the directories do not exist, Replicate reports error. Note this is Oracle mandatory prerequisites.

• STANDBY role

            1. The database is in read-only mode. however it accepts PRIMARY node synchronization operations, for example if a directory is created in PRIMARY node, then the directory will be synchronized to STANDBY node after the internal cycle.

               More information about Supported standby configurations.

            2. Replicate cannot create directory in read-only mode database (or Disaster Recovery database), trying to do so will trigger error ORA-16000, the operations should be done on PRIMARY node and waiting for it being transferred to STANDBY node. After the DIRECTORIES definition is synchro then Replicate can use it in STANDBY role node.

               If I understood correctly, this is exactly @qlikity_qlak  scenario.

Hope this helps.

Regards,

John.

[qlikity_qlak]

Hi @john_wang,

Thanks for the extensive information in your post.  This topic has become relevant for us again since we just experienced this error in Prod last night (only 2nd time in about 3 months).

In your last post, you mentioned focusing on "Replicate Log Reader" and the redo log is not ASM managed.  In our configuration, the redo logs are ASM managed.

Just to reiterate, everything in our setup is configured to the standby node (Database connection & ASM connection).   Do you have any additional guidance for this type of configuration?

Thanks for your help!

Steve

[john_wang]

Hello @qlikity_qlak ,

Thanks for the update.

In your last post, you mentioned focusing on "Replicate Log Reader" and the redo log is not ASM managed.  In our configuration, the redo logs are ASM managed.

---

Replicate does NOT need directory to read the redo log files if they are ASM managed. Under this scenario the error ORA-16000 (OPEN_MODE) should not be triggered by create/replace directory. however there are other factors that may trigger the error still. please open a support ticket (attach Diag Packages , it's better with source_capture setting to Verbose), let's trace it further.

Regards,
John.