Building on my previous blog post on Active Data Guard with DML Redirection, here are a few more tests.
First, on the Primary :
22:03:50 SQL> select name, db_unique_name, database_role from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE --------- ------------------------------ ---------------- ORCLCDB ORCLCDB PRIMARY Elapsed: 00:00:00.02 22:04:08 SQL> alter pluggable database orclpdb1 open ; alter pluggable database orclpdb1 open * ERROR at line 1: ORA-65019: pluggable database ORCLPDB1 already open Elapsed: 00:00:00.50 22:04:19 SQL> 22:05:02 SQL> alter session set container=ORCLPDB1; Session altered. Elapsed: 00:00:00.04 22:05:10 SQL> create table hemant.test_adg_dml as select * from dba_objects where 1=2; Table created. Elapsed: 00:00:01.16 22:05:31 SQL> insert into hemant.test_adg_dml select * from dba_objects; 73661 rows created. Elapsed: 00:00:09.29 22:06:03 SQL> commit; Commit complete. Elapsed: 00:00:00.00 22:06:12 SQL>
Now, one the first Standby as Active Data Guard and with DML Redirection
22:02:08 SQL> select name, db_unique_name, database_role from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB STDBYDB PHYSICAL STANDBY
Elapsed: 00:00:00.00
22:07:24 SQL> alter database recover managed standby database cancel;
Database altered.
Elapsed: 00:00:01.01
22:07:34 SQL> alter database open read only;
Database altered.
Elapsed: 00:00:34.66
22:08:23 SQL> alter pluggable database orclpdb1 open read only;
Pluggable database altered.
Elapsed: 00:00:10.76
22:08:39 SQL> alter database recover managed standby database disconnect from session;
Database altered.
Elapsed: 00:00:07.04
22:08:48 SQL> connect hemant/hemant@stdbypdb1
Connected.
22:09:12 SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
Session altered.
Elapsed: 00:00:00.00
22:09:28 SQL> select count(*) from hemant.test_adg_dml;
COUNT(*)
----------
73661
Elapsed: 00:00:01.60
22:09:48 SQL> insert into hemant.test_adg_dml select * from dba_objects;
73661 rows created.
Elapsed: 00:00:10.71
22:10:13 SQL> select count(*) from hemant.test_adg_dml;
COUNT(*)
----------
147322
Elapsed: 00:00:00.01
22:10:24 SQL> commit;
Commit complete.
Elapsed: 00:00:00.09
22:10:27 SQL>
Now, on another Standby with ADG and DML Redirection :
22:11:52 SQL> select name, db_unique_name, database_role from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB STDBNEW PHYSICAL STANDBY
Elapsed: 00:00:00.03
22:11:53 SQL> alter database recover managed standby database cancel;
Database altered.
Elapsed: 00:00:06.01
22:12:06 SQL> alter database open read only;
Database altered.
Elapsed: 00:00:32.27
22:12:42 SQL> alter pluggable database orclpdb1 open read only;
Pluggable database altered.
Elapsed: 00:00:14.89
22:12:59 SQL> alter database recover managed standby database disconnect from session;
Database altered.
Elapsed: 00:00:06.06
22:13:08 SQL>
22:20:58 SQL> connect hemant/hemant@stdbnewpdb1
Connected.
22:21:04 SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;
Session altered.
Elapsed: 00:00:00.01
22:21:18 SQL> select count(*) from hemant.test_adg_dml;
COUNT(*)
----------
147322
Elapsed: 00:00:00.02
22:21:27 SQL> insert into hemant.test_adg_dml select * from dba_objects;
73661 rows created.
Elapsed: 00:00:08.60
22:21:50 SQL> select count(*) from hemant.test_adg_dml;
COUNT(*)
----------
220983
Elapsed: 00:00:00.01
22:22:06 SQL> commit;
Commit complete.
Elapsed: 00:00:00.21
22:22:08 SQL>
22:22:43 SQL> select name, db_unique_name, database_role, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB STDBNEW PHYSICAL STANDBY READ ONLY WITH APPLY
Elapsed: 00:00:00.01
22:22:44 SQL>
So, this proves that we can run ADG and DML Redirection concurrently from multiple Standbys.
Let me test Locking.
I run a DML from STDBYDB and then try a similar DML from the Primary.
22:24:35 SQL> select name, open_mode from v$pdbs; NAME -------------------------------------------------------------------------------------------------------------------------------- OPEN_MODE ---------- ORCLPDB1 READ ONLY Elapsed: 00:00:00.00 22:24:40 SQL> show user USER is "HEMANT" 22:24:45 SQL> delete hemant.test_adg_dml; 220983 rows deleted. Elapsed: 00:00:17.20 22:25:16 SQL> --- no COMMIT has been issued yet !
-- now this is on the Primary ! 22:26:18 SQL> select name, db_unique_name, database_role, open_mode from v$database 22:26:25 2 / NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE --------- ------------------------------ ---------------- -------------------- ORCLCDB ORCLCDB PRIMARY READ WRITE Elapsed: 00:00:00.39 22:26:27 SQL> select name, open_mode from v$pdbs; NAME -------------------------------------------------------------------------------------------------------------------------------- OPEN_MODE ---------- ORCLPDB1 READ WRITE Elapsed: 00:00:00.12 22:26:33 SQL> delete hemant.test_adg_dml;
This goes into an indefinite WAIT on the Primary
--- from another session, I use utllockt to identify the Blocker and Waiter at the Primary
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135 None
391 Transaction Exclusive Exclusive 65541 5609
22:29:26 SQL> select s.sid, s.sql_id, sq.sql_text
22:30:47 2 from v$session s, v$sql sq
22:30:53 3 where s.sid in (135,391)
22:31:08 4 and s.sql_id=sq.sql_id
22:31:12 5 /
SID SQL_ID
---------- -------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
135 9utz9bdavk3bz
DELETE FROM "HEMANT"."TEST_ADG_DML" "A1"
391 4kmzmr764b4k6
delete hemant.test_adg_dml
22:31:13 SQL>
--- SID=135 is the remote session from the STDBYDB instance
To reconfirm, this, I deliberately, change the SQL Text and try again
--- this from STDBYDB 22:33:19 SQL> rollback; Rollback complete. Elapsed: 00:00:52.76 22:34:27 SQL> 22:34:51 SQL> select name, db_unique_name, database_role, open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE --------- ------------------------------ ---------------- -------------------- ORCLCDB STDBYDB PHYSICAL STANDBY READ ONLY WITH APPLY Elapsed: 00:00:00.02 22:34:53 SQL> select name, open_mode from v$pdbs; NAME -------------------------------------------------------------------------------------------------------------------------------- OPEN_MODE ---------- ORCLPDB1 READ ONLY Elapsed: 00:00:00.00 22:35:03 SQL> 22:35:50 SQL> delete /*+ Hint here from STDYBDB */ from hemant.test_adg_dml; 220983 rows deleted. Elapsed: 00:00:06.78 22:36:27 SQL> --- this from ORCLCDB 22:34:37 SQL> rollback; Rollback complete. Elapsed: 00:00:08.44 22:34:53 SQL> 22:36:41 SQL> select name, db_unique_name, database_role, open_mode from v$database; NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE --------- ------------------------------ ---------------- -------------------- ORCLCDB ORCLCDB PRIMARY READ WRITE Elapsed: 00:00:00.24 22:37:21 SQL> select name, open_mode from v$pdbs; NAME -------------------------------------------------------------------------------------------------------------------------------- OPEN_MODE ---------- ORCLPDB1 READ WRITE Elapsed: 00:00:00.00 22:37:30 SQL> DELETE /*+ I am the Primary */ hemant.test_adg_dml target_table;
Again goes into an Indefnite WAIT at the Primary
-- now check using utllockt at the Primary
WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135 None
391 Transaction Exclusive Exclusive 393247 7239
SQL> select s.sid, sq.sql_text
2 from v$session s, v$sql sq
3 where s.sid in (135, 391)
4 and s.sql_id = sq.sql_id
5 /
SID
----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
135
DELETE FROM "HEMANT"."TEST_ADG_DML" "A1"
391
DELETE /*+ I am the Primary */ hemant.test_adg_dml target_table
SQL>
---- apparently, when the Standby sends the SQL to the Primary it is rewritten as UPPER CASE
---- and the Invalid Hint that is a Comment is removed
---- nevertheless, this proves that SID=135 from the Standby is the Blocker and SID=391 on the Primary is the Waiter
So, this proves that DML Redirection from an ADG Standby does take locks and prevents concurrent DML against the same rows on the Primary (and, by extension, on any other ADG Standby)
Note : According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database." . I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.
Update : If the Primary goes down / shuts down, an ADG with DML Redirection will show
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from ADGREDIRECT
at the next call
No comments:
Post a Comment