Active Data Guard – limitations on ROWTYPE – Learning is not a spectator sport

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.