Users encountered this error when connecting to a version 11.1.0.7.0 replicated active data guard database via ODBC:
“Error Text ”[IBM][ODBC Oracle Wire Protocol driver][Oracle]
ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access”.
The error has the following diagnostic information: SQL State ”HY000”
SQL Native Error Code ‘604’ SQL Error Text ”[IBM][ODBC Oracle Wire Protocol driver][Oracle]
ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access””
I verified the error when trying to log in to the same database with identical user information.
Earlier in the day, I had examined that particular user’s information and privileges using a cloning script executed from the primary database in the data guard pair. This script has been used numerous times before in other environments without modifying the database it was executed against as it only performs ‘select’ queries.
I checked the replication status on the primary and replicated environment with several queries. The queries and their output are as follows:
Executed against the primary database:
select name
from v$database;
select sequence#, applied
from v$archived_log
where trunc(first_time) = trunc(sysdate)
order by 1;
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Mar 19 16:16:44 2015 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SEQUENCE# APPLIED ---------- --------- 16863 NO 16863 YES 16864 NO 16864 YES 16865 NO 16865 YES 16866 NO 16866 YES 16867 NO 16867 YES 45 rows selected.
NOTE – Each set of archived log has two application entries – one for the primary itself which is always a value of ‘NO’ for applied in this view and the other for the replicated environment. Since the value of the latest archived log is ‘YES’, this log has been applied to the replicated (secondary) database.
These next queries are executed against the replicated (secondary) database:
select sequence#, applied
from v$archived_log
where trunc(first_time) = trunc(sysdate)
order by 1;
select process, sequence#, status
from v$managed_standby;
SEQUENCE# APPLIED ---------- --------- 16863 YES 16864 YES 16865 YES 16866 YES 16867 YES 15 rows selected. PROCESS SEQUENCE# STATUS --------- ---------- ------------ ARCH 16867 CLOSING ARCH 0 CONNECTED ARCH 16865 CLOSING ARCH 16866 CLOSING MRP0 16868 WAIT_FOR_LOG RFS 0 IDLE RFS 0 IDLE RFS 16868 IDLE RFS 0 IDLE 9 rows selected. SQL> Disconnected from Oracle Database 11g Enterprise Edition Release SQL> 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
The first query is simply verifying that the archived logs have been applied. The second query let’s me know what the data guard processes are doing. Looks like its waiting for the next log from the primary.
My next thought was to restart the replication process on the replicated database, but since this database was already in sync, this action would likely have done no good. I thought next to refresh the password of the user using the output obtained from the earlier execution of the cloning script I mentioned. I simply executed an ‘alter user’ statement on the primary changing the user’s password to the same value it currently was. I then executed ‘alter system switch logfile’ a few times to ensure propagation of the change to the replicated environment. This fixed the problem. Post-problem research did not yield any similar situations, possible causes, or bugs. I guess I will have to be satisfied with being able to fix this the next time it happens.
The contents of the user cloning script are included here for reference. This script has proven very useful and has been proven in versions from 10.2.0.4.0 to 11.2.0.4.0.
-- userclone.sql -- 10/17/00 J. Adams -- Purpose: Generates a SQL script to create a new user -- based on the priviledges of another user -- -- Based upon this script... -- Date Created: June 20, 2000 -- Author: Mark Huber -- Modified - -- 4/24/02 J. Adams - Corrected erroneous quota grant statement -- 7/14/03 J. Adams - Modified erroneous role section (there is no -- 1 or 0 in the admin_option column). -- 6/3/05 J. Adams - Fixed problem with object privileges not being displayed. -- 12/5/05 J. Adams - Altered script to obtain password of original user for cloned user. -- 10/13/09 J. Adams - Altered create user query to include Oracle version 11g clear screen; clear breaks; clear buffer; set verify off; set heading off; set feedback off; set linesize 150; Accept user_in prompt "Enter the USER To create LIKE ..... " Accept new_user_in prompt "Enter the NEW USER To create ..... " --Accept new_user_pw_in prompt "Enter the NEW USER Password ..... " Accept outfile prompt "Enter the Output filename including path: " col username noprint; col lne newline; spool &&outfile col username noprint col lne newline -- ** create user SELECT username, 'CREATE USER '||UPPER('&&new_user_in')||' '|| DECODE(nvl(a.password,spare4), 'EXTERNAL', 'IDENTIFIED EXTERNALLY', 'IDENTIFIED BY VALUES '||''''||nvl(a.password,spare4)||''''||' ') lne, 'DEFAULT TABLESPACE '||b.default_tablespace lne, 'TEMPORARY TABLESPACE '||b.temporary_tablespace||';' lne FROM sys.USER$ a, dba_users b where a.name = b.username AND USERNAME = UPPER('&&user_in') / -- **create users tablespace quotas SELECT username, 'ALTER USER '||upper('&&new_user_in')||' QUOTA '|| DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K') ||' ON '||tablespace_name||';' FROM DBA_TS_QUOTAS WHERE USERNAME = UPPER('&&user_in') / -- ** create user roles select grantee username, 'Grant '||GRANTED_ROLE||' to'||' '|| upper('&&new_user_in')||' '|| DECODE(admin_option,NULL,null||';', 'NO',null||';', 'YES',' WITH GRANT OPTION;', null||';') from dba_role_privs where grantee = upper('&&user_in') / -- ** create user system privs SELECT 'Grant '||C.Name||' to'||' '|| upper('&&new_user_in')||' '|| DECODE(B.Option$,NULL,null||';', 0,null||';', 1,' WITH GRANT OPTION;', null||';') FROM SYS.SYSAUTH$ B , SYSTEM_PRIVILEGE_MAP C , SYS.USER$ D WHERE D.Name = upper('&&user_in') AND D.User# = B.Grantee# AND B.Privilege# = C.Privilege / -- ** create object privs SELECT 'Grant '||C.Name||' ON '|| A.Owner||'.'||A.Object_name|| ' TO '||upper('&&new_user_in')|| DECODE(B.Option$,NULL,null||';', 0,null||';', 1,' WITH GRANT OPTION;', null||';') FROM ALL_OBJECTS A , SYS.OBJAUTH$ B , TABLE_PRIVILEGE_MAP C , SYS.USER$ D WHERE D.Name = upper('&&user_in') AND D.User# = B.Grantee# AND A.Object_Id = B.Obj# AND B.Privilege# = C.Privilege / -- Create any synonyms owned by cloned user select 'create synonym '||upper('&&new_user_in')||'.'||synonym_name||' for '|| table_owner||'.'||table_name||';' from dba_synonyms where owner = upper('&&user_in') order by synonym_name / spool off; set verify on; set heading on; set feedback on;