Oracle GoldenGate on Oracle 12c cdb/pdb

Oracle GoldenGate on Oracle 12c cdb/pdb

1. how do I know if a 12c DB is container DB or non-container DB?

(1) container DB
SQL> select cdb from v$database;
CDB
---
YES

(2) non container DB
SQL> select cdb from v$database;
CDB
---
NO

2. how to list all the PDBs?

SQL> select name, open_mode, restricted from v$pdbs;
NAME OPEN_MODE            RES
------------------------------ ----------
PDB$SEED READ ONLY       NO  <===== this is a seed PDB
PDBORCL MOUNTED           NO  <===== this is a PDB at mount status
PDB1 READ WRITE             NO  <===== this is a PDB at read/write open status

pdb should be in no-restricted mode, or registering extract under ggsci may fail with error:

ERROR: The global name of database container ..... could not be retrieved.
Ensure the container is not in restricted mode.

3. how to open a PDB?

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> alter session set container=PDBORCL;
Session altered.

SQL> show con_name <==== this shows which container the current session at.
CON_NAME
------------------------------
PDBORCL

SQL> alter database PDBORCL open;
Database altered.

SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDBORCL READ WRITE

4. how to list all the PDBs (including CDB) on services?

(1) from CDB root
SQL> connect /as sysdba
Connected.
SQL> select NAME,CON_ID,PDB from v$services;
NAME CON_ID PDB
----------------------------------- ---------- ------------------------------
pdb1 4 PDB1
pdborcl 3 PDBORCL
orclXDB 1 CDB$ROOT
orcl 1 CDB$ROOT
SYS$BACKGROUND 1 CDB$ROOT
SYS$USERS 1 CDB$ROOT

(note: the read only PDB$SEED is not listed here)

(2) from a PDB, it only lists itself
SQL> alter session set container=PDBORCL;
Session altered.

SQL> select NAME,CON_ID,PDB from v$services;
NAME CON_ID PDB
----------------------------------- ---------- ------------------------------
pdborcl 3 PDBORCL

5. How to connect to a PDB directly without 'alter session set container'?

[oracle@ffzhang-linux ~]$ sqlplus pdb1_u1/pdb1_u1@pdb1
SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 19 12:03:14 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Thu Dec 19 2013 12:02:49 -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> show con_name
CON_NAME
------------------------------
PDB1

6. How to create cdb user for OGG extract?

SQL> connect /as sysdba
Connected.

SQL> create user C##GGADMIN identified by ggadmin;
User created.

SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'ALL');
PL/SQL procedure successfully completed.

SQL> grant dba to c##ggadmin container=all;
Grant succeeded.

please note the password may be case sensitive (check sec_case_sensitive_logon):
SQL> connect C##GGADMIN/GGADMIN
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect C##GGADMIN/ggadmin
Connected.
SQL> connect c##ggadmin/ggadmin
Connected.

so the parameter in extract/replicat for password should also be in right case.
e.g., userid c##ggadmin, password ggadmin

7. An example extract parameters and simple tests

(1) parameter file
extract e1
userid c##ggadmin, password ggadmin
exttrail ./dirdat/e1
ddl include mapped
ddloptions report
sequence pdb1.pdb1_u1.*;
table pdborcl.pdborcl_u1.*;
table pdb1.pdb1_u1.s1;

(2) setup (only integrated extract can capture transactions from CDB)
GGSCI (ffzhang-linux) 31> dblogin userid c##ggadmin, password ggadmin
Successfully logged into database CDB$ROOT.

GGSCI (ffzhang-linux) 32> add extract e1, integrated tranlog, begin now
EXTRACT added.

GGSCI (ffzhang-linux) 33> add exttrail ./dirdat/e1, extract e1
EXTTRAIL added.

GGSCI (ffzhang-linux) 37> REGISTER EXTRACT e1 DATABASE CONTAINER (pdborcl, pdb1)
Extract E1 successfully registered with database at SCN 2664333.

SQL> select CAPTURE_NAME,CAPTURE_USER,STATUS,START_SCN,PURPOSE from dba_capture;
CAPTURE_NAME CAPTURE_USER STATUS START_SCN PURPOSE
----------------- ------------------ -------- ---------- -------------------
OGG$CAP_E1 C##GGADMIN ENABLED 2664333 GoldenGate Capture

(3) test example
SQL> connect pdb1_u1/pdb1_u1@pdb1
Connected.
1) DML
SQL> insert into s1 values (1,1);
1 row created.
SQL> commit;
Commit complete.

2) DDL
SQL> create sequence ss1;
Sequence created.

3) Sequence
SQL> select ss1.nextval from dual;
NEXTVAL
----------
1
SQL> select ss1.nextval from dual;
NEXTVAL
----------
2

ggsci> send e1 report
from report file
....
From Table PDB1.PDB1_U1.S1:
# inserts: 1
# updates: 0
# deletes: 0
# discards: 0
From Sequence PDB1.PDB1_U1.SS1:
# updates: 1
# discards: 0

DDL replication statistics:

Operations: 1
Mapped operations: 1
Unmapped operations: 0
Other operations: 0
Excluded operations: 0

8. Can ogg replicat connect to cdb root?

No. The replicat has to log in to a specific pdb if target DB is Oracle container DB.
e.g,
userid pdb1_u1@pdb1, password pdb1_u1

9. How to replicate sequence to cdb target?

sequence.sql needs to be run the target cdb.

======================================================================