本文主要是介绍Oracle 12c CDB PDB，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Oracle 12c CDB & PDB
查看当前容器
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select name,cdb,open_mode,con_id from v$database;
NAME CDB OPEN_MODE CON_ID
--------- --- -------------------- ----------
ORCL YES READ WRITE 0
查看当前容器下的pdb
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
SQL>
查看数据文件存储路径
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
7 rows selected.
新建pdb库：
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2 IDENTIFIED BY "1q2w3e"
STORAGE (MAXSIZE 10G)
DEFAULT TABLESPACE pdb
DATAFILE '/u01/app/oracle/oradata/orcl/pdb2/pdb01.dbf' SIZE 50M AUTOEXTEND ON
PATH_PREFIX = '/u01/app/oracle/oradata/orcl/pdb2/'
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed','/u01/app/oracle/oradata/orcl/pdb2');
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
4 PDB2 MOUNTED
SQL>
在创建完可插拔数据库后，新的PDB处于MOUNTED模式。在新PDB中创建一个新会话前，必须先打开它。因此，我们可以用下面的命令打开它：
alter pluggable database pdb2 open;
如果报错 Warning: PDB altered with errors. 执行：
select message,time from pdb_plug_in_violations;
Sync PDB failed with ORA-959 during 'create user c##dddba identified by * defaul
t tablespace users
container = all'
20-NOV-20 11.54.03.627858 AM
报错因为之前在容器数据库中创建了一个公共用户C##dddba，现在还需要在所有可插入的数据库中创建该用户。这个用户是在数据库正常打开的情况下建立的，但是使用的是自定义表空间《IMPDATA》，当打开可插入数据库pdb2时将同步这个操作，也就是同步创建这个公共用户。但是 pdb2 这个可插入数据库中是没有 IMPDATA 表空间的，因此无法同步创建这个公共用户，最终可插入数据库 pdb2 仅以受限模式打开，状态显示如下（RESTRICTED）：
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE YES
SQL>
解决办法：
这时候去删除公共用户依然不行，因为同步动作已经在队列里面了。因此，唯一能做的就是在 pdb2 中创建该表空间，然后关闭数据库，再次进行同步即可。
create tablespace users datafile '/u01/app/oracle/oradata/orcl/pdb2/users01.dbf' size 10G;
alter pluggable database pdb2 close;
alter pluggable database pdb2 open;
检查容器数据库(CDB)和可插拔数据库(PDB)文件
alter session set container=pdb2;
select con_id, tablespace_name, file_name from cdb_data_files where file_name like '%orcl/pdbseed%' or file_name like '%orcl/pdb2%' order by 1,2;
SQL> alter session set container=pdb2;
Session altered.
SQL> select con_id, tablespace_name, file_name from cdb_data_files where file_name like '%orcl/pdbseed%' or file_name like '%orcl/pdb2%' order by 1,2;
CON_ID TABLESPACE_NAME
---------- ------------------------------
FILE_NAME
--------------------------------------------------------------------------------
4 PDB
/u01/app/oracle/oradata/orcl/pdb2/pdb01.dbf
4 SYSAUX
/u01/app/oracle/oradata/orcl/pdb2/sysaux01.dbf
4 SYSTEM
/u01/app/oracle/oradata/orcl/pdb2/system01.dbf
SQL>
如果要删除pdb数据库(先关闭、再删除)
alter pluggable database pdb2 close;
drop pluggable database pdb2 including datafiles;
切换pdb/cdb
alter session set container=pdb2;
show pdbs;
切换到pdb中创建本地用户
create user test identified by "1q2w3e";
alter session set container=CDB$ROOT;
show pdbs;
要连接pdb，还需要配置监听文件 tnsnames.ora & listener.ora
vim $ORACLE_HOME/network/admin/tnsnames.ora
[oracle@localhost orcl]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain)
)
)
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl.localdomain)
)
)
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2.localdomain)
)
)
vim $ORACLE_HOME/network/admin/listener.ora
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl) #cdb db_name
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = pdborcl) #pdb db_name
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = pdb2) #pdb db_name
(SID_NAME = pdb2)
)
)
lsnrctl stop
lsnrctl start
测试pdb
tnsping pdb2
[oracle@localhost orcl]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-NOV-2020 12:29:30
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 20-NOV-2020 12:29:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "pdb2", status UNKNOWN, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost orcl]$
[oracle@localhost orcl]$
[oracle@localhost orcl]$ tnsping pdb2
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-NOV-2020 12:29:39
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb2.localdomain)))
OK (0 msec)
[oracle@localhost orcl]$
这篇关于Oracle 12c CDB PDB的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
