Dear all

Operating system: Solaris 9
database:9.2
# Figure out version of database.

if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA="sqlplus internal"
else
SQLDBA="sqlplus /nolog"
SQLUSR="connect / as sysdba"
fi

# Now generate the backup commands

${ORACLE_HOME}/bin/$SQLDBA <<EOF > /dev/null
$SQLUSRset serverout on
set echo off
set pause off
set feed off

1)the above part is one of my backup script , i would like to know the bold statement that is
${ORACLE_HOME}/bin/$SQLDBA <<EOF > /dev/null
$SQLUSR

its not connecting sql application for backup purpose , is there any syntax error in the above two lines.

2)when I use this statement on command line direct, its getting connected succesful
${ORACLE_HOME}/bin/$SQLDBA

3)but when we use {ORACLE_HOME}/bin/$SQLDBA <<EOF > /dev/null its not connecting to sql application prompt

Waiting for the reply

Regards

bad spaces mostly

Dear Uxinn

$if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
conti>SQLDBA="sqlplus internal"
conti>else
conti>SQLDBA="sqlplus /nolog"
conti>SQLUSR="connect /as sysdba"
fi
conti>DARDTB@oradev $
DARDTB@oradev $# Now generate the backup commands
DARDTB@oradev $
DARDTB@oradev $${ORACLE_HOME}/bin/$SQLDBA<<EOF
conti>$SQLUSR
conti>select * from dual;
conti>set serverout on
conti>set echo off
conti>set pause off
conti>set feed off
conti>EOF

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jun 10 12:04:47 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> Connected.
SQL>
D
-
X

SQL> SQL> SQL> SQL> SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

1)your script is working perfectly as required but my concern regarding the first line you suggested me that is "!/bin/bash " becuase the user which is invoking this script his default shell
$echo $SHELL
/bin/sh

2) i really appreciate your explaination whats going wrong in my part of scripts which i posted above
" ${ORACLE_HOME}/bin/$SQLDBA <<EOF > /dev/null "

<<EOF > /dev/null i think this the area we need to give focus

Regards




Regards

Bad space between variable $SQLDBA and <<EOF
should be $SQLDBA<<EOF

Missing space between $SQLUSR and "set serverout on"

Other than that it should probably work

#!/bin/bash at the top makes the script run in bash instead of default shell, so if this is the correct path to bash then it will work fine for all users.

But this should work fine from within "sh" as well

Also are you still using Oracle 8 ?
if not then you can simplify your script since svrmgrl is no longer used.

Hope this answers your questions,

Uxinn

Dear Uxinn

1)my script part is working fine now
2)we have 9.2 database version
3)i have another question in my next part of script, if you can?

if [ -f $ORACLE_HOME/bin/svrmgrl ] ; then
SQLDBA="sqlplus internal"
else
SQLDBA="sqlplus /nolog"
SQLUSR="connect / as sysdba"
fi

# Now generate the backup commands

${ORACLE_HOME}/bin/$SQLDBA <<EOF > /dev/null
$SQLUSR
set serverout on
set echo off
set pause off
set feed off
set head off
set lines 250
spool hotbackup
declare
cursor ts is select distinct(tablespace_name) from dba_data_files;
cursor df(p_ts varchar2) is select file_name from dba_data_files
where tablespace_name=p_ts;
v_tsname dba_data_files.tablespace_name%type;
v_dfname dba_data_files.file_name%type;
begin
open ts;
loop
fetch ts into v_tsname;
exit when ts%notfound;
dbms_output.put_line('alter tablespace '||v_tsname||' begin backup;');
open df(v_tsname);
loop
fetch df into v_dfname;
exit when df%notfound;
dbms_output.put_line('!cp '||v_dfname||' '||'$BKUPDEST');
end loop;
close df;
dbms_output.put_line('alter tablespace '||v_tsname||' end backup;');
end loop;
close ts;
end;
/
select 'alter system archive log current;' from dual;
select 'alter system archive log current;' from dual;
select 'alter system archive log current;' from dual;
select 'alter database backup controlfile to '||chr(10)||'''$BKUPDEST/control.bkp'''||';' from dual;
spool off
exit;
EOF

# Clean up the hotbackup.lst and redirect the output to hotbackup1.lst.

tail +25 hotbackup.lst |grep -v SQL\> > hotbackup1.lst

# Now do the backup and then check the backup mode of the tablespaces.

${ORACLE_HOME}/bin/$SQLDBA <<EOF > ${BKUPLOG}
$SQLUSR

set echo on
set termout on

@hotbackup1.lst
set pages 1000 lines 132;

column recover heading "Requires|Recovery?" format a10;
column time heading "Date Of|Last Backup" format a12;
column ts heading "TsName" format a17;
column df heading "FileName" format a38;
column mode heading "Mode" format a8;

select tablespace_name ts ,name df,
a.status "Status",
decode(fuzzy,'YES','BACKUP','NORMAL') "mode" ,recover, time
from v\$datafile_header a, v\$backup b
where a.file#=b.file#
order by tablespace_name,name;
exit
EOF
if test $? -eq 0 ; then
echo ""
echo "****************************************************************"
echo "Hot Backup Of Database \"${ORACLE_SID}\" Completed Successfully."
echo "****************************************************************"
else
echo ""
echo "*************************************************************"
echo "Hot Backup Of Database \"${ORACLE_SID}\" Failed Due To Erros."
echo "*************************************************************"
echo ""
fi

in the above bold part for verification of the backup how it is determining the the backup is succesfull

if test $? -eq 0 ; then
echo ""
echo "****************************************************************"
echo "Hot Backup Of Database \"${ORACLE_SID}\" Completed Successfully."
echo "****************************************************************"
else
echo ""
echo "*************************************************************"
echo "Hot Backup Of Database \"${ORACLE_SID}\" Failed Due To Erros."
echo "*************************************************************"
echo ""

Regards

Dear Unixx

waiting for your valuable and accurate reply

Best regards

Hi Maooah
sqlplus exits with exit code 0 unless you tell sqlplus to do otherwise.
But if you add this line to your script
WHENEVER SQLERROR EXIT 1
then sqlplus will exit with code 0 = success and 1 = Failure


Cheers,
Uxinn

Dear Uxinn

i executed this script with oracle user it is working fine, but when i schedule this script it couldnot able to run succesfully i assign required privileges to execute this script also

$ls -ltr mHotBackupOneDB_Df.sh
-rwxrwxrwx 1 oradev dba 4724 Jun 14 09:09 mHotBackupOneDB_Df.sh

this are the messages which i found in the logs of this script

SQL*Plus: Release 9.2.0.6.0 - Production on Sat Jun 14 09:45:00 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> ERROR:
ORA-01031: insufficient privileges


SQL> SQL> SQL> SQL> SQL> SQL> SP2-0640: Not connected
SP2-0734: unknown command beginning "SP2-0640: ..." - rest of line ignored.
SQL> SP2-0640: Not connected
SP2-0734: unknown command beginning "SP2-0640: ..." - rest of line ignored.
SQL> SP2-0640: Not connected
SP2-0734: unknown command beginning "SP2-0640: ..." - rest of line ignored.
SQL> SP2-0640: Not connected
SP2-0734: unknown command beginning "SP2-0640: ..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL> SP2-0640: Not connected
SP2-0734: unknown command beginning "SP2-0640: ..." - rest of line ignored.
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 SP2-0640: Not connected
"HotBackup_DEV_14062008_0945.log" [Read only] [Incomplete last line] 23 lines, 913 characters

######################################################
crontab -l
######################################################
45 9 * * * /dev_ora/test/mHotBackupOneDB_Df.sh

###############END###################################

where should i check for this issue

Regards

Dear Uxinn

any update highly appreciated

Regards

Maooah,

Crontab behaves differently, it executes your jobs without getting your user profile.

Try adding this at the top of your script
or make sure you set path to
oracle bin ( sqlplus )
and set all ${VARIABLES} inside script so it would be $VARIABLES.


Cheers,
Uxinn