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正式上线了!这次更新可是带来了不少新功能,其中最引人...
小米墨水屏 安卓系统,融合科技... 你知道吗?在科技日新月异的今天,电子阅读器市场也迎来了新的活力。而小米,这个我们熟悉的品牌,最近推出...
系统软件最少的安卓系统,基于最... 你有没有想过,手机系统就像是我们生活的操作系统,有时候太复杂了,让人感觉头都大了。今天,我要给你介绍...
安卓系统关闭应用推荐,安卓系统... 你有没有发现,手机里的安卓系统最近有点儿“小情绪”,总是给你推荐一些你根本不感兴趣的应用?别急,今天...
车载安卓系统如何用,智能驾驶体... 你有没有想过,你的车载安卓系统其实是个隐藏的宝库呢?没错,就是那个你每天开车时几乎不离手的那个屏幕,...
安卓系统更新如何取消,```p... 你有没有遇到过这种情况:安卓手机的系统更新推送得让人有点头疼,有时候更新后的系统还各种不适应。别急,...
安卓系统源码修改练习,从零开始... 亲爱的技术爱好者,你是否曾梦想过深入安卓系统的内核,亲手修改源码,让手机变得更加个性化?那就让我们一...
安卓考勤系统论文,基于安卓平台... 你有没有想过,每天打卡上班,是不是也能变得有趣起来呢?没错,就是那个我们每天都要面对的安卓考勤系统。...
安卓系统哪家流畅度,安卓系统流... 手机里的安卓系统,就像是每个人的小世界,各有各的风采。但说到流畅度,这可是大家最关心的问题了。今天,...
安卓开不了定位系统,安卓设备定... 最近是不是发现你的安卓手机定位系统突然罢工了?别急,别慌,今天就来给你详细解析一下这个问题,让你轻松...
安卓系统怎么设置airpod,... 你有没有发现,自从AirPods问世以来,它就成为了科技界的宠儿?这款无线耳机不仅音质出众,而且连接...