- Introduction: Oracle Logical Standby Database can be used for write opeartion if required to do reports and test any statement. Although if the same transaction got executed on Primary then it will create conflict and SQL apply got abrupt on Standby, hence it will result in out of sync between Primary & Standby. In this article, we will see how to resolve such issue by skipping the transaction.
- Prerequisites:
- Logical standby should be configured and in sync.
- Environment:
- Now proceed to create the issue scenario and its resolution step by step:
| Server | Primary | Standby |
|---|---|---|
| Hostname | drdb.oraeasy.com | dcdb.oraeasy.com |
| OS | OEL 9 | OEL 9 |
| SID | ORADBDR | ORADBDC |
| Service Name | ORADBDR | ORADBDC |
1. Check the sync between Primary and Standby.
==> Primary:
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
DRDB oradbdr PRIMARY READ WRITE
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
44
==> Standby:
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORADB oradbdc LOGICAL STANDBY READ WRITE
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
27 2497644 2497970 20-DEC-25 YES 38333 512
28 2497970 2497978 20-DEC-25 YES 7 512
29 2497978 2501570 20-DEC-25 YES 16436 512
30 2501570 2503802 20-DEC-25 YES 10108 512
31 2503802 2503810 20-DEC-25 YES 14 512
32 2503810 2506655 20-DEC-25 YES 2482 512
33 2506655 2508558 20-DEC-25 YES 1114 512
34 2508558 2511064 20-DEC-25 YES 12143 512
35 2511064 2512631 21-DEC-25 YES 1190 512
36 2512631 2513019 21-DEC-25 YES 374 512
37 2513019 2517084 21-DEC-25 YES 42537 512
38 2517084 2519228 21-DEC-25 YES 1443 512
39 2519228 2521270 21-DEC-25 YES 1234 512
40 2521270 2523427 21-DEC-25 YES 1428 512
41 2523427 2526569 21-DEC-25 YES 11094 512
42 2526569 2528515 21-DEC-25 YES 1071 512
43 2528515 2530374 21-DEC-25 YES 1003 512
44 2530374 2532229 21-DEC-25 CURRENT 1118 512
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORADB oradbdc LOGICAL STANDBY READ WRITE
SQL> create user abhi identified by Abhi##123;
User created.
SQL> select username from dba_users where username like 'AB%';
USERNAME
--------------
ABHI
SQL> create user abhi identified by Abhi##123;
User created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
46
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
27 2497644 2497970 20-DEC-25 YES 38333 512
28 2497970 2497978 20-DEC-25 YES 7 512
29 2497978 2501570 20-DEC-25 YES 16436 512
30 2501570 2503802 20-DEC-25 YES 10108 512
31 2503802 2503810 20-DEC-25 YES 14 512
32 2503810 2506655 20-DEC-25 YES 2482 512
33 2506655 2508558 20-DEC-25 YES 1114 512
34 2508558 2511064 20-DEC-25 YES 12143 512
35 2511064 2512631 21-DEC-25 YES 1190 512
36 2512631 2513019 21-DEC-25 YES 374 512
37 2513019 2517084 21-DEC-25 YES 42537 512
38 2517084 2519228 21-DEC-25 YES 1443 512
39 2519228 2521270 21-DEC-25 YES 1234 512
40 2521270 2523427 21-DEC-25 YES 1428 512
41 2523427 2526569 21-DEC-25 YES 11094 512
42 2526569 2528515 21-DEC-25 YES 1071 512
43 2528515 2530374 21-DEC-25 YES 1003 512
44 2530374 2532229 21-DEC-25 CURRENT 1118 512
45 2532229 2532712 21-DEC-25 NO 289 512
46 2532712 2532724 21-DEC-25 NO 17 512
20 rows selected.
SQL> SELECT NAME, VALUE, UNIT FROM V$DATAGUARD_STATS;
NAME VALUE UNIT
-------------------------------- ---------------------------------------------------------------- ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval
apply lag +00 00:05:39 day(2) to second(0) interval
apply finish time day(2) to second(3) interval
estimated startup time 31 second
From above output we can see that logs are transferred but not applied.
5. Now check for the error and problematic SQL statement.
==> Check for the apply lag error:
SQL> SELECT event_time,status,EVENT from dba_logstdby_events;
EVENT_TIM STATUS EVENT
--------- ---------------------------------------------------------------------- ----------------------------------------------
21-DEC-25 ORA-16111: log mining and apply setting up
21-DEC-25 Apply LWM 2511526, HWM 2511526, SCN 2512607
21-DEC-25 ORA-16222: automatic Logical Standby retry of last action
21-DEC-25 ORA-16111: log mining and apply setting up
21-DEC-25 Apply LWM 2527314, HWM 2527314, SCN 2530373
21-DEC-25 ORA-01920: user name 'ABHI' conflicts with another user or role name create user abhi identified by '*'
20-DEC-25 ORA-16111: log mining and apply setting up
20-DEC-25 Apply LWM 0, HWM 0, SCN 0
20-DEC-25 ORA-16226: DDL skipped due to lack of support alter database commit to switchover to primary
20-DEC-25 Shutdown acknowledged
20-DEC-25 ORA-16128: User initiated stop apply successfully completed
==> Check the problematic statement:
SQL> SELECT XIDUSN, XIDSLT, XIDSQN, EVENT FROM DBA_LOGSTDBY_EVENTS
WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
XIDUSN XIDSLT XIDSQN EVENT
---------- ---------- ---------- --------------------------------------
10 30 1291 create user abhi identified by '*'
Syntax: EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION(XIDUSN_val, XIDSLT_val, XIDSQN_val);
==> Stop SQL apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
==> Skip the transaction:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION(10, 30, 1291);
PL/SQL procedure successfully completed.
==> Start SQL apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
Database altered.
SQL> SELECT NAME, VALUE, UNIT FROM V$DATAGUARD_STATS;
NAME VALUE UNIT
-------------------------------- ---------------------------------------------------------------- ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval
apply lag +00 00:08:27 day(2) to second(0) interval
apply finish time day(2) to second(3) interval
estimated startup time 31 second
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
27 2497644 2497970 20-DEC-25 YES 38333 512
28 2497970 2497978 20-DEC-25 YES 7 512
29 2497978 2501570 20-DEC-25 YES 16436 512
30 2501570 2503802 20-DEC-25 YES 10108 512
31 2503802 2503810 20-DEC-25 YES 14 512
32 2503810 2506655 20-DEC-25 YES 2482 512
33 2506655 2508558 20-DEC-25 YES 1114 512
34 2508558 2511064 20-DEC-25 YES 12143 512
35 2511064 2512631 21-DEC-25 YES 1190 512
36 2512631 2513019 21-DEC-25 YES 374 512
37 2513019 2517084 21-DEC-25 YES 42537 512
38 2517084 2519228 21-DEC-25 YES 1443 512
39 2519228 2521270 21-DEC-25 YES 1234 512
40 2521270 2523427 21-DEC-25 YES 1428 512
41 2523427 2526569 21-DEC-25 YES 11094 512
42 2526569 2528515 21-DEC-25 YES 1071 512
43 2528515 2530374 21-DEC-25 YES 1003 512
44 2530374 2532229 21-DEC-25 YES 1118 512
45 2532229 2532712 21-DEC-25 YES 289 512
46 2532712 2532724 21-DEC-25 CURRENT 17 512
20 rows selected.
==> Check the skipped transaction:
SQL> select XIDUSN,XIDSLT,XIDSQN from DBA_LOGSTDBY_SKIP_TRANSACTION;
XIDUSN XIDSLT XIDSQN
---------- ---------- ----------
10 30 1291
==> Stop SQL apply:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
==> Rollback the skipped transaction:
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP_TRANSACTION(10, 30, 1291);
PL/SQL procedure successfully completed.
==> Start SQL apply:
SQL> ALTER DATABASE start LOGICAL STANDBY APPLY;
Database altered.
==> Primary:
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
60
==> Standby:
SQL> select sequence#,first_change#,next_change#,timestamp,applied,blocks,block_size from dba_logstdby_log
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED BLOCKS BLOCK_SIZE
---------- ------------- ------------ --------- -------- ---------- ----------
27 2497644 2497970 20-DEC-25 YES 38333 512
28 2497970 2497978 20-DEC-25 YES 7 512
29 2497978 2501570 20-DEC-25 YES 16436 512
30 2501570 2503802 20-DEC-25 YES 10108 512
31 2503802 2503810 20-DEC-25 YES 14 512
32 2503810 2506655 20-DEC-25 YES 2482 512
33 2506655 2508558 20-DEC-25 YES 1114 512
34 2508558 2511064 20-DEC-25 YES 12143 512
35 2511064 2512631 21-DEC-25 YES 1190 512
36 2512631 2513019 21-DEC-25 YES 374 512
37 2513019 2517084 21-DEC-25 YES 42537 512
38 2517084 2519228 21-DEC-25 YES 1443 512
39 2519228 2521270 21-DEC-25 YES 1234 512
40 2521270 2523427 21-DEC-25 YES 1428 512
41 2523427 2526569 21-DEC-25 YES 11094 512
42 2526569 2528515 21-DEC-25 YES 1071 512
43 2528515 2530374 21-DEC-25 YES 1003 512
44 2530374 2532229 21-DEC-25 YES 1118 512
45 2532229 2532712 21-DEC-25 YES 289 512
46 2532712 2532724 21-DEC-25 YES 17 512
47 2532724 2543822 21-DEC-25 YES 79388 512
48 2543822 2545842 21-DEC-25 YES 1219 512
49 2545842 2547753 21-DEC-25 YES 1092 512
50 2547753 2549659 21-DEC-25 YES 1048 512
51 2549659 2552502 21-DEC-25 YES 12624 512
52 2552502 2552509 21-DEC-25 YES 4 512
53 2552509 2552613 21-DEC-25 YES 66 512
54 2552613 2552659 21-DEC-25 YES 34 512
55 2552659 2554650 21-DEC-25 YES 1108 512
56 2554650 2556557 21-DEC-25 YES 1069 512
57 2556557 2558464 21-DEC-25 YES 1046 512
58 2558464 2561556 21-DEC-25 YES 10615 512
59 2561556 2562799 21-DEC-25 YES 697 512
60 2562799 2562807 21-DEC-25 YES 14 512
==> Start SQL apply:
ALTER DATABASE START LOGICAL STANDBY APPLY;
==> Stop SQL apply:
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
==> Monitor apply lag:
SELECT name, value FROM v$dataguard_stats WHERE name LIKE '%lag%';
==> Check the last applied redo sequence:
SELECT thread#, sequence#, applied FROM v$archived_log
WHERE applied = 'YES' ORDER BY sequence# DESC;
==> View SQL apply progress:
SELECT * FROM v$logstdby_progress;
==> Identify SQL apply errors if any:
SELECT event_time,status,EVENT from dba_logstdby_events;
==> Check problematic SQL statement:
SQL> SELECT XIDUSN, XIDSLT, XIDSQN, EVENT FROM DBA_LOGSTDBY_EVENTS
WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
==> Skip transaction:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION(XIDUSN_val, XIDSLT_val, XIDSQN_val);
==> Check the skipped transaction:
SQL> select XIDUSN,XIDSLT,XIDSQN from DBA_LOGSTDBY_SKIP_TRANSACTION;
==> Rollback of skipped transaction:
SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP_TRANSACTION(XIDUSN, XIDSLT, XIDSQN);
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome — feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Comments
Post a Comment