ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failedWhat is Active DataGuard DML Redirection?

 Error:

Following error occurred during insert/DML operation on the Standby side of Active Dataguard in Oracle.

On Standby Database:

[oracle@orcl19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Nov 16 11:33:18 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.2.0.0.0
SQL> select * from testDMLtable;
NAME      TESTDATE
------    ----------------------------
sunny     16-NOV-19 11.35.19.000000 AM

SQL> INSERT INTO testDMLtable(c1, c3) VALUES ('RAM', sysdate);
INSERT INTO testDML(c1, c3) VALUES ('RAM', sysdate)
            *
ERROR at line 1:
ORA-16397: statement redirection from Oracle Active Data Guard standby database
to primary database failed
SQL> exit

Cause

Found the cause that we need to connect the session with username/password@standby_tns in case of using Active Dataguard DML redirection operations instead of sqlplus / as sysdba.

Solution

We need to connect the STANDBY database with SQLPLUS username/password@standby

• - AT standby database

[oracle@orcl19c ~]$ sqlplus scott/password@stanbyorcl as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Sat Nov 16 11:41:13 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
Version 19.2.0.0.0

SQL> select * from testDMLtable;
NAME      TESTDATE
------    ----------------------------
sunny     16-NOV-19 11.42.14.000000 AM

SQL> INSERT INTO testDMLtable(c1, c3) VALUES ('RAM', sysdate);
1 row inserted.

SQL> Commit;
SQL> exit;