linux下安裝12c
重新開機linux以後，dbcalinux
PDB/CDB使用
SQL> select instance_name from v$instance;sql
INSTANCE_NAMEc#
------------------------------------------------session
GORCLoracle
SQL> alter pluggable database p_orcl open;app
Pluggable database altered.dom
SQL> alter session set container=p_orcl;ide
Session altered.測試
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;url
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/[email protected]: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##[email protected] 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 [email protected]: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.
測試完成。