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>