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
wget http://s3tools.org/repo/RHEL_5/s3tools.repo
yum install s3cmd
s3cmd –configure
Access key and Secret key are your identifiers for Amazon S3
For more information on s3cmd set, check out http://s3tools.org/s3cmd
#!/bin/sh
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 }'` )
len=${#array[*]}
i=0
while [ $i -lt $len ]; do
echo "$i: ${array[$i]}"
s3cmd del ${array[$i]}
let i++
done
echo 'copying backup files into the S3 bucket'
array=(`ls $file1`)
len=${#array[*]}
i=0
while [ $i -lt $len ]; do
s3cmd put ${array[$i]} s3://myBackupFolder/
let i++
done
Wednesday, February 15, 2012
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.
oracle-data--1
oracle-log--1
Ensure your database server can access s3.amazonaws.com
>nslookup s3.amazonaws.com
Non-authoritative answer:
s3.amazonaws.com canonical name = s3-1.amazonaws.com.
Name: s3-1.amazonaws.com
Address: 72.21.203.145
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 otn.oracle.com
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: os.name = 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 oracle.security.client.connect_string1
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 osbws_linux64.zip.
Debug: Temp zip file = /tmp/osbws_linux644610275856950839391.zip
Downloaded 16350454 bytes in 10 seconds. Transfer rate was 1635045 bytes/second.
Download complete.
Extracted file /home/oracle/script/libosbws11.so
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/libosbws11.so ENV=(OSB_WS_PFILE=/opt/oracle/product/10.2.0/db_1
/dbs/osbwsTST10G.ora)';
rman target / @configure.rcv
Output
RMAN> configure CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/libosbws11.so 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/libosbws11.so 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 DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/libosbws11.so 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;
I created 2 buckets 1 for the log and 1 for data.
oracle-data-
oracle-log-
Ensure your database server can access s3.amazonaws.com
>nslookup s3.amazonaws.com
Non-authoritative answer:
s3.amazonaws.com canonical name = s3-1.amazonaws.com.
Name: s3-1.amazonaws.com
Address: 72.21.203.145
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 otn.oracle.com
Create argFile , I called it awsrman
-AWSID
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: os.name = 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-
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 oracle.security.client.connect_string1
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 osbws_linux64.zip.
Debug: Temp zip file = /tmp/osbws_linux644610275856950839391.zip
Downloaded 16350454 bytes in 10 seconds. Transfer rate was 1635045 bytes/second.
Download complete.
Extracted file /home/oracle/script/libosbws11.so
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/libosbws11.so ENV=(OSB_WS_PFILE=/opt/oracle/product/10.2.0/db_1
/dbs/osbwsTST10G.ora)';
rman target / @configure.rcv
Output
RMAN> configure CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/libosbws11.so 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/libosbws11.so 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 DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/home/oracle/script/libosbws11.so 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]
Linux=redhat-3,SuSE-9,redhat-4,redhat-5,asianux-1,asianux-2
2. Is to do a silent install where you edit the response file.
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]
Linux=redhat-3,SuSE-9,redhat-4,redhat-5,asianux-1,asianux-2
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.
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.
#!/usr/local/bin/perl
system ("/home/oracle/script/chkdbup.sh");
$infile="/tmp/dbup.tmp";
open(IN,"$infile") || die "cannot open $infile for reading: $!";
$alert=0; # 0 = down, 1 = up
while ( ) {
if (/PRODB/) {
print "1\n";
$alert=1;
}
}
if ( $alert == 0 ) {
system ("/home/oracle/script/alert.sh");
}
close (IN);
Shell Script
#!/bin/bash
export ORACLE_SID=EMGC
export db=PRODDB
export ORAENV_ASK=NO
. 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
exit
EOF
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.
#!/usr/local/bin/perl
system ("/home/oracle/script/chkdbup.sh");
$infile="/tmp/dbup.tmp";
open(IN,"$infile") || die "cannot open $infile for reading: $!";
$alert=0; # 0 = down, 1 = up
while (
if (/PRODB/) {
print "1\n";
$alert=1;
}
}
if ( $alert == 0 ) {
system ("/home/oracle/script/alert.sh");
}
close (IN);
#!/bin/bash
export ORACLE_SID=EMGC
export db=PRODDB
export ORAENV_ASK=NO
. 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
exit
EOF
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
search mycompany.com
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.
Here is an example of my resolve.conf
search mycompany.com
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:
/u01/grid/crs/install/crsconfig_params
Here is an example of the useful information in this config file.
CLUSTER_NAME=abcgrid
HOST_NAME_LIST=mynode1,mynode2
NODE_NAME_LIST=mynode1,mynode2
PRIVATE_NAME_LIST=
VOTING_DISKS=NO_VAL
#VF_DISCOVERY_STRING=%s_vfdiscoverystring%
ASM_UPGRADE=false
ASM_SPFILE=
ASM_DISK_GROUP=DATADG
ASM_DISCOVERY_STRING=
ASM_DISKS=ORCL:ORA01,ORCL:ORA02,ORCL:ORA03
ASM_REDUNDANCY=EXTERNAL
CRS_STORAGE_OPTION=1
CSS_LEASEDURATION=400
CRS_NODEVIPS='mynode1-vip/255.255.0.0/bond0,mynode2-vip/255.255.0.0/eth0'
NODELIST=mynode1,mynode2
NETWORKS="eth1"/10.80.0.0:cluster_interconnect,"bond0"/168.11.111.0:public
SCAN_NAME=mygrid-scan
SCAN_PORT=1521
GPNP_PA=
OCFS_CONFIG=
# GNS consts
GNS_CONF=false
GNS_ADDR_LIST=168.87.111.111
GNS_DOMAIN_LIST=myhost.com
/u01/grid/crs/install/crsconfig_params
Here is an example of the useful information in this config file.
CLUSTER_NAME=abcgrid
HOST_NAME_LIST=mynode1,mynode2
NODE_NAME_LIST=mynode1,mynode2
PRIVATE_NAME_LIST=
VOTING_DISKS=NO_VAL
#VF_DISCOVERY_STRING=%s_vfdiscoverystring%
ASM_UPGRADE=false
ASM_SPFILE=
ASM_DISK_GROUP=DATADG
ASM_DISCOVERY_STRING=
ASM_DISKS=ORCL:ORA01,ORCL:ORA02,ORCL:ORA03
ASM_REDUNDANCY=EXTERNAL
CRS_STORAGE_OPTION=1
CSS_LEASEDURATION=400
CRS_NODEVIPS='mynode1-vip/255.255.0.0/bond0,mynode2-vip/255.255.0.0/eth0'
NODELIST=mynode1,mynode2
NETWORKS="eth1"/10.80.0.0:cluster_interconnect,"bond0"/168.11.111.0:public
SCAN_NAME=mygrid-scan
SCAN_PORT=1521
GPNP_PA=
OCFS_CONFIG=
# GNS consts
GNS_CONF=false
GNS_ADDR_LIST=168.87.111.111
GNS_DOMAIN_LIST=myhost.com
Subscribe to:
Posts (Atom)