Skip to Main Content
  • Questions
  • Active Dataguard redirection fails with ORA-16397 when calling dbms_flashback.get_system_change_number@primary beforehand

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sokrates.

Asked: July 08, 2025 - 2:42 pm UTC

Last updated: July 30, 2025 - 4:03 am UTC

Version: 19.22.0.0.0

Viewed 1000+ times

You Asked

SQL> select banner_full from v$version where rownum=1;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.22.0.0.0


SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select database_role from v$database@primary;

DATABASE_ROLE
----------------
PRIMARY

SQL> desc temp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S                                                  VARCHAR2(100)

SQL> select dbms_flashback.get_system_change_number@primary from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
              3.4359E+11

SQL> alter session enable adg_redirect_dml;

Session altered.

SQL> desc temp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S                                                  VARCHAR2(100)

SQL> insert into temp values('bla');
insert into temp values('bla')
            *
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed




primary is a database link from the physical standby (opened read-only) to the primary.
The insert succeeds when we omit the call to dbms_flashback.get_system_change_number@primary

Is it a bug or a feature ?
If feature: what exactly is featured here ?

and Connor said...

Thanks for your patience. Querying a database link creates a transaction, and as per the docs

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/starting-up-and-shutting-down.html#GUID-D82D8BE3-5A49-4A86-93E5-80DB7492FD4A

Limitations of a Read-only Database


When executing on a read-only database, you must commit or roll back any in-progress transaction that involves one database link before you use another database link. This is true even if you execute a generic SELECT statement on the first database link and the transaction is currently read-only.


So you need to commit/rollback once you've done your query across the link

More to Explore

DBMS_FLASHBACK

More on PL/SQL routine DBMS_FLASHBACK here