ORA-16000 from ADG standby via dblink to primary

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.

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