大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

Oracle12cCDB&PDBs管理

本文摘取Database Administrator’s Guide(12.1.0.2)中40,42等章节部分内容,概括介绍管理CDB、PDBs任务。摘取翻译的内容侧重操作和实例,详细内容请参考官方文档:

专注于为中小企业提供成都网站制作、做网站服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业汝州免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上1000家企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。

40  Administering a CDB with SQL*Plus

https://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN13606

42  Administering PDBs with SQL*Plus

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

1.    CDB 管理任务

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.    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.    管理数据库对象

在 CDB 中,不同的容器可以包含不同的数据库对象。

Oracle 数据库存储数据库对象,如表、索引和目录。属于模式的数据库对象称为模式对象,而不属于模式的数据库对象称为非模式对象。 root 和 PDBs 包含模式,模式包含模式对象。 root 和 PDBs 还可以包含非模式对象,比如用户、角色、表空间、目录和版本。

在 CDB 中, root 包含 oracle 提供的模式和数据库对象。 oracle 提供的 Common User ,如 SYS 和 SYSTEM ,拥有这些模式和公共数据库对象。它们还可以拥有 root 和 PDB 中的本地对象。

您可以在 root 中创建 Common User 来管理 CDB 中的容器。用户创建的 Common User 可以在 root 中创建数据库对象。 Oracle 建议,在 root 中,由用户创建的 Common User 拥有的模式只包含数据库触发器及其定义中使用的对象。用户创建的 Common User 还可以拥有 PDB 中的任何类型的本地对象。

您可以在 PDB 中创建 Local User 。 PDB 中的 Local User 可以在 PDB 中创建模式对象和非模式对象。不能在 root 中创建 Local User 。

CDB 中的名称解析类似于非 CDB 中的名称解析,只不过名称是在用户当前容器的字典上下文中解析的。

4.    SQL*PLUS 访问容器的方法

#本地连接到root

sqlplus /nolog

connect system

connect / as sysdba

#通过服务名连接到root

connect c##dba@mycdb

 

#连接到pdbs

CONNECT sh@salespdb

CONNECT system@salespdb

#切换容器

ALTER SESSION SET CONTAINER = container_name

ALTER SESSION SET CONTAINER = CDB$ROOT;

ALTER SESSION SET CONTAINER = PDB$SEED;

ALTER SESSION SET CONTAINER = salespdb;

 

5.    跨容器执行 CODE: DBMS_SQL

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 管理操作

Ø   CDB 管理任务

管理 CDB 的操作通过 alter database ,对于不同 container 有不同的 clause ,大体可以分三部分,一是对整个 CDB 的修改操作,二是仅对 root 的修改操作,三是对 1 个或多个 PDB 的操作;详解见下表:

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)      对整个 CDB 的修改操作

例如:

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

ALTER DATABASE cdb ADD LOGFILE

  GROUP 4 ('/u01/logs/orcl/redo04a.log','/u02/logs/orcl/redo04b.log')

  SIZE 100M BLOCKSIZE 512 REUSE;

 

2)      仅对 root 的修改操作

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 '/u02/oracle/cdb_01.dbf' ONLINE;

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)      对 1 个或多个 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)      restart CDB 时,保留或丢弃 PDB 的 open mode 状态

PDB 在 CDB 启动时默认为 mount 状态,你可以这样做 :

l   当 CDB 重启时,指定 SAVE STATE 以保存 PDBs 模式。

例如,如果 PDB 在重启前处于开读 / 写模式,那么在重启后 PDB 处于开读 / 写模式 ; 如果在重启 CDB 之前 PDB 处于 mount 模式,那么在重启 CDB 之后 PDB 也处于 mount 模式。

l   指定 DISCARD STATE   ,以便在 CDB 重启时忽略 PDBs 的 open 模式。

当为 PDB 指定 DISCARD STATE   时,总是在重新启动 CDB 之后挂载 PDB 。

对于 Oracle RAC CDB 中的 PDB , save state 和 discard state 只影响当前实例的模式。即使在 instances 子句中指定了多个实例,它们也不会影响其他实例的模式。

在 Oracle RAC CDB 中的 PDB ,如果创建 service 时,指定了 -pdb 选项,启动 service 时,同时也会 open 对应的 pdb ;

 

如何查询 saved state 的 pdbs ?

col con_name for a20

col instance_name for a20

set linesize 200

select * from DBA_PDB_SAVED_STATES ;

 

您可以通过以下方式指定要修改哪些 PDBs:

l   列出一个或多个 PDBs 。

l   指定 ALL 来修改所有的 PDBs 。

l   指定例外 PDBs 外的所有 PDBs ,列出的 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)      CDB 中 ALTER SYSTEM SET 语句

ALTER SYSTEM RESET OPEN_CURSORS SCOPE = SPFILE;

Example 40-28 Setting an Initialization Parameter for All Containers

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;

Example 40-29 Setting an Initialization Parameter for the Root

ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = CURRENT;

 

6)      CDB 中 DDL 语句

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)      CDB 中执行 SQL 脚本

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

 

Ø   PDB 管理任务

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)      PDB 中的 ALTER PLUGGABLE DATABASE

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/oracle/pdb1_01.dbf' ONLINE;

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)             PDB 的 STARTUP 、 SHUTDOWN 命令

执行 STARTUP 、 SHUTDOWN 必须符合以下条件:

• 当前用户必须拥有 SYSDBA 、 SYSOPER 、 SYSBACKUP 或 SYSDG 管理权限,权限必须在 PDB 中被公共授予或本地授予。用户必须在连接时分别使用 SYSDBA 、 SYSOPER 、 SYSBACKUP 或 SYSDG 来行使权限。

• 除使用 FORCE 选项外, PDB 必须处于挂载模式才能打开。

• 要将 PDB 置于挂载模式, PDB 必须处于 open 只读或 open read/write 模式。

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 IMMEDIATE

11)             PDB 的 alter system 命令

PDB 中可执行如下 alter system 命令:

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)

 

# 查询 PDB 中可以修改的参数

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

当当前容器是PDB时,运行ALTER SYSTEM SET initialization_parameter语句来修改PDB。该语句不影响root或其他PDBs。下表描述了当您使用服务器参数文件(SPFILE)并在PDB上运行ALTER SYSTEM SET语句时,SCOPE子句的行为。

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)             PDB 中的 service

在 PDB 中可以对 service 进行增、删、改、查;

# 增:创建 service

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

或者

BEGIN

  DBMS_SERVICE.CREATE_SERVICE(

    service_name => 'salesrep',

    network_name => 'salesrep.example.com');

END;

/

 

# 改:修改 service

 

srvctl modify service -db mycdb -service salesrep -pdb hrpdb
不能通过DBMS_SERVICE包修改service,解决方法是删除再创建;

# 删:删除 service

srvctl remove service -db mycdb -service salesrep

BEGIN

  DBMS_SERVICE.DELETE_SERVICE(

    service_name => 'salesrep');

END;

/

 

# 查,视图 dba_services

 

在 Oracle RAC CDB 中的 PDB ,如果创建 service 时,指定了 -pdb 选项,启动 service 资源时,同时也会 open 对应的 pdb ;使用 ALTER PLUGGABLE DATABASE 命令关闭 pdb 时, crs 资源 service 会自动 offline ,但是使用 ALTER PLUGGABLE DATABASE 命令启动 pdb 时, service 资源不会随之 online 启动;

【参考】


网站栏目:Oracle12cCDB&PDBs管理
地址分享:http://dzwzjz.com/article/giippj.html
在线咨询
服务热线
服务热线:028-86922220
TOP