问题和答案

1.如何知道12c DB是CDB还是Non-CDB？

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

(2) 非CDB
SQL> select cdb from v$database;
CDB
---
NO

2. 如何列出所有PDB？

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 应该处于无限制模式 或者在 ggsci下注册Extract可能会失败，并报有如下错误:

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

3. 如何打开PDB？

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

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

SQL> show con_name <==== 这个会显示当前会话所在的容器.
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的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

(注意: 这里没有列出只读的 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@test-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创建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.

请注意密码大小写(检查 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.

因此在extract/replicat参数文件中应该设置正确的密码
e.g., 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 (test-linux) 31> dblogin userid c##ggadmin, password ggadmin
Successfully logged into database CDB$ROOT.

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

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

GGSCI (test-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) 测试案例
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. OGG Replicat可以连接到cdb root吗?

不可以. 如果是CDB环境，replicat需要连接到具体的PDB
比如,
userid pdb1_u1@pdb1, password pdb1_u1

9. 如何将sequence复制到cdb目标端?

 需要运行sequence.sql 脚本在目标端 cdb..

10. 是否可以在Extract中使用sqlexec从pdb获取数据?

通常情况下是不不允许直接从CDB中获得PDB的数据。临时解决办法就是使用 database link.