[编写]自制的一个自动备份数据文件脚本

txfy

经过大家的帮助，终于写好了这个自动备份数据文件的脚本，脚本特点是自动备份一个数据库中的数据文件，不需要知道数据库有些什么表空间，或者数据文件，脚本编写的还很不美观，先用着，慢慢美化吧，另外archive备份部分也暂时没有备，仓促编程，有什么问题请指正！
注意本脚本是应用于archive log模式下的，另外数据库的用户名密码，自己在脚本里改一下，脚本是带参数的，参数是备份的目标目录，注意要有写权限。#!/bin/sh
mypath=$1
if [ -z $mypath ] ; then
echo 'Please Use This Command Like ./backup /u01/backup'
else
if [ -d $mypath ] ; then
ORACLE_SID=dzzd; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
rm -Rf aa.txt
sqlplus -s  "system/oracle9i"<<EOF
set head off
set feedback off
spool aa.txt
select tablespace_name from dba_tablespaces;
spool off
EXIT
EOF
for spacename in `cat aa.txt`
do
echo $spacename |grep  "^TEMP"
if [ "$?" -eq "0" ]
then
echo TEMP no need backup else
else
echo begin backup $spacename
sqlplus -s system/oracle9i<<SETSTA
alter tablespace $spacename begin backup;
EXIT
SETSTA
rm -Rf bb.txt
sqlplus -s system/oracle9i<<DATAFILE1
set head off
set feedback off
spool bb.txt
SELECT 'cp '||file_name||' $mypath'||SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1,1))
     FROM dba_data_files where tablespace_name=trim('$spacename');
spool off
EXIT
DATAFILE1
sh bb.txt
sqlplus -s system/oracle9i<<SETSTA
alter tablespace $spacename end backup;
EXIT
SETSTA
echo end backup $spacename
fi
done
else
echo "lease Input a Right Direction"
fi
fi

mhere

好！
能不能有一个纯文本的附件？

txfy

不能上传文本文件，把内容沾出来放到backup文件中，chmod 755 backup, 就可以运行了 ./backup /u01

steal

请教一下：
我用
sql>;set head off
sql>;set feedback off
sql>;spool a.txt
sql>;SELECT 'cp '||file_name||' $mypath'||SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1,1)) FROM dba_data_files where tablespace_name=trim('$spacename');
sql>;spol off

但是在a.txt中还是有
SELECT ...........
spool off
等内容
不能直接sh 运行啊？

txfy

要整个执行前面的
sqlplus -s "system/oracle9i"<<EOF 会保证不出问题的！你整个执行一次看看！

txfy

版本更新：增加了如果不是archivelog模式自动退出，命令使用格式提示，错误命令格式自动退出，以及错误提示等方面，只需将这个脚本放到一个文件例如backup中，并chmod 755 backup,然后用./backup <dir>;就可以了（dir为你要备份到的目录）。
#!/bin/sh
ORACLE_SID=dzzd; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
rm -Rf arch.txt
sqlplus -s "/ as sysdba"  <<ARCH
set head off
set feedback off
spool arch.txt
select log_mode from v\$database;
spool off
EXIT
ARCH
for archstat in `cat arch.txt`
do
echo $archstat |grep  "^ARCHIVELOG"
if [ "$?" -eq "0" ] ; then
mypath=$1
if [ -z $mypath ] ; then
echo 'ERROR:        Please Use This Command Like ./backup /u01/backup'
else
rm -Rf direc.txt
sqlplus -s "/ as sysdba" <<dire
set head off
set feedback off
spool direc.txt
select '$mypath' from dual where trim('$mypath') like '%/';
spool off
exit
dire
if [ -s direc.txt ] ; then
echo 'ERRORlease Do Not Add / At The End Of The Direcotry!'
exit
fi
if [ -d $mypath ] ; then
rm -Rf aa.txt
sqlplus -s  "/ as sysdba"<<EOF
set head off
set feedback off
spool aa.txt
select tablespace_name from dba_tablespaces;
spool off
EXIT
EOF
for spacename in `cat aa.txt`
do
echo $spacename |grep  "^TEMP"
if [ "$?" -eq "0" ]
then
echo TEMP no need backup else
else
echo "---------------------------------------------------------------------------------------------"
echo Begin Backup $spacename
sqlplus -s "/ as sysdba"<<SETSTA
alter tablespace $spacename begin backup;
EXIT
SETSTA
rm -Rf bb.txt
sqlplus -s "/ as sysdba"<<DATAFILE1
set head off
spool bb.txt
SELECT 'cp '||file_name||' $mypath'||SUBSTR(FILE_NAME,INSTR(FILE_NAME,'/',-1,1))
     FROM dba_data_files where tablespace_name=trim('$spacename');
spool off
EXIT
DATAFILE1
sh bb.txt
sqlplus -s "/ as sysdba"<<SETSTA
alter tablespace $spacename end backup;
EXIT
SETSTA
echo NOTES:   End Backup $spacename
fi
done
echo "---------------------------------------------------------------------------------------------"
echo "NOTES: Begin Backup ControlFile!"
rm -Rf $mypath/control.dbf
sqlplus -s  "/ as sysdba"<<BACKCON
set head off
set feedback off
alter database backup controlfile to '$mypath/control.dbf';
EXIT
BACKCON
echo "NOTES: End Backup ControlFile!"
echo "---------------------------------------------------------------------------------------------"
echo "NOTES: Begin Backup ArchiveLogFile!"
rm -Rf $mypath/arc
mkdir $mypath/arc
rm -Rf arc1.txt
sqlplus -s "/ as sysdba"<<ARC
set head off
set feedback off
spool arc1.txt
alter system switch logfile;
SELECT 'cp '||name||' $mypath/arc'||SUBSTR(NAME,INSTR(NAME,'/',-1,1))
     FROM v\$archived_log where status!='D' and status!='X';
spool off
EXIT
ARC
sh arc1.txt
echo "NOTES:      End Backup ArchiveLogFiel!"
else
echo "ERROR:     Please Input a Right Direction!"
fi
fi
else
echo "ERROR:    Your Database Is Not Run At Archive Mod!"
fi
done

zhoujin0901

sqlplus -s  "system/manager"<<EOF

系统提示`<' is not matched
为什么？？？

RobinWang

for spacename in `cat aa.txt`

系统提示错误，能给说一下吗？