Oracle Database 12c – Container Database & Pluggable Databases

Oracle 12c Pluggable databases allow DBAs to consolidate large number of Oracle database applications into a single, larger RDBMS installation.

Before Oracle 12c, if you have large number of Oracle Database Applications we have to include the following factors.

• Too many background processes (Based on Oracle Database Instances)
• High shared / Process memory (Memory Allocations for the SGAs
• Many copies of Oracle Metadata (Storage space for Data Dictionaries for multiple databases)
• Upgrading multiple oracle database instances time-consuming process for DBAs
• Multiple backup sets and schedules for multiple databases.  

 

Oracle 12c has brought the following advantages for multiple Oracle database instances in one Oracle Database Server.

• Oracle 12c operates multiple oracle database instances in a centrally managed platform
• DBAs time saving for patching and upgrade
• No application changes required for Oracle database consolidation
• Fast and easy to provision multiple databases in Centralized platform
• Provides Isolation
• Fully operates with Oracle Real Application Clusters (RAC)
• A machine can run more number of oracle database instances in the form of Pluggable Databases (PDBs) than as individual Oracle database instance.

A Container database in Oracle database 12c is a set of database schemas that appears logically to users and applications as separate Oracle database

In Oracle database 12c, an instance is associated with an entire CDB (Container database).

Consider the following:

Container Database    : cdb1

Pluggable databases   : pdb1 & pdb2

Query to check whether the Database is Multitenant Database CDB:

Information on Pluggable Databases (PDBs) in Container Database (CDB):

 Query on currently connected instance:

 

 Query on SGA Usage by Pluggable Databases (PDBs):

 

Query on PGA Usage by Pluggable Databases (PDBs) :

Connection Information for Container Database and Pluggable Databases:

Connection Information for Container Database and Pluggable Databases:

 Information on Datafiles Of Container Database and Pluggable Databases:

 

Continued in Part-2

Oracle 12c RAC - New Features

Application Continuity with Oracle 12c RAC:

Oracle Database 12c RAC new feature, Application Continuity transparently replays (Re-directed) database requests to another RAC database instance after a failed database session.

Application Continuity with 12c RAC provides:

1.       Faster reconnect and replay to available RAC database instance.

2.       This feature protests against a wide range of failure scenarios.

Oracle 12c RAC with Flex ASM:

Oracle Database 12c RAC new feature, Flex ASM allows Oracle database instances to connect to another ASM instance on another node in the Cluster.

Flex ASM with 12c RAC provides:

1.       If an ASM instance fails in the cluster, Its user session connects to another ASM Instance automatically and transparently

2.       Flex ASM brings more flexibility to RAC availability

3.       Flex ASM frees resources in the cluster which can be used by RAC

Oracle 12c RAC with Multitenant:

Oracle Database 12c RAC new feature, Multitenant enables multiple Pluggable Databases (PDBs) to share the resources (Memory, Back Ground Processes etc) of single Container Database (CDB)

Oracle 12c RAC with Multitenant provides:

1.       Each Pluggable Database is exposed as a Service

2.       Enables more efficient DB Consolidation

3.       The basic idea is that an existing database can be simply adopted in 12c environment with no changes in the application tier as a pluggable database.

 

Oracle Exadata Demo @Sangam13 - AIOUG (All India Oracle User Group), Hyderabad, India.

Download all Oracle Open World (OOW) 13 Technical Presentations

https://blogs.oracle.com/oracleopenworld/entry/download_em_now_oracle_openworld

Index Elimination with Oracle Exadata

Objective : How to make an index invisible so that you can test the effect on your queries without actually dropping the index.

Step1: Connect as 'SYS' user and flush caches

SQL> connect sys/welcome1@xdbvm as sysdba
Connected.

SQL> alter system flush shared_pool;
System altered.

SQL> alter system flush buffer_cache;
System altered.

Step2: Connect as 'SH' user and check the indexes status and make the index 'INVISIBLE'

SQL> connect sh/welcome1@xdbvm
Connected.

SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';

INDEX_NAME                       STATUS   VISIBILITY
------------------------------   --------      -------------
CUSTOMERS_PK                   VALID     VISIBLE

SQL> alter index CUSTOMERS_PK invisible;
Index altered.

SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';

INDEX_NAME                     STATUS    VISIBILITY
------------------------------ --------       ------------
CUSTOMERS_PK                   VALID    INVISIBLE

Step3: Execute the following Query after making index 'INVISIBLE'

SQL> set autotrace on explain

SQL> select avg(cust_credit_limit) from customers where cust_id between 2000 and 2500;

AVG(CUST_CREDIT_LIMIT)
----------------------
            5996.80639

Elapsed: 00:00:01.59

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     9 |   405   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE            |           |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| CUSTOMERS |   268 |  2412 |   405   (1)| 00:00:05 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("CUST_ID"<=2500 AND "CUST_ID">=2000)
             filter("CUST_ID"<=2500 AND "CUST_ID">=2000)

Note: Oracle Exadata used 'Smart Scan' feature rather than an Index Range Scan.

Step4: Connect as 'SYS' user and flush caches

SQL> connect sys/welcome1@xdbvm as sysdba
Connected.

SQL> alter system flush buffer_cache;
System altered.

SQL> alter system flush shared_pool;
System altered.

Step5: Connect as 'SH' user and check the indexes status and make the index 'VISIBLE'

SQL> connect sh/welcome1@xdbvm
Connected.

SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';

INDEX_NAME                       STATUS    VISIBILITY
------------------------------    --------      -------------
CUSTOMERS_PK                   VALID      INVISIBLE

SQL> alter index CUSTOMERS_PK visible;
Index altered.

SQL> select index_name, status, visibility from user_indexes where table_name='CUSTOMERS';

INDEX_NAME                        STATUS   VISIBILITY
------------------------------     --------     -------------
CUSTOMERS_PK                   VALID      VISIBLE

Step6: Execute the following Query after making index 'VISIBLE'

SQL> set autotrace on explain
SQL> select avg(cust_credit_limit) from customers where cust_id between 2000 and 2500;

AVG(CUST_CREDIT_LIMIT)
----------------------
            5996.80639

Elapsed: 00:00:02.44

Execution Plan
----------------------------------------------------------
Plan hash value: 3995619262

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |     9 |   265   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE              |              |     1 |     9 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |   268 |  2412 |   265   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | CUSTOMERS_PK |   268 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("CUST_ID">=2000 AND "CUST_ID"<=2500)

Note: This above has used an Index Range Scan instead of Oracle Exadata 'Smart Scan' feature.

Note: Before dropping the Indexes, check the application nature and execution scenarios using 'INVISIBLE' index.