创建可插拔数据库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');

如果报错如下：

点击跳转解决方法：远程克隆non-CDB orcl报错：ORA-17628: Oracle error 1031 returned by remote Oracle server ORA-01031: insufficient privileges...

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;