ALTER PLUGGABLE DATABASE Syntax-优快云博客

本文链接：https://blog.youkuaiyun.com/linsuhangoracle/article/details/119445699

Alter pluggable database只能对traditional PDB, an application container, or an application PDB使用，可以在cdb$root或是pdb中使用，后者只对此pdb产生影响

基于向后兼容，也可以在pdb下使用alter database来设置pdb.

Alter pluggable database privilege：

To change the open mode of the PDB from mounted to opened or from opened to mounted, the current user must have SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative privilege, and the privilege must be either commonly granted or locally granted in the PDB. The user must exercise the privilege using AS SYSDBA, AS SYSOPER, AS SYSBACKUP, or AS SYSDG, respectively, at connect time.

For all other operations performed using the ALTER PLUGGABLE DATABASE statement, the current user must have the ALTER DATABASEsystem privilege, and the privilege must be either commonly granted or locally granted in the PDB.

alter_pluggable_database::=

(一) pdb_unplug_clause::=

使用xml一般用于重新plug到本地cdb或application container中；使用pdb压缩文件可以用于复制到远程新的location再plug into cdb/application container

A .pdb file enables you to copy a single, compressed file (instead of multiple files) to a new location to plug the application container into a CDB.

The PDB must be closed before it can be unplugged.(In an Oracle Real Application Clusters (Oracle RAC) environment, the PDB must be closed on all instances)

The unplug operation makes some changes in the PDB's data files to record, for example, that the PDB was successfully unplugged. Because it is still part of the CDB, the unplugged PDB is included in an RMAN backup of the entire CDB. Such a backup provides a convenient way to archive the unplugged PDB in case it is needed in the future.

To completely remove the PDB from the CDB, you can drop the PDB. The only operation supported on an unplugged PDB is dropping the PDB. The PDB must be dropped from the CDB before it can be plugged back into the same CDB. A PDB is usable only when it is plugged into a CDB.

如果是unplugging an application root，则它不能包含application pdbs

Note: The PDB must have been opened at least once.

SQL> ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

SQL> ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/sales.pdb';

（二）pdb_settings_clauses::=

上面选项基本在alter database语法中都能找到，所以也可用alter database替代，如：

ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs;

ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb1_temp;

Alter pluggable database add|drop supplemental log data(primary key) columns;

--cdb必须同时开启supplement log但类型可以不同

ALTER PLUGGABLE DATABASE DEFAULT EDITION = PDB1E3;

pdb_storage_clause::=

ALTER PLUGGABLE DATABASE pdbname STORAGE(MAXSIZE 2G MAX_AUDIT_SIZE 2G MAX_DIAG_SIZE 2G);

ALTER PLUGGABLE DATABASE pdbname STORAGE(MAXSIZE UNLIMITED);

ALTER PLUGGABLE DATABASE pdbname STORAGE UNLIMITED;

pdb_logging_clauses::=

logging_clause::=

pdb_force_logging_clause::=

不如使用alter database方便
Alter pluggable database logging| nologging; --PDB表空间默认为LOGGING还是NOLOGGING

Alter pluggable database enable|disable force logging|nologging;

注：cdb$root设置为force logging则pdb只能为force logging

pdb_refresh_mode_clause::=

只对refreshable pdb可用，可以在manual与automatic切换，一旦切为none，就切不回来了.不论是manual还是auto均可手动刷新，刷新时pdb必须为close:

Alter pluggable database pdbname close immediate;

Alter pluggable database pdbname refresh;

SET CONTAINER_MAP

Use this clause to specify the CONTAINER_MAP database property for an application container.

The current container must be the application root. The map_object is of the form [schema.]table. For schema, specify the schema containing table. If you omit schema, then the database assumed that the table is in your own schema. For table, specify a range-, list-, or hash-partitioned table.

CONTAINERS DEFAULT TARGET

Use this clause to specify the default container for DML statements in an application container. You must be connect to the application root.

For container_name, specify the name of the default container. The default container can be any container in the application container, including the application root or an application PDB. You can specify only one default container.

If you specify NONE, then the default container is the CDB root. This is the default.

When a DML statement is issued in the application root without specifying containers in the WHERE clause, the DML statement affects the default container for the application container.

RENAME GLOBAL_NAME TO Clause

CDB使用db_name+db_domain初始参数来定义全局名，PDB使用name of pdb(不是初始参数)+db_domain初始参数来定义全局名。相同cdb中pdb的do_domain必须相同

更改pdb global name前提：

Pdb需要以read write restricted模式打开，所以只有restricted session权限用户才能连接
如果是RAC，除操作节点其它节点必须关闭

Show parameter db_domain

Show pdbs

Alter pluggable database pdb1 close immediate;

Alter pluggable database pdb1 open read write restricted;

Alter session set container=pdb1;

ALTER PLUGGABLE DATABASE pdb1 RENAME GLOBAL_NAME TO pdb2;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 PDB2 READ WRITE YES

可见pdb名字已改。这里因为环境db_domain设置为空所有没有pdb2.example.com这种，oracle会把第一个点号前的字符串替代pdb旧名字

Alter pluggable database close immediate;

Alter pluggable database open;

Oralce自动更改默认的service

Also, Oracle Database changes the name of the default database service for the PDB automatically. Oracle Database also changes the PDB property of all database services in the PDB to the new global name of the PDB. You must close the PDB and open it in read/write mode for Oracle Database to complete the integration of the new PDB service name into the CDB

Oracle Net Services must be configured properly for clients to access database services. You might need to alter your Oracle Net Services configuration as a result of the PDB name change.

（三）pdb_datafile_clause::=

ALTER PLUGGABLE DATABASE DATAFILE '/u03/oracle/pdb1_01.dbf' ONLINE;

不如使用alter database方便

（四）pdb_recovery_clauses

不如使用alter database方便

1) pdb_general_recovery::=

用于备份还原pdb

pdb_general_recovery

This clause lets you control media recovery for the PDB or standby database or for specified tablespaces or files. The pdb_general_recoveryclause has the same semantics as the general_recovery clause of ALTER DATABASE. Refer to the general_recovery clause of ALTER DATABASE for more information.

BACKUP Clauses

Use these clauses to move all of the data files in the PDB into or out of online backup mode (also called hot backup mode). These clauses have the same semantics in ALTER PLUGGABLE DATABASE and ALTER DATABASE. Refer to the "BACKUP Clauses" of ALTER DATABASE for more information.

RECOVERY Clauses

Use these clauses to enable or disable a PDB for recovery. The PDB must be closed—that is, the open mode must be MOUNTED.

Specify ENABLE RECOVERY to bring all data files that belong to a PDB online and enable the PDB for recovery.

Specify DISABLE RECOVERY to take all data files that belong to a PDB offline and disable the PDB for recovery.

（五）pdb_change_state::=

pdb_change_state_from_root::=

在root$cdb中使用可以同时起停多个pdb, 在pdb中使用只启停此pdb

pdb_open::=

ALTER PLUGGABLE DATABASE pdb_name OPEN 默认是以READ WRITE打开pdb的，如果pdb作为物理备库默认是以READ ONLY 打开的

Restricted: 只有restricted session权限的用户才能访问pdb, 正在访问且没有此权限的会话将被中止，他们的事务会被回滚

The open read/write, read-only, and migrate modes can be restricted to users with RESTRICTED SESSION privilege in the PDB.

Force: read only与read write直接切换
Upgrade: 打开库后处于migrate mode，可以执行upgrade script

在pdb处于mounted或read-only状态时，在CDB中对comm users设置：

While a PDB is in mounted or read-only mode, database administrators can create, modify, or drop common users and roles in the CDB. The CDB applies these changes to the PDB when its open mode is changed to open in read/write mode. Before the changes are applied, descriptions of common users and roles in the PDB might be different from the descriptions in the rest of the CDB.

Pdb open时会进行check compatibility 检查，compatibility violation分为两种warning与error, 前者只会在日志报错，后者只有restricted session权限用户才能访问

When a PDB is opened, Oracle Database checks the compatibility of the PDB with the CDB. Each compatibility violation is either a warning or an error. If a compatibility violation is a warning, then the warning is recorded in the alert log, but the PDB is opened normally without displaying a warning message. If a compatibility violation is an error, then a message is displayed when the PDB is opened stating that the PDB was altered with errors, and the errors are recorded in the alert log. You must correct the condition that caused each error. When there are errors, the PDB is opened, but access to the PDB is limited to users with RESTRICTED SESSION privilege so that the compatibility violations can be addressed. You can view descriptions of violations by querying PDB_PLUG_IN_VIOLATIONS view.

2. instances_clause::=

用于Oracle RAC CDB, 如果不指定则只关闭ALTER PLUGGABLE DATABASE pdb_name中的pdb

3. pdb_close::=

close pdb会使pdb处于mounted状态

If you do not specify IMMEDIATE or ABORT, then the PDB is shut down with the normal mode.

When a PDB is in mounted mode, it behaves like a non-CDB in mounted mode. It does not allow changes to any objects, and it is accessible only to database administrators. It cannot read from or write to data files. Information about the PDB is removed from memory caches. Cold backups of the PDB are possible

4. relocate_clause::=

用于在关闭Oracle RAC某个实例时是否同时开启另一个实例

Specify NORELOCATE, the default, to close the PDB in the current instance.

Specify RELOCATE TO and specify an instance name to reopen the PDB in the specified instance.

Specify RELOCATE to reopen the PDB on a different instance that is selected by Oracle Database.

5. pdb_save_or_discard_state::=

save state表示会在cdb重新启动时会保留pdb的状态，如关闭CDB时pdb1处理于read write,则在CDB启动后pdb1仍为read write

Discard state则在CDB重启后所有pdb均为mounted

可通过dba_pdb_saved_states查看pdb此属性

ALTER PLUGGABLE DATABASE salespdb SAVE STATE;

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE;

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE;

application_clauses::= 看到这里了！！！！！！

1. SET PATCH

Use the SET PATCH clause to register the patch number of an application that is already installed in an application container. This clause allows you to assign a patch number to an application that was not patched using the PATCH clauses. This is useful if the application was migrated from a PDB in an earlier Oracle Database release, when the PATCH clauses were not available. The current container can be the application root or an application PDB.

2. SET VERSION

Use the SET VERSION clause to register the version of an application that is already installed in an application container. This clause allows you to assign a name and a version to an application that was not installed using the INSTALL clauses. This is useful if the application was migrated from a PDB in an earlier Oracle Database release, when the INSTALL clauses were not available. The current container can be the application root or an application PDB.