Oracle 12c CDB & PDB

拼搏现实的明天。 2022-12-23 00:42 195阅读 0赞

查看当前容器  
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

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

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\]$