Shulou(Shulou.com)06/01 Report--

This article summarizes the management of CDB and PDBs tasks by excerpts from the 40 ~ (st) ~ 42 chapters of Database Administrator's Guide (12.1.0.2). The excerpt of the translation focuses on operations and examples. For details, please refer to the official document: 40 Administering a CDB with SQL*Plus https://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN1360642 Administering PDBs with SQL*Plus

Https://docs.oracle.com/database/121/ADMIN/cdb_pdb_admin.htm#ADMIN13663

1. CDB management tasks

Table 40-1 Administrative Tasks Common to CDBs and Non-CDBs

Task

Description

Starting up a CDB instance

To start a CDB instance, the current user must be a common user whose current container is the root.

When you open a CDB, its PDBs are mounted. Use the ALTER PLUGGABLE DATABASE statement to modify the open mode of one or more PDBs.

Managing processes

A CDB has one set of background processes shared by the root and all PDBs.

Managing memory

A CDB has a single system global area (SGA) and a single aggregate program glob

Al area (PGA). The memory required by a CDB is the sum of the memory requirements for all of the PDBs that will be part of the CDB.

Managing security

You can create and drop common users and local users in a CDB. You can also grant privileges to and revoke privileges from these users. You can also manage the CONTAINER_DATA attributes of common users.

In addition, grant the following roles to the appropriate users:

Grant the CDB_DBA role to CDB administrators.

Grant the PDB_DBA role to PDB administrators.

Monitoring errors and alerts

A CDB has one alert log for the entire CDB. The name of a PDB is included in records in trace files, when appropriate.

Managing diagnostic data

In a CDB, you can use the Oracle Database fault diagnosability infrastructure and the Automatic Diagnostic Repository (ADR).

Managing control files

A CDB has one control file.

Managing the online redo log and the archived redo log files

A CDB has one online redo log and one set of archived redo log files.

Managing tablespaces

You can create, modify, and drop tablespaces and temporary tablespaces for the root and for individual PDBs. You can also specify a default tablespace, default tablespace type, and a default temporary tablespace for the root. The root has its own set of Oracle-supplied tablespaces, such as the SYSTEM tablespace, and each PDB has its own set of Oracle-supplied tablespaces.

Managing data files and temp files

The root has its own data files, and each PDB has its own data files. In a CDB, you can manage data files and temp files in basically the same way you would manage them for a non-CDB. However, the following exceptions apply to CDBs:

You can limit the amount of storage used by the data files for a PDB by using the STORAGE clause in a CREATE PLUGGABLE DATABASE or ALTER PLUGGABLE DATABASE statement.

There is a default temporary tablespace for the root and for each PDB.

Managing undo

There is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. In a CDB, the UNDO_MANAGEMENT initialization parameter must be set to AUTO, and an undo tablespace is required to manage the undo data.

Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace. Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB. When the current container is a PDB, an attempt to create an undo tablespace fails without returning an error.

Moving data between PDBs

You can move data between PDBs using the same methods that you would use to move data between non-CDBs. For example, you can transport the data or use Data Pump export/import to move the data.

Using Oracle Managed Files

Using Oracle Managed files can simplify administration for both a CDB and a non-CDB.

Using Transparent Data Encryption

Transparent Data Encryption is a feature that enables encryption of individual table columns before storing them in the data file, or enables encryption of entire tablespaces. In a CDB, each PDB has its own master key for Transparent Data Encryption, and, where applicable, the ADMINISTER KEY MANAGEMENT SQL statement enables key management at the CDB level and for individual PDBs.

Using a standby database

Oracle Data Guard can configure a physical standby or a logical standby of a CDB. Data Guard operates on the entire CDB, not on individual PDBs.

Using Oracle Database Vault

Oracle Database Vault policies are scoped to individual PDBs.

Dropping a database

When you drop a CDB, the root, seed, and all of its PDBs (including their data) are also dropped.

You can also drop individual PDBs with the DROP PLUGGABLE DATABASE statement.

2. Manageable features in CDB

Table 40-2 Manageability Features in a CDB

Manageability Feature

Data Location

Data Visibility

Active Session History (ASH)

ASH collects information about active database sessions. You can use this information to analyze and identify performance issues.

Most of the ASH data is stored in memory. A small percentage of the ASH data samples are stored in the root.

ASH data related to a PDB is not included if the PDB is unplugged.

A common user whose current container is the root can view ASH data for the root and for PDBs.

A user whose current container is a PDB can view ASH data for the PDB only.

Alerts

An alert is a notification of a possible problem.

Threshold settings that pertain to a PDB are stored in the PDB.

Alerts posted when thresholds are violated are enqueued into the alert queue in the root.

Threshold settings that pertain to a PDB are included if the PDB is unplugged. Alerts related to a PDB are not included if the PDB is unplugged.

A common user whose current container is the root can view alerts for the root and for PDBs.

A user whose current container is a PDB can view alert thresholds and alerts for the PDB only.

Automated Database Maintenance Tasks

Automated database maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. Automated tasks include automatic optimizer statistics collection, Automatic Segment Advisor tasks, and Automatic SQL Tuning Advisor tasks.

A user can schedule maintenance windows and enable or disable maintenance tasks for the current container only. If the current container is the root, then the changes only apply to the root. If the current container is a PDB, then the changes only apply to the PDB.

Data related to a PDB is stored in the PDB for automatic optimizer statistics collection and the Automatic Segment Advisor. This data is included if the PDB is unplugged.

Automatic SQL Tuning Advisor runs only in the root. See the SQL Tuning Advisor row in this table for information about data collected by Automatic SQL Tuning Advisor.

See the appropriate row in this table for data visibility information about the following manageability features: automatic optimizer statistics collection, Automatic Segment Advisor, and Automatic SQL Tuning Advisor.

Automatic Database Diagnostic Monitor (ADDM)

ADDM can diagnose a database's performance and determine how identified problems can be resolved.

All ADDM runs must be performed in the root. All ADDM results are stored in the root.

ADDM analyzes activity in a PDB within the context of the current analysis target. ADDM does not analyze one PDB at a time. As in previous releases, ADDM runs with a target of either the entire instance or Oracle RAC database.

ADDM results related to a PDB are not included if the PDB is unplugged.

ADDM results are visible only to a common user whose current container is the root. The ADDM results can include information about multiple PDBs. The ADDM results cannot be viewed when the current container is a PDB.

Automatic Optimizer Statistics Collection

Automatic optimizer statistics collection gathers optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics. The statistics gathered by this task are used by the SQL query optimizer to improve the performance of SQL execution.

When an automatic optimizer statistics collection task gathers data for a PDB, it stores this data in the PDB. This data is included if the PDB is unplugged.

A common user whose current container is the root can view optimizer statistics data for PDBs.

A user whose current container is a PDB can view optimizer statistics data for the PDB only.

Automatic Segment Advisor

The Automatic Segment Advisor identifies segments that have space available for reclamation and makes recommendations on how to defragment those segments.

When Automatic Segment Advisor gathers data for a PDB, it stores this data in the PDB. This data is included if the PDB is unplugged.

A common user whose current container is the root can view Automatic Segment Advisor data for PDBs.

A user whose current container is a PDB can view the Automatic Segment Advisor data for the PDB only.

Automatic Workload Repository (AWR)

The AWR collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is stored in the database. The gathered data can be displayed in both reports and views.

AWR data is stored in the root.

AWR data related to a PDB is not included if the PDB is unplugged.

A common user whose current container is the root can view AWR data for the root and for PDBs.

A user whose current container is a PDB can view AWR data for the PDB only.

Database Replay

Database Replay is a feature of Oracle Real Application Testing. Database Replay captures the workload for a database and replays it exactly on a test database.

Information about database captures and replays are stored in the root.

A common user whose current container is the root can view database capture and replay information.

SQL Management Base (SMB)

SMB stores statement logs, plan histories, SQL plan baselines, and SQL profiles in the data dictionary.

SMB data related to a PDB is stored in the PDB. The SMB data related to a PDB is included if the PDB is unplugged.

A common user whose current container is the root can view SMB data for PDBs.

A user whose current container is a PDB can view the SMB data for the PDB only.

SQL Performance Analyzer (SPA)

SPA can analyze the SQL performance impact of SQL tuning and other system changes. SPA is often used with Database Replay.

A common user whose current container is the root can run SPA for any PDB. In this case, the SPA results data is stored in the root and is not included if the PDB is unplugged.

A user whose current container is a PDB can run SPA on the PDB. In this case, the SPA results data is stored in the PDB and is included if the PDB is unplugged.

A common user whose current container is the root can view SPA results data for PDBs.

A user whose current container is a PDB can view the SPA results data for the PDB only.

SQL Tuning Sets (STS)

An STS is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking.

You can use an STS to tune a group of SQL statements or test their performance using SPA.

An STS can be stored in the root or in any PDB. If it is stored in the root, then you can load SQL statements from any PDB into it.

When a PDB is unplugged, an STS stored in the root is not included, even if the STS contains SQL statements from the PDB.

When a PDB is unplugged, an STS stored in the PDB is included.

A common user whose current container is the root can view STS data stored in the root only.

A user whose current container is a PDB can view STS data for the PDB only.

SQL Tuning Advisor

SQL Tuning Advisor optimizes SQL statements that have been identified as high-load SQL statements.

Automatic SQL Tuning Advisor data is stored in the root. It might have results about SQL statements executed in a PDB that were analyzed by the advisor, but these results are not included if the PDB is unplugged.

A common user whose current container is the root can run SQL Tuning Advisor manually for SQL statements from any PDB. When a statement is tuned, it is tuned in any container that runs the statement.

A user whose current container is a PDB can also run SQL Tuning Advisor manually for SQL statements from the PDB. When SQL Tuning Advisor is run manually from a PDB, the results are stored in the PDB from which it is run. In this case, a statement is tuned only for the current PDB, and the results related to a PDB are included if the PDB is unplugged.

When SQL Tuning Advisor is run automatically, the results are visible only to a common user whose current container is the root. These results cannot be viewed when the current container is a PDB.

When SQL Tuning Advisor is run manually by a user whose current container is a PDB, the results are only visible to a user whose current container is that PDB.

3. Manage database objects

In CDB, different containers can contain different database objects.

The Oracle database stores database objects such as tables, indexes, and catalogs. Database objects that belong to schemas are called schema objects, while database objects that do not belong to schemas are called non-schema objects. Root and PDBs contain schemas, and schemas contain schema objects. Root and PDBs can also contain non-schema objects, such as users, roles, tablespaces, directories, and versions.

In CDB, root contains schemas and database objects provided by oracle. Common User provided by oracle, such as SYS and SYSTEM, have these schemas and common database objects. They can also have local objects in root and PDB.

You can create a Common User in root to manage containers in CDB. A user-created Common User can create database objects in root. Oracle recommends that in root, user-created Common User-owned schemas contain only the objects used in database triggers and their definitions. A user-created Common User can also have any type of local object in the PDB.

You can create a Local User in PDB. Local User in PDB can create schema objects and non-schema objects in PDB. You cannot create a Local User in root.

Name resolution in CDB is similar to name resolution in non-CDB, except that the name is resolved in the dictionary context of the user's current container.

4. SQL*PLUS 's method to access the container # locally connect to root

Sqlplus / nolog

Connect system

Connect / as sysdba

# Connect to root through the service name

Connect c##dba@mycdb

# Connect to pdbs

CONNECT sh@salespdb

CONNECT system@salespdb

# switch containers

ALTER SESSION SET CONTAINER = container_name

ALTER SESSION SET CONTAINER = CDB$ROOT

ALTER SESSION SET CONTAINER = PDB$SEED

ALTER SESSION SET CONTAINER = salespdb

5. Execute CODE: DBMS_SQL across containers

DECLARE

C1 INTEGER

Rowcount INTEGER

TaskList VARCHAR2 (32767): =

'DECLARE

PRAGMA AUTONOMOUS TRANSACTION

BEGIN

-- Create the hr.identact table.

EXECUTE IMMEDIATE

'' CREATE TABLE hr.identact

(actionno NUMBER (4) NOT NULL

Action VARCHAR2 (10))''

EXECUTE IMMEDIATE

'' INSERT INTO identact VALUES (1, 'ACTION1')''

-- A commit is required if the tasks include DML.

COMMIT

EXCEPTION

WHEN OTHERS THEN

-- If there are errors, then drop the table.

BEGIN

EXECUTE IMMEDIATE''DROP TABLE identact''

EXCEPTION

WHEN OTHERS THEN

NULL

END

END;'

TYPE containerListType IS TABLE OF VARCHAR2 (128) INDEX BY PLS_INTEGER

ContainerList containerListType

BEGIN

ContainerList (1): = 'PDB1'

ContainerList (2): = 'PDB2'

C1: = DBMS_SQL.OPEN_CURSOR

FOR conIndex IN containerList.first..containerList.last LOOP

DBMS_OUTPUT.PUT_LINE ('Creating in container:' | | containerList (conIndex))

DBMS_SQL.PARSE (c = > C1)

Statement = > taskList

Language_flag = > DBMS_SQL.NATIVE

Edition= > NULL

Apply_crossedition_trigger = > NULL

Fire_apply_trigger = > NULL

Schema = > 'HR'

Container = > containerList (conIndex))

Rowcount: = DBMS_SQL.EXECUTE (c = > C1)

END LOOP

DBMS_SQL.CLOSE_CURSOR (c = > C1)

END

/

6. CDB&PDBs management operations @ CDB management tasks

The operation of managing CDB is through alter database. There are different clause for different container, which can be divided into three parts. One is to modify the entire CDB, the second is to modify only root, and the third is to operate one or more PDB. For more information, please see the following table:

Table 40-3 Statements That Modify Containers in a CDB

Modify Entire CDB

Modify Root Only

Modify One or More PDBs

When connected as a common user whose current container is the root, ALTER DATABASE statements with the following clauses modify the entire CDB:

Startup_clauses

Recovery_clauses

Logfile_clauses

Controlfile_clauses

Standby_database_clauses

Instance_clauses

Security_clause

RENAME GLOBAL_NAME clause

ENABLE BLOCK CHANGE TRACKING clause

DISABLE BLOCK CHANGE TRACKING clause

When connected as a common user whose current container is the root, ALTER DATABASE statements with the following clauses modify the root only:

Database_file_clauses

DEFAULT EDITION clause

DEFAULT TABLESPACE clause

ALTER DATABASE statements with the following clauses modify the root and set default values for PDBs:

DEFAULT TEMPORARY TABLESPACE clause

Flashback_mode_clause

SET DEFAULT {BIGFILE | SMALLFILE} TABLESPACE clause

Set_time_zone_clause

You can use these clauses to set non-default values for specific PDBs.

When connected as a common user whose current container is the root, ALTER PLUGGABLE DATABASE statements with the following clause can modify the open mode of one or more PDBs:

Pdb_change_state

When the current container is a PDB, ALTER PLUGGABLEDATABASE statements with this clause can modify the open mode of the current PDB. See "Modifying a PDB with the ALTER PLUGGABLE DATABASE Statement".

When connected as a common user whose current container is the root, ALTER PLUGGABLE DATABASE statements with the following clause can preserve or discard the open mode a PDB when the CDB restarts:

Pdb_save_or_discard_state

1) modify the entire CDB

For example:

ALTER DATABASE BACKUP CONTROLFILE TO'+ DATA/dbs/backup/control.bkp'

ALTER DATABASE cdb ADD LOGFILE

GROUP 4 ('/ u01qlogsqqorclplash redo04a.loghandlecharacyredo04a.loghandlecharacyredo04b.log')

SIZE 100M BLOCKSIZE 512 REUSE

2) modify root only

Example 40-11 Changing the Default Permanent Tablespace for the Root

ALTER DATABASE DEFAULT TABLESPACE root_tbs

Example 40-12 Bringing a Data File Online for the Root

ALTER DATABASE DATAFILE'/ u02max oracle ONLINE cdbaccount 01.dbf'Univ

Example 40-13 Changing the Default Tablespace Type for the Root

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE

Example 40-14 Changing the Default Temporary Tablespace for the Root

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE root_temp

3) Operation on one or more PDB

Example 40-15 Changing the Open Mode of Listed PDBs

ALTER PLUGGABLE DATABASE salespdb, hrpdb OPEN READ WRITE

ALTER PLUGGABLE DATABASE salespdb

OPEN READ ONLY RESTRICTED

ALTER PLUGGABLE DATABASE salespdb

OPEN UPGRADE

ALTER PLUGGABLE DATABASE ALL

OPEN READ WRITE

ALTER PLUGGABLE DATABASE ALL

OPEN READ WRITE FORCE

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb

CLOSE IMMEDIATE

Example 40-18 Opening a PDB in Read/Write Mode with the STARTUP Command

STARTUP PLUGGABLE DATABASE hrpdb OPEN

STARTUP PLUGGABLE DATABASE hrpdb RESTRICT

STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY RESTRICT

STARTUP PLUGGABLE DATABASE hrpdb OPEN READ ONLY

STARTUP PLUGGABLE DATABASE hrpdb FORCE

4) retain or discard the open mode status of PDB during restart CDB

PDB defaults to mount status when CDB starts, and you can do this:

When CDB restarts, specify SAVE STATE to save the PDBs mode.

For example, if PDB is in read / write mode before restart, PDB is in read / write mode after restart; if PDB is in mount mode before restarting CDB, PDB is also in mount mode after CDB restart.

Specify DISCARD STATE to ignore the open mode of PDBs when CDB is restarted.

When DISCARD STATE is specified for PDB, PDB is always mounted after CDB is restarted.

For PDB in Oracle RAC CDB, save state and discard state affect only the schema of the current instance. Even if multiple instances are specified in the instances clause, they do not affect the schema of other instances.

In PDB in Oracle RAC CDB, if you specify the-pdb option when creating a service, when you start service, it will also open the corresponding pdb

How to query the pdbs of saved state?

Col con_name for a20

Col instance_name for a20

Set linesize 200

Select * from DBA_PDB_SAVED_STATES

You can specify which PDBs to modify in the following ways:

List one or more PDBs.

L specify ALL to modify all PDBs.

L specifies all PDBs except the exception PDBs, except the listed PDBs.

Example 40-23 Preserving the Open Mode of a PDB When the CDB Restarts

ALTER PLUGGABLE DATABASE salespdb SAVE STATE

Example 40-24 Discarding the Open Mode of a PDB When the CDB Restarts

ALTER PLUGGABLE DATABASE salespdb DISCARD STATE

Example 40-25 Preserving the Open Mode of All PDBs When the CDB Restarts

ALTER PLUGGABLE DATABASE ALL SAVE STATE

Example 40-26 Preserving the Open Mode of Listed PDBs When the CDB Restarts

ALTER PLUGGABLE DATABASE salespdb, hrpdb SAVE STATE

Example 40-27 Preserving the Open Mode of All PDBs Except for Listed Ones When the CDB Restarts

ALTER PLUGGABLE DATABASE ALL EXCEPT salespdb, hrpdb SAVE STATE

5) ALTER SYSTEM SET statement in CDB

ALTER SYSTEM RESET OPEN_CURSORS SCOPE = SPFILE

Example 40-28 Setting an Initialization Parameter for All Containers

ALTER SYSTEM SET OPEN_CURSORS = 200CONTAINER = ALL

Example 40-29 Setting an Initialization Parameter for the Root

ALTER SYSTEM SET OPEN_CURSORS = 200CONTAINER = CURRENT

6) DDL statement in CDB

Table 40-8 DDL Statements and the CONTAINER Clause in a CDB

DDL Statement

CONTAINER = CURRENT

CONTAINER = ALL

CREATE USER

Creates a local user in the current PDB.

Creates a common user.

ALTER USER

Alters a local user in the current PDB.

Alters a common user.

CREATE ROLE

Creates a local role in the current PDB.

Creates a common role.

GRANT

Grants a privilege in the local container to a local user, common user, or local role.

The SET CONTAINER privilege can be granted to a user-created common user in the current PDB.

Grants a system privilege or object privilege on a common object to a common user or common role. The specified privilege is granted to the user or role across the entire CDB.

REVOKE

Revokes a privilege in the local container from a local user, common user, or local role.

This statement can revoke only a privilege granted with CURRENT specified in the CONTAINER clause from the specified user or role in the local container. The statement does not affect privileges granted with ALL specified in the CONTAINER clause.

The SET CONTAINER privilege can be revoked from a user-created common user in the current PDB.

Revokes a system privilege or object privilege on a common object from a common user or common role. The specified privilege is revoked from the user or role across the entire CDB.

This statement can revoke only a privilege granted with ALL specified in the CONTAINER clause from the specified common user or common role. The statement does not affect privileges granted with CURRENT specified in the CONTAINER clause. However, any privileges granted locally that depend on the privilege granted commonly that is being revoked are also revoked.

Example 40-30 Creating Local User in a PDB

CREATE USER testpdb IDENTIFIED BY password

DEFAULT TABLESPACE pdb1_tbs

QUOTA UNLIMITED ON pdb1_tbs

CONTAINER = CURRENT

Example 40-31 Creating Common User in a CDB

CREATE USER c##testcdb IDENTIFIED BY password

DEFAULT TABLESPACE cdb_tbs

QUOTA UNLIMITED ON cdb_tbs

CONTAINER = ALL

7) execute SQL script in CDB

Example 40-32 Running the catblock.sql Script in All Containers in a CDB

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl-u SYS

-d $ORACLE_HOME/rdbms/admin-b catblock_output catblock.sql

Example 40-33 Running the catblock.sql Script in Specific PDBs

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl-u SYS-U SYS

-d $ORACLE_HOME/rdbms/admin-l'/ disk1/script_output'-c 'HRPDB SALESPDB'

-b catblock_output catblock.sql

Example 40-34 Running the catblock.sql Script in All Containers Except for Specific PDBs

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl-u SYS

-d $ORACLE_HOME/rdbms/admin-l'/ disk1/script_output'-C 'HRPDB SALESPDB'

-b catblock_output catblock.sql

Example 40-35 Running a SQL Script with Command Line Parameters

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl-u SYS

-d / u01/scripts-b custom_script_output custom_script.sql

'--phr''-- PEnter password for user hr:'

Example 40-36 Running a SQL Statement in All Containers in a CDB

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl-u SYS-e-b select_output-x "SELECT * FROM DUAL"

8) shutdown CDB

SQL*Plus

SRVCTL (When Oracle Restart Is In Use)

SHUTDOWN IMMEDIATE

Srvctl stop database-db db_unique_name-stopoption immediate

SHUTDOWN TRANSACTIONAL

Srvctl stop database-db db_unique_name-stopoption transactional

SHUTDOWN ABORT

Srvctl stop database-db db_unique_name-stopoption abort

Add PDB administrative tasks

Table 42-1 Administrative Tasks Common to PDBs and Non-CDBs

Task

Description

Managing tablespaces

You can create, modify, and drop tablespaces for a PDB. You can specify a default tablespace and default tablespace type for each PDB. Also, there is a default temporary tablespace for each PDB. You optionally can create additional temporary tablespaces for use by individual PDBs.

Managing data files and temp files

Each PDB has its own data files. You can manage data files and temp files in the same way that you would manage them for a non-CDB. You can also limit the amount of storage used by the data files for a PDB by using the STORAGE clause in a CREATE PLUGGABLE DATABASE or ALTER PLUGGABLE DATABASE statement.

Managing schema objects

You can create, modify, and drop schema objects in a PDB in the same way that you would in a non-CDB. You can also create triggers that fire for a specific PDB.

When you manage database links in a CDB, the root has a unique global database name, and so does each PDB. The global name of the root is defined by the DB_NAME and DB_DOMAIN initialization parameters. The global database name of a PDB is defined by the PDB name and the DB_DOMAIN initialization parameter. The global database name of each PDB must be unique within the domain.

9) ALTER PLUGGABLE DATABASE in PDB

Example 42-2 Changing the Open Mode of a PDB

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE

ALTER PLUGGABLE DATABASE OPEN READ ONLY

ALTER PLUGGABLE DATABASE OPEN FORCE

ALTER PLUGGABLE DATABASE OPEN UPGRADE

Example 42-3 Bringing a Data File Online for a PDB

ALTER PLUGGABLE DATABASE DATAFILE'/ u03 make oracle ONLINE pdb1account 01.dbf'

Example 42-4 Changing the Default Tablespaces for a PDB

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE pdb1_tbs

ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb1_temp

Example 42-5 Changing the Default Tablespace Type for a PDB

ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE

Example 42-6 Setting Storage Limits for a PDB

ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 2G)

ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE UNLIMITED)

ALTER PLUGGABLE DATABASE STORAGE UNLIMITED

Example 42-7 Setting the Logging Attribute of a PDB

ALTER PLUGGABLE DATABASE NOLOGGING

Example 42-8 Setting the Force Logging Mode of a PDB

ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING

Example 42-9 Setting the Default Edition for a PDB

ALTER PLUGGABLE DATABASE DEFAULT EDITION = PDB1E3

10) STARTUP, SHUTDOWN commands of PDB

To perform STARTUP and SHUTDOWN, the following conditions must be met:

The current user must have SYSDBA, SYSOPER, SYSBACKUP, or SYSDG administrative rights, which must be granted publicly or locally in PDB. Users must use SYSDBA, SYSOPER, SYSBACKUP, or SYSDG to exercise permissions when connecting.

In addition to using the FORCE option, PDB must be in mount mode to open.

To put PDB in mount mode, PDB must be in open read-only or open read/write mode.

Example 42-10 Opening a PDB in Read/Write Mode with the STARTUP Command

STARTUP OPEN

Example 42-11 Opening a PDB in Read-Only Mode with the STARTUP Command

STARTUP OPEN READ ONLY

Example 42-12 Opening a PDB in Read-Only Restricted Mode with the STARTUP Command

STARTUP RESTRICT OPEN READ ONLY

Example 42-13 Opening a PDB in Read/Write Mode with the STARTUP Command and the FORCE Option

STARTUP FORCE

Example 42-14 Closing a PDB with the SHUTDOWN IMMEDIATE Command

SHUTDOWN IMMEDIATE11) alter system command for PDB

The following alter system commands can be executed in PDB:

L ALTER SYSTEM FLUSH SHARED_POOL

L ALTER SYSTEM FLUSH BUFFER_CACHE

L ALTER SYSTEM ENABLE RESTRICTED SESSION

L ALTER SYSTEM DISABLE RESTRICTED SESSION

L ALTER SYSTEM SET USE_STORED_OUTLINES

L ALTER SYSTEM SUSPEND

L ALTER SYSTEM RESUME

L ALTER SYSTEM CHECKPOINT

L ALTER SYSTEM CHECK DATAFILES

L ALTER SYSTEM REGISTER

L ALTER SYSTEM KILL SESSION

L ALTER SYSTEM DISCONNECT SESSION

L ALTER SYSTEM SET initialization_parameter (for a subset of initialization parameters)

# query the parameters that can be modified in PDB

SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE='TRUE' ORDER BY NAME

Run the ALTER SYSTEM SET initialization_parameter statement to modify the PDB when the current container is PDB. This statement does not affect root or other PDBs. The following table describes the behavior of the SCOPE clause when you use a server parameter file (SPFILE) and run the ALTER SYSTEM SET statement on PDB.

SCOPE Setting

Behavior

MEMORY

The initialization parameter setting is changed in memory and takes effect immediately in the PDB. The new setting affects only the PDB.

The setting reverts to the value set in the root in the any of the following cases:

An ALTER SYSTEM SET statement sets the value of the parameter in the root with SCOPE equal to BOTH or MEMORY, and the PDB is closed and re-opened. The parameter value in the PDB is not changed if SCOPE is equal to SPFILE, and the PDB is closed and re-opened.

The PDB is closed and re-opened.

The CDB is shut down and re-opened.

SPFILE

The initialization parameter setting is changed for the PDB and stored persistently. The new setting takes effect in any of the following cases:

The PDB is closed and re-opened.

The CDB is shut down and re-opened.

In these cases, the new setting affects only the PDB.

BOTH

The initialization parameter setting is changed in memory, and it is changed for the PDB and stored persistently. The new setting takes effect immediately in the PDB and persists after the PDB is closed and re-opened or the CDB is shut down and re-opened. The new setting affects only the PDB.

12) service in PDB

Service can be added, deleted, changed and checked in PDB.

# add: create service

Srvctl add service-db mycdb-service salesrep-pdb salespdb

Or

BEGIN

DBMS_SERVICE.CREATE_SERVICE (

Service_name = > 'salesrep'

Network_name = > 'salesrep.example.com')

END

/

# change: modify service

Srvctl modify service-db mycdb-service salesrep-pdb hrpdb cannot modify the service through the DBMS_SERVICE package. The solution is to delete and create.

# Delete: delete service

Srvctl remove service-db mycdb-service salesrep

Or

BEGIN

DBMS_SERVICE.DELETE_SERVICE (

Service_name = > 'salesrep')

END

/

# check, view dba_services

In PDB in Oracle RAC CDB, if the-pdb option is specified when the service is created, the corresponding pdb will also be open when the service resource is started; when the pdb is closed using the ALTER PLUGGABLE DATABASE command, the crs resource service will automatically offline, but when the ALTER PLUGGABLE DATABASE command is used to start pdb, the service resource will not be started with online

[reference]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.