--源端數據庫配置
[oracle@db12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 8 14:24:15 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> create user c##ggadmin identified by ggadmin;
User created.
SQL> grant dba to c##ggadmin;
Grant succeeded.
SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'all');
PL/SQL procedure successfully completed.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ZHONGWC1 READ WRITE NO
4 ZHONGWC2 READ WRITE NO
SQL> alter session set container=zhongwc1;
Session altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data;
Database altered.
SQL> create user ggtest identified by ggtest;
User created.
SQL> grant dba to ggtest;
Grant succeeded.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ZHONGWC1 READ WRITE NO
SQL> conn ggtest/ggtest@zhongwc1
Connected.
SQL> create table tab01(sid number(8),sname varchar2(20));
Table created.
--源端ogg配置
[oracle@db12c ggs]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI (db12c) 1> dblogin useridalias ggzhongwc1 --連到zhongwc1 PDB
Successfully logged into database ZHONGWC1.
GGSCI (db12c) 2> add schematrandata ggtest allcols
2014-04-08 14:37:57 INFO OGG-01788 SCHEMATRANDATA has been added on schema ggtest.
2014-04-08 14:37:57 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema ggtest.
2014-04-08 14:37:57 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema ggtest.
GGSCI (db12c) 3> capture tabledef ggtest.tab01
Default catalog name ZHONGWC1 will be used for table specification ggtest.tab01.
Table definitions for ZHONGWC1.GGTEST.TAB01:
SID NUMBER (8)
SNAME VARCHAR (20)
GGSCI (db12c) 4> dblogin useridalias ggroot --連接CDB$ROOT
Successfully logged into database CDB$ROOT.
GGSCI (db12c) 5> register extract ext1 database container (zhongwc1) --Register the Integrated Extract
Extract EXT1 successfully registered with database at SCN 2014272.
--Add the Extract and Data Pump process groups
GGSCI (db12c) 7> add extract ext1 integrated tranlog, begin now
EXTRACT added.
GGSCI (db12c) 8> add exttrail ./dirdata/lt extract ext1
EXTTRAIL added.
GGSCI (db12c) 9> add extract extdp1 exttrailsource ./dirdat/lt, begin now
EXTRACT added.
GGSCI (db12c) 10> add rmttrail ./dirdat/rt extract extdp1
RMTTRAIL added.
GGSCI (db12c) 18> view params ext1
EXTRACT ext1
SETENV (ORACLE_SID='zwc')
USERIDALIAS ggroot
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
EXTTRAIL ./dirdat/lt
SOURCECATALOG zhongwc1
TABLE GGTEST.*;
GGSCI (db12c) 20> view params extdp1
EXTRACT extdp1
SETENV (ORACLE_SID='zwc')
USERIDALIAS ggroot
RMTHOST test12c, MGRPORT 7809
RMTTRAIL ./dirdat/rt
SOURCECATALOG zhongwc1
TABLE GGTEST.*;
******************************************************************************************************
PDBs
SOURCECATALOG sales
TABLE sh.*;
TABLE oe.*;
SOURCECATALOG hr
TABLE hr.*
Basic parameters for Extract where the source database is the mining database and is a regular database
EXTRACT financep
USERIDALIAS tiger1
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED
ENCRYPTTRAIL AES192
EXTTRAIL /ggs/dirdat/lt
SEQUENCE hr.employees_seq;
TABLE hr.*;
Basic parameters for Extract where the source database is the mining database and is a multitenant container database
EXTRACT financep
USERIDALIAS tiger1
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED SOURCECATALOG pdb1 INCLUDE MAPPED SOURCECATALOG pdb2
ENCRYPTTRAIL AES192
EXTTRAIL /ggs/dirdat/lt
TABLE test.ogg.tab1;
SOURCECATALOG pdb1
SEQUENCE hr.employees_seq;
TABLE hr.*;
SOURCECATALOG pdb2
TABLE sales.*;
TABLE acct.*;
Basic parameters for Extract where the mining database is a downstream database and is a regular database
EXTRACT financep
USERIDALIAS tiger1
TRANLOGOPTIONS MININGUSERALIAS tiger2
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 164, &
DOWNSTREAM_REAL_TIME_MINE y)
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED
ENCRYPTTRAIL AES192
EXTTRAIL /ggs/dirdat/lt
SEQUENCE hr.employees_seq;
TABLE hr.*;
Basic parameters for the primary Extract where the mining database is a downstream database and is a multitenant container database
EXTRACT financep
USERIDALIAS tiger1
TRANLOGOPTIONS MININGUSERALIAS tiger2
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 164, &
DOWNSTREAM_REAL_TIME_MINE y)
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
DDL INCLUDE MAPPED SOURCECATALOG pdb1 INCLUDE MAPPED SOURCECATALOG pdb2
ENCRYPTTRAIL AES192EXTTRAIL /ggs/dirdat/lt
TABLE test.ogg.tab1;
SOURCECATALOG pdb1
SEQUENCE hr.employees_seq;
TABLE hr.*;
SOURCECATALOG pdb2
TABLE sales.*;
TABLE acct.*;
******************************************************************************************************
--目標端ogg配置
Add the Replicat process group connected to the target PDB zwc5
GGSCI (test12c.localdomain) 1> dblogin userid c##ggadmin@zwc5, password ggadmin
Successfully logged into database ZWC5.
GGSCI (test12c.localdomain) 3> add replicat rep1 integrated exttrail ./dirdat/rt
REPLICAT (Integrated) added.
GGSCI (test12c.localdomain) 5> view params rep1
--測試
--源端啟動Extract,Data Pump
GGSCI (db12c) 29> start extract ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (db12c) 30> start extract extdp1
EXTRACT EXTDP1 is already running.
GGSCI (db12c) 31> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:06 00:00:07
EXTRACT RUNNING EXTDP1 00:00:00 00:00:09
--目標端啟動Replicat
GGSCI (test12c.localdomain) 9> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (test12c.localdomain) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:04
--源端插入數據
[oracle@db12c ~]$ sqlplus ggtest/ggtest@zhongwc1
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 8 16:15:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Tue Apr 08 2014 16:00:22 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> insert into tab01 values(8,'11gR2 RAC OCE');
1 row created.
SQL> commit;
Commit complete.
SQL> update tab01 set sid=18 where sid=8;
1 row updated.
SQL> commit;
Commit complete.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ZHONGWC1 READ WRITE NO
SQL> show user
USER is "GGTEST"
--目標端logdump查看
[oracle@test12c ggs]$ logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
Logdump 1 >ghdr on
Logdump 2 >detail on
Logdump 3 >detail data
Logdump 4 >usertoken on
Logdump 5 >open /u01/app/oracle/ggs/dirdat/rt000000
Current LogTrail is /u01/app/oracle/ggs/dirdat/rt000000
Logdump 6 >n
2014/04/08 16:01:27.656.085 FileHeader Len 1427 RBA 0
Name: *FileHeader*
3000 0327 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0..'0...GG..TL..1...
0004 3200 0004 2000 0000 3300 0008 02f2 1cb6 d8da | ..2... ...3.........
7695 3400 0026 0024 7572 693a 6462 3132 633a 3a75 | v.4..&.$uri:db12c::u
3031 3a61 7070 3a6f 7261 636c 653a 6767 733a 4558 | 01:app:oracle:ggs:EX
5444 5031 3500 0028 3500 0024 0022 7572 693a 6462 | TDP15..(5..$."uri:db
3132 633a 3a75 3031 3a61 7070 3a6f 7261 636c 653a | 12c::u01:app:oracle:
6767 733a 4558 5431 3600 0013 0011 2e2f 6469 7264 | ggs:EXT16....../dird
Logdump 7 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x00)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 0 (x0000) IO Time : 2014/04/08 16:01:30.673.111
IOType : 151 (x97) OrigNode : 0 (x00)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 0 (x00)
2014/04/08 16:01:30.673.111 RestartOK Len 0 RBA 1435
Name:
After Image: Partition 0 G s
Logdump 8 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 28 (x001c) IO Time : 2014/04/08 16:04:18.445.081
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 30 AuditPos : 18933828
Continued : N (x00) RecCount : 1 (x01)
2014/04/08 16:04:18.445.081 Insert Len 28 RBA 1494
Name: ZHONGWC1.GGTEST.TAB01
After Image: Partition 4 G s
0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | ....................
0006 4747 5445 5354 | ..GGTEST
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0001 | ..........
Column 1 (x0001), Len 10 (x000a)
0000 0006 4747 5445 5354 | ....GGTEST
Logdump 9 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 28 (x001c) IO Time : 2014/04/08 16:11:12.516.573
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 30 AuditPos : 19556368
Continued : N (x00) RecCount : 1 (x01)
2014/04/08 16:11:12.516.573 Insert Len 28 RBA 1645
Name: ZHONGWC1.GGTEST.TAB01
After Image: Partition 4 G s
0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | ....................
0006 3130 674f 4350 | ..10gOCP
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0001 | ..........
Column 1 (x0001), Len 10 (x000a)
0000 0006 3130 674f 4350 | ....10gOCP
Logdump 10 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 32 (x0020) IO Time : 2014/04/08 16:14:16.547.033
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 30 AuditPos : 20605968
Continued : N (x00) RecCount : 1 (x01)
2014/04/08 16:14:16.547.033 Insert Len 32 RBA 1797
Name: ZHONGWC1.GGTEST.TAB01
After Image: Partition 4 G s
0000 000a 0000 0000 0000 0000 1639 0001 000e 0000 | .............9......
000a 3131 6752 4143 204f 4345 | ..11gRAC OCE
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 1639 | .........9
Column 1 (x0001), Len 14 (x000e)
0000 000a 3131 6752 4143 204f 4345 | ....11gRAC OCE
Logdump 11 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 35 (x0023) IO Time : 2014/04/08 16:15:57.565.282
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 30 AuditPos : 20854800
Continued : N (x00) RecCount : 1 (x01)
2014/04/08 16:15:57.565.282 Insert Len 35 RBA 1953
Name: ZHONGWC1.GGTEST.TAB01
After Image: Partition 4 G s
0000 000a 0000 0000 0000 0000 0008 0001 0011 0000 | ....................
000d 3131 6752 3220 5241 4320 4f43 45 | ..11gR2 RAC OCE
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0008 | ..........
Column 1 (x0001), Len 17 (x0011)
0000 000d 3131 6752 3220 5241 4320 4f43 45 | ....11gR2 RAC OCE
Logdump 12 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 74 (x004a) IO Time : 2014/04/08 16:16:41.573.430
IOType : 135 (x87) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 30 AuditPos : 20905488
Continued : N (x00) RecCount : 1 (x01)
2014/04/08 16:16:41.573.430 GGSUnifiedPKUpdate Len 74 RBA 2112
Name: ZHONGWC1.GGTEST.TAB01
After Image: Partition 4 G s
0000 0023 0000 000a 0000 0000 0000 0000 0008 0001 | ...#................
0011 0000 000d 3131 6752 3220 5241 4320 4f43 4500 | ......11gR2 RAC OCE.
0000 0a00 0000 0000 0000 0000 1200 0100 1100 0000 | ....................
0d31 3167 5232 2052 4143 204f 4345 | .11gR2 RAC OCE
Before Image Len 39 (x00000027)
BeforeColumnLen 35 (x00000023)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0008 | ..........
Column 1 (x0001), Len 17 (x0011)
0000 000d 3131 6752 3220 5241 4320 4f43 45 | ....11gR2 RAC OCE
After Image Len 35 (x00000023)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0012 | ..........
Column 1 (x0001), Len 17 (x0011)
0000 000d 3131 6752 3220 5241 4320 4f43 45 | ....11gR2 RAC OCE
Logdump 13 >n
GGSCI (test12c.localdomain) 58> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2014-04-08 16:49:24.
Integrated Replicat Statistics:
Total transactions 5.00
Redirected 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from ZHONGWC1.GGTEST.TAB01 to ZWC5.GGTEST.TAB01:
*** Total statistics since 2014-04-08 16:13:54 ***
Total inserts 4.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
*** Daily statistics since 2014-04-08 16:13:54 ***
Total inserts 4.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
*** Hourly statistics since 2014-04-08 16:13:54 ***
Total inserts 4.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
*** Latest statistics since 2014-04-08 16:13:54 ***
Total inserts 4.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 5.00
End of Statistics.
