登录
当前位置： 首页 > 工具软件 > cdb > 使用案例 >

创建CDB与PDB

戚良弼
2023-12-01

假设已有一个容器数据库ORCLCDB，含可插拔数据库orclpdb1和orclpdb2。
创建新的容器数据库CDB2，也就是新建一个实例。其实dbca是最简单的，但我没有图形界面:

dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb2 -sid cdb2 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword Welcome1\
 -systemPassword Welcome1\
 -createAsContainerDatabase true \
 -numberOfPDBs 2 \
 -pdbName pdb \
  -pdbAdminPassword Welcome1\
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 1536 \
 -storageType FS \
 -datafileDestination "/u01/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

Copying database files
1% complete
13% complete
25% complete
Creating and starting Oracle instance
26% complete
30% complete
31% complete
35% complete
38% complete
39% complete
41% complete
Completing Database Creation
42% complete
43% complete
44% complete
46% complete
49% complete
50% complete
Creating Pluggable Databases
55% complete
75% complete
Executing Post Configuration Actions
100% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/cdb2/cdb2.log" for further details.

验证：

$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ORCLCDB:/opt/oracle/product/12.2.0.1/dbhome_1:Y
cdb2:/opt/oracle/product/12.2.0.1/dbhome_1:N

注意cdb2是小写。
验证pdb已创建：

$ . oraenv
ORACLE_SID = [ORCLCDB] ? cdb2
The Oracle base remains unchanged with value /opt/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 2 22:38:19 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

查看口令和spfile文件：

$ cd $ORACLE_HOME/dbs
$ ls -l orapw* spfile*
-rw-r-----. 1 oracle oinstall 3584 Sep  2 22:27 orapwcdb2
-rw-r-----. 1 oracle oinstall 3584 Aug 28 09:36 orapwORCLCDB
-rw-r-----. 1 oracle oinstall 2560 Sep  2 22:27 spfilecdb2.ora
-rw-r-----. 1 oracle oinstall 3584 Sep  2 22:13 spfileORCLCDB.ora

在cdb2中创建可插拔数据库orclpdb2:

$ mkdir $ORACLE_BASE/oradata/cdb2/orclpdb2
$ sqplus / as sysdba
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdb2

SQL> CREATE PLUGGABLE DATABASE orclpdb2 ADMIN USER pdbadmin
IDENTIFIED BY Welcome1 ROLES=(CONNECT)
CREATE_FILE_DEST='/u01/app/oracle/oradata/cdb2/orclpdb2';
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 ORCLPDB2                       MOUNTED
SQL> alter pluggable database orclpdb2 open ;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 ORCLPDB2                       READ WRITE NO

服务中已经有了新建的CDB和PDB：

$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 02-SEP-2019 22:44:30

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-SEP-2019 19:28:12
Uptime                    0 days 3 hr. 16 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle-12201-vagrant/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle-12201-vagrant)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "90e724bb8f647897e0530100007f48a8" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "90e725a8fbcc78ffe0530100007ff088" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "9193d307767c448be0530100007f5a3d" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "9193fcd7ce2d4a20e0530100007f6f2a" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 2 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "orclpdb2" has 2 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

当看到一个服务下有多个实例时，就知道犯了个错误，就是在一个监听下，pdb是不能重名的。
参照文档：

The PDB name must be unique in the CDB, and it must be unique within the scope of all the CDBs whose instances are reached through a specific listener.

如果不停连接，发现其会使用Round-Robin方式依次连接服务下的各实例：

[oracle@oracle-12201-vagrant admin]$ sqlplus sys/Welcome1@orclpdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 2 22:56:26 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT name FROM v$database;

NAME
---------
ORCLCDB

SQL> conn sys/Welcome1@orclpdb1 as sysdba
Connected.
SQL> SELECT name FROM v$database;

NAME
---------
CDB2
SQL> conn sys/Welcome1@orclpdb1 as sysdba
Connected.
SQL> SELECT name FROM v$database;

NAME
---------
ORCLCDB

这是我们可以修改PDB的名字，或者创建新的监听。不过我们推荐前者。
修改PDB名称参见MOS 2439885.1
通过以上秘籍，我们将可插拔数据库名称改为PDB1和PDB2.

这回监听正常了：

$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 02-SEP-2019 23:18:58

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-SEP-2019 19:28:12
Uptime                    0 days 3 hr. 50 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle-12201-vagrant/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle-12201-vagrant)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "90e724bb8f647897e0530100007f48a8" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "90e725a8fbcc78ffe0530100007ff088" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "9193d307767c448be0530100007f5a3d" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "9193fcd7ce2d4a20e0530100007f6f2a" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "cdb2" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb2" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully

使用easy connect连接：

$ sqlplus sys/Welcome1@localhost:1521/cdb2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 2 23:20:27 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oracle-12201-vagrant admin]$ sqlplus sys/Welcome1@localhost:1521/pdb1 as sysdba
...

可是不用easy connect，用网络连接就连不上：

$ sqlplus sys/Welcome1@cdb2

SQL*Plus: Release 12.2.0.1.0 Production on Mon Sep 2 23:24:54 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:

我们需要在tnsnames.ora中添加网络连接条目：

ORCLCDB=localhost:1521/ORCLCDB
ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

# new entries for container DB: CDB2 & PDB1
CDB2=localhost:1521/cdb2
PDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB1)
  )
)

这样网络连接就可以了：

$ sqlplus sys/Welcome1@cdb2 as sysdba
$ sqlplus sys/Welcome1@pdb1 as sysdba

以上可以看出，tnsnames.ora不是必需的，它将easy connect后面部分定义成了网络服务名。
再回头看一下tnsnames.ora中的网络服务：

PDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PDB1)
  )
)

其中ADDRESS定义了listener，CONNECT_DATA定义了服务，此为数据库服务。
接下来准备删除cdb2中的pdb2，先看一下数据文件：

SQL> alter session set container=pdb2;

Session altered.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/cdb2/orclpdb2/CDB2/9193FCD7CE2D4A20E0530100007F6F2A/datafile/o1_mf_system_gptbmk4p_.dbf
SYSTEM

/u01/app/oracle/oradata/cdb2/orclpdb2/CDB2/9193FCD7CE2D4A20E0530100007F6F2A/datafile/o1_mf_sysaux_gptbmk4z_.dbf
SYSAUX

/u01/app/oracle/oradata/cdb2/orclpdb2/CDB2/9193FCD7CE2D4A20E0530100007F6F2A/datafile/o1_mf_undotbs1_gptbmk50_.dbf
UNDOTBS1

实施删除：

SQL> drop pluggable database pdb2 including datafiles;
drop pluggable database pdb2 including datafiles
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database


SQL> alter session set container=cdb$root;

Session altered.

SQL> drop pluggable database pdb2 including datafiles;
drop pluggable database pdb2 including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB2 is not closed on all instances.


SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> drop pluggable database pdb2 including datafiles;

Pluggable database dropped.

SQL> !ls /u01/app/oracle/oradata/cdb2/orclpdb2/CDB2/9193FCD7CE2D4A20E0530100007F6F2A/datafile/o1_mf_system_gptbmk4p_.dbf
ls: cannot access /u01/app/oracle/oradata/cdb2/orclpdb2/CDB2/9193FCD7CE2D4A20E0530100007F6F2A/datafile/o1_mf_system_gptbmk4p_.dbf: No such file or directory

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

可以看到，必须在CDB中删，PDB必须关闭，必须指定including datafiles以删除数据文件。

然后我们从cdb2中的pdb1克隆pdb2，源库需处于只读状态：

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> !mkdir /u01/app/oracle/oradata/cdb2/pdb2

SQL> CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 CREATE_FILE_DEST='/u01/app/oracle/oradata/cdb2/pdb2';

Pluggable database created.

克隆完成后恢复源库读写状态：

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ ONLY  NO
         4 PDB2                           MOUNTED
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO

SQL Developer对于PDB的操作也很方便，不过注意是在DBA页面中操作，此略。
最后把cdb2删除：

$ dbca -silent -deleteDatabase -sourceDB cdb2
Enter SYS user password:

Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file "/opt/oracle/cfgtoollogs/dbca/cdb2.log" for further details.
[oracle@oracle-12201-vagrant admin]$ cat /etc/oratab
#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
ORCLCDB:/opt/oracle/product/12.2.0.1/dbhome_1:Y

参考

  1. https://oracle-base.com/articles/misc/database-configuration-assistant-dbca-silent-mode
  2. https://oracle-base.com/articles/12c/multitenant-pluggable-database-names-12cr1
  3. https://dbaclass.com/article/create-listener-using-netca-utility/
 类似资料：

相关阅读

php文件夹的创建与删除方法创建HTML：PHP服务器端与jQuery客户端jQuery与JavaScript节点创建方法的对比C#验证码的创建与使用示例Yii2中组件的注册与创建方法

相关文章

创建用户创建工程创建仓库Oracle创建表OrientDB创建边

相关问答

创建一个新的领域与asm 4单例httpclient与创建新的httpclient请求没有创建与LiquiBase.Change.core.RawSQLChange相反的内容创建龙与地下城骰子滚轮创建创建查询集参数

相关文档

CSS 创作指南Makeblock 创客平台树莓派与 LinuxRedis 设计与实现Django 入门与实践
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.