Oracle

Oracle  Oracle
Transactional Data Management
Replication Demo
Stand: 20.09.2011
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
2
Inhalt
1. Oracle GoldenGate Installation
1.1. Installationschritte
1.2. Source und Target Überblick
2. GoldenGate Demo Files
2.1. Kopieren der Demo Parameterdateien
2.2. Modifikation der Parameterdateien
3.
GoldenGate Demo
3.1. Setup der Oracle Datenbank
3.1.1. Voraussetzungen
3.1.2. User und Datenbankobjekte
3.2. Setup von GoldenGate
3.3. Demo Schritte
3.3.1. Füllen der Source Tabellen
3.3.2. Anzeigen der Tabelleninhalte
3.3.3. Inserts und Updates
3.3.4. GoldenGate Replicat Reports
3.4. Demo Cleanup
3.4.1. Source Seite
3.4.2. Target Seite
3.4.3. Source Tabelleninhalte löschen
3.4.4. Target Tabelleninhalte löschen
3.5. Demo Wiederholung
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
3
1. Oracle GoldenGate Installation
1.1. Installationsschritte
Die Installation wird wie im Handbuch beschrieben durchgeführt. Es werden zwei unabhängige GG
Installationsfolder erstellt. Für die Source Seite „ogg_source“ und für die Target Seite „ogg_target“.
GoldenGate kann Version 10.4 oder 11.1 haben.
- Setzen der Environment Variablen ORACLE_HOME und ORACLE_SID
Da die Demo nur mit einer Oracle Datenbank durchgeführt wird, werden die Variablen in der Windows
Umgebung des Nutzers gesetzt.
- Nur GoldenGate Version 10.4: MS Visual C++ 2005 SP1 Redistributable Package installieren
- Download der GoldenGate Software for Windows (Version 10.4 oder Version 11.1)
http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html?
- Anlegen der GodenGate Folder (pro Installation ein Verzeichnis)
z.B.: „D:\ogg_source“ und „D:\ogg_target“ wie in dieser Demo benutzt
- Erstellen der GoldenGate Subfolder mit GGSCI Programm (Jeweils im Source und im Target Folder)
GGSCI  CREATE SUBDIRS
- ADDEVENTS: Files „category.dll“ und „ggsmsg.dll“ in Folder „C:\WINDOWS\system32” kopieren
Installationshandbuch: „Oracle GoldenGate, Oracle Installation and Setup Guide“.
1.2. Source und Target Überblick
Merkmal
Source
Target
GoldenGate Folder
D:\ogg_source
D:\ogg_target
Demo Folder
…\demo_source
…\demo_target
Manager-Name
OGG_MGR_SRC
OGG_MGR_TAR
7810
7811
Manager-Port
Oracle-UID/PW
oggs/source
oggt/target
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
4
Die Demo wäre auch mit nur einer GG Installation durchführbar, weil nur eine Datenbank verwendet wird. In
der Praxis hat man in der Regel unterschiedliche Umgebungen für Source und Target, d.h. die Replikation
wird zwischen Datenbanken auf verschiedenen Rechnern durchgeführt. Wenn man sich für zwei
unabhängige GG Umgebungen entschieden hat, sollte man auch konsequenter Weise zwei verschiedene
Datenbanken auf einem Laptop bzw. PC für die Demo verwenden. Das wäre dann realistischer als die hier
genutzte Konfiguration.
2. GoldenGate Demo Files
Nach dem Entpacken des Files „OGGProject.zip“ entstehen die Folder „ogg_source“ und „ogg_target“.
Der Inhalt dieser Folder soll in die gleichnamigen Installations-Folder kopiert werden. Hier wurden die Folder
umbenannt in „demo_source“ und „demo_target“ und diese dann in die jeweiligen Folder kopiert. Das ist
übersichtlicher, weil alle Demo Files in einem separaten Verzeichnis zu finden sind. Nur ausgewählte
Parameter Files und GGSCI Kommando Files müssen in die Installations-Folder kopiert werden.
ogg_source
( umbenennen in: demo_source)
data
prm
biginsert.sql
delete_prod.sql
ER_src_setup.oby
insert_cust.sql
insert_orders.sql
insert_prod.sql
src_clean.sql
src_count.sql
src_create.sql
src_load.bat
update_orders.sql
update_order_det.sql
update_prod.sql
Die Files im Foldern “prm” müssen in den Folder “D:\ogg_source\dirprm” kopiert werden und ersetzen dort
die vorhandenen Default-Files. Das Kommando-File „ER_src_setup.oby“ wird nach „ogg_source“ kopiert,
weil es für die Ausführung im gleichen Verzeichnis wie GGSCI stehen muß (siehe 2.1).
ogg_target
( umbenennen in: demo_target)
prm
ER_target_setup.oby
select_audit.sql
select_cust.sql
select_orders.sql
select_order_det.sql
select_prod.sql
target_clean.sql
target_count.sql
target_create.sql
Die Files im Folder “prm” müssen in den Folder “D:\ogg_target\dirprm“ kopiert werden und ersetzen dort
die vorhandenen Default-Files. File „ER_target_setup.oby“ muß analog zu „ER_src_setup.oby“ in den
Folder „ogg_target“ kopiert werden (siehe 2.1).
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
5
commands.txt
OGGHandsOnLab.pdf
OGGInstallation.pdf
(Kommandos, die während der Demo ausgeführt werden müssen)
(Demo-Beschreibung)
(Installations-Beschreibung)
Da diese 3 Dokumente nicht ganz schlüssig sind in ihrer Abgrenzung zueinander, werden sie durch
diese Beschreibung ersetzt. Sie ist ausführlicher und umfangreicher und auch für Personen geeignet,
die sich bisher wenig oder gar nicht mit GoldenGate beschäftigt haben.
2.1. Kopieren der Demo Parameterdateien
D:\...\demo_source\prm\defgen.prm
D:\...\demo_source\prm\ggs_ext.prm
D:\...\demo_source\prm\ggs_pump.prm
D:\...\demo_source\prm\mgr.prm




d:\ogg_source\dirprm\
d:\ogg_source\dirprm\
d:\ogg_source\dirprm\
d:\ogg_source\dirprm\
D:\...\demo_source\ER_src_setup.oby

d:\ogg_source
D:\...\demo_target\prm\ggs_rep.prm
D:\...\demo_target\prm\mgr.prm


D:\...\demo_target\ER_target_setup.oby 
d:\ogg_target\dirprm\
d:\ogg_source\dirprm\
d:\ogg_target
Die anderen Dateien (bat, sql) verbleiben in den Demo-Foldern und werden dort ausgeführt.
2.2. Modifikation der Parameterdateien
Datei: GLOBALS
Um die beiden Manager unterscheiden zu können, bekommen sie eindeutige Namen:
D:\ogg_source\GLOBALS:
D:\ogg_target\\GLOBALS:
MGRSERVNAME OGG_MGR_SRC
MGRSERVNAME OGG_MGR_TAR
Datei: mgr.prm
Beide Manager müssen unterschiedliche TCP/IP Ports für die Kommunikation benutzen. Default-Port 7809
wird in dieser Demo nicht verwendet.
D:\ogg_source\mgr.prm:
D:\ogg_target\mgr.prm:
PORT 7810
PORT 7811
Korrekturen in der Datei: ggs_pump.prm
RMTTRAIL .\dirdat\rt
 RMTTRAIL d:\ogg_target\dirdat\rt
Der REPLICAT-Prozeß auf der TARGET-Seite nutzt den Default „.\dirdat\rt“. Das führt dazu, daß der
REPLICAT-Prozeß kein Trail-File findet.
RMTHOST localhost, MGRPORT 7810  RMTHOST localhost, MGRPORT 7811
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
6
Korrektur in der Datei: ER_target_setup.oby
ADD RMTTRAIL .\dirdat\rt, EXTRACT ggs_pump
 ADD RMTTRAIL d:\ogg_target\dirdat\rt, EXTRACT ggs_pump
3. GoldenGate Demo
Bei der Replikation der Tabellen von Source zu Target werden die vielfältigen GG-ModifikationsMöglichkeiten demonstriert. Die Übersicht, zeigt die Modifikationen auf Basis der beteiligten Tabellen:
Source Table
GG-Modifikation
CUSTOMERS
Map
Filter (WHERE)
Split column CONTACTNAME to
CONTACTFIRST & CONTACTLAST
(@STREXT, @STRFIND, @STRLEN)
ORDERS
ORDER_DETAILS
PRODUCTS
BIGINSERT_TABLE
Map
Decodes SHIPPERID to Shippername
Select on Lookup-Table: SHIPPERS
(SQLEXEC)
Additional Column: SHIPPERNAME
(@GETVAL)
Map
Stored-Proc compute the Shipping-Price
(SQLEXEC)
Additional Column: SP_TOTAL
Map
Transform NUMBER Field DISCONTINUED
To VARCHAR2 (@CASE)
Additional Columns in PRODUCTS_AUDIT
(@DATENOW, @GETENV)
Map (1:1)
Target Table(s)
US_CUSTOMERS
NON_US_CUSTOMERS
ORDERS
ORDER_DETAILS
PRODUCTS
PRODUCTS_AUDIT
BIGINSERT_TABLE
Die Tabelle BIGINSERT_TABLE wird dazu benutzt, Workload zu erzeugen. Über die Stored Procedure
BIGINSERT kann die Anzahl der Insert-Operationen parametrisiert werden. Damit wird die Möglichkeit
geboten, die Latency bzw. Performance der Demo-Umgebung zu testen.
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
7
3.1. Setup der Oracle Datenbank
3.1.1. Voraussetzungen
- Oracle Version: 9i, 10g oder 11g
- Windows User muß das Recht haben, die Archivelog Files lesen zu dürfen
- ARCHIVELOG Mode der Datenbank
- Minimal Supplemental Logging für die Datenbank ist aktiviert
(select supplemental_log_dat_min from v$database;  YES or IMPLICIT)
3.1.2. User und Datenbankobjekte
- Anlegen der User „oggs“ und „oggt“
create user oggs identified by source;
grant connect, dba, resource to oggs;
create user oggt identified by target;
grant connect, dba, resource to oggt;
- Erstellen der benötigten Datenbankobjekte
sqlplus oggs/source @src_create.sql
(Aufruf im Folder: d:\ogg_source\demo_source)
sqlplus oggt/target @target_create.sql (Aufruf im Folder: d:\ogg_target\demo_target)
3.2. Setup von GoldenGate
- Windows Services für die GoldenGate Manager installieren
D:\ogg_source> install addevents addservice manualstart (GGS_MGR_SRC Service)
D:\ogg_target> install addevents addservice manualstart (GGS_MGR_TAR Service)
- Windows Services starten
Start  Control Panel  Administrative Tools  Services  Services starten
oder
D:\ogg_source> ggsci  start manager
D:\ogg_target> ggsci  start manager
- Setup der GoldenGate Umgebung
D:\ogg_source> ggsci  obey ER_src_setup.oby
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Windows (optimized), Oracle 11 on Jul 28 2010 17:20:29
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (jjaensch-lap) 1> obey ER_src_setup.oby
GGSCI (jjaensch-lap) 2> -- optionally start manager from here
GGSCI (jjaensch-lap) 3> --START MANAGER
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
8
GGSCI (jjaensch-lap) 4>
GGSCI (jjaensch-lap) 4> -- log in to create supplemental log groups
GGSCI (jjaensch-lap) 5> DBLOGIN USERID oggs PASSWORD source
Successfully logged into database.
GGSCI (jjaensch-lap) 6>
GGSCI (jjaensch-lap) 6> --add table level supplemental logging
GGSCI (jjaensch-lap) 7> --force logging on non-updated columns with COLS keyword
GGSCI (jjaensch-lap) 8> --because we need values in those 2 columns for
GGSCI (jjaensch-lap) 9> --parameter passing later
GGSCI (jjaensch-lap) 10> ADD TRANDATA customers
Logging of supplemental redo log data is already enabled for table OGGS.CUSTOMERS.
GGSCI (jjaensch-lap) 11> ADD TRANDATA orders
Logging of supplemental redo log data is already enabled for table OGGS.ORDERS.
GGSCI (jjaensch-lap) 12> ADD TRANDATA order_details COLS (UNITPRICE, QUANTITY)
Logging of supplemental redo log data is already enabled for table OGGS.ORDER_DETAILS.
GGSCI (jjaensch-lap) 13> ADD TRANDATA products
Logging of supplemental redo log data is already enabled for table OGGS.PRODUCTS.
GGSCI (jjaensch-lap) 14> ADD TRANDATA biginsert_table
Logging of supplemental redo log data is already enabled for table OGGS.BIGINSERT_TABLE.
GGSCI (jjaensch-lap) 15>
GGSCI (jjaensch-lap) 15> --verify supplemental log groups were created
GGSCI (jjaensch-lap) 16> INFO TRANDATA *
Logging of supplemental redo log data is enabled for table OGGS.BIGINSERT_TABLE
Logging of supplemental redo log data is enabled for table OGGS.CUSTOMERS
Logging of supplemental redo log data is enabled for table OGGS.ORDERS
Logging of supplemental redo log data is enabled for table OGGS.ORDER_DETAILS
Logging of supplemental redo log data is enabled for table OGGS.PRODUCTS
GGSCI (jjaensch-lap) 17>
GGSCI (jjaensch-lap) 17> --register log based extract to the manager starting now
GGSCI (jjaensch-lap) 18> ADD EXTRACT ggs_ext, TRANLOG, BEGIN now
EXTRACT added.
GGSCI (jjaensch-lap) 19>
GGSCI (jjaensch-lap) 19> --register the extracts trail info
GGSCI (jjaensch-lap) 20> ADD EXTTRAIL .\dirdat\et, EXTRACT ggs_ext
EXTTRAIL added.
GGSCI (jjaensch-lap) 21>
GGSCI (jjaensch-lap) 21> --register the pump process
GGSCI (jjaensch-lap) 22> --pump reads from exttrailsource file named et
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
9
GGSCI (jjaensch-lap) 23> ADD EXTRACT ggs_pump, EXTTRAILSOURCE .\dirdat\et
EXTRACT added.
GGSCI (jjaensch-lap) 24>
GGSCI (jjaensch-lap) 24> --register the remote trail
GGSCI (jjaensch-lap) 25> --pump writes to remote trailfile named rt
GGSCI (jjaensch-lap) 26> --ADD RMTTRAIL .\dirdat\rt, EXTRACT ggs_pump
GGSCI (jjaensch-lap) 27> ADD RMTTRAIL d:\ogg_target\dirdat\rt, EXTRACT ggs_pump
RMTTRAIL added.
GGSCI (jjaensch-lap) 28>
GGSCI (jjaensch-lap) 28>
GGSCI (jjaensch-lap) 28>
GGSCI (jjaensch-lap) 29>
D:\ogg_target> ggsci  obey ER_target_setup.oby
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Windows (optimized), Oracle 11 on Jul 28 2010 17:20:29
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
GGSCI (jjaensch-lap) 1> obey ER_target_setup.oby
GGSCI (jjaensch-lap) 2> --START MANAGER
GGSCI (jjaensch-lap) 3>
GGSCI (jjaensch-lap) 3> --Login to Database
GGSCI (jjaensch-lap) 4> dblogin userid oggt, password target
Successfully logged into database.
GGSCI (jjaensch-lap) 5>
GGSCI (jjaensch-lap) 5> --Adds Checkpoint Table
GGSCI (jjaensch-lap) 6> add checkpointtable OGGT.GGSCHKPT
Successfully created checkpoint table OGGT.GGSCHKPT.
GGSCI (jjaensch-lap) 7>
GGSCI (jjaensch-lap) 7> --Adds the replicat process
GGSCI (jjaensch-lap) 8> ADD REPLICAT GGS_REP, EXTTRAIL .\dirdat\rt, CHECKPOINTTABLE OGGT.GGSCHKPT
REPLICAT added.
GGSCI (jjaensch-lap) 9>
GGSCI (jjaensch-lap) 9>
GGSCI (jjaensch-lap) 9>
GGSCI (jjaensch-lap) 9>
GGSCI (jjaensch-lap) 9>
GGSCI (jjaensch-lap) 9>
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
10
GGSCI (jjaensch-lap) 9>
GGSCI (jjaensch-lap) 10>
- Erstellen eines Data-Definition Files mittels DEFGEN Utilitiy
D:\ogg_source> defgen paramfile .\dirprm\defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.1.1.0.0 Build 078
Windows (optimized), Oracle 11 on Jul 28 2010 17:50:26
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-11-10 10:33:06
***********************************************************************
Operating System Version:
Microsoft Windows XP Professional, on x86
Version 5.1 (Build 2600: Service Pack 3)
Process id: 5992
***********************************************************************
**
Running with the following parameters
**
***********************************************************************
--paramter file to run the defgen utility
--to document source metadata
--for heterogenous replication
--from ogg_source directory issue
--DEFGEN PARAMFILE .\dirprm\defgen.prm
--write out source defs file to local directory
--then copy it to targets dirdef directory
DEFSFILE .\dirdef\source.def, PURGE
--log in to get meta data
USERID oggs, PASSWORD ******
--get definitions for these tables
TABLE OGGS.CUSTOMERS;
Retrieving definition for OGGS.CUSTOMERS
TABLE OGGS.ORDERS;
Retrieving definition for OGGS.ORDERS
TABLE OGGS.ORDER_DETAILS;
Retrieving definition for OGGS.ORDER_DETAILS
TABLE OGGS.PRODUCTS;
Retrieving definition for OGGS.PRODUCTS
TABLE OGGS.BIGINSERT_TABLE;
Retrieving definition for OGGS.BIGINSERT_TABLE
Definitions generated for 5 tables in .\dirdef\source.def
Hinweis: Das Data-Definition File ist notwendig, weil auf der Target Seite “Column Mapping”
und „Transformations“ durchgeführt werden sollen. Dieses „Source-Definition“ File
benötigt der REPLICAT-Prozeß zur Durchführung der geforderten Änderungen.
- Übertragen des erstellten Files von Source zu Target
D:\ogg_source\dirdef\source.def  D:\ogg_target\dirdef
- Starten der GoldenGate Prozesse auf der Source Seite
D:\ogg_source> ggsci  start ER *
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
11
GGSCI (jjaensch-lap) 30> start er *
Sending START request to MANAGER ('OGG_MGR_SRC') ...
EXTRACT GGS_EXT starting
Sending START request to MANAGER ('OGG_MGR_SRC') ...
EXTRACT GGS_PUMP starting
GGSCI (jjaensch-lap) 31> info all
Program
Status
Group
Lag
Time Since Chkpt
MANAGER
EXTRACT
EXTRACT
RUNNING
RUNNING
RUNNING
GGS_EXT
GGS_PUMP
00:17:00
00:00:00
00:00:09
00:00:06
GGSCI (jjaensch-lap) 32> info er *
EXTRACT
GGS_EXT
Checkpoint Lag
Log Read Checkpoint
Last Started 2010-11-17 13:45
Status RUNNING
00:00:00 (updated 00:00:02 ago)
Oracle Redo Logs
2010-11-17 13:48:30 Seqno 554, RBA 10112512
EXTRACT
GGS_PUMP
Checkpoint Lag
Log Read Checkpoint
Last Started 2010-11-17 13:45
Status RUNNING
00:00:00 (updated 00:00:00 ago)
File .\dirdat\et000000
First Record RBA 0
GGSCI (jjaensch-lap) 33>
- Starten des GoldenGate REPLICAT Prozeß auf der Target Seite
D:\ogg_target> ggsci  start ER *
GGSCI (jjaensch-lap) 11> start er *
Sending START request to MANAGER ('OGG_MGR_TAR') ...
REPLICAT GGS_REP starting
GGSCI (jjaensch-lap) 12> info all
Program
Status
Group
Lag
Time Since Chkpt
MANAGER
REPLICAT
RUNNING
RUNNING
GGS_REP
00:00:00
00:00:05
GGSCI (jjaensch-lap) 13> info er *
REPLICAT
GGS_REP
Checkpoint Lag
Log Read Checkpoint
Last Started 2010-11-17 13:47
Status RUNNING
00:00:00 (updated 00:00:04 ago)
File .\dirdat\rt000000
First Record RBA 0
GGSCI (jjaensch-lap) 14>
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
12
3.3.
Demo Schritte
3.3.1. Füllen der Source Tabellen
Auf der Source Seite sind ein EXTRACT und ein PUMP Prozeß und auf der Target Seite ein REPLICAT
Prozeß gestartet. Diese Prozesse befinden sich in Bereitschaft und werden sofort aktiv, wenn zu den
definierten Tabellen DML-Operationen erkannt werden. Mit dem folgenden INSERT Operationen werden die
Source Tabellen gefüllt und die Änderungen werden von GoldenGate in Abhängigkeit der definierten Regeln
auf die Target Seite repliziert. Geben Sie nach den Änderungen auf der Source Seite immer ein COMMIT!
- Abarbeiten des Kommando-Files mit den SQLPLUS Aufrufen (pro Tabelle, INSERT-Kommandos)
D:\ogg_source\demo_source> src_load.bat
3.3.2. Anzeige der Tabelleninhalte
Source Seite
D:\ogg_source\demo_source> sqlplus oggs/source @src_count.sql
Die Source Tabellen sollten diese Anzahl Rows haben:
CUSTOMERS
ORDERS
ORDER_DETAIL
PRODUCTS
BIGINSERT_TABLE
91
- 830
- 2155
77
0
Target Seite
D:\ogg_target\demo_target> sqlplus oggt/target @target_count.sql
Die Target Tabellen sollten diese Anzahl Rows haben:
US_CUSTOMERS
NON_US_ORDERS
ORDERS
ORDER_DETAIL
PRODUCTS
PRODUCTS_AUDIT
BIGINSERT_TABLE
SHIPPERS
13
78
- 830
- 2155
77
77
0
4
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
13
3.3.3. Inserts und Updates
INSERTS in Tabelle CUSTOMERS
Source Seite
D:\ogg_source\demo_source> sqlplus oggs/source @insert_cust.sql
D:\ogg_source\demo_source> select count(*) from CUSTOMERS;
Anzahl Rows in Tabelle CUSTOMERS: 94 (3 neue Rows)
Target Seite
D:\ogg_target\demo_target> sqlplus oggt/target
select count(*) from US_CUSTOMERS;
Anzahl Rows in Tabelle US_CUSTOMERS: 15 (2 neue Rows)
D:\ogg_target\demo_target> select count(*) from NON_US_CUSTOMERS;
Anzahl Rows in Tabelle NON_US_CUSTOMERS: 79 (1 neue Row)
Anzeige der neuen Rows in den Tabellen US_CUSTOMERS und NON_US_CUSTOMERS:
D:\ogg_target\demo_target> @select_cust.sql;
CUSTO
----GOLFK
SERVM
COMPANYNAME
--------------------Golf King
Service Master Inc.
CONTACTFIRST
------------Ben
John
CONTACTLAST
------------Hogan
Smith
COUNTRY
--------USA
USA
CUSTO COMPANYNAME
CONTACTFIRST CONTACTLAST
COUNTRY
----- --------------------- ------------- ------------- --------FERAR Ferrari
Marco
Ferrari
Italy
INSERTS und UPDATES in Tabelle ORDERS
Source Seite
D:\ogg_source\demo_source> sqlplus oggs/source @insert_orders.sql
D:\ogg_source\demo_source> select count(*) from ORDERS;
Anzahl Rows in Tabelle ORDERS: 832 (2 neue Rows)
Target Seite
D:\ogg_target\demo_target> sqlplus oggt/target
select count(*) from ORDERS;
Anzahl Rows in Tabelle ORDERS: 832 (2 neue Rows)
Anzeige der beiden neuen Rows der Tabelle ORDERS:
D:\ogg_target\demo_target> @select_orders.sql
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
SHIPPERID SHIPPERNAME
---------- ---------------------------------------1 UPS
2 FedEx
3 U.S. Postal Service
4 DHL
ORDERID
---------1
2
CUSTO
SHIPVIA SHIPPERNAME
----- ---------- ---------------------------------------VINET
2 FedEx
TOMSP
1 UPS
Source Seite
D:\ogg_source\demo_source> sqlplus oggs/source @update_orders.sql
Target Seite
D:\ogg_target\demo_target> @select_orders.sql
SHIPPERID SHIPPERNAME
---------- ---------------------------------------1 UPS
2 FedEx
3 U.S. Postal Service
4 DHL
ORDERID
---------1
2
CUSTO
SHIPVIA SHIPPERNAME
----- ---------- ---------------------------------------VINET
3 U.S. Postal Service
TOMSP
3 U.S. Postal Service
UPDATES in Tabelle ORDER_DETAILS
Target Seite
Anzeige der Rows der Tabelle ORDER_DETAILS vor UPDATE (PRODUCTID = 3):
D:\ogg_target\demo_target> sqlplus oggt/target @select_order_det.sql
ORDERID PRODUCTID UNITPRICE
QUANTITY
TOTAL
---------- ---------- ---------- ---------- ---------10405
3
8
50
400
10485
3
8
20
160
10540
3
10
60
600
10591
3
10
14
140
10702
3
10
6
60
10742
3
10
20
200
10289
3
8
30
240
11017
3
10
25
250
11077
3
10
4
40
10764
3
10
20
200
10849
3
10
49
490
10857
3
10
30
300
12 rows selected.
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
14
15
Source Seite
Update Tabelle ORDER_DETAILS (UNITPRICE = 100 where PRODUCTID = 3):
D:\ogg_source\demo_source> sqlplus oggs/source @update_order_det.sql
Target Seite
Anzeige der Rows der Tabelle ORDER_DETAILS nach UPDATE (PRODUCTID = 3):
D:\ogg_target\demo_target> sqlplus oggt/target @select_order_det.sql
ORDERID PRODUCTID UNITPRICE
QUANTITY
TOTAL
---------- ---------- ---------- ---------- ---------10405
3
100
50
5000
10485
3
100
20
2000
10540
3
100
60
6000
10591
3
100
14
1400
10702
3
100
6
600
10742
3
100
20
2000
10289
3
100
30
3000
11017
3
100
25
2500
11077
3
100
4
400
10764
3
100
20
2000
10849
3
100
49
4900
10857
3
100
30
3000
12 rows selected.
UPDATES in Tabelle PRODUCTS
Target Seite
Anzeige der Rows der Tabelle PRODUCTS (PRODUCTID > 70) vor UPDATE
D:\ogg_target\demo_target> sqlplus oggt/target @select_prod.sql
PRODUCTID DISCONTI
---------- -------71 NO
72 NO
73 NO
74 NO
75 NO
76 NO
77 NO
7 rows selected.
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
16
Source Seite
Update Tabelle PRODUCTS (DISCONTINUED = 1 where PRODUCTID > 70)
D:\ogg_source\demo_source> sqlplus oggs/source @update_prod.sql
Target Seite
Anzeige der Rows der Tabelle PRODUCTS (PRODUCTID > 70) nach UPDATE
D:\ogg_target\demo_target> sqlplus oggt/target @select_prod.sql
PRODUCTID DISCONTI
---------- -------71 YES
72 YES
73 YES
74 YES
75 YES
76 YES
77 YES
7 rows selected.
Audit aller Transaktionen in Tabelle PRODUCTS in Tabelle PRODUCTS_AUDIT
Target Seite
Anzeigen der bisherigen Einträge, verursacht durch INSERT & UPDATE
D:\ogg_target\demo_target> sqlplus oggt/target @select_audit.sql
PRDID
----71
72
73
74
75
76
77
71
72
73
74
75
76
77
DIS
--NO
NO
NO
NO
NO
NO
NO
YES
YES
YES
YES
YES
YES
YES
TRANTIME
---------------------------12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
POSTTIME
---------------------------12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
SOURCE_NODE
-----------jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
O
I
I
I
I
I
I
I
C
C
C
C
C
C
C
B
A
A
A
A
A
A
A
A
A
A
A
A
A
A
UNAME
-----SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
14 rows selected.
Source Seite
Löschen aller Rows der Tabelle PRODUCTS (where PRODUCTID > 70)
D:\ogg_source\demo_source> @delete_prod.sql
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
17
Target Seite
Anzeigen der aller Einträge nach DELETE auf Tabelle PRODUCTS
D:\ogg_target\demo_target> sqlplus oggt/target @select_audit.sql
PRDID
----71
72
73
74
75
76
77
71
72
73
74
75
76
77
71
72
73
74
75
76
77
DIS
--NO
NO
NO
NO
NO
NO
NO
YES
YES
YES
YES
YES
YES
YES
TRANTIME
---------------------------12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.22.00.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.37.35.000000 AM
12-NOV-10 10.42.02.000000 AM
12-NOV-10 10.42.02.000000 AM
12-NOV-10 10.42.02.000000 AM
12-NOV-10 10.42.02.000000 AM
12-NOV-10 10.42.02.000000 AM
12-NOV-10 10.42.02.000000 AM
12-NOV-10 10.42.02.000000 AM
POSTTIME
---------------------------12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.23.15.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.37.43.000000 AM
12-NOV-10 10.42.07.000000 AM
12-NOV-10 10.42.07.000000 AM
12-NOV-10 10.42.07.000000 AM
12-NOV-10 10.42.07.000000 AM
12-NOV-10 10.42.07.000000 AM
12-NOV-10 10.42.07.000000 AM
12-NOV-10 10.42.07.000000 AM
SOURCE_NODE
-----------jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
jjaensch-lap
O
I
I
I
I
I
I
I
C
C
C
C
C
C
C
D
D
D
D
D
D
D
B
A
A
A
A
A
A
A
A
A
A
A
A
A
A
B
B
B
B
B
B
B
UNAME
-----SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
21 rows selected.
3.3.4. GoldenGate Replicate Reports
D:\ogg_target> ggsci  send ggs_rep, report
D:\ogg_target> ggsci  view report ggs_rep, details
Report vor Ausführung der INSERT in Tabelle BIGINSERT_TABLE
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.0.0 Build 078
Windows (optimized), Oracle 11 on Jul 28 2010 18:18:19
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2010-11-12 10:20:34
***********************************************************************
Operating System Version:
Microsoft Windows XP Professional, on x86
Version 5.1 (Build 2600: Service Pack 3)
Process id: 5296
Description:
***********************************************************************
**
Running with the following parameters
**
***********************************************************************
--name the replicat process
REPLICAT ggs_rep
--helpful if more than one instnace on the box
--SETENV (ORACLE_SID="ORA10GR2")
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
USERID oggt, PASSWORD ******
--use the sourcedefs file to read the trail
SOURCEDEFS .\dirdef\source.def
--throw error records to discard file
DISCARDFILE .\dirrpt\ggs_rep.dsc, purge
--on encountering an error when applying data to the target db
--set a global response to all errors which is
--log the error ro the discard file but continue processing
REPERROR default, discard
REPORTCOUNT EVERY 1 MINUTES, RATE
--increase the thruput of replicat processing by arranging
--similar SQL statements into arrays and applying them
--at an accelerated rate
BATCHSQL
--ignore truncates is default
GETTRUNCATES
--Mapping section, source to target
--filtering example, exclude USA CUSTOMERS
--transformation example - split contact name into 2 columns
MAP OGGS.CUSTOMERS, TARGET OGGT.NON_US_CUSTOMERS,
COLMAP (USEDEFAULTS,
CONTACTFIRST = @STREXT (CONTACTNAME, 1, @STRFIND (CONTACTNAME," ") - 1),
CONTACTLAST = @STREXT (CONTACTNAME, @STRFIND (CONTACTNAME," ") + 1, @STRLEN(CONTACTNAME)))
WHERE (COUNTRY <> "USA");
--filtering example, only USA CUSTOMERS
--transformation example - split contact name into 2 columns
MAP OGGS.CUSTOMERS, TARGET OGGT.US_CUSTOMERS,
COLMAP (USEDEFAULTS,
CONTACTFIRST = @STREXT (CONTACTNAME, 1, @STRFIND (CONTACTNAME," ") - 1),
CONTACTLAST = @STREXT (CONTACTNAME, @STRFIND (CONTACTNAME," ") + 1, @STRLEN (CONTACTNAME)))
WHERE (COUNTRY = "USA");
--in-stream SQL query example to do a look up which decodes shipper id from source into
--shipper name on target
MAP OGGS.ORDERS, TARGET OGGT.ORDERS,
SQLEXEC (ID lookup, QUERY " select SHIPPERNAME from SHIPPERS where SHIPPERID = :sid ",
PARAMS (sid = SHIPVIA)),
COLMAP (USEDEFAULTS, SHIPPERNAME = @GETVAL(lookup.SHIPPERNAME));
--stored proc example...call a stored proc to create a calculated field
--target table has an extra column called TOTAL
--insert or update fires the SP_TOTAL stored proc
--stored proc has 2 inputs: price and quantity
--stored proc has an out value of TOTAL which gets returned and set into the column
MAP OGGS.ORDER_DETAILS, TARGET OGGT.ORDER_DETAILS,
SQLEXEC (SPNAME SP_TOTAL, PARAMS (price = UNITPRICE, quantity = QUANTITY)),
COLMAP (USEDEFAULTS, TOTAL = SP_TOTAL.total);
--example of using case evaluation to decode
MAP OGGS.PRODUCTS, TARGET OGGT.PRODUCTS,
COLMAP (USEDEFAULTS, DISCONTINUED = @CASE (DISCONTINUED, "0", "NO", "1", "YES", "UNKNOWN"));
--simple like-to-like mapping
MAP OGGS.BIGINSERT_TABLE, TARGET OGGT.BIGINSERT_TABLE;
--from here forward treat all updates & deletes as inserts
INSERTALLRECORDS
--example of mapping to an audit table and providing enhanced data
MAP OGGS.PRODUCTS, TARGET OGGT.PRODUCTS_AUDIT,
COLMAP (USEDEFAULTS,
DISCONTINUED = @CASE (DISCONTINUED, "0" , "NO", "1","YES", "UNKNOWN"),
TRAN_TIME = GGS_TRANS_TIMESTAMP,
POST_TIME = @DATENOW(),
SOURCE_NODE = @GETENV("GGENVIRONMENT", "HOSTNAME"),
OP_TYPE = GGS_OP_TYPE,
BEFORE_AFTER_IND = GGS_BEFORE_AFTER_IND,
USER_NAME = @GETENV("GGENVIRONMENT", "OSUSERNAME"));
CACHEMGR virtual memory values (may have been adjusted)
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
18
CACHEBUFFERSIZE:
CACHESIZE:
CACHEBUFFERSIZE (soft max):
CACHEPAGEOUTSIZE (normal):
PROCESS VM AVAIL FROM OS (min):
CACHESIZEMAX (strict force to disk):
64K
512M
4M
4M
1G
881M
Database Version:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE
11.1.0.7.0
Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE
= "AMERICAN"
NLS_TERRITORY
= "AMERICA"
NLS_CHARACTERSET = "WE8MSWIN1252"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
***********************************************************************
**
Run Time Messages
**
***********************************************************************
Opened trail file .\dirdat\rt000000 at 2010-11-12 10:20:34
MAP resolved (entry OGGS.CUSTOMERS):
MAP OGGS.CUSTOMERS, TARGET OGGT.NON_US_CUSTOMERS, COLMAP (USEDEFAULTS,
CONTACTFIRST = @STREXT (CONTACTNAME, 1, @STRFIND(CONTACTNAME," ") - 1), CON
TACTLAST = @STREXT(CONTACTNAME, @STRFIND(CONTACTNAME," ") + 1, @STRLEN(CONTACTNAME)))
WHERE (COUNTRY <> "USA");
Using the following default columns with matching names:
CUSTOMERID=CUSTOMERID, COMPANYNAME=COMPANYNAME, CONTACTTITLE=CONTACTTITLE,
ADDRESS=ADDRESS, CITY=CITY, REGION=REGION, POSTALCODE=POSTALCODE, COUNTRY=COUNTRY,
PHONE=PHONE, FAX=FAX
Using the following key columns for target table OGGT.NON_US_CUSTOMERS: CUSTOMERID.
MAP resolved (entry OGGS.CUSTOMERS):
MAP OGGS.CUSTOMERS, TARGET OGGT.US_CUSTOMERS, COLMAP (USEDEFAULTS,
CONTACTFIRST = @STREXT (CONTACTNAME, 1, @STRFIND (CONTACTNAME," ") - 1), CONTAC
TLAST = @STREXT (CONTACTNAME, @STRFIND (CONTACTNAME," ") + 1, @STRLEN (CONTACTNAME)))
WHERE (COUNTRY = "USA");
Using the following default columns with matching names:
CUSTOMERID=CUSTOMERID, COMPANYNAME=COMPANYNAME, CONTACTTITLE=CONTACTTITLE,
ADDRESS=ADDRESS, CITY=CITY, REGION=REGION, POSTALCODE=POSTALCODE, COUNTR
Y=COUNTRY, PHONE=PHONE, FAX=FAX
Using the following key columns for target table OGGT.US_CUSTOMERS: CUSTOMERID.
MAP resolved (entry OGGS.ORDERS):
MAP OGGS.ORDERS, TARGET OGGT.ORDERS, SQLEXEC (ID lookup, QUERY
" select SHIPPERNAME from SHIPPERS where SHIPPERID = :sid ", PARAMS (sid = SHIPVIA))
, COLMAP (USEDEFAULTS, SHIPPERNAME = @GETVAL(lookup.SHIPPERNAME));
Using the following default columns with matching names:
ORDERID=ORDERID, CUSTOMERID=CUSTOMERID, EMPLOYEEID=EMPLOYEEID,
ORDERDATE=ORDERDATE, REQUIREDDATE=REQUIREDDATE, SHIPPEDDATE=SHIPPEDDATE, SHIPVIA=SHI
PVIA, FREIGHT=FREIGHT, SHIPNAME=SHIPNAME, SHIPADDRESS=SHIPADDRESS,
SHIPCITY=SHIPCITY, SHIPREGION=SHIPREGION, SHIPPOSTALCODE=SHIPPOSTALCODE, SHIPCOUNT
RY=SHIPCOUNTRY
Using the following key columns for target table OGGT.ORDERS: ORDERID.
MAP resolved (entry OGGS.ORDER_DETAILS):
MAP OGGS.ORDER_DETAILS, TARGET OGGT.ORDER_DETAILS, SQLEXEC (SPNAME SP_TOTAL, PARAMS
(price = UNITPRICE, quantity = QUANTITY)), COLMAP (USEDEFAULTS,
TOTAL = SP_TOTAL.total);
Using the following default columns with matching names:
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
19
20
ORDERID=ORDERID, PRODUCTID=PRODUCTID, UNITPRICE=UNITPRICE, QUANTITY=QUANTITY, DISCOUNT=DISCOUNT
Using the following key columns for target table OGGT.ORDER_DETAILS: ORDERID, PRODUCTID.
MAP resolved (entry OGGS.PRODUCTS):
MAP OGGS.PRODUCTS, TARGET OGGT.PRODUCTS, COLMAP (USEDEFAULTS, DISCONTINUED = @CASE
(DISCONTINUED, "0", "NO", "1", "YES", "UNKNOWN"));
Using the following default columns with matching names:
PRODUCTID=PRODUCTID, PRODUCTNAME=PRODUCTNAME, SUPPLIERID=SUPPLIERID,
CATEGORYID=CATEGORYID, QUANTITYPERUNIT=QUANTITYPERUNIT, UNITPRICE=UNITPRICE, U
NITSINSTOCK=UNITSINSTOCK, UNITSONORDER=UNITSONORDER,
REORDERLEVEL=REORDERLEVEL, DISCONTINUED=DISCONTINUED
Using the following key columns for target table OGGT.PRODUCTS: PRODUCTID.
MAP resolved (entry OGGS.PRODUCTS):
MAP OGGS.PRODUCTS, TARGET OGGT.PRODUCTS_AUDIT, COLMAP (USEDEFAULTS,
DISCONTINUED = @CASE (DISCONTINUED, "0" , "NO", "1","YES", "UNKNOWN"),
TRAN_TIME = GGS_TRANS_TIMESTAMP, POST_TIME = @DATENOW(),
SOURCE_NODE = @GETENV("GGENVIRONMENT", "HOSTNAME"), OP_TYPE = GGS_OP_TYPE,
BEFORE_AFTER_IND = GGS_BEFORE_AFTER_IND,
USER_NAME = @GETENV("GGENVIRONMENT", "OSUSERNAME") );
2010-11-12 10:23:07 WARNING OGG-00869 No unique key is defined for table PRODUCTS_AUDIT.
All viable columns will be used to represent the key, but may not guarantee uniqueness.
KEYCOLS may be used to define the key.
Using the following default columns with matching names:
PRODUCTID=PRODUCTID, PRODUCTNAME=PRODUCTNAME, SUPPLIERID=SUPPLIERID,
CATEGORYID=CATEGORYID, QUANTITYPERUNIT=QUANTITYPERUNIT, UNITPRICE=UNITPRICE, U
NITSINSTOCK=UNITSINSTOCK, UNITSONORDER=UNITSONORDER,
REORDERLEVEL=REORDERLEVEL, DISCONTINUED=DISCONTINUED
Using the following key columns for target table OGGT.PRODUCTS_AUDIT:
PRODUCTID, PRODUCTNAME, SUPPLIERID, CATEGORYID, QUANTITYPERUNIT, UNITPRICE, UNITSINSTOCK,
UNITSONORDER, REORDERLEVEL, DISCONTINUED, TRAN_TIME, POST_TIME, SOURCE_NODE, OP_TYPE,
BEFORE_AFTER_IND, USER_NAME.
3231 records processed as of 2010-11-12 10:36:13 (rate 3,delta 3)
3250 records processed as of 2010-11-12 10:37:43 (rate 3,delta 0)
3264 records processed as of 2010-11-12 10:42:07 (rate 2,delta 0)
2010-11-12 10:44:56
INFO
OGG-01021
Command received from GGSCI: REPORT.
***********************************************************************
*
** Run Time Statistics **
*
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : .\dirdat\rt000000
Hdr-Ind
:
E (x45)
Partition :
. (x04)
UndoFlag
:
. (x00)
BeforeAfter:
B (x42)
RecLength :
14 (x000e)
IO Time
: 2010-11-12 10:42:02.000000
IOType
:
3 (x03)
OrigNode
:
255 (xff)
TransInd
:
. (x02)
FormatType :
R (x52)
SyskeyLen :
0 (x00)
Incomplete :
. (x00)
AuditRBA
:
550
AuditPos
: 37889368
Continued :
N (x00)
RecCount
:
1 (x01)
2010-11-12 10:42:02.000000 Delete
Len
14 RBA 677600
Name: OGGS.PRODUCTS
___________________________________________________________________
Reading .\dirdat\rt000000, current RBA 677706, 3277 records
Report at 2010-11-12 10:44:56 (activity since 2010-11-12 10:21:58)
From Table OGGS.CUSTOMERS to OGGT.NON_US_CUSTOMERS:
#
inserts:
79
#
updates:
0
#
deletes:
0
#
discards:
0
From Table OGGS.CUSTOMERS to OGGT.US_CUSTOMERS:
#
inserts:
15
#
updates:
0
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
#
deletes:
#
discards:
From Table OGGS.ORDERS to OGGT.ORDERS:
#
inserts:
#
updates:
#
deletes:
#
discards:
0
0
832
2
0
0
Stored procedure lookup:
attempts:
834
successful:
834
From Table OGGS.ORDER_DETAILS to OGGT.ORDER_DETAILS:
#
inserts:
2155
#
updates:
12
#
deletes:
0
#
discards:
0
Stored procedure SP_TOTAL:
attempts:
2167
successful:
2167
From Table OGGS.PRODUCTS to OGGT.PRODUCTS:
#
inserts:
77
#
updates:
7
#
deletes:
7
#
discards:
0
From Table OGGS.PRODUCTS to OGGT.PRODUCTS_AUDIT:
#
inserts:
77
#
updates:
7
#
deletes:
7
#
discards:
0
BATCHSQL statistics:
Batch operations:
Batches:
Batches executed:
Queues:
Batches in error:
Normal mode operations:
Immediate flush operations:
PK collisions:
UK collisions:
FK collisions:
Thread batch groups:
Commits:
Rollbacks:
Queue flush calls:
3277
17
17
9
0
0
0
2
0
0
0
142
0
7
Ops per batch:
Ops per batch executed:
Ops per queue:
Parallel batch rate:
192.76
192.76
364.11
N/A
***********************************************************************
**
Run Time Warnings
**
***********************************************************************
GGSCI (jjaensch-lap) 5> lag ggs_rep
Sending GETLAG request to REPLICAT GGS_REP ...
Last record lag: 5 seconds.
At EOF, no more records to process.
GGSCI (jjaensch-lap) 6> stats ggs_rep,reset
Sending STATS request to REPLICAT GGS_REP ...
Successfully reset statistics.
GGSCI (jjaensch-lap) 7> stats ggs_rep, reportrate sec
Sending STATS request to REPLICAT GGS_REP ...
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
21
22
Start of Statistics at 2010-11-12 10:55:24.
Replicating from OGGS.CUSTOMERS to OGGT.NON_US_CUSTOMERS:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.04
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.04
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.04
*** Latest statistics since 2010-11-12 10:53:20 ***
No database operations have been performed.
Replicating from OGGS.CUSTOMERS to OGGT.US_CUSTOMERS:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.01
0.00
0.00
0.00
0.01
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.01
0.00
0.00
0.00
0.01
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.01
0.00
0.00
0.00
0.01
*** Latest statistics since 2010-11-12 10:53:20 ***
No database operations have been performed.
Replicating from OGGS.ORDERS to OGGT.ORDERS:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.41
0.00
0.00
0.00
0.42
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.41
0.00
0.00
0.00
0.42
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.41
0.00
0.00
0.00
0.42
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
23
*** Latest statistics since 2010-11-12 10:53:20 ***
No database operations have been performed.
Replicating from OGGS.ORDER_DETAILS to OGGT.ORDER_DETAILS:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
1.07
0.01
0.00
0.00
1.08
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
1.07
0.01
0.00
0.00
1.08
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
1.07
0.01
0.00
0.00
1.08
*** Latest statistics since 2010-11-12 10:53:20 ***
No database operations have been performed.
Replicating from OGGS.PRODUCTS to OGGT.PRODUCTS:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.05
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.05
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.05
*** Latest statistics since 2010-11-12 10:53:20 ***
No database operations have been performed.
Replicating from OGGS.PRODUCTS to OGGT.PRODUCTS_AUDIT:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.05
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
0.04
0.00
0.00
0.00
0.05
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
0.04
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
Total
Total
Total
Total
updates/second:
deletes/second:
discards/second:
operations/second:
0.00
0.00
0.00
0.05
*** Latest statistics since 2010-11-12 10:53:20 ***
No database operations have been performed.
Replicating from OGGS.BIGINSERT_TABLE to OGGT.BIGINSERT_TABLE:
*** Total statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
49.85
0.00
0.00
0.00
49.85
*** Daily statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
Total updates/second:
Total deletes/second:
Total discards/second:
Total operations/second:
49.85
0.00
0.00
0.00
49.85
*** Hourly statistics since 2010-11-12 10:21:58 ***
Total inserts/second:
49.85
Total updates/second:
0.00
Total deletes/second:
0.00
Total discards/second:
0.00
Total operations/second:
49.85
*** Latest statistics since 2010-11-12 10:53:20 ***
Total inserts/second:
807.57
Total updates/second:
0.00
Total deletes/second:
0.00
Total discards/second:
0.00
Total operations/second:
807.57
End of Statistics.
Target Seite
D:\ogg_target> stats ggs_rep, reset
Source Seite
D:\ogg_source\demo_source> sqlplus oggs/source @biginsert.sql
Sofort danach (!) auf der Target Seite
D:\ogg_target> stats ggs_rep, reportrate sec
Target Seite
D:\ogg_target> ggsci  send ggs_rep, report
D:\ogg_target> ggsci  view report ggs_rep, details
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
24
25
Report nach Ausführung der INSERT in Tabelle BIGINSERT_TABLE
.
.
.
***********************************************************************
**
Run Time Warnings
**
***********************************************************************
2010-11-12 10:51:54
INFO
OGG-01021
Command received from GGSCI: GETLAG.
2010-11-12 10:53:20
INFO
OGG-01021
Command received from GGSCI: STATS
reset.
MAP resolved (entry OGGS.BIGINSERT_TABLE):
MAP OGGS.BIGINSERT_TABLE, TARGET OGGT.BIGINSERT_TABLE;
Using following columns in default map by name:
ID, TEXT1, TEXT2, NUM1, NUM2
Using the following key columns for target table OGGT.BIGINSERT_TABLE: ID.
3278 records processed as of 2010-11-12 10:54:54 (rate 1,delta 0)
Switching to next trail file .\dirdat\rt000001 at 2010-11-12 10:55:09 due to EOF,
with current R BA 9999931
Opened trail file .\dirdat\rt000001 at 2010-11-12 10:55:09
2010-11-12 10:55:24
INFO
OGG-01021
Command received from GGSCI: STATS
2010-11-12 11:05:45
INFO
OGG-01021
Command received from GGSCI: STOP.
reportrate sec.
***********************************************************************
*
** Run Time Statistics **
*
***********************************************************************
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : .\dirdat\rt000001
Hdr-Ind
:
E (x45)
Partition :
. (x04)
UndoFlag
:
. (x00)
BeforeAfter:
A (x41)
RecLength :
74 (x004a)
IO Time
: 2010-11-12 10:54:51.000000
IOType
:
5 (x05)
OrigNode
:
255 (xff)
TransInd
:
. (x02)
FormatType :
R (x52)
SyskeyLen :
0 (x00)
Incomplete :
. (x00)
AuditRBA
:
552
AuditPos
: 2317576
Continued :
N (x00)
RecCount
:
1 (x01)
2010-11-12 10:54:51.000000 Insert
Len
74 RBA 8008705
Name: OGGS.BIGINSERT_TABLE
___________________________________________________________________
Reading .\dirdat\rt000001, current RBA 8008878, 103277 records
Report at 2010-11-12 11:05:45 (activity since 2010-11-12 10:21:58)
From Table OGGS.CUSTOMERS to OGGT.NON_US_CUSTOMERS:
#
inserts:
79
#
updates:
0
#
deletes:
0
#
discards:
0
From Table OGGS.CUSTOMERS to OGGT.US_CUSTOMERS:
#
inserts:
15
#
updates:
0
#
deletes:
0
#
discards:
0
From Table OGGS.ORDERS to OGGT.ORDERS:
#
inserts:
832
#
updates:
2
#
deletes:
0
#
discards:
0
Stored procedure lookup:
attempts:
0
successful:
0
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
From Table OGGS.ORDER_DETAILS to OGGT.ORDER_DETAILS:
#
inserts:
2155
#
updates:
12
#
deletes:
0
#
discards:
0
Stored procedure SP_TOTAL:
attempts:
0
successful:
0
From Table OGGS.PRODUCTS to OGGT.PRODUCTS:
#
inserts:
77
#
updates:
7
#
deletes:
7
#
discards:
0
From Table OGGS.PRODUCTS to OGGT.PRODUCTS_AUDIT:
#
inserts:
77
#
updates:
7
#
deletes:
7
#
discards:
0
From Table OGGS.BIGINSERT_TABLE to OGGT.BIGINSERT_TABLE:
#
inserts:
100000
#
updates:
0
#
deletes:
0
#
discards:
0
BATCHSQL statistics:
Batch operations:
Batches:
Batches executed:
Queues:
Batches in error:
Normal mode operations:
Immediate flush operations:
PK collisions:
UK collisions:
FK collisions:
Thread batch groups:
Commits:
Rollbacks:
Queue flush calls:
103277
119
119
111
0
0
0
2
0
0
0
365
0
109
Ops per batch:
Ops per batch executed:
Ops per queue:
Parallel batch rate:
867.87
867.87
930.42
N/A
Last log location read:
FILE:
.\dirdat\rt000001
SEQNO:
1
RBA:
8008878
TIMESTAMP: Not Available
EOF:
YES
READERR:
400
CACHE OBJECT MANAGER statistics
CACHE MANAGER VM USAGE
vm current
=
0
vm anon in use =
0
vm used max
=
0
CACHE CONFIGURATION
cache size
= 512M
buffer min
= 64K
pageout eligible size =
CACHE Transaction Stats
trans active
=
0
non-zero total =
0
CACHE File Caching
disk current
=
disk caching
=
file retrieves =
0
0
0
vm anon queues =
vm file
=
==> CACHE BALANCED
0
0
cache force paging = 881M
buffer highwater
=
4M
4M
max concurrent =
trans total
=
disk total =
file cached =
0
0
0
0
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
26
27
CACHE MANAGEMENT
buffer links =
forced unmaps =
cached out
=
0
0
0
anon gets
cnnbl try
force out
=
=
=
0
0
0
Allocation Request Distribution
< 128B:
0
128B:
0
0
| 512B:
2K:
0
0
|
8K:
32K:
0
0
| 128K:
512K:
0
0
|
2M:
8M:
0
0
| 32M:
128M:
0
0
| 512M:
2G:
0
0
|
8G:
0
0
0
0
0
0
0
0
0
0
0
0
0
Cached Transaction Size Distribution
0:
0
< 4K:
0
4K:
0
0
| 16K:
64K:
0
0
| 256K:
1M:
0
0
|
4M:
16M:
0
0
| 64M:
256M:
0
0
|
1G:
4G:
0
0
| 16G:
64G:
0
0
| 256G:
1T:
0
0
|
4T:
16T:
0
0
| 64T:
256T:
0
0
|1024T:
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
QUEUE Statistics:
num queues
=
cur len
=
q vm current =
q hits
=
15
0
0
0
queue size
0
64K
1 128K
2 256K
3 512K
4
1M
5
2M
6
4M
7
8M
8
16M
9
32M
10
64M
11 128M
12 256M
13 512M
14
1G
curlen
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
q hits
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
default index
max len
vm max
q misses
maxlen
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
=
=
=
=
0
0
0
0
cannibalized
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
================================================================================
CACHE POOL #0
POOL INFO
group: GGS_REP id: p5296_BLOB
trans active =
0
trans concurrent (max) =
0
trans total
=
0
(0 )
flag
= 0x00000000
last error
= (0=<none>)
Allocation Request Distribution
< 128B:
0
128B:
0
0
| 512B:
2K:
0
0
|
8K:
32K:
0
0
| 128K:
512K:
0
0
|
2M:
8M:
0
0
| 32M:
128M:
0
0
| 512M:
2G:
0
0
|
8G:
QUEUE Statistics:
num queues
=
cur len
=
q vm current =
q hits
=
15
0
0
0
queue size
curlen
q hits
0
0
0
0
0
0
0
default index
max len
vm max
q misses
maxlen
=
=
=
=
0
0
0
0
0
0
0
0
0
0
cannibalized
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
64K
128K
256K
512K
1M
2M
4M
8M
16M
32M
64M
128M
256M
512M
1G
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
28
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
================================================================================
CACHE POOL #0
POOL INFO
group: GGS_REP id: p5296_BLOB
trans active =
0
trans concurrent (max) =
0
trans total
=
0
(0 )
flag
= 0x00000000
last error
= (0=<none>)
Allocation Request Distribution
< 128B:
0
128B:
0
0
| 512B:
2K:
0
0
|
8K:
32K:
0
0
| 128K:
512K:
0
0
|
2M:
8M:
0
0
| 32M:
128M:
0
0
| 512M:
2G:
0
0
|
8G:
0
0
0
0
0
0
0
0
0
0
0
0
0
GGSCI (jjaensch-lap) 2>
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
29
3.4. Demo Cleanup
3.4.1. Source Seite
GGSCI (jjaensch-lap) 2> info all
Program
Status
Group
Lag
Time Since Chkpt
MANAGER
EXTRACT
EXTRACT
RUNNING
STOPPED
STOPPED
GGS_EXT
GGS_PUMP
00:00:00
00:00:00
121:01:29
121:01:29
GGSCI (jjaensch-lap) 5> delete extract ggs_ext
Deleted EXTRACT GGS_EXT.
GGSCI (jjaensch-lap) 6> delete extract ggs_pump
Deleted EXTRACT GGS_PUMP.
GGSCI (jjaensch-lap) 7> info all
Program
Status
MANAGER
RUNNING
Group
Lag
Time Since Chkpt
GGSCI (jjaensch-lap) 8>
3.4.2. Target Seite
GGSCI (jjaensch-lap) 3> info all
Program
Status
Group
Lag
Time Since Chkpt
MANAGER
REPLICAT
RUNNING
STOPPED
GGS_REP
00:00:00
121:01:40
GGSCI (jjaensch-lap) 6> delete replicat ggs_rep
ERROR: Could not delete DB checkpoint for REPLICAT GGS_REP
(Database login required to delete database checkpoint).
GGSCI (jjaensch-lap) 9> dblogin userid oggt,password target
Successfully logged into database.
GGSCI (jjaensch-lap) 11> delete replicat ggs_rep
Deleted REPLICAT GGS_REP.
GGSCI (jjaensch-lap) 12> info all
Program
Status
MANAGER
RUNNING
Group
Lag
Time Since Chkpt
GGSCI (jjaensch-lap) 13>
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
30
3.4.3. Source Tabelleninhalte löschen
D:\ogg_source\demo_source> sqlplus oggs/source @src_clean.sql
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Nov 17 12:50:55 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
NLS Date Format:
---------------DD-MON-RR
GLOBAL_NAME
-------------------------------------------------------------------------------ORACLEJ.DE.ORACLE.COM
Table
Table
Table
Table
Table
truncated.
truncated.
truncated.
truncated.
truncated.
SQL> column object_name format a25
SQL> select object_name, object_type from user_objects order by 2,1;
OBJECT_NAME
------------------------SYS_C0015256
SYS_C0015262
SYS_C0015264
SYS_C0015267
SYS_C0015269
BIGINSERT
BIGINSERT_SEQ
BIGINSERT_TABLE
CUSTOMERS
ORDERS
ORDER_DETAILS
PRODUCTS
OBJECT_TYPE
------------------INDEX
INDEX
INDEX
INDEX
INDEX
PROCEDURE
SEQUENCE
TABLE
TABLE
TABLE
TABLE
TABLE
12 rows selected.
SQL>
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
31
3.4.4. Target Tabelleninhalte löschen
D:\ogg_target\demo_target> sqlplus oggt/target @target_clean.sql
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Nov 17 12:53:47 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
NLS Date Format:
---------------DD-MON-RR
GLOBAL_NAME
-------------------------------------------------------------------------------ORACLEJ.DE.ORACLE.COM
Table
Table
Table
Table
Table
Table
Table
truncated.
truncated.
truncated.
truncated.
truncated.
truncated.
truncated.
SQL> column object_name format a25
SQL> select object_name, object_type from user_objects order by 2,1;
OBJECT_NAME
------------------------SYS_C0015272
SYS_C0015275
SYS_C0015281
SYS_C0015283
SYS_C0015287
SYS_C0015291
SYS_C0015293
SYS_C0015305
SP_TOTAL
BIGINSERT_TABLE
GGSCHKPT
NON_US_CUSTOMERS
ORDERS
ORDER_DETAILS
PRODUCTS
PRODUCTS_AUDIT
SHIPPERS
US_CUSTOMERS
OBJECT_TYPE
------------------------INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
PROCEDURE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
18 rows selected.
SQL>
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011
32
3.5. Demo Wiederholung
Nach dem Cleanup der Source und der Target Seite ist eine Wiederholung der Demo mit sehr wenig
Aufwand möglich, weil einige der Voraussetzungen noch existieren:
Datenbank:
Die Nutzer „oggs“ und „oggt“ und deren Objekte existieren noch, und es kann sofort mit dem Füllen der
Tabellen begonnen werden
GoldenGate:
Beide Installationsumgebungen sind weiter nutzbar, die beiden Demo-Verzeichnisse existieren noch und die
Parameterdateien sind auch noch vorhanden, nur die beiden EXTRACT und der REPLICAT Prozeß müssen
neu definiert werden. Der folgende Fehler bezüglich Checkpointtable kann ignoriert werden. Er tritt auf, weil
diese Tabelle beim Cleanup nicht mit gelöscht wird.
ERROR: SQL operation failed: Creating checkpoint table OGGT.GGSCHKPT,
SQL <CREATE TABLE "OGGT"."GGSCHKPT" (group_name VARCHAR2(8) NOT NULL,
group_key NUMBER(19) NOT NULL, seqno NUMBER(10), rba NUMBER(19) NOT NULL,
audit_ts VARCHAR2(29), create_ts DATE NOT NULL, last_
> SQL Error 955: ORA-00955: name is already used by an existing object.
Workaround: „DROP TABLE ggschkpt“ auf Target Seite
Das Source-Definition File ist auf der Target Seite ebenfalls noch vorhanden und brauch deshalb auch nicht
nochmal erzeugt bzw. auf die Target Seite übertragen werden.
Achtung: Trail-Files, Report-Files und Checkpoint-Files
Bitte unbedingt alle Files in den Verzeichnissen „ogg_source\dirdat“ und „ogg_target\dirdat“ löschen!
Sonst kommt es zu diesen Meldungen und der REPLICAT Prozeß verweigert die Arbeit:
2010-11-17 13:45:53 WARNING OGG-01438 Checkpoint marked as from graceful shutdown, but
records found after checkpoint in trail d:\ogg_target\dirdat\rt.
Expected EOF Seqno 0, RBA 0. Found Seqno 1, RBA 8008878.
2010-11-17 13:45:53 INFO OGG-01026 Rolling over remote filed: \ogg_target\dirdat\rt000002
Die Report- und Checkpoint-Files beider Seiten bitte auch löschen:
Report-Files in den Verzeichnissen:
„ogg_source\dirrep“ und „ogg_target\dirrep“
Checkpoint-Files in den Verzeichnissen: „ogg_source\dirchk“ und „ogg_target\dirchk“
Joachim Jaensch – Principal Sales Consultant - ORACLE Deutschland B.V. & Co KG – BU ST-PCM - 2011