Oracle 12c与GoldenGate 12c的一些问答

1. 如何知道一个12c 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. 如何查看所有PDB？
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY <===== this is a seed PDB
PDBORCL MOUNTED <===== this is a PDB at mount status
PDB1 READ WRITE <===== this is a PDB at read/write open status

3. 如何打开一个PDB？
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

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

SQL> show con_name <==== 查看当前的DB会话
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. 如何在服务上列出所有PDB（包括CDB）？
(1) 在CDB根会话中
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
(注:只读的PDB$SEED未在此列出)

(2) 在PDB会话中
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. 如何直接连接到一个PDB，而不是通过‘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. 如何创建一个给OGG extract使用的CDB用户？
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.

注意：密码可能区分大小写:
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.

因此，在extract/replicat参数文件中，密码的大小写也应该一致。
如, userid c##ggadmin, password ggadmin

7. 下面是一个extract示例和简单的测试
(1) 参数文件
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) 配置 (CDB中只能使用集成抽取数据)
GGSCI  31> dblogin userid c##ggadmin, password ggadmin
Successfully logged into database CDB$ROOT.

GGSCI  32> add extract e1, integrated tranlog, begin now
EXTRACT added.

GGSCI  33> add exttrail ./dirdat/e1, extract e1
EXTTRAIL added.

GGSCI  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) 测试
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. replicat中OGG用户可以连接到cdb root不？
不行，replicat中OGG投递用户只允许连接到一个PDB中。
如：userid pdb1_u1@pdb1, password pdb1_u1


9. 如何复制sequence到目标端？
需要在两端执行：sequence.sql，具体步骤，可参考ogg for oracle.pdf文档中<Installing Support for Oracle Sequences>