Skip to content

ORA-1 Occurring on Logical Standby

ORA-1 Occurring on Logical Standby

NOTE: In the images and/or the document content below, the user information and environment data used
represents fictitious data from the Oracle sample schema(s),Public Documentation delivered with an
Oracle database product or other training material. Any similarity to actual environments, actual
persons, living or dead, is purely coincidental and not intended in any manner.
For the purposes of this document, the following fictitious environment is used as an example
to describe the procedure:

Schema = ME
Table Name = Test
Problem Description
——————-
During SQL Apply operations on the logical standby you see the following
messages in the alert log:

LOGSTDBY stmt: alter table test add constraint test_pk primary key (col1)
LOGSTDBY event: ORA-00001: unique constraint (.) violated
LOGSTDBY stmt: ME.TEST (Oper=INSERT)

The ORA-1 error also shows up in dba_logstdby_events view:

SQL> select event,status_code,status from dba_logstdby_events
where event_time = (
select max(event_time) from dba_logstdby_events
);

EVENT STATUS_CODE STATUS
———————– ————– ———————————–
ME.TEST (Oper=INSERT) 1 ORA-00001: unique constraint (.) violated

Even though the messages appear, SQL Apply operations continue to run.

NOTE: If SQL Apply DOES fail, this may be an issue with transient
duplicates. Please refer to the following note instead:

Note.266086.1 ‘Unique Constraint Violations Stop SQL Apply’
Solution
———-

The messages are normal and are informational only.

Consider the following test:

On the primary:

SQL> create table test
2 (col1 number,
3 col2 varchar2(10));
Table created.

SQL> alter table test add constraint test_pk primary key (col1);
Table altered.

SQL> desc test
Name Null? Type
———————- ——– ———————-
COL1 NOT NULL NUMBER
COL2 VARCHAR2(10)

SQL> alter system switch logfile;
System altered.

SQL> insert into test values(1,’t1′);
1 row created.

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.
On the logical standby:

SQL> connect me/<password>
Connected.

SQL> select * from test;

COL1 COL2
———- ———-
1 t1
On the primary:

SQL> insert into test values (2,’t2′);
1 row created.

SQL> insert into test values (2,’t3′);
insert into test values (2,’t3′)
*
ERROR at line 1:
ORA-00001: unique constraint (ME.TEST_PK) violated

SQL> commit;
Commit complete.

SQL> alter system switch logfile;
System altered.
In the logical standby alert.log:

LOGSTDBY event: ORA-16204: DDL successfully applied
LOGSTDBY stmt: alter table test add constraint test_pk primary key (col1)
LOGSTDBY event: ORA-00001: unique constraint (.) violated
LOGSTDBY stmt: ME.TEST (Oper=INSERT)

Explanation
————-

When an insert is performed that violates a constraint the insert statement
is placed into the redo stream with a record that states the insert should
be rolled back. As the SQL Apply operations apply the sql from the redo
stream the insert that violated the constraint is attempted and then rolled
back.

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: