Introduction
An Oracle Logical Standby database can be used for write operations if required, to run reports or test statements. However, if the same transaction is executed on the primary, it will create a conflict and SQL Apply will abort on the standby, resulting in the primary and standby falling out of sync. In this article, we will see how to resolve such an issue by skipping the transaction.
Prerequisites
- Logical standby should be configured and in sync.
Environment Used in this Guide
| Server | Primary | Standby |
|---|---|---|
| Hostname | drdb.oraeasy.com | dcdb.oraeasy.com |
| OS | OEL 9 | OEL 9 |
| SID | ORADBDR | ORADBDC |
| Service Name | ORADBDR | ORADBDC |
Creating and Resolving the Conflict Step by Step
Step 1: Check the Sync Between Primary and Standby
Before intentionally creating a conflict, confirm the standby is fully in sync. This establishes the clean baseline this article deliberately breaks and then repairs.
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
Sequence 44 marked CURRENT on the standby confirms it's fully caught up with the primary before the conflicting transaction is introduced.
Step 2: On the Standby, Create a User
To set up the scenario, create a user directly on the standby first. Since a logical standby is open read/write, it accepts this write locally without issue.
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 Your@Password;
User created.
SQL> select username from dba_users where username like 'AB%';
USERNAME
--------------
ABHI
The ABHI user now existing locally on the standby, but not yet on the primary, is what will collide once the primary's own version of this user tries to replicate in.
Step 3: On the Primary, Create the Same User and Switch Logs
Create the same username on the primary and force log switches so the conflicting CREATE USER statement gets shipped to the standby.
SQL> create user abhi identified by Your@Password;
User created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
46
The archived log sequence advancing to 46 confirms the conflicting transaction has been generated on the primary and is on its way to the standby.
Step 4: On the Standby, Check the Sync
Check the standby's sync status again. Sequences 45 and 46 should now show APPLIED = NO, since SQL Apply can't process the conflicting CREATE USER statement.
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 this output we can see that logs are transferred but not applied. The apply lag climbing to over 5 minutes, with the two most recent sequences unapplied, confirms SQL Apply has stalled on the conflict.
Step 5: Check for the Error and Problematic SQL Statement
DBA_LOGSTDBY_EVENTS records exactly what went wrong. The ORA-01920 error names the conflicting user and the statement that caused it.
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 '*'
The XIDUSN, XIDSLT, and XIDSQN values returned here uniquely identify the stalled transaction, which is exactly what the skip command needs in the next step.
Step 6: On the Standby, Skip the Transaction
SQL Apply must be stopped before skipping a transaction, since DBMS_LOGSTDBY.SKIP_TRANSACTION can't be issued while apply is active. The syntax is EXECUTE DBMS_LOGSTDBY.SKIP_TRANSACTION(XIDUSN_val, XIDSLT_val, XIDSQN_val);, passing the three identifiers found in the previous step.
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.
Step 7: Check the Sync
With the conflicting transaction skipped, confirm the previously stuck sequences are now processing.
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.
Sequences 45 and 46 now showing APPLIED = YES confirm the skip resolved the stall and SQL Apply has resumed processing normally.
Step 8: Roll Back the Skip Transaction Activity (If Needed)
If a transaction was skipped in error, DBMS_LOGSTDBY.UNSKIP_TRANSACTION reverses it. DBA_LOGSTDBY_SKIP_TRANSACTION lists any transactions currently marked as skipped, which is where the identifiers for the rollback come from.
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.
Step 9: Verify the Sync Between Primary and Standby
A final full sync check confirms the logical standby has fully caught up after the skip-and-resume cycle covered in this article.
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
All sequences through 60 now showing APPLIED = YES confirm the standby is fully caught up with the primary after the skip-and-resume cycle.
Step 10: Useful Commands for Managing Logical Standby
For quick reference, here are the commands used throughout this article, all in one place.
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
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment