Shell Scripts and ways to run a shell script

What is a Shell Script?
A script, or file that contains shell commands, is a shell program. You can create a shell script using the editor supplied with your operating system such as vi editor.

Different ways to run as shell script:
There are mainly three different ways of running a shell script but before running a script you should have execute permissions on the script which can be assigned explicitly if you don't have permissions to execute i.e., by executing chmod +x scriptname at command prompt.

a. Ex: $ . yourscript.ksh.

This method of running a script is called the "dot" (.) method of running a script. The "dot" method of running a script causes the commands in the script to be run as if they were part of your login session.

b. Ex: $ yourscript.ksh.

This method is called "just the name" method which causes the shell to do a series of things. First, it runs another copy of the shell as a subprocess. The shell subprocess then takes commands from the script, runs them, and terminates, handing control back to the parent shell.

c. Ex: $ yourscript.ksh &.

& makes the command run in the background, which is really just another term for "subprocess". This method of running a shell script is similar to that method as in above point b. But there is a basic difference in this method you have control of your terminal or workstation while the command runs -- you need not wait until it finishes before you can enter further commands.

Below is a figure which depicts different ways of running a script i.e., here the script name is fred:


Figure 4-1. Ways to run a shell script

Some imp. SQL's on user sessions

SQL for getting active user sessions:

select /*+RULE */ SID,SPID,ses.SERIAL#,ses.USERNAME,ses.module,SQL_TEXT,pro.TERMINAL
from v$sqlarea sql, v$session ses,v$process pro
where HASH_VALUE=SQL_HASH_VALUE
and status = 'ACTIVE'
and ses.username is not null
and ses.PADDR = pro.ADDR
order by sql_text;

SQL to get blocking sessions and SQL's:

select /*+RULE */s.username username , s.sid,s.serial#, p.spid,o.OBJECT_NAME objectname,s.process,substr(s.module,1,20) module,
to_char(sysdate - s.last_call_et/60/60/24,'DD-MON-YYYY HH24:MI') last_call,
substr(w.event,1,24) event,
decode(block ,0,'NO','YES') BLOCKER,
decode(request,0,'NO','YES') WAITER
from v$lock l, v$session s, v$process p, v$session_wait w,v$locked_object lb, dba_objects o where (l.request > 0 or l.block > 0 ) and s.sid=l.sid and p.addr=s.paddr and s.last_call_et > 600 and w.sid=s.sid and s.sid = lb.SESSION_ID and lb.OBJECT_ID = o.OBJECT_ID order by last_call_et desc;

To find out the invalid objects :

select object_name,object_type from dba_objects where username='schema_name' and status ='INVALID';

To find out the locked objects:

select a.OBJECT_NAME,b.SESSION_ID,b.ORACLE_USERNAME,b.OS_USER_NAME,b.PROCESS,b.LOCKED_MODE
from dba_objects a, v$locked_object b where b.OBJECT_ID=a.OBJECT_ID;

Forcing a database to open in case of ORA-01194 and ORA-01110 errors

Problem Summary:
SQL> startup mount
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 32514 generated at 12/26/2001 17:47:47 needed for thread 1
ORA-00280: change 32514 for thread 1 is in sequence #7


Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 32546 generated at 01/15/2002 17:01:06 needed for thread 1
ORA-00289: suggestion : /oradb/npap/archives/arch8.arc
ORA-00280: change 32546 for thread 1 is in sequence #8
ORA-00278: log file '/oradb/npap/archives/arch7.arc' no longer needed for this
recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/npap/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradb/npap/system01.dbf'

Got the above errors while executing ‘alter database open resetlogs’
After cloning a DB.



Solution:
If you have issues after cloning a database and you cannot open the database using the resetlogs option, you can add the following paramater to the init.ora file, restart the instance and try again:

_allow_resetlogs_corruption = true

Importance of .profile file and a sample

What is a .profile and why do you need it?

.profile is an important file in your home directory that helps in customizing your environment. This is a file of shell commands, also called a shell script, that the Korn shell reads and runs whenever you log in to your system. This .profile along with your environment file are sources of customizing your environment.


A Sample .profile file:
#!/bin/ksh

set +u
PATH=/bin:/usr/local/bin:/usr/bin:/usr/sbin:$PATH:/etc:/usr/ucb::/usr/bin/X11:/sbin:opt/FXboks/bin

export PATH

export HOME=/optware/oracle

TERM=xterm
export TERM
EDITOR=vi
export EDITOR
# export TMOUT=0
# readonly TMOUT


#########################
# Set up shell environment:
#########################
set -u # error if undefined variable.
trap "echo 'logout'" 0 # what to do on exit.
PS1='$LOGNAME'"@"`hostname -s`"["'$ORACLE_SID'"] "'$PWD'" => "

########################
# Setup Aliases
#########################
alias l='ls -al'
alias ll='ls -al'
alias lt='ls -altr'
alias lll='ls -al|pg'
alias ldt='ls -lt|pg'
alias Grep=grep
alias tohome='cd /dbms/oracle/local/DEMO/etc'
alias tohomec='cd /dbms/oracle/local/DEMO/etc'
alias torman='cd /backup/DEMO/rman'
alias todb='cd /optware/oracle/DEMO'

########################
# Setup Default ORACLE SID
#########################

export ORACLE_SID=DEMO
export ORACLE_TERM=vt100

ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

AIXTHREAD_SCOPE=S
export AIXTHREAD_SCOPE

TNS_ADMIN=/dbms/oracle/tnsadmin
export TNS_ADMIN

if [ -s "$MAIL" ] # This is at Shell startup. In normal
then echo "$MAILMSG" # operation, the Shell checks
fi # periodically.

. /dbms/oracle/local/dbms_local.env
set -o vi
stty erase ^?

Setting up your unix environment

. profile plays a prominent role in setting up environment, you Unix command prompt, your default etc. It is also useful in setting up some of your environment variables.

Ex: PATH is one of the most important environment variables stores the path for Unix executables.

Customizing your command prompt:
Placing the following code snippet in your .profile file will give you a Unix prompt that identifies your current server name, database name, and working directory. Knowing this information can help prevent you from accidentally running a command against the wrong database.

#***********************************************
# Standard Unix Prompt
#***********************************************
PS1="
`hostname`[\${ORACLE_SID}]\${PWD}=>
>"

Ex: Your command prompt will look like:
orcl.com[DEMO]/optware/oracle=>

Table Statistics Tips

How to check for tables which have stale statistics:
select owner,table_name,last_analyzed,sample_size from dba_tables;

Ex: SQL> select owner,table_name,last_analyzed,sample_size from
dba_tables where table_name in ('EMP','DEPT');

How to gather statistics for a schema:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
estimate_percent=>20, cascade=>TRUE);

How to gather statistics for a particular table:
Ex: exec dbms_stats.GATHER_TABLE_STATS(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>20, cascade=>TRUE);
( or )
Ex: exec dbms_stats.gather_table_stats(ownname=>'SCOTT',
tabname=>'EMP', estimate_percent=>NULL,
method_opt=>'FOR ALL INDEXED COLUMNS', degree=>5,
GRANULARITY => 'ALL', CASCADE=>TRUE);

Note: The above gather statistics command gives a more detailed
statistics

To check when a particular table was last analyzed:
Ex: SQL> select table_name, column_name, num_distinct nd,
num_nulls nn, density, last_analyzed from dba_tab_columns
where table_name in ( 'EMP' ) order by table_name, column_name;

How to read a trace file (10046 trace file)

Event 10046 Raw Output:
~~~~~~~~~~~~~~~~~~~~~~

----------------------------------------------------------------------------
APPNAME mod='%s' mh=%lu act='%s' ah=%lu
----------------------------------------------------------------------------
APPNAME is the application name setting. This only applies to Oracle 7.2
onwards. This can be set by using the DBMS_APPLICATION_INFO package.
See Note:30366.1.

mod  Module name
mh  Module hash value
act  Action
ah  Action hash value

----------------------------------------------------------------------------
PARSING IN CURSOR #%d; len=X dep=X uid=X oct=X lid=X tim=X hv=X ad='X'
END OF STMT
----------------------------------------------------------------------------
CURSOR Cursor Number
len  Length of SQL statement
dep  PGA Depth
uid  Schema user id of parsing user
oct  Oracle Command Type
lid  Privilege user id
tim  Timestamp (100th's of a second)
Can be used to determine times between points in the trace file.
hv  Hash ID
ad  SQLTEXT address (See V$SQLTEXT)

statement The actual SQL statement being parsed

----------------------------------------------------------------------------
PARSE ERROR #%d:len=%ld dep=%d uid=%ld oct=%d lid=%ld tim=%lu err=%d statement...
----------------------------------------------------------------------------

PARSE ERROR In Oracle 7.2+ we report parse errors.

len  length of SQL statement
dep  PGA Depth
uid  User ID
oct  Oracle Command Type (if known)
lid  Privilege user id
tim   Timestamp
err  Error reported

statement The SQL statement that errored. If this contains a password
the statement is truncated as indicated by '...' at the end.

----------------------------------------------------------------------------
PARSE #%d :c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
EXEC  #%d:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
FETCH #%d:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
UNMAP #%d:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=0
----------------------------------------------------------------------------
- OPERATIONS:

PARSE Parse a statement
EXEC  Execute a pre-parsed statement
FETCH Fetch rows from a cursor
UNMAP If the cursor uses a temporary table then when the cursor is
closed you see an UNMAP when we  free up the temporary table
locks. (ie: free the lock, delete the state object, free the
temp segment)
In tkprof UNMAP stats get added to the EXECUTE statistics.
SORT UNMAP
As above but for OS file sorts or TEMP table segments.

c CPU time (100th's of a second)
e Elapsed time (100th's of a second)
p Number of physical reads
cr Number of buffers gotten for CR reads
cu Number of buffers gotten in current mode
mis Cursor missed in the cache
r Number of rows processed
dep Recursive call depth (0 = user SQL, >0 = recursive)
og Optimizer goal:
KKOSHARW 1  All_Rows
KKOSHFRW 2  First_Rows
KKOSHRUL 3  Rule
KKOSHCHO 4  Choose
tim Timestamp (large number in 100th's of a second)
Use this to determine the time between any 2 operations.

----------------------------------------------------------------------------
ERROR #%d:err=%d tim=%lu
----------------------------------------------------------------------------
SQL Error shown after an execution or fetch error.

err  Oracle error code at the top of the stack.
tim  Timestamp

----------------------------------------------------------------------------
STAT #%d id=N cnt=0
----------------------------------------------------------------------------

STAT lines report explain plan statistics for the numbered %d.

%d  Cursor which the statistics apply to
id  Line of the explain plan the row count applies to (starts
at line 1). This is effectively the row source row count
for all row sources in the execution tree.
cnt  Number of rows for this row source.


----------------------------------------------------------------------------
XCTEND rlbk=%d rd_only=%d
----------------------------------------------------------------------------
XCTEND is a transaction end marker.

rlbk 1 if a rollback was performed, 0 if no rollback  (commit)
rd_only 1 if transaction was read only. 0 is changes occurred.

----------------------------------------------------------------------------

==========================================================
Special output option from non default 10046 trace levels:
==========================================================

----------------------------------------------------------------------------
BINDS #%d:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08
bfp=088058a8 bln=22 avl=03 flg=0d
value=1379
----------------------------------------------------------------------------

BIND variables bound to a cursor.
This is only reported if Event:10046 is used with the level 4 bit on
(00000100)

bind N The bind position being bound.
dty  Data type. See Glossary:DataTypes
mxl  Maximum length of the bind variable (private max len in paren)
mal  Array length
scl  Scale
pre  Precision
oacflg See uacdef.h.
UACFIND  0x01   true if using indicators
UACFALN  0x02   true if using length vector
UACFRCP  0x04   true if returning retcodes
UACFBBV  0x08   true if bind by value
UACFBPW  0x10   true if piecewise bind
UACFBLP  0x20   true if blank pad/strip
UACFARR  0x40   true if vector object
UACFIGN  0x80   Ignore this bind/define.
 Used for delayed upi
bfp  Bind address
bln  Bind buffer length
avl  Actual value length (array length too)
flg  See kxs.h
KXSBFBBV 0x01  Bound By Value
KXSBFBBR 0x02  Bound By Reference
KXSBFRBD 0x04  ReBounD
KXSBFARR 0x08  object is an ARRay
KXSBFLNG 0x10  object is LoNG
KXSBFOST 0x20 Out SeT:  used to indicate pl/sql out
variable set
KXSBFBUC 0x40  Bind used in child cursor
KXSBFPBN 0x80 Parent bind variable

value The actual value of the bind variable.
Numbers show the numeric value, strings show the string
etc...

It is also possible to see "bind 6: (No oacdef for this bind)" if no
separate bind buffer exists.

----------------------------------------------------------------------------
WAIT #%d: nam=eventname ela=0 p1=0 p2=0 p3=0
----------------------------------------------------------------------------

WAIT An event that we waited for.
This is only reported if Event:10046 is used with
the level 8 bit on (00001000)

nam  What is being waited for - See WaitEvents
ela  Elapsed time for the operation
p1  P1 for the given wait event
p2  P2 for the given wait event
p3  P3 for the given wait event
For p1-p3 see the relevent wait-event article.

Example (Full Table Scan):
WAIT #1: nam=db file scattered read; ela= 5 p1=4 p2=1435 p3=25

WAITing under CURSOR no 1
for db file scattered read
We waited 0.05 seconds
For a read of:  File 4, start block 1435, for 25 Oracle blocks

Example (Index Scan):
WAIT #1: nam=db file sequential read; ela= 4 p1=4 p2=1224 p3=1

WAITing under CURSOR no 1
for db file sequential read
We waited 0.04 seconds for a single block read (p3=1)
from file 4, block 1224

Reference:
http://tonguc.yilmaz.googlepages.com/ev10046.txt - the
above data is completely from the link I have given
here. You can refer to above link for a clear formatted
one if the above data is not clear.

How to generate a trace

To generate a trace in the current session:
10046 trace:
alter session set events '10046 trace name context forever, level 8';
(0r)
alter session set events '10046 trace name context forever, level 12';

Note: 12 will trace wait_events and bind variables

10053 trace:
alter session set events '10053 trace name context forever';
(or)
alter session set events '10053 trace name context forever, level 1' ;

Note: To generate a trace in your current session you will need privilege to set trace for yourself. In case the above commands doesn't work please check if you have privileges to set trace for yourself.

To generate a trace for a particular session:
10046 trace:
Ex: EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,8,'');
(or)
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');

10053 trace:
Ex: EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10053,1,'');

Note: You can get the SID, SERIAL# from v$session

To turn-off trace in your current session that you have put trace on:
10046 trace:
alter session set events '10046 trace name context off';

10053 trace:
alter session set events '10053 trace name context off';

To turn-off trace for a particular session that you have put trace on:
10046 trace:
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');

10053 trace:
EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10053,0,'');

Note: The SID, SERIAL# are the values of a particular session for which you have put the trace on.

References:
http://oradbatips.blogspot.com/2007/03/tip-38-tracing-session-with-10046-event.html
some commands have been taken from above link and some commands are that I have used at my work.

Memory Structures

Oracle consists of three memory structures known as :
* System Global Area (SGA): This is a large, shared memory segment that virtually all
Oracle processes will access at one point or another.

* Process Global Area (PGA): This is memory that is private to a single process or thread,
and is not accessible from other processes/threads.

* User Global Area (UGA): This is memory associated with your session. It will be found
either in the SGA or the PGA depending on whether you are connected to the database
using shared server (then it will be in the SGA), or dedicated server (it will be in the PGA, in the process memory

1. System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle Instance. SGA is dynamic and is sized by SGA_MAX_SIZE parameter.
a. SGA consists of several memory structures such as:
i. Shared Pool:Used to store most recently executed SQL statements and most recently
used data definitions. Sized by the parameter SHARED_POOL_SIZE. It Consists of two
key performance-related memory structures:

- Library Cache: It stores information about the most recently used SQL and
PL/SQL statements and enables the sharing of commonly used statements. It is
managed by a least recently used (LRU) algorithm. Consists of two structures: Shared
SQL area, Shared PL/SQL area. Its size determined by the Shared Pool sizing

- Data Dictionary Cache: A collection of the most recently used definitions in the
database. Includes information about database files, tables, indexes, columns, users,
privileges, and other database objects. During the parse phase, the server process looks
at the data dictionary for information to resolve object names and validate access.
Caching data dictionary information into memory improves response time on queries
and DML. Size determined by the Shared Pool sizing.

ii. Database Buffer Cache: Stores copies of data blocks that have been retrieved from the
datafiles. Enables great performance gains when you obtain and update data. Managed
through an LRU algorithm. DB_BLOCK_SIZE determines primary block size and can be
managed dynamically.

iii. Redo Log Buffer: Records all changes made to the database data blocks. Primary
purpose is recovery. Changes recorded within are called redo entries. Redo entries contain
information to reconstruct or redo changes. Size defined by LOG_BUFFER

iv. Other structures (for example, lock and latch management, statistical data)

b. Two other additional memory structures that can be configured within the SGA:
i. Large Pool: An optional area of memory in the SGA. Relieves the burden placed on the
Shared Pool. Used for: (a) Session memory (UGA) for the Shared Server (b) I/O server
processes (c) Backup and restore operations or RMAN (d) Parallel execution message
buffers - PARALLEL_AUTOMATIC_TUNING set to TRUE. Does not use an LRU list.
Sized by LARGE_POOL_SIZE

ii. Java Pool: Services parsing requirements for Java commands. Required if installing and
using Java. Sized by JAVA_POOL_SIZE parameter.

2. Program Global Area (PGA) & User Global Area (UGA):
Memory reserved for each user process connecting to an Oracle database. Allocated when a process is created. Deallocated when the process is terminated. Used by only one process. The PGA is a process-specific piece of memory, memory specific to a single operating system process or thread.The PGA is never allocated in Oracle's SGA-it is always allocated locally by the process or thread. PGA memory management is controlled by the database initialization parameter WORKAREA_SIZE_POLICY and may be altered at the session level.

UGA is your session's state. It is memory that your session must always be able to get to.

• If you connected via a shared server, then the UGA must be stored in a memory structure that every shared server process has access to-and that would be the SGA.Every one can read and write your session's data.
• If you are using a dedicated server connection UGA will be be contained inthe PGA of your dedicated server
  

The other areas of PGA memory are generally used for in-memory sorting, bitmap merging, and
hashing. There are two ways to manage this other non-UGA memory in the PGA:

• Manual PGA memory management, where you tell Oracle how much memory is it allowed to use to sort and hash any time it needs to sort or hash in a specific process. Parameters used in manual memory management are:

a. SORT_AREA_SIZE: The total amount of RAM that will be used to sort information
before swapping out to disk. This will be allocated out of PGA.
b. SORT_AREA_RETAINED_SIZE: The amount of memory that will be used to hold
sorted data after the sort is complete. This will be in your UGA.
c. HASH_AREA_SIZE: The amount of memory your server process would use to store
hash tables in memory. These structures are used during a hash join, typically when
joining a large set with another set. The smaller of the two sets would be hashed into
memory and anything that didn’t fit in the hash area region of memory would be stored
in the temporary tablespace by the join key.

• Automatic PGA memory management, where you tell Oracle how much memory it should attempt to use system wide. It is a new way to manage PGA memory was introduced that
  avoids using the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE parameters. It addresses few issues:

a. Ease of use
b. Manual allocation was a “one-size-fits-all” method:
c. Memory control

Setting up of automatic PGA memory management will include configuration of below
initialization parameters:

a. WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which
will use the sort area and hash area size parameters to control the amount of memory
allocated, or AUTO, in which case the amount of memory allocated will vary based on
the current workload present in the database. The default and recommended value is
AUTO.
b. PGA_AGGREGATE_TARGET: This parameter controls how much memory the
instance should allocate, in total, for all work areas used to sort/hash data. Its default
value varies by version and may be set by various tools such as the DBCA. In general, if
you are using automatic PGA memory management, you should explicitly set this
parameter.

Note: In Oracle9i, when using a shared server connection, you can only use manual PGA memory management

Reference: Thomas, Kyte. Expert Oralce Database Architecture.

Oracle Basic Terminology

Oracle Server: a database management system that provides an open, comprehensive, integrated approach to information management that consists of an Oracle Instance and an Oracle database.

Oracle Instance: Consists of memory and background process structures. A means to access an Oracle database and always opens one and only one database

• Memory structures: Shared Pool (Library Chache and Data Disctionary Cache), Java Pool, Large Pool, Database Buffer Cache, Redo Log Buffer. (Note: In 10g all the memory structures are grouped under a command name SGA)
• Background Processes: PMON, SMON, DBWR, LGWR, CKPT and Others

Oracle Database: A collection of data that is treated as a unit consists of three file types:

• Physical Structure files: Data files, Control files, Redo Log files
• Parameter file: Ex:-initWORK.ora
• Archived Log files

Oracle Architecture

Note: Click on the above link to view a bigger clear picture