Friday, February 13, 2015

Oracle Total Recall / Flashback Data Archive

Sometimes our clients does wrong DML operation and wants us to recover some critical tables after 3 days of the operation or so. Since Oracle 11gR2 version we have Oracle "Total Recall" feature which gives the ability to query deleted/modified records by giving a point a time. So that our clients can instantly query and correct the problematic records without need of a DBA.

Technical details are as follows:

Thursday, September 11, 2014

New Recover Table Option in 12c

If someone deletes wrong records, or some other wrong update,truncate etc. is done on the table, and if it is too late to query the undo tablespace with "as of timestamp" clause, or  if total recall is not enabled or there is no standby database with flashback enabled, DBA has no other chance than recovering tablespace from a backup.
DBAs are too lazy do this recovery unless he has an recovery automation. This is because creating an auxiliary instance on the same server is risky, so it is understandable that he doesn't want to take this risk. Also preparing and finding other server is a hard operation. Tablespace point in time recovery operation itself is a complicated operation.

Luckily with 12c, Oracle gives a simple solution : "recover table" command in rman.

Thursday, July 3, 2014

Oracle In Memory Execution Plans&Performance

I will write about in memory execution performance, comparison with traditional execution plans and new execution plan steps in this post. This is the second of my in memory article series. I mentioned about basics of in memory option in the previous post.

In all the examples I will execute non inmemory executions several times until there is 0 physical reads to eliminate I/O related confusions. So that we will see the power of new column format over row format in join operations.

Tuesday, July 1, 2014

Oracle In Memory Option - When and How to Use, Sample Statements&Scripts

Finally Oracle Database 12c is released and writing about the In Memory Option is allowed.
I will give some basic "How to do's" and understanding on In Memory Option in this post. You can find my tests about execution plans and performance related observations in the next post.

Wednesday, June 25, 2014

Create a Baseline on Original SQL Using Modified SQL

Optimizer does not pick the correct plan. You can find a better plan by adding some hints to the SQL.
So how can you force the original SQL to use your modified SQL'S better plan?
Maria Colgan explains how to  create a baseline out of a modified sql in this blog post:

And Carlos Sierra made the easy to use script
Puts it in SQLT utl directory.

The script is:

Tuesday, June 24, 2014

Checkdb Script for Checking Common Problem Sources on a Database

Sometimes performance problems may be caused by non-obvious thing such an unusable index, parameter change or session limits on the user profiles. I wrote the checkdb script to easily control following things with one script:
  • Session Counts
  • Sessions and Processes Parameters
  • Whether there is an unusual wait event that is whether there is more than 5 sessions waiting on an event more than 20 seconds
  • Top SQL's (That is the sqlplus version of EM's top activity SQL area)
  • Lock Waits
  • CPU, Shared Pool and Temp space utilizations
  • Compares the following metrics with last week:'I/O Megabytes per Second','Redo Generated Per Sec','I/O Requests per Second','Host CPU Utilization (%)','Average Active Sessions','Temp Space Used','Total PGA Allocated','User Transaction Per Sec','Total Table Scans Per Sec','Full Index Scans Per Sec','Shared Pool Free %','Session Count','Logons Per Sec'
  • Tablespaces having 0% free
  • Diskgroups having 0% free
  • Session Count is above 90% of the profile limit
  • Unusable Indexes
  • Instance Parameter Changes since last week
  • SQL Plan Changes since last week

Monday, June 23, 2014

Add Datafile Script Generation SQL

If you use normal datafiles, if you don't archive the data and if the data grows continuously you need to add datafiles on a regular basis.
The following script is for those who has hundreds of tablespaces on ASM, it controls all the tablespaces, the ASM diskgroups' empty size in which datafiles of a tablespace resides, and generates datafile addition script on the emptiest diskgroup used  by the tablespace.

Sunday, June 22, 2014

How to Deal With SQL Plan Changes-Reactive Approach

It is one of the main problems for OLTP DBA's; suddenly active session count rises above hundreds, and there is one sql causes this rise at the top. In this post I will share a script to identify sql plan changes and a script to fix sql plan with creating a sql profile that uses the better working sql plan.

How to understand whether there is a plan change on the sql?
By querying DBA_HIST_SQLSTAT you can see whether one sql had different plan previously.
Following script compares now with last week (I hope it was running fine last week on the same hours), and it gives the sqls if there is a sql plan change and elapsed time is increased.

Thursday, June 5, 2014

How to Find Top SQL's Based on IO

If you experience increase in the IO related wait events like log file sync/db file sequential read etc,
one probability is you have a performance problem on storage infrastructure, in this option you need to observe decrease in IOPS and Disk throughput.
Other probability is someone do something that does extraordinary disk read/write, if this is the case you need to observe increase in IOPS and disk  throughput. And you need to find the SQL and the session calling that SQL.

Thursday, May 22, 2014

Resizing/Shrinking all datafiles in a tablespace to the high water mark level

If you/your friend give a lot of space to datafiles in a tablespace, after some time you may need to get unused space from that tablespace. Before trying to move segments in a tablespace you can consider to use this script, if you are lucky (if the segments that are growing and resides at the end blocks are dropped/moved or datafile size has been increased unnecessarily large) you can have some space to gain. For reducing the high watermark of a tablespace, you need to move segments on the blocks that are on the high water mark. You can find the script for moving all segments in a tablespace at

Moving all the segments in a tablespace to another tablespace to empty the tablespace

The sqlplus script which generates the move table/partition and lob and rebuild index scripts for a given tablespace to a new tablespace. You may use this script to gain free space in a tablespace and lower the high watermark of a tablespace.

Tuesday, May 20, 2014

Moving 10.2 Database on AIX to Database on Linux using Transportable Tablespace and dbms_file_transfer

Moving/Migrating a database to a different platform and different version is one of the most complicated tasks for database administrators. Since there are several changes including OS change, platform change, database version change and the infrastructure change, the risks are multiplied. Therefore a good testing and a migration scenario is needed. General experience is making one change at a time, but if you don't have so much time for downtime you may consider a change like this.
You have several option for moving database to a different OS/platform and version which are; goldengate (very little downtime), exp/imp (for small databases or if you can get a lot downtime), logical standby (there are some restrictions about this) and transportable tablespace. (If you have any other ideas please comment)
In this article I will give all the details/checklists for transportable tablespace option. By using this article you will have all the necessary details, scripts about migrating a database using transportable tablespace.