Monday, May 31, 2010

Oracle RAC Background processes

RAC Background Processes:

1. Lock Monitor Processes ( LMON)
2. Lock Monitor Services (LMS)
3. Lock Monitor Daemon Process ( LMD)
4. LCKn ( Lock Process)
5. DIAG (Diagnostic Daemon)

1. Lock Monitor Processes ( LMON)

It Maintains GCS memory structures.
Handles the abnormal termination of processes and instances.
Reconfiguration of locks & resources when an instance joins or leaves the cluster are handled by LMON ( During reconfiguration LMON generate the trace files)
It responsible for executing dynamic lock remastering every 10 mins ( Only in 10g R2 & later versions).
LMON Processes manages the global locks & resources.
It monitors all instances in cluster, primary for dictionary cache locks,library cache locks & deadlocks on deadlock sensitive on enqueue & resources.
LMON also provides cluster group services.
Also called Global enqueue service monitor.

2. Lock Monitor Services (LMS)

LMS is most very active background processes.
Consuming significant amount of CPU time. ( 10g R2 - ensure that LMS process does not encounter the CPU starvation).
Its primary job is to transport blocks across the nodes for cache-fusion requests.
If there is a consistent-read request, the LMS process rolls back the block, makes a Consistent-Read image of the block and then ship this block across the HSI (High Speed Interconnect) to the process requesting from a remote node.
LMS must also check constantly with the LMD background process (or our GES process) to get the lock requests placed by the LMD process.
Each node have 2 or more LMS processes.

GCS_SERVER_PROCESSES --> no of LMS processes specified in init. ora parameter.
Above parameter value set based on number of cpu's ( MIN(CPU_COUNT/2,2))
10gR2, single CPU instance,only one LMS processes started.
Increasing the parameter value,if global cache activity is very high.
Also called the GCS (Global Cache Services) processes.

Internal View: X$KJMSDP

3. Lock Monitor Daemon Process ( LMDn)

LMD process performs global lock deadlock detection.
Also monitors for lock conversion timeouts.
Also sometimes referred to as the GES (Global Enqueue Service) daemon since its job is to manage the global enqueue and global resource access.
LMD process also handles deadlock detection and remote enqueue requests.
Remote resource requests are the requests originating from another instance.
Internal View: X$KJMDDP
4. LCKn ( Lock Process)
Manages instance resource requests & cross instance calls for shared resources.
During instance recovery,it builds a list of invalid lock elements and validates lock elements.
5. DIAG (Diagnostic Daemon)
Oracle 10g - this one new background processes ( New enhanced diagnosability framework).
Regularly monitors the health of the instance.
Also checks instance hangs & deadlocks.
It captures the vital diagnostics data for instance & process failures.

Saturday, May 29, 2010

Day2Day UNIX commands

Basic File Navigation

The "pwd" command displays the current directory:

root> pwd
/u01/app/oracle/product/ "ls" command lists all files and directories in the specified directory. If no location is defined it acts on the current directory:

root> ls
root> ls /u01
root> ls -alThe "-a" flag lists hidden "." files. The "-l" flag lists file details.

The "cd" command is used to change directories:
root> cd /u01/app/oracleThe "touch" command is used to create a new empty file with the default permissions:

root> touch my.logThe "rm" command is used to delete files and directories:

root> rm my.log
root> rm -R /archiveThe "-R" flag tells the command to recurse through subdirectories.

The "mv" command is used to move or rename files and directories:

root> mv [from] [to]
root> mv my.log my1.log
root> mv * /archive
root> mv /archive/* .The "." represents the current directory

The "cp" command is used to copy files and directories:

root> cp [from] [to]
root> cp my.log my1.log
root> cp * /archive
root> cp /archive/* .The "mkdir" command is used to create new directories:

root> mkdir archiveThe "rmdir" command is used to delete directories:
root> rmdir archiveThe "find" command can be used to find the location of specific files:
root> find / -name dbmspool.sql
root> find / -print grep -i dbmspool.sqlThe "/" flag represents the staring directory for the search. Wildcards such as "dbms*" can be used for the filename.

The "which" command can be used to find the location of an executable you are using:
oracle> which sqlplusThe "which" command searches your PATH setting for occurences of the specified executable.
File Permissions
The "umask" command can be used to read or set default file permissions for the current user:

root> umask 022The umask value is subtracted from the default permissions (666) to give the final permission:

666 : Default permission
022 : - umask value
644 : final permissionThe "chmod" command is used to alter file permissions after the file has been created:

root> chmod 777 *.log

Owner Group World Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+wx) 6 (g+wx) 6 (o+wx) write + execute
5 (u+Rx) 5 (g+Rx) 5 (o+Rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute onlyCharacter eqivalents can be used in the chmod command:

root> chmod o+rwx *.log
root> chmod g+r *.log
root> chmod -Rx *.logThe "chown" command is used to reset the ownership of files after creation:

root> chown -R oinstall.dba *The "-R" flag causes the command ro recurse through any subdirectories.
OS Users Management
The "useradd" command is used to add OS users:

root> useradd -G oinstall -g dba -d /usr/users/my_user -m -s /bin/ksh my_user•The "-G" flag specifies the primary group.
•The "-g" flag specifies the secondary group.
•The "-d" flag specifies the default directory.
•The "-m" flag creates the default directory.
•The "-s" flag specifies the default shell.
The "usermod" command is used to modify the user settings after a user has been created:

root> usermod -s /bin/csh my_userThe "userdel" command is used to delete existing users:

root> userdel -r my_userThe "-r" flag removes the default directory.

The "passwd" command is used to set, or reset, the users login password:

root> passwd my_userThe "who" command can be used to list all users who have OS connections:

root> who
root> who head -5
root> who tail -5
root> who grep -i ora
root> who wc -l•The "head -5" command restricts the output to the first 5 lines of the who command.
•The "tail -5" command restricts the output to the last 5 lines of the who command.
•The "grep -i ora" command restricts the output to lines containing "ora".
•The "wc -l" command returns the number of lines from "who", and hence the number of connected users.
Process Management
The "ps" command lists current process information:

root> ps
root> ps -ef grep -i oraSpecific processes can be killed by specifying the process id in the kill command:

root> kill -9 12345uname and hostname
The "uname" and "hostname" commands can be used to get information about the host:

root> uname -a
OSF1 V5.1 2650 alpha

root> uname -a awk '{ print $2 }'

root> hostname Lines in Files
You can return the error lines in a file using:

root> cat alert_LIN1.log grep -i ORA-The "grep -i ORA-" command limits the output to lines containing "ORA-". The "-i" flag makes the comparison case insensitive. A count of the error lines can be returned using the "wc" command. This normally give a word count, but the "-l" flag alteres it to give a line count:

root> cat alert_LIN1.log grep -i ORA- wc -lFile Exists Check
The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:

if test -s /backup/daily_backup.log
DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
mv /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log
fiRemove Old Files
The find command can be used to supply a list of files to the rm command:

find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {} ;Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:

sed -e 's/^M$//' filename > tempfileThe newly created tempfile should have the ^M character removed.
Run Commands As Oracle User From Root
The following scripts shows how a number of commands can be run as the "oracle" user the "root" user:
su - oracle <
•Login as root
•crontab -l > newcron
•Edit newcron file.
•crontab newcron
Alternatively you can use the "crontab -e" option to edit the crontab file directly.

The entries have the following elements:

field allowed values
----- --------------
minute 0-59
hour 0-23
day of month 1-31
month 1-12
day of week 0-7 (both 0 and 7 are Sunday)
user Valid OS user
command Valid command or script.The first 5 fields can be specified using the following rules:

* - All available values or "first-last".
3-4 - A single range representing each possible from the start to the end of the range inclusive.
1,2,5,6 - A specific list of values.
1-3,5-8 - A specific list of ranges.
0-23/2 - Every other value in the specified range.The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the script are piped to /dev/null to prevent a buildup of mails to root:

0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1Cluster Wide CRON Jobs On Tru64
On clustered systems cron is node-specific. If you need a job to fire once per cluster, rather than once per node you need an alternative approach to the standard cron job. One approach is put forward in the HP best practices document (Using cron in a TruCluster Server Cluster), but in my opinion a more elegant solution is proposed by Jason Orendorf of HP Tru64 Unix Enterprise Team (TruCluster Clustercron).

In his solution Jason creates a file called /bin/cronrun with the following contents:

set -- $(/usr/sbin/cfsmgr -F raw /)
shift 12
[[ "$1" = "$(/bin/hostname -s)" ]] && exit 0
exit 1This script returns TRUE (0) only on the node which is the CFS serving cluster_root.

All cluster wide jobs should have a crontab entry on each node of the cluster like:

5 * * * /bin/cronrun && /usr/local/bin/myjobAlthough the cron jobs fire on all nodes, the "/bin/cronrun &&" part of the entry prevents the script from running on all nodes except the current CFS serving cluster_root.
NFS Mount (Sun)
The following deamons must be running for the share to be seen by a PC:

•/usr/lib/nfs/nfsd -a
To see a list of the nfs mounted drives already present type:

exportfsFirst the mount point must be shared so it can be seen by remote machines:

share -F nfs -o ro /cdromNext the share can be mounted on a remote machine by root using:

mkdir /cdrom#1
mount -o ro myhost:/cdrom /cdrom#1NFS Mount (Tru64)
On the server machine:

If NFS is not currently setup do the following:

•Application Manager -> System Admin -> Configuration -> NFS
•Select the "Configure system as an NFS server" option.
•Accept all defaults.
Create mount point directory:

mkdir /u04/backupAppend the following entry to the "/etc/exports" file:

/u04/backupMake sure the correct permissions are granted on the directory:

chmod -R 777 /u04/backupOn the client machine:

If NFS is not currently setup do the following:

•Application Manager -> System Admin -> Configuration -> NFS
•Select the "Configure system as an NFS client" option.
•Accept all defaults.
Create mount point directory:

mkdir /backupAppend an following entry to the "/etc/fstab" file:

nfs-server-name:/u04/backup /backup nfs rw,bg,intr 0 0Finally, mount the fileset:

mount /backupAt this point you can start to use the mount point from your client machine. Thanks to Bryan Mills for his help with Tru64.
PC XStation Configuration
Download the CygWin setup.exe from

Install, making sure to select all the X11R6 (or XFree86 in older versions) optional packages.

If you need root access add the following entry into the /etc/securettys file on each server:

:0From the command promot on the PC do the following:

set PATH=PATH;c:cygwinbin;c:cygwinusrX11R6bin
XWin.exe :0 -query The X environment should start in a new window.

Many Linux distributions do not start XDMCP by default. To allow XDMCP access from Cygwin edit the "/etc/X11/gdm/gdm.conf" file. Under the "[xdmcp]" section set "Enable=true".

If you are starting any X applications during the session you will need to set the DISPLAY environment variable. Remember, you are acting as an XStation, not the server itself, so this variable must be set as follows:

DISPLAY=:0.0; export DISPLAYUseful Profile Settings
The following .profile settings rely on the default shell for the user being set to the Korn shell (/bin/ksh).

The backspace key can be configured by adding the following entry:

stty erase "^H"The command line history can be accessed using the [Esc][k] by adding the following entry:

set -o viAuto completion of paths using a double strike of the [Esc] key can be configured by adding the following entry:

set filecUseful Files
Here are some files that may be of use:

Path Contents
/etc/passwd User settings
/etc/group Group settings for users.
/etc/hosts Hostname lookup information.
/etc/system Kernel parameters for Solaris.
/etc/sysconfigtab Kernel parameters for Tru64.

Monitoring oracle long running sessions

To monitor long running operations in oracle the following two conditions must met.
1) Set the initialization parameter TIMED_STATISTICS or SQL_TRACE parameters to true.
2) Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package.

After you have met above two conditions you can easily monitor your long running operations by querying V$SESSION_LONGOPS view.

Name Null? Type
----------------------------------------- -------- ----------------------------
From above view,
- SOFAR is units of work done so far.
- TIME_REMAINING is estimate (in seconds) of time remaining for the operation to complete

In order to monitor how much percentage of an operation is completed query as,

SQL> SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops;

If you have long running operations in your database then above query will return rows.

You can query long running operations based on they have started like,

SQL> select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops order by start_time desc;