LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Enterprise Linux Forums > Linux - Enterprise
User Name
Password
Linux - Enterprise This forum is for all items relating to using Linux in the Enterprise.

Notices

Reply
 
Search this Thread
Old 06-09-2008, 09:43 AM   #1
maooah
Member
 
Registered: Dec 2007
Posts: 44

Rep: Reputation: 15
backup script for database


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
$SQLUSR
set 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
 
Old 06-09-2008, 05:36 PM   #2
Uxinn
Member
 
Registered: May 2008
Location: Iceland
Distribution: Ubuntu Hardy
Posts: 47

Rep: Reputation: 16
bad spaces mostly

Code:
#!/bin/bash

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
$SQLUSR
select * from dual;
set serverout on
set echo off
set pause off
set feed off
EOF
 
Old 06-10-2008, 04:13 AM   #3
maooah
Member
 
Registered: Dec 2007
Posts: 44

Original Poster
Rep: Reputation: 15
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
 
Old 06-10-2008, 04:46 AM   #4
Uxinn
Member
 
Registered: May 2008
Location: Iceland
Distribution: Ubuntu Hardy
Posts: 47

Rep: Reputation: 16
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


Quote:
${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
 
Old 06-11-2008, 06:01 AM   #5
maooah
Member
 
Registered: Dec 2007
Posts: 44

Original Poster
Rep: Reputation: 15
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
 
Old 06-11-2008, 08:04 AM   #6
maooah
Member
 
Registered: Dec 2007
Posts: 44

Original Poster
Rep: Reputation: 15
Dear Unixx

waiting for your valuable and accurate reply

Best regards
 
Old 06-12-2008, 04:01 AM   #7
Uxinn
Member
 
Registered: May 2008
Location: Iceland
Distribution: Ubuntu Hardy
Posts: 47

Rep: Reputation: 16
Quote:
Originally Posted by maooah View Post

# 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
WHENEVER SQLERROR EXIT 1
@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
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
 
Old 06-14-2008, 02:05 AM   #8
maooah
Member
 
Registered: Dec 2007
Posts: 44

Original Poster
Rep: Reputation: 15
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
 
Old 06-15-2008, 01:48 AM   #9
maooah
Member
 
Registered: Dec 2007
Posts: 44

Original Poster
Rep: Reputation: 15
Dear Uxinn

any update highly appreciated

Regards
 
Old 06-15-2008, 09:58 AM   #10
Uxinn
Member
 
Registered: May 2008
Location: Iceland
Distribution: Ubuntu Hardy
Posts: 47

Rep: Reputation: 16
Maooah,

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

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


Cheers,
Uxinn

Last edited by Uxinn; 06-15-2008 at 10:10 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shell Script: want to insert values in database when update script runs ring Programming 2 10-25-2007 10:48 PM
how to backup urpmi database davidleroux1 Mandriva 1 02-04-2006 04:16 PM
SQLite database backup ddpicard Linux - Software 2 11-01-2004 06:54 PM
automate database backup Stormproof Linux - Networking 1 10-19-2004 03:19 AM
Need script to dump database, ftp file to backup server glock19 Linux - Networking 17 05-12-2003 05:56 PM


All times are GMT -5. The time now is 03:13 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration