创建可插拔数据库PDB
第一章、从PDB$SEED创建新PDB
- 从PDB$SEED复制数据文件
- 创建system和sysaux表空间
- 创建完整目录，包括指向Oracle提供的对象元数据
- 创建临时表空间temp
- 创建公共用户 sys、system
- 创建授予本地PDB_DBA角色的本地用户PDBA
- 创建新的默认服务
-- 使用 create_file_dest
create pluggable database pdb1 admin user admin1 indentified by hrp roles = (dba) create_file_dest = '/u03/oradata/cdb/';
create pluggable database pdb1 admin user admin1 indentified by hrp roles = (connect) create_file_dest = '/u03/oradata/cdb/';
-- 使用create_file_dest；目录是以guid命名 其中 select guid from v$pdbs;
-- 使用参数file_name_convert
create pluggable database pdb2 admin user admin2 indentified by "oracle123_" roles = (dba) create_file_dest = ('PDB$SEEDDIR','/u01/oradata/cdb/');
小试牛刀
使用种子PDB 在容器数据库lottu创建新的PDB：PDB2
1、查看pdb状态
seed pdb 在数据库cdb启动是出于read only
sys@LOTTU>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
2、查看数据文件路径
set line 1000
col name format a100
select name, con_id from v$datafile order by con_id;
select name, con_id from v$tempfile order by con_id;
可以获取 PDB$SEED 数据存放的位置
3、创建新的PDB目录
mkdir -p /u01/app/oracle/oradata/LOTTU/pdb2
4、创建pdb2
create pluggable database pdb2 admin user pdb2_admin identified by oracle roles=(connect) file_name_convert=('/u01/app/oracle/oradata/LOTTU/pdbseed','/u01/app/oracle/oradata/LOTTU/pdb2');
5、查看pdb状态
sys@LOTTU>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
6、打开pdb2数据库
sys@LOTTU>alter session set container = pdb2;
Session altered.
sys@LOTTU>alter pluggable database pdb2 open;
Pluggable database altered.
sys@LOTTU>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
7、查看pdb2的数据文件
col name format a100
select name, con_id from v$datafile order by con_id；
--或者查cdb_data_files
sys@LOTTU>col file_name for a100;
sys@LOTTU>col tablespace_name for a20;
sys@LOTTU>select file_id, file_name, tablespace_name from cdb_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------------------------------------------------------------------- --------------------
13 /u01/app/oracle/oradata/LOTTU/pdb2/system01.dbf SYSTEM
14 /u01/app/oracle/oradata/LOTTU/pdb2/sysaux01.dbf SYSAUX
15 /u01/app/oracle/oradata/LOTTU/pdb2/undotbs01.dbf UNDOTBS1
8、配置tnsnames.ora文件
vi $ORACLE_HOME/network/admin/tnsnames.ora
pdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lottu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
9、连接pdb2
sqlplus sys/***@pdb2 as sysdba
10、查看用户
sys@PDB2>select username,common,con_id from cdb_users where account_status = 'OPEN';
USERNAME COMMON CON_ID
------------------------------ ------ ----------
SYS YES 4
SYSTEM YES 4
C##LOTTU01 YES 4
PDB2_ADMIN NO 4
11、创建并指定默认表空间
select file_name, tablespace_name from cdb_data_files;
create tablespace users datafile '/u01/app/oracle/oradata/LOTTU/pdb2/user.dbf' size 50M autoextend on next 1G maxsize 5G;
第二章、克隆本地pdb
小试牛刀
从容器数据库cdb1克隆本地pdb1 创建pdb3
1、只读模式打开pdb1
sys@LOTTU>conn sys/li0924520_@pdb1 as sysdba
Connected.
sys@PDB1>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
sys@PDB1>shutdownimmediate;
Pluggable Database closed.
sys@PDB1>startup open read only;
Pluggable Database opened.
sys@PDB1>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ ONLY NO
2、查看数据文件路径
set line 1000
col name format a100
select name, con_id from v$datafile where con_id = 3;
3、创建新的pdb目录
mkdir -p /u01/app/oracle/oradata/LOTTU/pdb3
4、克隆数据pdb3
create pluggable database pdb3 from pdb1 file_name_convert=('pdb1','pdb3');
5、打开pdb3数据库
alter session set container =pdb3
alterpluggable database pdb3 open;
6、打开pdb1数据库
alter session set container = pdb1;
alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 open;
第三章、克隆非cdb或远程pdb
- 将源端pdb设置只读模式
- 在目标端cdb、创建数据库dblink
- 克隆pdb
- 运行noncdb_to_pdb.sql脚本
- 在读写模式打开数据库
- 将源端pdb设置可读写模式
小试牛刀
克隆非容器数据库prod；在容器数据库cdb1中创建pdb4
1、只读模式打开prod
[oracle@enmoedu1 ~]$ export ORACLE_SID=PROD
[oracle@enmoedu1 ~]$ sqlplus / as sysdba
SYS@PROD> startup open read only;
SYS@PROD> select open_mode from v$database;
2、配置tnsnames.ora
在源端查看监听；根据信息在目标端配置tnsnames.ora
prod =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ***)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ***)
)
)
3、在目标端创建dblink
create database link lk_prod connect to system identified by oracle using 'prod';
--在目标端
select file_name from dba_data_files@lk_prod;
--在源端
select file_name from dba_data_files;
比较两个结果是否一致。
4、创建pdb4目录
mkdir /u01/app/oracle/oradata/cdb1/pdb4
5、克隆pdb4
create pluggable database pdb4 from non$cdb@lk_prod file_name_convert=('/u01/app/oracle/oradata/PROD','/u01/app/oracle/oradata/cdb1/pdb4');
6、在pdb4 mount状态执行noncdb_to_pdb脚本
conn sys/****@pdb4 assysdba
showpdbs
@?/rdbms/admin/noncdb_to_pdb.sql
7、打开pdb4
等6中脚本执行完；打开pdb4
startup
show pdbs
8、打开远端数据库
shutdownimmediate;
startup
第四章、删除数据库
alter pluggable database pdb3 close;
drop pluggable database pdb3 [including datafiles];
- 更新控制文件
- 删除pdb数据文件
- 默认是保留数据文件；可以插入另外一个cdb
- 要求sysdba权限
小试牛刀
从容器数据库删除pdb3
1、关闭pdb
show pdbs
alter pluggable database pdb3 close immediate;
showpdbs
2、拔出pdb
alter pluggable database pdb3 unplug into '/home/oracle/pdb3.xml'
col pdb_name fora20
select pdb_name,STATUS from cdb_pdbs where pdb_name='PDB3';
3、 删除pdb
drop pluggable database pdb3;
第五章、将拔出的pdb插入cdb
小试牛刀
根据前面删除pdb3；冲击插入cdb中
1、关闭pdb
show pdbs
alter pluggable database pdb3 close immediate;
show pdbs
2、拔出pdb
alter pluggable database pdb3 unplug into '/home/oracle/pdb3.xml'
col pdb_name for a20
select pdb_name,STATUS from cdb_pdbs where pdb_name='pdb3';
3、 删除pdb
drop plaggable database pdb3;
4、检查兼容性
使用 DBMS_PDB.CHECK_PLUG_COMPATIBILITY 函数检查拔出的 prodpdb 与 cdb1 是否兼容
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/pdb3.xml',
pdb_name => 'pdb3')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
返回为yes；表名满足兼容性
5、插入cdb1
create pluggable database new_pdb3 using '/home/oracle/pdb3.xml' nocopy;
-- 使用原来的路径
-- 换个路径
create pluggable database pdb4 using '/home/oracle/pdb3.xml' COPY FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/LOTTU/pdb3', '/u01/app/oracle/oradata/LOTTU/pdb4') PARALLEL 4
COPY: The files of the tablespaces are copied to a new location.
MOVE: The files of the tablespaces are moved to a new location.
NOCOPY: The files of the tablespaces are not copied or moved.
SNAPSHOT COPY: The tablespaces are cloned with storage snapshots.
NO DATA: The data model definition of the tablespaces is cloned but not the tablespaces’ data.
6、打开新的数据库
alter session set container = pdb4;
alterpluggable database pdb4 open;
浙公网安备 33010602011771号