Create Global Temporary table on ADG reports below error
SQL> CREATE GLOBAL TEMPORARY TABLE <table name>(c1 number, c2 varchar(10)) ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE tab2(c1 number, c2 varchar(10)) ON COMMIT PRESERVE ROWS
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
Following parameters are Already set on Primary and Standby
temp_undo_enabled boolean TRUE
Compatible >= 12
MRP is running real time with Apply in Open Mode
Also adg_redirect_dml =true
CHANGES
Please refer
Managing Physical and Snapshot Standby Databases
DDL Operations =============>
Global temporary tables can be created on, and dropped from, Active Data Guard standby databases. The DDL for these operations is transparently redirected to the primary database .
The Active Data Guard session then waits until the corresponding changes are shipped and applied to the Active Data Guard standby. The following is an example of creating a global temporary table:
CAUSE
Connection to ADG was made using OS authentication
SQL> Connect /as sysdba
Due to this the DDL (Create statement) get issued on ADG which is read only instead of getting redirected to Primary.
We cannot create or Perform DDL operation on read only database.
From 12c/18c onwards the Creation of GTT(Global temporary table gets redirected to the primary database). Due to this we need to connect with connect string and as sysdba or with Password
DDL Operations
Global temporary tables can be created on, and dropped from, Active Data Guard standby databases. The DDL for these operations is transparently redirected to the primary database . The Active Data Guard session then waits until the corresponding changes are shipped and applied to the Active Data Guard standby. The following is an example of creating a global temporary table:
SOLUTION
Connect sys/<password>@<Connect string of standby> as sysdba？ 一定要sysdba才能吗？
CREATE GLOBAL TEMPORARY TABLE <table name> (c1 number, c2 varchar(10)) ON COMMIT PRESERVE ROWS;
-----------alter session enable adg_redirect_dml; 即可
Active Data Guard DML Redirection
This new feature allows incidental DML to be executed and allow DDL to dynamically create Global Temporary Tables (GTTs) on an Active Data Guard standby database. When DML is executed the update is passed to the Primary database where it is executed and the resulting redo of the transaction will update the Standby database after which control will be returned to the application. The DML is executed preserving all ACID properties of the transaction. When DDL to create a GTT is executed on the standby, the command is captured and passed to the Primary where it is executed. Control is return to the application when the redo for the GTT creation has been applied at the standby database.
DDL Operations: Global temporary tables can be created on, and dropped from, Active Data Guard standby databases. The DDL for these operations is transparently redirected to the primary database. The Active Data Guard session then waits until the corresponding changes are shipped and applied to the Active Data Guard standby.
The following criteria should be met for the DML redirection to succeed:
- The managed standby recovery at the Active Data Guard standby database must be started with the real-time apply option and the Active Data Guard standby database must be in sync with the primary database.
- Connection to the Active Data Guard Standby database from a client machine or using a tns string like '$ sqlplus sys@CDB19SBY as sysdba'
TEST:
Primary: Create table tab1 and insert values
Standby: Check if table is created and populated
Standby: Run DML on the table and see the result.
Primary
SQL> CREATE TABLE tab1(id NUMBER, description VARCHAR2(20));
Table created.
SQL> alter system archive log current;
System altered.
SQL> insert into tab1 values (1, 'a');
1 row created.
SQL> alter system archive log current;
System altered.
SQL> commit;
Commit complete.
SQL>
Standby:
SQL> desc tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCRIPTION VARCHAR2(20)
SQL>
SQL> select * from tab1;
ID DESCRIPTION
---------- --------------------
1 a
SQL> insert into tab1 values (2, 'b');
insert into tab1 values (2, 'b')
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
The above error would be encountered if the parameter adg_redirect_dmlis not enabled at the session level.
The default value of ADG_REDIRECT_PLSQL is FALSE.
SQL> alter session enable adg_redirect_dml;
Session altered.
SQL> insert into tab1 values (2, 'b');
1 row created.
SQL> commit;
Commit complete.
SQL>
Primary:
SQL> select * from tab1;
ID DESCRIPTION
---------- --------------------
1 a
2 b
SQL>
Creation of a Global Temporary Table from ADG
SQL> alter session enable adg_redirect_dml;
SQL> CREATE GLOBAL TEMPORARY TABLE GTT_tab1(id NUMBER, description VARCHAR(20))
2 ON COMMIT DELETE ROWS;
Table created.
SQL>
This feature was also present in the 18c version but the underscore parameter "_enable_proxy_adg_redirect=True" had to be set in the parameters file for this feature to work.
An enhancement bug, Bug 27847368 : DML REDIRECTION NEEDS TO BE A PUBLISHED FEATURE FOR ACTIVE DATA GUARD was logged that developed the session level parameter ADG_REDIRECT_PLSQL that can be used in the 19c release.