【德哥说库系列】-Oracle 19C PDB创建大全

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
? 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
??????感谢各位大可爱小可爱！??????

前言

可以使用多种技术创建 pdb、应用程序容器并且迁移

---

📣 1.Scratch方式

以种子容器为模板去新创建一个新的PDB
例 1：Creating a PDB Using No Clauses
alter session set pdb_file_name_convert=‘pdbseed’,‘salpdb1’;
create pluggable database salpdb1 admin user saladm identified by 123;
alter pluggable database SALPDB1 open;

例 2：创建 PDB 并将预定义的 Oracle 角色授予 PDB 管理员
alter session set pdb_file_name_convert=‘pdbseed’,‘salpdb2’;
create pluggable database salpdb2 admin user saladm identified by 123 roles=(dba);
alter pluggable database SALPDB2 open;

例 3 ： Creating a Pdb Using The Storage, Default Tablespace, Path_Prefix, And
File_Name_Convert Clauses

create pluggable database pdb3 admin user saladm identified by oracle storage (maxsize 3g)
default tablespace wl
datafile '/u01/app/oracle/oradata/ORADB/pdb3/wl01.dbf' size 200m autoextend on
path_prefix = '/u01/app/oracle/oradata/ORADB/pdb3/'
file_name_convert = ('/u01/app/oracle/oradata/ORADB/pdbseed',
'/u01/app/oracle/oradata/ORADB/pdb3');

alter pluggable database PDB4 open;

📣 2.Cloning方式

? 2.1 Cloning from Local

方法一：
alter pluggable database pdb3 close;
alter pluggable database pdb3 open read only;
alter session set pdb_file_name_convert=‘pdb3’,‘pdb5’;
create pluggable database pdb5 from pdb3;
alter pluggable database PDB5 open;

方法二：
alter pluggable database pdb2 close;
alter pluggable database pdb2 open read only;
create pluggable database pdb6 from pdb2
path_prefix = ‘/u01/app/oracle/oradata/ORADB/pdb4/’
file_name_convert = (‘/u01/app/oracle/oradata/ORADB/pdb2/’,
‘/u01/app/oracle/oradata/ORADB/pdb4/’)
service_name_convert = (‘pdb2’,‘pdb4’) nologging;
alter pluggable database PDB6 open;

? 2.2 Cloning Remote PDB

确保字符集一致
select * from nls_database_parameters where parameter=‘NLS_CHARACTERSET’
or parameter=‘NLS_LANGUAGE’ or parameter=‘NLS_NCHAR_CHARACTERSET’;

确保字节序一致
select d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name from
v$transportable_platform t, gv$database d
where t.platform_name = d.platform_name;

目标端创建 dblink

目标端创建 dblink
source =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.31)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME =prod)
 )
)

create database link mydb connect to system identified by oracle using 'source';

源端授权
grant CREATE PLUGGABLE DATABASE,cdb_dba,sysoper to system container=all;
目标端创建 pdb
create pluggable database mypdb1 from PDB3@mydb
path_prefix = ‘/u01/app/oracle/oradata/PROD/mypdb1’
file_name_convert = (‘/u01/app/oracle/oradata/PROD/pdb3/’,
‘/u01/app/oracle/oradata/PROD/mypdb1’);

? 2.3 Cloning Remote Non-CDB

源断创建非容器数据库
dbca -silent -createDatabase -createAsContainerDatabase false -templateName
General_Purpose.dbc -gdbname oradb -sid oradb -responseFile NO_VALUE
-characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL

确保字符集一致
select * from nls_database_parameters where parameter=‘NLS_CHARACTERSET’
or parameter=‘NLS_LANGUAGE’ or parameter=‘NLS_NCHAR_CHARACTERSET’;

确保字节序一致
select d.inst_id, t.platform_id, t.platform_name, t.endian_format, d.name from
v$transportable_platform t, gv$database d
where t.platform_name = d.platform_name;

目标端创建 dblink

目标端创建 dblink
nocdb =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.31)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME =oradb)
 )
)

create database link orcl_link connect to system identified by beijing using 'nocdb';

源端授权
grant CREATE PLUGGABLE DATABASE,cdb_dba,sysoper to system;

目标端创建 PDB
create pluggable database mypdb2 from oradb@orcl_link
file_name_convert = (‘/u01/app/oracle/oradata/ORADB/’,
‘/u01/app/oracle/oradata/PROD/mypdb2’);

容器转换
alter session set container=MYPDB2;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

–若开库有报错，查询此数据字典
SQL> select message,time from pdb_plug_in_violations;

📣 3. Relocating

#兼容性检查方法
源端生成 pdb 的 xml 文件
begin
dbms_pdb.describe(
pdb_descr_file=>‘/home/oracle/PDB1.xml’,
pdb_name=>‘PDB1’);
end;
/

#将 PDB1.xml 复制到上标主机，目标端进行检查
set serveroutput on
declare
compatible constant varchar2(3):=
case dbms_pdb.check_plug_compatibility(
pdb_descr_file=>‘/home/oracle/PDB1.xml’,
pdb_name=>‘PDB1’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
end;
begin dbms_output.put_line(compatible);
end;
/

##源库打开
alter Pluggable database PDB1 open;

##源端授权
grant create pluggable database,cdb_dba,sysoper to system container=all;

#目标库创建容器,源库和目标库容器名字保持一致
create pluggable database MYPDB3 from PDB1@mydb relocate availability max file_name_convert=(‘/u01/app/oracle/oradata/PROD/pdb1/’,‘/u01/app/oracle/oradata/PROD/MYPDB3/’);

#目标端开库，则源端即关库了
SQL> alter pluggable database PDB1 open;

📣 4. PDB热插拔

alter pluggable database PDB1 close immediate;
alter pluggable database PDB1 unplug into ‘/home/oracle/PDB1.xml’;
drop pluggable database PDB1 keep datafiles;
create pluggable database PDB1 using ‘/home/oracle/PDB1.xml’ nocopy tempfile reuse;

补充案例：CDB 与非 CDB 在同一台主机进行热插拔

1.非 CDB 处于事务一致状态，并将其置于只读模式
startup open read only

ORA-00845: MEMORY_TARGET not supported on this system
tmpfs /dev/shm tmpfs defaults,size=3G 0 0
mount -o remount,size=3G /dev/shm


2.非 CDB 的 XML 文件
begin
dbms_pdb.describe(
pdb_descr_file => '/tmp/ncdb.xml');
end;
/

3.兼容性检查，CDB中确认
SET SERVEROUTPUT ON
declare
compatible constant varchar2(3) :=
case dbms_pdb.check_plug_compatibility(pdb_descr_file =>
'/tmp/ncdb.xml',pdb_name => 'NCDB')
when true then 'yes'
else 'no'
end;
begin
dbms_output.put_line(compatible);
end;
/

4.关闭非 CDB
shutdown immediate


5.创建目录
mkdir /u01/app/oracle/oradata/ORADB/ncdb


6.插入非 CDB，将文件复制到新位置
create pluggable database ncdb using '/tmp/ncdb.xml' copy 
file_name_convert = ('/u01/app/oracle/oradata/ORCL/','/u01/app/oracle/oradata/ORADB/ncdb/');


7.必须先运行脚本，才能首次打开 PDB
alter session set container=ncdb;
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql