How do I plug a PDB to Oracle12c container database

Applies to: 

---

Databases: Oracle 12cR1

Author:

---

Mahesh Reddy

Problem: 

---

How do I Plug a PDB to Oracle12c container database?

Solution: 

---

You can create a pluggable database in following ways:

• Create new PDB using seed.
• Create new PDB by cloning existing PDB
• Plug an unplugged PDB into CDB
• Create new PDB from 12c non-CDB

1. Create new PDB using seed:

Create a PDB using the SEED database. This method copies the files associated with the seed to a new location and associates to new PDB. You can easily create new PDB from the SEED using create pluggable database statement.

You can specify the following clauses to create new PDB from SEED in different ways:

• Storage: you can specify the storage limits
• Default Tablespace: It creates a small file tablespace and assigns this to non-system users.
• path_prefix : It specifies the absolute path
• file_name_convert: It specifies the new location of PDB files from source files.
• Tempfile reuse: it specifies, reuse the tempfile from target location.
• Roles: the predefined Oracle roles to grant to the PDB_DBA role.

Example: 1

Sql> Create pluggable database PDBNAME admin user PDBUSR identified by PASSWORD;

Above statement create PDB with the local user PDBUSR.  This statement grants the PDB_DBA role to the PDB administrator ‘pdbusr’ and grants the specified predefined Oracle roles to the PDB_DBA role locally in the PDB.

Example: 2                              

Sql> Create pluggable database PDBNAME admin user pdbusr identified by PASSWORD storage (MAXSIZE 5G) default tablespace USERS datafile ‘+DATA_NEW’ size 100M autoextend on path_prefix = ‘+DATA_NEW’ file_name_convert = (‘+DATA’,’DATA_NEW’);

Above statement create the pdb with the limited size, it specifies the all tablespaces that belong to the PDB must not exceed 5 gigabytes and create the default tablespace users for non admin users in the new location is ‘+DATA_NEW’. 

2.  Create new PDB by cloning an existing PDB:

Cloning production database is a common technique used to develop and test changes to applications and associated environments. Before a new operating system release, storage software, or application version is installed in a production environment, thorough testing is needed using production data. This is usually accomplished by copying the production database to a test environment.

In Oracle12c, you can use the CREATE PLUGGABLE DATABASE statement to clone a PDB. This statement clones a source PDB and plugs the clone into the same CDB or remote CDB.

The CREATE PLUGGABLE DATABASE statement copies the files associated with the source PDB to a new location and associates the files with the target PDB.

Different types of Clauses:

1.       Cloning a Local PDB Using No Clauses

No clause means No structural changes.

Ex: sql> create pluggable database clone_pdb from pdb;

Here, clone pdb has created with same size and same roles with different GUID in default disk group.

2.       Cloning a Local PDB With the PATH_PREFIX and FILE_NAME_CONVERT Clauses

The FILE_NAME_CONVERT clause is required to specify the target locations of the copied files. In this example, the files are copied from one location to another location.

EX:  sql> create pluggable database clone_pdb from pdb PATH_PREFIX= '+BRLOAD’ FILE_NAME_CONVERT = ('+DATA','+BRLOAD');

Here, source pdb datafiles are stored in ‘+DATA’ disk group, clone pdb datafiles are placed in different diskgroup ‘+BRLOAD’, you can use FILE_NAME_CONVERT clause to convert the datafiles location from ‘+DATA’ to ‘+BRLOAD’.

3.       Cloning a Local PDB Using the FILE_NAME_CONVERT and STORAGE Clauses.

Storage limits must be enforced for the PDB. Therefore, the STORAGE clause is required. Specifically, all tablespaces that belong to the PDB must not exceed 2 gigabytes, and the storage used by the PDB sessions in the shared temporary tablespace must not exceed 100 megabytes.

EX: sql> create pluggable database clone_pdb from pdb FILE_NAME_CONVERT= ('+DATA','+BRLOAD') STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

 Steps to clone a local PDB:

This section describes cloning a local PDB. After cloning a local PDB, the source and target PDBs are in the same CDB.

You can clone PDB using following steps:

Step 1: Check the source pdb status

  Sql>show pdbs;

      CON_ID   CON_NAME    OPEN MODE      RESTRICTED

          2    PDB$SEED       READ ONLY   NO

          3    PDB1           READ WRITE  NO

 PDBs intended to be cloned have to be READ ONLY state, if it exists in READ WRITE mode, change the status using below command

Sql> alter pluggable database pdbname close instances=all;

Here, instances=all means close the PDB in all nodes.

Sql> alter pluggable database pdb open read only instances=all;

Sql>show pdbs;

      CON_ID   CON_NAME    OPEN MODE      RESTRICTED

          2    PDB$SEED       READ ONLY   NO

          3    PDB1           READ ONLY   NO

Step 2:  Chose the required options from different cloning clauses mentioned in section2,

Sql> create pluggable database clone_pdb1 from pdb1; (no clauses)

Where pdb1 is source and clone_pdb1 is target

Step 3: Cloned PDB will be in mounted mode, and its status is NEW. You can view the status of a PDB by querying the STATUS column of the CDB_PDBS or DBA_PDBS view.

After that change the state of pdb using alter pluggable database and then you need to add the PDB service in tnsnames.ora in all nodes.

3. Unplug and plug a PDB to CDB:

Unplugging a PDB:

Unplug a PDB disassociates the PDB from a CDB. The unplug operation makes some changes in the PDB's data files to record, for example, that the PDB was successfully unplugged. Because it is still part of the CDB, the unplugged PDB is included in an RMAN backup of the entire CDB. Such a backup provides a convenient way to archive the unplugged PDB in case it is needed in the future. The PDB must be closed before it can be unplugged. When you unplug a PDB from a CDB, the unplugged PDB is in mounted mode

Steps to unplug a PDB:

Check the source pdb status

 Sql>show pdbs;

      CON_ID   CON_NAME    OPEN MODE      RESTRICTED

          2    PDB$SEED       READ ONLY   NO

          3    PDB1           READ WRITE  NO

If the source PDB is in read write mode, you should change the PDB state to MOUNTED state.

Sql> alter pluggable database PDBNAME close instances=all;

Connect to the root container and execute below command

Sql> alter pluggable database pdb1 unplug into /u01/app/oracle/pdb1.xml’;

Here, creates the pdb1.xml metadata file in the /u01/app/oracle directory.

Using XML metadata file, you can plug the PDB into

3.1. same CDB

3.2. remote CDB

You can plug the PDB to CDB using create pluggable database and specify the XML file with different clauses.

Different types of clauses:

Using source_file_name_convert, no copy clause:
SOURCE_FILE_NAME_CONVERT: the XML file does not show the current file location. That time we can use this clause. The XML indicates that the files are in +DATA, but actual files are in +DATA1.

Source_file_name_convert = (‘+DATA’,’+DATA1’)

+DATA= string1   +DATA1=string2

Here, string2 file name pattern replaces the string1 file name pattern.

2.       Using path_prefix, copy, file_name_convert clause:

PATH_PREFIX: this clause is used to specify absolute path of datafiles location.

FILE_NAME_CONVERT: you can use this clause when the files are not at target location and then tou want to copy or move them during the PDB creation.

3.       Using FILE_NAME_CONVERT,MOVE,SOURCE_FILE_NAME_CONVERT clause:

Xml file does not describe the current files location, so source file name convert is required. Example: xml file indicates files are in ‘+DATA’, but files are in ‘/u01/app’.

File name convert is required to move the files from   ‘/u01/app’ to ‘+DATA1’.

3.1. plug the unplugged pdb to the same CDB:

The PDB must be dropped from the CDB before it can be plugged back into the same CDB.

 Create pluggable database using XML file with different methods.

a)       NOCOPY Method

Sql>Create pluggable database pdb1 using ‘/u01/app/oracle/pdb1.xml’ nocopy;

b)      COPY Method

Sql> Create pluggable database pdb1 using ‘/u01/app/oracle/pdb1.xml’ copy file_name_convert=(‘+DATA/pdb1/’,’+DATA1’/pdb1/’);

c)       AS CLONE MOVE Method

Sql> Create pluggable database pdb1 as clone using ‘/u01/app/oracle/pdb1.xml’ move file_name_convert=(‘+DATA/pdb1/’,’+DATA1’/pdb1/’);

3.2. Plug the unplugged pdb to remote CDB:

Prerequisites:

A)      They must have same endian format.

B)      They must have same set of database options installed.

SOURCE:

1.   After unplugging the PDB, transfer the XML to the target location and also transfer the database files from disk group (+DATA) to OS location (/home/) using ASMCMD cp or DATA_FILE_NAME_CONVERT command.

2.   Transfer the database files from source to target OS location. 

TARGET:

Once you get the XML and database files from source, you can use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine whether the unplugged pdb is compatible to the TARGET CDB.

If you get an output ‘YES’, then PDB is compatible.

If you get an output ‘NO’, then PDB is not compatible and check the view PDB_PLUG_IN_VIOLATIONS for Errors.

While creating a remote PDB, Choose required clauses from section3,

EX: Sql> Create pluggable database pdbname using ‘/home/oracle/pdbname.xml’

      Source_file_name_convert = (‘+SOURCE_DATA’,’/home/oracle/datafiles’)

      Move

      File_name_convert = (‘/home/oracle/datafiles’,’+TARGET_DATA’);

Description:  xml file specifies PDB datafiles location in terms of ASM disks, but actual files reside in ‘/home/oracle’ location. Source_file_name_convert clause helps to replace file name pattern and point the actual source location. file_name_convert clause helps in movement of data from OS location to ASM disks.

4. Migrate 12c-noncdb to PDB:

Please refer this whitepaper.