问题和答案

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.