Oracle Database Experts

You can see from the output listed below that the ASM diskgroups, DATA and FRA, are composed of disks /dev/hda7 to /dev/hda10.

DISKGROUP       DISK# DISKNAME                 TOTAL_MB      FREE_MB PATH                                HEADER_STATU
--------------- ----- -------------------- ------------ ------------ ----------------------------------- ------------
DATA                0 DATA_0000                   3,914        3,301 /dev/hda6                           MEMBER
                    1 DATA_0001                   3,914        3,294 /dev/hda7                           MEMBER
                    2 DATA_0002                   3,914        3,314 /dev/hda8                           MEMBER
                    3 DATA_0003                   3,914        3,288 /dev/hda9                           MEMBER
***************                            ------------ ------------
sum                                              15,656       13,197

FRA                 0 FRA_0000                    3,914        3,847 /dev/hda10                          MEMBER
***************                            ------------ ------------
sum                                               3,914        3,847

We will demonstrate how to migrate to ASMLIB from block devices.  Since ASM stores disk information in the first 1MB header of each disk, diskgroup information can be re-instantiated retrieving the header information of each disk.

Note:  Before you convert block devices to ASMLIB, you need to make sure that every database is shutdown prior to creating ASM disks using the /etc/init.d/oracleasm command.

[root@v1 sbin]# kudzu --probe --class=network
-
class: NETWORK
bus: PCI
detached: 0
device: eth0
driver: pcnet32
desc: "Advanced Micro Devices [AMD] 79c970 [PCnet32 LANCE]"
vendorId: 1022
deviceId: 2000
subVendorId: 1022
subDeviceId: 2000
pciType: 1
pcidom:    0
pcibus:  2
pcidev:  3
pcifn:  0
-
class: NETWORK
bus: PCI
detached: 0
device: eth1
driver: pcnet32
desc: "Advanced Micro Devices [AMD] 79c970 [PCnet32 LANCE]"
vendorId: 1022
deviceId: 2000
subVendorId: 1022
subDeviceId: 2000
pciType: 1
pcidom:    0
pcibus:  2
pcidev:  0
pcifn:  0

Linux 64-bit Requirements for Red Hat 5 for Oracle Database 11g Release 2
(Also includes Asianux Server 3, Oracle Enterprise Linux 5,)

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2
libstdc++-devel
make-3.81
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-2.2.11
unixODBC-devel-2.2.11
unixODBC-devel-2.2.11 (32 bit)

The following list of RPMs are required to successfully install Oracle Database 11g Release 1 on Red Hat:

binutils-2.15.92.0.2-18
compat-libstdc++-33.2.3-47.3
elfutils-libelf-0.97-5
elfutils-libelf-devel-0.97-5
glibc-2.3.9.4-2.19
glibc-common-2.3.9.4-2.19
glibc-devel-2.3.9.4-2.19
gcc-3.4.5-2
gcc-c++-3.4.5-2
libaio-devel-0.3.105-2
libaio-0.3.105-2
libgcc-3.4.5
libstdc++-3.4.5-2
libstdc++-devel-3.4.5-2
make-3.80-5
sysstat-5.0.5
unixODBC-2.2.11
unixODBC-devel-2.2.11

If you are considering migrating to ASM, here’s a simple piece of code to restore the database into ASM. You must take a backup of the database using RMAN. First, you would restore the controlfile and then proceed to restore the database to the +Data diskgroup for this particular example. This script simply generates an RMAN restore script to be executed after the restore of the control file from backup.

set echo off ver off feed off pages 0 head off lines 1022 trims on
spool /tmp/restore_database.sql
prompt run
prompt {
prompt allocate channel d1 type disk;;
prompt allocate channel d2 type disk;;
prompt allocate channel d3 type disk;;
prompt allocate channel d4 type disk;;
select 'set newname for datafile '||file#||' to '||chr(39)||'+DATA'||chr(39)||';'
from v$datafile;
prompt restore database;;
prompt switch datafile all;;
prompt release channel d1;;
prompt release channel d2;;
prompt release channel d3;;
prompt release channel d4;;
prompt }
spool off

You wanted to know how much space is actually being used at each of your ASM disks. You can use the following query to determine that.

set lines 255
col path for a35
col Diskgroup for a15
col DiskName for a20
col disk# for 999
col total_mb for 999,999,999
col free_mb for 999,999,999
compute sum of total_mb on DiskGroup
compute sum of free_mb on DiskGroup
break on DiskGroup skip 1 on report -

set pages 255

select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path, b.header_status
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
order by b.group_number, b.disk_number, b.name
/

set lines 122
set pages 66

You can use this comprehensive shell script to export the database to /dev/null to check for logical corruptions. This coupled with dbverify can be a great toolkit for DBAs. In Oracle Database 11g, the validate database command simplifies checking database corruptions.

#!/bin/ksh
# ------------------------------------------------------------------------------------------
# INITIAL SETUP
# ------------------------------------------------------------------------------------------
export ORACLE_BASE=`grep -i ^oracle: /etc/passwd | cut -d: -f6`
. $ORACLE_BASE/general/sh/functions.ksh

set_basics;
# ----------------------
# CONFIGURATION SECTION
# ----------------------
# Ksh version issue
# So enter it again here
#-----------------------------------------------
export FILENAME=$(basename $0 |awk -F"." {'print $1'})
export CONFIG_FILE="${SH}"/"${FILENAME}".conf
export CONTROL_FILE="${SH}"/"${FILENAME}".ctl

set_oratab;

#---------------------------------------------------------------------
# Setup the Oracle Environment and pass the ORACLE_SID
#---------------------------------------------------------------------
[ -d /var/opt/oracle/bin ] && export BINDIR=/var/opt/oracle/bin || export BINDIR=$HOME/general/sh/bin
. $BINDIR/oracle_setup.ksh `$BINDIR/getsid.ksh`

TODAY=`date +%m%d%y:%H%M`; export TODAY

for DB in $(cat $ORATAB |grep -v \^# |grep :Y |cut -d: -f1 |sort |sed 's/ //g')
do
echo "$DB" > /tmp/exp.devnull.current_database

#---------------------------------------------------------------------
# Setup the Oracle Environment and pass the ORACLE_SID
#---------------------------------------------------------------------
. oracle_setup.ksh $DB

export TARGET=$ORACLE_BASE/admin/${ORACLE_SID}
export LOGFILE=${TARGET}/log/${ORACLE_SID}.${TODAY}.exp.devnull.log
export ERRFILE=${TARGET}/log/${ORACLE_SID}.${TODAY}.exp.devnull.err
export EXPFILE=$TARGET/exp/$ORACLE_SID.export.nr.${TODAY}.dmp

[ -f $ERRFILE ]; rm $ERRFILE

exp "'/ as sysdba'" buffer=10240000 \
   full=y \
   compress=n \
   consistent=n \
   rows=y \
   direct=n \
   statistics=estimate \
   file=/dev/null \
   volsize=0 \
   log=$LOGFILE

grep ORA- $LOGFILE > $ERRFILE
grep EXP- $LOGFILE |egrep -v "EXP-00091" >> $ERRFILE

if [ -s $ERRFILE ]
then
  cat $ERRFILE
  set_mail;
  $SH/alert_notification.ksh $FN `hostname` $ORACLE_SID "" "Export Norows Error" $ERRFILE
  echo "${ORACLE_SID}'s devnull full database export completed with errors/warnings -- \c"
  date
else

  echo "${ORACLE_SID}'s devnull full database export completed successfully -- \c"

  date
fi

#gzip $EXPFILE
#cd $TARGET
#find $TARGET/exp -name "$ORACLE_SID.export.devnull.*" -follow -mtime +7 -exec rm {} \;

done

[ -f $SH/EMConfig_${DATABASE_NAME}.log ]; rm $SH/EMConfig_${DATABASE_NAME}.log

export DATABASE_NAME=$1
# --------------------------------------------------- #
#  New Tier Environments:
#  export ORACLE_HOME=/apps/oracle/product/10.2.0/DB
#  export AGENT_HOME=/apps/oracle/product/agent
#  export PORT=1975
#      -RMI_PORT 5520 -JMS_PORT 5540
#      -DBCONSOLE_HTTP_PORT 55000
# --------------------------------------------------- #

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/DB
export AGENT_HOME=/u01/app/oracle/product/agent
export PORT=1975
emca -config dbcontrol db -repos create -silent
-DB_UNIQUE_NAME $DATABASE_NAME -PORT $PORT
-EM_HOME $ORACLE_HOME -LISTENER $DATABASE_NAME -SERVICE_NAME $DATABASE_NAME
-CENTRAL_AGT_HOME $AGENT_HOME -SYS_PWD $SYS_PASSWORD -SID $DATABASE_NAME
-ORACLE_HOME $ORACLE_HOME
-DBSNMP_PWD $DBSNMP_PASSWORD
-SYSMAN_PWD $SYSMAN_PASSWORD
-HOST `hostname` -LISTENER_OH $ORACLE_HOME
-LOG_FILE $SH/EMConfig_${DATABASE_NAME}.log;

You can use the following korn shell script instead of the crs_stat.  Here’s and example of the output produced by this shell script:

 

oracle:VISK1 $./dba_crs
HA Resource                                    Target     State            
-----------                                    ------     -----            
ora.VISK.VISK1.inst                            ONLINE     ONLINE on dbaexpert10
ora.VISK.VISK2.inst                            ONLINE     ONLINE on dbaexpert11
ora.VISK.db                                    ONLINE     ONLINE on dbaexpert10
ora.dbaexpert10.ASM1.asm                       ONLINE     ONLINE on dbaexpert10
ora.dbaexpert10.VISK_dbaexpert10.lsnr          ONLINE     ONLINE on dbaexpert10
ora.dbaexpert10.gsd                            ONLINE     ONLINE on dbaexpert10
ora.dbaexpert10.ons                            ONLINE     ONLINE on dbaexpert10
ora.dbaexpert10.vip                            ONLINE     ONLINE on dbaexpert10
ora.dbaexpert11.ASM2.asm                       ONLINE     ONLINE on dbaexpert11
ora.dbaexpert11.VISK_dbaexpert11.lsnr          ONLINE     ONLINE on dbaexpert11
ora.dbaexpert11.gsd                            ONLINE     ONLINE on dbaexpert11
ora.dbaexpert11.ons                            ONLINE     ONLINE on dbaexpert11
ora.dbaexpert11.vip                            ONLINE     ONLINE on dbaexpert11

 #!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
#    - Returns formatted version of crs_stat -t, in tabular
#      format, with the complete rsc names and filtering keywords
#   - The argument, $RSC_KEY, is optional and if passed to the script, will
#     limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
#   - $ORA_CRS_HOME should be set in your environment

RSC_KEY=$1
export ORA_CRS_HOME=/orahomes/CRS
QSTAT=-u
AWK=/usr/bin/awk    # if not available use /usr/bin/awk

# Table header:echo ""
$AWK \
  'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
          printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'

# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
 'BEGIN { FS="="; state = 0; }
  $1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
  state == 0 {next;}
  $1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
  $1~/STATE/ && state == 2 {appstate = $2; state=3;}
  state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'

Let’s look at invisible indexes. For our demonstration purposes, create a table called books based on the book_id which has an auto-generated PK and other basic columns: name, author, ISBN.

  </span></span>

create table books (book_id number,

name varchar2(255),

author varchar2(255),

isbn varchar2(255));

insert into books

(book_id, name, author, isbn) values (2,'RMAN Recipes','Darl Kuhn','1590598512');

insert into books

(book_id, name, author, isbn) values (1,'Oracle ASM','Nitin Vengurlekar','0071496076');

insert into books

(book_id, name, author, isbn) values (3,'Oracle 11g New Features','Charles Kim','1590599101');

Now, let’s create the invisible index:

create index BOOK_ISBN_INVISIBLE_IDX on books(isbn) invisible;

By querying user_indexes, you can see that the new index is invisible.
1* select index_name,VISIBILITY

from user_indexes

where index_name=’BOOK_ISBN_INVISIBLE_IDX’

SQL> /

INDEX_NAME VISIBILIT

—————————— ———

BOOK_ISBN_INVISIBLE_IDX INVISIBLE

You must set the initialization parameter, optimizer_use_invisible_indexes = TRUE to take adavantage of invisible indexes.

Technorati : Posted by Charles Kim @ DBAExpert.com

The upgrade process to 11gR1 is the same process as upgrading from 10.2.0.1 to 10.2.0.3. The steps for the upgrade process is:

Shutdown CRS

The following steps are optional:

• Modify /etc/inittab and comment out last three lines
• init q

$ORA_CRS_HOME/bin/crsctl stop crs

Execute pre-update script from the unzipped software/clusterware/upgrade directory
./preupdate.sh -crshome $ORA_CRS_HOME -crsuser oracrs

Install new software binaries on all the RAC nodes
./runInstaller

From $ORA_CRS_HOME/install directory
./rootupgrade

Check upgrade status by querying activeversion of the CRS
crsctl query crs activeversion