Oracle DBA

Oracle Database Cloud Backup to Amazon S3 using osbws

2011-10-28T14:47:00.000-07:00

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;

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

2011-10-27T15:06:00.000-07:00

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.

Oracle DBA Interview Questions focusing on Performance Tuning

2011-10-27T08:32:00.000-07:00

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.

Database Check using Perl and Shell script

2011-10-26T12:32:00.000-07:00

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

configuring oracle 11gR2 scan and troubleshooting nslookup hang

2011-10-21T14:19:00.000-07:00

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.

Configuring 11gr2 Grid for RAC

2011-10-19T14:00:00.000-07:00

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

oracle rac ntp pre-install check

2011-10-19T10:29:00.000-07:00

Oracle RAC has a pre-install check for ntpd running with -x option

Starting Linux NTP service:
/sbin/service ntpd start | stop | restart
Reference support.oracle.com Notid 551704.1
cat /etc/sysconfig/ntpd

# Drop root to id 'ntp:ntp' by default.
#OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"
# Set to 'yes' to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no
# Additional options for ntpdate
NTPDATE_OPTIONS=""

Migrating from EM Grid Control 11 to 12c Linux 64 bit

2011-10-13T17:10:00.000-07:00

Oracle released Enterprise Manager ( EM ) 12c. Here is the procedure I used to migrate from EM 11g on Red Hat Linux 5 x64 to EM 12c.

The oracle online doc for EM 12c is available here: http://download.oracle.com/docs/cd/E24628_01/index.htm

There are a few different upgrade options discussed in this section of the documentation. None of the options met my needs so I followed the path of fresh install on new server and migrated the targets over from 11g to 12c over a week.

My reasons for approaching the upgrade in this fashion were:
- I had a spare server available that was not being used
- I wanted to get a feel for the 12c changes from 11 using my test/dev targets
- I wanted to move production systems over 1 RAC cluster at a time so I could get the correct monitoring in place.

If you are going to attempt upgrade, Oracle has documented a few methods found in link below.
Below is the matrix of the upgrade paths.
http://download.oracle.com/docs/cd/E24628_01/upgrade.121/e22625/overview_upg_approaches.htm#CJAHJGJI

Preparation: Check for required packages for Oracle Management Service ( OMS ).
make-3.81
binutils-2.17.50.0.6
gcc -4.1.1
libaio-0.3.106
glibc-common-2.3.4
libstdc++ -4.1.1
setarch-1.6
sysstat-5.0.5
rng-utils-2.0
glibc-devel-2.5-49 ( 32bit and 64 bit )

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep make
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep binutils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep gcc
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libstdc++
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc-common
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep sysstat
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep setarch
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep rng-utils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc-devel

Preparation: Check for required packages for Oracle Management Agent ( OMA ).
make-3.81
binutils-2.17.50.0.6
gcc -4.1.1
libaio-0.3.106
glibc-common-2.3.4
libstdc++ -4.1.1
setarch-1.6
sysstat-5.0.5
rng-utils-2

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep make
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep binutils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep gcc
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libstdc++
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep glibc-common
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep sysstat
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep setarch
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep rng-utils
rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n" | grep libaio-0.3.106

Set kernel.shmmax parameter to a value less than 4 GB
cat /proc/sys/kernel/shmmax
Example: To set shmmax to 3.9 gb
sysctl -w kernel.shmmax=33500744908
cat /etc/sysctl.conf | grep kernel.shmmax

Demo of a simple install from oraclelearning.
http://www.youtube.com/watch?v=i7Zci4eNxW4&NR=1

I performed an advanced install and found the installation process very smooth. I placed the oms and database on same server.

My start, stop, and status scripts for stopping oms just need the OMS path and agent path updated.

When deploying the agents, I used the manual method. Since I had 11g agents on my targets, I removed all the targets in 11g EM, stopped the agent, removed the host target from 11g EM and then un-installed the 11g agent using the below silent install method.

This is documented in: How to Remove the 11.1.0.1.0 Grid Control Management Agent with the Silent Method ID 1068036.1
cd /opt/oracle/agent11g/oui/bin
./runInstaller -silent -deinstall "REMOVE_HOMES={/opt/oracle/agent11g}" -removeallfiles

Before installing the 12c agent using the manual method, I need to make 2 changes to the sudoers file, hashing out requiretty and setting visiblepw

/etc/sudoers
#Defaults requiretty
Defaults visiblepw

After those changes I re-added my database most of which are 10gR2 RAC where I just need to support the cluster name and the CRS HOME path.

So far I am liking the interface changes in EM 12c and have not run into any bugs.

orale standard edition woes

2011-10-12T10:21:00.000-07:00

Supporting a large database over 300gb with performance challenges on 10g standard edition oracle is a rough endeavor. Here is a list of my favorite missing oracle features from enterprise edition.

Parallel ( Backups using multiple channels, building indexes, running long running queries )

RMAN Block Change Tracking File for fast incremental backups.

Table Partitioning, I have a work around but it involves doing poor man partitioning like we did in Oracle 7.

Bitmap Indexes, this is truly a missed feature that is hard to work around.

Dataguard, I have a work around but it involes manually copying archive logs to standby and updating standby controlfile everytime I add a datafile on primary.

Here is oracle's official list for 11g.

http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/editions.htm#CJACGHEB

Preparation for Oracle 11gR2 on Enterprise Linux 64 bit install

2011-10-05T18:24:00.000-07:00

Here is the procedure I used for my last 11gr2 install.

Requirements for Installing Oracle 11g r2 RDBMS on Oracle Unbreakable Linux

Document sources for procedure:
Requirements for Installing Oracle 11gR2 on RHEL 5 on IBM: Linux [ID 1306889.1]
How to Configure the DNS Server for SCAN VIP on Linux for 11gR2 [ID 1107295.1]
Defining a "default RPMs" installation of the RHEL OS [ID 376183.1]
Upon startup of Linux database get ORA-27102: out of memory Linux [ID 301830.1]
RAC Assurance Support Team: RAC Starter Kit and Best Practices (Linux) [ID 811306.1]

You can obtain more information about the Oracle Unbreakable Enterprise Kernel for
Linux at the following URL:
http://www.oracle.com/us/technologies/linux/index.html
The Oracle Unbreakable Enterprise Linux kernel installs directly on top of Oracle
Linux 5, or Red Hat Enterprise Linux 5, starting with Update 5, so you are not
required to upgrade to a new major release of the operating system to obtain the
benefits and features of this new kernel. You can obtain additional information and
download the Oracle Unbreakable Enterprise Kernel for Linux at the following URL:
http://public-yum.oracle.com/
Oracle Linux 5
http://oss.oracle.com/el5/oracle-validated/
If the Linux distribution is Oracle Linux, or Red Hat Enterprise Linux, and you are an Unbreakable Linux customer, then you can complete most preinstallation
configuration tasks by using the Oracle Validated Configurations Setup RPM,
available from the Unbreakable Linux Network (ULN), or available on the Oracle
Linux DVD disks. When it is installed, the Oracle Validated Configuration RPM does the following:Automatically installs any additional packages needed for installing Oracle Grid Infrastructure and Oracle Database.

Creates an oracle user, and creates the oraInventory (oinstall) and OSDBA
(dba) groups for that user

Sets and verifies sysctl.conf settings, system startup parameters, user limits,
and driver parameters to values based on recommendations from the Oracle

Validated Configurations program

To become an Unbreakable Linux Network customer, contact your sales representative
or purchase a license from the Unbreakable Linux store:
https://shop.oracle.com/product/oraclelinux
To register your server on the Unbreakable Linux Network or to find out more
information, go to:
https://linux.oracle.com
If you are using Oracle Linux 4.7 and later, or Oracle Linux 5.2 and later, then the
Oracle Validated RPM is included on the installation media.

Installing the Oracle Validated Configuration RPM from Unbreakable Linux Network
Use the following procedure to subscribe to Oracle Unbreakable Linux channels and to
add the Oracle Software for the Enterprise Linux channel that distributes the Oracle
Validated Configurations Setup RPM:

1. Complete a default Oracle Linux workstation installation or a default Red Hat
Enterprise Linux installation.
2. Register the server with the Unbreakable Linux Network (ULN). By default, you
are registered for the Oracle Linux Latest channel for the operating system and
hardware.
3. Log in to ULN at:
https://linux.oracle.com
4. Click the Systems tab, and in the System Profiles list, select a registered server. The
System Details window opens and displays the subscriptions for the server.
5. From the Available Channels list, select the _base and _patch channels
corresponding to your Oracle Linux distribution. For example, if your distribution
is Oracle Linux 5 Update 5 for x86_64, then select the following:
Oracle Linux 5 Update 5 installation media copy (x86_64)
Oracle Linux 5 Update 5 Patch (x86_64)
6. Click Subscribe.
7. From a terminal session, as a root user, enter the following command:
# up2date --nox --show-channels
You should see output indicating that you have subscribed to the Oracle Linux
channel, for example:
el5_u5_i386_base
el5_u5_x86_64_patch
8. Open a terminal session as a root user, and install the Oracle Validated
Configurations Setup RPM with up2date using the following command:
# up2date --install oracle-validated
Oracle Linux automatically creates a standard (not role-allocated) Oracle
installation owner and groups, and sets up other kernel configuration settings as
required for Oracle installations.
Check the Oracle Validated Configuration RPM log file to
review the system configuration changes:
/var/log/oracle-validated/results/orakernel.log

Minimum Hardware Requirements

a.) At least 4.0 GB (4096 MB) of physical RAM
b.) Swap disk space proportional to the system's physical memory as follows:

grep MemTotal /proc/meminfo 32940856 kB 32 gb
grep SwapTotal /proc/meminfo 34996216 Kb 32 gb

RAM Swap Space
Between 4 GB and 8 GB 2 x RAM
Between 8 GB and 32 GB 1.5 x RAM
greater than 32 Gb 32 Gb

c.) approximately 5.5 GB of disk space for the Oracle Grid Infrastructure for a Grid
Home for Cluster and ASM Home (more than 1GB and less than 2Tb of disk space) in the /tmp directory.
d.) approximately 4.6 GB of local disk space for the database software.
e.) approximately 1.7 GB of disk space for a preconfigured database that uses file system storage (optional)

SELinux may need to be disabled Please refer Note 454196.1 for more information.
To check whether SELinux is Enabled or Disabled in a system, please refer Note 432988.1
To disable SELinux, please refer Note 457458.1
vi /etc/selinux/config
SELINUX=disabled

RPM Packages
The following packages are required to be installed to have a supported to installation. It is recommended to choose a "default list of RPMs" listed in the document taken from 11gR2 linux install pdf chapter 2.
Asianux Server 3, Oracle Linux 5, and Red HatEnterprise Linux 5
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
elfutils-libelf-devel-static-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-common-2.5
glibc-devel-2.5
glibc-headers-2.5
kernel-headers-2.6.18
ksh-20060214
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.2
libgomp-4.1.2
libstdc++-4.1.2
libstdc++-devel-4.1.2
make-3.81
numactl-devel-0.9.8.i386
sysstat-7.0.2

Note:
Starting with Oracle Database 11g Release 2 (11.2.0.2), all the 32-bit
packages, except for gcc-32bit-4.3, listed in the following
table are no longer required for installing a database on Linux
x86-64. Only the 64-bit packages are required. However, for any
Oracle Database 11g release prior to 11.2.0.2, both the 32-bit and
64-bit packages listed in the following table are required.
If you are using Oracle Unbreakable Enterprise Kernel, then all
required kernel packages are installed as part of the Oracle
Unbreakable Enterprise Kernel installation.

For example: rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep glibc-devel.
# rpm --query --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" binutils compat-db compat-libstdc++ control-center gcc gcc-c++ glibc glibc-devel glibc-headers glibc-common ksh libaio libgcc libgnome libgnomeui libgomp libstdc++ libXp make sysstat
up2date -i libXp
up2date -i glibc-headers
up2date -i gcc
up2date -i gcc-c++

up2date -i glibc-devel --arch=i386
up2date -i libaio-devel --arch=i386
up2date -i unixODBC
up2date -i unixODBC-devel
up2date -i unixODBC --arch=i386
up2date -i unixODBC-devel --arch=i386
up2date -i libXp --arch=i386
up2date -i unixODBC-devel --arch=x86_64
up2date -i oracleasm-support oracleasmlib oracleasm-`uname -r`

cd /var
mkdir /var/oracle_packages
tar -C /var/oracle_packages/ -xvf /var/oracle_packages/oracle_linux_packages.tar

rpm -i --nodeps /var/oracle_packages/cvuqdisk-1.0.7-1.rpm
rpm -i --nodeps /var/oracle_packages/elfutils-libelf-devel-0.137-3.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/elfutils-libelf-devel-static-0.137-3.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/gcc-4.1.2-48.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/gcc-c++-4.1.2-48.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/glibc-devel-2.5-49.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/glibc-devel-2.5-49.i386.rpm
rpm -i --nodeps /var/oracle_packages/glibc-headers-2.5-49.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/kernel-headers-2.6.18-194.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/libaio-devel-0.3.106-5.i386.rpm
rpm -i --nodeps /var/oracle_packages/libaio-devel-0.3.106-5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/libgomp-4.4.0-6.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/libstdc++-devel-4.1.2-48.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/numactl-devel-0.9.8-11.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/oracleasm-2.6.18-194.el5-2.0.5-1.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/oracleasmlib-2.0.4-1.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/oracleasm-support-2.1.3-1.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/strace-4.5.18-5.el5_4.1.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/sysstat-7.0.2-3.el5.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/unixODBC-2.2.11-7.1.i386.rpm
rpm -i --nodeps /var/oracle_packages/unixODBC-2.2.11-7.1.x86_64.rpm
rpm -i --nodeps /var/oracle_packages/unixODBC-devel-2.2.11-7.1.i386.rpm
rpm -i --nodeps /var/oracle_packages/unixODBC-devel-2.2.11-7.1.x86_64.rpm

Creating Grid and Oracle Users and directory for oracle software
1) Create grid user
a. groupadd –g 501 oinstall
b. groupadd –g 502 dba
c. groupadd –g 502 asmadmin
d. groupadd –g 503 asmdba
e. groupadd –g 504 asmoper
f. useradd –u 504 -g500 -G501,502,503,504 -s /bin/bash -d /home/grid grid
i. or useradd -u 504 -g oinstall -G asmadmin,asmdba,asmoper grid
g. passwd grid < keypass >
2) Create oracle user
a. useradd –u 500 -g500 -G501,503 -s /bin/bash -d /home/oracle oracle
i. or useradd -u 500 -g oinstall -G dba,asmdba oracle
b. passwd oracle < keypass >
3) mkdir -p /u00/app/
a. chown -R grid:oinstall /u00/app/
b. chmod -R 775 /u00/app/
c. mkdir /u00/app/oracle
d. mkdir /u00/app/oracle/zipfiles
e. mkdir /u00/app/oracle/cluster_verify_util
4) ssh presence setup
a. NoteID: SSH without a password [ID 464238.1]
b. Can also use /u00/app/grid/grid/sshsetup/sshUserSetup.sh
i. sshUserSetup.sh -hosts "host1 host2" -user grid -advanced
c. Also you can let installer setup ssh
d. If doing ssh setup manually here are the steps
i. as oracle and grid cd $HOME
ii. mkdir .ssh
iii. chmod 700 .ssh
iv. cd .ssh
v. ssh-keygen -t rsa
vi. ssh-keygen -t dsa
vii. scp ~/.ssh/id_dsa.pub grid@host1:~/.ssh/id_dsa.pub
viii. scp ~/.ssh/id_rsa.pub grid@host1:~/.ssh/id_rsa.pub
ix. ssh grid@carz0dbxd02
x. cd .ssh
xi. cat id_dsa.pub >> authorized_keys
xii. cat id_rsa.pub >> authorized_keys
xiii. chmod 640 authorized_keys

Kernel Settings
Modify your kernel settings in /etc/sysctl.conf as follows. If the current value for any parameter is higher than the value listed in this table, do not change the value of that parameter. Range values (such as net.ipv4.ip_local_port_range) must match exactly. Use the below cat commands to validate the values.

Automatic Memory Management
Starting with Oracle Database 11g, the Automatic Memory Management feature
requires more shared memory (/dev/shm)and file descriptors. The size of the shared
memory must be at least the greater of the MEMORY_MAX_TARGET and MEMORY_
TARGET parameters for each Oracle instance on the computer.
Note: The MEMORY_MAX_TARGET and MEMORY_TARGET parameters
cannot be used when the LOCK_SGA parameter is enabled, or with
HugePages on Linux.

In dev we are using 12928Mfor database memory, in prod 28928M
Prod
memory_max_target 28928M
memory_target 28928M
Dev
memory_max_target 12928M
memory_target 12928M

To determine the amount of shared memory available, enter the following command:
# df -h /dev/shm/

Prod
Filesystem Size Used Avail Use% Mounted on
tmpfs 36G 17G 19G 48% /dev/shm

Dev
Filesystem Size Used Avail Use% Mounted on
tmpfs 16G 6.5G 9.4G 41% /dev/shm

cat /proc/sys/net/core/rmem_default
cat /proc/sys/net/core/rmem_max
cat /proc/sys/net/core/wmem_default
cat /proc/sys/net/core/wmem_max
cat /proc/sys/kernel/shmmax
cat /proc/sys/kernel/shmmni
cat /proc/sys/kernel/shmall
cat /proc/sys/fs/file-max
cat /proc/sys/kernel/sem
cat /proc/sys/net/ipv4/ip_local_port_range

kernel.shmall = 6291456
kernel.shmall = 3309568 ( can go as low as this since dev has 12 gb memory target )
kernel.shmall = 4294967296 ( current set at this but this appears to high )
Set shmall equal to the sum of all the SGAs on the system, divided by the page size, getconf PAGE_SIZE 4096

kernel.shmmax = 68719476736 ( 1/2 of physical RAM )
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
If these parameters need to be modified, edit /etc/sysctl.conf. To activate these new settings into the running kernel space, run the “sysctl –p” command as this will propagate the changes to the /proc/sys files listed above. If you modify the individual files, and not sysctl.conf, the changes will be lost at the next reboot.
Network changes require reboot or restart of network service: /etc/rc.d/init.d/network restart
Examine /etc/sysctl.conf for the following entries:
#cat /etc/sysctl.conf

limits.conf
- Add the following settings to /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle hard stack 10240
#
grid hard nofile 65536
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024

pam.d/login
- Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required pam_limits.so

# pam_selinux.so close should be the first session rule
session required pam_selinux.so close
session include system-auth
session required pam_loginuid.so
session optional pam_console.so
# pam_selinux.so open should only be followed by sessions to be executed in the user context
session required pam_selinux.so open
session optional pam_keyinit.so force revoke

profile
- Verify that the below ulimits are set in either /etc/profile or in $ORACLE_HOME/oracle_env

if [ $USER = "oracle" ] || [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Vi /etc/sudoers
%dba ALL=(ALL) ALL
%asmadmin ALL=(ALL) ALL

Storage Setup
MultiPath or EMC PowerPath
rpm -q device-mapper-multipath
cat /etc/multipath.conf
device {
vendor "DGC"
product ".*"
product_blacklist "LUNZ"
getuid_callout "/sbin/scsi_id -g -u -s /block/%n"
prio_callout "/sbin/mpath_prio_emc /dev/%n"
features "1 queue_if_no_path"
hardware_handler "1 emc"
path_grouping_policy group_by_prio
failback immediate
rr_weight uniform
no_path_retry 60
rr_min_io 1000
path_checker emc_clariion

modprobe dm-multipath
service multipathd start
multipath -v2
chkconfig multipathd on
service multipathd reload
ls /dev | grep sd
cd /sys/class/scsi_host/
cat ./host3/device/fc_host\:host3/
ls ./host3/device/fc_host\:host3/
ls ./host3/device/fc_host\:host3/port_name
cat ./host3/device/fc_host\:host3/port_name
cat ./host3/device/fc_host\:host4/port_name
cat ./host4/device/fc_host\:host4/port_name
multipath -l
multipathd -l
service multipathd reload
service multipathd restart
multipath -v2
reboot
multipath -l
chown grid /dev/dm-*
chgrp asmadmin /dev/dm-*
ll /dev | grep dm
multipath –l

Disk Setup
Reference Doc Page 10 of RACGuides_Rac11gR2OnLinux.pdf
Once the LUNs have been presented from the SAN to ALL servers in the cluster, partition the LUNs from one node only, run fdisk to create a single whole-disk partition with exactly 1 MB offset on each LUN to be used as ASM Disk. From the fdisk prompt, type "u" to switch the display unit from cylinder to sector. Then create a single primary partition starting on sector 2048 (1MB offset assuming sectors of 512 bytes per unit).
fdisk /dev/dm
Command (m for help): u
Changing display/entry units to sectors
Command (m for help): n
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First sector (61-1048575, default 61): 2048
Last sector or +size or +sizeM or +sizeK (2048-1048575, default 1048575):
Using default value 1048575
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
Load the updated block device partition tables by running the following on ALL servers participating in the cluster: sbin/partprobe

ASM Setup
The latest ASM RPMs can be downloaded from Oracle at:
http://www.oracle.com/technology/tech/linux/asmlib/index.html
NOTE: The below query listing the ASM RPMs is an example and is not meant to provide the correct version.
# rpm -qa | grep oracleasm | sort

Do asm under grid control user ( grid )
cd /etc/init.d/
./oracleasm stop
./oracleasm disable
./oracleasm configure
/etc/init.d/oracleasm enable
./oracleasm configure

Configuring the Oracle ASM library driver.
Default user to own the driver interface []: grid
Default group to own the driver interface []:asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
/etc/init.d/oracleasm createdisk ARCHIVE1 /dev/dm-6
/etc/init.d/oracleasm createdisk DATA1 /dev/dm-7
/etc/init.d/oracleasm createdisk OCR_VOTE1 /dev/dm-8
/etc/init.d/oracleasm createdisk OCR_VOTE2 /dev/dm-2
/etc/init.d/oracleasm createdisk OCR_VOTE3 /dev/dm-3
/etc/init.d/oracleasm listdisks
I should see disks now in /dev/oracleasm/disks/

Nice additions to Amazon RDS with 11.2.0.2v3 version

2011-10-04T15:28:00.000-07:00

Thank up Amazon RDS team for giving us DBA's a little more power with 11.2.0.2v3 version.

Execute privileges to a number of DBMS packages including DBMS_CRYPTO, DBMS_ALERT, DBMS_LOCK and DBMS_PIPE are now available.
Privileges to grant DBA, CONNECT and RESOURCE roles to other users are also available.
You can use the CTXAPP (Oracle Text) role and grant it to other users.
The system privileges “RESTRICTED SESSION” and “GRANT ANY OBJECT".

For the full announcement forum: https://forums.aws.amazon.com/ann.jspa?annID=1186

Amazon RDS Oracle Database and Alert and Listener Log

2011-10-04T14:36:00.000-07:00

To see the oracle alert log and listener log data on Oracle RDS at Amazon RDS, we utilize sql.

For Alert Log:
select message_text from alertlog;

For listener log
select message_text from listenerlog;

Note: only last 10 MB of entries in these files are available since oracle is rotating them at 10mb interval so if you need history this might be a good candidate for nightly job.

More information can be found in AWS api rds-ug.pdf

Oracle Dynamic Session Kill for sessions in Amazon RDS

2011-10-04T14:16:00.000-07:00

For those folks running Oracle Databases in Amazon RDS environment, we can kill sessions using the Amazon API: rdsadmin.rdsadmin_util.kill

select 'exec rdsadmin.rdsadmin_util.kill('|| sid ||',' || serial# || ');'
from v$session where username in ('&myUsername', '&myOtherUserName');

Here is the link to the amazon API doc. awsdocs.s3.amazonaws.com/RDS/latest/rds-ug.pdf

RMAN Backup Monitoring

2011-10-04T10:02:00.000-07:00

A good query to monitor your backups in your rman recovery catalog is.

SELECT db_name, start_time, end_time, status
FROM rcat.rc_rman_backup_job_details
WHERE end_time > sysdate -1

TEST 10/4/2011 12:30:04 AM 10/4/2011 12:32:51 AM COMPLETED
DEV1 10/4/2011 3:05:05 AM 10/4/2011 8:56:13 AM COMPLETED
PROD1 10/3/2011 11:24:07 PM 10/4/2011 9:57:56 AM RUNNING

RMAN Duplicate 10g

2011-09-29T14:25:00.000-07:00

I ran into oracle RMAN bug today and figured it was a good time to add a post on Oracle 10g RMAN duplicates and the work around for the bug I encountered.

The bug is documented on support.oracle.com in Note ID 1076816.1. In short the problem as I was trying to restore up to a sequence time where the sequence had not been recorded in v$log_history or rc_log_history. The work around is to restore to the sequence or a sequence that has been recorded in either v$log_history or rc_log_history.

For an overview of my duplicate procedure.

There is a good note on support.oracle.com for rac database duplicate to asm.
RMAN Duplicate Database From RAC ASM To RAC ASM [ID 461479.1]

I create spfile from pfile and mount and edited pfile on my target server. I then add a tns entry for the database I am cloning on the server where my clone is going to be hosted to allow me to communicate with the target database.

I then create an rman script setting the new names of the files where I am placing them since I am restoring to a different asm disk group than target database uses. This sql script is helpful to set the names for the restored data files.

select 'set newname for datafile ' || file_id || ' to ''+DATA/test/' ||
substr(file_name,instr(file_name,'/',-1)+1) || ''';'
from dba_data_files

set newname for datafile 1 to '+DATA/test/system.311.750536617';
set newname for datafile 3 to '+DATA/test/sysaux.342.750535475';

Here is the sql script to get my max sequence# to restore to.

select max(sequence#) from v$log_history where thread# =1;

Here is what my clone rman rcv script looks like.

run
{
set until sequence 2727 thread 1;
set newname for datafile 1 to '+DATA/test/system.311.750536617';
set newname for datafile 3 to '+DATA/test/sysaux.342.750535475';
... set newnames ...
DUPLICATE TARGET DATABASE TO test;
restore database;
SWITCH DATAFILE ALL;
recover database;
}

Now to run the duplicate, connect to rman on server hosting cloned database.

rman target sys@mytargetdb auxiliary / catalog rmancat/rmancatpwd@emrep
@duplicate.rcv

Move ASM spfile to different disk group

2011-09-28T15:03:00.000-07:00

To move an asm spfile from one disk group to another, I recommend using the method create spfile from pfile, editing the pfile and then creating pfile from edited spfile.

In my case the spfile was located in DATA01.
ASMCMD> ls DATA01/ASM/ASMPARAMETERFILE/
REGISTRY.253.762024745

create pfile='/tmp/init+ASM.ora' from spfile;

Edit the /tmp/init+ASM.ora and change entry +ASM.asm_diskgroups='DATA01'#Manual
In my case I changed it to +ASM.asm_diskgroups='DATA1'

create spfile '+DATA1' from pfile '/tmp/init+ASM.ora';

Restart asm or in my case I just restarted all crs services.
crs_stop -all
crs_start -all

Installing Oracle 11g 64 bit on Windows Server 2008

2010-01-11T10:19:00.000-08:00

When installing Oracle 11.1.0.7 on Windows Server 2008 64 bit, the installer gave a an error saying the Operating System ( OS ) was not supporting. In looking at the install log on c:\program files\oracle, I noticed the installer thinks the OS is windows vista.

To work around this error, I selected the check box for user verified and continued with the install.

Migrate Oracle Windows from 32 bit to 64 bit

2010-01-11T10:15:00.000-08:00

Create the Oracle service on the 64bit system, using the ORADIM command.
oradim –new –sid orcl –startmode auto –pfile c:\oracle\product\11.1.0\db_1\init.ora

Run Sqlplus
Issue the command: startup upgrade.

Run the script:
cd
c:\oracle\product\11.1.0\db_1\rdbms\admin\
--This script invalidates all the objects and change the word size
@utlip.sql
--This script recompiles the invalid objects
@utlrp.sql

sql trace and tkprof

2009-12-17T11:04:00.000-08:00

Sql trace and tkprof are useful tools for understanding what sqls are being generated by a poorly performing report or application.

To invoke sql trace you can either alter your session using sql command
ALTER SESSION SET sql_trace = true;

To invoke sql trace in other users/application sessions you can use plsql package
dbms_system.set_sql_trace_in_session ( sid, serial#, TRUE 0 );

To find out the users sid and serial we select from v$session
select sid, serial# , status from v$session where program = < ' The Relevant EXE ' >;

example:
select sid, serial# , status from v$session where program = 'BbTSMain.exe' ;

If there are multiple sessions as there are for BbTSMain.exe then I can dynamically build a sql trace for this

example
spool trace.sql
select 'EXECUTE dbms_system.set_sql_trace_in_session (' ||sid || ','|| serial# || ', TRUE);' from v$session where program='BbTSMain.exe'
/
spool off

This will generate a script like

EXECUTE dbms_system.set_sql_trace_in_session (396,55252, TRUE);

@trace.sql
Now that sql tracing is turned on, it is time to look for our trace directory and tracefile
show parameter user_dump_dest

sort the directory for the *.trc files being actively modified

Now lets add some value to the sql trace by running the tracefile through the tkprof utility

tkprof explain=envision/@envision

example: tkprofe bbts_ora_2160.trc k.out explain=envision/123@envision

edit the outfile to see the sqls and the execution plans

You have now successfully mined the underlying sqls for the application

View Oracle Bind Variables version 10g 11g

2009-10-01T09:31:00.000-07:00

Using grid control and v$sql you can see sql statements but to see the bind varibles in 10g and 11g you need to query the view v$sql_bind_capture.

select
sql_id,
t.sql_text SQL_TEXT,
b.name BIND_NAME,
b.value_string BIND_STRING
from
v$sql t
join v$sql_bind_capture b using (sql_id)
where
b.value_string is not null
and sql_id='&mysqlid'
/

Find Oracle Trace File

2009-09-23T13:06:00.000-07:00

When looking for an oracle trace file here are a 2 sqls that will help you out. First ascertain the SID then run sql 1 to get the ospid. Then run sql 2 to get the trace file.

Sql 1
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/
Then user the spid ( server process identifier ) to get the location of the tracefile.
SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS where pid= &spid

To turn on trace event 10046 you can use the oradebug

connect / as sysdba
oradebug setospid
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

Clean Oracle 11g diag dir with adrci

2009-09-18T08:37:00.001-07:00

Cleaning the diag directory in Oracle 11g can be done with using cronjob and with embedded adrci commands. Adrci is Oracles new interface for working with and managing incidents. To keep your diag directory from filling up you can set a default purge script. An example is below

Cronjob.
02 04 * * * /var/opt/oracle/bin/clean_trc_adrci.sh >/tmp/clean_trc_adrci.log 2>&1

Script:
#!/bin/bash
export ORACLE_SID=test
export ORAENV_ASK=NO
# keep 7 days of incidents
. oraenv
$ORACLE_HOME/bin/adrci << CMD
set homepath diag/rdbms/test/test
set home diag/rdbms/test/test
purge -age 10080
CMD

Perl Script Oracle Apps Clone CONTEXT_FILE

2009-06-13T21:06:00.001-07:00

I was working on automating Rapid Clone for Oracle Apps 11.5 and need to script changes to CONTEXT_FILE. Here is the perl script I used.

$infile=$ENV{"CONTEXT_FILE"};
print "infile is $infile \n";
open(IN,"$infile") || die "cannot open $infile for reading: $!";
$outfile="$infile" . "_new";
print "outfile is $outfile \n";
open(OUT,">$outfile") || die "cannot open $outfile for writing: $!";
while () {
if (/AF_CLASSPATH/) {
s/appsborg/appsborg2/;
}
if (/\disabled/) {
s/disabled/enabled/;
}
if (/\disabled/) {
s/disabled/enabled/;
}
if (/\disabled/) {
s/disabled/enabled/;
}
if (/\disabled/) {
s/disabled/enabled/;
}
if (/\disabled/) {
s/disabled/enabled/;
}
print OUT $_;
}
close (IN);
close (OUT);
print "new context file is $outfile \n";

Here is the shell script I used a wrapper.
perl chgcontext.pl
diff $CONTEXT_FILE "$CONTEXT_FILE"_new
cp $CONTEXT_FILE "$CONTEXT_FILE"_old
cp "$CONTEXT_FILE"_new $CONTEXT_FILE
diff $CONTEXT_FILE "$CONTEXT_FILE"_new

Database Health Check List

2009-06-05T07:47:00.000-07:00

Daily Checks
Alert Log
Jobs ( DBMS_JOBS, Scheduler, CronJobs )
Invalid_Objects
Grid Control Alerts
AWR Report
DashBoard
Backup successful
Standby lag is ok

Weekly Check
All the above daily
Dashboards Enhancement
Bad Queries evaluated with explain plan
Daily checks are automated
Job logs are being monitored for success/failurs
Dashboard updated and email is sent and enhanced

Monthly Check
All daily/weekly checks
Password file up to date
Any Oracle issues have updated SR

Quarterly Check
Oracle patches are reviewed
Database DB and APP diagrams are up to date

Restore and Recover Inconsistent rman backup

2009-02-24T11:52:00.000-08:00

Have you ever had to restore an oracle database when you did not have all the archived redo logs that occurred during the backup? Well I did and it was an interesting day that required an escalated SR to oracle support. Here is how I recovered.

I restored the tape backup using rman and a backup of the snapshot control file found in my $ORACLE_HOME/dbs directory.

cp $ORACLE_HOME/dbs/snapcf_mydb.f /ora01/mydb.ctl

I mounted the controlfile
startup mount

I connected to rman
rman target /

I restored my inconsistent backup
restore database;

I tried to recover my database but could not since I am missing archived redo logs during the backup.
recover database;

I tried opening the database with alter database resetlogs but could not since system datafile was inconsistent.

I changed init.ora parameter to add the following parameters per Oracle Support recommendation and then restarted the database in mount mode.
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_minimum_giga_scn=6884
_allow_error_simulation=true
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU1$,_SYSSMU2$, _SYSSMU3$,_SYSSMU4$, _SYSSMU5$,_SYSSMU6$,_SYSSMU7$, _SYSSMU8$,_SYSSMU9$, _SYSSMU10$,SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$, _SYSSMU16$,_SYSSMU17$, _SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SYSSMU21$, SYSSMU22$,_SYSSMU23$,_SYSSMU24$, _SYSSMU25$, _SYSSMU26$,_SYSSMU27$,_SYSSMU28$,_SYSSMU29$, _SYSSMU30$,_SYSSMU31$)
undo_management=manual

In sqlplus I could then do
alter database open resetlogs;

I then created a shell database using dbca

I then exported the full database using
exp system/manager full=y file=full.dmp log=full.log

Next I imported my full export into my new shell database
imp system/manager file=full.dmp log=impfull.log commit=y ingore=yes destroy=no

I then put the new database in archivelog mode so I do not have this problem again.
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

I then created a full backup and archive log backups through grid control.