How can I connect a PDB to the Oracle 12c container database?

February 23, 2020  

You can create a connectable database in the following ways:

• Create a new PDB using propagation.
• Create a new PDB by cloning the current PDB.
• Connect a disconnected PDB to the CBD.
• Create a new PDB from 12c without the CBD.

1. Create a new PDB using propagation:

Create a PDB using the PROPAGATION database. This method copies the files associated with propagation to a new location and associates them with a new PDB. You can easily create a new PDB from PROPAGATION by creating a connectable database.

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

• Storage: You can specify storage limits
• Default Tablespace: creates a small file table space and assigns it to users who do not belong to the system
• path_prefix: specifies the absolute path
• file_name_convert: Specifies the new location of the PDB files from the source files.
• Tempfile reuse: specifies and reuses the tempfile of the target location.
• Roles: Oracle predefined functions to grant them to the PDB_DBA function.

Note : The ROLES clause can only be used when creating a PDB from propagation.

Example: 1

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

The above instruction creates a PDB with the local user PDBUSR. This instruction grants the PDB_DBA function to the "pdbusr" administrator of the PDB and grants the predefined Oracle functions specified to the PDB_DBA function 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') ;

The above statement creates the PDB with the limited size, specifies that all table spaces belonging to the PDB must not exceed 5 gigabytes and creates the users of the default table spaces for users who are not administrators in the new location "+ DATA_NEW". 

2. Create a new PDB by cloning a current PDB:

Cloning of the production database is a common technique used to develop and test changes in applications and associated environments. Before installing a new version of the operating system, storage software or the application version in a production environment, extensive testing using production data is required. Usually, this is done by copying the production database in a test environment.

In Oracle 12c, you can use the CREATE PLUGGABLE DATABASE statement to clone a PDB. This instruction clones a source PDB and connects the clone to the same CBD or remote CBD.

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 without clauses

Without clauses means that there are no structural changes.

Example: sql> create pluggable database clone_pdb from pdb;

Here, the cloned PDB was created with the same size and the same functions with different GUIDs in the 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.

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

Here, the data files of the source PDB are stored in the “+ DATA” disk group, the cloned PDB data files are placed in a different “+ BRLOAD” disk group and you can use the FILE_NAME_CONVERT clause to convert the location of the data files from “+ DATA” to “+ BRLOAD”.

3. Cloning a local PDB with the clauses FILE_NAME_CONVERT and STORAGE

Storage limits for the PDB should apply. Therefore, the STORAGE clause is required. Specifically, all table spaces belonging to the PDB must not exceed 2 gigabytes and the storage used by the PDB sessions in the temporary shared table space must not exceed 100 megabytes.

Example: 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 the cloning of a local PDB. After cloning a local PDB, the source and target PDBs are in the same CBD.

You can clone the PDB using the following steps:

Step 1: Check the status of the source PDB

  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 must have the status READ ONLY, if it exists in READ WRITE mode, change the status with the following command:

Sql> alter pluggable database pdbname close instances = all;

Here, "instances = all" refers to closing the PDB on 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: Select the required options of different cloning clauses mentioned in section 2

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

Where “pdb1” is the origin and “clone_pdb1” is the objective

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

After that, change the status of the PDB by modifying the connectable database and then you must add the PDB service in tnsnames.ora on all nodes.

3. Disconnect and connect a PDB to a CBD:

Disconnecting a PDB:

Disconnecting a PDB disassociates the PDB from a CBD. The disconnect operation makes some changes to the PDB data files to record, for example, that the PDB was disconnected correctly. Because it is still part of the CBD, the disconnected PDB is included in an RMAN backup of the entire CBD. This type of backup provides a convenient way to archive the disconnected PDB if necessary in the future. The PDB must be closed before it can be disconnected. When a PDB is disconnected from a CBD, the disconnected PDB is in mounted mode.

Steps to disconnect a PDB:

Check the status of the source PDB

 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 and write mode, you must change the status of the PDB to the MOUNTED state.

Sql> alter pluggable database PDBNAME close instances = all;

Connect to the root container and run the following command:

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

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

With the XML metadata file, you can connect the PDB at:

3.1. Same CBD

3.2. Remote CBD

You can connect the PDB to the CBD by creating a connectable database and specifying the XML file with different clauses.

Different types of clauses:

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

Source_file_name_convert = ('+ DATA', '+ DATA1')

+ DATA = string1 + DATA1 = string2

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

2. With the path_prefix, copy and file_name_convert clauses:

PATH_PREFIX: This clause is used to specify the absolute path of the location of the data files.

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

3. With the clauses FILE_NAME_CONVERT, MOVE, SOURCE_FILE_NAME_CONVERT:

The XML file does not describe the current location of the files, so it is necessary to convert the name of the source file. Example: The XML file indicates that the files are in “+ DATA”, but the files are in “/ u01 / app”.

Conversion of the file name is required to move the files from “/ u01 / app” to “+ DATA1”.

3.1. Connect the disconnected PDB to the same CBD:

The PDB must be removed from the CBD before it can be connected again on the same CBD.

 Create a connectable database using an XML file with different methods.

a) NOCOPY

Sql method > Create pluggable database pdb1 using '/u01/app/oracle/pdb1.xml' nocopy;

b) COPY

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

c) AS CLONE MOVE

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

3.2. Connect the disconnected PDB to the remote CBD:

Prerequisites:

A) They must have the same endian format.

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

ORIGIN:

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

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

OBJECTIVE:

Once you obtain the XML and database files from the source, you can use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function to determine if the disconnected PDB is compatible with the OBJECTIVE CBD.

If you get a “YES” result, the PDB is compatible.

If you get a “NO” result, the PDB is not compatible and check for errors in the PDB_PLUG_IN_VIOLATIONS view.

During the creation of a remote PDB, select the required clauses shown in section 3

Example: 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: The XML file specifies the location of the PDB data files for ASM disks, but the actual files are located in the “/ home / oracle” location. The Source_file_name_convert clause helps replace the pattern of file names and indicate the actual location of the source. The file_name_convert clause helps in moving data from the location of the OS to ASM disks.