Few days ago we had concurrency problem with "cursor: pin S wait on X
" wait event. This wait event is mostly associated with parsing in some form.
After quick diagnosis I’ve found problematic query. It was fairly complex query which was executed very often with average 0.20 seconds of execution time. As this query was using bind variables, Oracle reused existing plan and problems with "cursor: pin S wait on X" wait (more...)
We have several databases where automatic capturing of sql plan baselines is enabled for a few schemas.
Execution of some queries deeply depend on variables where is not always the best to reuse same execution plan for all executions. For those queries I want to avoid using literals and inefficient execution plans. Also, I want to use SQL plan baselines as I have automatic capturing enabled.
Question is, can I make Adaptive Cursor Sharing to (more...)
Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.
This was the statement:
from CLIENT k
where k.expires is null;
I've used fake names for table name and columns.
Database version: 184.108.40.206.0
Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)
Last week we have created standby database in our dev environment and performed some ETL actions on primary side. Loading data or rebuilding indexes was performed with NOLOGGING option. After few days we noticed lots ORA-01578/ORA-26040 errors.
Corruption happened because we forgot to enable force logging.
As this was new dev database there wasn’t backup, but maybe not everything was lost. If only corrupted segments are indexes we could easily rebuild them.
Then I’ve learnt (more...)
It is widely known that unindexed foreign keys can be performance issue. Unindexed foreign keys on child tables can cause table locks or performance problems in general.
There are many articles on this subject so I won't go in details.
My plan is to show simple demo case where empty child table with unindexed foreign key column can cause big problems.
Imagine that you have highly active table (supplier) with lots DML operations from many (more...)
directory is filesystem directory created at root level of mounted drive for ext file systems. It is used by file system check tools (fsck) for file recoveries.
In MySql world it can cause confusion or possible problems with synchronisation in Galera cluster configuration. Let’s check some examples.
I have MySQL database with datadir=/data in configuration file. I have deleted lost+found directory and restarted MySQL service.
When I list my databases this is result:
Imagine you have several Oracle databases on the same host under same OS user.
In scripts directory you have shell script that kills OS processes.
Idea is to call OS script from database procedure and kill problematic process using shell script.
Script will run simple query to get process id and kill that process.
But how to assure that this script will execute in correct environment for correct database?
One way is to create one (more...)
Few days ago I saw great post from Norman Dunbar
on how to fix a broken ASM spfile.
With version 11gR2 ASM spfile can be stored in ASM diskgroup and by default Oracle Installer will put it there. So if you want to create pfile from spfile your ASM instance should be up and running.
If you have incorrect parameter in ASM spfile which is blocking ASM to start than you have slight problem. You (more...)
Restore points and Flashback database are nice features introduced in 10g database that provide efficient point in time recovery to reverse unwanted data changes.
But what if you have Standard Edition database:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
alter database flashback on
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database
In Standard Edition you don’t have Flashback Database feature, but you can still create restore points and (more...)
There are many articles explaining how to migrate database from file system into ASM. You could use RMAN to create an image copy of the database into ASM and switch to the database copy, restore database from backup sets into ASM or create duplicate database.
All of these RMAN features are available on Oracle versions before 12c.
In this post I will use slightly different approach - using online relocation of data files into ASM. (more...)
Percona Toolkit is collection of command-line tools to perform many MySQL tasks like creating backups, finding duplicate indexes, managing replication, etc.
In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools: pt-heartbeatpt-table-checksumpt-table-sync
I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.
Install Percona Toolkit
on both nodes:
$ sudo wget percona.com/get/percona-toolkit.deb
The simplest and probably most common replication method is master-slave
replication. Basically, data is replicated from master database to the slave. In case of master database failure you must get the slave database up-to-date before failover and then promote slave to be new master.
Another method is to set up replication in both directions called master-master
replication. But you must be aware that this setup brings some potential issues as data changes are happening on (more...)
Few days ago I experienced strange issue in my development environment running on OEL 5.8 with EXT4 filesystem. Note - EXT4 filesystem is supported from OEL 5.6 version.
This was virtual machine running oldish 10.2.0.5.0 Oracle database.
I noticed that backup for my database is failing because of archive log corruption. As this is development database I've simply deleted corrupted archive logs and initiated full backup again. But backup (more...)
Imagine you have 11gR2 Oracle Restart configuration with database files located in ASM.
After server crash you realized that local disks are corrupted and with local disks you lost all Oracle installations. Even though this is important system you don’t have database backup (always take backups!).
But you managed to save all ASM disks as they were located on separate storage.
This will be small beginner guide on how to help yourself in such (more...)
When I create virtual machines on my notebook I always create too small disk for root partition or partition where I put Oracle binaries. After a while when I want to perform upgrade, or install another Oracle software, there is not enough space. This time I want to note steps about how to increase disk free space.
I can easily extend or shrink my logical volumes because I am using LVM in my virtual machines. (more...)
Oracle Flex ASM was introduced in 12c version. This is one of the best features introduced with new version in my opinion.
I won’t speak in detail about Flex ASM because you can find more information in documentation. In this post I will concentrate on how Flex ASM handles crash of ASM instance.
For this test I’ve created 2 node cluster - 12c Grid Infrastructure with Flex ASM enabled.
$ asmcmd showclustermode
ASM cluster : (more...)
I love to read Oracle related blogs, forum posts and mailing lists much more often than books. Why? Because there many Oracle DBA’s and developers share their experiences, problems, "best practices",... which are very valuable to me.
It's great that we have so big and active Oracle community.
Today I noticed mail from Oracle-L
list where someone asked for help with recovery after overwriting production controlfiles. Check Oracle-L
for more info.
It reminded me that (more...)
I've played a little with Oracle SQL Plan Management and something caught my eye.
Let's create simple test.
SQL> select /* test_spm */ count(*) from admin.objekti;
SQL> set lines 200
SQL> col sql_text for a50 wrapped
SQL> select sql_id, sql_text from v$sql
2 where sql_text like '%test_spm%';
cpzgdw9swdvzk select /* test_spm */ count(*) from admin.objekti
ap9td7vafq26n select sql_id, sql_text from v$sql where sql_text
RAC One Node is Oracle active-passive
solution very similar to RAC which is active-active solution.
Running active-passive solution using Oracle Clusterware was possible even before introduction of RAC One Node, but RAC One node brought configuration and managing of active-passive cluster to another level.
In my test environment I've created active-passive cluster using 12cR1 Grid Infrastructure with 12cR1 RAC One Node database.
I've tested failover for Oracle database and noticed interesting behavior with instance placement. (more...)
I have recently configured Active/Passive Failover cluster using Grid Infrastructure 12cR1 and wrote document on that subject. Now I plan to use this environment and convert "test12" database from Single-Instance to RAC database.
There are several ways to convert non-RAC database to RAC database:
- using Database Configuration Assistant (DBCA)
- Oracle Enterprise Manager
- rconfig command line utility
In this post I will describe how to perform conversion using rconfig utility