OracleDBPro - One of the Top 60 Database Blogs on the web!

Last week I've received a very kind notification from Anuj Agarwal, the Founder of Feedspot, that this blog has been selected by their panelist as one of the Top 60 Database Blogs on the web as well as one of the Top 60 Oracle Blogs on the web.

I'd like to thank you all for following this blog and for finding the information here to be interesting and useful for your job as DBAs.

Thank you Anuj Agarwal and Feedspot for this nice recognition.

Changes in AWR behavior in versions 12cR1 and 12cR2

Introduction

AWR reports are available as part of the Diagnostics pack (extra cost option to the Enterprise Edition). AWR reports are commonly used by Oracle DBAs and they can be extremely useful when diagnosing performance issues. In this short article I will show the differences between AWR in version 12c Release 1 (12.1) and 12c Release 2 (12.2)

12c Release 1 (12.1) with Multitenant

In Oracle 12c Release 1, Oracle introduced the Multitenant architecture (I've posted a separate article to cover the great benefits of Oracle Multitenant. Read more here). However, the main challenge with Oracle 12cR1 is that AWR data is stored at the CDB$ROOT container level. This has several implications:

• AWR reports are available only at CDB level - it is not possible to run AWR for a specific pluggable database.
• AWR management operations (e.g. snapshots schedule, data retention, taking manual snapshots, purging snapshots) could be done only at CDB level.
• Unplugged PDB does not contain AWR information, so when unplugging a PDB and plugging it into a different CDB all the AWR data will be lost.

Having said that, it's important to mention that even that it is only possible to generate AWR reports at CDB level, Oracle added column "PDB Name" to the tables in the AWR so it allows us to understand to which PDB the information is associated with. Here is an example:

12c Release 2 (12.2) with Multitenant

Good new is that in Oracle 12c Release 2, Oracle added AWR at PDB-Level meaning that AWR information will also be stored in each PDB (under SYSAUX tablespace). This has several implications:

• AWR reports are available at both CDB and PDB level
• AWR management operations (e.g. snapshots schedule, data retention, taking manual snapshots, purging snapshots) can be done at both CDB and PDB level
• Unplugged PDB does contain AWR information, so when unplugging a PDB and plugging it into a different CDB all the AWR data will be available.

Let's see an example of how it looks like when running AWR report in 12c Release 2:

As you can see, Oracle enables us to choose whether we would like to run a CDB-level AWR report by specifying "AWR_ROOT" which is the default, or running a PDB-level AWR report by specifying "AWR_PDB". If you choose PDB-level AWR, Oracle will generate a report that displays performance data only for a particular PDB, as follows:

Pro Tip

In Oracle 12c Release 2, Oracle will automatically create AWR snapshots only at CDB-level by default. If you would like to change this default behavior and enable automatic AWR snapshots for PDBs, you would need to alter the new AWR_PDB_AUTOFLUSH_ENABLED parameter which can be set at either CDB level or PDB level.

My Upcoming Speaker Events for H1 2017

I'm very excited to share with you that I've been accepted to speak at 2 Oracle events that will take place in the first half of 2017: The OUGN and IOUG Collaborate.

OUGN 17

This year the Oracle User Group Norway event will take place from 8 - 11 February in Norway.
The title of my presentation is "Winning Performance Challenges in Oracle Multitenant Architecture". In this session we will cover how to effectively monitor and diagnose performance issues in Oracle Multitenant environments and if needed implement resource management plans to ensure high QoS (Quality of Service) for the pluggable databases. 
Here is a link to my session: https://guidebook.com/guide/85471/event/15235506/

IOUG Collaborate 17

This year the IOUG Collaborate event will take place from 2- 6 April in Las Vegas.
The title of my presentation is "Database Consolidation Using the Oracle Multitenant Architecture". In this session we will explore the new Oracle Multitenant architecture as well as some tools and best practices that will help you consolidate your databases and ensure a high SLA for each pluggable database. I've already presented this session at the IOUG COLLABORATE 16 and OOW 16 so I'm very happy to present this interesting topic again at the IOUG Collaborate 17. 
Here is a link to my session: https://app.attendcollaborate.com/event/member/318780

Hope to see you there!

My favorite Oracle 12cR2 Multitenant Enhancements

Introduction

Oracle 12c Release 1 (12.1) introduced the new Multitenant architecture which is the highlight feature of Oracle 12c. The main purpose of Multitenant architecture is to simplify Database Consolidation by allowing to have multiple Pluggable Databases which are associated to the same instance or instances (in case of RAC). Each pluggable database is a self-contained, independent Database with its own schemas, data, etc. Pluggable Database is a “regular” Database from the application standpoint. This is a new paradigm compared to previous releases (i.e. pre 12c) which allowed to have only one single database associate to an Oracle instance. You can read more about Multitenant in my blog post here.

Oracle Multitenant 12c Release 1 - The Main Challenges

Even though Oracle 12c Multitenant announcement was very exciting, it still had several areas in the first release of Oracle 12c (i.e. 12cR1) which were problematic for DBAs - I've wrote a blog post this named "Where Oracle 12c Multitenant can (and should) be improved". The main items are:

• Flashback Database - In Oracle 12cR1 there is no PDB-level flashback database. It is only possible to flash back the entire Container Database (CDB) with all its associated PDBs. This effectively means that all the PDBs lose data during a Flashback Database operation.
• Memory Resource Management - Oracle 12c allows to ensure quality of service by defining resource plan via Oracle DBRM feature (Database Resource Manager) in order to prioritize CPU resources to pluggable databases across the container; however, in Oracle 12cR1 there is no way to limit or prioritize the memory usage by competing pluggable databases within the same CDB.
• PDB Cloning - Oracle 12c allows fast provisioning by cloning a PDB from another PDB within the same CDB or by cloning a PDB from another PDB in a remote CDB. The only problem is that Oracle 12cR1 support "cold cloning", i.e. the source PDB must be in a READ ONLY mode which essentially means that a down time is required during the clone operation. 

My Favorite Oracle Multitenant 12c Release 2 Enhancements

Based on Oracle's "Oracle Multitenant 12cR2 New Features" white paper, all of the above challenges were addressed in the latest 12cR2:

• Flashback Database - Flashback PDB is now fully supported with Oracle 12R2. In order to enable this feature, Oracle introduced the concept of local undo in 12cR2 which allows a PDB to have its own undo (In Oracle 12cR1 the undo was shared for the entire CDB). Please note that the shared undo mode is still supported in 12cR2.
• Memory Resource Management - Now with Oracle 12cR2, it is possible to set the following parameters at PDB level (which were previously modifiable only at CDB level):
• SGA_TARGET
• SGA_MIN_SIZE (new in 12.2)
• DB_CACHE_SIZE
• DB_SHARED_POOL_SIZE
• PGA_AGGREGATE_LIMIT
• PGA_AGGREGATE_TARGET
• PDB Cloning - "Hot Clone" is now supported with Oracle 12cR2, i.e. it is possible to clone a PDB while the source PDB is in OPEN READ WRITE mode; hence, the on-line cloning is available without interrupting operations in the source PDB. 

Additional cool 12cR2 Multitenant Enhancements 

Additional great 12c Multitenant features which I believe worth mentioning are:

• 4K PDBs Per Container - In Oracle 12cR1, the maximum number user pluggable databases per CDB was 252. Now with Oracle 12cR2, the limit has been increased to 4,096.
• Lockdown profiles - This feature allows to define granular control over network access, common users, common objects, and administrative features. For example, it can be used to limit developers that in a specific PDB, they can execute ALTER SYSTEM SET command only to set a specific parameter like plsql_warnings. This is done by defining a lockdown profile and apply it to the PDB. This feature could be very useful in some cases as we probably don't wont to grant the ALTER SYSTEM SET to developers as they should not have permissions to change other parameters.
• Character Set at PDB Level - In Oracle 12cR1, character set was defined at CDB level, i.e. all PDBs within the same CDB must be defined with the same character set. Now with Oracle 12cR2, it is possible to define different character sets for different PDBs with the same CDB.
• AWR at PDB Level - In Oracle 12cR1, all the AWR data was stored at the CDB root container (CDB$ROOT), meaning that if a DBA unplugs a PDB and plugs it into a different CDB, all the AWR data will be lost. Now with Oracle 12cR2, AWR data is available at PDB level.
• Data Guard Broker - Now allows to perform a "PDB" level failover. The way it is implemented is by having 2 pairs of CDBs in 2 servers - one pair for the primary and another pair for the standby, with replication in opposite directions. Once a PDB fails, the standby counterpart can be moved to the other CDB within the same server. Since no physical movement of copying files is required (as this involves shared storage), this process can be done with minimum downtime. This feature effectively allows a PDB-level failover to standby without having to fail over the entire CDB.
• Refreshebale PDB - This new feature allows to have a cloned PDB that can be refreshed from another PDB either manually (on-demand) or automatically (scheduled refresh). The way this works is by creating a full clone at first stage (which can be taken with no downtime now with the new 12cR2 hot clone feature), and then there is no need to perform a full clone again because this feature allows applying incremental redo since the last clone or the last refresh time. This could be very useful in scenarios that the source PDB is very large and we would like to avoid creating a full clone to the entire PDB from scratch every time, because this process takes a long time when the PDB is very large. By having to apply only the incremental redo since last refresh or last clone, the process of having up to date cloned PDB for dev/test purposes becomes much faster and easier.

Summary

Oracle 12cR2 introduced several significant Multitenant enhancements. In this post, I've listed my favorite Oracle 12cR2 Multitenant enhancements. Note that Oracle 12cR2 is currently available only in Oracle Cloud and not in "regular" on premise deployments.

DOAG 2016 Summary

Introduction

Last week I attended my first DOAG conference (German Oracle User Group) and wanted to share with you my DOAG experience :)
For those of you who are not familiar with DOAG, it is one of the large Oracle conferences out of the US (if not the largest one). I think it was very well organized, I attended some really good sessions, and also presented Quest's products in our booth.

Sessions

Here are some sessions that I specifically remember as great and wanted to mention here:

• Oracle Hacking Session
• Initialization parameters –- why bother?
• Exploring Best Practices for Getting Started with DBaaS
• Multitenant New Security Features Clarify DevOps/DBA roles
• Data Guard in Oracle Database 12.2 - Crash Course

I've presented a session named "Oracle Database Locking Mechanism Demystified" (see picture below from my session) and as always - had lots of fun presenting (quite a lot of people showed up - around 100 attendees). I'd like to thank anyone who attended my session!
Here is a link to my presentation : http://www.slideshare.net/PiniDibask/doag-oracle-database-locking-mechanism-demystified

Booth

For those of you who came to the Quest booth - you probably saw me there as I spent most of my time there showing our Database Monitoring Solutions (Foglight for Oracle and Spotlight for Oracle). I would like to thank everyone that came to experience and watch our team demonstrates our products' capabilities

Hope to see you again next year!