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;

相关内容

热门资讯

安卓怎么传到苹果系统,从安卓到... 你是不是也有过这样的烦恼:手机里存了好多好用的安卓应用,可是一换到苹果系统,就发现这些宝贝们都不见了...
安卓改系统字体app,安卓系统... 你有没有想过,手机上的字体也能变得个性十足?没错,就是那个安卓改系统字体app,它可是让手机界面焕然...
安卓系统重启密码错误,破解与预... 手机突然重启了,屏幕上竟然出现了密码输入的界面!这可怎么办?别急,让我来帮你一步步解决这个安卓系统重...
安卓系统怎么删除相片,照片删除... 手机里的相片越来越多,是不是感觉内存都要不够用了?别急,今天就来教你怎么在安卓系统里轻松删除那些不再...
什么安卓机系统最好,安卓系统最... 你有没有想过,手机里那个默默无闻的系统,其实才是决定你手机体验好坏的关键呢?没错,说的就是安卓机系统...
小米手环8安卓系统,智能生活新... 你有没有注意到,最近小米手环8安卓系统成了大家热议的话题呢?这款智能手环自从上市以来,就凭借其强大的...
虹膜系统怎么换为安卓,技术革新... 你有没有想过,你的虹膜系统怎么换为安卓呢?这可是个挺酷的话题,不是吗?想象你的手机上装了个高科技的虹...
安卓刷苹果mac系统,探索跨平... 你有没有想过,你的安卓手机竟然能变身成为苹果Mac系统的超级战士?没错,这就是今天我要跟你分享的神奇...
安卓系统不模仿苹果,不模仿苹果... 你知道吗?在科技圈里,有一场关于操作系统的大戏正在上演。没错,就是安卓系统和苹果iOS系统之间的较量...
安卓系统计步开启,开启健康生活... 你有没有发现,最近你的手机里多了一个小助手——计步器?没错,就是那个默默记录你每一步的小家伙。今天,...
怎么备份安卓系统 recove... 你有没有想过,如果你的安卓手机突然间像顽皮的小猫一样,把你的照片、视频和重要文件都给“藏”了起来?别...
安卓系统同步功能停用,安卓系统... 最近发现了一个让人有点小郁闷的消息——安卓系统的同步功能竟然被停用了!这可真是让人有点措手不及呢。想...
安卓系统的平板重装系统,轻松恢... 你那安卓平板是不是突然间卡得跟蜗牛似的,系统反应慢得跟乌龟赛跑似的?别急,今天就来给你支个招,教你怎...
安卓操作系统语言,引领智能时代... 你知道吗?在手机世界里,有一个超级厉害的操作系统,它就是安卓!这个操作系统可是全球最流行的,几乎每个...
安卓系统声音录音软件,声音记录... 你有没有想过,在安卓手机上,那些美妙的旋律、有趣的对话或者重要的会议内容,如何变成你随时可以回顾的宝...
coloros系统和安卓9,创... 你知道吗?最近手机圈里可是热闹非凡呢!一款名为ColorOS的系统,还有那个大家熟悉的安卓9,它们俩...
安卓个推系统搭建,基于个推系统... 你有没有想过,自己的手机里那些推送消息是怎么悄无声息地出现在你眼前的?没错,就是安卓个推系统在默默为...
设置系统时间app安卓,安卓时... 你有没有想过,手机里那个默默无闻的系统时间,竟然能通过一个小巧的App变得如此有趣和个性化?没错,今...
安卓系统输出开关量,安卓系统开... 你有没有想过,你的安卓手机里竟然隐藏着这么一个神奇的开关量输出功能?没错,就是那个你可能从未留意过的...
安卓系统音乐软件推荐,五大热门... 你有没有发现,手机里音乐软件那么多,挑一款适合自己的真心不容易啊!安卓系统上的音乐软件更是五花八门,...