ORACLE在系统级别修改PDB
admin
2024-04-15 00:41:22
0

可以使用ALTER SYSTEM命令动态修改PDB,如果当前容器是PDB,那么可以执行以下命令。

ALTER SYSTEM FLUSH { SHARED_POOL | BUFFER_CACHE | FLASH_CACHE };ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION;ALTER SYSTEM SET USE_STORED_OUTLINES;ALTER SYSTEM {SUSPEND | RESUME};ALTER SYSTEM CHECKPOINT;ALTER SYSTEM CHECK DATAFILES;ALTER SYSTEM REGISTER;ALTER SYSTEM {KILL | DISCONNECT} SESSION;ALTER SYSTEM SET 初始化参数

 对于修改的初始化参数,若表v$system_parameter中的字段ISPDB_MODIFIABLE='TRUE',说明在PDB级别可以修改,并不会影响CDB的参数值。

SQL> desc v$system_parameter;Name                                      Null?    Type----------------------------------------- -------- ----------------------------NUM                                                NUMBERNAME                                               VARCHAR2(80)TYPE                                               NUMBERVALUE                                              VARCHAR2(4000)DISPLAY_VALUE                                      VARCHAR2(4000)DEFAULT_VALUE                                      VARCHAR2(255)ISDEFAULT                                          VARCHAR2(9)ISSES_MODIFIABLE                                   VARCHAR2(5)ISSYS_MODIFIABLE                                   VARCHAR2(9)ISPDB_MODIFIABLE                                   VARCHAR2(5)ISINSTANCE_MODIFIABLE                              VARCHAR2(5)ISMODIFIED                                         VARCHAR2(8)ISADJUSTED                                         VARCHAR2(5)ISDEPRECATED                                       VARCHAR2(5)ISBASIC                                            VARCHAR2(5)DESCRIPTION                                        VARCHAR2(255)UPDATE_COMMENT                                     VARCHAR2(255)HASH                                               NUMBERCON_ID                                             NUMBERSQL> select count(*) from v$system_parameter where ISPDB_MODIFIABLE='TRUE';COUNT(*)
----------222SQL> show user;
USER is "SYS"
SQL> 

在数据库级别修改PDB

在数据库基本修改PDB,主要是使用ALTER PLUGGABLE DATABASE 命令。

[oracle@oracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 30 21:13:10 2022
Version 19.3.0.0.0Copyright (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.0SQL> show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 open;Pluggable database altered.SQL> 
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 close immediate;Pluggable database altered.SQL> alter pluggable database cndbapdb2 open read only;Pluggable database altered.SQL> 

在线查看数据库文件,代码如下:

SQL> show user;
USER is "SYS"
SQL> show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=CNDBAPDB22  ;Session altered.SQL> alter session set container=CNDBAPDB2;Session altered.SQL> alter pluggable database datafile '/u02/oradata/CDB1/cndbapdb2/cndba01.dbf' online;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------7 CNDBAPDB2                      READ WRITE NO
SQL> select name from v$datafile;NAME
--------------------------------------------------------------------------------
/u02/oradata/CDB1/cndbapdb2/system01.dbf
/u02/oradata/CDB1/cndbapdb2/sysaux01.dbf
/u02/oradata/CDB1/cndbapdb2/undotbs01.dbf
/u02/oradata/CDB1/cndbapdb2/cndba01.dbfSQL> 

修改默认表空间,代码如下:

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE cndba_tbs;ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE cndba_temp;

设置PDB的存储大小,代码如下:

ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 20G);ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE UNLIMITED);ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

设置强制记录日志,代码如下:

ALTER PLUGGABLE DATABASE NOLOGGING;ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

启动/关闭PDB

打开模式:

  • OPEN READ WRITE

读写模式,允许用户进行读写操作

  • OPEN READ ONLY

只读模式,只允许用户读取数据,无法写数据

  • OPEN MIGRATE

当前模式,可以执行升级脚本操作(ALTER DATABASE OPEN UPGRADE)

  • MOUNT

不允许进行任何修改操作,只允许数据库管理员访问,无法读取/修改数据文件。此时内存中关于PDB的信息会被移除,可以进行冷备份。

打开PDB

OPEN READ WRITE

SQL> show user;
USER is "SYS"
SQL> show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> STARTUP PLUGGABLE DATABASE CNDBAPDB2 OPEN READ WRITE;
Pluggable Database opened.
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 open read write;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 open;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 

OPEN READ ONLY

SQL> 
SQL> alter pluggable database cndbapdb2 open READ ONLY;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      READ ONLY  NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> STARTUP PLUGGABLE DATABASE CNDBAPDB2 OPEN READ ONLY;
Pluggable Database opened.
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      READ ONLY  NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database cndbapdb2 close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 

OPEN MIGRATE(以升级脚本的模式打开)

[oracle@oracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 30 21:49:44 2022
Version 19.3.0.0.0Copyright (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.0SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 
SQL> 
SQL> ALTER PLUGGABLE DATABASE cndbapdb OPEN UPGRADE;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MIGRATE    YES6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 
SQL> alter pluggable database cndbapdb close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL>

同时打开/关闭多个PDB

SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> STARTUP PLUGGABLE DATABASE CNDBAPDB2,CNDBAPDB3,CNDBAPDB OPEN READ WRITE;
Pluggable Database opened.
SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       READ WRITE NO6 CNDBAPDB3                      READ WRITE NO7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database CNDBAPDB2,CNDBAPDB3,CNDBAPDB close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database CNDBAPDB2,CNDBAPDB3,CNDBAPDB OPEN READ WRITE;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       READ WRITE NO6 CNDBAPDB3                      READ WRITE NO7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> alter pluggable database CNDBAPDB2,CNDBAPDB3,CNDBAPDB close immediate;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 

打开所有的PDB和关闭所有的PDB

SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 
SQL> 
SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           READ WRITE NO5 CNDBAPDB                       READ WRITE NO6 CNDBAPDB3                      READ WRITE NO7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           MOUNTED4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 

除了CNDBAPDB4_FRESH(只能只读模式打开)外,打开其他PDB,

SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           MOUNTED4 PDB2                           MOUNTED5 CNDBAPDB                       MOUNTED6 CNDBAPDB3                      MOUNTED7 CNDBAPDB2                      MOUNTED8 CNDBAPDB4_FRESH                MOUNTED
SQL> 
SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT CNDBAPDB4_FRESH OPEN READ WRITE;Pluggable database altered.SQL> show pdbs;CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED                       READ ONLY  NO3 PDB1                           READ WRITE NO4 PDB2                           READ WRITE NO5 CNDBAPDB                       READ WRITE NO6 CNDBAPDB3                      READ WRITE NO7 CNDBAPDB2                      READ WRITE NO8 CNDBAPDB4_FRESH                MOUNTED
SQL> 

保存当前PDB的打开状态

## 保存一个PDB的打开状态
ALTER PLUGGABLE DATABASE CNDBAPDB SAVE STATE;## 保存所有PDB的打开状态
ALTER PLUGGABLE DATABASE ALL SAVE STATE;## 保存多个PDB的打开状态。
ALTER PLUGGABLE DATABASE CNDBAPDB,CNDBAPDB2 SAVE STATE;## 除了CNDBAPDB4_FRESH外,打开其他PDB状态,ALTER PLUGGABLE DATABASE ALL EXCEPT CNDBAPDB4_FRESH SAVE STATE;

关闭PDB

alter pluggable database cndbapdb close immediate;SHUTDOWN IMMEDIATE;

相关内容

热门资讯

安卓10系统省电不,安卓10系... 你有没有发现,自从升级到安卓10系统,手机续航能力好像大不如前了?别急,今天就来给你揭秘安卓10系统...
cm14安卓系统,深度定制与极... 你有没有发现,你的安卓手机最近是不是有点不一样了?是不是觉得系统运行得更加流畅,界面也更加美观了呢?...
平板安卓系统咋样升级,轻松实现... 你那平板安卓系统是不是有点儿卡,想给它来个升级大变身?别急,让我来给你详细说说平板安卓系统咋样升级,...
安卓原系统在哪下载,探索纯净体... 你有没有想过,为什么安卓手机那么受欢迎?那是因为它的系统——安卓原系统,它就像是一个充满活力的魔法师...
安卓系统procreate绘图... 你有没有发现,现在手机上画画变得越来越流行了?尤其是用安卓系统的手机,搭配上那个神奇的Procrea...
电视的安卓系统吗,探索安卓电视... 你有没有想过,家里的电视是不是也在悄悄地使用安卓系统呢?没错,就是那个我们手机上常用的安卓系统。今天...
苹果手机系统操作安卓,苹果iO... 你有没有发现,身边的朋友换手机的时候,总是对苹果和安卓两大阵营争论不休?今天,咱们就来聊聊这个话题,...
安卓系统换成苹果键盘,键盘切换... 你知道吗?最近我在想,要是把安卓系统的手机换成苹果的键盘,那会是怎样的体验呢?想象那是不是就像是在安...
小米操作系统跟安卓系统,深度解... 亲爱的读者们,你是否曾在手机上看到过“小米操作系统”和“安卓系统”这两个词,然后好奇它们之间有什么区...
miui算是安卓系统吗,深度定... 亲爱的读者,你是否曾在手机上看到过“MIUI”这个词,然后好奇地问自己:“这玩意儿是安卓系统吗?”今...
安卓系统开机启动应用,打造个性... 你有没有发现,每次打开安卓手机,那些应用就像小精灵一样,迫不及待地跳出来和你打招呼?没错,这就是安卓...
小米搭载安卓11系统,畅享智能... 你知道吗?最近小米的新机子可是火得一塌糊涂,而且听说它搭载了安卓11系统,这可真是让人眼前一亮呢!想...
安卓2.35系统软件,功能升级... 你知道吗?最近在安卓系统界,有个小家伙引起了不小的关注,它就是安卓2.35系统软件。这可不是什么新玩...
安卓系统设置来电拦截,轻松实现... 手机里总是突然响起那些不期而至的来电,有时候真是让人头疼不已。是不是你也想摆脱这种烦恼,让自己的手机...
专刷安卓手机系统,安卓手机系统... 你有没有想过,你的安卓手机系统是不是已经有点儿“老态龙钟”了呢?别急,别急,今天就来给你揭秘如何让你...
安卓系统照片储存位置,照片存储... 手机里的照片可是我们珍贵的回忆啊!但是,你知道吗?这些照片在安卓系统里藏得可深了呢!今天,就让我带你...
华为鸿蒙系统不如安卓,挑战安卓... 你有没有发现,最近手机圈里又掀起了一股热议?没错,就是华为鸿蒙系统和安卓系统的较量。很多人都在问,华...
安卓系统陌生电话群发,揭秘安卓... 你有没有遇到过这种情况?手机里突然冒出好多陌生的电话号码,而且还是一个接一个地打过来,简直让人摸不着...
ios 系统 安卓系统对比度,... 你有没有发现,手机的世界里,iOS系统和安卓系统就像是一对双胞胎,长得差不多,但细节上却各有各的特色...
安卓只恢复系统应用,重拾系统流... 你有没有遇到过这种情况?手机突然卡顿,或者某个应用突然罢工,你一气之下,直接开启了“恢复出厂设置”大...