Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
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:
Thank you,
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.
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:
Thank you,
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
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.
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
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:
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!
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:
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.
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.
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
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.