12.1.0.2 Multitenant Database New Features

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


关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569