ORACLE在系统级别修改PDB

民生 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;

本文地址： http://cn.yexian114.com//minsheng/26030.html