ORA-16000: database open for read-only access when logging into replicated active dataguard database

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s