创建可插拔数据库PDB
创建可插拔数据库PDB
多种方法:
- 从种子PDB创建新的PDB
- 将非CDB插入或克隆到CDB中
- 克隆:将移走的PDB插入到另一个CDB中
- 将本地 PDB克隆到同一个CDB中
- 将远程PDB克隆到CDB中
工具:
要预配新的PDB,可以使用:
- SQL*Plus
- SQL Developer
- Enterprise Manager Cloud Control
- Enterprise Manager Database Express
- DBCA
- 从种子复制
- 通过移走/插入方法
1 从PDB$SEED创建新PDB
1.1 案例需求PDB2
使用种子PDB:PDB$SEED在容器数据库cdb3中创建新的PDB:PDB2
1.2 查看pdb状态
show pdbs
1.3 查看pdb$seed数据文件路径
alter session set container=pdb$seed;
select file_name from cdb_data_files;
切回cdb3
conn / as sysdba
show pdbs
1.4 创建新PDB目录
mkdir /u01/app/oracle/oradata/CDB3/pdb2
ls -ld /u01/app/oracle/oradata/CDB3/pdb2
1.5 创建pdb2
create pluggable database pdb2 admin user pdb2_admin identified by oracle roles=(connect) file_name_convert=('/u01/app/oracle/oradata/CDB3/pdbseed','/u01/app/oracle/oradata/CDB3/pdb2');
1.6 查看pdb状态
show pdbs
1.7 查看监听程序状态
lsnrctl status
1.8 open打开pdb
alter pluggable database pdb2 open;
show pdbs
1.9 查看pdb2数据文件
col FILE_NAME for a50
select file_id,file_name,tablespace_name,con_id from cdb_data_files;
1.10 配置tnsnames.ora
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
写入:
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2.example.com)
)
)
1.11 pdb2通过服务名登录
sqlplus sys/oracle@pdb2 as sysdba
show pdbs
1.12 pdb2查看用户
col USERNAME for a30
select username,COMMON,CON_ID from cdb_users where account_status='OPEN';
1.13 创建并指定默认表空间
create tablespace users datafile '/u01/app/oracle/oradata/CDB3/pdb2/users01.dbf' size 50m;
alter pluggable database default tablespace users;
2 克隆本地PDB
2.1 案例需求PDB3
在容器数据库CDB3中克隆本地PDB:PDB2创建PDB3
2.2 PDB2只读方式打开
show pdbs
alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 open read only;
show pdbs
2.3 查看PDB2数据文件路径
select file_name from cdb_data_files where con_id=4;
2.4 创建目录
mkdir /u01/app/oracle/oradata/CDB3/pdb3
ls -ld /u01/app/oracle/oradata/CDB3/pdb3
2.5 创建pdb3
create pluggable database pdb3 from pdb2 file_name_convert=('pdb2','pdb3');
2.6 open打开pdb3
show pdbs
alter pluggable database pdb3 open;
2.7 重新打开pdb2
alter pluggable database pdb2 close immediate;
alter pluggable database pdb2 open;
show pdbs
3 克隆non-CDB或远程PDB
3.1 案例需求PDB4
克隆非容器数据库orcl,在容器数据库CDB3中创建PDB4
3.2 read only启动orcl
export ORACLE_SID=PROD
sqlplus / as sysdba
startup open read only;
select open_mode from v$database;
3.3 查看监听器状态和tnsnames.ora
lsnrctl status
cd $ORACLE_HOME/network/admin
cat tnsnames.ora
3.4 cdb3中创建dblink
create database link lk_orcl connect to system identified by oracle using 'orcl';
3.5 查看orcl数据文件路径
select file_name from dba_data_files@lk_orcl;
使用db link查看,同时验证db link可用
3.6 创建pdb4目录
mkdir /u01/app/oracle/oradata/CDB3/pdb4
ls -ld /u01/app/oracle/oradata/CDB3/pdb4
3.7 克隆no-CDB
create pluggable database pdb4 from non$cdb@lk_orcl file_name_convert=('/u01/app/oracle/oradata/ORCL','/u01/app/oracle/oradata/CDB3/pdb4');
如果报错如下:
3.8 脚本配置tnsnames.ora
cd $ORACLE_HOME/network/admin
vi tnsnames.ora
写入:
PDB4 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb4.example.com)
)
)
3.9 pdb4 mount状态执行noncdb_to_pdb
conn sys/oracle@pdb4 as sysdba
show pdbs
@?/rdbms/admin/noncdb_to_pdb.sql
脚本执行时间比较长,请耐心等待...
3.9 脚本执行完成查看pdb状态
show pdbs
3.10 open打开pdb
alter pluggable database open;
show pdbs
3.11 open打开non-CDB
直接重启数据库即可正常open
shutdown immediate;
startup;
4 删除PDB
4.1 案例需求
从容器数据库CDB3中删除可插拔数据库PDB1以及对应的数据文件
4.2 关闭PDB
show pdbs
alter pluggable database pdb1 close immediate;
4.3 删除pdb1
drop pluggable database pdb1 including datafiles;
!ls -lh /u01/app/oracle/oradata/CDB3/pdb1
5 将拔出的PDB插入CDB
5.1 任务需求
从cdb2中拔出orclpdb插入到cdb3中
5.2 启动cdb2实例
export ORACLE_SID=cdb2
sqlplus / as sysdba
show pdbs
5.3 拔出orclpdb
alter pluggable database orclpdb close immediate;
alter pluggable database orclpdb unplug into '/home/oracle/orclpdb.xml';
col pdb_name for a10
select pdb_name,STATUS from cdb_pdbs where pdb_name='ORCLPDB';
drop pluggable database orclpdb keep datafiles;
keep datafiles:保留数据文件,不删除数据文件
5.4 检查兼容性
使用DBMS_PDB.CHECK_PLUG_COMPATIBILITY函数检查拔出的orclpdb与cdb1是否兼容
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/home/oracle/orclpdb.xml',
pdb_name => 'ORCLPDB')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
返回YES满足兼容性要求
5.5 插入cdb1
create pluggable database orcl_pdb using '/home/oracle/orclpdb.xml' nocopy;
nocopy:使用原来路径下数据文件
5.6 open打开插入的orcl_pdb
alter pluggable database orcl_pdb open;
show pdbs
col file_name for a60
select file_name from cdb_data_files where con_id=3;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】博客园携手 AI 驱动开发工具商 Chat2DB 推出联合终身会员
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步