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

A New feature of 12C in Oracle-Container Database

-12.1.0.2_GI_RAC_Create_CDB_Blog

In March, Jackie told me that I had time to deploy Oracle 12C-container database, refer to the company's migration documentation, and do a test. This matter has been dragging on. I have tried it when I have time recently. It is quite good, but it burns memory very much.

I. Overview of 12C

Oracle 12C introduces new features of CDB and PDB, allowing a database container (CDB) to host multiple pluggable databases (PDB) in the multi-rent user environment (Multitenant Environment) introduced by ORACLE 12C databases. The full name of CDB is Container Database, the Chinese translation is database container, and the full name of PDB is Pluggable Database. You can plug and unplug the database. Before ORACLE 12C, the instance and database had an one-to-one or many-to-one relationship (RAC): that is, an instance could only be associated with one database, and the database could be loaded by multiple instances. It is impossible for an instance to have an one-to-many relationship with a database. When entering ORACLE 12C, the instance can have an one-to-many relationship with the database. The following is a diagram of the official documentation on the relationship between CDB and PDB.

In fact, if you are familiar with SQL SERVER, does this kind of CDB and PDB feel the same thing as SQL SERVER's single-instance multi-database architecture? Like PDB$SEED can be seen as master, msdb and other system databases, PDBS can be seen as user-created databases. The concept of pluggable and the separation and attachment of the user database in SQL SERVER is actually the same thing.

2. CDB component (Components of a CDB)

An CDB database container contains the following components:

-ROOT component

ROOT, also known as CDB$ROOT, stores the metadata and Common User provided by ORACLE. An example of metadata is the source code of the PL/SQL package provided by ORACLE, and Common User is the user that exists in each container.

-SEED component

Seed, also known as PDB$SEED, is the template you use to create a PDBS database. You cannot add or modify an object in Seed. There can be and only one Seed in a CDB. This feeling is very similar to the model database in SQL SERVER.

-PDBS

There can be one or more PDBS,PDBS backward compatible in CDB, and PDBS can be manipulated as before in the database, in this case, most general operations.

Each of these components can be called a container. Therefore, ROOT (root) is a container, Seed (seed) is a container, and each PDB is a container. Each container has a unique ID and name in CDB.

Note:

①: a CDB can contain 253 PDB (252 PDB can be created without one seed).

②: the data files for Root,seed and each PDB are independent.

③: a single instance of CDB has a redo log;RAC environment in which each instance has a redo log.

④: there is only one set of background processes that are shared by ROOT and all PDB.

⑤: the global database name of Root is the global database name of CDB; the global database name of PDB is defined by both the PDB name and DB_DOMAIN parameters.

⑥: a CDB uses either a SPFILE or a PFILE, where the value of the parameter set for ROOT can be inherited by PDB. Alternatively, you can use the ALTER SYSTEM statement to set the parameters of PDB. When performing operations on parameter files, you must use common user and use AS SYSDBA, ASSYSOPER, AS SYSBACKUP to connect to the database. To create a CDB, the initialization parameter ENABLE_PLUGGABLE_DATABASE must be set to TRUE.

⑦: all PDB character sets use CDB's character set.

You can set the time zone for all PDB to be the same as CDB, or you can set the time zone for PDB separately.

Block size is applied to the entire CDB.

A CDB also has only one control file (which can be multiplexed).

⑧: Root and each PDB have their own SYSAUX and SYSTEM tablespaces. Default tablespaces can be set for root and for each PDB. The default temporary tablespace is only one for the entire CDB, but temporary tablespaces can be created for each PDB.

⑨: undo tablespaces are unique to CDB. In CDB, the initialization parameter UNDO_MANAGEMENT must be set to AUTO. When the current container is PDB, the undo tablespace cannot be viewed through the data dictionary view, but only through the dynamic performance view.

III. Introduction to PDB the advantages of the new features of 1.PDB

"multiple PDB can be integrated into one platform.

"you can quickly provide a new PDB or a clone of an existing PDB.

Through the plug technology, the existing database can be quickly redeployed to a new platform.

"multiple PDB database patches or upgrades are completed at once.

"by unplugging a single PDB to a different CDB in a later version, you can patch or upgrade a PDB.

"separate the contents of a PDB from many PDB in the same CDB.

"separate the responsibilities of these PDB application administrators.

The functions of the new features of 2.PDB

"in a CDB, you can have a lot of PDB.

"PDB and pre-12.1 plain databases are backward compatible.

"PDB is transparent to applications-you don't need to change client code or database objects.

"each instance in RAC opens CDB as a whole (so CDB and the PDB database version in it are the same).

"the session only sees the PDB to which it is connected.

"you can pull a PDB from one CDB and insert another CDB.

"you can clone PDB between the same CDB or different CDB.

"Resource managers are expanded with the capabilities of PDB.

"entity PDB operations are implemented through SQL statements (create, unplug, insert, clone, clear, set open mode).

When connected to the so-called 'root', the CDB administrator performs these operations.

"all PDB can be backed up once, but can be restored separately.

Detailed explanation of 3.PDB

Each PDB has its own private data dictionary for user-created database objects; on the other hand, CDB as a whole also contains the data dictionary of the Oracle providing system, where each data dictionary defines its own namespace. In other words, there are global data dictionaries (CDB level) and local data dictionaries (PDB level).

"there is a new separate data dictionary architecture that allows a PDB to be quickly pulled out of a CDB and inserted into a different CDB.

"each PDB can only see a read-only definition of the system provided by the oracle.

"there are global database parameters and local database parameters. The PDB parameter only belongs to a specific PDB, and when unplugged, the PDB parameter will remain the same.

"Database users can be global (CDB) or local (PDB). SYS and SYSTEM users exist in two levels of DB from the very beginning. If you create a new user in CDB, you can also see that user in PDB. Users created in PDB can only be used in that PDB.

"temporary tablespaces can be global or local.

"Redo logs and Undo tablespaces are global (CDB level).

"Data Guard works at the CDB level as a whole; backups scheduled by RMAN are also done at the CDB level as a whole; you can back up only one PDB at any time.

"when the application connects to the PDB, there is no need to modify the code; the system administrator can connect the service name in the CDB; connection string to determine the target PDB.

"PDB allows you to define an application more clearly; an PDB knows nothing about other PDB in the same CDB; each PDB is an airtight container. This ensures the independence and security of the new level of DB.

IV. Daily operation

1. Check whether the database is CDB

[root@vastdata11] # su-oracle [oracle@vastdata11 ~] $sqlplus / as sysdbaSQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 1 21:42:21 2019Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Application Testing optionsSQL > select name,cdb,open_mode,con_id from v$database NAME CDB OPEN_MODE CON_ID- CDB YES READ WRITE 02. View the current container

SQL > show con_name;CON_NAME--CDB$ROOTSQL > select Sys_Context ('Userenv',' Con_Name') "current Container" from dual Current Container----CDB$ROOT3. View PDB information in CDB SQL > select con_id, dbid, guid, name, open_mode from v$pdbs CON_ID DBID GUID NAME OPEN_MODE -2 2382420430 8A33449DBB2C529AE0530B38A8C0899C PDB$SEED READ ONLY 3 1983375270 8A337E167FD16986E0530B38A8C0632A PDB1 READ WRITE 4 2621964339 8A33890C63336EADE0530B38A8C02ED2 PDB2 READ WRITE 5 4092698095 8A3390728AB570E0E0530B38A8C0006E PDB3 READ WRITE SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED-- -2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED4 PDB2 MOUNTED 5 PDB3 MOUNTED4. Start and close the created PDB database SQL > alter pluggable database pdb1 open Pluggable database altered.SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 MOUNTED 5 PDB3 MOUNTEDSQL > alter pluggable database pdb1 close Pluggable database altered. SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED 5 PDB3 MOUNTED-you can also use traditional startup and shutdown commands through sqlplus to start and close PDB-SQL > alter session set container=pdb1 Session altered.SQL > startupPluggable Database opened.SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 3 PDB1 READ WRITE NOSQL > shu immediate Pluggable Database closed.SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 3 PDB1 MOUNTEDSQL > alter session set container=CDB$ROOT Session altered.SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED5 PDB3 MOUNTED5. Configure Jiantong file And log in to the PDB1 database listening file directory at $ORACLE_HOME/network/ admin [oracle @ vastdata11 admin] $cat tnsnames.ora# tnsnames.ora Network Configuration File: / u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.CDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = vastdata-scan.us.oracle.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdb)) PDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = vastdata-scan.us.oracle.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1) [oracle@vastdata11 admin] $tnsping pdb1TNS Ping Utility for Linux: Version 12.1.0.2.0-Production on 01-JUN-2019 23:16:33Copyright (c) 1997 2014, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = vastdata-scan.us.oracle.com) (PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)) OK (0 msec) [oracle@vastdata11 admin] $sqlplus system/oracle@pdb1SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 1 23:14:15 2019Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Advanced Analytics and Real Application Testing optionsSQL > select name,cdb,open_mode,con_id from v$database NAME CDB OPEN_MODE CON_ID- CDB YES READ WRITE 0 SQL > show con_nameCON_NAME--PDB1 SQL > select name from v$datafile NAME----+DATA/CDB/DATAFILE/undotbs1.280.1009718515+DATA/CDB/8A337E167FD16986E0530B38A8C0632A/DATAFILE/system.292.1009719563+DATA/CDB/8A337E167FD16986E0530B38A8C0632A/DATAFILE/sysaux.293.1009719563+DATA/CDB/8A337E167FD16986E0530B38A8C0632A/DATAFILE/users.295.1009719733SQL > 6. Create a CDB startup trigger

When the database is started in oracle12C, PDB does not start with CDB.

However, we can start PDB with CDB by creating a trigger. As follows:

SQL > SHOW CON_NAME CON_NAME-- CDB$ROOT SQL > CREATE OR REPLACE TRIGGER open_pdbs 2 AFTER STARTUP ON DATABASE 3 BEGIN 4 EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; 5 END open_pdbs; 6 / Trigger created.SQL > shu immediate Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started.Total System Global Area 1560281088 bytesFixed Size 2924784 bytesVariable Size 570429200 bytesDatabase Buffers 973078528 bytesRedo Buffers 13848576 bytesDatabase mounted.Database opened.SQL > show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED -- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB3 READ WRITE NO

7. Create a new PDB

SQL > create pluggable database pdb4 admin user admin identified by admin;SQL > alter pluggable database pdb4 open;SQL > select con_id,dbid,con_uid,guid,name,open_mode,create_scn,total_size,block_size from v$pdbs CON_ID DBID CON_UID GUID NAME OPEN_MODE CREATE_SCN TOTAL_SIZE BLOCK_SIZE -2 427872663 427872663 439BFCB2A2EE1BE7E055000000000001 PDB$SEED READ ONLY 1594401 838860800 8192 3 1891424478 439C5B1174C121B7E055000000000001 FEIGE READ WRITE 1753608 865075200 8192 4 2998508999 53F376E8715628BCE055000000000001 PDB4 READ WRITE 1757024 859832320 8192

The "admin user" clause is required, and in the extended format, the new user is given permissions and roles, and the user can create a new session only within admin.

After creating the pluggable database, the new PDB is in MOUNTED mode.

8. Clone a new PDB

SQL > create pluggable database pdb5 from pdb4;SQL > alter pluggable database pdb5 open; switch to PDB, or switch to CDBSQL > alter session set container=pdb4;SQL > alter session set container=cdb$root; to close one pdb and close all pdbSQL > alter pluggable database pdb4 close;SQL > alter pluggable database all close

9. Insert a pdb database

Pdb5.xml: the file determines information such as the name and full path of the data file. This information is used during the insert operation.

Note: PDB is still part of the CDB it pulled, but now the state has changed to UNPLUGGED.

SQL > alter pluggable database pdb5 close;SQL > alter pluggable database pdb5 unplug into'/ opt/oracle/oradata/pdb5.xml'

-delete this pdb in the current container

SQL > drop pluggable database pdb5 keep datafiles

-check whether pdb is compatible with this container. If an error is reported, it is not compatible.

Exec DBMS_PDB.Check_Plug_Compatibility (PDB_Descr_File= >'/ opt/oracle/oradata/pdb5.xml')

-start inserting pdb

(the using keyword must be followed by the absolute path described by PDB, that is, the .XML file generated during the previous pull operation)

Create pluggable database pdb5 using'/ opt/oracle/oradata/pdb5.xml' move file_name_convert = ('/ cdb1/','/ cdb2/')

-finally, open the pdb.

Alter pluggable database pdb5 open

-so far

Some basic knowledge and operation of cdb and pdb are introduced.

Let's sum it up here.

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.