linux下安裝12c
重啟linux之後，dbca
PDB/CDB使用
SQL> select instance_name from v$instance;
INSTANCE_NAME
------------------------------------------------
GORCL
SQL> alter pluggable database p_orcl open;
Pluggable database altered.
SQL> alter session set container=p_orcl;
Session altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
--------- ---------- --------------------------------------------------------------------------- ------------------------------
4 1842197265 P_ORCL READ WRITE
SQL> create user test identified by test;
User created.
SQL>create user hr identified by hr;
Create table test.t1(id int primary key, name varchar2(50));
Create table hr.t2(id int primary key, name varchar2(50));
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter PLUGGABLE database all open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------------------------------------------------------ ------------------------------
2 2940647386 PDB$SEED READ ONLY
3 1813216422 C_ORCL READ WRITE
4 1842197265 P_ORCL READ WRITE
然後在listener.ora, tnsnames.ora中，dbname因為CDB, PDB是不同的，可以單獨配置。以下是listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = G_ORCL)
(ORACLE_HOME = /app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = GORCL)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /app/oracle/product/12.1.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = P_ORCL)
(ORACLE_HOME = /app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = GORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.89.134)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
以下是tnsnames.ora
PORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.89.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = G_ORCL)
)
)
GORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.89.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = G_ORCL)
)
)
所以使用tnsping是針對上面tnsnames.ora中的設定建立的，都是指向同一個service，因此其實只有一個service。
直接連線到PDB的方法如下：
OGG12.2 安裝
原理
OGG針對db12c多租戶只需要配置一個extract，指向多個pdb；然後使用多個傳輸程式傳輸不同PDB的資料到目標端，當然也可以只用一個傳輸程式。
目標端針對不同的PDB，必須配置不同的rep程式。
源端DB準備
SQL>Alter database archivelog;
SQL>Alter database force logging;
SQL>Alter database add supplemental log data;
SQL>Alter system switch logfile;
SQL>alter system set enable_goldengate_replication=true;
SQL>create user C##ogg identified by ogg;
SQL>grant connect, create session, resource to c##ogg;
SQL>exec dbms_goldengate_auth.grant_admin_privilege(‘C##OGG’,container=>’all’)
SQL>grant dba to c##ogg container=all;
測試表
源端
測試表
PDB: P_orcl
Test.t1
Hr.t2
Pdb: pdb3
SQL>create user p3 identified by p3;
SQL>create table p3.t3 (id int primary key, name varchar2(50));
目標端
Pdb: pdb4
Dba user: pdb4_admin
SQL>Alter session set container=pdb4;
SQL>create user p4 identified by p4;
SQL>create table p4.t4 (id int primary key, name varchar2(50));
sqlplus / as sysdba
SQL> alter session set container=pdb4;
Session altered.
SQL> grant dba to pdb4_admin container=pdb4;
Grant succeeded.
SQL> conn pdb4_admin/pdb4_admin as sysdba;
Connected.
conn pdb4_admin/pdb4_admin@192.168.89.134:1521/pdb4 as sysdba;
配置
Credential store配置
GGSCI (localhost.localdomain) 27> create wallet
Created wallet at location 'dirwlt'.
Opened wallet at location 'dirwlt'.
GGSCI (localhost.localdomain) 28> ADD MASTERKEY
Master key 'OGG_DEFAULT_MASTERKEY' added to wallet at location 'dirwlt'.
GGSCI (localhost.localdomain) 29> INFO MASTERKEY
Masterkey Name: OGG_DEFAULT_MASTERKEY
Creation Date: Mon Oct 20 20:51:23 2014
Version: Creation Date: Status:
1 Mon Oct 20 20:51:23 2014 Current
GGSCI (localhost.localdomain) 30> INFO MASTERKEy version 1
Masterkey Name: OGG_DEFAULT_MASTERKEY
Creation Date: Mon Oct 20 20:51:23 2014
Version: 1
Renew Date: Mon Oct 20 20:51:23 2014
Status: Current
Key Hash (SHA1): 0x2E491E86DA7A0C0D767360B2B8C07968AD9A14BF
拷貝wallet目錄dirwlt到其它系統，這樣所有系統的認證資訊相同。在其它系統確認masterkey的版本及key hash是相同的。
GGSCI (localhost.localdomain) 50> add credentialstore
Credential store created in ./dircrd/.
GGSCI (localhost.localdomain) 51> ALTER CREDENTIALSTORE ADD USER c##ogg alias extuser
Password:
Credential store in ./dircrd/ altered.
GGSCI (localhost.localdomain) 52> info CREDENTIALSTORE
Reading from ./dircrd/:
Domain: OracleGoldenGate
Alias: extuser
Userid: c##ogg
GGSCI (localhost.localdomain) 61> dblogin useridalias extuser
Successfully logged into database CDB$ROOT.
配置extract
export ORACLE_SID=GORCL
登入到pdb
GGSCI> dblogin userid c##ogg@porcl password ogg
The following enables supplemental logging for the schema scott.
GGSCI>ADD SCHEMATRANDATA scott
The following example logs all supported key and non-key columns
for all current and future tables in the schema named scott.
GGSCI>ADD SCHEMATRANDATA scott ALLCOLS
Logging of supplemental redo data enabled for table P_ORCL.TEST.T1.
TRANDATA for scheduling columns has been added on table 'P_ORCL.TEST.T1'.
GGSCI (localhost.localdomain) 11> add schematrandata p_orcl.hr allcols
2014-10-20 19:50:44 INFO OGG-01788 SCHEMATRANDATA has been added on schema hr.
2014-10-20 19:50:44 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hr.
2014-10-20 19:50:44 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema hr.
GGSCI (localhost.localdomain) 12> info trandata p_orcl.hr.t2
Logging of supplemental redo log data is disabled for table P_ORCL.HR.T2.
GGSCI (localhost.localdomain) 13> add trandata p_orcl.hr.t2
Logging of supplemental redo data enabled for table P_ORCL.HR.T2.
TRANDATA for scheduling columns has been added on table 'P_ORCL.HR.T2'.
GGSCI (localhost.localdomain) 14> info trandata p_orcl.hr.t2
Logging of supplemental redo log data is enabled for table P_ORCL.HR.T2.
Columns supplementally logged for table P_ORCL.HR.T2: ID.
抽取程式
extract ex12c setEnv(ORACLE_SID="GORCL") --dblogin userid c##ogg, password ogg useridAlias extuser LOGALLSUPCOLS UPDATERECORDFORMAT COMPACT exttrail ./dirdat/et sourceCatalog p_orcl table test.*; table hr.*; --SourceCatalog pdb3 table pdb3.p3.t3; |
GGSCI > add extract ex12c integrated tranlog, begin now
EXTRACT added.
GGSCI> add exttrail ./dirdat/et, extract ex12c
EXTTRAIL added.
GGSCI>REGISTER EXTRACT ex12c DATABASE CONTAINER (p_orcl,pdb3)
Register extract ex12c database container(pdb11)
配置replicat
| replicat rep1 setenv(ORACLE_SID='GORCL') dbOptions IntegratedParams(Parallelism 6) userid pdb4_admin@localhost:1521/pdb4, password pdb4_admin assumeTargetDefs sourcecatalog p_orcl map hr.t2, target p4.t4; map test.t1, target p4.t4; map pdb3.p3.t3, target p4.t4; |
GGSCI>add replicat rep1 integrated exttrail ./dirdat/et
資料同步測試
DB操作
SQL> alter session set container=p_orcl;
Session altered.
SQL> insert into test.t1 values(1,'test1111');
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set container=pdb4;
Session altered.
SQL> select * from p4.t4;
ID NAME
1 test1111
OGG驗證
GGSCI (localhost.localdomain) 19> stats ex12c, daily
| Sending STATS request to EXTRACT EX12C ...stats Start of Statistics at 2014-10-21 01:29:17. Output to ./dirdat/et: Extracting from P_ORCL.TEST.T1 to P_ORCL.TEST.T1: *** Daily statistics since 2014-10-21 01:28:02 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. |
GGSCI (localhost.localdomain) 47> stats rep1, daily
Sending STATS request to REPLICAT REP1 ... Start of Statistics at 2014-10-21 02:17:28. Integrated Replicat Statistics: Total transactions 0.00 Redirected 1.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from P_ORCL.TEST.T1 to PDB4.P4.T4: *** Daily statistics since 2014-10-21 02:12:56 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. |
測試完成。