create user c#xb identified by xb container=all; grant create session,sysoper, create pluggable database to c#xb container=all;
# 必须本地undo模式和归档模式下 archive log list;
col property_name for a30 col property_value for a30 select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
create public database link to_remote connect to c#xb identified by xb using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stbyum)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cdb1) ) )';
# 测试数据链 sys@ORA12C> select * from dual@to_remote;
D - X
# 检查cdb的本地undo模式和archive log col property_name for a30 col property_value for a30 select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
sys@ORA12C> create pluggable database pdb3 from pdb3@to_remote relocate;
Pluggable database created.
sys@ORA12C> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12C READ WRITE NO 4 PDB3 MOUNTED
# 这个时候本地新建的pdb3还是mount状态,而远端的pdb3还是读写,可以提供服务 sys@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB3 READ WRITE NO sys@CDB1> alter session set container=pdb3;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12C READ WRITE NO 4 PDB3 READ WRITE NO sys@ORA12C> select file#,name from v$datafile where con_id=4;
sys@ORA12C> alter session set container=PDB3_PROXY ;
Session altered.
sys@ORA12C> select name from v$database; select name from v$database * ERROR at line 1: ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from PROXYPDB$DBLINK
sys@ORA12C> conn sys@ora12c as sysdba Enter password: Connected.
sys@ORA12C> alter session set container=PDB3_PROXY ;
sys@ORA12C> select con_id,name,open_mode,proxy_pdb from v$pdbs;
CON_ID NAME OPEN_MODE PRO ---------- ------------------------------ ---------- --- 2 PDB$SEED READ ONLY NO 3 PDB12C READ WRITE NO 4 PDB4 READ WRITE NO 5 APP_ROOT READ WRITE NO 6 APP1 READ WRITE NO 7 APP_ROOT$SEED READ ONLY NO 8 APP2 READ WRITE NO 9 F3483665686_3_1 READ WRITE NO 10 F3483665686_3_2 READ WRITE NO 11 F3483665686_3_3 READ WRITE NO 12 PDB3_PROXY READ WRITE YES
sys@ORA12C> select pdb_name,status,IS_PROXY_PDB from cdb_pdbs;
PDB_NAME STATUS IS_ ------------------------------ ---------- --- PDB$SEED NORMAL NO PDB12C NORMAL NO PDB4 NORMAL NO APP_ROOT NORMAL NO APP1 NORMAL NO APP_ROOT$SEED NORMAL NO APP2 NORMAL NO F3483665686_3_1 NORMAL NO F3483665686_3_2 NORMAL NO F3483665686_3_3 NORMAL NO PDB3_PROXY NORMAL YES
create pluggable database app_root as application container admin user xb identified by xb; alter pluggable database app_root open;
alter session set container=app_root; col name format a20 col root format a10 select con_id, name, application_root as root, application_pdb as pdb from v$containers;
CON_ID NAME ROOT PDB ---------- -------------------- ---------- --- 5 APP_ROOT YES NO
# 创建种子pdb alter session set container=app_root; create pluggable database as seed admin user seed_xb identified by xb; alter pluggable database app_root$seed open;
# 刷新种子pdb alter session set container=app_root$seed; alter pluggable database application all sync;
# 将种子pdb置于只读 alter pluggable database close immediate; alter pluggable database open read only;
也可以通过其他应用pdb来创建种子pdb
1 2 3 4 5 6 7 8
alter session set container=app_root; alter pluggable database app_root$seed close immediate; drop pluggable database app_root$seed including datafiles;
create pluggable database as seed from app1; alter pluggable database app_root$seed open; alter pluggable database app_root$seed close immediate; alter pluggable database app_root$seed open read only;
create pluggable database as seed from app_root; alter pluggable database app_root$seed open;
# 种子pdb打开时有告警 sys@ORA12C> Warning: PDB altered with errors.
sys@ORA12C> select message,time from pdb_plug_in_violations;
MESSAGE TIME ---------------------------------------------------------------------------------------------------- ---------------------------------------------------- Non-Application PDB plugged in as an Application PDB, requires pdb_to_apppdb.sql be run. 20-JUN-19 01.37.32.388687 PM
# 提示要运行pdb_to_apppdb.sql alter session set container=app_root$seed; @$ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql
# 重新打开种子pdb alter pluggable database app_root$seed close immediate; alter pluggable database app_root$seed open read only;
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- -------------------------- default_sharing string METADATA
# 创建应用 alter session set container=app_root; alter pluggable database application sales_app begin install '1.0';
# 创建表空间和用户 create tablespace tbs_sales datafile size 10m autoextend on; create user sales_xb identified by xb container=all; grant dba to sales_xb; alter user sales_xb default tablespace tbs_sales;
# 创建元数据关联表 drop table sales_xb.sales purge; create table sales_xb.sales SHARING=METADATA (id number, amount number);
insert into sales_xb.sales values(1,100); commit;
# 结束应用的创建 alter pluggable database application sales_app end install '1.0';
现在可以通过DBA_APPLICATIONS视图查看刚创建的应用
1 2 3 4 5 6 7 8 9 10 11 12
col app_name format a20 col app_version format a10
select app_name, app_version, app_status from dba_applications where app_name = 'SALES_APP';
APP_NAME APP_VERSIO APP_STATUS -------------------- ---------- ------------ SALES_APP 1.0 NORMAL
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 5 APP_ROOT READ WRITE NO 6 APP1 READ WRITE NO 7 APP_ROOT$SEED READ ONLY NO 8 APP2 READ WRITE NO
同步到APP1当中
1 2 3 4 5 6 7 8 9 10 11 12 13 14
alter session set container=app1; select * from sales_xb.sales;
ERROR at line 1: ORA-00942: table or view does not exist
# 刷新数据 alter pluggable database application sales_app sync;
alter session set container=app_root; select c.name, a.con_uid, a.app_name, a.app_version, a.app_status from dba_app_pdb_status a join v$containers c on c.con_uid = a.con_uid where a.app_name = 'SALES_APP';
NAME CON_UID APP_NAME APP_VERSIO APP_STATUS -------------------- ---------- -------------------- ---------- ------------ APP1 3232047153 SALES_APP 1.0 NORMAL
数据关联
这里升级应用程序从1.0到2.0,然后创建数据关联的公用表
1 2 3 4 5 6 7 8 9 10 11 12
# 升级应用程序 alter pluggable database application sales_app begin upgrade '1.0' to '2.0';
drop table sales_xb.sales2 purge; create table sales_xb.sales2 SHARING=DATA (id number, amount number);
insert into sales_xb.sales2 values(2,100); commit;
alter pluggable database application sales_app end upgrade to '2.0';
alter pluggable database application sales_app sync; select * from sales_xb.sales2;
ID AMOUNT ---------- ---------- 2 100
alter session set container=app_root; select c.name, a.con_uid, a.app_name, a.app_version, a.app_status from dba_app_pdb_status a join v$containers c on c.con_uid = a.con_uid where a.app_name = 'SALES_APP';
NAME CON_UID APP_NAME APP_VERSIO APP_STATUS -------------------- ---------- -------------------- ---------- ------------ APP1 3232047153 SALES_APP 1.0 NORMAL APP2 827740620 SALES_APP 2.0 NORMAL
alter pluggable database application sales_app begin upgrade '2.0' to '3.0';
drop table sales_xb.sales3 purge; create table sales_xb.sales3 SHARING=EXTENDED DATA (id number, amount number);
insert into sales_xb.sales3 values(3,100); commit;
alter pluggable database application sales_app end upgrade to '3.0';
alter session set container=app1;
alter pluggable database application sales_app sync; select * from sales_xb.sales3;
ID AMOUNT ---------- ---------- 3 100
alter session set container=app_root; select c.name, a.con_uid, a.app_name, a.app_version, a.app_status from dba_app_pdb_status a join v$containers c on c.con_uid = a.con_uid where a.app_name = 'SALES_APP';
NAME CON_UID APP_NAME APP_VERSIO APP_STATUS -------------------- ---------- -------------------- ---------- ------------ APP2 827740620 SALES_APP 2.0 NORMAL APP1 3232047153 SALES_APP 3.0 NORMAL
接下来验证一下不同共享模式下数据的读写情况
1 2 3 4 5 6 7 8 9 10
# sales -> metadata # sales2 -> data # sales3 -> extended data alter session set container=app1; insert into sales_xb.sales values(1,200); insert into sales_xb.sales2 values(2,200); insert into sales_xb.sales3 values(3,200); commit;
# 在插入sales2时出现报错 ORA-65097: DML into a data link table is outside an application action。
alter pluggable database application sales_app begin uninstall; drop user sales_xb cascade; drop tablespace tbs_sales including contents and datafiles;
alter pluggable database application sales_app end uninstall;
alter session set container=app1; alter pluggable database application sales_app sync;
alter session set container=app2; alter pluggable database application sales_app sync;
alter session set container=app_root; select c.name, a.con_uid, a.app_name, a.app_version, a.app_status from dba_app_pdb_status a join v$containers c on c.con_uid = a.con_uid where a.app_name = 'SALES_APP';