OracleGoldenGate12c configure OracleDatabase12cMultitenantdatabase

-- Source Database Configuration

[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,201 3, Oracle. All rights reserved.

Connected:
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.

-- Source ogg Configuration

[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,201 3, Oracle and/or its affiliates. All rights reserved.

GGSCI (db12c) 1> dblogin useridalias ggzhongwc1-- Connect to zhongwc1 PDB
Successfully logged into database ZHONGWC1.

GGSCI (db12c) 2> add schematrandata ggtest allcols

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-- Connect 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 configuration on the target end

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 .*;

-- Test

-- Start Extract and Data Pump on the source end

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

-- Start Replicat on the target end

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

-- Insert data to the source end

[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,201 3, Oracle. All rights reserved.

Last Successful login time: Tue Apr 08 2014 16:00:22 +

Connected:
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"

-- View logdump on the target end

[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,201 3, 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

16:01:27. 656.085 FileHeader Len 1427 RBA 0
Name: * FileHeader *
3000 0327 3000 0008 4747 0d0a 544c 0a0d 3100 | 0... '0... GG... TL... 1...
0004 3200 0004 2000 0000 3300 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 693a 7572 | 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: 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)

16:01:30. 673.111 RestartOK Len 0 RBA 1435
Name:
After Image: Partition 0G s

Logdump 8> n
___________________________________________________________________
Hdr-Ind: E (x45) Partition:. (x04)
UndoFlag:. (x00) BeforeAfter: A (x41)
RecLength: 28 (x001c) IO Time: 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)

16:04:18. 445.081 Insert Len 28 RBA 1494
Name: ZHONGWC1.GGTEST. TAB01
After Image: Partition 4G 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 | ..........
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: 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)

16:11:12. 516.573 Insert Len 28 RBA 1645
Name: ZHONGWC1.GGTEST. TAB01
After Image: Partition 4G s
0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | ....................
0006 3130 674f 4350 |... 10 gOCP
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 | ..........
Column 1 (x0001), Len 10 (x000a)
0000 0006 3130 674f 4350 |... 10 gOCP

Logdump 10> n
___________________________________________________________________
Hdr-Ind: E (x45) Partition:. (x04)
UndoFlag:. (x00) BeforeAfter: A (x41)
RecLength: 32 (x0020) IO Time: 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)

16:14:16. 547.033 Insert Len 32 RBA 1797
Name: ZHONGWC1.GGTEST. TAB01
After Image: Partition 4G s
0000 000a 0000 0000 0000 0000 1639 0001 000e 0000 | ......
000a 3131 6752 4143 204f 4345 | .. 11 gRAC OCE
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 | ......
Column 1 (x0001), Len 14 (x000e)
0000 000a 3131 6752 4143 204f 4345 |... 11 gRAC OCE

Logdump 11> n
___________________________________________________________________
Hdr-Ind: E (x45) Partition:. (x04)
UndoFlag:. (x00) BeforeAfter: A (x41)
RecLength: 35 (x0023) IO Time: 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)

16:15:57. 565.282 Insert Len 35 RBA 1953
Name: ZHONGWC1.GGTEST. TAB01
After Image: Partition 4G s
0000 000a 0000 0000 0000 0000 0008 0001 0011 | ....................
000d 3131 6752 3220 5241 4f43 45 |... 11gR2 RAC OCE
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 | ..........
Column 1 (x0001), Len 17 (x0011)
0000 000d 3131 6752 3220 5241 4f43 45 |... 11gR2 RAC OCE

Logdump 12> n
___________________________________________________________________
Hdr-Ind: E (x45) Partition:. (x04)
UndoFlag:. (x00) BeforeAfter: A (x41)
RecLength: 74 (x004a) IO Time: 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)

16:16:41. 573.430 GGSUnifiedPKUpdate Len 74 RBA 2112
Name: ZHONGWC1.GGTEST. TAB01
After Image: Partition 4G s
0000 0023 0000 000a 0000 0000 0000 0000 0008 | ...#................
0011 0000 000d 3131 6752 3220 5241 4320 4f43 4500 | ...... 11gR2 rac oce.
0000 0a00 0000 0000 0000 0000 1200 0100 1100 | ....................
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 | ..........
Column 1 (x0001), Len 17 (x0011)
0000 000d 3131 6752 3220 5241 4f43 45 |... 11gR2 RAC OCE

After Image Len 35 (x00000023)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 | ..........
Column 1 (x0001), Len 17 (x0011)
0000 000d 3131 6752 3220 5241 4f43 45 |... 11gR2 RAC OCE

Logdump 13> n

GGSCI (test12c. localdomain) 58> stats rep1

Sending STATS request to REPLICAT REP1...

Start of Statistics at 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 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 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 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 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.