Wednesday, February 15, 2012

Script Backups to Amazon s3

Recently I had to create a shell script to place rman backups on Amazon S3 storage. Here is how I did it.

Dependencies: On the host unix box you need to install the s3 toolkit and the pem file.

To install the s3cmd toolkit on linux.
As root cd /etc/yum.repos.d
yum install s3cmd
s3cmd –configure
Access key and Secret key are your identifiers for Amazon S3

For more information on s3cmd set, check out

export DATE_STRING=`date +%Y%m%d`
export DATE_STRING_OLD=`date -d last-month +%Y%m%d`
export file1="/myfiledir/*_$DATE_STRING"

echo 'deleting old backup files in the S3 bucket'
array=(`s3cmd ls s3://myBackupFolder/ | awk ' ($4 ~ /'$DATE_STRING_OLD'/){ print $4 }'` )
while [ $i -lt $len ]; do
echo "$i: ${array[$i]}"
s3cmd del ${array[$i]}
let i++

echo 'copying backup files into the S3 bucket'
array=(`ls $file1`)
while [ $i -lt $len ]; do
s3cmd put ${array[$i]} s3://myBackupFolder/
let i++

Friday, October 28, 2011

Oracle Database Cloud Backup to Amazon S3 using osbws

After registering with S3 check to ensure your S3 bucks are available.
I created 2 buckets 1 for the log and 1 for data.

Ensure your database server can access

Non-authoritative answer: canonical name =

Ensure you have java installed on your database server
java -version
java version "1.6.0_27"
Java(TM) SE Runtime Environment (build 1.6.0_27-b07)
Java HotSpot(TM) 64-Bit Server VM (build 20.2-b06, mixed mode)

Download osbws_install.jar from

Create argFile , I called it awsrman
-AWSID -AWSKEY -otnUser -otnPass < yourOTNpassword> -walletDir /home/oracle/script/wallet -libDir /home/oracle/script -debug

java -jar osbws_install.jar -argFile ./awsrman

Here is the output from the jar install
Oracle Secure Backup Database Web-Service Install Tool, build 2011-02-04.0001
Debug: = Linux
Debug: os.arch = amd64
Debug: os.version = 2.6.18-194.el5
Debug: file.separator = /
Debug: S3 ID:
Debug: S3 Key:
Debug: AWS Success, owner=sthenmoz, id=
AWS credentials are valid.
Debug: Platform = PLATFORM_LINUX64
S3 user already registered.
Registration ID:
S3 Logging Bucket: oracle-log--1
Validating log bucket location ...
Debug: Get location HTTP response: 200 - OK
Debug: log bucket location = []
Validating license file ...
Debug: Get license file HTTP response: 200 - OK
Create credential
OSB web-services wallet created in directory /home/oracle/script/wallet.
OSB web-services initialization file /opt/oracle/product/10.2.0/db_1/dbs/osbwsTST10G.ora created.
Downloading OSB Web Services Software Library from file
Debug: Temp zip file = /tmp/
Downloaded 16350454 bytes in 10 seconds. Transfer rate was 1635045 bytes/second.
Download complete.
Extracted file /home/oracle/script/
Debug: Delete RC = true

Created a script to configure rman and placed in configure.rcv
configure CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/ ENV=(OSB_WS_PFILE=/opt/oracle/product/10.2.0/db_1

rman target / @configure.rcv

RMAN> configure CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/ ENV=(OSB_WS_PFILE=/opt/oracle/product/10.2.0/db_1/dbs/osbwsTST10G.ora)';
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/ ENV=(OSB_WS_PFILE=/opt/oracle/product/10.2.0/db_1/dbs/osbwsTST10G.ora)';
new RMAN configuration parameters are successfully stored

show all;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/ ENV=(OSB_WS_PFILE=/opt/oracle/product/10.2.0/db_1ee/dbs/osbwsTST10G.ora)';

RMAN> backup as compressed backupset database include current controlfile plus archivelog;

Thursday, October 27, 2011

Oracle 10g runInstaller OS check to install Oracle on redhat-5

If you need to install Oracle Database 10g on newer version of redhat and the runInstaller is complaining about OS pre-requisite checks there are 2 ways to work around this.

1. One easy work around is to edit the oraparam.ini under database/install directory to add the OS. In my case I added redhat-5

[Certified Versions]

2. Is to do a silent install where you edit the response file.

Oracle DBA Interview Questions focusing on Performance Tuning

Situational ( Performance Tuning )
Q. Customer reports a application slowness issue, and you need to evaluate database performance. What do you look at for 9i and for 11g.

Possible answer: On oracle 9i, look at the statspack report , on oracle 11g look at the AWR report. In both reports look top sqls listed in elapsed time or cpu time. At sqlplus look sql_text from v$sql where disk_reads is high.

Q. You have found a long running sql in your evaluation of system health of database, what do you look for to determine why sql is slow?

Possible answer: Use explain plan to determine the execution plan of the sql. When looking at execution plan look for indexes being used, full table scans on large tables.

Q. You have a windows service is crashing, how can you determine the sqls related to the windows service?

Possible answer: Use sql trace to trace the username and program associated with the trace file. Use tkprof to analyze the sql trace and determine the long running sqls.

Wednesday, October 26, 2011

Database Check using Perl and Shell script

I ran into a host that did not support Oracle Grid Control Agent 12c since it is running Red Hat 3 OS so I needed to resort to old shell script for alert.

I use a Perl script so that I can eventually put this into a Oracle User Defined Metric ( UDM ) for Grid Control.

Here is the perl script and the shell script.
The PERL script calls the Shell Script.

system ("/home/oracle/script/");
open(IN,"$infile") || die "cannot open $infile for reading: $!";
$alert=0; # 0 = down, 1 = up
while ( ) {
if (/PRODB/) {
print "1\n";
if ( $alert == 0 ) {
system ("/home/oracle/script/");
close (IN);

Shell Script

export db=PRODDB
. oraenv
$ORACLE_HOME/bin/sqlplus /nolog

<< EOF
set pagesize 0
set echo off
set feedback off
conn dbsnmp/password@$db
spool /tmp/dbup.tmp
select global_name from global_name;
spool off

Friday, October 21, 2011

configuring oracle 11gR2 scan and troubleshooting nslookup hang

Today I was implement the scan IP for Oracle Grid 11gR2 and my nslookup tesst were hanging. Turns out I just need to add entries into my /etc/resolv.conf for lookups.

Here is an example of my resolve.conf
nameserver 4.x.x.x

There is a good note explaining setup of the SCAN IP for 11gR2. The note is 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained [ID 887522.1].

Below is a summary of the note.
Single client access name (SCAN) is the virtual name to provide for all clients connecting to the cluster instead of using server-vip addresses.

SCAN is a domain name registered to at least one and up to three IP addresses, either in the domain name service (DNS) or the Grid Naming Service (GNS).

SCAN name must be unique to your network and at least one character long and no more than 15 characters in length, must be alphanumeric - cannot begin with a numeral and may contain hyphens (-). If you require a SCAN that is longer than 15 characters, then select an Advanced installation.

SCAN VIP addresses must be on the same subnet as virtual IP addresses and public IP addresses.

Don't use /etc/hosts to resolve SCAN, use DNS or GNS.

Wednesday, October 19, 2011

Configuring 11gr2 Grid for RAC

One of the useful config files post install to review is:
Here is an example of the useful information in this config file.


# GNS consts