Oracle Database 23 FreeのdockerでPDBを追加

背景

ローカル開発環境のdockerでoracle-dbを起動して、かつ、dbが複数欲しい場合。PDBの新規追加が考えられる。複数コンテナ（大分重いだろうけど）とか色々考えられるが、せっかくなのでPDBを知るためにもやってみる。

手順

gvenzl/oracle-freeの場合

https://hub.docker.com/r/gvenzl/oracle-free にはORACLE_DATABASEというパラメータがある。ORACLE_DATABASE=PDB1,PDB2,PDB3 という風にカンマ区切りで複数のPDBを生成できる。以下はコマンド例。

docker run -d --name multiple_pdb -p 11521:1521 -e ORACLE_PASSWORD="password" -e ORACLE_DATABASE="PDB1,PDB2,PDB3" gvenzl/oracle-free:23.5

PDBを確認する。

$ docker exec -it multiple_pdb bash
$ sqlplus / as sysdba
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
         4 PDB1                           READ WRITE NO
         5 PDB2                           READ WRITE NO
         6 PDB3                           READ WRITE NO

Oracle Database 23 Freeの場合

手順は https://www.reqtc.com/blog/-pdbseedpdb.html の方が書かれている通り。もしversionが異なる場合はデフォルトのPDBの名前とかデータファイルの位置とかの読み替えが必要と思われる。

コンテナ作成

https://container-registry.oracle.com/ords/ocr/ba/database/free を参考にまずは適当なコンテナを作成する。

docker run --name my_oracle -p 11521:1521 -e ORACLE_PWD="Oracle23" container-registry.oracle.com/database/free:23.5.0.0-lite

以下は正常起動したときのログ。

Expanding oracle data
/home/oracle
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.
Pluggable Database FREEPDB1 opened.


SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Tue Nov 26 10:34:05 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07

SQL>
User altered.

SQL>
User altered.

SQL>
Session altered.

SQL>
User altered.

SQL> Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Pluggable database FREEPDB1 with pdb id - 3 is now marked as NEW.
****************************************************************
2024-11-26T10:34:03.127074+00:00
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB1 opened read write
Completed:             alter pluggable database all open
            alter pluggable database FREEPDB1 save state
Completed:             alter pluggable database FREEPDB1 save state
2024-11-26T10:34:05.723801+00:00
FREEPDB1(3):TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P343 (3802) VALUES LESS THAN (TIMESTAMP' 2024-11-27 00:00:00')
2024-11-26T10:34:58.377918+00:00
TABLE SYS.WRP$_REPORTS: ADDED AUTOLIST FRAGMENT SYS_P402 (3) VALUES (( 1449104036, TO_DATE(' 2024-11-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ))
TABLE SYS.WRP$_REPORTS_DETAILS: ADDED AUTOLIST FRAGMENT SYS_P403 (3) VALUES (( 1449104036, TO_DATE(' 2024-11-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ))
TABLE SYS.WRP$_REPORTS_TIME_BANDS: ADDED AUTOLIST FRAGMENT SYS_P406 (3) VALUES (( 1449104036, TO_DATE(' 2024-11-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ))

PDB追加

コンテナに入ってsqlplusを起動。

$ docker exec -it my_oracle bash
bash-4.4$ sqlplus / as system

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Tue Nov 26 10:38:18 2024
Version 23.5.0.24.07

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07

あとは https://www.reqtc.com/blog/-pdbseedpdb.html の手順を実行する。以下は参照先と全く同一のSQLと結果になっている。。

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
SQL> alter session set container=PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf
/opt/oracle/oradata/FREE/pdbseed/system01.dbf
/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf

SQL> alter session set container=CDB$ROOT ;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> create pluggable database TEST admin user pdbadmin identified by oracle file_name_convert=('/opt/oracle/oradata/FREE/pdbseed/','/opt/oracle/oradata/TEST/');

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
         4 TEST                           MOUNTED
SQL> alter pluggable database TEST open;

Pluggable database altered.