How to delete/reset parameter in spfile using ALTER SYSTEM?
Filed under: General DBA, Initialization, Parameters
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”
How to use DBMS_FLASHBACK?
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.
Another way of flashback can be used is as follows
SQL> select count(1) from test as of scn :current_scn;
COUNT(1)
———-
0
-- Shows rows before the change and after using “AS OF SCN..”
SQL> select *
from (select count(1) from test),
(select count(1) from test as of scn :current_scn);
COUNT(1) COUNT(1)
———- ———-
1 0
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.
How to turn trace and set events using dbms_system?
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, '');
How to set max_dump_file_size on a running session?
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);
Redirection on unix
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
grep options
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
Regular expression
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
Arrays in ksh
# 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
# how to get number of elements in the array
$ x[1]=5 x[2]=3 x[3]=6 x[11]=55
$ print ${#x[*]}
4
# Looping through an array
$ for place in ${x[*]}
do
print $place
done
5
3
6
55
How to write a case condition in shell?
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
How ERR trap works?
# 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
