Oracle DBA Daily Experiences

Using ALTER SYSTEM RESET one can delete parameter from spfile if the parameter is in the spfile.

Example:
– shows the parameter is in the spfile
$ pwd
/u01/oracle/product/10.2.0/db_1/dbs
$ strings spfileTEST.ora | grep open
*.open_cursors=100

– Login to SQL*Plus, scope has to spfile and sid has to be supplied. When '*' is specified it applies to all instances if it is a cluster
SQL> alter system reset open_cursors scope=spfile sid='*';

System altered.

# check spfile after running reset command grep no longer finds the parameter in the spfile
$ strings spfileTEST.ora | grep open
$ echo $?
1

Note: If the parameter is not found in the spfile oracle will return “ORA-32010: cannot find entry to delete in SPFILE”, and if the scope is set to both oracle will return “ORA-32009: cannot reset the memory value for instance * from instance TESTDB”

In 10g using dbms_flashback one can flashback using timestamp or SCN. If a user accidentally deletes rows or updates rows one can use this to review the data without need to have flashback turned on in the database. Note: SYS can’t use dbms_flashback to flashback, Oracle will return “ORA-08185: Flashback not supported for user SYS” when SYS will try to enable flashback.

Example:
– shows flashback is off
SQL> select flashback_on from V$database;

FLASHBACK_ON
——————
NO

– declare a variable to store current SCN
SQL> variable current_scn number
– Get the current SCN and initialize current_scn
SQL> execute :current_scn := sys.dbms_flashback.get_system_change_number();

PL/SQL procedure successfully completed.

– print the current scn
SQL> print current_scn;

CURRENT_SCN
———–
389907

– query the table, shows no rows
SQL> select * FROM test;

no rows selected

– insert the row and commit
SQL> insert into test values ( 1 );

1 row created.

SQL> commit;

Commit complete.

– flashback to before insert was executed, so select should return no rows
SQL> execute dbms_flashback.enable_at_system_change_number(:current_scn);

PL/SQL procedure successfully completed.

– as expected select returned no rows
SQL> select * FROM test;

no rows selected

– disable flashback
SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed.

Note: If there is a truncate executed on the table then data can’t be read from the table after enabling flashback Oracle will return “ORA-01466: unable to read data – table definition has changed” on truncated table.

To turn on trace for a specific event.
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 8, '');
Example: Enable trace at level 8 for session id 10046
exec dbms_system.set_ev(12345, 543211, 10046, 8, '');

– To turn off the tracing:
exec dbms_system.set_ev(<SID>, <serial#>, <event>, 0, '');
Example: exec dbms_system.set_ev( 1234, 56789, 10046, 0, '');

Using dbms_system.set_int_param_in_session, one can set max_dump_file_size of a running session. In the example it sets trace file to 100MB
exec sys.dbms_system.set_int_param_in_session(sid => <sid>, serial# => <serial#>, parnam => 'MAX_DUMP_FILE_SIZE', intval => 100*1024*1024);

Redirecting stdout and stderr
ls > file – redirect output to file
ls 2> err – redirect error to file named err
ls 2>&1 file – redirect error to where output is going to file named file
ls 1>&2 file – redirect stdout to stderr to file named file
ls > file 2> err – redirect output to file and error to file named err
exec 2> std.err – Redirect all error messages to file named std.err

Here are some of the options that can be passed to grep:
-l – Print filename if pattern found otherwise it doesn’t print the filename, if found any one file exit status is 0, if not found in any files then 1
-n – Print line # where pattern was found
-i – ignore case when matching string
-v – Display lines where the given pattern doesn’t match
-c – Display total number of occurrences of pattern found, if multiple occurrences on one line it is counted as one
-s – silent displays except error message, if found then exit status is 0 else non-zero

One can use the regular expressions to match in vi, grep, sed and awk.

[a-d] – Match one character with in a-d i.e. a, b, c, d
[^a-d] – Match one character not in the range a-d
\<test\> – Match whole word test
test\> – Match words that ends with test
\<test\> \1ing – Match following text “test testing”, \1 maps to first tag i.e \(\)
x\{5,\} – Match at least 5 occurrences of x
x\{5,9\} – Match between 5 to 9 times occurrences of x
^test – Looks for test at the beginning of a line
test$ – Looks for test at the end of the line
^test$ – Looks for test on a line by itself
th.t – “.” matches one character i.e. 4 letters has th + any character and ends with t. Example: this, that are valid matches
\. – Look for period, using “\” one can escape metacharacters

Search and replace:
:s/\(square\) and \(fair\)/\2 and \1/ – searches for “square and fair” and replaces it with fair and square

# how to create a array and assign a list of values
set -A fruit apples pears oranges banana
# print the first element in the array
print $fruit[0]
# set a new value to element 1
$fruit[1]=watermelon

In ksh shell – case statement
case variable_name in
pattern1)
statements1
;;
pattern2)
statements2
;;
# default (catch all remaining)
*)
statements3
;;
esac

Example:
case $answer in
yes|Yes|y) # received yes
echo got a positive answer
;;
no|n) # received no
echo got a ‘no’
;;
q*|Q*)
#assume the user wants to quit
exit
;;
*)
echo This is the default clause
;;
esac

In csh shell – case statement
Example:
switch ( $color )
case blue:
echo $color is blue
breaksw
case red:
case purple
echo $color is red or purple
breaksw
default:
echo “Not a valid color”
endsw

# Sample script that shows how ERR signal works, when a command returns a non-zero status it triggers to calls to print the message in double quotes.

trap ‘print “You gave me non-integer. Try again”‘ ERR
typeset -i number
while true
do
print -n “Enter an integer: ”
read number 2> /dev/null
if [ $? -eq 0 ]; then
break
fi
done

# reset trap else the grep command below would also generate the error message
trap – ERR
grep “blahblah” /etc/passwd > /dev/null 2>&1
echo “Number entered: $number