How to add a device in vmware

+++ 1. How to add a device and file-system
 ------------------------------------------
1. via virtual-machine option. add a disk of size 8G
2. partition the disk via fdisk : /dev/sdb1
3. create a file-system via mkfs -t ext3 /dev/sdb1
4. blkid /dev/sdb1 will give you uuid
5. create an empty directory on / as a mount point
6. modify /etc/fstab to add the mount-point line ( copy and modify an earlier line )
7. mount -all
8. check whether it is mounted : mount
9. create some directories and change their ownership and group : chown oracle:dba oracle

Index ------ 1. Optimizer trace parameters. 2. database events and tkprof 3. Statistics in case of cell offload 4. testcase in case you need RAC on one node 5. glogin.sql +++1. Optimizer trace parameters. ================================= Legend The following abbreviations are used by optimizer trace. CBQT - cost-based query transformation JPPD - join predicate push-down OJPPD - old-style (non-cost-based) JPPD FPD - filter push-down PM - predicate move-around CVM - complex view merging SPJ - select-project-join SJC - set join conversion SU - subquery unnesting OBYE - order by elimination OST - old style star transformation ST - new (cbqt) star transformation CNT - count(col) to count(*) transformation JE - Join Elimination JF - join factorization SLP - select list pruning DP - distinct placement qb - query block LB - leaf blocks DK - distinct keys LB/K - average number of leaf blocks per key DB/K - average number of data blocks per key CLUF - clustering factor NDV - number of distinct values Resp - response cost Card - cardinality Resc - resource cost NL - nested loops (join) SM - sort merge (join) HA - hash (join) CPUSPEED - CPU Speed IOTFRSPEED - I/O transfer speed IOSEEKTIM - I/O seek time SREADTIM - average single block read time MREADTIM - average multiblock read time MBRC - average multiblock read count MAXTHR - maximum I/O system throughput SLAVETHR - average slave I/O throughput dmeth - distribution method 1: no partitioning required 2: value partitioned 4: right is random (round-robin) 128: left is random (round-robin) 8: broadcast right and partition left 16: broadcast left and partition right 32: partition left using partitioning of right 64: partition right using partitioning of left 256: run the join in serial 0: invalid distribution method sel - selectivity ptn - partition -- getting runtime statistics: @ alter session set statistics_level=all; @ @ select * from table (dbms_xplan.display_cursor(,0,'runstats_last')); @ . display_cursor with run time stats +100053 for both cases: hint no hint -- OPT_PARAM('_optimizer_multi_level_push_pred' 'true') -- +++2. Database Events and tkprof ================================ General Information Note: Use "dbms_monitor.session_trace_enable()" instead of "alter session set events ..." and you will see the columns sys.gv_$session updated TKPROF stands for Transient Kernel Profiler Event Numbering Range 10000 - 10999 Event Definitions Event # Level Description 8186 - ALTER SESSION SET EVENTS '8186 trace name context OFF' - VERSIONS BETWEEN query 10013 1 Monitor Transaction Recovery 10015 1 Dump Undo Segment Headers 10015 3 Identify corrupt undo segments 10032 4 Dump Sort Statistics 10031 Sort Debug Events 10032 10 Dump Sort Statistics 10033 4 Dump Sort Large Run Statistics (blocks dumped to disk and read back) - poss. no levels. 10033 10 Dump Sort Intermediate Run Statistics 10045 1 Trace Free List Management Operations 10046 0 No statistics generated 10046 1 Standard trace output including parsing, executes and fetches plus more 10046 2 Same as Level 1 10046 4 Level 1 + Bind Variables 10046 8 Level 1 + Waits 10046 12 Level 1 + Bind Variables & Waits 10046 16 Added in 11g to generate STAT line dumps for each execution. STAT dumping has been amended in 11g so that they are not aggregated across all executions but are dumped after execution. 10053 1 Dump Optimizer Statistics and Computations 10053 2 Dump Optimizer Computations only 10060 1 Dump Predicates 10065 1 Restrict Library Cache Output for State Object Dumps - Library Objects Only 10065 2 Restrict Library Cache Output for State Object Dumps + library object lock details 10065 3 Restrict Library Cache Output for State Object Dumps + library object handle and library object 10079 2 Dump SQL*Net Statistics 10081 1 Dump High Water Mark Changes 10104 10 Dump Hash Join Statistics 10128 1 Dump Partition Pruning Information 10128 2 Dump partition iterators 10128 4 Dump optimizer decisions about partition-wise joins 10128 8 Dump ROWID range scan pruning information 10132 12 Dumps SQL statements into UDUMP along with the actual execution plan 10200 1 Dump Consistent Reads 10201 1 Dump Consistent Read Undo Application 10210 10 Table Integrity Check 10211 10 Index Integrity Check 10220 1 Dump Changes to Undo Header 10221 7 Dump Undo Changes 10224 1 Dump Index Block Splits / Deletes 10225 1 Dump Changes to Dictionary Managed Extents 10231 10 Causes full table scans to skip corrupt blocks, rather than fail with an ORA-1578 error 10232 10 Causes corrupt blocks to be dumped to the process trace file 10233 10 Traces memory usage statistics 10235 1 Check memory manager internal structures (use with ORA-600/ORA-7445 errors) 10241 1 Dump Remote SQL Execution 10246 1 Trace PMON Process 10248 10 Trace Dispatcher Processes 10249 10 Trace Shared Server (MTS) Processes 10270 10 Debug Shared Cursors 10289 1 Dump a database block in hexadecimal 10299 1 Debug Prefetch 10357 1 Debug Direct Path 10359 1 Stops writes to the control file during NOLOGGING operations: May substantially improve performance 10390 0x0001 Dump Parallel Execution Slave Statistics - Slave-side execution messages 10390 0x0002 Dump Parallel Execution Slave Statistics - Coordinator-side execution messages 10390 0x0004 Dump Parallel Execution Slave Statistics - Slave context state changes 10390 0x0008 Dump Parallel Execution Slave Statistics - Slave ROWID range bind variables and xty 10390 0x0010 Dump Parallel Execution Slave Statistics - Slave fetched rows as enqueued to TQ 10390 0x0020 Dump Parallel Execution Slave Statistics - Coordinator wait reply handling 10390 0x0040 Dump Parallel Execution Slave Statistics - Coordinator wait message buffering 10390 0x0080 Dump Parallel Execution Slave Statistics - Slave dump timing 10390 0x0100 Dump Parallel Execution Slave Statistics - Coordinator dump timing 10390 0x0200 Dump Parallel Execution Slave Statistics - Slave dump allocation file number 10390 0x0400 Dump Parallel Execution Slave Statistics - Terse format for debug dumps 10390 0x0800 Dump Parallel Execution Slave Statistics - Trace CRI random sampling 10390 0x1000 Dump Parallel Execution Slave Statistics - Trace signals 10390 0x2000 Dump Parallel Execution Slave Statistics - Trace parallel execution granule operations 10390 0x4000 Dump Parallel Execution Slave Statistics - Force compilation by slave 0 10391 0x0001 Dump Parallel Execution Granule Allocation - Summary of each object scanned in parallel 10391 0x0002 Dump Parallel Execution Granule Allocation - Full dump of each object except extent map 10391 0x0004 Dump Parallel Execution Granule Allocation - Full dump of each object including extent map 10391 0x0010 Dump Parallel Execution Granule Allocation - Summary of each granule generators 10391 0x0020 Dump Parallel Execution Granule Allocation - Granule generators except granule instances 10391 0x0040 Dump Parallel Execution Granule Allocation - Granule generators including granule instances 10391 0x0080 Dump Parallel Execution Granule Allocation - System information 10391 0x0100 Dump Parallel Execution Granule Allocation - Reference object for the query 10391 0x0200 Dump Parallel Execution Granule Allocation - kxfralo timing 10391 0x0400 Dump Parallel Execution Granule Allocation - Affinity module 10391 0x0800 Dump Parallel Execution Granule Allocation - Granule allocation during query execution 10391 0x1000 Dump Parallel Execution Granule Allocation - Object flush 10393 1 Dump Parallel Execution Statistics 10401 1 KSXP tracing - event 10401 trace name context forever, level 1 (for RAC kernel) 10402 1 SKGXP tracing - event 10402 trace name context forever, level 1 (for RAC kernel) minimal tracing 10402 127 SKGXP tracing - event 10402 trace name context forever, level 1 (for RAC kernel) maximum tracing 10500 1 Trace SMON Process 10608 10 Trace Bitmap Index Creation 10704 1 Trace Enqueues 10706 1 Trace Global Enqueue Manipulation 10708 10 Trace RAC Buffer Cache 10710 1 Trace Bitmap Index Access 10711 1 Trace Bitmap Index Merge Operation 10712 1 Trace Bitmap Index OR Operation 10713 1 Trace Bitmap Index AND Operation 10714 1 Trace Bitmap Index MINUS Operation 10715 1 Trace Bitmap Index Conversion to ROWIDs 10716 1 Trace Bitmap Index Compress / Decompress 10717 1 Trace Bitmap Index Compaction 10719 1 10719 - Trace Bitmap Index DML 10730 1 Trace Fine Grained Access Predicates 10731 1 Trace CURSOR Statements 10731 2 Trace CURSOR Statements 10851 2 Disables error 24005 when attempting to manually drop a queue table 10852 Enable dumping of the AQ statistics hash table 10853 Event for AQ statistics latch cleanup testing 10856 Disable AQ propagator from using streaming 10857 Force AQ propagator to use two-phase commit 10858 Crash the AQ propagator at different stages of commit 10859 Disable updates of message retry count 10860 Event for AQ admin disable new name parser 10861 Disable storing extended message properties 10862 1 Resolve default queue owner to current user in enqueue/dequeue 10901 Disable extent trimming 10928 1 Trace PL/SQL Execution 10938 1 Trace PL/SQL Execution Statistics 29700 Enable the collection of DLM lock conversion statistics to query GV$DLM_CONVERT_LOCAL and GV$DLM_CONVERT_REMOTE 44410 0 xmlagg() with a GROUP BY can fail with ORA-22813 if the result is too large. No statistics 44410 1 Basic statistics CURSOR, PARSE, EXEC, FETCH ERROR, SORT UMAP, ERROR, UMAP, STATS and XCTEND. Same as setting sql_trace=true. 44410 2 Same as level 1 44410 4 Level 1 plus binds 44410 8 Level 1 plus wait events 44410 12 Combine levels 4 and 8 ShowDoc 10060 Event 10128 Event CREATE TABLE kkoipt_table ( c1 INTEGER, c2 VARCHAR2(80); CREATE TABLE kkpap_pruning ( partition_count NUMBER, iterator VARCHAR2(32), partition_level VARCHAR2(32), order_pt VARCHAR2(12), call_time VARCHAR2(12), part# NUMBER, subp# NUMBER, abs# NUMBER); Trace File Locations $ORACLE_BASE/admin/sid_name/ bdump ... alert logs and system generated trace files cdump ... core dumps udump ... user generated trace files Grant public access to all views used by TKPROF with verbose=y option. $ORACLE_HOME/rdbms/admin/utltkprf.sql Add timed statistics to trace file conn / as sysdba set linesize 121 col name format a40 col value format a30 SELECT name, value FROM gv$parameter WHERE name LIKE 'timed%'; ALTER SYSTEM SET timed_statistics=TRUE; System Privilege To Enable and Disable Tracing GRANT alter session TO ; GRANT alter session TO uwclass; REVOKE alter session FROM uwclass; PFile Entries vs. Alter Session init.ora Parameter Example event='1401 trace name errorstack, level 12'; ALTER SESSION Commands Start and Stop Session Tracing ALTER SESSION SET sql_trace = ; ALTER SESSION SET sql_trace = TRUE; ALTER SESSION SET sql_trace = FALSE; Event Tracing Examples Active Session History alter session set events 'immediate trace name ashdump level 10'; Control File Dump alter session set events 'immediate trace name CONTROLF level 10'; Error Stack (Exception) Trace alter session set events ' trace name errorstack level 10'; alter session set events '60 trace name errorstack level 10'; File Header Dump alter session set events 'immediate trace name FILE_HDRS level 10'; Library Cache Dump alter session set events 'immediate trace name LIBRARY_CACHE level 10'; Process State Dump alter session set events 'immediate trace name PROCESSSTATE LEVEL 10'; Redo Log Headers Dump alter session set events 'immediate trace name REDOHDR LEVEL 10'; System State Dump alter session set events 'immediate trace name SYSTEMSTATE LEVEL 10'; Tracing Demo Go through a 10053 output to find why Oracle ignores a hint or thinks it is smarter than you. One little trick is to grep for lines starting "Join order" and "Best so far." The join order lists tables by table name, so you can see when an order you expected did not survive (or did not reach) the CBO's processing. The "Best so far" appear only when the CBO has decided that the most recent join order has a plan better than the previous best, so you can quickly check the evolution of the plan. ALTER SESSION SET tracefile_identifier = 'test_plan1'; ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; SELECT srvr_id FROM servers WHERE srvr_id IN ( SELECT srvr_id FROM serv_inst); SELECT srvr_id FROM servers WHERE srvr_id IN ( SELECT i.srvr_id FROM serv_inst i, servers s WHERE i.srvr_id = s.srvr_id); ALTER SESSION SET EVENTS '10053 trace name context OFF'; ALTER SESSION SET EVENTS '10046 trace name context OFF'; or ALTER SESSION SET SQL_TRACE=FALSE; review the trace file in $ORACLE_BASE/diag/orabase/orabase/trace TKPROF Generate TKPROF Output TKPROF [explain= [table=]] [print=] -- list only the first nth SQL statements [aggregate= [insert=] -- list SQL statements and data inside INSERT statements [sys=] -- TKPROF does not list SQL statements run as user SYS [record=] -- record non-recursive statements in the trace file [waits=] -- record summary of for wait events in the trace file [sort=] -- zero or more of the listed sort options Sort Option Description execnt number of execute was called execpu cpu time spent executing execu number of buffers for current read during execute exedsk number of disk reads during execute exeela elapsed time executing exemis number of library cache misses during execute exeqry number of buffers for consistent read during execute exerow number of rows processed during execute fchcnt number of times fetch was called fchcpu cpu time spent fetching fchcu number of buffers for current read during fetch fchdsk number of disk reads during fetch fchela elapsed time fetching fchqry number of buffers for consistent read during fetch fchrow number of rows fetched prscnt number of times parse was called prscpu cpu time parsing prscu number of buffers for current read during parse prsdsk number of disk reads during parse prsela elapsed time parsing prsmis number of misses in library cache during parse prsqry number of buffers for consistent read during parse userid userid of user that parsed the cursor TKPROF c: emp\orabase_ora_1492.trc c: emp race_out.txt PLSHPROF Generate PLSHPROF Output PLSHPROF [] -output [] Note: "output" refers to the output from DBMS_HPROF not the output from PLSHPROF. Options Description Default -trace Specifies function name of tree root N/A -skip Skips first count calls. Use only with -trace symbol 0 -collect Collects information for count calls. Use only with -trace symbol 1 -output Specifies name of output file N/A -summary Prints only elapsed time N/A plshprof -trace -output hprof_trace.html hprof.trc ================================================== +++3. Statistics in case of cell offload ======================================== -- collect v$sysstat set pages 9999 select name, value from v$sysstat where name = 'physical read total bytes' or name = 'physical write total bytes' or name like '%cell%'; -- show offloaded IO percentage select 100*v2.value/v1.value io_offload_percentage from v$sysstat v1, v$sysstat v2 where v1.name = 'physical read total bytes' and v2.name = 'cell physical IO bytes eligible for predicate offload'; -- collect v$cell_state, may take a while set pagesize 10000 set long 500000 column time new_value time select to_char (sysdate, 'YYMMDD.HH24MISS') time from dual; spool cell_state_&time select cell_name, statistics_type, object_name, xmltype(statistics_value) from v$cell_state; spool off ============================================ +++4. test Case in case of RAC : ================================= -- How to create standalone testcase in RAC database. ====================================================== New View : ========== VIEW_NAME : akshukla_a824v2 . 0. bring up the cluster ware : make hcc_on rac_on nm_none oracle 1. clone empty database to create the database. . create undo tablespace ud2 datafile 'ud2.f' size 100M reuse autoextend on; alter database add logfile thread 2 't_log3.f' size 100M, 't_log4.f' size 100M; alter database enable thread 2; -- modify init.ora *.cluster_database=true bug11.instance_number = 1 bug12.instance_number = 2 bug11.thread = 1 bug12.thread = 2 bug11.undo_tablespace = ud1 bug12.undo_tablespace = ud2 -- link init.ora to $ORACLE_HOME/dbs directory. -- expdp and impdp to get the data in. -- EXPDP : Export datapump : 24 min 55 secs : parallel = 8 ========================================================= bash-3.2$ cat expdp.sh # # Minerva_BUG : Schema Name # #SEGMENT_TYPE COUNT(*) GB #------------------ ---------- ---------- #TABLE PARTITION 24 20.223938 #TABLE 22 1.15563965 # # PWD=`pwd` sqlplus "/as sysdba"< 30 and data_type = 'VARCHAR2' group by column_name; spool off @/tmp/colform.sql set head on feedback on verify on pages 20000 lines 120 trimspool on timing on time on termout on

Setting sql*Plus column format

If you use sqlplus to query the dba*/v$ tables to find the state of the database, you often have to format the columns to get a readable output. One has to set pagesize, time on or timing on and so on to get output in readable form. . It is a lot less stressful to set these sql*plus commands are set in sqlplus/admin/glogin.sql. . Following sql will set all columns having size greater than 30 to format 30 so that you can view the results in readable way without having to set column formats again and again.

set head off feedback off verify off pages 0 lines 120 trimspool on timing off termout off spool /tmp/colform.sql select 'col '||column_name||' for a'||case when column_name like '%TYPE' or column_name like '%OWNER' then 12 when column_name like '%TEXT%' then 80 else 30 end ||' word_wrapped' str from dba_tab_columns where data_length > 30 and data_type = 'VARCHAR2' group by column_name; spool off @/tmp/colform.sql set head on feedback on verify on pages 20000 lines 120 trimspool on timing on time on termout on .

All the Oracle dictionary columns and which tables they occur in.

create a function which returns the table_name separated by ":"
--
create or replace function ret_tables(i_column_name in varchar2)
return clob
as
cursor c1
is
select table_name from dict_columns where column_name = i_column_name;
str clob;
begin
str := '';
for c1rec in c1 loop
str := str||c1rec.table_name||' : ';
end loop;
return str;
end;
/
--
set lines 2200 trimspool on
col tab_list for a2000 word_wrapped
select col_name, ret_tables(col_name) tab_list from (select distinct column_name col_name from dict_columns order by column_name);
--

select col_name, ret_tables(col_name) tab_list from (select distinct column_name col_name from dict_columns order by column_name);

Linux Command for Oracle DBA

General Linux Commands

uptime : amount of time system has been up and load average for last 5, 10, 15 minutes.

last : log of system shutdowns

memory :
free -m : note : linux uses available memory for buffer and cache

cat /proc/meminfo

ipcs -m : shared memory segments.

ipcrm -M : deletes shared memory segments.

lsof -p PID : gives details of open files, ports, sharedmem for a process.

System Monitor

OSWatcher :
start : ./startOSW.sh
stop : ./stopOSW.sh

graph the data:
export PATH=$ORACLE_HOME/jdk/bin:$PATH
java -jar oswg.jar -i archive
where archive is archive directory where all the traces went.


top : gives snapshot of the system every 2 seconds: CPU usage, memory and swap usage.
provides load avg for last 1, 5 and 15 minutes.

load_avg should be less than number of CPU on the system.

vmstat : usage of virtual memory.
vmstat 3 10 ( every 3 seconds 10 times == virtual memory status for last 30 seconds )

strace : reports system calls and signals of a process
strace -p 1234 -o 1234.txt

netstat -i : interface statistics

iostat -p sda 30 : read/write stats for /dev/sda at every 30 secs forever.

mpstat 3 10 : Look at iterrupts and cpu usage.

ifconfig eth[n] : provides dropped packets, HW address

netstat -s : summary statistics

netstat -a : lists all established and listening socket related information.
--inet -a option will only give network related information

/etc/services lists ports for well-known services.

ss : socket statistics.

nmon : performance monitoring tool from IBM.

tcpdump : It allows you to look at network traffic.
tcpdump -D : Lists interfaces
tcpdump -i 1 -w tcp.dump.file

tcpdump -r tcp.dump.file

sar -A -o sar.txt 10 30 : Capture all system related info. in sar.txt.
sar -f sar.txt : reads the sar.txt file
sar -u : reports cpu
sar -n : networks statistics
sar -b : IO statistics
(ksar is a popular graphing sar data tool).
.



Software Installation and Machine Info

To find the hostname and OS version:
uname -a
.
To find where a module is installed :
rpm -q _module_name_ { e.g. rpm -q libaio }
rpm -qa | grep _module_name_ { if you dont know the exact name of the module }
.
To install a module :
rpm -ivh _module_name_
for example:
rpm -ivh oracleasm* ( Installs ASMLIB RPM packages).
.
To list loaded device drivers:
lsmod
driver information is also provided in /var/log/messages.
.
To get a file from ftp server:
# wget ftp://ftp.mogumbo.com/pub/dest/of/file
.
TO install via yum ( works for centos/Red Hat, yum == yellow dog Updater, Modified ):
yum list mozilla
yum install mozilla
.

Installation commands:

To create a group:
# groupadd -g 4500 dba
# groupadd -g 4501 oinstall

To add oracle user:
# useradd -g oinstall -G dba -d /home/vkshukla -p passwd vkshukla

To verify id of a user:
# id vkshukla

To change passwd:
# passwd vkshukla

To change system configuration for Oracle
edit /etc/sysctl.conf
# sysctl -p

Important kernel parameters for oracle:
SHMMAX : max. size of one shared mem segment
SHMMNI : max. # of shared mem segments allowed in the system.
SHMSEG : max. # of shared mem segments one process can attach
SEMMNS : max # of semaphores in the system
.
file-max : maximum # of files for the entire system.
.
Network buffersize parameters:
net.core.rmem_default #bytes of the socket receive buffer
net.core.wmem_default #bytes of the socket send buffer
net.core.rmem_max # Maximum socket receive buffer size
net.core.wmem_max # Maximum socket send buffer size
.
Limit of open files per user:
/etc/security/limits.conf
.
To view the limits for the user:
ulimit -a
memlock : maximum amount of memory user can lock in memory.
.
Maximum asynchronous I/O kernel parameter :
/proc/sys/fs/aio-nr
.
To view shared memory or semaphores:
ipcs -m|sb
To remove semaphores:
ipcrm -s
To remove shared memory:
ipcrm -m
.
To create a directory:
# mkdir -p /u01/app/oracle
change ownership
# chown -R root:oinstall /u01
change security ( umast 002 )
# chmod -R 775 /u01
.
To set environment variable on login:
edit ~/.bash_profile where ~ stands for HOME directory (e.g. /home/vkshukla )
for example : PATH=$PATH:$HOME/bin
.
To set alias and function ( this gets executed when you open another Xterm )
edit ~/.bashrc

Important environment variables:

ORACLE_HOME: Oracle software install location
ORACLE_SID: system identifier for instance
ORA_CRS_HOME: clusterware software install location
TNS_ADMIN: network admin location - tnsnames.ora, listener.ora
PATH: must include $ORACLE_HOME/bin and $ORA_CRS_HOME/bin
NLS_LANG: AMERICAN_AMERICA.AL32UTF8
ORA_NLS10: $ORACLE_HOME/nls/data
LD_LIBRARY_PATH : $ORACLE_HOME/lib, $ORA_CRS_HOME/lib
( runtime shared library loader searches for shared libraries. )
CLASSPATH: ( Java loader searches List of directories and class libraries. )
JAVE_HOME, JRE_HOME : needed for jdbc and sqlj
JAVA_HOME: $ORACLE_HOME/jdk
JRE_HOME: $ORACLE_HOME/jre/bin
ORACLE_PATH, SQL_PATH: directory which sql*plus searched for sql scripts.
DISPLAY : servername:screen_number ( X windows display ).
TEMP, TMPDIR: /tmp where installation logs etc go.
.
Example .bash_profile which sets the above env variable:
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
unset USERNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/8.1.7; export ORACLE_HOME
ORACLE_SID=prod1ora; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/8.1.7/bin; export PATH
CLASSPATH=.:/u01/app/oracle/product/8.1.7/jdbc/lib/classes111.zip; export CLASSPATH
LD_LIBRARY_PATH=/u01/app/oracle/product/8.1.7/lib; export LD_LIBRARY_PATH
LD_ASSUME_KERNEL=2.2.5; export LD_ASSUME_KERNEL
umask 022
----End of File------
export ensures that all child shells inherit these environment variable.
.
umask 022 sets all file read-write permission to be 755(rwxr_xr_x).

Monitor and administration commands for DBA:

TO swith user :
su - oracle ( it will prompt for passwd unless you are root ).
.
To mount usbdisk:
mount /media/usbdisk/
.
To create a file with dd command:
dd if=/dev/zero of=/file/name bs=1M count=1024
To list files in reverse order of time :
ls -ltr
.
To list files on a particular date with > 1MB size:
$ ls -tlr *.trc | grep "Apr 22" | awk '{ if ($5 > 1048576) { print } }'
-rw-rw-r-- 1 oracle dba 4231767 Apr 22 19:58 yama_lgwr_8846.trc
$
To see ORA- error and 2 lines above it( to see date and tracefile name ) in alert log:
grep -i -b2 ora- alert_yama.log ( here ORACLE_SID = yama ).
.
To list processes
ps -ef
.
To kill database process to cause instance crash:
ps -ef | grep lgwr | awk '{print $2}' | xargs kill -9
.
To find out OS level messages/info/alerts:
cat /var/log/messages | more
dmesg | more
.
List network interface:
ifconfig -a
.
List message queue:
#ipcs -lq
.
For disks being used by the system:
cat /proc/scsi/scsi or dmesg ( as root )
.
To partition the disk:
parted /dev/sda or use fdisk /dev/sda
.
To list partitions:
fdisk -l /dev/sda
.
To set env on Linux using oraenv
. oraenv ( dot space makes changes with parent shell rather than only in subshell )

Install software in Linux:

( # means root prompt on shell )
# rpm -q software
# rpm -ivh software.rpm
# service software status
# service software start
.
To start/stop/restart/status a daemon ( for example nfsd )
service nfsd start
service nfsd stop
service nfsd restart
service nfsd status

To find out space usage:
df -kh .
.
To list cpu details:
cat /proc/cpuinfo
.
To list memory details:
cat /proc/meminfo
.
To list semaphores and shared memory:
ipcs -a
.
To make Oracle binary:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ipc_g ioracle ( please note that RAC uses UDP for cache fusion ).
.
To list patches applied on Oracle Binary:
$ORACLE_HOME/OPatch/opatch lsinventory ( It lists details of bug-fixes put in the patch).
.
If there are > 1 instances, how to map shared memory to instances:
on sql*plus as sysdba:
oradebug setmypid
oradebug ipc
now, match shmid from trace to outout of ipcs -a

Storage
To find out storage ( generally Linux uses SCSI protocol to access the storage ):
cat /proc/scsi/scsi
.
To list configured block devices:
ls -ld /sys/block/sd*
.
TO list partitions ( This provides major number, minor number and #blocks ).
cat /proc/partitions
please note that minor 0 corresponds to complete disk, 1 onwards are different partitions.
.
To find out the I/O thruput via orion
$ORACLE_HOME/orion -run dss -testname star
where star.lun contains the list of block devices.
.

ADVANCED:

TO find I/O scheduling :
cat /sys/block/sda/queue/scheduler ( policy is listed in [ ] bracket ).
To renice the priority of a process :
ionice -c1 -n4 -p PID

To check and modify runlevels

chkconfig: It manages symbolic links from /etc/rc.d/rc[0-6].d to /etc/rc.d/init.d.
To start crond on levels 2,3,4,5
chkconfig --levels 2345 crond on
.
To check runlevel information of services:
chkconfig --list ipmi
.
To disable a daemon from starting on any level:
chkconfig --del sendmail
find /etc/rc.d -name '*sendmail' -print /etc/rc.d/init.d/sendmail
.
chkconfig --levels 2345 crond on
.
Find all files with name *crond in directory /etc/rc.d and run file command on each find.
find /etc/rc.d -name '*crond' -exec file {} \;
.
To reload a xinetd:
xinetd[x=inter(net) daemon] manages all internet services e.g. ftp, rsh, finger, rlogin.
/etc/init.d/xinetd reload
.
To add oracle startup script:
add these comments at the top of oracle_start_script:
#chkconfig: 2345 80 05
#description: Oracle 8 Server
.
/sbin/chkconfig --add oracle_start_script
/sbin/chkconfig --list | grep oracle
.
For remote monitoring of Linux machine:

IPMI == Intelligent Platform management Interface ( standard for remote server management )
BMC == Baseboard management Controller ( It is an independent microprocessor with its power supply and network connection ).
To start/stop/status ipmi
service ipmi start/stop/status


How to set SSH:

mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa
cd .ssh
cat id_rsa.pub id_dsa.pub > authorized_keys
concatenate authorized_keys from all nodes to one file and copy that on all nodes.
.
if passphrase specified during key generation:
ssh-agent $SHELL
ssh-add
.
To avoid warning for X11 forwarding:
change /etc/ssh/ssh_config ( ForwardX11 yes->no )
service sshd restart
or
create file config in ~/.ssh dir:
Host *
ForwardX11 no
.
To check if the processor supports virtualization:
egrep '(vmx|svm)' /proc/cpuinfo
.

Check connection between client and server

Check connection between client and server machine using sqlplus ( shell script ):

--------cut here--------------
check_conn()
{
name=$1
dblink=$2
sqlplus -s $name/$name@$dblink _there_is_an_EOF_here
col inst for a6
col client for a12
col usr for a8
col srvr for a12
SELECT sys_context('USERENV', 'SESSION_USER') usr,
sys_context('USERENV', 'INSTANCE_NAME') inst,
sys_context('USERENV', 'HOST') client,
sys_context('USERENV', 'SERVER_HOST') srvr
FROM dual;
_there_is_an_EOF_here
}
check_conn
----------end here------------------------

How to print after matching a pattern using awk

cat file_name | awk '{ if ( $0 ~/pattern_to_match/ ) { pstart = 1 }; if (pstart) { print }

Shell script which runs the command in a directory on a list of files:

cd $DIRNAME
mkdir -p orig
for name in `ls`; do
cat $name | awk '{ if ( $0 ~/pattern_to_match/ ) { pstart = 1 }; if (pstart) { print } }' > $name.tmp
mv $name ./orig
mv $name.tmp $name
done