关于PDB的几种启动方式 二、触发器启动 三、SAVE ST

发表于

「这是我参与11月更文挑战的第10天，活动详情查看：2021最后一次更文挑战」

自从12C开始支持pdb以来，我们多多少少的接触或是使用了ORACLE的CDB+PDB的模式，对于数据库实例开启后，PDB为mount状态，需要再次开启，我想大家应该也觉得不是很方便。

下面就来聊聊关于PDB启动的几种方式：

一、手动启动

打开数据库实例时，默认PDB是mounted状态，需要手动执行命令打开PDB：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
bash复制代码SQL> show pdbs

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

--启动所有PDB
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

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

这个方法，最为常见，但是每次开库都要去手动执行，在当今自动化运维的社会，显得很不自动化，如果为RAC数据库，需要每个实例都手动去开启。

二、触发器启动

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
sql复制代码--创建触发器，cdb启动时，open所有的pdb
CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
  7  /

Trigger created.

--查看触发器
SQL> select owner,TRIGGER_NAME,TRIGGER_TYPE from dba_triggers where owner='SYS' and TRIGGER_NAME='OPEN_ALL_PDBS';

OWNER		     TRIGGER_NAME	  TRIGGER_TYPE
-------------------- -------------------- ----------------
SYS		     OPEN_ALL_PDBS	  AFTER EVENT

SQL> select text from all_source where type='TRIGGER' AND name='OPEN_ALL_PDBS';

TEXT
-----------------------------------------------------------------------------------
TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;

6 rows selected.

--测试触发器是否生效
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  READ WRITE NO
--关闭所有pdb
SQL> alter pluggable database all close;

Pluggable database altered.
--查看pdb是否全部关闭
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  MOUNTED
--关闭数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--启动数据库实例
SQL> startup
ORACLE instance started.

Total System Global Area 3355441944 bytes
Fixed Size		    9141016 bytes
Variable Size		  704643072 bytes
Database Buffers	 2634022912 bytes
Redo Buffers		    7634944 bytes
Database mounted.
Database opened.
--pdb已自动启动
SQL> show pdbs

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

三、SAVE STATE

通过设置视图DBA_PDB_SAVED_STATES来控制PDB的启动模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
bash复制代码--这里我们先DROP触发器
SQL> drop trigger OPEN_ALL_PDBS;

Trigger dropped.
SQL> 
SQL> select owner,TRIGGER_NAME,TRIGGER_TYPE from dba_triggers where owner='SYS' and TRIGGER_NAME='OPEN_ALL_PDBS';

no rows selected

SQL> select text from all_source where type='TRIGGER' AND name='OPEN_ALL_PDBS';

no rows selected

--设置state为open
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  READ WRITE NO
SQL> 
--记录当前所有pdb的启动状态
SQL> alter pluggable database all save state;

Pluggable database altered.

SQL> set line222
SQL> col con_name for a20
SQL> col instance_name for a20
SQL> select * from dba_pdb_saved_states;

    CON_ID CON_NAME		INSTANCE_NAME		CON_UID GUID				 STATE		RES
---------- -------------------- -------------------- ---------- -------------------------------- -------------- ---
	 3 ORCL 		lucifer 	      251291369 BF269544BE8B17F9E053AC01A8C0447F OPEN		NO

--重启数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3355441944 bytes
Fixed Size		    9141016 bytes
Variable Size		  704643072 bytes
Database Buffers	 2634022912 bytes
Redo Buffers		    7634944 bytes
Database mounted.
Database opened.
--PDB已自动启动
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  READ WRITE NO
--如何取消自动启动
--先关闭pdb
SQL> alter pluggable database ORCL close;

Pluggable database altered.

SQL> show pdbs

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

--记录当前关闭状态
SQL> alter pluggable database ORCL save state;

Pluggable database altered.

SQL> select * from dba_pdb_saved_states;

no rows selected

--重启数据库实例
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3355441944 bytes
Fixed Size		    9141016 bytes
Variable Size		  704643072 bytes
Database Buffers	 2634022912 bytes
Redo Buffers		    7634944 bytes
Database mounted.
Database opened.

--pdb为mounted状态
SQL> show pdbs

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

总结：

三种方式都可以打开PDB，孰优孰劣大家自可斟酌。

个人建议是第三种方式，从12C开始就可以支持，设置简单，方便快捷，缺点是基于实例的，如果是RAC需要实例都需要去保存一下。不像触发器是基于数据库的，当然触发器可以是万能的。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%