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;
