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.
测试完成。