This is a demostration of CLASSIC CAPTURE on ASM using New ASM API , which reading redo log on ASM Using GoldenGate user provided by USERID in Extract
Source Side : 3 Node RAC / Grid Infrastructure 11.2.0.2
Target Side : Stand-alone 11.2.0.1
DBLOGREADER
(Oracle) Valid for Extract in classic capture mode.Causes Extract to use a newer ASM API that is available as
of Oracle 10.2.0.5 and later 10g R2 versions, and Oracle 11.2.0.2 and later 11g R2 versions (but not in Oracle 11g R1
versions). This API uses the database server to access the redo and archive logs, instead of connecting directly to the
Oracle ASM instance. The database must contain the libraries that contain the API modules and must be
running. To use this feature, the Extract database user must have SELECT ANY TRANSACTION privilege.
When used, DBLOGREADER enables Extract to use a read size of up to 4 MB in size. This is controlled with the
DBLOGREADERBUFSIZE option The maximum read size when using the default OCI buffer is 28672 bytes.
This is controlled by the ASMBUFSIZE option. A larger buffer may improve the performance of Extract
when redo rate is high.When using DBLOGREADER, do not use the ASMUSER and ASMPASSWORD options of TRANLOGOPTIONS.
The API uses the user and password specified with the USERID parameter.
DBLOGREADERBUFSIZE
(Oracle) Valid for Extract in classic capture mode.Controls the maximum size, in bytes, of a read operation
into the internal buffer that holds the results of each read of the transaction log in ASM. Higher values increase
extraction speed but cause Extract to consume more memory. Low values reduce memory usage but increase I/O
because Extract must store data that exceeds the cache size to disk.
Use DBLOGREADERBUFSIZE together with the DBLOGREADER option if the source ASM instance is Oracle 10.2.0.5 or
later10g R2 versions, or Oracle 11.2.0.2 and later 11g R2 versions (but not Oracle 11g R1 versions). The newer ASM
API in those versions provides better performance than the older one. If the Oracle version is not one of those versions,
then ASMBUFSIZE must be used.
--SOURCE SIDE
Add supplement log at database level.
1.
SQL>
ALTER
DATABASE
ADD
SUPPLEMENTAL LOG DATA (
ALL
) COLUMNS;
2.
3.
Database
altered.
--ON SOURCE Add checkpoint Table if it is bi-directional replication,and if you dont have one.
01.
GGSCI (PNETN1.localdomain.com) 19>DBLOGIN, USERID GGADMIN,
PASSWORD
Summer2011
02.
GGSCI (PNETN1.localdomain.com) 19>INFO CHECKPOINTTABLE
03.
04.
--if you dont have one , create it.
05.
06.
GGSCI (PNETN1.localdomain.com) 19>
ADD
CHECKPOINTTABLE GGADMIN.CKPT_TABLE
07.
08.
GGSCI (PNETN1.localdomain.com) 19>EDIT PARAMS ./GLOBALS
09.
GGSCHEMA GGADMIN
10.
CHECKPOINTTABLE GGADMIN.CKPT_TABLE
--Add Extract as Below , REGISTER extract using LOGRETENTION
01.
Use DBLOGIN
to
REGISTER extract
02.
03.
GGSCI (PNETN1.localdomain.com)>DBLOGIN USERID GGADMIN,
PASSWORD
Summer2011
04.
GGSCI (PNETN1.localdomain.com)>REGISTER EXTRACT DW_EX LOGRETENTION
05.
06.
--Add extract
07.
08.
GGSCI (PNETN1.localdomain.com)>
ADD
EXTRACT DW_EX TRANLOG,
BEGIN
NOW,THREADS 3
09.
GGSCI (PNETN1.localdomain.com)>
ADD
EXTTRAIL ./dirdat/EX, EXTRACT DW_EX
10.
11.
--Create parameter file for Extract
12.
GGSCI (PNETN1.localdomain.com) 19>EDIT PARAMS DW_EX
13.
EXTRACT DW_EX
14.
---ORACLE ENVIRONMET
15.
SETENV (ORACLE_HOME =
"/u01/app/oracle/11.2.0/db_1"
)
16.
SETENV (ORACLE_SID =
"EDWP1"
)
17.
SETENV (NLS_LANG =
"AMERICAN_AMERICA.WE8MSWIN1252"
)
18.
USERID GGADMIN,
PASSWORD
Summer2011
19.
20.
--TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD Summer69
21.
--This is ASM API that is available as of
22.
--Oracle 10.2.0.5 and later 10g R2 versions AND
23.
--Oracle 11.2.0.2 and later 11g R2 versions
24.
--BUT NOT in Oracle 11g R1 versions
25.
26.
TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 2597152,ASMBUFSIZE 28000
27.
DYNAMICRESOLUTION
28.
DISCARDFILE ./dirrpt/edwp.dsc,PURGE, MEGABYTES 100
29.
EXTTRAIL ./dirdat/EX
30.
31.
--DDL REPLICATION
32.
DDL INCLUDE MAPPED OBJNAME TEST.*
33.
34.
--DML replication for SCHEMA level.
35.
TABLE
TEST.*;
36.
--end
--Add DATAPUMP
01.
GGSCI (PNETN1.localdomain.com) 19>
ADD
EXTRACT DW_EP, EXTTRAILSOURCE ./dirdat/EX,
begin
now
02.
GGSCI (PNETN1.localdomain.com) 19>
ADD
RMTTRAIL ./dirdat/EP, EXTRACT DW_EP, MEGABYTES 100
03.
GGSCI (PNETN1.localdomain.com) 19>edit params DW_EP
04.
EXTRACT DW_EP
05.
SETENV (ORACLE_HOME =
"/u01/app/oracle/11.2.0/db_1"
)
06.
SETENV (ORACLE_SID =
"EDWP1"
)
07.
SETENV (NLS_LANG =
"AMERICAN_AMERICA.WE8MSWIN1252"
)
08.
USERID GGADMIN,
PASSWORD
Summer2011
09.
PASSTHRU
10.
RMTHOST 192.168.100.101, MGRPORT 7809
11.
RMTTRAIL ./dirdat/EP
12.
TABLE
TEST.*;
13.
--end
--START extract / Pump.
01.
GGSCI (PNETN1.localdomain.com) 17> start DW_EX
02.
03.
Sending START request
to
MANAGER ...
04.
EXTRACT DW_EX starting
05.
06.
07.
GGSCI (PNETN1.localdomain.com) 18> info
all
08.
09.
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
10.
11.
MANAGER RUNNING
12.
EXTRACT STOPPED DW_EP 00:00:00 00:20:02
13.
EXTRACT RUNNING DW_EX 00:33:56 00:00:06
14.
15.
16.
GGSCI (PNETN1.localdomain.com) 19> start DW_EP
17.
18.
Sending START request
to
MANAGER ...
19.
EXTRACT DW_EP starting
20.
21.
22.
GGSCI (PNETN1.localdomain.com) 20> INFO
ALL
23.
24.
Program Status
Group
Lag
at
Chkpt
Time
Since Chkpt
25.
26.
MANAGER RUNNING
27.
EXTRACT RUNNING DW_EP 00:00:00 00:00:08
28.
EXTRACT RUNNING DW_EX 00:00:02 00:00:07
--Output from ggserr.log
01.
[[A2012-07-31 12:34:15 INFO OGG-00987 Oracle GoldenGate Command Interpreter
for
Oracle: GGSCI command (oracle): start DW_EX.
02.
2012-07-31 12:34:15 INFO OGG-00963 Oracle GoldenGate Manager
for
Oracle, mgr.prm: Command received
from
GGSCI
on
host PNETN1.localdomain.com (START EXTRACT DW_EX ).
03.
2012-07-31 12:34:15 INFO OGG-00975 Oracle GoldenGate Manager
for
Oracle, mgr.prm: EXTRACT DW_EX starting.
04.
2012-07-31 12:34:15 INFO OGG-00992 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: EXTRACT DW_EX starting.
05.
2012-07-31 12:34:15 INFO OGG-03035 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Operating system
character
set
identified
as
UTF-8. Locale: en_US, LC_ALL:.
06.
2012-07-31 12:34:15 INFO OGG-01635 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: BOUNDED RECOVERY: reset
to
initial
or
altered
checkpoint
.
07.
2012-07-31 12:34:15 INFO OGG-01815 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Virtual Memory Facilities
for
: BR
08.
anon alloc: mmap(MAP_ANON) anon
free
: munmap
09.
file alloc: mmap(MAP_SHARED) file
free
: munmap
10.
target directories:
11.
/u02/gghome/BR/DW_EX.
12.
2012-07-31 12:34:15 INFO OGG-01815 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Virtual Memory Facilities
for
: COM
13.
anon alloc: mmap(MAP_ANON) anon
free
: munmap
14.
file alloc: mmap(MAP_SHARED) file
free
: munmap
15.
target directories:
16.
/u02/gghome/dirtmp.
17.
2012-07-31 12:34:17 INFO OGG-00546 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm:
Default
thread stack
size
: 33554432.
18.
2012-07-31 12:34:17 INFO OGG-01515 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Positioning
to
begin
time
Jul 31, 2012 12:00:23 PM.
19.
2012-07-31 12:34:18 INFO OGG-01516 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Positioned
to
(Thread 1)
Sequence
4, RBA 5810192, SCN 0.0, Jul 31, 2012 12:00:23 PM.
20.
2012-07-31 12:34:18 INFO OGG-01515 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Positioning
to
begin
time
Jul 31, 2012 12:00:23 PM.
21.
2012-07-31 12:34:18 INFO OGG-01516 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Positioned
to
(Thread 2)
Sequence
2, RBA 5019152, SCN 0.0, Jul 31, 2012 12:00:23 PM.
22.
2012-07-31 12:34:18 INFO OGG-01515 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Positioning
to
begin
time
Jul 31, 2012 12:00:23 PM.
23.
2012-07-31 12:34:19 INFO OGG-01516 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Positioned
to
(Thread 3)
Sequence
2, RBA 4712464, SCN 0.0, Jul 31, 2012 12:00:23 PM.
24.
2012-07-31 12:34:19 INFO OGG-01517 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Position
of
first
record processed
for
Thread 2,
Sequence
2, RBA 5019152, SCN 0.1098626, Jul 31, 2012 12:00:23 PM.
25.
2012-07-31 12:34:19 INFO OGG-01517 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Position
of
first
record processed
for
Thread 3,
Sequence
2, RBA 4712464, SCN 0.1098627, Jul 31, 2012 12:00:23 PM.
26.
2012-07-31 12:34:19 INFO OGG-00993 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: EXTRACT DW_EX started.
27.
2012-07-31 12:34:19 INFO OGG-01052 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm:
No
recovery
is
required
for
target file ./dirdat/EX000000,
at
RBA 0 (file
not
opened).
28.
2012-07-31 12:34:19 INFO OGG-01478 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm:
Output
file ./dirdat/EX
is
using format RELEASE 11.2.
29.
2012-07-31 12:34:19 INFO OGG-01517 Oracle GoldenGate Capture
for
Oracle, dw_ex.prm: Position
of
first
record processed
for
Thread 1,
Sequence
4, RBA 5810192, SCN 0.1098585, Jul 31, 2012 12:00:23 PM.
30.
2012-07-31 12:34:32 INFO OGG-00987 Oracle GoldenGate Command Interpreter
for
Oracle: GGSCI command (oracle): start DW_EP.
31.
2012-07-31 12:34:32 INFO OGG-00963 Oracle GoldenGate Manager
for
Oracle, mgr.prm: Command received
from
GGSCI
on
host PNETN1.localdomain.com (START EXTRACT DW_EP ).
32.
2012-07-31 12:34:32 INFO OGG-00975 Oracle GoldenGate Manager
for
Oracle, mgr.prm: EXTRACT DW_EP starting.
33.
2012-07-31 12:34:33 INFO OGG-00992 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm: EXTRACT DW_EP starting.
34.
2012-07-31 12:34:33 INFO OGG-03035 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm: Operating system
character
set
identified
as
UTF-8. Locale: en_US, LC_ALL:.
35.
2012-07-31 12:34:33 INFO OGG-01815 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm: Virtual Memory Facilities
for
: COM
36.
anon alloc: mmap(MAP_ANON) anon
free
: munmap
37.
file alloc: mmap(MAP_SHARED) file
free
: munmap
38.
target directories:
39.
/u02/gghome/dirtmp.
40.
2012-07-31 12:34:33 WARNING OGG-01015 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm: Positioning
with
begin
time
: Jul 31, 2012 12:02:01 PM, waiting
for
data:
at
extseqno 0, extrba 0.
41.
2012-07-31 12:34:33 INFO OGG-00993 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm: EXTRACT DW_EP started.
42.
2012-07-31 12:34:38 INFO OGG-01226 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm: Socket buffer
size
set
to
27985 (flush
size
27985).
43.
2012-07-31 12:34:38 INFO OGG-01052 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm:
No
recovery
is
required
for
target file ./dirdat/EP000000,
at
RBA 0 (file
not
opened).
44.
2012-07-31 12:34:38 INFO OGG-01478 Oracle GoldenGate Capture
for
Oracle, dw_ep.prm:
Output
file ./dirdat/EP
is
using format RELEASE 11.2.
---Also you will see below output on Target ggserr.log
01.
2012-07-31 09:32:36 INFO OGG-00963 Oracle GoldenGate Manager
for
Oracle, mgr.prm: Command received
from
EXTRACT
on
host 192.168.100.126 (START SERVER CPU -1 PRI -1 TIMEOUT 300 PARAMS ).
02.
2012-07-31 09:32:36 INFO OGG-00974 Oracle GoldenGate Manager
for
Oracle, mgr.prm: Manager started collector process (Port 7840).
03.
2012-07-31 09:32:36 INFO OGG-01677 Oracle GoldenGate Collector: Waiting
for
connection
(started dynamically).
04.
2012-07-31 09:32:36 INFO OGG-01228 Oracle GoldenGate Collector: Timeout
in
300 seconds.
05.
2012-07-31 09:32:41 INFO OGG-01229 Oracle GoldenGate Collector: Connected
to
192.168.100.126:23270.
06.
2012-07-31 09:32:41 WARNING OGG-01223 Oracle GoldenGate Collector: did
not
recognize command (n).
07.
2012-07-31 09:32:41 INFO OGG-01669 Oracle GoldenGate Collector: Opening ./dirdat/EP000000 (byte -1,
current
EOF 0).
08.
2012-07-31 09:32:41 INFO OGG-01670 Oracle GoldenGate Collector: Closing ./dirdat/EP000000.
09.
2012-07-31 09:32:41 INFO OGG-01669 Oracle GoldenGate Collector: Opening ./dirdat/EP000000 (byte -1,
current
EOF 0).
=============================================================
=============================================================
--TARGET SIDE
--Add checkpoint Table into GLOBALS, if you dont have any.
01.
GGSCI (TEST.localdomain.com) 4>
view
PARAMS ./GLOBALS
02.
CHECKPOINTTABLE GGSUSER.CKPT
03.
GGSCHEMA GGSUSER
04.
05.
--Add checkpoint Table into Database.
06.
07.
GGSCI (TEST.localdomain.com) 6>DBLOGIN USERID GGSUSER,
PASSWORD
Summer2011
08.
Successfully logged
into
database
.
09.
10.
--confirm if any checkpoint Table is already exist.
11.
GGSCI (TEST.localdomain.com) 8>INFO CHECKPOINTTABLE
12.
13.
No
checkpoint
table
specified, using GLOBALS specification (GGSUSER.CKPT)...
14.
Checkpoint
table
GGSUSER.CKPT created 2012-05-31 13:32:57.
--Add Replicat on Target SIDE.
01.
GGSCI (TEST.localdomain.com) 8>
ADD
REPLICAT DW_ER, EXTTRAIL ./dirdat/EP,checkpointtable GGSUSER.CKPT
02.
03.
--Create parameter file for Replicat
04.
05.
GGSCI (TEST.localdomain.com) 8>edit params DW_ER
06.
REPLICAT DW_ER
07.
SETENV (ORACLE_HOME =
"/u00/app/oracle/product/11.2.0/db_1"
)
08.
SETENV (ORACLE_SID =
"TEST"
)
09.
--Assume DDL of Source.
10.
ASSUMETARGETDEFS
11.
USERID ggsuser,
PASSWORD
Summer2011
12.
DISCARDFILE ./dirrpt/EDWP.dsc, append, megabytes 100
13.
--DLL replication.
14.
DDL INCLUDE
ALL
15.
--DML replication from TEST schema to TEST schema.
16.
MAP TEST.*, TARGET TEST.*;
17.
--end
--start Replicat
1.
GGSCI (TEST.localdomain.com) 6> start DW_ER
2.
3.
Sending START request
to
MANAGER ...
4.
REPLICAT DW_ER starting
--Some Output from Replicat ggserr.log
1.
2012-07-31 09:33:08 INFO OGG-00987 Oracle GoldenGate Command Interpreter
for
Oracle: GGSCI command (oracle): start DW_ER.
2.
2012-07-31 09:33:08 INFO OGG-00963 Oracle GoldenGate Manager
for
Oracle, mgr.prm: Command received
from
GGSCI
on
host 192.168.100.101 (START REPLICAT DW_ER ).
3.
2012-07-31 09:33:08 INFO OGG-00975 Oracle GoldenGate Manager
for
Oracle, mgr.prm: REPLICAT DW_ER starting.
4.
2012-07-31 09:33:09 INFO OGG-00995 Oracle GoldenGate Delivery
for
Oracle, dw_er.prm: REPLICAT DW_ER starting.
5.
2012-07-31 09:33:09 INFO OGG-00996 Oracle GoldenGate Delivery
for
Oracle, dw_er.prm: REPLICAT DW_ER started.