Oracle RMAN Monitoring Script


You can monitor your RMAN session using v$session_longops view.

Here is a script :

(SOFAR / TOTALWORK) * 100 done,


MySQL Error 1236


When mysqld crashes before flushes the log , you may get this error and replication can not resume.

This can be shown like when “show slave status” performed:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’

For solution :

Suppose that you have a Master-Master Replication on HostA and HostB.

On HostA:

1- stop slave;

2-reset master;

3-reset slave;

On HostB:

1- stop slave;

2-reset master;

3-reset slave;

After all you can set up replication between them using this method

Good Luck.

Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl)

Richard Foote's Oracle Blog

Sometimes, a few pictures (or in this case index block dumps) is better than a whole bunch of words 🙂

In my previous post, I introduced the new Advanced Index Compression feature, whereby Oracle automatically determines how to best compress an index. I showed a simple example of an indexed column that had sections of index entries that were basically unique (and so don’t benefit from compression) and other sections with index entries that had many duplicates (that do compress well). Advanced Index Compression enables Oracle to automatically just compress those index leaf blocks where compression is beneficial.

If we look at a couple of partial block dumps from this index, first a dump from a leaf block that did have duplicate index entries:

Leaf block dump
header address 216542820=0xce82e64
KDXCOLEV Flags = – – –
kdxcoopc 0xa0: opcode=0: iot flags=-C- is converted=Y
kdxconco 2
kdxconro 651
kdxcofbo 1346=0x542
kdxcofeo 2172=0x87c
kdxcoavs 826

View original post 857 more words

12c Fixed Subquery

Oracle Scratchpad

It’s been about 8 months since I posted a little note about a “notable change in behaviour” of the optimizer when dealing with subqueries in the where clause that could be used to return a constant, e.g.:

There’s been a note at the start of the script ever since saying: Check if this is also true for any table with ‘select fixed_value from table where primary = constant’ I finally had a few minutes this morning (San Francisco time) to check – and it does, in both and With the t1 table from the previous article run the following:

Instead of the historic 5% of 5% selectivity, the plan shows the optimizer predicting (approximately) the 80,000 rows that it will actually get:

I can’t think it’s very likely that anyone has written SQL that looks like this – but I’m often surprised by what I see in the…

View original post 28 more words

Checking RMAN backup using Shell Script

Hi ,

Here is a shell script for checking your RMAN backup operation status.

Usage :

[oracle@ora1 ~]$ chmod 755

[oracle@ora1 ~]$ ORA1 (This is your ORACLE_SID)

Oracle Database Backup and Recovery User’s Guide ‘s  “To query details about past and current RMAN jobs” section is here.

Good Luck 🙂

[oracle@ora1 ~]$ cat

#If you will schedule this script oracle's crontab then you do not need export both SID and HOME variables
export ORACLE_SID=$1
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

#Formatting date
MYDATE=`date +%Y-%m-%d:%H:%M:%S`


#Query FAILED Backup Operations
set feed off
set linesize 400
set pagesize 200
spool backup.alert
spool off

#Check if there is any FAILURE
ISFAIL=`cat backup.alert|grep FAILED |grep -v RMAN_BACKUP_JOB_DETAILS|wc -l`

if [ $ISFAIL -gt 0 ]
#Fetch Backup Type if any failure occured. For DB Full Backup BTYPE = DB , For ARCHIVELOG BTYPE= ARCHIVELOG etc.
BTYPE=`cat backup.alert|grep FAILED |grep -v RMAN_BACKUP_JOB_DETAILS|awk '{print $1}'`
echo $BTYPE " Backup ERROR"  > backup_check_$MYDATE.log
exit 0

SCSI LUN Disk permission operation using udev rules on RedHat 5

If you are working with RedHat 5.x and SCSI LUNs before adding your disks into your ASM diskgroups , creating RAWs can be an easy way to manage permissions. Let’s do an example :

For example your storage admin gave you 2 LUNs. One is /dev/sdb1 and the other one is /dev/sdc1.

1- ) Assigning these LUNs to RAWs

[root@ora1 ~]# raw /dev/raw/raw1 /dev/sdb1

[root@ora1 ~]# raw /dev/raw/raw2 /dev/sdc1

[root@ora2 ~]# raw /dev/raw/raw1 /dev/sdb1

[root@ora2 ~]# raw /dev/raw/raw2 /dev/sdc1

2- ) Please add these two rows in /etc/sysconfig/rawdevices
/dev/raw/raw1 /dev/sdb1
/dev/raw/raw2 /dev/sdc1

3- ) Now we are creating a new file like 88-raw.rules under /etc/udev/rules.d/ and writing down this entry:

KERNEL==”raw[1-2]*”, OWNER=”oracle”,GROUP=”dba”, MODE=”640″

If you have 3 RAWs , it will be KERNEL==”raw[1-3]*”, … as well.

That’s all. Now you can discover your disks to add them into your ASM.

Good Luck 🙂

Disk permission operation using udev rules on RedHat 6

Changing disk permission can be perform using udev. Let’s see an example :

1- ) Fetch the unique disk id with scsi_id.

[root@ora1 ~]# scsi_id -g -u -d /dev/sdb

This command returns a unique id like “20a0c2b147c3ae84b74a42b058e8a7c3ae84b74a42b0a393"

2- ) A new udev file will be used for giving permission (But really new)

[root@ora1 ~]# cd /etc/udev/rules.d/

[root@ora1 ~]# more 88-oracle-disks.rules

KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="20a0c2b147c3ae84b74a42b058e8a7c3ae84b74a42b0a393", NAME+="raw/raw1", OWNER="oracle", GROUP="dba", MODE="0660"

[root@ora1 ~]#

Disk will be created under /dev, i.e., raw/raw1 means disk’s location is /dev/raw/raw1.

As you see this rule has serial id of the disk (20a0c…393) ,disk name (raw/raw1), mode (0660) and group-owner  information.

After creating this rule , please reboot host(s) and kindly check if you can discover your disk under “/dev/raw/*”

You can find a very good article about this topic in Frits Hoogland’s blog. Here is the link.

Good Luck 🙂