这篇常见文件是官方手册关于cdb/pdb 问题常见描述
In this Document
APPLIES TO:Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Exadata Cloud Machine - Version N/A and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. PURPOSEThis document addresses the frequently asked questions related to Oracle Multitenant achitecture which includes container database (CDB) and pluggable database (PDB). QUESTIONS AND ANSWERSNOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner
General CDB / PDB Concept in 12c Multitenant Architecture
What is a pluggable database (PDB) in Multitenant Architecture?Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 (12.1). You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are fully backwards compatible with an ordinary pre-12.1 database. Why would I consider using the Multitenant option ?You should consider following Database Consolidation Goals to achieve:
- Administration costs - Operations costs - Data center costs - Storage costs - Contingency costs
- Resource utilization - Manageability - Integration - Service management
What other benefits will I get from Multitenant option ?The benefits of mulitenant Pluggable Databases are:
How easy to migrate an existing pre 12.1 database to 12c Multitenant database ?Migrate to 12c Pluggable database is very simple and easy. You can evaluate and adopt which one best suit for you. Plan A.
Plan B.
You can take a look at Doc ID 2051130.1 - Database upgrade to 12c Pluggable database (Multitenant) using TTS method. It talks about upgrading 11.2.0.4.0 database directly to 12.1.0.2.0 pluggable database through TTS method. This method won't require intermediate 12c upgrade. Which Oracle Database features are currently not supported in a Multitenant architecture ?The following Oracle Database features are currently not supported in a CDB:
If you must use one or more of these features, then create a non-CDB. Does each non-CDB need its own CDB if I do not have a license for Multitenant?Yes Can multiple CDBs run on the same server?Yes Can multiple CDBs run out of the same ORACLE_HOME installation?Yes Basic Multitenant CDB / PDB OperationsHow do I know if my database is Multitenant or not ?Establish a SQL Session and run this Query: SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE; NAME Multitenant Option ? OPEN_MODE CON_ID What Pluggable databases do we have in this container database ?SQL> Show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED How do I connect to a Pluggable Database , say, PDB6 ?You can switch to PDB6 from other PDB or Root Container using following command. SQL> alter session set container = pdb6; Connecting to a PDB directly Using the SQL*Plus CONNECT Command: Examples of SQLPLUS from Os prompt: $ sqlplus username/password@//<HOST>:1521/pdb2 ======= LISTENER_CDB1 = Example of SQLPLUS from Os prompt: $ sqlplus username/password@pdb2 How do I switch to main container Database ?SQL> ALTER SESSION SET CONTAINER = CDB$ROOT; How do I determine which PDB or CDB I am currently connected to ?SQL> show con_name CON_NAME OR SQL> select sys_context ( 'Userenv', 'Con_Name') "Container DB" from dual; Container DB How do I start up a Pluggable database ?When connect to current PDB: SQL> alter pluggable database open; When connect to root: SQL> alter pluggable database pdb6 open; How do I shutdown / close a Pluggable database ?When connect to current PDB: SQL> alter pluggable database close; When connect to root: SQL> alter pluggable database pdb6 close; How do I shut down / Startup the Container Database ?Use startup / Shutdown command similar to startup / shutdown of Non CDB. When the container database is shutdown , no PDB is accessible. In a CDB, the root and all of the PDBs share a single instance, or, when using Oracle RAC, multiple concurrent database instances. You start up and shut down an entire CDB, not individual PDBs. However, when the CDB is open, you can change the open mode of an individual PDB by using the Which parameters are modifiable at PDB level ?select NAME, ISPDB_MODIFIABLE from V$PARAMETER; What common users do I have in my cdb ?SQL> select distinct USERNAME from CDB_USERS where common = 'YES';How do I create a common user ?SQL> create user c##xxxidentified by <password> container=all; How do I create a local user ?SQL> create user <username> identified by <password> container=current; Multitenant ArchitectureWhat is the difference between Container ID Zero and One ?CON_ID "0" means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to "0". A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0. Are there any background processes ex, PMON, SMON etc associated with PDBs ?No. There is one set of background processes shared by the root and all PDBs. Are there separate control file required for each PDB ?No. There is a single redo log and a single control file for an entire CDB. Are there separate Redo log file required for each PDB ?No. There is a single redo log and a single control file for an entire CDB. Can I monitor SGA usage on a PDB by PDB basis?There are single SGA shared by all pluggable databases. However, you can determine SGA consumptions by all containers i.e, root and PDB. SQL> alter session set container=CDB$ROOT; SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id'; SQL> select CON_ID, POOL, sum(bytes) from v$sgastat Can I monitor PGA usage on a PDB by PDB basis?select CON_ID, sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM), sum(PGA_MAX_MEM) alter session set container =CDB$ROOT;
Do I need separate UNDO tablespaces for for each of my PDB ?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. Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace. Please refer the below note for various modes of Undo available from 12.2 onwards Undo Modes in 12.2 Multitenant Databases - Local and Shared Modes (Doc ID 2169828.1) Do I need separate SYSTEM tablespaces for each of my PDB ?There is a separate Do I need separate SYSAUX tablespaces for for each of my PDB ?There is a separate Do I need Temporary tablespaces for for each of my PDB ?There is one default temporary tablespace for the entire CDB. However, you can create additional temporary tablespaces in individual PDBs. One active temporary tablespace is needed for a single-instance CDB, or one active temporary tablespace is needed for each instance of an Oracle RAC CDB. Can I specify a separate default tablespace for the root and for each PDB ?Yes. You can specify a separate default tablespace for the root and for each PDB Are all physical datafiles separate for root and PDB?There are separate datafiles for the root, the seed, and each PDB. Where is user data stored in CDB ? In a CDB, most user data is in the PDBs. The root contains no user data or minimal user data. Does Pluggable database support separate database characterset ?A CDB uses a single character set. All of the PDBs in the CDB use this character set. Oracle recommends the following:
Reference: Oracle Database Globalization Support Guide, 12c Release 1 (12.1) How do I configure Net Files in a Pluggable database environment ?There is a single Advance CDB / PDB OperationsHow can I install and setup Pluggable Database ?Use runInstaller to install the Oracle Database software Use dbca to create databases. You can create many pluggable databases in a single operation. DBCA enables you to specify the number of PDBs in the CDB when it is created.After a CDB is created, you can use DBCA to plug PDBs into it and unplug PDBs from it. What Operations act on PDBs as entities ?These operations act on PDBs as entities: • create PDB (brand-new, as a clone of an existing PDB, by plugging in an unplugged PDB) • unplug PDB • drop PDB • set the Open_Mode for a PDB How can I create a pluggable database ?create pluggable database x admin user a identified by p; create pluggable database y admin user a identified by p file_name_convert = ('pdbseed', 'y'); How to drop a PDB irrevocably ?drop pluggable database x including datafiles; How easy is it to manage the provisioning of PDBs using PL/SQL ? Following an Example of PL/SQL Code to show this. -- Using Oracle-Managed Files declare t0 integer not null := -1; procedure Show_Time(What in varchar2) is t varchar2(10); begin t := Lpad((DBMS_Utility.Get_Time() - t0), 5); DBMS_Output.Put_Line('create PDB:'||t||' centiseconds'); end Show_Time; begin t0 := DBMS_Utility.Get_Time(); execute immediate ' create pluggable database x admin user a identified by p '; Show_Time('create PDB:'); t0 := DBMS_Utility.Get_Time(); execute immediate ' drop pluggable database x including datafiles '; Show_Time('drop PDB: '); end; How to clone a PDB from an existing PDB ?The clonee must be open in read only mode. -- Using Oracle-Managed Files create pluggable database x2 from x; How to unplug a PDB ?alter pluggable database x unplug into '/some_directory/x_description.xml' ; The into keyword must be followed by the full path for a description of the PDB, in XML, generated by the operation. Scalability & RACHow to add or modify a user-managed service ?srvctl add service … –pdb <pdb_name> Starting a user-managed service using srvtcl will open the PDB automatically in all the instances in which the service is started. Specifying the empty string ("") as the <pdb_name> will cause the pluggable database attribute of a service to be set to null. The service can then be used only to connect to the root. How can I view which service is attached to my Pluggable database ? SQL> column NAME format a30 DiagnosibilityWhere can I find Alert log for my pluggable Database ?A Single copy of Alert log is generated which contains warnings and alert information for all PDBs. XML version of alert can be found in "Diag Alert" and text formatted Aler log can be found in "Diag Trace" of the container database. You can find details by selecting from v$diag_info dynamic view. Where can I find trace files related for my pluggable Database ?All traces generated from all PDBs are currently found in "Diag Trace" of the container database. You can find details by selecting from v$diag_info dynamic view.
MiscellaneousIf a user-defined, common user creates schema objects in a PDB, and if later that PDB is unplugged
|