Adaptive cursor sharing was introduced by Oracle in 11g release 1 as a way to generate best plan for a SQL in all situation. Prior to adaptive cursor sharing, optimizer used to generate a single plan for a SQL statement and that plan is used by all cursors of that SQL_ID. So if we have data skewness in a column and that column is being using in “where” clause of the SQL statement, single plan of that SQL will not (more...)
Table recovery was possible in earlier release as well. Until previous release, if we wanted to recover a table, we had following options
- Database point in time recovery (DBPITR)
- Tablespace point in time recovery (TSPITR)
- Flashback technology
In Oracle 12c, RMAN has been enhanced to perform recovery of table. We have a new command in RMAN which automates complete process of recovering the table. New process does not affect the existing objects in the database (more...)
Sometimes we end up in a situation where our long running transaction is not completing and we are also not sure how much further time it’s going to take. This happened with one of our DBA where they found MLOG to be bloated because of one orphan snapshot entry. Orphan entries are the one where actual site is not registered on master (no entry in DBA_REGISTERED_SNAPSHOTS), but they see entry for MLOGS (entry in DBA_SNAPSHOT_LOGS). This could happen if (more...)
One of the major challenge faced by an Oracle DBA is – how to effectively deal with historical data? Today, if we consider database tables for an enterprise, data in the table goes back several years and most of the data in the table is inactive. Challenge remains as to how to archive this data and make our query run efficiently.
If we choose to archive old data outside of database on a tape, cost (more...)
With Active Dataguard setup, many of the read only applications runs on ADG and sometime the SQLs that are used by these applications runs into bad plans. ADG being a read only database, its not possible to create a baseline or a profile on ADG.
One of the way to have good plan for SQLs running on ADG is to make them run on primary first and fix the plan by creating profile or baseline (more...)
This is a small article on using UDev on RHEL 7. I have a virtual box with RHEL 7 and I am configuring ASM diskgroups.
Before we configure ASM diskgroups, we need to have disks/partitions available at OS level and those should be recognized by ASM. Oracle provides a utility called ASMLib which can be installed and used very easily to configure disk/partitions at OS level.
ASMLib stamps the header of partitions/disk at OS level (more...)
I am starting Oracle 12c learning series, where I am planning to publish multiple articles on Oracle 12c new features. I hope these articles will be helpful to DBAs aspiring for OCP 12c certification and also others who are planning to implement these features in there databases.
This is a very long post about ADO – Automatic Data Optimization, which is one of the ILM strategy to manage aging data. So please be patient while reading this article. This article provides end-to-end details (more...)
This is a short article on RMAN where we can check if our backups are really intact and can help us in critical situation when we have to restore and recover the database.
We have a command option called PREVIEW which we can use with RESTORE DATABASE. This option does not actually restore the datafiles from backup but it just tell us SCN number until which we should be recovering our database. It also tells (more...)
I recently encountered a bug related to MView log causing very high library cache: mutex x wait events.
I will brief about the debugging steps I tried and fix for the same.
Few things to note before I proceed:-
- We observed huge wait events for library cache: mutex X whenever we performed flip to standby or when DB was bounced. I am implying that library cache was cold and didn’t had required cursor information and object handles.
- Load on the (more...)
In my previous article on Latches, I mentioned various things related to latches including different types and their behavior.
In this article I will describe similar things about mutexes.
Mutex are low level serialization / locking mechanism to protect memory structure inside library cache.
Why there was a change from Latches ?
Mutex was introduced from 10.2 onwards and have been proved very efficient in managing library cache operations.
Mutex takes less memory then latches. Typically latch (more...)