My Upcoming Speaker Events in H1 2018

Hi everybody,

I'd like to share with you the plans for my upcoming speaking activities for the first half of 2018:

• Israel Oracle User (ILOUG) Group Tech Days 2018 (Jan 22-23) - Very excited to present a session in one of the best Oracle conferences that ever took place in my country. I will be presenting a session titled "Winning Performance Challenging in Oracle Multitenant" on January 23rd about 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.
• Rocky Mountain Oracle User Group (RMOUG) Training Days (Feb 20-22) - This will be my first time attending (and speaking) at this event. I will deliver 2 sessions:
• "Winning Performance Challenging in Oracle Multitenant"- similar to the one I will be presenting at ILOUG
• "Oracle Database Locking Mechanism Demystified" - In this session I will be talking about the concepts of Oracle Database locking mechanism, advanced locking scenarios, best practices to avoid performance issues which relate to blocked SQLs, and ways to investigate lock issues using Oracle dictionary views and performance monitoring tools
• Independent Oracle User Group (April 22-26) - IOUG Collaborate is definitely my favorite Oracle Conference in the world - it's a combination of great location and very technical presentations. I got accepted to present the following sessions:
• ASM Concepts, Architecture and Best Practices - Since it was introduced back in 2003 with Oracle 10g, ASM became an essential part of almost every Oracle DBA's life. In this session you will learn ASM from A to Z - starting with the basic concepts of the ASM all the way to the new features of ASM and important best practices that will help you increase the performance and stability of your Database environments.
• Oracle Data Guard for Beginners - Oracle Data Guard is one of the most important options in the Oracle Database as it allows organizations to ensure high availability, data protection, and disaster recovery for their Oracle database environments. In this session we'll review Oracle Data Guard solution from A to Z.

I invite you all to my sessions and looking forward to meeting you there!

I'm an Oracle ACE - thank you Oracle for this recognition!

Hi everybody,

Yesterday I received the following email from the Oracle ACE Program:

"I am pleased to inform you that you have been accepted to receive the Oracle ACE award. You have been chosen based on your significant contribution and activity in the Oracle technical community. Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a willingness to share your knowledge and experiences with the community."

I received this recognition from Oracle and I'm proud to be part of the awesome Oracle ACE family - thank you Oracle ACE Program!


Thank you IOUG (Independent Oracle User Group) for choosing me to present sessions at IOUG Collaborate 16 & 17 and also for inviting me to present on behalf of the IOUG at Oracle OpenWorld 2016 & 2017. I would also like to thank additional local user groups: DOAG, AOUG, OUNG for choosing me to speak at their annual user group conferences.

Here is a link to my Oracle ACE page: https://apex.oracle.com/pls/apex/f?p=19297:4:::NO:4:P4_ID:16480

ODC Appreciation Day : Online Move Data File Operation

I've decided to dedicate my ODC Appreciation Day blog post to "Online Move Data File Operation" - a feature which has been introduced in Oracle 12c.
As a quick background to this blog post, ODC (Oracle Developer Community) appreciation day is Tim Hall's (Oracle-Base) initiative which started last year (when it was OTN Appreciation Day).

So what is online move data file operation?

Moving data files is a common-use case, for example, when the DBA would like to move data files to a faster/larger disk. Another common use case is when DBA would like to migrate the files from traditional file systems to Oracle's Automatic Storage Management (more information about Oracle ASM is available in my ODC published article : https://community.oracle.com/docs/DOC-995178)

Prior to Oracle 12c, there was no way to accomplish this task with zero downtime. Common solution was to take the entire tablespace or data file offline, move it to the new location, change the location using the "ALTER DATABASE" command (which  essentially changes the location in the Oracle's control file) and then bring the tablespace/file online again; however, during this operation the entire tablespace or datafile won't be accessible. Better approach to mitigate the downtime was to change the tablespace or data file to be in a read-only mode which will at least allow queries to be executed against the data which reside on that tablespace/file - but it's still not favorable because DMLs/DDLs cannot be executed during that operation.

Oracle 12c allows us to do this completely online with zero downtime - while the database is open and users are accessing the data file. I've presented this feature with several syntax examples as part of my IOUG Collaborate 16 presentation - "Best New Features of  Oracle Database 12c" (slide deck is available here: https://www.slideshare.net/PiniDibask/best-new-features-of-oracle-database-12c).

You could also find several examples and even a short video which demonstrates this cool feature in the Oracle documentation: https://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837

Summary

Whether running Oracle databases on premise or in the cloud, this feature is very useful. When Larry Ellison spoke at Oracle OpenWorld 2017 keynotes about Oracle 18c and Oracle's option of to ensure having up to 30 minutes down-time a year with their cloud offering, this feature is another useful way  that can help when it comes to ensuring zero impact on the customer's applications (among the many other Oracle's HA & DR features - such as ASM, RAC Rolling Upgrades, Active Data Guard, Flashback, etc.)

My Oracle OpenWorld 2017 Presentation - Slide Deck is Now Available!

Last week, on October 1st, I've presented a session at Oracle OpenWorld about Database consolidation using Oracle Multitenant option.
I was invited to speak on behalf of the IOUG (Independent Oracle User Group) at Oracle OpenWorld for the second year in a row! Thank you IOUG for this great opportunity! It was fun (as always).

I got several requests to share my presentation - so here you go: https://www.slideshare.net/PiniDibask/oow-17-database-consolidation-using-the-oracle-multitenant-architecture

Oracle OpenWorld 2017 was a great experience, with some exciting announcements about the future Oracle 18c release (I will write a separate blog post about this upcoming release).

I'd also like to thank everybody who attended my session, and hope to see you again next year!

Common misconception about how Oracle handles deadlocks

Introduction

In today's blog post, I'd like to uncover a common misconception about how Oracle handles deadlocks, but first let's talk about what deadlock is. Deadlock is a locking scenario that occurs when two or more sessions are blocked as they wait because of a lock that is being held by the other session. Oracle Database automatically detects this scenario and handles this because otherwise, they will wait forever as both of them are blocked and waiting to each other’s locked resources. But the question is how does Oracle handle this? some would say that Oracle handles these scenarios by terminating one of the sessions which is known to be as the "deadlock victim" - well, that's wrong. Others would say that by rolling-back the entire transaction of one of the sessions - well, that's also wrong. So how Oracle really handles deadlock scenarios?

Deadlock Illustration

Let's see a quick example of how deadlock occurs

Step 1

Session #1 performs an update of a row (employee #151) and acquires a lock on that row

SQL> UPDATE employee
          SET first_name = 'David'
          WHERE employee_id = 151;

1 row updated.

Step 2

Session #2 performs an update of a row (employee #39) and acquires a lock on that row

SQL> UPDATE employee
          SET first_name = 'Greg'
          WHERE employee_id = 39;

1 row updated. 

Step 3

Session #1 performs an update of a row (employee #39) and is now waiting since a lock has been acquired on the same row which has not been released yet as session #2 transaction is still active

UPDATE employee
          SET first_name = 'Mark'
          WHERE employee_id = 39;

Step 4

Session #2 performs an update of a row (employee #151) and is now waiting since a lock has been acquired on the same row which has not been released yet as session #1 transaction is still active.  

SQL> UPDATE employee
          SET first_name = 'John'
          WHERE employee_id = 151;

At this stage both sessions (session #1 and session #2) are blocked  and waiting to each other’s locked resources - that's exactly what deadlock is.

So how Oracle handles deadlock scenarios?

The way Oracle handles deadlock scenarios is not by terminating one of the sessions or performing a transaction-level rollback, it's actually just by performing a statement-level rollback to one of the sessions.  The session that its statement is being rolled back, will encounter an “ORA-00060: Deadlock detected while waiting for resource.” error message (that will also be recorded in the alert log file).

Summary of Austrian Oracle User Group (AOUG) 2017 conference

Last week (on June 20th) I presented a session about the Oracle Database Locking Mechanism at the Austrian Oracle User Group (AOUG) conference.
This event took place in beautiful Vienna and was my first attending this event.
It's a small, one-day event . There were 180 attendees - small, but still very fun well-organized.
I particularly enjoyed the keynote of Maria Colgan & Gerald Venzl (both Product Managers for Oracle) about Oracle Database 12c and DevOps - it was a very amusing show in which Maria played the role of the DBA and Gerald played the role of the developer and together they showed how they can work together happily by leveraging Oracle Database 12c features and technologies.
Another session that I particularly enjoyed was presented by Maris Elsins about Mining the AWR and how to find alternative Methods for Identification of the Top SQLs in order to address various performance issues.

You can find in event page links to my presentation as well as other presentations from this event.
I'd like to thank everyone who attended my session, and I hope to see you again next year.

SYSBACKUP and SYSDG permissions in Oracle 12c

Introduction

In many companies there is a clear separation of duties for various Oracle Database related tasks such as administering ASM and backing up/restoring Oracle databases.

In the past, DBAs used SYSDBA permission for administering ASM and RMAN. As you probably know, SYSDBA is the most powerful permission in Oracle Database which even allows viewing all the application data.

Oracle realized that they need to address the separation of duties requirement of many customers and therefore they have provided in Oracle 11g a dedicated permission for administering ASM - I've written a dedicated blog post in the past for this matter. The SYSASM permission cannot access application data, but it can perform various ASM related management tasks (such as altering diskgroup, adding disks, etc.)

What about RMAN?

Until Oracle Database version 12cR1, there wasn't a good solution from a separation of duties when it comes to RMAN backups as users had to use SYSDBA which also allows them to access any application data (as well as other strong permissions).

In Oracle 12cR1, Oracle introduced the SYSBACKUP permission which allows a user to perform backup and recovery operations either from Oracle Recovery Manager (RMAN) or SQL*Plus.

You can view here the full list of operations allowed by this administrative privilege

And what about Data Guard?

Very similar to RMAN, Oracle also introduced in version 12cR1 a dedicated privilege named SYSDG which can be used with the Data Guard Broker and the DGMGRL command-line interface. 

Demo

First, we can connect to a 12c instance and look for those accounts. Next step would be to connect / AS SYSBACKUP since I'm logged with a user that has OS permissions to connect without any username and password

SQL> SELECT username, account_status
  FROM dba_users
 WHERE username LIKE '%SYS%';

USERNAME   ACCOUNT_STATUS
---------- --------------------------------
SYS        OPEN
SYSTEM     OPEN
SYS$UMF    EXPIRED & LOCKED
APPQOSSYS  EXPIRED & LOCKED
GGSYS      EXPIRED & LOCKED
WMSYS      EXPIRED & LOCKED
SYSBACKUP  EXPIRED & LOCKED
SYSRAC     EXPIRED & LOCKED
AUDSYS     EXPIRED & LOCKED
SYSKM      EXPIRED & LOCKED
SYSDG      EXPIRED & LOCKED

SQL> connect / as sysbackup
Connected.
SQL> show user
USER is "SYSBACKUP"

I can also create a new user and grant him the SYSBACKUP or SYSDG permissions

SQL> connect / as sysdba
Connected.

SQL> create user C##PINI identified by PINI;
User created.

SQL> grant SYSBACKUP to C##PINI;
Grant succeeded.

SQL> select username,SYSBACKUP, SYSDG from V$PWFILE_USERS;

USERNAME   SYSBA SYSDG
---------- ----- -----
SYS        FALSE FALSE
SYSDG      FALSE TRUE
SYSBACKUP  TRUE  FALSE
SYSKM      FALSE FALSE
C##PINI    TRUE  FALSE

Note that in order to connect to the database as either SYSDG or SYSBACKUP using a password, there must be a password file for it because it is possible to connect even when the database is not up and running, as follows

SQL> connect / as sysdba
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  8793400 bytes
Variable Size             989856456 bytes
Database Buffers          637534208 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> connect c##pini/pini
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.

SQL> connect c##pini/pini as SYSBACKUP;
Connected.

Summary

In this post we've reviewed the SYSDG and SYSBACKUP users and permissions in Oracle 12c which could be useful in case that in your company there is a requirement to have a separation of duties for backup/recovery as well as for Data Guard related administration tasks. I hope you find it useful for you.

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!