Oracle GoldenGate 12c 配置Oracle Database 12c Multitenant database

本文主要是介绍Oracle GoldenGate 12c 配置Oracle Database 12c Multitenant database，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

--源端数据库配置

[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

REPLICAT rep1
--SETENV (ORACLE_SID='zhongwc')
DBOPTIONS INTEGRATEDPARAMS(parallelism 6)
USERID C##GGADMIN@zwc5, PASSWORD ggadmin
ASSUMETARGETDEFS
--SOURCECATALOG zwc5
MAP ZHONGWC1.GGTEST.*, TARGET ZWC5.GGTEST.*;

--测试

--源端启动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.