当主库执行的insert操作违反主键约束时,由于这个操作会被记入日志(执行insert,发现违反约束,然后回滚),因此备库在应用日志的时候,也会同样执行这个操作,然后报一下信息:
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)
这只是个提醒信息,是正常情况。
MOS解释如下:
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/me
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.
Sharon
2014.06.13
----------------------------------------------------------------------------------------------
转载须注明出处!
http://blog.csdn.net/sharqueen_wu/article/details/30461867
转载须注明出处!
http://blog.csdn.net/sharqueen_wu/article/details/30461867
转载须注明出处!
http://blog.csdn.net/sharqueen_wu/article/details/30461867