Oracle 12C或以上版本CDB模式下自动启动PDB

Oracle 12C引入了CDB、PDB的概念，当数据库重启后，CDB启动成功后，PDB均处于mount状态下，此时业务仍无法连接数据库，需要人工干预执行alter pluggable database xxx open。

可以通过创建触发器的方法让PDB自动启动（根据实际需求而定是否需要次功能）

[root@19cpdb ~]# ps -ef | grep smon
root      2092  2044  0 23:10 pts/0    00:00:00 grep --color=auto smon
[root@19cpdb ~]# su - oracle
Last login: Sat Feb 27 00:56:08 CST 2021 on pts/0
[oracle@19cpdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 23:10:38 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1677718944 bytes
Fixed Size                  9135520 bytes
Variable Size             939524096 bytes
Database Buffers          721420288 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

数据库启动后，PDB1为mount状态，需要手工open pdb

SQL> alter pluggable database pdb1 open;
System altered.

SQL> show pdbs

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

通过创建触发器，让PDB自动启动

SQL> CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/
  2    3    4    5    6    7  
Trigger created.

SQL> 
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@19cpdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jun 7 23:15:56 2021
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1677718944 bytes
Fixed Size                  9135520 bytes
Variable Size             939524096 bytes
Database Buffers          721420288 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs

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

查看alert日志

2021-06-07T23:16:46.572889+08:00
Starting background process AQPC
2021-06-07T23:16:46.626712+08:00
AQPC started with pid=48, OS id=3053
2021-06-07T23:16:52.258633+08:00
PDB$SEED(2):Autotune of undo retention is turned on.
2021-06-07T23:16:53.348680+08:00
PDB$SEED(2):Endian type of dictionary set to little
2021-06-07T23:16:57.493734+08:00
PDB$SEED(2):Undo initialization finished serial:0 start:688983 end:688983 diff:0 ms (0.0 seconds)
PDB$SEED(2):Database Characterset for PDB$SEED is AL32UTF8
2021-06-07T23:17:16.409619+08:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
2021-06-07T23:17:16.844635+08:00
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
2021-06-07T23:17:20.641123+08:00
alter pluggable database all open
PDB1(3):Autotune of undo retention is turned on.
2021-06-07T23:17:22.082259+08:00
PDB1(3):Endian type of dictionary set to little
2021-06-07T23:17:24.598143+08:00
PDB1(3):Undo initialization recovery: err:0 start: 715282 end: 716087 diff: 805 ms (0.8 seconds)
2021-06-07T23:17:29.823187+08:00
PDB1(3):[2988] Successfully onlined Undo Tablespace 2.
PDB1(3):Undo initialization online undo segments: err:0 start: 716087 end: 721312 diff: 5225 ms (5.2 seconds)
PDB1(3):Undo initialization finished serial:0 start:715282 end:721479 diff:6197 ms (6.2 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2021-06-07T23:17:48.983033+08:00
===========================================================
Dumping current patch information
===========================================================
Patch Id: 29517242
Patch Description: Database Release Update : 19.3.0.0.190416 (29517242)
Patch Apply Time: 2019-04-18T15:21:17+08:00
Bugs Fixed: 14735102,19697993,20313356,21965541,25806201,25883179,25986062,
26476244,26611353,26872233,27369515,27423500,27666312,27710072,27846298,
27957203,28064977,28072567,28129791,28181021,28210681,28279456,28313275,
28350595,28371123,28379065,28431445,28463226,28489419,28502773,28513333,
28534475,28561704,28569897,28572533,28572544,28587723,28593682,28594086,
28597221,28601957,28605066,28606598,28625862,28627033,28636532,28643718,
28644549,28645570,28646200,28646939,28649388,28655209,28663782,28673945,
28692275,28694872,28696373,28705231,28710385,28710734,28714461,28718469,
28730079,28740708,28760206,28772390,28774416,28777214,28789531,28791852,
28795551,28802734,28804517,28810381,28811560,28815123,28815355,28819640,
28824482,28833912,28835937,28862532,28863432,28873575,28876253,28876639,

在数据库alert日志中，有alter pluggable database all open的动作。