Sql error 16000

ORA-16000 on ADG standby via dblink to primary

Some day ago my collegues asked me about ORA-16000 error in application log.

[22/04/2013:13:42:40] xxxxxxxxxxxxxxxxx:sql execute error: ORA-16000: database open for read-only access

Small deviation.
We have some service who has support perform I/O read operation (like select) on standby side. Client connections always occur on standby side and PL/SQL Packages to decide, what user want to reading or to changing data (insert,update).

So, from application log provided by our application administrators, i can see which exactly procedure performed.
Let’s try perform that procedure.

SQL> begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
 2 /
begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
*
ERROR at line 1:
ORA-04053: error occurred when validating remote object
user.user@PRIMARY.SERVER
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Inside that procedure call another procedure wich will decide need to go to primary via dblink or not. Try perform it.

SQL> begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
 2 /
begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

But i see the same error. I’m don’t understand what happen. We should go to primary via dblink from standby and shouldn’t to see any error about read-only access.
Ok, let’s try to see error stack on standby.

SQL> alter system set events '16000 trace name errorstack level 3';

System altered.

SQL> begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
 2 /
begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
*
ERROR at line 1:
ORA-04053: error occurred when validating remote object
user.user@PRIMARY.SERVER
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

In trace.

*** 2013-04-22 16:33:52.444
*** SESSION ID:(2274.5) 2013-04-22 16:33:52.444
*** CLIENT ID:() 2013-04-22 16:33:52.444
*** SERVICE NAME:() 2013-04-22 16:33:52.444
*** MODULE NAME:(sqlplus@some.SERVER (TNS V1-V3)) 2013-04-22 16:33:52.444
*** ACTION NAME:() 2013-04-22 16:33:52.444

Managed Recovery: Real Time Apply enabled.
Managed Recovery: THROUGH ALL SWITCHOVER posted.
Managed Recovery: DISCONNECT posted.
Managed Recovery: Startup posted.

*** 2013-04-22 16:42:09.455
Managed Recovery: Cancel posted.

*** 2013-04-22 16:42:46.890
Managed Recovery: Real Time Apply enabled.
Managed Recovery: THROUGH ALL SWITCHOVER posted.
Managed Recovery: DISCONNECT posted.
Managed Recovery: Startup posted.

*** 2013-04-22 16:47:45.544
KQRCMT: Write failed with error=604 po=0x196d592118 cid=8
diagnostics : cid=8 hash=808b698f flag=2a

*** 2013-04-22 16:56:39.421
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-16000: database open for read-only access
----- Current SQL Statement for this session (sql_id=4yyb4104skrwj) -----
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null

Hmmm, i see attempt to update dictionary with objects. Now i’m understand why ORA-16000 error happened. Probably we have problem with shared_pool. Let’s try to flush it.

SQL> alter system flush shared_pool;

System altered.

No effect. Hmmm, ok, let’s try perform that procedure on primary.

SQL> begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
 2 /
begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
*
ERROR at line 1:
ORA-04062: timestamp of Package1 "user.Package1" has been changed
ORA-06512: at "user.Package2", line 72
ORA-06512: at line 1

Wow!!! Timestamp of package is changed! Try to perform one more.

SQL> begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
 2 /

PL/SQL procedure successfully completed.

SQL> begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
 2 /

PL/SQL procedure successfully completed.

Errors gone. Let’s try to perform on standby side.

SQL> begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
 2 /
begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
 *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'user.Package2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
 2 /
begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
 *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'user.Package1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I see other errors. Now standby like doesn’t know about our packages. I’m waited 10 min but nothing happened and i decided to restart instance 🙂

SQL> begin user.Package2.Procedure1(1130000010449084,null,null,null); end;
 2 /

PL/SQL procedure successfully completed.
SQL> begin user.Package1.Procedure1(1130000010449084,null,null,null); end;
 2 /

PL/SQL procedure successfully completed.

After that all packages work brilliant!

Obviously something happen in shared_pool. Our packages based on timestamp.
Unfortunately i can’t recompile packages on primary because we have lot of load system and i could catch latches on packages like (library cache pin).
But i think if i could recompile packages, this error can be resolved with recompile.

Errors 16000 — 16999

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;