Oracle在12c版本引入了多租户的概念,在一个cdb的根容器下可以创建多个pdb供不同用户使用,cdb中主要保存数据库元数据,而pdb中保存用户数据,各个pdb直接不相互影响。Oracle提供了多种方式进行pdb数据库的创建/迁移/克隆,甚至实现了不停机的在线克隆。
12.2的online pdb relocate 实现了PDB 在线几乎零停机时间在不同CDB之间的迁移,且在relocate过程中源库一直是open read-write状态,使用了增量日志的方式追加减少了最源库和网络资源的影响。
实施步骤验证
主机 | cdb | pdb | |
---|---|---|---|
源 | 192.168.1.36/192.168.1.37(RAC) | sourcdb | pdb1 |
目标 | 192.168.1.225/192.168.1.226(RAC) | destcdb | pdb1 |
源库:创建复制用户,授权(cdb级别)
SQL>create user C##CLONE_ADMIN identified by oracle container=all;
User created.
SQL> grant connect, sysoper, create pluggable database to C##CLONE_ADMIN container=all;
Grant succeeded.
目标库:修改tnsnames.ora
cdb_remote =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sourcdb)
)
)
目标库:创建dblink(一定要是cdb级别的)
SQL> create database link link_cdb connect to C##CLONE_ADMIN identified by oracle using 'cdb_remote';
Database link created.
目标库:测试dblink
SQL> select sysdate from dual@link_cdb;
SYSDATE
---------------
30-JUL-18
目标库:创建pdb1
SQL> create pluggable database pdb1 from pdb1@link_cdb relocate ;
Pluggable database created.
目标库:查询pdb1
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB3 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB1 MOUNTED
目标库:启动pdb1
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
源库:查看pdb1,发现pdb1已经不存在了。
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ----------------------------- ----------
2 PDB$SEED READ ONLY NO
Relocating和copying方式支持并行方式克隆数据库,需加上参数 parallel integer
主机 | cdb | pdb | |
---|---|---|---|
源 | 192.168.1.36/192.168.1.37(RAC) | sourcdb | pdb1 |
目标 | 192.168.1.225/192.168.1.226(RAC) | destcdb | pdb1 |
SQL>col name for a15;
Set linesize 200;
select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
------- ---------- ------------------------------- ---------- ----------
2 3862635051 6FBFB6C2522C0FBBE0537A2003BC80B0 PDB$SEED READ ONLY
SQL> create pluggable database pdb1 admin user pdb1admin identified by pdb1admin roles=(connect,select_catalog_role);
Pluggable database created.
SQL>col pdb_name for a15;
select pdb_id,pdb_name,status,creation_time from cdb_pdbs where pdb_name='PDB1';
PDB_ID PDB_NAME STATUS CREATION_
---------- ---------- ---------- ---------
5 PDB1 NEW 29-JUN-18
SQL> select con_id,name,open_mode from v$pdbs where name='PDB1';
CON_ID NAME OPEN_MODE
---------- ---------- ----------
5 PDB1 MOUNTED
SQL> alter pluggable database PDB1 OPEN;
Pluggable database altered.
SQL> conn PDB1ADMIN/pdb1admin@192.168.1.36:1521/pdb1
Connected.
SQL> show user;
USER is "PDB1ADMIN"
SQL> col username for a10
SQL> col granted_role for a10
SQL> select * from user_role_privs
USERNAME GRANTED_RO ADM DEL DEF OS_ COM INH
---------- ---------- --- --- --- --- --- ---
PDB2ADMIN PDB_DBA YES NO YES NO NO NO
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE PLUGGABLE DATABASE
CREATE SESSION
SQL> ho lsnrctl status |grep pdb1 -B1
Instance "rac12c1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
If the CDB is not in ARCHIVELOG mode, then the source PDB must be in open read only
mode. This requirement does not apply if the CDB is in ARCHIVELOG mode.
col PROPERTY_NAME for a20
col PROPERTY_VALUE for a20
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME PROPERTY_VALUE
-------------------- -------------------
LOCAL_UNDO_ENABLED TRUE
select
con_id,username,default_tablespace,temporary_tablespace
from cdb_users
where lower(username)='c##global_user1' order by con_id;
CON_ID USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------- -------------------- ---------------------------
1 C##GLOBAL_USER1 USERS TEMP
3 C##GLOBAL_USER1 USERS TEMP
SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
PATH_PREFIX ='+DATADG';
Pluggable database created.
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
CON_ID DBID GUID NAME OPEN_MODE
------- ---------- -------------------------------- -------------------
2 3862635051 6FBFB6C2522C0FBBE0537A2003BC80B0 PDB$SEED READ ONLY
3 1192027733 70CFFC1034AE1258E0537A2003BCF86A PDB1 READ WRITE
4 3478260040 70D030CE84E51838E0537A2003BC0B37 PDB2 MOUNTED
SQL> alter pluggable database PDB2 OPEN;
Pluggable database altered.
SQL> alter session set container=PDB2;
Session altered.
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------
+DATADG/RAC12C/70D030CE84E51838E0537A2003BC0B37/DATAFILE/users.413.981330561
+DATADG/RAC12C/70D030CE84E51838E0537A2003BC0B37/DATAFILE/undotbs1.393.981330561
+DATADG/RAC12C/70D030CE84E51838E0537A2003BC0B37/DATAFILE/sysaux.392.981330561
+DATADG/RAC12C/70D030CE84E51838E0537A2003BC0B37/DATAFILE/system.391.981330561
SQL> conn PDB1ADMIN/pdb1admin@192.168.1.36:1521/pdb2
Connected.
SQL> show user;
USER is "PDB1ADMIN"
SQL> col username for a10
SQL> col granted_role for a10
SQL> select * from user_role_privs;
USERNAME GRANTED_RO ADM DEL DEF OS_ COM INH
---------- ---------- --- --- --- --- --- ---
PDB1ADMIN PDB_DBA YES NO YES NO NO NO
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE PLUGGABLE DATABASE
CREATE SESSION
主机 | cdb | pdb | |
---|---|---|---|
源 | 192.168.1.36/192.168.1.37(RAC) | sourcdb | pdbtest |
目标 | 192.168.1.225/192.168.1.226(RAC) | destcdb | Pdbtest_new |
发现是因为源库和目标库的pga_aggregate_target参数和processes参数设置不一致导致的。第三条是因为源pdb没有users表空间造成的,目标库启动后处于限制模式。
有两种方式解决这个问题:
1.在目标端pdb添加users表空间:
SQL> create tablespace users datafile '+DATADG' size 512M;
Tablespace created.
SQL> select file_name,bytes/1024/1024 from dba_data_files;
FILE_NAME BYTES/1024/1024
--------------------------------------------------------- ---------------
+DATADG/DESTCDB/71CA8CBF1BBE3D46E053E18003C58534/DATAFILE/system.296.982405347 1228.80469
+DATADG/DESTCDB/71CA8CBF1BBE3D46E053E18003C58534/DATAFILE/sysaux.297.982405347 1228.80469
+DATADG/DESTCDB/71CA8CBF1BBE3D46E053E18003C58534/DATAFILE/undotbs1.298.982405347 6144
+DATADG/DESTCDB/71CA8CBF1BBE3D46E053E18003C58534/DATAFILE/undo_2.300.982408217 6144
+DATADG/DESTCDB/71CA8CBF1BBE3D46E053E18003C58534/DATAFILE/users.301.982419533 512
然后重启一下pdbtest_new,发现此时pdbtest_new可以正常启动:
SQL> alter pluggable database pdbtest_new close immediate;
Pluggable database altered.
SQL> alter pluggable database pdbtest_new open;
Pluggable database altered.
再查看pdb_plug_in_violations视图发现错误已解决
1.在源数据库pdbtest创建users表空间,然后再进行克隆:
SQL> create tablespace users datafile '+DATADG' size 512M;
Tablespace created.
SQL> select name,con_id from v$tablespace;
NAME CON_ID
------------------------------- ----------
SYSTEM 6
SYSAUX 6
UNDOTBS1 6
TEMP 6
USERS 6
UNDO_2 6
此时源端已经有了users表空间
然后执行克隆命令:
SQL> create pluggable database pdbtest_new from pdbtest@pdbtest_link;
Pluggable database created.
SQL> alter pluggable database pdbtest_new open;
Pluggable database altered.
发现此时正常open,没有报错。
主机 | cdb | pdb | |
---|---|---|---|
源 | 192.168.1.36/192.168.1.37(RAC) | sourcdb | pdbtest |
目标 | 192.168.1.225/192.168.1.226(RAC) | destcdb | Pdbtest_new |
主机 | cdb | pdb | |
---|---|---|---|
源 | 192.168.1.36/192.168.1.37(RAC) | noncdb(非cdb) | 不适用 |
目标 | 192.168.1.225/192.168.1.226(RAC) | destcdb | pdbtest |
源端:由于没有noncdb,使用dbca创建一个non-cdb类型的数据库noncdb
export ORACLE_SID=noncdb1
SQL> select name,cdb,con_id from v$database;
NAME CDB CON_ID
--------- --- ----------
NONCDB NO 0
目标端:配置监听
noncdb_remote =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.36)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.37)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = noncdb)
)
)
目标端:测试连接
sqlplus sys/oracle@noncdb_remote as sysdba
目标端:创建dblink
SQL> create public database link noncdb_link connect to linkuser identified by linkuser using 'noncdb_remote';
Database link created.
源端:创建linkuser
SQL> create user linkuser identified by linkuser;
User created.
SQL> grant dba to linkuser;
Grant succeeded.
目标端:测试dblink
SQL> select cdb from v$database@noncdb_link;
CDB
---------
NO
目标端:远程克隆pdb
SQL> create pluggable database noncdb_new from noncdb@noncdb_link;
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ----------------------------- ----------
2 PDB$SEED READ ONLY NO
3 NONCDB_NEW MOUNTED
4 PDB1 READ WRITE NO
5 PDB2 MOUNTED
目标端:执行noncdb_to_pdb.sql(在打开数据库之前一定要切换到该pdb下并执行这个sql,否者启动会报错)
SQL> alter session set container=noncdb_new;
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
目标端:切换到cdb,然后再启动数据库
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database noncdb_new open;
Pluggable database altered.
使用xml文件创建pdb
主机 | cdb | pdb | |
---|---|---|---|
源 | 192.168.1.36/192.168.1.37(RAC) | sourcdb | pdb1 |
目标 | 192.168.1.225/192.168.1.226(RAC) | destcdb | pdbtest |
源库:关闭并拔出数据库
SQL> alter session set container=pdb1;
Session altered.
SQL> alter pluggable database pdb1 close immediate;
Pluggable Database closed.
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database PDB1 unplug into '/tmp/pdb1.xml';
Pluggable database altered.
源库:删除pdb1保留数据文件:
SQL> drop pluggable database PDB1;
Pluggable database dropped.
--拷贝文件(将源库asm磁盘中该guid文件夹下的文件挨个拷贝到本地,再拷贝到远程,再拷贝入远程的asm中)
示例是拷贝到/tmp目录下做中转
Scp到目标库文件系统,然后拷贝到目标库的asm磁盘(不能加上OMF的文件后缀,否则报错)
ASMCMD> pwd
+datadg/sourcdb/722EE1EE9A370DBDE053100300C558EF/datafile
ASMCMD> cp /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/SYSAUX.289.982836275 +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/SYSAUX
copying /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/SYSAUX.289.982836275 -> +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/SYSAUX
ASMCMD> cp /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/SYSTEM.290.982836275 +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/SYSTEM
copying /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/SYSTEM.290.982836275 -> +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/SYSTEM
ASMCMD> cp /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/UNDOTBS1.284.982836275 +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/UNDOTBS1
copying /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/UNDOTBS1.284.982836275 -> +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/UNDOTBS1
ASMCMD> cp /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/USERS.292.982836373 +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/USERS
copying /tmp/722EE1EE9A370DBDE053100300C558EF/datafile/USERS.292.982836373 -> +datadg/destcdb/722EE1EE9A370DBDE053100300C558EF/datafile/USERS
目标库:查询pdb
SQL> select con_id,name,open_mode from v$pdbs where name='PDB1';
no rows selected
目标库:创建pdb1
SQL> CREATE PLUGGABLE DATABASE pdb1 USING '/tmp/pdb1.xml'
COPY tempfile reuse
--PATH_PREFIX = '+DATADG'
--FILE_NAME_CONVERT = ('+DATADG/SOURCDB', '+DATADG/DESTCDB');
Pluggable database created.
目标库:查询并开启pdb1
SQL> select con_id,name,open_mode from v$pdbs where name='PDB1';
CON_ID NAME OPEN_MODE
---------- --------------------
5 PDB1 MOUNTED
SQL> alter session set container=PDB1;
Session altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
目标库:查看相关数据文件
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATADG/DESTCDB/722EE1EE9A370DBDE053100300C558EF/DATAFILE/system.286.982861811
+DATADG/DESTCDB/722EE1EE9A370DBDE053100300C558EF/DATAFILE/sysaux.302.982861811
+DATADG/DESTCDB/722EE1EE9A370DBDE053100300C558EF/DATAFILE/undotbs1.306.982861811
+DATADG/DESTCDB/722EE1EE9A370DBDE053100300C558EF/DATAFILE/users.284.982861811
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATADG/DESTCDB/722EE1EE9A370DBDE053100300C558EF/TEMPFILE/temp.282.982861839
SQL> conn PDB1ADMIN/pdb1admin@192.168.1.225:1521/pdb1
Connected.