使用Docker搭建Oracle19c运行环境

一、拉取镜像（这里是我使用oracle官方的dockerfile构建的镜像）

$ docker pull registry.cn-hangzhou.aliyuncs.com/it-boy/oracle19c
Using default tag: latest
latest: Pulling from it-boy/oracle19c
f09c1d3b7e7b: Pull complete
e21babc95602: Pull complete
0f5197b95f24: Pull complete
81249adac3a0: Pull complete
2bd243282769: Downloading [=======================>                           ]  1.241GB/2.611GB
c97d0f42641c: Download complete

二、启动容器（这里我用作测试就没挂载数据目录,有需要 可以配置挂载到容器的/opt/oracle/oradata目录）

docker run -d --name oracle-19c --restart=always \
 -e ORACLE_SID=ORCL \
 -e ORACLE_PDB=TEST \
 -e ORACLE_PWD=123456 \
  -p 1521:1521  -p  5500:5500 \
registry.cn-hangzhou.aliyuncs.com/it-boy/oracle19c:latest

三、创建用户

从Oracle 12C开始引入了CDB与PDB的新特性，在ORACLE 12C数据库引入的多租用户环境（Multitenant Environment）中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。在ORACLE 12C之前，实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。下面是官方文档关于CDB与PDB的关系图。

通常在CDB上建立的用户是common user，新建用户名前要加C##。在PDB上创建的用户是local user。

3.1 在CDB上创建用户

3.1.1 连接并创建用户

$ docker exec -it oracle-19c /bin/bash
[oracle@2079a6e391ad ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 11 13:44:08 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>select name,cdb from v$database;

NAME      CDB
--------- ---
ORCL      YES

SQL> create user C##test identified by testpass;

User created.

3.1.2 授权

SQL> grant dba,connect,resource,create view to C##test;

Grant succeeded.
SQL> grant create session to C##test;
SQL> grant select any table to C##test;
SQL> grant update any table to C##test;
SQL> grant insert any table to C##test;
SQL>  grant delete any table to C##test;

3.1.3 删除用户

SQL> drop user C##test cascade;

User dropped.

3.2 在PDB上创建用户

3.2.1 查看PDB name并进入

SQL> select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

    PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
      DBID STATUS     CREATION_SCN
---------- ---------- ------------
         3
TEST
1104146582 NORMAL          2123203

         2
PDB$SEED
2864464127 NORMAL          2002450

    PDB_ID
----------
PDB_NAME
--------------------------------------------------------------------------------
      DBID STATUS     CREATION_SCN
---------- ---------- ------------


SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
         2 2864464127
PDB$SEED
READ ONLY

         3 1104146582
TEST
READ WRITE

    CON_ID       DBID
---------- ----------
NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------


SQL> alter session set container=TEST;

3.2.2 在PDB上创建用户并授权

SQL> create user test2 identified by test2pass;

User created.

SQL> grant dba,connect,resource,create view to test2;

Grant succeeded.

SQL> grant select any table to test2;

Grant succeeded.

3.2.3 登录方式 sqlplus 用户名/密码@PDB

[oracle@2079a6e391ad ~]$ sqlplus test2/test2pass@TEST

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 11 14:02:52 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

四、SpringBoot中配置连接方式

4.1 CDB模式

spring.datasource.url=jdbc:oracle:thin:@192.168.186.10:1521:ORCL
spring.datasource.username=C##test
spring.datasource.password=testpass

4.2 PDB模式

spring.datasource.url=jdbc:oracle:thin:@//192.168.186.10:1521/TEST
spring.datasource.username=test2
spring.datasource.password=test2pass