Golden Gate – Multitenant (Non CDB to PDB)

 

-- References:
http://www.oracle.com/technetwork/database/multitenant/
learn-more/ogg-multitenant-2408073.pdf

https://docs.oracle.com/goldengate/1212/gg-winux/GIORA/
config_containerdb.htm#GIORA987

Case:
Replication on Oracle Database 12c with databases on the same server.
Source: Non CDB
Target: CDB

Source: SID: DB12C
target: SID: CDB - Pluggable Database: PDB3

GOLDEN_GATE_HOME: /oracle/product/GG/12c

Server: lnx01 - 10.130.1.36

Both must be on ARCHIVE mode
-------------------------------------------------------------------------------
1.) Preparing Source/Target

- Logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

- streams_pool_size, at least 1g 
- ENABLE_GOLDENGATE_REPLICATION, parameter set to TRUE
- Create Tablespace /User on both sides, on target you need create inside PDB

- Source:

create tablespace ts_ggate datafile '/data03/oradata/DB12C/datafile/ts_ggate_01.dbf' size 8m autoextend on next 8m maxsize 8g;
create user ggate identified by ggate default tablespace ts_ggate;
grant connect , resource to ggate;
grant select any dictionary, select any table to ggate;
grant create table to ggate;
grant flashback any table to ggate;
grant execute on dbms_flashback to ggate;
grant execute on utl_file to ggate;
grant dba to ggate;

exec dbms_goldengate_auth.grant_admin_privilege('ggate');

- Target:

create tablespace ts_ggate datafile '/data03/CDB/ts_ggate_01.dbf' size 8m autoextend on next 8m maxsize 8g;
create user c##ggate identified by ggate container=all;
alter user c##ggate default tablespace TS_GGATE container=current;
grant connect , resource to c##ggate;
grant select any dictionary, select any table to c##ggate;
grant create table to c##ggate;
grant flashback any table to c##ggate;
grant execute on dbms_flashback to c##ggate;
grant execute on utl_file to c##ggate;
grant dba to c##ggate;

exec dbms_goldengate_auth.grant_admin_privilege('C##GGATE',container=>'all');

- Configure UNDO:

UNDO_MANAGEMENT=AUTO
UNDO_RETENTION=86400 
UNDO_RETENTION can be adjusted upward in high-volume environments.

- Executing: sequence.sql from GG_HOME on both sides

Source:

sqlplus / as sysdba
@/oracle/product/GG/12c/sequence.sql
ggate

Target:

sqlplus / as sysdba
alter session set container=pdb3;
@/oracle/product/GG/12c/sequence.sql
c##ggate

2.) Test environment:

On Source:
create user app identified by app default tablespace users;
grant connect , resource to app;
alter user app quota unlimited on users;

create table app.t1 
(num number, 
 text varchar2(100),
 dt date);
 
 create table app.t2
 (num number, 
 text varchar2(100),
 dt date);
 
 create table app.t3
 (num number, 
 text varchar2(100),
 dt date);
 
ALTER TABLE app.t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (num);
ALTER TABLE app.t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (num);
ALTER TABLE app.t3 ADD CONSTRAINT pk_t3 PRIMARY KEY (num);

create sequence app.seq1 nocache order;
create sequence app.seq2 nocache order;
create sequence app.seq3 nocache order;

declare 
i number;
begin
for i in 1..100 loop
 insert into app.t1 values (app.seq1.nextval,'TEST1',sysdate);
 insert into app.t2 values (app.seq2.nextval,'TEST2',sysdate);
 insert into app.t3 values (app.seq3.nextval,'TEST3',sysdate);
end loop
commit;
end;
/

exec dbms_stats.gather_schema_stats('APP');

-- Copying the structure for PDB3;
create tablespace users 
datafile '/data03/CDB/PDB3/ts_users_01.dbf' 
size 1m autoextend on next 1m maxsize 16g;

create user app identified by app default tablespace users;
grant connect , resource to app;
alter user app quota unlimited on users;

create table app.t1 
(num number, 
 text varchar2(100),
 dt date);
 
 create table app.t2
 (num number, 
 text varchar2(100),
 dt date);
 
 create table app.t3
 (num number, 
 text varchar2(100),
 dt date);
 
ALTER TABLE app.t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (num);
ALTER TABLE app.t2 ADD CONSTRAINT pk_t2 PRIMARY KEY (num);
ALTER TABLE app.t3 ADD CONSTRAINT pk_t3 PRIMARY KEY (num);

create sequence app.seq1 nocache order;
create sequence app.seq2 nocache order;
create sequence app.seq3 nocache order;

- Check TNS for the PDB, on my case PDB3 on target

- On the Multitenant, we need grant Privileges to root user;
alter session set container=pdb3;

grant connect , resource to c##ggate;
grant select any dictionary, select any table to c##ggate;
grant create table to c##ggate;
grant flashback any table to c##ggate;
grant execute on dbms_flashback to c##ggate;
grant execute on utl_file to c##ggate;
grant dba to c##ggate;

[oracle@lnx01 admin]$ tnsping pdb3

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 22-APR-2016 05:47:24

Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB3)))
OK (10 msec)
[oracle@lnx01 admin]$

[oracle@lnx01 admin]$ sqlplus c##ggate/ggate@pdb3

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 22 06:20:42 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Fri Apr 22 2016 05:31:43 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

3.) Configuring GOLDEN GATE

- GLOBALS on TARGET

cd %GG_HOME% 
# ggsci

GGSCI (lnx01) 1> sh echo $ORACLE_SID

CDB

DBLOGIN USERID c##ggate@pdb3, PASSWORD ggate

ADD CHECKPOINTTABLE c##ggate.chktable
EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE c##ggate.chktable

4.) First Load:

- Source:

#GGSCI

DBLOGIN USERID ggate, PASSWORD ggate

ADD EXTRACT extfirst, SOURCEISTABLE

EDIT PARAMS extfirst

EXTRACT extfirst
SETENV (ORACLE_HOME="/oracle/product/12.1.0.2/db_1")
SETENV (ORACLE_SID="db12c")
USERID ggate, PASSWORD ggate
RMTHOST 10.130.1.36, MGRPORT 7809
RMTTASK replicat, GROUP repfirst
TABLE app.*;

- Target

#GGSCI

DBLOGIN USERID c##ggate@pdb3, PASSWORD ggate
ADD REPLICAT repfirst, SPECIALRUN

EDIT PARAMS repfirst

REPLICAT repfirst
SETENV (ORACLE_HOME="/oracle/product/12.1.0.2/db_1")
SETENV (ORACLE_SID="CDB")
USERID c##ggate@pdb3, PASSWORD ggate
ASSUMETARGETDEFS
WILDCARDRESOLVE IMMEDIATE
MAP app.*, TARGET app.*;

- Source:
#GGSCI
ADD SCHEMATRANDATA APP ALLCOLS

START EXTRACT extfirst

- Target:
info replicat repfirst

- Checking Target:
sqlplus / as sysdba
alter session set container=pdb3
exec dbms_stats.gather_schema_stats('APP');

select owner, table_name , num_rows
from dba_tables
where owner='APP';

OWNER TABLE_NAME NUM_ROWS
---------- -------------------- --------------------
APP T3 100
APP T2 100
APP T1 100

5.) OnGoing Manager

SOURCE
#GGSCI

DBLOGIN USERID ggate, PASSWORD ggate

edit params mgr

PURGEOLDEXTRACTS /oracle/product/GG/12c/dirdat/ex, USECHECKPOINTS
Autostart Replicat R*
AUTORESTART Replicat *, WaitMinutes 1, Retries 3
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3

stop manager!
exit
GGSCI

DBLOGIN USERID ggate, PASSWORD ggate

==> Note, with this configuration all processes starting with
==> E* and R* will be started automaticaly, so REPFIRST and EXTFIRST
==> also, is better delete these process before start

GGSCI (lnx01 as ggate@db12c) 4> delete extfirst
Deleted EXTRACT EXTFIRST.

GGSCI (lnx01 as c##ggate@CDB/PDB3) 2> delete repfirst
Deleted REPLICAT REPFIRST.

start manager

6.) OnGoing Extract - Source

mkdir -p /oracle/product/GG/12c/dirdat/DB12c

#GGSCI
edit params extdb12c

EXTRACT extdb12c
SETENV (ORACLE_HOME="/oracle/product/12.1.0.2/db_1")
SETENV (ORACLE_SID="db12c")
USERID ggate, PASSWORD ggate
LOGALLSUPCOLS
UPDATERECORDFORMAT COMPACT
TRANLOGOPTIONS EXCLUDEUSER ggate
DDL INCLUDE MAPPED OBJNAME APP.*
DDLOPTIONS ADDTRANDATA, GETREPLICATES, GETAPPLOPS
BR BROFF
EOFDELAY 1
FLUSHSECS 1
ENCRYPTTRAIL AES192
EXTTRAIL /oracle/product/GG/12c/dirdat/DB12c/ex
TABLE APP.*;
SEQUENCE APP.*;

#GGSCI
add trandata APP.*

7.) OnGoing Pump - Source

#GGSCI
edit params pmpdb12c

EXTRACT pmpdb12c
SETENV (ORACLE_HOME="/oracle/product/12.1.0.2/db_1")
SETENV (ORACLE_SID="db12c")
USERID ggate, PASSWORD ggate
RMTHOST 10.130.1.36, MGRPORT 7809
-- Specify the remote trail on the secondary system:
RMTTRAIL /oracle/product/GG/12c/dirdat/DB12c/rm
PASSTHRU
-- Specify tables to be captured:
TABLE APP.*;
SEQUENCE APP.*;

8.) OnGoing Replicat - Target
#GGSCI

DBLOGIN USERID c##ggate@pdb3, PASSWORD ggate

edit params repdb12c

REPLICAT repdb12c
SETENV (ORACLE_HOME="/oracle/product/12.1.0.2/db_1")
SETENV (ORACLE_SID="CDB")
USERID c##ggate@pdb3, PASSWORD ggate
ASSUMETARGETDEFS
DISCARDFILE /oracle/product/GG/12c/dirdat/repdb12c.dsc, PURGE
REPORTCOUNT EVERY 1 MINUTES, RATE
DDLOPTIONS UPDATEMETADATA
DDLERROR DEFAULT IGNORE RETRYOP
EOFDELAY 1
MAP APP.*, TARGET APP.*;

9.) Registering Process
#GGSCI
- Source:
register extract extdb12c database
add extract extdb12c, integrated tranlog, begin now 
add exttrail /oracle/product/GG/12c/dirdat/DB12c/ex, 
extract extdb12c, megabytes 10

add extract pmpdb12c, exttrailsource /oracle/product/GG/12c/dirdat/DB12c/ex
add rmttrail /oracle/product/GG/12c/dirdat/DB12c/rm, extract pmpdb12c, 
megabytes 10

- Target:
#GGSCI
DBLOGIN USERID c##ggate@pdb3, PASSWORD ggate

Add Replicat repdb12c exttrail /oracle/product/GG/12c/dirdat/DB12c/rm, checkpointtable c##ggate.chktable

10.) Starting

- Source

GGSCI (lnx01 as ggate@db12c) 26> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT STOPPED EXTDB12C 00:00:00 00:04:29
EXTRACT STOPPED PMPDB12C 00:00:00 00:04:07
REPLICAT STOPPED REPDB12C 00:00:00 00:01:10

CREATE WALLET
ADD MASTERKEY

start EXTDB12C
start PMPDB12C

- Target:
DBLOGIN USERID c##ggate@pdb3, PASSWORD ggate

start REPDB12C

11.) Testing:

- Source:

sqlplus / as sysdba

declare 
i number;
begin
for i in 1..100 loop
 insert into app.t1 values (app.seq1.nextval,'TEST1',sysdate);
 insert into app.t2 values (app.seq2.nextval,'TEST2',sysdate);
 insert into app.t3 values (app.seq3.nextval,'TEST3',sysdate);
 commit;
end loop
commit;
end;
/

exec dbms_stats.gather_schema_stats('APP');

select owner, table_name , num_rows
from dba_tables
where owner='APP';

OWNER TABLE_NAME NUM_ROWS
-------------------- -------------------- --------------------
APP T1 200
APP T2 200
APP T3 200

- Target:

sqlplus / as sysdba
alter session set container=pdb3;
exec dbms_stats.gather_schema_stats('APP');

select owner, table_name , num_rows
from dba_tables
where owner='APP';

OWNER TABLE_NAME NUM_ROWS
-------------------- -------------------- --------------------
APP T1 200
APP T2 200
APP T3 200
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s