Oracle Database Experts

By Charles Kim

July 20th, 2007

RAID Levels

RAID supports various configurations, including levels 0, 1, 4, 5, and linear. These RAID types are defined as follows:

  • Level 0 – RAID level 0, often called “striping,” is a performance-oriented striped data mapping technique. This means the data being written to the array is broken down into strips and written across the member disks of the array, allowing high I/O performance at low inherent cost but provides no redundancy. The storage capacity of a level 0 array is equal to the total capacity of the member disks in a Hardware RAID or the total capacity of member partitions in a Software RAID.
  • Level 1 – RAID level 1, or “mirroring,” has been used longer than any other form of RAID. Level 1 provides redundancy by writing identical data to each member disk of the array, leaving a “mirrored” copy on each disk. Mirroring remains popular due to its simplicity and high level of data availability. Level 1 operates with two or more disks that may use parallel access for high data-transfer rates when reading but more commonly operate independently to provide high I/O transaction rates. Level 1 provides very good data reliability and improves performance for read-intensive applications but at a relatively high cost [1] The storage capacity of the level 1 array is equal to the capacity of one of the mirrored hard disks in a Hardware RAID or one of the mirrored partitions in a Software RAID.
  • Level 4 – Level 4 uses parity [2] concentrated on a single disk drive to protect data. It’s better suited to transaction I/O rather than large file transfers. Because the dedicated parity disk represents an inherent bottleneck, level 4 is seldom used without accompanying technologies such as write-back caching. Although RAID level 4 is an option in some RAID partitioning schemes, it is not an option allowed in Red Hat Linux RAID installations [3] The storage capacity of Hardware RAID level 4 is equal to the capacity of member disks, minus the capacity of one member disk. The storage capacity of Software RAID level 4 is equal to the capacity of the member partitions, minus the size of one of the partitions if they are of equal size.
  • Level 5 – This is the most common type of RAID. By distributing parity across some or all of an array’s member disk drives, RAID level 5 eliminates the write bottleneck inherent in level 4. The only performance bottleneck is the parity calculation process. With modern CPUs and Software RAID, that usually isn’t a very big problem. As with level 4, the result is asymmetrical performance, with reads substantially outperforming writes. Level 5 is often used with write-back caching to reduce the asymmetry. The storage capacity of Hardware RAID level 5 is equal to the capacity of member disks, minus the capacity of one member disk. The storage capacity of Software RAID level 5 is equal to the capacity of the member partitions, minus the size of one of the partitions if they are of equal size.
  • Linear RAID – Linear RAID is a simple grouping of drives to create a larger virtual drive. In linear RAID, the chunks are allocated sequentially from one member drive, going to the next drive only when the first is completely filled. This grouping provides no performance benefit, as it is unlikely that any I/O operations will be split between member drives. Linear RAID also offers no redundancy and, in fact, decreases reliability — if any one member drive fails, the entire array cannot be used. The capacity is the total of all member disks.



Technorati :

July 11th, 2007

RAC Logfiles

$ORA_CRS_HOME/log/<hostname>/

  1. This directory houses the Oracle Clusterware alert log (alert[hostname].log)
  2. The subdirectories grow fast and need to be purged on a regular basis
  3. Need to check for core and dump files and remove them

Recommendation:

This directory should be put on shared storage

 

Subdirectories include:

  • client

Contains log files for various OCR applications including clscfg, css, ocrcheck, ocrconfig, ocrdump and oifcfg

  • crsd

houses log files for the crsd daemon including crsd.log

  • cssd

houses logfiles for ocssd daemon including ocssd.log

  • evmd

houses logfiles for evmd daemon including evmd.log

  • racg

houses logfiles for node applications including VIP and ONS

 

racg logfiles also exist at $ORACLE_HOME/log/hostname/racg

~

~

~

July 10th, 2007

rpm.ksh

rpm -qa –queryformat “%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n”
List of RPMs for 64-bit RAC implementation

July 9th, 2007

/etc/sudoers

Oracle was added to the sudoers file so that most of the maintenance for CRS can be done as oracle.

[root@rac1.dbaexpert.com ~]# cat /etc/sudoers

# sudoers file.

#

# This file MUST be edited with the ‘visudo’ command as root.

#

# See the sudoers man page for the details on how to write a sudoers file.

#


# Host alias specification


# User alias specification

User_Alias DBAS = oracle


# Cmnd alias specification

Cmnd_Alias SUDO_ROOT = /etc/init.d/init.crs, \

/usr/bin/passwd oracle, /u01/app/oracle/product/CRS/bin/, \

/bin/cat /etc/sudoers, /sbin/init q, \

/usr/bin/vim /etc/inittab, \

/u01/app/oracle/product/CRS/root.sh, \

/u01/app/oracle/product/10.2.0/DB/root.sh, \

/u01/app/oracle/product/10.2.0/COMP/root.sh, \

/u01/app/oracle/oraInventory/orainstRoot.sh, \

/u01/app/oracle/product/CRS/install/root102.sh


# Defaults specification


# User privilege specification

root ALL=(ALL) ALL


# Uncomment to allow people in group wheel to run all commands

# %wheel ALL=(ALL) ALL


# Same thing without a password

# %wheel ALL=(ALL) NOPASSWD: ALL


# Samples

# %users ALL=/sbin/mount /cdrom,/sbin/umount /cdrom

# %users localhost=/sbin/shutdown -h now


DBAS ALL = SUDO_ROOT

July 5th, 2007

AUDSES$ Sequence

Prior to 10.2.0.3, the AUDSES$ sequence had to be manually adjusted to 10,000 from the default of 20. Failure to change this cache_size from the default value of 20 can cause havoc to you RAC environment. This is fixed in 10.2.0.3.

1 select sequence_owner, sequence_name, cache_size

2 from dba_sequences

3* where sequence_name = ‘AUDSES$’

SQL> /

 

SEQUENCE_OWNER SEQUENCE_NAME CACHE_SIZE

—————————— —————————— ———-

SYS AUDSES$ 10000

July 5th, 2007

RPMs for RAC on Redhat 4 64-bit

RPMs Needed for RHEL 4.0 64-bit

Any missing RPMs will have to be added to the system before installing CRS, the database software or Grid Control. Use the following command to validate that the required RPMs are installed:

rpm –qf ‘%{NAME}.%{VERSION}.%{ARCH}\n’ -qa|egrep “^binutils|^control-center|^gcc|^gnome-libs|^libstdc++|^make|^pdksh|^sysstat| \

^xscreensave|^compat-db|^glibc|^compat-libstdc++|^libaio|^libgcc|^glibc-devel| \

^openmotif21|^setarch|^xorg-x11-deprecated-libs”|sort

Base RPMs as documented in the installation guide:

binutils-2.15.92.0.2-13.0.0.0.2.x86_64 or binutils-2.15.92.0.2-18.x86_64.rpm from RHEL4 Update 3 (Ask the SAs to do an up2date on binutils to pull down the latest)

compat-db-4.1.25-9.i386.rpm

compat-db-4.1.25-9.x86_64.rpm

control-center-2.8.0-12.x86_64.rpm

gcc.3.4.3-22.1.x86_64.rpm

gcc-c++-3.4.3-22.1.x86_64.rpm

glibc-2.3.4-2.9.i686.rpm

glibc-2.3.4-2.9.x86_64.rpm

glibc-common-2.3.4-2.9.x86_64.rpm

gnome-libs-1.4.1.2.90-44.1.x86_64

libstdc++-3.4.3-22.1.x86_64

libstdc++-devel-3.4.3-22.1.x86_64

make-3.80-5.x86_64.rpm

pdksh-5.2.14-30.x86_64.rpm

sysstat-5.0.5-1.x86_64.rpm

xscreensaver-4.18-5.rhel4.2.x86_64.rpm

You will need these also to be able to complete the installation successfully:

Needed RPMS for RHEL4 not mentioned in Install Guide/Release Notes

  1. Ref. Note 339510.1, Note 340622.1 Note 360853.1 Note 344761.1 Note 340369.1
  2. 2. Also see metalink notes 169706, 283748.1, and 279069.1

compat-libstdc++-33-3.2.3-47.3.x86_64.rpm

compat-libstdc++-33-3.2.3-47.3.i386.rpm

glibc-kernheaders-2.4-9.1.87.x86_64.rpm

glibc-headers-2.3.4-2.9.x86_64.rpm

libaio-0.3.103-3.i386.rpm

libaio-0.3.103-3.x86_64.rpm

libgcc-3.4.3-22.1.i386.rpm

glibc-devel-2.3.4-2.9.x86_64.rpm

glibc-devel-2.3.4-2.9.i386.rpm

xorg-x11-deprecated-libs-6.8.2-1.EL.13.6.i386.rpm

Please make sure that both 32-bit and 64-bit RPMs are loaded.

Required for Grid Control – including the Agent – I would ask for these RPMs as part of the standard RAC build

openmotif21-2.1.30-11

setarch-1.6-1

Installing the cvuqdisk Package for Linux

If you are using Red Hat or SUSE Linux, then you must download and install the operating system package cvuqdisk. Without cvuqdisk, CVU is unable to discover shared disks, and you receive the error message “Package cvuqdisk not installed” when you run CVU.

To install the cvuqdisk RPM, complete the following procedure:

  1. Locate the cvuqdisk RPM package, which is in the directory clusterware/rpm on the installation media. If you have already installed Oracle Clusterware, then it is located in the directory CRS_home/rpm.
  2. Copy the cvuqdisk package to each node on the cluster. You should ensure that each node is running the same version of Linux.
  3. Log in as root.
  4. Using the following command, check to see if you have an existing version of the cvuqdisk package:# rpm -ihv cvuqdisk
  5. If you have an existing version, then enter the following command to de-install the existing version:# rpm -e cvuqdisk
  6. Set the environment variable CVUQDISK_GRP to point to the group that will own cvuqdisk, typically oinstall.
  7. Use the following command to install the cvuqdisk package if it is not present:

rpm -iv cvuqdisk-1.0.1-1.rpm

July 5th, 2007

RAC Cheatsheet

EDOCPRD1 > cluvfy -h

USAGE:

cluvfy [ -help ]

cluvfy stage { -list | -help }

cluvfy stage {-pre|-post} <stage-name> <stage-specific options> [-verbose]

cluvfy comp { -list | -help }

cluvfy comp <component-name> <component-specific options> [-verbose]

 

EDOCPRD1 > oifcfg -help

 

Name:

oifcfg – Oracle Interface Configuration Tool.

 

Usage: oifcfg iflist [-p [-n]]

oifcfg setif {-node <nodename> | -global} {<if_name>/<subnet>:<if_type>}…

oifcfg getif [-node <nodename> | -global] [ -if <if_name>[/<subnet>] [-type <if_type>] ]

oifcfg delif [-node <nodename> | -global] [<if_name>[/<subnet>]]

oifcfg [-help]

 

<nodename> – name of the host, as known to a communications network

<if_name> – name by which the interface is configured in the system

<subnet> – subnet address of the interface

<if_type> – type of the interface { cluster_interconnect | public | storage }

 

EDOCPRD1 > ocrconfig -help

Name:

ocrconfig – Configuration tool for Oracle Cluster Registry.

Synopsis:

ocrconfig [option]

option:

-export <filename> [-s online]

– Export cluster register contents to a file

-import <filename> – Import cluster registry contents from a file

-upgrade [<user> [<group>]]

– Upgrade cluster registry from previous version

-downgrade [-version <version string>]

– Downgrade cluster registry to the specified version

-backuploc <dirname> – Configure periodic backup location

-showbackup – Show backup information

-restore <filename> – Restore from physical backup

-replace ocr|ocrmirror [<filename>] – Add/replace/remove a OCR device/file

-overwrite – Overwrite OCR configuration on disk

-repair ocr|ocrmirror <filename> – Repair local OCR configuration

-help – Print out this help information

 

 

EDOCPRD1 > crs_stat -h

Usage: crs_stat [resource_name [...]] [-v] [-l] [-q] [-c cluster_member]

crs_stat [resource_name [...]] -t [-v] [-q] [-c cluster_member]

crs_stat -p [resource_name [...]] [-q]

crs_stat [-a] application -g

crs_stat [-a] application -r [-c cluster_member]

crs_stat -f [resource_name [...]] [-q] [-c cluster_member]

crs_stat -ls [resource_name [...]] [-q]

 

crs_register –u resname

crs_profile

crs_relocate

crs_start

crs_stop

crs_unregister

 

NGSDVA > clscfg -h

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

clscfg — Oracle cluster configuration tool

 

This tool is typically invoked as part of the Oracle Cluster Ready

Services install process. It configures cluster topology and other

settings. Use -help for information on any of these modes.

Use one of the following modes of operation.

-install – creates a new configuration

-upgrade – upgrades an existing configuration

-downgrade – downgrades an existing configuration

-add – adds a node to the configuration

-delete – deletes a node from the configuration

-local – creates a special single-node configuration for ASM

-concepts – brief listing of terminology used in the other modes

 

-trace – may be used in conjunction with any mode above for tracing

WARNING: Using this tool may corrupt your cluster configuration. Do not

use unless you positively know what you are doing.

 

 

 

 

 

 

 

 

 

 

NGSDVA > crsctl

Usage: crsctl check crs – checks the viability of the CRS stack

crsctl check cssd – checks the viability of CSS

crsctl check crsd – checks the viability of CRS

crsctl check evmd – checks the viability of EVM

crsctl set css <parameter> <value> – sets a parameter override

crsctl get css <parameter> – gets the value of a CSS parameter

crsctl unset css <parameter> – sets CSS parameter to its default

crsctl query css votedisk – lists the voting disks used by CSS

crsctl add css votedisk <path> – adds a new voting disk

crsctl delete css votedisk <path> – removes a voting disk

crsctl enable crs – enables startup for all CRS daemons

crsctl disable crs – disables startup for all CRS daemons

crsctl start crs – starts all CRS daemons.

crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.

crsctl start resources – starts CRS resources.

crsctl stop resources – stops CRS resources.

crsctl debug statedump evm – dumps state info for evm objects

crsctl debug statedump crs – dumps state info for crs objects

crsctl debug statedump css – dumps state info for css objects

crsctl debug log css [module:level]{,module:level} …

– Turns on debugging for CSS

crsctl debug trace css – dumps CSS in-memory tracing cache

crsctl debug log crs [module:level]{,module:level} …

– Turns on debugging for CRS

crsctl debug trace crs – dumps CRS in-memory tracing cache

crsctl debug log evm [module:level]{,module:level} …

– Turns on debugging for EVM

crsctl debug trace evm – dumps EVM in-memory tracing cache

crsctl debug log res <resname:level> turns on debugging for resources

crsctl query crs softwareversion [<nodename>] – lists the version of CRS software installed

crsctl query crs activeversion – lists the CRS software operating version

crsctl lsmodules css – lists the CSS modules that can be used for debugging

crsctl lsmodules crs – lists the CRS modules that can be used for debugging

crsctl lsmodules evm – lists the EVM modules that can be used for debugging

 

If necesary any of these commands can be run with additional tracing by

adding a “trace” argument at the very front.

Example: crsctl trace check css

 

 

 

 

 

 

Clusterware check: log files in $ORA_CRS_HOME/nodename

Crsctl check crs

Crsctl check cssd

Crsctl check crsd

Crsctl check evmd

Crsctl query crs softwareversion

Crsctl query crs softwareversion node2

Crsctl start crs

Crsctl stop crs

Crsctl debug log res “resname:level”

 

Interconnect check:

Oifcfg getif

olsnodes

disable auto reboot of aix nodes /etc/init*

EDOCPRD1 > ls -lrt /etc/init*

lrwxrwxrwx 1 root system 14 Feb 20 11:18 /etc/init -> /usr/sbin/init

-rw-r–r– 1 root system 2914 Feb 22 16:19 /etc/inittab.orig

-r-xr-xr-x 1 root system 3194 Feb 22 16:19 /etc/init.evmd

-r-xr-xr-x 1 root system 36807 Feb 22 16:19 /etc/init.cssd

-r-xr-xr-x 1 root system 4854 Feb 22 16:19 /etc/init.crsd

-r-xr-xr-x 1 root system 2226 Feb 22 16:19 /etc/init.crs

-rw-r–r– 1 root system 3093 Feb 22 21:58 /etc/inittab

 

OCR check:

Ocrcheck

Ocrconfig –export /tmp/dba/exp_ocr.dmp –s online

Ocrconfig –showbackup

ocrdump

 

VIP:

Ifconfig –a

Ifconfig en8 delete host1-vip

Ifconfig en8 delete host2-vip

 

 

Command = /apps/oracle/product/10.2.0/CRS/bin/racgons add_config ictcdb621:6200 ictcdb622:6200

 

 

Command = /apps/oracle/product/10.2.0/CRS/bin/oifcfg setif -global en8/10.249.199.0:public en9/172.16.32.0:cluster_interconnect

 

 

EDOCPRD1 > srvctl -h

Usage: srvctl [-V]

Usage: srvctl add database -d <name> -o <oracle_home> [-m <domain_name>] [-p <spfile>] [-A <name|ip>/netmask] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s <start_options>] [-n <db_name>] [-y {AUTOMATIC | MANUAL}]

Usage: srvctl add instance -d <name> -i <inst_name> -n <node_name>

Usage: srvctl add service -d <name> -s <service_name> -r “<preferred_list>” [-a "<available_list>"] [-P <TAF_policy>]

Usage: srvctl add service -d <name> -s <service_name> -u {-r “<new_pref_inst>” | -a “<new_avail_inst>”}

Usage: srvctl add nodeapps -n <node_name> -o <oracle_home> -A <name|ip>/netmask[/if1[|if2|...]]

Usage: srvctl add asm -n <node_name> -i <asm_inst_name> -o <oracle_home> [-p <spfile>]

Usage: srvctl config database

Usage: srvctl config database -d <name> [-a] [-t]

Usage: srvctl config service -d <name> [-s <service_name>] [-a] [-S <level>]

Usage: srvctl config nodeapps -n <node_name> [-a] [-g] [-o] [-s] [-l]

Usage: srvctl config asm -n <node_name>

Usage: srvctl config listener -n <node_name>

Usage: srvctl disable database -d <name>

Usage: srvctl disable instance -d <name> -i “<inst_name_list>”

Usage: srvctl disable service -d <name> -s “<service_name_list>” [-i <inst_name>]

Usage: srvctl disable asm -n <node_name> [-i <inst_name>]

Usage: srvctl enable database -d <name>

Usage: srvctl enable instance -d <name> -i “<inst_name_list>”

Usage: srvctl enable service -d <name> -s “<service_name_list>” [-i <inst_name>]

Usage: srvctl enable asm -n <node_name> [-i <inst_name>]

Usage: srvctl getenv database -d <name> [-t "<name_list>"]

Usage: srvctl getenv instance -d <name> -i <inst_name> [-t "<name_list>"]

Usage: srvctl getenv service -d <name> -s <service_name> [-t "<name_list>"]

Usage: srvctl getenv nodeapps -n <node_name> [-t "<name_list>"]

Usage: srvctl modify database -d <name> [-n <db_name] [-o <ohome>] [-m <domain>] [-p <spfile>] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY}] [-s <start_options>] [-y {AUTOMATIC | MANUAL}]

Usage: srvctl modify instance -d <name> -i <inst_name> -n <node_name>

Usage: srvctl modify instance -d <name> -i <inst_name> {-s <asm_inst_name> | -r}

Usage: srvctl modify service -d <name> -s <service_name> -i <old_inst_name> -t <new_inst_name> [-f]

Usage: srvctl modify service -d <name> -s <service_name> -i <avail_inst_name> -r [-f]

Usage: srvctl modify service -d <name> -s <service_name> -n -i <prefered_inst> [-a <available_list>] [-f]

Usage: srvctl modify asm -n <node_name> -i <asm_inst_name> -p <spfile>

Usage: srvctl relocate service -d <name> -s <service_name> -i <old_inst_name> -t <new_inst_name> [-f]

Usage: srvctl remove database -d <name> [-f]

Usage: srvctl remove instance -d <name> -i <inst_name> [-f]

Usage: srvctl remove service -d <name> -s <service_name> [-i <inst_name>] [-f]

Usage: srvctl remove nodeapps -n “<node_name_list>” [-f]

Usage: srvctl remove asm -n <node_name> [-i <asm_inst_name>] [-f]

Usage: srvctl setenv database -d <name> {-t <name>=<val>[,<name>=<val>,...] | -T <name>=<val>}

Usage: srvctl setenv instance -d <name> [-i <inst_name>] {-t “<name>=<val>[,<name>=<val>,...]” | -T “<name>=<val>”}

Usage: srvctl setenv service -d <name> [-s <service_name>] {-t “<name>=<val>[,<name>=<val>,...]” | -T “<name>=<val>”}

Usage: srvctl setenv nodeapps -n <node_name> {-t “<name>=<val>[,<name>=<val>,...]” | -T “<name>=<val>”}

Usage: srvctl start database -d <name> [-o <start_options>] [-c <connect_str> | -q]

Usage: srvctl start instance -d <name> -i “<inst_name_list>” [-o <start_options>] [-c <connect_str> | -q]

Usage: srvctl start service -d <name> [-s "<service_name_list>" [-i <inst_name>]] [-o <start_options>] [-c <connect_str> | -q]

Usage: srvctl start nodeapps -n <node_name>

Usage: srvctl start asm -n <node_name> [-i <asm_inst_name>] [-o <start_options>] [-c <connect_str> | -q]

Usage: srvctl start listener -n <node_name> [-l <lsnr_name_list>]

Usage: srvctl status database -d <name> [-f] [-v] [-S <level>]

Usage: srvctl status instance -d <name> -i “<inst_name_list>” [-f] [-v] [-S <level>]

Usage: srvctl status service -d <name> [-s "<service_name_list>"] [-f] [-v] [-S <level>]

Usage: srvctl status nodeapps -n <node_name>

Usage: srvctl status asm -n <node_name>

Usage: srvctl stop database -d <name> [-o <stop_options>] [-c <connect_str> | -q]

Usage: srvctl stop instance -d <name> -i “<inst_name_list>” [-o <stop_options>] [-c <connect_str> | -q]

Usage: srvctl stop service -d <name> [-s "<service_name_list>" [-i <inst_name>]] [-c <connect_str> | -q] [-f]

Usage: srvctl stop nodeapps -n <node_name>

Usage: srvctl stop asm -n <node_name> [-i <asm_inst_name>] [-o <stop_options>] [-c <connect_str> | -q]

Usage: srvctl stop listener -n <node_name> [-l <lsnr_name_list>]

Usage: srvctl unsetenv database -d <name> -t “<name_list>”

Usage: srvctl unsetenv instance -d <name> [-i <inst_name>] -t “<name_list>”

Usage: srvctl unsetenv service -d <name> [-s <service_name>] -t “<name_list>”

Usage: srvctl unsetenv nodeapps -n <node_name> -t “<name_list>”

 

 

 

 

 

July 5th, 2007

Enterprise Manager AutoDiscovery Process

AutoDiscovery Process

Go to Targets –> Databases. Click on the Add button.

When you click on the Add button, you can pick the host that you want to do auto-discovery on:

After you pick the host you want using the flashlight icon, click Continue and it will start the auto-discovery process:

After this, you’ll get a screen that allows you to pick the target you want (in this case a DB target) – just select any or all, and then click on the configure icon to configure the target. – That’s it.

July 4th, 2007

Password SHA-1 Hash Algorithm

Passwords hashed using the Secure Hash Algorithm (SHA) cryptographic hash function SHA-1. Oracle Database uses the SHA-1 verifier is to authenticate the user password and establish the session of the user. In addition, it enforces case sensitivity and restricts passwords to 160 bits. The advantage of using the SHA-1 verifier is that it is commonly used by Oracle Database customers and provides much better security without forcing a network upgrade. It also adheres to compliance regulations that mandate the use of strong passwords being protected by a suitably strong password hashing algorithm

|