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)

[qlikity_qlak]

Thanks @john_wang.  I have opened case # 00102785 for our particular issue related to the confusing ORA-16000 & "Cannot create Oracle directory" errors.

[john_wang]

Hello @qlikity_qlak ,

Thanks for opening the case. I'd like to summarize the RCA of this issue (this article and support case #00102785😞

1- If the redo log files (both archived and online redo log files) are ASM managed then Replicate does NOT need directory to read the redo log files. Instead, the dbms_diskgroup API package is used to access ASM managed redo log files. In this scenario, there is no chance to get the error ORA-16000 (database or pluggable database open for read-only access ).

2- However in some very rare scenario the redo log files were not ASM managed but written to OS filesystem, then Replicate will try to create directory on the read-only node, that's we got error ORA-16000. For example in your env, if sometimes the system runs short of ASM disk space for the archive logs and Dataguard has to ship the archive logs to a filesystem in $ORACLE_HOME/dbs. DBA will allocate more free space to fix the space shortage issue.

Thank you for your great support.

Regards,

John.