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.
- 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.
- 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.
- 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.
- 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.
- 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
- Connect to root with CREATE
PLUGGABLE DATBASE privileges.
- Configure a storage location (and
ensure disk space) for the new database’s files.
- 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
- Connect to root with CREATE
PLUGGABLE DATBASE privileges.
- Configure a storage location
(and ensure disk space) for the new database’s files.
- Select a unique name for the
new database.
- 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.
- 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.
- 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
- First configure adequate storage
at the site which will soon have a new PDB.
- Then, unplug the source database by executing an ALTER
PLUGGABLE DATABASE <plug1> unplug into <xml-file>
- This will create a .XML file and
detach the pluggable database.
- 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:
- /u01/app/oracle/oradata/cdb2/pdb1/system.ora
- /u01/app/oracle/oradata/cdb2/pdb1/sysaux.ora
- /u01/app/oracle/oradata/cdb2/pdb1/users.ora
These
paths will change as highlighted in bold after plug-in.
- /u01/app/oracle/oradata/cdb1/newpdb/system.ora
- /u01/app/oracle/oradata/cdb1/newpdb/sysaux.ora
- /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
- Non-cdb is Oracle 12c database
- It has been shutdown
consistently and open read only.
- Storage has been configured
for the new pluggable database.
Steps
- Start up the source noncdb
database in read only mode after clean shut down.
- Run the dbms_pdb.describe
procedure and generate the xml file
- Shut down the noncdb, and run
the Create database statement.
- In the CDB, connect to the new
container and run the migration script
$ORACLE_HOME/rdbms/admin/non_cdb_to_pdb.sql.
- Open the new database in
read-write mode at least once.
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.
SQL> begin
dbms_pdb.describe(pdb_descr_file => '/home/oracle/noncdb1.xml');
end;
/
PL/SQL procedure successfully
completed.
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.
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.
No comments:
Post a Comment