Thursday, May 10, 2012

Oracle global temporary table not supported by OGG 11.2

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.





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

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) 

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.