Active Data Guard – limitations on ROWTYPE

Posted by

I had an AskTOM question come in with an issue trying to PL/SQL on an Active Data Guard (ADG) database (which of course is running in read-only mode). The PL/SQL block seems innocuous; it does not DML and yet refuses to run:



SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

I did a trace on the code, and made an interesting discovery, and hence the cause of the error. When we reference a ROWTYPE definition, we need a mechanism to refer to and compile against that definition of that type. Hence we temporarily “create” a type definition to handle that. Tracing the above anonymous block, you’ll see the following entries in the trace file



********************************************************************************

insert into "SYS"."KOTTD$" (SYS_NC_OID$, SYS_NC_ROWINFO$) values (:1, :2 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute      1      0.00       0.00          0         67          4           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0         70          4           1

********************************************************************************

insert into "SYS"."KOTTB$" (SYS_NC_OID$, SYS_NC_ROWINFO$) values (:1, :2 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute      1      0.00       0.00          0         67          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0         70          3           1

********************************************************************************

insert into "SYS"."KOTAD$" (SYS_NC_OID$, SYS_NC_ROWINFO$)  values (:1, :2 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute      1      0.00       0.00          0         97         24           8
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0        100         24           8

********************************************************************************

insert into "SYS"."KOTTBX$" (SYS_NC_OID$, SYS_NC_ROWINFO$)  values (:1, :2 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          3          0           0
Execute      1      0.00       0.00          0         67          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0         70          3           1

In a read-only environment, that is a problem, because we cannot perform DML, even against the data dictionary. However, if we make this scenario a little more realistic in terms of why we would want to run a PL/SQL block against an ADG environment, it is probably to perform some more “robust” data processing. In this instance, you can use some of the ADG extensions to redirect that operation back to the primary



SQL> alter session enable adg_redirect_plsql;

Session altered.

SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /

PL/SQL procedure successfully completed.

If you are interested in more of the capacities of ADG, check out this great presentation from OpenWorld last year.

Big thanks to DataGuard PM Pieter Van Puymbroeck for his assistance with this post.

4 comments

  1. I think for this we need to use19c database,I am right and then have to enable adg_redirect_dml in adg configuration.

Got some thoughts? Leave a comment

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.