2021-07-23

使用Oracle 12c容器服务创建数据库

1) 启动Oracle服务容器

docker run -d --name oracle -p1521:1521 -v OracleDBData:/ORCL store/oracle/database-enterprise:12.2.0.1-slim

参考：https://hub.docker.com/u/dhyuan/content/sub-2794a3fe-cd52-4dcb-bf8e-3fda40f02a83

通过docker inspect oracle 可以看到被映射在 /var/lib/docker/volumes/OracleDBData

"Mounts": [
    {
        "Type": "volume",
        "Name": "OracleDBData",
        "Source": "/var/lib/docker/volumes/OracleDBData/_data",
        "Destination": "/ORCL",
        "Driver": "local",
        "Mode": "z",
        "RW": true,
        "Propagation": ""
    }
],

2）进入Oracle，执行sqlplus。

oracle 12c 是多租户数据库，有个核心概念CDB，PDB需要理解。进入容器oracle：
参考： https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89236

docker exec -it oracle bash

sqlplus / as sysdba

show user
show con_name
show pdbs

查看当前用户连接到的数据库是CDB还是PDB。

SELECT NAME, CDB, CON_ID FROM V$DATABASE;

3）创建PDB

创建PDB之前需要设置 FILE_NAME_CONVERT 或者 db_create_file_dest
参考：https://smarttechways.com/2019/03/05/ora-65016-file_name_convert-must-be-specified/
https://segmentfault.com/a/1190000038344836
https://dba.stackexchange.com/questions/190451/unable-to-connect-to-pluggable-database-in-oracle-12c

检查PDB$SEED database文件的位置：

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> select FILE_NAME from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/ORCL/pdbseed/system01.dbf
/u02/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/ORCL/pdbseed/xdb01.dbf

SQL>

因为创建PDB时需要连接在CDB$ROOT，所以先检查当前连接。如果不是，则切换到CDB。

SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB$SEED

SQL>  alter session set container = CDB$ROOT;

Session altered.

SQL> select sys_context ('USERENV', 'CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

创建一个PDB: YDPDB 用户：ydhbqb

    SQL> CREATE PLUGGABLE DATABASE YDPDB ADMIN USER ydhbqb IDENTIFIED BY ydhbqb0531 FILE_NAME_CONVERT=('/u02/app/oracle/oradata/ORCL/pdbseed/','/u02/app/oracle/oradata/ORCL/pdbseed/YDPDB/');

赋予sysdba权限：

SQL>  grant SYSDBA to ydhbqb;

Grant succeeded.
   

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 YDPDB              MOUNTED
SQL> 

SQL> alter pluggable database YDPDB 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 YDPDB                  READ WRITE NO
SQL> 

让Oracle记住这个状态：

alter pluggable database YDPDB save state;

关闭PDB

alter pluggable database YDPDBclose immediate;

查看错误信息

select * from PDB_PLUG_IN_VIOLATIONS;

4） 连接到PDB。

1） 用sqlplus连接到PDB。

sqlplus ydhbqb/ydhbqb0531@localhost:1521/ydpdb.localdomain

2） Spring DS

spring:
    datasource:
        url:  jdbc:oracle:thin:@localhost:1521/ydpdb.localdomain
        username: ydhbqb
        password: ydhbqb0531

DevOps