SQL> create table global temporary table bbadmin.gt (id number);
SQL> drop table bbadmin.gt;
Only first statement captured, and drop statement did not captured by OGG.
document [Does GoldenGate Support Replicating Temporary Tables? (Doc ID 1451424.1)] stating the replication of global temporary tables is not supported.
Thursday, May 10, 2012
Sunday, May 6, 2012
11G externalized alert log table
Alert log can be monitored through SQL using "sys.x$dbgalertext".
SQL> desc sys.x$dbgalertext
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(64)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(64)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
SUPPLEMENTAL_DETAILS VARCHAR2(128)
PARTITION NUMBER
RECORD_ID NUMBER
SQL> col MESSAGE_TEXT for a120
SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24
2 /
no rows selected
SQL> create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M;
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file '/root/not_able_to_create.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24;
ADDR INDX INST_ID
---------------- ---------- ----------
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
NORMALIZED_TIMESTAMP
---------------------------------------------------------------------------
ORGANIZATION_ID
----------------------------------------------------------------
COMPONENT_ID
----------------------------------------------------------------
HOST_ID
----------------------------------------------------------------
HOST_ADDRESS MESSAGE_TYPE MESSAGE_LEVEL
---------------------------------------------- ------------ -------------
MESSAGE_ID
----------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
CLIENT_ID
----------------------------------------------------------------
MODULE_ID
----------------------------------------------------------------
PROCESS_ID
--------------------------------
THREAD_ID
----------------------------------------------------------------
USER_ID
----------------------------------------------------------------
INSTANCE_ID
----------------------------------------------------------------
DETAILED_LOCATION
--------------------------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
UPSTREAM_COMP_ID
--------------------------------------------------------------------------------
DOWNSTREAM_COMP_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_SEQUENCE ERROR_INSTANCE_ID ERROR_INSTANCE_SEQUENCE VERSION
-------------------------- ----------------- ----------------------- ----------
MESSAGE_TEXT
--------------------------------------------------------------------------------
MESSAGE_ARGUMENTS
--------------------------------------------------------------------------------
SUPPLEMENTAL_ATTRIBUTES
--------------------------------------------------------------------------------
SUPPLEMENTAL_DETAILS
--------------------------------------------------------------------------------
PARTITION RECORD_ID
---------- ----------
00007FAA553E5840 3110 1
06-MAY-12 11.42.14.357 PM +08:00
oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141 5 16
opiexe:3025:4222364190
admin_ddl
sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422
0 0 0 0
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M
1 3111
00007FAA553E5840 3111 1
06-MAY-12 11.42.14.384 PM +08:00
oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141 5 16
opiexe:3087:2780954927
admin_ddl
sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422
0 0 0 0
ORA-1119 signalled during: create tablespace tbl_failed datafile '/root/not_able
_to_create.dbf' size 10M...
1 3112
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID VARCHAR2(64)
COMPONENT_ID VARCHAR2(64)
HOST_ID VARCHAR2(64)
HOST_ADDRESS VARCHAR2(46)
MESSAGE_TYPE NUMBER
MESSAGE_LEVEL NUMBER
MESSAGE_ID VARCHAR2(64)
MESSAGE_GROUP VARCHAR2(64)
CLIENT_ID VARCHAR2(64)
MODULE_ID VARCHAR2(64)
PROCESS_ID VARCHAR2(32)
THREAD_ID VARCHAR2(64)
USER_ID VARCHAR2(64)
INSTANCE_ID VARCHAR2(64)
DETAILED_LOCATION VARCHAR2(160)
PROBLEM_KEY VARCHAR2(64)
UPSTREAM_COMP_ID VARCHAR2(100)
DOWNSTREAM_COMP_ID VARCHAR2(100)
EXECUTION_CONTEXT_ID VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE NUMBER
ERROR_INSTANCE_ID NUMBER
ERROR_INSTANCE_SEQUENCE NUMBER
VERSION NUMBER
MESSAGE_TEXT VARCHAR2(2048)
MESSAGE_ARGUMENTS VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
SUPPLEMENTAL_DETAILS VARCHAR2(128)
PARTITION NUMBER
RECORD_ID NUMBER
SQL> col MESSAGE_TEXT for a120
SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24
2 /
no rows selected
SQL> create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M;
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M
*
ERROR at line 1:
ORA-01119: error in creating database file '/root/not_able_to_create.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 13: Permission denied
Additional information: 1
SQL> select * from sys.x$dbgalertext where ORIGINATING_TIMESTAMP > sysdate -1/24;
ADDR INDX INST_ID
---------------- ---------- ----------
ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
NORMALIZED_TIMESTAMP
---------------------------------------------------------------------------
ORGANIZATION_ID
----------------------------------------------------------------
COMPONENT_ID
----------------------------------------------------------------
HOST_ID
----------------------------------------------------------------
HOST_ADDRESS MESSAGE_TYPE MESSAGE_LEVEL
---------------------------------------------- ------------ -------------
MESSAGE_ID
----------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
CLIENT_ID
----------------------------------------------------------------
MODULE_ID
----------------------------------------------------------------
PROCESS_ID
--------------------------------
THREAD_ID
----------------------------------------------------------------
USER_ID
----------------------------------------------------------------
INSTANCE_ID
----------------------------------------------------------------
DETAILED_LOCATION
--------------------------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
UPSTREAM_COMP_ID
--------------------------------------------------------------------------------
DOWNSTREAM_COMP_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_ID
--------------------------------------------------------------------------------
EXECUTION_CONTEXT_SEQUENCE ERROR_INSTANCE_ID ERROR_INSTANCE_SEQUENCE VERSION
-------------------------- ----------------- ----------------------- ----------
MESSAGE_TEXT
--------------------------------------------------------------------------------
MESSAGE_ARGUMENTS
--------------------------------------------------------------------------------
SUPPLEMENTAL_ATTRIBUTES
--------------------------------------------------------------------------------
SUPPLEMENTAL_DETAILS
--------------------------------------------------------------------------------
PARTITION RECORD_ID
---------- ----------
00007FAA553E5840 3110 1
06-MAY-12 11.42.14.357 PM +08:00
oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141 5 16
opiexe:3025:4222364190
admin_ddl
sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422
0 0 0 0
create tablespace tbl_failed datafile '/root/not_able_to_create.dbf' size 10M
1 3111
00007FAA553E5840 3111 1
06-MAY-12 11.42.14.384 PM +08:00
oracle
rdbms
ncsdb02.ncs.edu.sg
192.168.251.141 5 16
opiexe:3087:2780954927
admin_ddl
sqlplus@ncsdb02.ncs.edu.sg (TNS V1-V3)
17422
0 0 0 0
ORA-1119 signalled during: create tablespace tbl_failed datafile '/root/not_able
_to_create.dbf' size 10M...
1 3112
Manual fix OGG Replicat Broken Example
-- insert into target first
SQL> insert into bbadmin.tbl_p values (1,'p');
SQL> commit;
-- insert into source later
SQL> insert into bbadmin.tbl_p values (1,'p');
SQL> commit;
SQL> insert into bbadmin.tbl_p values (2,'p');
SQL> commit;
File: $GGS_HOME/ggserr.log
2012-05-06 22:56:13 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: REPLICAT RBBP01 started.
2012-05-06 22:56:18 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RBBP01 starting.
2012-05-06 23:14:44 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL
2012-05-06 23:14:44 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Aborted grouped transaction on 'BBADMIN.TBL_P', Database error 1 (OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL
2012-05-06 23:14:44 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Repositioning to rba 11860 in seqno 13.
2012-05-06 23:14:44 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: SQL error 1 mapping BBADMIN.TBL_P to BBADMIN.TBL_P OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL
2012-05-06 23:14:44 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Repositioning to rba 11860 in seqno 13.
2012-05-06 23:14:44 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Error mapping from BBADMIN.TBL_P to BBADMIN.TBL_P.
2012-05-06 23:14:44 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: PROCESS ABENDING.
2012-05-06 23:14:44 INFO OGG-01237 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.
2012-05-06 23:18:57 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: REPLICAT RBBP01 starting.
2012-05-06 23:18:57 INFO OGG-03035 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Operating system character set identified as UTF-8. Locale: en_SG, LC_ALL:.
2012-05-06 23:18:57 INFO OGG-01236 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Trace file /u01/ggs/dirrpt/trace_RBBP01.trc opened.
2012-05-06 23:18:57 INFO OGG-01815 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Virtual Memory Facilities for: COM
File: $GGS_HOME/dirrpt/RBBP01.dsc
Current time: 2012-05-06 23:14:44
Discarded record from action ABEND on error 1
OCI Error ORA-00001: unique constraint (BBADMIN.TBL_P_PK) violated (status = 1), SQL
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11860
error at seqno 13 rba 11860
Problem replicating BBADMIN.TBL_P to BBADMIN.TBL_P
Mapping problem with insert record (target format)...
*
PID = 1
NAME = p
*
Process Abending : 2012-05-06 23:14:44
Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 23:18:57
[oracle@ncsdb02 ggs]$ logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Logdump 13 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 14 >pos 11860
Reading forward from RBA 11860
Logdump 15 >n
2012/05/06 23:14:43.994.911 Insert Len 18 RBA 11860
Name: BBADMIN.TBL_P
After Image: Partition 4 G s
0000 0005 0000 0001 3100 0100 0500 0000 0170 | ........1........p
Logdump 16 >n
2012/05/06 23:23:50.917.770 Insert Len 18 RBA 11995
Name: BBADMIN.TBL_P
After Image: Partition 4 G s
0000 0005 0000 0001 3200 0100 0500 0000 0170 | ........2........p
GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*
REPLICAT RBBP01 Last Started 2012-05-06 23:23 Status ABENDED
Checkpoint Lag 00:09:14 (updated 00:03:34 ago)
Log Read Checkpoint File /u01/ggs/dirdat/ta000013
2012-05-06 23:14:43.994911 RBA 11860
GGSCI (ncsdb02.ncs.edu.sg) 6> alter replicat RBBP01 extseqno 13, extrba 11995
REPLICAT altered.
GGSCI (ncsdb02.ncs.edu.sg) 8> start replicat RBBP01
Sending START request to MANAGER ...
REPLICAT RBBP01 starting
Simulate effects without "DEFERREFCONST"
To simulate the issue, make sure following DBOPTIONS not enabled on Replicat.
--DBOPTIONS DEFERREFCONST
DDL statement on Source Database (with DDL replication is on)
alter session set current_schema=bbadmin;
drop table tbl_p purge;
drop table tbl_c purge;
create table tbl_p (pid number,name varchar2(20));
alter table tbl_p add constraint tbl_p_pk primary key (pid);
create table tbl_c (cid number,pid number,name varchar2(20));
alter table tbl_c add constraint tbl_c_pk primary key (cid);
alter table tbl_c add constraint tbl_c_tbl_p_fk foreign key (pid) references tbl_p on delete cascade not deferrable;
create index tbl_c_n1 on tbl_c(pid);
insert into tbl_p values (1,'a');
insert into tbl_p values (2,'b');
insert into tbl_c values (100,1,'c');
insert into tbl_c values (200,2,'c');
commit;
delete from tbl_p where pid=2;
[oracle@ncsdb02 ggs]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*, detail
REPLICAT RBBP01 Last Started 2012-05-06 22:38 Status ABENDED
Checkpoint Lag 00:10:35 (updated 00:02:52 ago)
Log Read Checkpoint File /u01/ggs/dirdat/ta000013
2012-05-06 22:28:18.921409 RBA 11633
Extract Source Begin End
/u01/ggs/dirdat/ta000013 2012-05-06 22:27 2012-05-06 22:28
/u01/ggs/dirdat/ta000013 2012-05-06 22:27 2012-05-06 22:28
/u01/ggs/dirdat/ta000013 2012-05-06 22:27 2012-05-06 22:28
/u01/ggs/dirdat/ta000013 2012-05-02 23:32 2012-05-06 22:27
/u01/ggs/dirdat/ta000012 2012-05-02 23:20 2012-05-02 23:32
/u01/ggs/dirdat/ta000011 2012-05-02 22:42 2012-05-02 23:20
/u01/ggs/dirdat/ta000007 2012-05-01 21:45 2012-05-02 22:42
/u01/ggs/dirdat/ta000006 2012-05-01 07:51 2012-05-01 21:45
/u01/ggs/dirdat/ta000005 2012-05-01 07:46 2012-05-01 07:51
/u01/ggs/dirdat/ta000005 2012-05-01 07:14 2012-05-01 07:46
/u01/ggs/dirdat/ta000004 2012-04-30 22:39 2012-05-01 07:14
/u01/ggs/dirdat/ta000003 2012-04-29 12:06 2012-04-30 22:39
/u01/ggs/dirdat/ta000002 2012-04-28 18:17 2012-04-29 12:06
/u01/ggs/dirdat/ta000000 * Initialized * 2012-04-28 18:17
/u01/ggs/dirdat/ta000000 * Initialized * First Record
Current directory /u01/ggs
Report file /u01/ggs/dirrpt/RBBP01.rpt
Parameter file /u01/ggs/dirprm/rbbp01.prm
Checkpoint file /u01/ggs/dirchk/RBBP01.cpr
Checkpoint table ggs_admin.ggschkpt
Process file /u01/ggs/dirpcs/RBBP01.pcr
Stdout file /u01/ggs/dirout/RBBP01.out
Error log /u01/ggs/ggserr.log
File: $GGS_HOME/ggserr.log
2012-05-06 22:28:00 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: REPLICAT RBBP01 started.
2012-05-06 22:28:05 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RBBP01 starting.
2012-05-06 22:28:19 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Aborted grouped transaction on 'BBADMIN.TBL_C', Database error 1403 (No data found).
2012-05-06 22:28:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: SQL error 1403 mapping BBADMIN.TBL_C to BBADMIN.TBL_C No data found.
2012-05-06 22:28:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Error mapping from BBADMIN.TBL_C to BBADMIN.TBL_C.
2012-05-06 22:28:19 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: PROCESS ABENDING.
2012-05-06 22:28:19 INFO OGG-01237 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.
File: $GGS_HOME/dirrpt/RBBP01.dsc
Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:28:00
Current time: 2012-05-06 22:28:19
Discarded record from action ABEND on error 1403
No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*
Continuing to discard records up to the last discarded record from action ABEND
Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:28:19
Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:33:54
Current time: 2012-05-06 22:33:54
Discarded record from action ABEND on error 1403
No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*
Continuing to discard records up to the last discarded record from action ABEND
Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:33:54
[oracle@ncsdb02 ggs]$ ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Logdump 1 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 2 >pos 11633
Reading forward from RBA 11633
Logdump 3 >fileheader detail
Logdump 4 >ghdr on
Logdump 5 >detail on
Logdump 6 >usertoken detail
Logdump 7 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 9 (x0009) IO Time : 2012/05/06 22:28:18.921.409
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 58 AuditPos : 33719824
Continued : N (x00) RecCount : 1 (x01)
2012/05/06 22:28:18.921.409 Delete Len 9 RBA 11633
Name: BBADMIN.TBL_P
Before Image: Partition 4 G b
0000 0005 0000 0001 32 | ........2
Column 0 (x0000), Len 5 (x0005)
Logdump 8 >reclen 1000
Reclen set to 1000
Logdump 9 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 11 (x000b) IO Time : 2012/05/06 22:28:18.921.409
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 58 AuditPos : 33720668
Continued : N (x00) RecCount : 1 (x01)
2012/05/06 22:28:18.921.409 Delete Len 11 RBA 11757
Name: BBADMIN.TBL_C
Before Image: Partition 4 G e
0000 0007 0000 0003 3230 30 | ........200
Column 0 (x0000), Len 7 (x0007)
--DBOPTIONS DEFERREFCONST
DDL statement on Source Database (with DDL replication is on)
alter session set current_schema=bbadmin;
drop table tbl_p purge;
drop table tbl_c purge;
create table tbl_p (pid number,name varchar2(20));
alter table tbl_p add constraint tbl_p_pk primary key (pid);
create table tbl_c (cid number,pid number,name varchar2(20));
alter table tbl_c add constraint tbl_c_pk primary key (cid);
alter table tbl_c add constraint tbl_c_tbl_p_fk foreign key (pid) references tbl_p on delete cascade not deferrable;
create index tbl_c_n1 on tbl_c(pid);
insert into tbl_p values (1,'a');
insert into tbl_p values (2,'b');
insert into tbl_c values (100,1,'c');
insert into tbl_c values (200,2,'c');
commit;
delete from tbl_p where pid=2;
[oracle@ncsdb02 ggs]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb 1 2012 00:55:59
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (ncsdb02.ncs.edu.sg) 1> info replicat r*, detail
REPLICAT RBBP01 Last Started 2012-05-06 22:38 Status ABENDED
Checkpoint Lag 00:10:35 (updated 00:02:52 ago)
Log Read Checkpoint File /u01/ggs/dirdat/ta000013
2012-05-06 22:28:18.921409 RBA 11633
Extract Source Begin End
/u01/ggs/dirdat/ta000013 2012-05-06 22:27 2012-05-06 22:28
/u01/ggs/dirdat/ta000013 2012-05-06 22:27 2012-05-06 22:28
/u01/ggs/dirdat/ta000013 2012-05-06 22:27 2012-05-06 22:28
/u01/ggs/dirdat/ta000013 2012-05-02 23:32 2012-05-06 22:27
/u01/ggs/dirdat/ta000012 2012-05-02 23:20 2012-05-02 23:32
/u01/ggs/dirdat/ta000011 2012-05-02 22:42 2012-05-02 23:20
/u01/ggs/dirdat/ta000007 2012-05-01 21:45 2012-05-02 22:42
/u01/ggs/dirdat/ta000006 2012-05-01 07:51 2012-05-01 21:45
/u01/ggs/dirdat/ta000005 2012-05-01 07:46 2012-05-01 07:51
/u01/ggs/dirdat/ta000005 2012-05-01 07:14 2012-05-01 07:46
/u01/ggs/dirdat/ta000004 2012-04-30 22:39 2012-05-01 07:14
/u01/ggs/dirdat/ta000003 2012-04-29 12:06 2012-04-30 22:39
/u01/ggs/dirdat/ta000002 2012-04-28 18:17 2012-04-29 12:06
/u01/ggs/dirdat/ta000000 * Initialized * 2012-04-28 18:17
/u01/ggs/dirdat/ta000000 * Initialized * First Record
Current directory /u01/ggs
Report file /u01/ggs/dirrpt/RBBP01.rpt
Parameter file /u01/ggs/dirprm/rbbp01.prm
Checkpoint file /u01/ggs/dirchk/RBBP01.cpr
Checkpoint table ggs_admin.ggschkpt
Process file /u01/ggs/dirpcs/RBBP01.pcr
Stdout file /u01/ggs/dirout/RBBP01.out
Error log /u01/ggs/ggserr.log
File: $GGS_HOME/ggserr.log
2012-05-06 22:28:00 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: REPLICAT RBBP01 started.
2012-05-06 22:28:05 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RBBP01 starting.
2012-05-06 22:28:19 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Aborted grouped transaction on 'BBADMIN.TBL_C', Database error 1403 (No data found).
2012-05-06 22:28:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: SQL error 1403 mapping BBADMIN.TBL_C to BBADMIN.TBL_C No data found.
2012-05-06 22:28:19 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Repositioning to rba 11633 in seqno 13.
2012-05-06 22:28:19 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Error mapping from BBADMIN.TBL_C to BBADMIN.TBL_C.
2012-05-06 22:28:19 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: PROCESS ABENDING.
2012-05-06 22:28:19 INFO OGG-01237 Oracle GoldenGate Delivery for Oracle, rbbp01.prm: Trace file /u01/ggs/dirrpt/trace_RBBP01.trc closed.
File: $GGS_HOME/dirrpt/RBBP01.dsc
Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:28:00
Current time: 2012-05-06 22:28:19
Discarded record from action ABEND on error 1403
No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*
Continuing to discard records up to the last discarded record from action ABEND
Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:28:19
Oracle GoldenGate Delivery for Oracle process started, group RBBP01 discard file opened: 2012-05-06 22:33:54
Current time: 2012-05-06 22:33:54
Discarded record from action ABEND on error 1403
No data found
Aborting transaction on /u01/ggs/dirdat/ta beginning at seqno 13 rba 11633
error at seqno 13 rba 11757
Problem replicating BBADMIN.TBL_C to BBADMIN.TBL_C
Record not found
Mapping problem with delete record (target format)...
*
CID = 200
*
Continuing to discard records up to the last discarded record from action ABEND
Operation discarded from seqno 13 rba 11633
Aborted delete from BBADMIN.TBL_P to BBADMIN.TBL_P (target format)...
*
PID = 2
*
Process Abending : 2012-05-06 22:33:54
[oracle@ncsdb02 ggs]$ ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Logdump 1 >open /u01/ggs/dirdat/ta000013
Current LogTrail is /u01/ggs/dirdat/ta000013
Logdump 2 >pos 11633
Reading forward from RBA 11633
Logdump 3 >fileheader detail
Logdump 4 >ghdr on
Logdump 5 >detail on
Logdump 6 >usertoken detail
Logdump 7 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 9 (x0009) IO Time : 2012/05/06 22:28:18.921.409
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 58 AuditPos : 33719824
Continued : N (x00) RecCount : 1 (x01)
2012/05/06 22:28:18.921.409 Delete Len 9 RBA 11633
Name: BBADMIN.TBL_P
Before Image: Partition 4 G b
0000 0005 0000 0001 32 | ........2
Column 0 (x0000), Len 5 (x0005)
Logdump 8 >reclen 1000
Reclen set to 1000
Logdump 9 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 11 (x000b) IO Time : 2012/05/06 22:28:18.921.409
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 58 AuditPos : 33720668
Continued : N (x00) RecCount : 1 (x01)
2012/05/06 22:28:18.921.409 Delete Len 11 RBA 11757
Name: BBADMIN.TBL_C
Before Image: Partition 4 G e
0000 0007 0000 0003 3230 30 | ........200
Column 0 (x0000), Len 7 (x0007)
Wednesday, May 2, 2012
How to achieve sub-second latency when your application workload is low
Add following parameter to your Extract process (Including the one for PUMP):
FLUSHCSECS 10
EOFDELAYCSECS 10
And add following parameter to your Replicat process.
EOFDELAYCSECS 10
Remark: Verified in OGG 11.2.
Subscribe to:
Posts (Atom)