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

The concept of pluggable CDB Multi-tenant Container Database

Oracle Multitenant Container Database (CDB), that is, multi-tenant container database, is a feature introduced by Oracle 12C, which refers to a database that can hold one or more pluggable databases. This feature allows multiple databases to be created and maintained in the CDB container database. The database created in CDB is called PDB, and each PDB exists independently of each other in CDB. When using PDB alone, it is no different from an ordinary database.

The main function of the CDB root container database is to hold the metadata of all related PDB and to manage all PDB in CDB.

Composition of ROOT container database for multi-tenant environment

The Root container database is the root database in the CDB environment. There is a master data dictionary view in the root database, which contains the metadata related to the Root container and all the PDB information contained in the CDB. Identified as CDB$ROOT in a CDB environment, there can be only one Root container database per CDB environment.

CDB seed

CDB seed is the seed of PDB, which provides the data file, which is identified as PDB$SEED in the CDB environment, and is the template for creating a new PDB. You can connect to PDB$SEED, but cannot execute anything, because PDB$SEED is read-only and cannot be modified.

PDBs

PDB database, in the CDB environment, each PDB is independent, basically no different from the traditional Oracle database, each PDB has its own data file and objects, the only difference is that PDB can be inserted into the CDB, and pulled out in the CDB, and at any point in time PDB must be pulled out or inserted into a CDB, when users link to PDB will not feel the existence of the root container and other PDB.

Database CDB and PDB structure diagram

Query the pdb in the current database with the following command:

SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTEDSQL >

Where PDB$SEED is CDB seed and ORCLPDB is PDB database.

Application Containers

In the 12cR2 version, Oracle enhances the multi-tenancy feature to create a container called Application root in the CDB root container, in which multiple Application PDBs dependent on Application root can be created, as shown below:

12c R2 CDB and PDB structure diagrams (new Application Containers)

Users in CDB environment

There are two types of users in the CDB environment, public users and local users.

Public users create public users

A public user is a user that exists in the root container database and in all PDB databases. The public user must be created in the root container, and then this user will be automatically created in all existing PDB. The public user identity must start with sys # or system #. Sys and system users are public users automatically created by Oracle in the CDB environment.

For example:

The creation statement is:

SQL > create user c##mytest1 identified by mytest1; User created.

Summary:

(1) Public users create in the root container

(2) the public user name is special. It should start with clocked # or Category #.

Public user empowerment

After the public user is created, all pluggable database permissions need to be given to the public user before the public user can access other PDB. If only the public user is given relevant permissions when connecting to the root container, then this permission will not be passed to all pluggable databases. The public user must be given permissions that can be passed to the PDB. You can create a public role and then give a public user. Or specify the clause container=ALL when paying rights for public users

Summary:

(1) Public users need to be given corresponding permissions to connect to pdb.

(2) the permission can be given by adding container=all (or container=pdb), or by giving permission to the role role.

For example:

Give c##mytest1 users basic connection permissions.

SQL > grant resource,connect to cantilever mytest 1; Grant succeeded. SQL > SQL > exit

Test the login of "c##mytest1" users, and log in normally

[oracle@linux14] $sqlplus / nolog SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 23:01:30 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL > conn cantilever Mytest1According to mytest1tion connection. SQL >

Login failed without the relevant permissions granted to access pdb

SQL > alter session set container=orclpdb;ERROR:ORA-01031: insufficient privileges SQL >

Two ways to give c##mytest1 users access to pdb

(1) add container=all [oracle@linux14 ~] $sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 23:08:15 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production SQL > grant resource,connect to c##mytest1 container=all; Grant succeeded. SQL > conn c##mytest1/mytest1Connected.SQL > SQL > alter session set container=orclpdb; Session altered. SQL > (2) Grant permissions to existing roles

1. Create the role of c##mytestprivs-- >

two。 Give resource,connect permission-- >

3. Then give the c##mytestprivs to cymbals mytest1muri->

4. Then c##mytest1 can log in to pdb normally.

[oracle@linux14] $sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 5 23:19:16 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production SQL > SQL > create role c##mytestprivs container=all; Role created. SQL > grant resource,connect to c##mytestprivs container=all; Grant succeeded. SQL > grant c##mytestprivs to c##mytest1 container=all; Grant succeeded. SQL > SQL > conn c##mytest1/mytest1Connected.SQL > SQL > show userUSER is "C##MYTEST1" SQL > SQL > alter session set container=orclpdb; Session altered. SQL > Local user

A local user refers to a normal user created in PDB, which exists only in the PDB in which it was created, and only local users can be created in PDB. This user is the same as a user in a normal database.

The basics you need to know about CDB in SYSTEM/SYSAUX tablespace

SYSTEM and SYSAUX tablespaces are independent, owned separately in PDB and root container databases, and are not shared.

Query the results in the CDB$ROOT container database:

SQL > show con_id con_name user; CON_ID--1 CON_NAME--CDB$ROOTUSER is "SYS" SQL > select tablespace_name,file_name from dba_data_files TABLESPACE_NAME FILE_NAME - - - -USERS / data/oradata/orcl/users01.dbfUNDOTBS1 / data/oradata/orcl/undotbs01 .dbfSYSTEM / data/oradata/orcl/system01.dbfSYSAUX / data/oradata/orcl/sysaux01.dbf

The query result in orclpdb's pdb database is as follows:

SQL > alter session set container=orclpdb; Session altered. SQL > show con_id con_name user; CON_ID--3 CON_NAME--ORCLPDBUSER is "SYS" SQL > SQL > select tablespace_name,file_name from dba_data_files TABLESPACE_NAME FILE_NAME - - - -UNDOTBS1 / data/oradata/orcl/orclpdb/undotbs01.dbfSYSAUX / data/oradata/orcl/ Orclpdb/sysaux01.dbfSYSTEM / data/oradata/orcl/orclpdb/system01.dbfUSERS / data/oradata/orcl/orclpdb/users01.dbf SQL > REDO file

(1) the redo log file is public and exists in the CDB$ROOT in the root container, and the entries in the REDO identify which PDB the REDO is from.

(2) the ALTER SYSTEM SWITCH LOGFILE command cannot be executed in PDB and can only be executed by a public user in the ROOT container.

SQL > alter system switch logfile;alter system switch logfile*ERROR at line 1:ORA-65040: operation not allowed from within a pluggable database

(3) in addition, ALTER SYSTEM CHECKPOINT commands can be executed in PDB.

SQL > alter system checkpoint; System altered. SQL > Archive

Archive logs are public

In a CDB environment, all PDB share the archive mode of CDB, as well as archive files. You cannot set your own archive mode separately for PDB. You can start archive mode only after privileged users connect to the root container.

UNDO MODE--UNDO tablespace

(1) in versions prior to 12.2, all PDB shared UNDO files in CDB$ROOT.

(2) in the version after 12.2, there are two modes of using UNDO: SHARED UNDO MODE and LOCAL UNDO MODE. As the name implies, LOCAL UNDO MODE means that each PDB uses its own UNDO tablespace, but when there is no own UNDO tablespace in PDB, the public UNDO tablespace in CDB$ROOT is used.

View the usage pattern (CDB$ROOT) of UNDO tablespaces:

SQL > COL PROPERTY_NAME FOR A50SQL > COL PROPERTY_VALUE FOR A50SQL > COL DESCRIPTION FOR A50SQL > SQL > SELECT property_name, property_value 2 FROM database_properties 3 WHERE property_name='LOCAL_UNDO_ENABLED' PROPERTY_NAME PROPERTY_VALUE -LOCAL_UNDO_ENABLED TRUE SQL > close Local UNDO (database restart is required for operation)

Directly shutting down local undo will result in an error:

SQL > alter database local undo off;alter database local undo off*ERROR at line 1:ORA-65192: database must be in UPGRADE mode for this operation

Shut down the database, boot to upgrade mode and shut down local undo

SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > SQL > startup upgradeORACLE instance started. Total System Global Area 1241513984 bytesFixed Size 8792248 bytesVariable Size 788531016 bytesDatabase Buffers 436207616 bytesRedo Buffers 7983104 bytesDatabase mounted.Database opened.SQL > SQL > alter database local undo off; Database altered.

After restarting the database, the query local undo has been closed

SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started. Total System Global Area 1241513984 bytesFixed Size 8792248 bytesVariable Size 788531016 bytesDatabase Buffers 436207616 bytesRedo Buffers 7983104 bytesDatabase mounted.Database opened.SQL > SQL > SELECT property_name, property_value 2 FROM database_properties 3 WHERE property_name='LOCAL_UNDO_ENABLED' PROPERTY_NAME PROPERTY_VALUE -LOCAL_UNDO_ENABLED FALSE SQL > enable local UNDO (database restart is required for operation)

Even if you turn on local undo directly, you will get an error.

SQL > alter database local undo on Alter database local undo on * ERROR at line 1: ORA-65192: database must be in UPGRADE mode for this operation

Restart the database to upgrade mode, and then shut down local undo

SQL > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL > SQL > startup upgrade ORACLE instance started. Total System Global Area 1241513984 bytes Fixed Size 8792248 bytes Variable Size 788531016 bytes Database Buffers 436207616 bytes Redo Buffers 7983104 bytes Database mounted. Database opened. SQL > SQL > alter database local undo on Database altered.

Restart the database again to query the open status of local undo

SQL > shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL > startup ORACLE instance started. Total System Global Area 1241513984 bytes Fixed Size 8792248 bytes Variable Size 788531016 bytes Database Buffers 436207616 bytes Redo Buffers 7983104 bytes Database mounted. Database opened. SQL > SQL > SELECT property_name Property_value 2 FROM database_properties 3 WHERE property_name='LOCAL_UNDO_ENABLED' PROPERTY_NAME PROPERTY_VALUE-- -LOCAL_UNDO_ENABLED TRUE SQL > temporary files

(1) temporary tablespaces are also independent by default, and PDB and root container databases own

(2) if PDB does not have its own temporary tablespace file, then PDB can use temporary tablespaces in CDB$ROOT.

Parameter file

Parameter files are common

(1) only the parameter information of the root container is recorded in the parameter file, but not at the PDB level. If you modify the initialization parameters in the root container, it will be inherited into all PDB.

(2) after you modify the parameters in PDB, the parameters of PDB override the parameters at CDB level, and the parameters at PDB level are recorded in the pdb_spfile$ view of the root container, but not all parameters can be modified in PDB.

(3) you can view the modifiable parameters in PDB through the v$system_parameter view:

SELECT name FROM v$system_parameterWHERE ispdb_modifiable = 'TRUE'ORDER BY name; control file

The control file is public

There is only one set of control files in the CDB environment, and all PDB share this set of common control files. Adding data files from any PDB will be recorded in the public control files. When public users connect to the root container, they can manage the control files.

SQL > show parameter control_files NAME TYPE VALUE---control_files string / Data/oradata/orcl/control01.c tl / data/flash_recovery_area/ orcl/control02.ctlSQL > alarm log and trace file

Alarm logs and trace files are common

All PDB in CDB share an alarm log and a set of trace files, and all PDB alarm messages are written to the same alarm log.

Time zone

In a CDB environment, you can set the same time zone for CDB and all PDB, or you can set a separate time zone for each PDB.

Query time zone statement:

SQL > select dbtimezone from dual; DBTIME-+00:00 character set

Defining a character set in CDB can also be applied to the PDB it contains, and each PDB can have its own character set settings.

The statement to query the character set:

SELECT a.value | |'_'| | b.value | |. | | c.value NLS_LANG FROM nls_database_parameters a, nls_database_parameters b, nls_database_parameters c WHERE a.parameter = 'NLS_LANGUAGE' AND b.parameter =' NLS_TERRITORY' AND c.parameter = 'NLS_CHARACTERSET'; data Dictionary View and dynamic performance View

(1) A CDB-level data dictionary view is introduced in the CDB environment, which is higher than DBA_/ALL_/USER_. The CDB-level data dictionary view contains the metadata information of all PDB, in which the con_id column is added, and con_id is the unique identifier of all containers in CDB.

(2) where con_id is CDB$ROOT, con_id is 2 is PDB$SEED, and each PDB is assigned a unique con_id in CDB.

(3) if you want to view a CDB-level data dictionary view, you must use public users to view it in the container, and the PDB you want to view must be in the open state before you can see the information in the PDB.

Operation and management of CDB

Managing CDB is also typically done with sys users, which connect and operate in the same way as non-CDB databases.

Methods of logging in to CDB and PDB databases

Use tnsname to log in to CDB and PDB in oracle 12c

Example configuration of tnsnames.ora

[oracle@linux14 admin] $cat tnsnames.ora# tnsnames.ora Network Configuration File: / opt/app/oracle/product/12c/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP) (HOST = linux14) (PORT = 1521) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = linux14) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)) ORCLPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = linux14) (PORT = 1521)) SERVER = DEDICATED) (SERVICE_NAME = orclpdb)

The configuration of ORCL is used to log in to the cdb$root container database of CDB

ORCLPDB is configured to log in to PDB as an orclpdb database

Use orcl to log in to the cdb$root database:

[oracle@linux14 admin] $sqlplus sys/oracle@orcl as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 8 22:13:15 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production SQL > show con_id con_name user; CON_ID--1 CON_NAME--CDB$ROOTUSER is "SYS"

Use orclpdb to log in to the pdb database

[oracle@linux14 admin] $sqlplus sys/oracle@orclpdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 8 22:14:37 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production SQL > show con_id con_name user; CON_ID--3 CON_NAME--ORCLPDBUSER is "SYS" SQL > query the information of the current connection container:

(1) Mode 1

SQL > show con_id con_name user; CON_ID--1 CON_NAME--CDB$ROOTUSER is "SYS" SQL >

(2) Mode 2

SQL > COL CON_ID FOR A10SQL > COL CUR_CONTAINER FOR A25SQL > COL CUR_USER FOR A25SQL > SELECT 2 sys_context ('USERENV','CON_ID') con_id, 3 sys_context (' USERENV','CON_NAME') cur_container, 4 sys_context ('USERENV','session_user') cur_user 5 FROM dual CON_ID CUR_CONTAINER CUR_USER- 1 CDB$ROOT SYS SQL > start and shut down the CDB database

The method of starting and shutting down CDB databases is the same as that of ordinary non-CDB databases, in which PDB databases are not included with CDB, but only to mounted status:

[oracle@linux14] $sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 8 20:47:24 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL > startupORACLE instance started. Total System Global Area 1241513984 bytesFixed Size 8792248 bytesVariable Size 788531016 bytesDatabase Buffers 436207616 bytesRedo Buffers 7983104 bytesDatabase mounted.Database opened.SQL > SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTEDSQL > alter pluggable database orclpdb open Pluggable database altered. SQL >

Manually start PDB to open

Alter pluggable database orclpdb open;-- > specify a pdb startup, such as orclpdbalter pluggable database all open;-- > all pdb databases are started

The command to turn off CDB:

SQL > shutdown immediate

Query the tablespace usage of CDB database

With generator0 as (select cf.con_id, cf.tablespace_name, sum (cf.bytes) / 1024 / 1024 frm from cdb_free_space cf group by cf.con_id, cf.tablespace_name), generator1 as (select cd.con_id, cd.tablespace_name, sum (cd.bytes) / 1024 / 1024 usm from cdb_data_files cd group by cd.con_id, cd.tablespace_name), generator2 as (select g0.con_id, c.name con_name, g0.tablespace_name, g0.frm) G1.usm from generator0 G0, generator1 G1, v$containers c where g0.con_id = g1.con_id and g0.tablespace_name = g1.tablespace_name and c.con_id = g1.con_id union select c.con_id, c.name, ct.tablespace_name, null, sum (ct.bytes) / 1024 / 1024 from v$containers c Cdb_temp_files ct where c.con_id = ct.con_id group by c.con_id, c.name, ct.tablespace_name) select con_id, case when con_name = LAG (con_name, 1) OVER (PARTITION BY con_name ORDER BY tablespace_name) THEN null ELSE con_name END con_name, tablespace_name, frm | | M' freemb Usm | |'M' usemb from generator2 order by con_id

The result is shown in the figure:

Operation of switching containers

Under normal circumstances, public users are used to do operations.

After using a public user to connect to CDB, you can use alter session to switch different containers.

For example:

Alter session set container=orclpdb;alter session set container= cdb$root; experiment:

A mytest1 user is created in the pdb database orclpdb. If you use this user to switch containers, an error without permissions will be reported.

[oracle@linux14 admin] $sqlplus mytest1/mytest1@orclpdb; SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 8 22:01:09 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Tue Jan 08 2019 21:50:33 + 08:00 Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production SQL > SQL > alter session set container=cdb$root;ERROR:ORA-01031: insufficient privileges

An attempt was made to grant permissions to the mytest1 user with the sys user, and an error occurred

SQL > conn sys/oracle as sysdbaConnected.SQL > show con_id user; CON_ID--1USER is "SYS" SQL > alter session set container=orclpdb; Session altered. SQL > SQL > show con_id user; CON_ID--3USER is "SYS" SQL > grant dba to mytest1 container=all;grant dba to mytest1 container=all*ERROR at line 1:ORA-65030: cannot grant a privilege commonly to a local user or role SQL >

Therefore, it is recommended that the operations of the CDB container database use public users for related operations.

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.