Monday 21 July 2014

Creating a pluggable database in Oracle 12c.

Creating a pluggable database in Oracle Database 12c.

Oracle database 12c introduced the concept of a pluggable database, which can be plugged into a multi tenant container database; unplugged when required, transported and re-plugged into a different container database.

With the introduction of pluggable databases, database creation has become a mostly fast and efficient operation. The following methods exist to create a pluggable database.

  1. Create a pluggable database using the seed container. See Example 1 below.
The seed data files are copied to the new location and used by the new database. The catalog scripts, etc do not need to be executed afresh.

  1. Create a pluggable database by duplicating an existing one. See Example 2 below.
The files from the existing database are copied to the location of the new database. The new database automatically gets assigned new pdb id to distinguish it from its parent.

  1. Create a pluggable database by plugging it in. See Example 3 below.
Because the pluggable database is self-contained, it can be detached from its container and transported to a different container. The action of detaching it is called an unplug operation. The unplug operation results in a .XML file (called a manifest file) which describes the database and has the information necessary for a re-attach (including pdb-specific initialization parameters). This offers another quick method of creating a pluggable database.

  1. Creating a pluggable database from a noncdb source database. See Example # 4 below.
A database created as a non-pluggable database is known as a noncdb. A noncdb is similar to pre-12c databases.

The first step would be to open the noncdb read-only, and generate a self-describing XML file, known as a manifest file. This manifest file can be created using the DBMS_PDB.DESCRIBE procedure.  Having created the manifest file, the database can be plugged into the CDB by either copying the source datafiles or using them in-place.

  1. Creating a pluggable database using Oracle Goldengate
This method uses database replication technology. In this technique, Oracle Goldengate software is configured to capture changes occurring at a source database, and to transmit and apply it at an Oracle 12c PDB destination, until the destination it is fully synchronized.

While this technique requires significant investment and is complex, it offers fault tolerance, availability, and flexibility.

The main requirement imposed on the production database by GoldenGate is that supplemental logging needs to be enabled at the source database for the captured table/columns.



Example  1 - Create a pdb using the seed

To create a pluggable database using the seed use the CREATE PLUGGABLE DATABASE and specify a file_name_convert parameter as shown.

Pre-requisite
  1. Connect to root with CREATE PLUGGABLE DATBASE privileges.
  2. Configure a storage location (and ensure disk space) for the new database’s files.
  3. Select a unique name for the new database.


SQL> create pluggable database PDBTEST
  2   admin user pdbdba identified by oracle
  3   file_name_convert=('/u01/app/oracle/oradata/cdb1/seed',
  4                      '/u01/app/oracle/oradata/cdb1/pdbtest')
  5   ;

Pluggable database created.


After the database is created, it is in the NEW state. To complete the database creation, it is necessary to open it read-write at least once, as shown.

SQL> alter pluggable database pdbtest open read write;

Pluggable database altered.

SQL> column pdb_name format a20
SQL> column status format a20
SQL> select pdb_name,status from cdb_pdbs;

PDB_NAME             STATUS
-------------------- --------------------
PDB$SEED             NORMAL
HRPDB                NORMAL
SALESPDB             NORMAL
PDBTEST              NORMAL


Listing the datafiles of the new database, we see that it has its own system, sysaux and default user tablespace; the undo tablespace is always shared with the CDB.

SQL> alter session set container=pdbtest;   

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/root/undo.ora
/u01/app/oracle/oradata/cdb1/pdbtest/system.ora
/u01/app/oracle/oradata/cdb1/pdbtest/sysaux.ora
/u01/app/oracle/oradata/cdb1/pdbtest/users.ora


Example  2 - Create a pdb by duplicating an existing one

An existing pluggable database can be rapidly duplicated by using the CREATE PLUGGABLE DATABASE FROM <existing-pdb>.

Pre-requisite
  1. Connect to root with CREATE PLUGGABLE DATBASE privileges.
  2. Configure a storage location (and ensure disk space) for the new database’s files.
  3. Select a unique name for the new database.
  4. Place the source pluggable database in read-only mode. For this example we will create a copy of the HRPDB database.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
HRPDB
SALESPDB
PDBTEST

SQL> alter pluggable database hrpdb close;

Pluggable database altered.

SQL> alter pluggable database hrpdb open read only;

Pluggable database altered.


  1. Issue the CREATE PLUGGABLE DATABASE … FROM statement.
SQL> create pluggable database HRCOPY
  2   FROM HRPDB
  3   file_name_convert=('/u01/app/oracle/oradata/cdb1/hrpdb',
  4                      '/u01/app/oracle/oradata/cdb1/hrcopy')
  5  ;

Pluggable database created.


  1. Open it at least once in read-write mode to complete the integration with the CDB.

SQL> alter pluggable database hrcopy open read write;



Example  3 - Create a pdb by a plug-in operation

It is possible to unplug a pluggable database from container A and plug it into container B.
The mechanism for plugging in is by creating a .XML file which describes the metadata of the unplugged database, so that it can be read and processed by the new container.

Overview of steps
  1. First configure adequate storage at the site which will soon have a new PDB.
  2. Then, unplug the source database by executing an ALTER PLUGGABLE DATABASE <plug1> unplug into <xml-file>
  3. This will create a .XML file and detach the pluggable database.
  4. The datafiles belonging to the database along with the XML can be transported to the new site and a simple CREATE PLUGGABLE DATABASE <plug2> using <xml> will accomplish the job.

Example:
There is a pluggable databse called PDB1 in the container database CDB2. The objective is to unplug this database from CDB2, and plug it into the container database CDB1, thus creating a new database in CDB1 using the unplugged database.

First configure storage in the site which has the CDB1 container.
To prepare for plug-in create the folders in the CDB1 container site called newcdb
to house the datafiles.


[oracle@laboms ~]$ mkdir -p /u01/app/oracle/oradata/cdb1/newcdb



Then, make the target pdb is closed consistently, and unplug it.

[oracle@laboms oradata]$ sqlplus sys/oracle@localhost:1521/CDB2 as sysdba
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1    
  2   unplug into '/home/oracle/pdb1_mdata.xml';

Pluggable database altered.


Transport the pdb1_mdata.xml and the datafiles belonging to PDB1 to the CDB1 site.
The datafiles are:
  1. /u01/app/oracle/oradata/cdb2/pdb1/system.ora
  2. /u01/app/oracle/oradata/cdb2/pdb1/sysaux.ora
  3. /u01/app/oracle/oradata/cdb2/pdb1/users.ora

These paths will change as highlighted in bold after plug-in.

  1. /u01/app/oracle/oradata/cdb1/newpdb/system.ora
  2. /u01/app/oracle/oradata/cdb1/newpdb/sysaux.ora
  3. /u01/app/oracle/oradata/cdb1/newpdb/users.ora


Next, plug-in the database, as shown.



SQL> create pluggable database newpdb                                   
  2    using '/home/oracle/pdb1_mdata.xml'
  3   file_name_convert=('/u01/app/oracle/oradata/cdb2/pdb1',
  4              '/u01/app/oracle/oradata/cdb1/newpdb')
  5  storage  (maxsize 1g max_shared_temp_size 100m);

Pluggable database created.

Note:
§        file_name_convert parameter is required to tell Oracle how to translate paths in the .XML such as:
    <file>
      <path>/u01/app/oracle/oradata/cdb2/pdb1/seed/users.ora</path>
      <afn>22</afn>
      <rfn>7</rfn>

to the destination path.

§        The move keyword specifies that source datafile are moved to the destination, not copied.

§        If the source datafiles are not accessible from CDB1 site, and they have been copied to a staging location like ‘/d01/stage’ then the source_file_name_convert parameter becomes necessary to translate xml paths to location of the staging directory so that the files can be copied/moved to their destination in CDB1.

Finally, open the database read-write once to complete the operation.
SQL> alter pluggable database newpdb open read write;

Pluggable database altered.

Example  4 - Create a pdb using a noncdb

Support for integration into a CDB is built within the noncdb. The noncdb is still a 12c database, and it supports a db package called DBMS_PDB which enables the user to create a self-describing XML file describing the PDB for a possible future plug-in operation.

Having created this XML file, it is possible to plug-in the noncdb into the database using a CREATE PLUGGABLE DATABASE pdbtest using <xml-file>. You can optionally copy the noncdb source files to a new location or use it in-place.

Finally, connect to the new pdb, run the Oracle-supplied script named noncdb_to_pd.sql, a migration script, and open then open the database in read write mode.

Pre-requisite
  1. Non-cdb is Oracle 12c database
  2. It has been shutdown consistently and open read only.
  3. Storage has been configured for the new pluggable database.

Steps
  1. Start up the source noncdb database in read only mode after clean shut down.
  2. Run the dbms_pdb.describe procedure and generate the xml file
  3. Shut down the noncdb, and run the Create database statement.
  4. In the CDB, connect to the new container and run the migration script $ORACLE_HOME/rdbms/admin/non_cdb_to_pdb.sql.
  5. Open the new database in read-write mode at least once.

§        Start up the database in read only mode after clean shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2296576 bytes
Variable Size             385877248 bytes
Database Buffers          675282944 bytes
Redo Buffers                5480448 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

§        Running the DBMS_PDB.describe procedure to generate the manifest file

SQL> begin
 dbms_pdb.describe(pdb_descr_file => '/home/oracle/noncdb1.xml');
end;
 /

PL/SQL procedure successfully completed.


§        Shut down the noncdb, and run the Create database statement. In the example below the CREATE PLUGGABLE database statement specifies COPY, which instructs Oracle to copy the data files from the source.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

[oracle@laboms ~]$ sqlplus sys/oracle@localhost:1521/cdb1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 14 16:12:17 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> create pluggable database plug1
  2   using '/home/oracle/noncdb1.xml'
  3    copy
  4     file_name_convert=('/u01/app/oracle/oradata/noncdb1',
  5                        '/u01/app/oracle/oradata/cdb1/plug1')
  6   ;

Pluggable database created.

§        In the CDB, connect to the new container (DO NOT OPEN the database yet)and run the migration script noncdb_to_pdb.sql
SQL> alter session set container=plug1;

Session altered.
SQL > @?rdbms/admin/noncdb_to_pdb.sql;

…
…



§        Finally when the script is done, open in read-write mode (the script takes a while to complete)

SQL> alter pluggable database open read write;

Pluggable database altered.




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)