Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to create and delete CDB and PDBs for Oracle12c". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, plan a multi-tenant environment

Prepare enough memory and disk space; the issues to consider include the following 16 aspects:

1. Number of PDB (max 253) and space plan

two。 Number of background processes (processes parameter): PDB+CDB user

3. DB_NAME,global db name

4. Spfile is shared by CDB and PDB. Pdb can set parameters separately. Operation spfile must be in root.

5. The character set is shared by CDB and PDB. Al32utf8/al16utf16 (National character set) is recommended.

6. The time zone can be unified by CDB and PDB, or set separately by PDB.

7. Db_block_size is unified within CDB and cannot be modified

8. Both block size,redo log and control file of Online redo log are shared at the instance level

9. Design the appropriate sysaux and plan the CDB and PDB respectively

10. Default tablespace, you can schedule CDB and PDB default tablespaces respectively, and share temporary tablespaces

11. Default temporary tablespace, which can be scheduled for CDB and PDB, respectively

12. Undo tablespace, with only one active undo tablespace per instance

13. Planning Service

14. Familiar with startup and shutdown of CDB and PDB

15. Whether or not to use the RAC environment

16. Unsupported features need to be planned to avoid

Second, create and delete CDB&PDB1. Create CDB

DBCA (recommended), create database

CREATE DATABASE must contain ENABLE PLUGGABLE DATABASE clauses. Root and seed are created by default when the database is created.

Location and name of the data file:

1. The SEED FILE_NAME_CONVERT clause

2. Oracle Managed Files

3. The PDB_FILE_NAME_CONVERT initialization parameter

Create CDB

CREATE DATABASE cdb_name

DATAFILE'/ u01 SIZE REUSE

SYSAUX DATAFILE'/ u01 SIZE REUSE

SEED # seed container's tbs

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M

Example 1: Creating a CDB Without Using Oracle Managed Files

CREATE DATABASE newcdb

USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password

LOGFILE GROUP 1 ('/ u01qlogsUniqqredo01a.loghandhelplicalredo01a.loghandlemagedredo01b.log')

SIZE 100M BLOCKSIZE 512

GROUP 2 ('/ u01qlogsCompact redo02a. Loghammer Commons redo02a. Loghammer Commons redo02andredo02b.log')

SIZE 100M BLOCKSIZE 512

GROUP 3 ('/ u01qlogsUniqqredo03a.loghandhelplicalredo03a.loghandleredredo03b.log')

SIZE 100M BLOCKSIZE 512

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 1024

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE'/ u01qapplash oracle.oradataAccording to newcdbscarp system01.dbf'.

SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SYSAUX DATAFILE'/ u01qapplash oracle.oradataAccording to newcdbip, sysaux01.dbf'

SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DEFAULT TABLESPACE deftbs

DATAFILE'/ u01qapplash oracle.oradataAccording to newcdbscarp deftbs01.dbf'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE tempts1

TEMPFILE'/ u01qapplash oracle.oradataAccording to newcdbamptemp01.dbf'.

SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

UNDO TABLESPACE undotbs1

DATAFILE'/ u01qapplash oracle.oradataAccording to undotbs01.dbf'

SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

ENABLE PLUGGABLE DATABASE

SEED

FILE_NAME_CONVERT = ('/ u01qqappActionoradataUniplex'/ u01max

'/ u01qapqqoracle.oradataUniplicatedoradataPlacement')

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M

USER_DATA TABLESPACE usertbs

DATAFILE'/ u01qapplash oracle.oradataAccording to pdbs01.dbf'.

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

Example 2: Creating a CDB Using Oracle Managed Files

Parameter file:

DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'

This example sets the parameter Oracle ASM storage:

DB_CREATE_FILE_DEST = + data

CREATE DATABASE newcdb

USER SYS IDENTIFIED BY sys_password

USER SYSTEM IDENTIFIED BY system_password

EXTENT MANAGEMENT LOCAL

DEFAULT TABLESPACE users

DEFAULT TEMPORARY TABLESPACE temp

UNDO TABLESPACE undotbs1

ENABLE PLUGGABLE DATABASE

SEED

SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

SYSAUX DATAFILES SIZE 100M

Execute catcdb.sql to install CDB components

@? / rdbms/admin/catcdb.sql

SYSDBA executes the following script:

@? / rdbms/admin/catalog.sql

@? / rdbms/admin/catproc.sql

@? / rdbms/admin/utlrp.sql

SYSTEM users execute:

@? / sqlplus/admin/pupbld.sql

two。 Configure EM express

SYSDBA permission execution:

Exec DBMS_XDB_CONFIG.SETHTTPPORT (http_port_number)

Exec DBMS_XDB_CONFIG.SETHTTPSPORT (https_port_number)

Each container must use a unique port, access method:

Http://database_hostname:http_port_number/em/

Https://database_hostname:https_port_number/em/

3. Create PDB

Figure 38-1 Options for Creating a PDB

Technique

Description

Create a PDB by using the seed

Create a PDB in a CDB using the files of the seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB.

Create a PDB by cloning an existing PDB or non-CDB

Create a PDB by cloning a source PDB or non-CDB and plugging the clone into the CDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, or a non-CDB. This technique copies the files associated with the source to a new location and associates the copied files with the new PDB.

Create a PDB by plugging an unplugged PDB into a CDB

Create a PDB by using the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.

Create a PDB by using a non-CDB

Create a PDB by moving a non-CDB into a PDB. You can use the DBMS_PDB package to create an unplugged PDB from an Oracle Database 12c non-CDB. You can then plug the unplugged PDB into the CDB.

1) CREATE PLUGGABLE DATABASE statement

a. Storage limit

STORAGE (MAXSIZE 2G)

STORAGE (MAXSIZE UNLIMITED)

B. PDB file location

FILE_NAME_CONVERT

DB_CREATE_FILE_DEST

PDB_FILE_NAME_CONVERT

PATH_PREFIX # pdb file location limit

SOURCE_FILE_NAME_CONVERT, SOURCE_FILE_DIRECTORY # plug and unplug pdb

c. Other

SERVICE_NAME_CONVERT # rename the service name

TEMPFILE REUSE # temporary file reuse

Specify TBS,ALL / ALL EXCEPT / NONE/list ('tbs1','tbs4','tbs5') when USER_TABLESPACES # move non-cdb to pdb

LOGGING/NOLOGGING # Tablespace logging

STANDBYS=ALL/NONE # standby CDB

NO DATA # specifies that data is not cloned when cloning pdb

2) preparation before creating PDB

Before you can create a PDB, the prerequisites must be met. Ensure that the following prerequisites are met before creating the PDB:

CDB must exist. See create and configure CDB.

CDB must be in read / write mode.

The current user must be the Common User of the root container.

The current user must have CREATE PLUGGABLE DATABASE system privileges.

You must determine a unique PDB name for each PDB. Each PDB name must be unique within a single CDB, and each PDB name must be unique within the scope of all CDBs reached by its instance through a particular listener.

3) create PDB with seed

When using the SNAPSHOT COPY clause, all data files for the source PDB must be stored in the same storage type.

When a clone of the source PDB is created using the SNAPSHOT COPY clause and the CLONEDB initialization parameter is set to FALSE, the underlying file system of the source PDB file must support storage of snapshots. These file systems include Oracle automatic Storage Management Cluster File system (Oracle ACFS) and direct NFS client storage.

When you use SNAPSHOT COPY to create a clone, the CLONEDB initialization parameter is set to TRUE, and the underlying file system source PDB file can be any local file system, network file system (NFS), or cluster file system, enabling NFS directly. However, the source PDB must remain in open read-only mode as long as there is a clone.

Example 38-18 Creating a PDB Using No Clauses

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

Example 38-19 Creating a PDB and Granting Predefined Oracle Roles to the PDB Administrator

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

ROLES= (DBA)

Example 38-20 Creating a PDB Using the STORAGE, DEFAULT TABLESPACE, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password

STORAGE (MAXSIZE 2G)

DEFAULT TABLESPACE sales

DATAFILE'/ disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250m AUTOEXTEND ON

PATH_PREFIX ='/ disk1/oracle/dbs/salespdb/'

FILE_NAME_CONVERT = ('/ disk1/oracle/dbs/pdbseed/','/ disk1/oracle/dbs/salespdb/')

4) Clone PDB or non-CBD to create PDB

a. Clone Local PDB

Example 38-21 Cloning a Local PDB Using No Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

Example 38-22 Cloning a Local PDB With the PATH_PREFIX, FILE_NAME_CONVERT, and SERVICE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

PATH_PREFIX ='/ disk2/oracle/pdb2'

FILE_NAME_CONVERT = ('/ disk1/oracle/pdb1/','/ disk2/oracle/pdb2/')

SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry')

NOLOGGING

Example 38-23 Cloning a Local PDB Using the FILE_NAME_CONVERT, STORAGE, and SERVICE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1

FILE_NAME_CONVERT = ('/ disk1/oracle/pdb1/','/ disk2/oracle/pdb2/')

STORAGE (MAXSIZE 2G)

SERVICE_NAME_CONVERT = ('salesrep','salesperson','orders','orderentry')

Example 38-24 Cloning a Local PDB Without Cloning Its Data

ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1 NO DATA

ALTER PLUGGABLE DATABASE pdb2 OPEN

b. Clone a remote PDB or non-CDB

Example 38-25 Creating a PDB by Cloning a Remote PDB Using No Clauses

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1@pdb1_link

Example 38-26 Creating a PDB by Cloning a Remote Non-CDB

CREATE PLUGGABLE DATABASE pdb2 FROM mydb@mydb_link;@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql5) insert the method of PDB to create PDB

BEGIN

DBMS_PDB.DESCRIBE (

Pdb_descr_file = >'/ disk1/oracle/salespdb.xml'

Pdb_name = > 'SALESPDB')

END

/

SET SERVEROUTPUT ON

DECLARE

Compatible CONSTANT VARCHAR2 (3): =

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY (

Pdb_descr_file = >'/ disk1/usr/salespdb.xml'

Pdb_name = > 'SALESPDB')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END

BEGIN

DBMS_OUTPUT.PUT_LINE (compatible)

END

/

Example 38-27 Plugging In an Unplugged PDB Using the NOCOPY Clause

CREATE PLUGGABLE DATABASE salespdb USING'/ disk1/usr/salespdb.xml'

NOCOPY

TEMPFILE REUSE

Example 38-28 Plugging In an Unplugged PDB Using the AS CLONE and NOCOPY Clauses

CREATE PLUGGABLE DATABASE salespdb AS CLONE USING'/ disk1/usr/salespdb.xml'

NOCOPY

TEMPFILE REUSE

Example 38-29 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, NOCOPY, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING'/ disk1/usr/salespdb.xml'

SOURCE_FILE_NAME_CONVERT = ('/ disk1/oracle/sales/','/ disk2/oracle/sales/')

NOCOPY

STORAGE (MAXSIZE 2G)

TEMPFILE REUSE

Example 38-30 Plugging In an Unplugged PDB With the COPY, PATH_PREFIX, and FILE_NAME_CONVERT Clauses

CREATE PLUGGABLE DATABASE salespdb USING'/ disk1/usr/salespdb.xml'

COPY

PATH_PREFIX ='/ disk2/oracle/sales/'

FILE_NAME_CONVERT = ('/ disk1/oracle/sales/','/ disk2/oracle/sales/')

Example 38-31 Plugging In an Unplugged PDB Using the SOURCE_FILE_NAME_CONVERT, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING'/ disk1/usr/salespdb.xml'

SOURCE_FILE_NAME_CONVERT = ('/ disk1/oracle/sales/','/ disk2/oracle/sales/')

MOVE

FILE_NAME_CONVERT = ('/ disk2/oracle/sales/','/ disk3/oracle/sales/')

STORAGE (MAXSIZE 2G)

Example 38-32 Plugging In an Unplugged PDB Using the SOURCE_FILE_DIRECTORY, MOVE, FILE_NAME_CONVERT, and STORAGE Clauses

CREATE PLUGGABLE DATABASE salespdb USING'/ disk1/usr/salespdb.xml'

SOURCE_FILE_DIRECTORY ='/ disk2/oracle/sales/'

MOVE

FILE_NAME_CONVERT = ('/ disk2/oracle/sales/','/ disk3/oracle/sales/')

STORAGE (MAXSIZE 2G)

6) create PDB with Non-CDB

BEGIN

DBMS_PDB.DESCRIBE (

Pdb_descr_file = >'/ disk1/oracle/ncdb.xml')

END

/

SET SERVEROUTPUT ON

DECLARE

Compatible CONSTANT VARCHAR2 (3): =

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY (

Pdb_descr_file = >'/ disk1/oracle/ncdb.xml'

Pdb_name = > 'NCDB')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END

BEGIN

DBMS_OUTPUT.PUT_LINE (compatible)

END

/

CREATE PLUGGABLE DATABASE ncdb USING'/ disk1/oracle/ncdb.xml'

COPY

FILE_NAME_CONVERT = ('/ disk1/oracle/dbs/','/ disk2/oracle/ncdb/')

USER_TABLESPACES= ('tbs3')

@ $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

4. Unplugging PDB

Example 38-33 Unplugging PDB salespdb

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO'/ oracle/data/salespdb.xml'

5. Delete PDB

When you delete a PDB, the control file of the CDB is modified to remove all references to the deleted PDB. Archive redo log files and backups associated with PDB are not deleted, but you can delete them using Oracle Recovery Manager (RMAN).

Example 38-34 Dropping PDB salespdb While Keeping Its Data Files

DROP PLUGGABLE DATABASE salespdb KEEP DATAFILES

Example 38-35 Dropping PDB salespdb and Its Data Files

DROP PLUGGABLE DATABASE salespdb INCLUDING DATAFILES

This is the end of the introduction to "how to create and delete CDB and PDBs for Oracle12c". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.