Checking RMAN backup using Shell Script

Hi ,

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

Usage :

[oracle@ora1 ~]$ chmod 755 backup_check.sh


[oracle@ora1 ~]$ backup_check.sh 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 backup_check.sh
#!/bin/bash


#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`


#Credentials
USER=system
PASSW=oracle


#Query FAILED Backup Operations
$ORACLE_HOME/bin/sqlplus $USER/$PASSW <<EOF
set feed off
set linesize 400
set pagesize 200
spool backup.alert
SELECT   INPUT_TYPE,STATUS
FROM V\$RMAN_BACKUP_JOB_DETAILS where STATUS ='FAILED';
spool off
exit
EOF


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

if [ $ISFAIL -gt 0 ]
then
#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
fi
exit 0

Advertisements

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 🙂

Enabling Write back cache using hpacucli

Hi,

Write back cache enabled systems are mostly more optimized because writing into the cache is faster than writing into the disk. Lets look how can we enable this feature for the controller using hpacucli.

To Display :
hpacucli ctrl all show config detail

This command’s output is starting with :

Smart Array P400 in Slot 1
Bus Interface: PCI
Slot: 1  <<< This is the Slot# (We will use it to enable Write back cache)
Serial Number: PXXXXXXXXX
Cache Serial Number: PXXXXXXX
RAID 6 (ADG) Status: Enabled
Controller Status: OK
Chassis Slot:
...
Cache Board Present: True
Cache Status: OK
Accelerator Ratio: 25% Read / 75% Write
Drive Write Cache: Disabled <<< This Means Write back cache is disabled
Total Cache Size: 512 MB <<< This is the size of cache
No-Battery Write Cache: Disabled <<< This means when Battery is Not OK, WRB will be automatically get disabled.
Cache Backup Power Source: Batteries
Battery/Capacitor Count: 1 <<< One Battery is present
Battery/Capacitor Status: OK <<< Battery is OK
SATA NCQ Supported: True
....

To enable WBC please run this command :

ctrl slot=1 modify dwc=enable

When you run this command a warning message is shown about possible loss of data in the event of power failure.
Warning: Without the proper safety precautions, use of write cache on physical
drives could cause data loss in the event of power failure.  To ensure
data is properly protected, use redundant power supplies and
Uninterruptible Power Supplies. Also, if you have multiple storage
enclosures, all data should be mirrored across them. Use of this
feature is not recommended unless these precautions are followed.
Continue? (y/n) y

If no problem with this message , you can pree y and go on.

That’s all.

Good luck 🙂  

“crsctl stop/start crs” vs “crsctl start/stop cluster”

Hi,

crs and cluster start/stop operations are sometimes confused.

For that reason, I want to identify the difference between crs and cluster operations.

Lets clearify this from “crsctl stop” command help :

-bash-3.2$ crsctl stop
.... (Some usage hints...)
(Here is important ..)
crsctl stop crs [-f]
Stop OHAS on this server.(This means you can ONLY stop local host's CRS.)
where
-f  Force option  (You can use Force optin using "-f")


crsctl stop cluster [[-all]|[-n <server>[...]]] [-f]
Stop CRS stack
where
Default         Stop local server
-all            Stop all servers
-n              Stop named servers
server [...]    One or more blank-separated server names
-f              Force option

As you see above, you can stop CRS stack using cluster command both local node and remote node while OHASD is running. OHASD must be running for managing CRS stack.(That is important)

 

enq: TM Contention Wait Events

Hi all, Missing index on the Foreign Key column of the Child table is the first cause of TM Contention Wait Events. Other one is may be Parallel DML.

We first look at the first “missing index” case. Lets create environment for the test case. We need one parent table called “Cities” and a child one called “Districts”.

CREATE TABLE CITIES
(
CITY_ID     NUMBER,
CITY_NAME   VARCHAR2 (20),
CONSTRAINT CITIES_PK PRIMARY KEY (CITY_ID)
);

CREATE TABLE DISTRICTS
(
DISTRICT_ID     NUMBER,
CITY_ID         NUMBER,
DISTRICT_NAME   VARCHAR2 (30),
CONSTRAINT DISTRICTS_PK PRIMARY KEY (DISTRICT_ID),
CONSTRAINT CITY_FK FOREIGN KEY (CITY_ID) REFERENCES CITIES (CITY_ID)
);

Lets INSERT SOME rows.

INSERT INTO CITIES
VALUES (1, 'Istanbul');

INSERT INTO CITIES
VALUES (2, 'Hatay');

INSERT INTO CITIES
VALUES (3, 'Ankara');

COMMIT;

INSERT INTO DISTRICTS
VALUES (1, 1, 'Kadikoy');

INSERT INTO DISTRICTS
VALUES (2, 1, 'Basiktas');

INSERT INTO DISTRICTS
VALUES (3, 2, 'Antakya');

INSERT INTO DISTRICTS
VALUES (4, 2, 'Defne');

COMMIT;

Now we can run some DMLs for reproduce this wait event. Session 1 inserts a new district into the DISTRICTS table running this dml :

SQL> insert into districts values (6,1,'Sisli');

1 row created.

Session 2 updates Primary Key column of the CITIES table running this dml :

SQL> update cities set city_id=4,city_name='Adana' where city_id=3;

Lets have a look to the blockers :

SQL> set lines 500

SQL> r

1  SELECT sid,

2        sql_id,

3        blocking_session,

4        event,

5        p2

6    FROM v$session

7   WHERE blocking_session IS NOT NULL

8  UNION ALL

9  SELECT sid,

10        sql_id,

11        blocking_session,

12        event,

13        p2

14    FROM v$session

15*  WHERE sid IN (SELECT blocking_session FROM v$session)

SID SQL_ID         BLOCKING_SESSION  EVENT                 P2

--- -------------- ----------------- -------------------- ---------

147 6tadxuk2a86fn  26                 enq: TM - contention 

58382 26                              SQL*Net msg frm clie  1

As you see these two tables are not huge tables. I mean it is size independent. Problem is uncommitted transaction and missing index as well. Lets create an index for solving problem on DISTRICTS table column (CITY_ID)

CREATE INDEX city_id_ndx ON DISTRICTS (CITY_ID);

Conclusion : Bad design (Missing index on the FK column) and uncommitted transactions are the main cause of this wait.

For more reading :

Doc ID 1905174.1

http://www.confio.com/logicalread/solving-oracle-enq-tm-contention-wait-events/#.U8ZIyqjjcsk

http://www.dbspecialists.com/blog/database-maintenance/unindexed-foreign-keys-enqtm-contention/