Heres a quick look at some of the new features introduced in 12.1.0.2 around Pluggable and Container databases.
PDB CONTAINERS Clause
Using the CONTAINERS clause, from the root container we can issue a query which selects or aggregates data across multiple pluggable databases.
For example each pluggable data can contain data for a specific geographic region and we can issue a query from the root container database which will aggregate the data obtained from all individual regions.
The requirement is that we have to create an empty table in the root container with just the structure of the tables contained in the PDB’s.
In this example we have a table called MYOBJECTS and the pluggable databases are DEV1 and DEV2.
Each pluggable database has its own copy of the MYOBJECTS table.
We have a common user C##USER who owns the MYOBJECTS table in all the pluggable databases.
SQL> alter session set container=dev1;
Session altered.
SQL> select count(*) from myobjects
2 where object_type='TABLE';
COUNT(*)
----------
2387
SQL> alter session set container=dev2;
Session altered.
SQL> select count(*) from myobjects
2 where object_type='TABLE';
COUNT(*)
----------
2350
Now connect to the root container. We are able to issue a query which aggregates data from both Pluggable databases – DEV1 and DEV2.
Note the root container has a table also called MYOBJECTS – but with no rows.
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select con_id, name from v$pdbs;
CON_ID NAME
---------- ------------------------------
2 PDB$SEED
3 DEV1
4 DEV2
SQL> select count(*) from myobjects;
COUNT(*)
----------
0
SQL> select count(*) from containers ( myobjects)
2 where object_type='TABLE'
3 and con_id in (3,4);
COUNT(*)
----------
4737
PDB Subset Cloning
12.1.0.2 extends database cloning where we can just clone a subset of a source database. The USER_TABLESPACES clause allows us to specify which tablespaces need to be available in the new cloned pluggable database
In this example the source Pluggable Database (DEV1) has two tablespaces with application data located in two tablespaces – USERS and TEST_DATA.
The requirement is to create a clone of the DEV1 pluggable database, but the target database only requires the tables contained in the TEST_DATA tablespace.
This would be useful in a case where we are migrating data from a non-CDB database which contains multiple schemas and we perform some kind of schema consolidation where each schema is self-contained in its own pluggable database.
Note the MYOBJECTS table is contained in the USERS tablespace and we are creating a new tablespace TEST_DATA which will contain the MYTABLES table. The cloned database only requires the TEST_DATA tablespace
SQL> alter session set container=dev1;
Session altered.
SQL> select tablespace_name from dba_tables where table_name='MYOBJECTS';
TABLESPACE_NAME
------------------------------
USERS
SQL> select count(*) from system.myobjects;
COUNT(*)
----------
90922
SQL> create tablespace test_data
2 datafile
3 '/oradata/cdb1/dev1/dev1_test_data01.dbf'
4 size 50m;
Tablespace created.
SQL> create table system.mytables
2 tablespace test_data
3 as select * from dba_tables;
Table created.
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata/cdb1/dev1/system01.dbf SYSTEM
/oradata/cdb1/dev1/sysaux01.dbf SYSAUX
/oradata/cdb1/dev1/dev1_users01.dbf USERS
/oradata/cdb1/dev1/dev1_test_data01.dbf TEST_DATA
We now will create the clone database – DEV3 using DEV1 as the source. Note the USER_TABLESPACES clause which defines the tablespaces which we want to be part of the cloned pluggable database.
SQL> ! mkdir /oradata/cdb1/dev3/
SQL> conn / as sysdba
Connected.
SQL> sho con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE PLUGGABLE DATABASE dev3 FROM dev1
FILE_NAME_CONVERT = ('/oradata/cdb1/dev1/', '/oradata/cdb1/dev3/')
USER_TABLESPACES=('TEST_DATA') ;
Pluggable database created.
SQL> alter pluggable database dev3 open;
Pluggable database altered.
If we connect to the DEV3 database we can see a list of the data files which the PDB comprises of .
We can see that the datafile which belongs to the USERS tablespace has the MISSING keyword included in its name. While wed can now select from tables which were contained in the TEST_DATA tablespace on source (like MYTABLES), we cannot access tables (obviously) which existed in other tablespaces which were not part of the USER_TABLESPACES clause of the CREATE PLUGGABLE DATABASE command like MYOBJECTS.
To clean up the database we can now drop the other tablespaces like USERS which are not required in the cloned database.
SQL> alter session set container=dev3;
Session altered.
select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------- ------------------------------
/oradata/cdb1/dev3/system01.dbf SYSTEM
/oradata/cdb1/dev3/sysaux01.dbf SYSAUX
/u01/app/oracle/product/12.1.0.2/dbs/MISSING00017 USERS
/oradata/cdb1/dev3/dev1_test_data01.dbf TEST_DATA
SQL> select count(*) from system.mytables;
COUNT(*)
----------
2339
SQL> select count(*) from system.myobjects;
select count(*) from system.myobjects
*
ERROR at line 1:
ORA-00376: file 21 cannot be read at this time
ORA-01111: name for data file 21 is unknown - rename to correct file
ORA-01110: data file 21: '/u01/app/oracle/product/12.1.0.2/dbs/MISSING00021'
SQL> alter database default tablespace test_data;
Database altered.
SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.
PDB Metadata Clone
There is an option to also create a clone of a pluggable database with just the structure or definition of the source database but without any table or index user or application data.
This feature can help in the rapid provisioning of test or development environments where just the structure of the production database is required and after the pluggable database has been created it will be populated with some test data.
In this example we are creating the DEV4 pluggable database which just has the data dictionary and metadata of the source DEV1 database. Note the use of the NO DATA clause.
SQL> conn / as sysdba
Connected.
SQL> ! mkdir /oradata/cdb1/dev4
SQL> CREATE PLUGGABLE DATABASE dev4 FROM dev1
2 FILE_NAME_CONVERT = ('/oradata/cdb1/dev1/', '/oradata/cdb1/dev4/')
3 NO DATA;
Pluggable database created.
SQL> alter pluggable database dev4 open;
Pluggable database altered.
SQL> alter session set container=dev4;
Session altered.
SQL> select count(*) from system.myobjects;
COUNT(*)
----------
0
SQL> select count(*) from system.mytables;
COUNT(*)
----------
0
SQL> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------- ------------------------------
/oradata/cdb1/dev4/system01.dbf SYSTEM
/oradata/cdb1/dev4/sysaux01.dbf SYSAUX
/oradata/cdb1/dev4/dev1_users01.dbf USERS
/oradata/cdb1/dev4/dev1_test_data01.dbf TEST_DATA
PDB State Management Across CDB Restart
In Oracle 12c version 12.1.0.1, when we started a CDB, by default all the PDB’s except the seed we left in MOUNTED state and we had to issue an explicit ALTER PLUGGABLE DATABASE ALL OPEN command to open all the PDB’s.
SQL> startup; ORACLE instance started. Total System Global Area 805306368 bytes Fixed Size 2929552 bytes Variable Size 318770288 bytes Database Buffers 478150656 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY DEV1 MOUNTED DEV2 MOUNTED DEV3 MOUNTED DEV4 MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY DEV1 READ WRITE DEV2 READ WRITE DEV3 READ WRITE DEV4 READ WRITE
Now in 12.1.0.2 using the SAVE STATE command we can preserve the open mode of a pluggable database (PDB) across multitenant container database (CDB) restarts.
So if a PDB was open in READ WRITE mode when a CDB was shut down, when we restart the CDB all the PDB’s which were in READ WRITE mode when the CDB was shut down will be opened in the same READ WRITE mode automatically without the DBA having to execute the ALTER PLUGGABLE DATABASE ALL OPEN command which was required in the earlier 12c version.
SQL> sho con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter pluggable database all save state; Pluggable database altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 805306368 bytes Fixed Size 2929552 bytes Variable Size 318770288 bytes Database Buffers 478150656 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> select name,open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY DEV1 READ WRITE DEV2 READ WRITE DEV3 READ WRITE DEV4 READ WRITE
PDB Remote Clone
In 12.1.0.2 we can now create a PDB from a non-CDB source by cloning it over a database link. This feature further enhances the rapid provisioining of pluggable databases.
In non-CDB:
SQL> grant create pluggable database to system; Grant succeeded.
In CDB root – create a database link to the non-CDB:
SQL> create database link non_cdb_link 2 connect to system identified by oracle using 'upgr'; Database link created. SQL> select * from dual@non_cdb_link; D - X
Now shut down the non-CDB and open it in READ ONLY mode.
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 826277888 bytes Fixed Size 2929792 bytes Variable Size 322964352 bytes Database Buffers 494927872 bytes Redo Buffers 5455872 bytes Database mounted. SQL> alter database open read only; Database altered.
Create the pluggable database DEV5 from the non-CDB source using the database link we just created.
CREATE PLUGGABLE DATABASE dev5 FROM dev1@non_cdb_link
FILE_NAME_CONVERT = ('/oradata/cdb1/dev1/', '/oradata/cdb1/dev5/');
After the PBD has been created we will now need to run the noncdb_to_pdb.sql script and then open the PDB.
SQL> alter session set container=dev5; Session altered. SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql SQL> alter pluggable database open; Pluggable database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/cdb1/undotbs01.dbf /oradata/cdb1/dev5/system01.dbf /oradata/cdb1/dev5/sysaux01.dbf /oradata/cdb1/dev5/users01.dbf /oradata/cdb1/dev5/aq01.dbf
