oracle12C以上版本创建pdb数据库实例实践

1.1 连接数据库

说明：本示例数据库为docker容器部署，实际物理机部署的数据库直接使用sqlplus / as sysdba 进入数据库即可

[admin@localhost ~]$ docker ps
CONTAINER ID   IMAGE           COMMAND       CREATED       STATUS       PORTS                                                                                  NAMES
857c9fee68c2   settle/ora:v1   "/bin/bash"   13 days ago   Up 13 days   0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 0.0.0.0:5500->5500/tcp, :::5500->5500/tcp   容器名
[admin@localhost ~]$ docker exec -it --user oracle 容器名/容器id /bin/bash
[oracle@ora184 /]$ sqlplus / as sysdba

1.2 查询该容器是CDB 还是非CDB

SQL> select name,cdb,open_mode,con_id from v$database;

NAME      CDB OPEN_MODE                CON_ID
--------- --- -------------------- ----------
ORCLCDB   YES READ WRITE                    0

查看所创建的PDB

SQL> show pdbs;

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

2.1创建一个新PDB

SQL> select  name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf


--创建pdb
SQL> create pluggable database 数据库实例 admin user 用户名 identified by 密码 file_name_convert=('/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/数据库实例/');

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 数据库实例                         MOUNTED

注：select name from v$datafile; 可查询file_name_convert的路径

2.2启动一个创建好的pdb

--启动一个创建好的pdb
SQL> alter pluggable database 数据库实例 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 数据库实例                         READ WRITE NO
SQL>

2.3创建表空间

切换到对应的pdb下
ALTER SESSION SET CONTAINER=数据库实例;
创建表空间
CREATE TABLESPACE 数据库实例_INDEX
LOGGING
DATAFILE '/opt/oracle/oradata/ORCLCDB/数据库实例/数据库实例_INDEX01.DBF'
SIZE 100M
AUTOEXTEND ON
NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE 数据库实例_DATA
LOGGING
DATAFILE '/opt/oracle/oradata/ORCLCDB/数据库实例/数据库实例_DATA01.DBF'
SIZE 100M
AUTOEXTEND ON
NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

若是要删除表空间，用如下语句：
drop tablespace 数据库实例_INDEX including contents and datafiles cascade constraint;
drop tablespace 数据库实例_DATA including contents and datafiles cascade constraint;

● 重启数据库

alter pluggable database 数据库实例 close;
alter pluggable database 数据库实例 open;

3. 配置监听文件

监听文件目录在$ORACLE_HOME/network/admin
说明：容器里无法使用vi命令，因此在容器外修改对应配置文件，挂载目录为：/home/admin/oradata/dbconfig/ORCLCDB

3.1 tnsnames.ora 文件内容

 数据库实例=
     (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = 数据库实例)
    )
     )

3.2 listener.ora 文件内容

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orclpdb1)
      (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
      (SID_NAME = orclpdb1)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = 数据库实例)
      (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
      (SID_NAME = 数据库实例)
    )
   )

3.3 测试配置是否成功（需要切换到Oracle用户）

[oracle@ora184 /]$ tnsping 数据库实例

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 31-AUG-2022 14:25:09

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 数据库实例)))
OK (30 msec)

4. 配置启动项

oracle12C中在启动数据库的时候，PDB并不会随着CDB而启动。可以通过创建一个触发器让PDB能够随CDB启动。

 alter session set container=CDB$ROOT;
   CREATE OR REPLACE TRIGGER open_pdbs
     AFTER STARTUP ON DATABASE
   BEGIN
     EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
   END open_pdbs;
   /
   --关闭数据库
   SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   --启动数据库
   SQL> startup
   ORACLE instance started.
   
   Total System Global Area 1610611336 bytes
   Fixed Size                  9136776 bytes
   Variable Size            1040187392 bytes
   Database Buffers          553648128 bytes
   Redo Buffers                7639040 bytes
   Database mounted.
   Database opened.
   SQL>
   

5.创建用户并授权

切换容器:
alter session set container=swapry;

查看当前使用容器：
select sys_context ('USERENV', 'CON_NAME') from dual;

创建用户：

create user 用户 identified by oracle
default tablespace 之前创建的表空间名
temporary tablespace TEMP
profile DEFAULT;

可以考虑给如下权限：
grant connect,resource to  用户;
grant unlimited tablespace to  用户;
grant restricted session to  用户;
grant imp_full_database to 用户;
grant exp_full_database to 用户;