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.
Learning is not a spectator sport 
I think for this we need to use19c database,I am right and then have to enable adg_redirect_dml in adg configuration.
Yes this is 19c. Sorry, I should have noted that.
Hi thanks for shariing this