Friday, October 13, 2017

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

Tuesday, October 10, 2017

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!


Thursday, August 31, 2017

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).

Tuesday, June 27, 2017

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.