Hlp needed for new bie

banumyname · #1 (**permalink**) 08-17-10, 08:20

i am new to shell scripting..

I had a requirement that needs to find whether the database was backed up or not in last 24 hrs .i am tring to query it in my catalog database if i query

select owner from from dba_objects where object_name='rc_database';

it will give me all the databases that are reciding on thar catalog database

and i need to query

select BACKUP_TYPE,START_TIME,COMPLETION_TIME from metat.rc_backup_piece where start_time > (sysdate -1);

gives the back ups started since last day out put is like this for this query (metat is the one of the database name)

in this way the loop need to run for all the database names that we get from the rc_database view ,

the o/p for this statement looks like

B START_TIM COMPLETIO
- --------- ---------
L 16-AUG-10 16-AUG-10
D 16-AUG-10 16-AUG-10
L 16-AUG-10 16-AUG-10
D 16-AUG-10 16-AUG-10

so if no lines are selected for this start_time then need to get a mail for the group.

THANKS ALL PLEASE HELP ME OUT

pdreyer · #2 (**permalink**) 08-19-10, 07:42

Replace isqlcmd in the below code with whatever command you use to run sql commands.

Code:

#!/usr/bin/ksh
getdbnm()
{
isqlcmd <<EOF
select owner from from dba_objects where object_name='rc_database';
EOF
}
for n in $(getdbnm)
do
isqlcmd <<EOF
select BACKUP_TYPE,START_TIME,COMPLETION_TIME 
from $n.rc_backup_piece where start_time > (sysdate -1); 
EOF
done

banumyname · #3 (**permalink**) 08-19-10, 07:53

Quote:

Originally Posted by pdreyer

Replace isqlcmd in the below code with whatever command you use to run sql commands.

Code:

#!/usr/bin/ksh
getdbnm()
{
isqlcmd <<EOF
select owner from from dba_objects where object_name='rc_database';
EOF
}
for n in $(getdbnm)
do
isqlcmd <<EOF
select BACKUP_TYPE,START_TIME,COMPLETION_TIME 
from $n.rc_backup_piece where start_time > (sysdate -1); 
EOF
done

Thanks i try that and let you know

Thank you so much

Internet Services

Web Development

Digital Marketing

Consumer Tech