Oracle 12c CDB PDB

本文主要是介绍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的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---