Testing Conditions in Shell Programming

In the shell programming, conditional expressions are used for performing tests by
- evaluating and comparing integers or strings
- testing file attributes

Testing Conditional expressions is done by using the following three different forms of syntax.
test expression
[ expression ]
[[ expression ]]

These expressions will return an exit status of 0 for true and 1 for false. It is preferrable to use [[ ]] command, as it will result in fewer syntax errors.

Example :
To compare two integers, by checking if one is less than the other with one of the following :
[[ $a gt $b ]]
[ $a gt $b ]
test $a gt $b

You can also combine two or more expressions (aka. compound expressions) using the following operators
- Double ampersand, && ( which means “AND” )
- Double Pipe, || (which means “OR”)

Example :
the syntax for for compound expressions using || is :
[[ expression1 || expression2 ]]

If you would like to test student’s subject mark with minimum required mark and print pass if either of the subject is more than the required score, the sample code would be

$[[ $SubjectA > $ReqScore || $SubjectB > $ReqScore ]] && print “PASS”

[[ .... ]] syntax can also be used for the following tests :
To test variables with string operators
-z true if length is zero
-n will be true if the string is not zero
-o will be true if option is set

example:
if [[ -z $StringA ]]; then
print “Length is zero”
fi

To compare one string with another string
[[ string1 = string2 ]]

Example :
[[ $Subject = "MATH A" ]] && print “Optional Subject”

To compare strings with String operators
=, !=, < , >

To test files with options
-a (file exists)
-d (file is a dir)
-f (file is regular file)
-r (file readable)
-w (file writable)
-x (file executable)
-s (file non-empty)
-u (file has userID bit set)

To compare files
[[ file1 -ef file2]] for same files
[[ file1 -nt file2]] for finding new file
[[ file1 -ot file2]] for finding older file

To compare expressions involving integers using integer operators
-eq, -ne, -le, -ge, -gt

Temporarily Temporary Tablespace

Oracle uses temporary tablespace to perform any sorting required by the queries that have clauses mainly like order by, distinct, group by, union, interset and minus. Index creation and certain correlated subqueries will also use the temporary tablespace.

By default, if the temporary tablespace is not explicitly defined, oracle will use SYSTEM tablespace as the temporary tablespace.

Here is an example to create a temporary tablespace :

SQL> create temporary tablespace TEMP tempfile ‘/ora0001/oradata/temp_1.dbf’ size 500 M extent management local uniform size 256k;

A temporary tablespace can be made as default temporary tablespace for all the users in the database or can be assigned to specific users exclusively as the default one.

To make temp tablespace as default temporary tablespace for all the users in the database :
SQL> alter database default temporary tablespace temp;

To alter a user (e.g.scott) to use temp tablespace as temporary tablespace :
SQL> alter user scott temporary tablespace temp;

To specify temporary tablespace clause while creating a new user :
SQL> create user john identified by changeme
default tablespace users temporary tablespace temp1;

If you omit the TEMPORARY TABLESPACE clause, the temporary segments default to the SYSTEM tablespace. DBA can resize the temporary tablespace depending on the requirement for the temp space usage.

To resize the temp tablespace by adding new tempfile :
SQL> alter tablespace temp add tempfile ‘/ora0001/oradata/temp_2.dbf’ size 512m;

To resize an existing tempfile :
SQL> alter database tempfile ‘/ora0001/oradata/temp_1.dbf’ resize 1024M;

Sometimes, shrinking a temp tablespace may error out with
Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value

In such cases, it may be advisable to create a new temp tablespace with the required temp size and drop the old one using drop tablespace command. If the old tablespace is the default temporary, reassign the new tablespace as default one before dropping the old one.

The command for drop tablespace is :
SQL> drop tablespace temp including contents and datafiles;

Normally, sort operation will be done in memory based on the SORT_AREA_SIZE parameter size in the init.ora file. Any sorts exceeding the SORT_AREA_SIZE limit will acquire temporary segments.

Since temporary tablespaces do not contain any permanent objects, these tablespaces need not be backed up. Infact, tempfile information is not recorded in control file and tempfiles can be recreated any time.

If needed, DBA can make the tempfile offline or even can drop the tempfile as shown below :
SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ offline;

SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ drop including datafiles;

Configuration of Broadcom wireless cards for Ubuntu

Have you ever had trouble setting up your Broadcom wirless cards to work with Ubuntu? The following procedure worked perfect ( I have tried several times) for
me to configure Broadcom wireless card on my old and heavy Dell Inspiron 9100 to work with Ubuntu Edgy.

1) Before you start,  get a copy of bcmwl5.inf and bcmwl5.sys files and copy them to your desktop

2) Open a terminal session and enter the following commands

a) remove the broadcom module
$sudo gedit /etc/modprobe.d/blacklist
add: “blacklist bcm43xx” (no quotes) and save the file
$sudo modprobe -r bcm43xx
$sudo modprobe ndiswrapper

b) Install windows wireless drivers(ndiswrapper)

$sudo apt-get install ndiswrapper-utils
$sudo ndiswrapper -i ~/Desktop/bcmwl5.inf
$sudo ndiswrapper -m
$sudo modprobe ndiswrapper

3) Reboot your PC and the wireless card should start working fine.

Monitoring Temp Space Usage

The following is a very useful script to monitor temp space usage inside the database.

#!/usr/bin/ksh
#set up the Oracle environment.
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=/oracle/app/oracle/product/9.2.0; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export
LD_LIBRARY_PATH
tmp_output=/tmp/temp_usage.log

script_name=${0##*/}
echo “”
echo “Script: $script_name”
echo ” started on: `date`”
echo ” by user: `id`”
echo ” on machine: `uname -n`”
echo “”
echo “This script is designed to capture temp space usage information “
echo “”

${ORACLE_HOME}/bin/sqlplus -s <$ORACLE_PASSWD
whenever sqlerror exit 3 rollback
whenever oserror exit 4 rollback
SET SERVEROUTPUT ON
SET FEEDBACK OFF
insert into temp_usage
select (select instance_name from v\$instance) instance_name ,
s.sid ,
s.username,
u.tablespace,
substr(a.sql_text, 1, 1000) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb ,sysdate
from v\$sort_usage u,
v\$session s,
v\$sqlarea a,
v\$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = ‘db_block_size’
/
exit;
EOF_TEMP
if [[ $? -ne 0 ]]; then
echo “*** ERROR: The ${script_name} did not complete successfully.”
exit 1
else
echo “The ${script_name} script appears to have completed successfully on `date`.”
exit 0
fi

RESUMABLE

From 9i, you can run long running operations/processes that run out of space, using RESUME feature. Basically, the transaction runs in resumable mode and if there are any space issues, the transaction will not fail. It will be suspended until the problem is fixed and will resume automatically after the space is available.

You will need to grant resumable to user, before the user can start a session in resumable mode

Examples:

Altering the session to resumable mode
Grant RESUMABLE to user1;
Alter session enable resumable timeout 3600 name ‘SPACE-ISSUE’;

Disabling resumable
Alter session disable resumable;

Monitoring the suspended sessions :
You can query dba_resumable view to monitor any suspended transactions.

SELECT * FROM dba_resumable;

Purging STATSPACK snapshots

STATSPACK provides a purge utility to remove the older snapshots. sppurge.sql script can be invoked to remove specific snapshots.
You will need to specify begin and end snapid for the script or you can modify it to select all the snapids for a snap_time date range.
You can setup a small batch job to run daily to purge the snapshots older than certain time period, to free up perfstat tablespace.

$ORACLE_HOME/bin/sqlplus -s “perfstat/perfstat” << EOF
TTITLE OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
WHENEVER SQLERROR EXIT 8
WHENEVER OSERROR EXIT 9
spool $HOME/logs/sp_purge_db1.log
@$HOME/scripts/statspack/sp_purge.sql
commit;
spool off
exit;
EOF
if [ `wc -l ${HOME}/logs/sp_purge_db1.log awk '{print $1}'` -gt 0 ]
then
/bin/mail -s “Error” email_dba < ${HOME}/logs/sp_purge_db1.log
fi

sp_purge.sql can modified to specify the required date range for the snapshots to be purged
———————-
———-
——————
column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.startup_time = s.startup_time
and s.snap_time < sysdate – 60;

–
– Post warning

prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id’s specified, for the database instance
prompt you are connected to.
prompt
prompt You may wish to export this data before continuing.
prompt

–
– Obtain snapshot ranges

prompt
prompt Specify the Lo Snap Id and Hi Snap Id range to purge
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Using &&LoSnapId for lower bound.
prompt
prompt Using &&HiSnapId for upper bound.

variable lo_snap number;
variable hi_snap number;
begin
:lo_snap := &losnapid;
:hi_snap := &hisnapid;
end;
/

set termout off
–
– Get begin and end snapshot times – these are used to delete undostat
column btime new_value btime
column etime new_value etime
select to_char(snap_time, ‘YYYYMMDD HH24:MI:SS’) btime
from stats$snapshot b
where b.snap_id = :lo_snap
and b.dbid = :dbid
and b.instance_number = :inst_num;
select to_char(snap_time, ‘YYYYMMDD HH24:MI:SS’) etime
from stats$snapshot e
where e.snap_id = :hi_snap
and e.dbid = :dbid
and e.instance_number = :inst_num;

variable btime varchar2(25);
variable etime varchar2(25);
begin
:btime := ‘&btime’;
:etime := ‘&etime’;
end;
/
set termout on

set heading off

select ‘WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT’
from dual
where not exists
(select null
from stats$snapshot
where instance_number = :inst_num
and dbid = :dbid
and snap_id = :lo_snap)
or not exists
(select null
from stats$snapshot
where instance_number = :inst_num
and dbid = :dbid
and snap_id = :hi_snap);

set heading on
———————–
——————
———

Crontab Usage

crontab utility helps to set up tasks to run automatically in the background at regular intervals, as specified in the schedule.
You can use cron.allow and cron.deny files under /usr/lib/cron directory to control access to the cron.
crontab -e Edit crontab file, or create one if it doesn’t already exist.
crontab -l Display crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time the crontab file is edited

The following is the crontab file syntax
* * * * * command
- – - – -

+—– day of week (0 – 6) (Sunday=0)
+——- month (1 – 12)
+——— day of month (1 – 31)
+———– hour (0 – 23)
+————- min (0 – 59)

Example

Crontab entry for health check report that runs every night at 11.30pm

30 23 * * * /home/oracle/monitoring/health.sh > /logs/health.log 2>&1

READ access to everyone on Trace files

By default, non-dba users will not have access to the tracefiles generated under user_dump_dest directory. It may not be compliant with audit procedures, if we give access to these files in production. But, atleast in development, it will be very helpful for the developers, if they can see their tracefiles generated.

DBA will have to modify permissions on these tracefiles, inorder to give the developers access to read. Or, the better approach may be by setting the following undocumented parameter in the init.ora file and restarting the database instance.

_trace_files_public = true

Reading/Writing files using PL/SQL

Prior to Oracle 9i R2, each directory to be accessed by utl_file must be specified in the init.ora parameter and the database needs to be restarted inorder to make the directory effective.
From 9i R2, instead of specifying directory in the in the init.ora file, a directory object can be used.
Directory object can be created using the create directory command. You need to check with your admin to make sure the directory exists and has correct permissions at the os level. Also, DBA user can control the permissions on the directory object by granting read and write privileges only to the required users.

Examples:

1) Using directory specified in the utl_file_dir parameter in init.ora file
Add the directory e.g. /u01/utilout to the utl_file_dir parameter in the init.ora file and bounce the database.
Then use the directory to read and write the files using UTL_FILE package.

DECLARE
v_out_file UTIL_FILE.FILE_TYPE;
BEGIN
v_out_file := UTL_FILE.FOPEN(‘/u01/utilout’,'test.txt’, ‘W’);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message1′);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message2′);
UTL_FILE.FCLOSE(v_out_file);
END;
/

2) Using directory object
create directory object using create directory command and grant read, write to the user as shown below :
create directory UTIL_OUT as ‘/u01/utilout’;
grant read, write on directory UTIL_OUT to testuser;
Then use the directory object name to read and write the files using UTL_FILE package.

DECLARE
v_out_file UTIL_FILE.FILE_TYPE;
BEGIN
v_out_file := UTL_FILE.FOPEN(‘UTIL_OUT’,'test.txt’, ‘W’);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message1′);
UTL_FILE.PUT_LINE (v_out_file, ‘Test Message2′);
UTL_FILE.FCLOSE(v_out_file);
END;
/

The files created by UTIL_FILE package will have the permissions as per the umask settings of the oracle user.These file permissions can be changed manually after the files are created using chown and chgrp commands.
Incase you need to have the files created automatically with different permissions, you can try changing the umask permissions of the oracle user.

Exporting/Importing Large Data

When exporting Large data, you can specify multiple files in the export command. This can be used from Oracle 8i version.

$> exp testuser1/testuser1 files=/oraexport/data/tab1.dmp, /oraexport/data/tab2.dmp filesize=1024m log=large_exp.log

Please note that the export files need to be specified in the same order, when you import the data.

Or else, you can write the export data to a pipe and compress the data before writing to a file.
for export :
$PIPE=/tmp/exp_ora9i.dmp
$MAXSIZE=1024m
$ExportFile=employees.dmp
$ExportLog=exp_employees.log
$mknod $PIPE p
$ORACLE_USER=testuser1/testuser1
$ParamString=”compress=n feedback=10000 tables=employees log=$ExportLog statistics=none”
$( gzip < $PIPE ) split -b $MAXSIZE – $ExportFile. & $exp $ORACLE_USER buffer=2000000 constraints=n triggers=n grants=n indexes=n file=$PIPE $ParamString

To import the data exported as above, you can uncompress the data to a pipe and use the same pipe to import.

$PIPE=/tmp/imp_ora9i.dmp
$mknod $PIPE p
$ORACLE_USER=testuser2/testuser2
$cat `echo employees.* sort` gunzip > $PIPE &
$imp $ORACLE_USER file=$PIPE ignore=y