某台windows服务器,由于杀毒软件设置问题,导致每天凌晨都会锁定controlfile/datafile,导致数据库crash或者某些文件处于recover状态;由于公司流程繁琐,通知系统管理员禁掉杀毒软件需要时间比较长,所以就考虑写一个job,在凌晨时候定时检查数据库状态,若已经crash则重启;若有数据文件离线则对其recover;
注:oracle的动态语句不支持execute immediate 'recover datafile '||file#,所以需要改写一下语句(在此需感谢maclean的帮助 http://t.askmaclean.com/thread-1020-1-1.html);整体job如下
注:oracle的动态语句不支持execute immediate 'recover datafile '||file#,所以需要改写一下语句(在此需感谢maclean的帮助 http://t.askmaclean.com/thread-1020-1-1.html);整体job如下
--启动数据库
Dbstart.bat
set ORACLE_SID=DBSTART
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
Dbstart.bat
set ORACLE_SID=DBSTART
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
sqlplus "/nolog" @db_start.sql > dbstart.log
--因为oracle启动后再尝试startup只会报错,并不会影响现有程序,故不需判断数据库当前状态,直接startup即可
dbstart.sql
conn sys/oracle as sysdba
@startup
Exit
--
Startup.sql
startup
--因为oracle启动后再尝试startup只会报错,并不会影响现有程序,故不需判断数据库当前状态,直接startup即可
dbstart.sql
conn sys/oracle as sysdba
@startup
Exit
--
Startup.sql
startup
--恢复数据文件
set ORACLE_SID=DBSTART
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
set ORACLE_SID=DBSTART
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
sqlplus "/nolog" @db_recover.sql
--
db_recover.sql
connect sys/oracle as sysdba
set echo off
set veri off
set feedback off
set termout on
set heading off
set linesize 1500
set termout off
spool recovery_generate.sql
select 'recover datafile '||FILE#||' ;' from v$recover_file;
spool off
set termout on;
set echo on
@recovery_generate
@db_online
--
Db_online.sql
begin
for i in (select file# from v$datafile where status='OFFLINE') loop
execute immediate 'alter database datafile '||i.file#||' online';
end loop;
end;
/
--
db_recover.sql
connect sys/oracle as sysdba
set echo off
set veri off
set feedback off
set termout on
set heading off
set linesize 1500
set termout off
spool recovery_generate.sql
select 'recover datafile '||FILE#||' ;' from v$recover_file;
spool off
set termout on;
set echo on
@recovery_generate
@db_online
--
Db_online.sql
begin
for i in (select file# from v$datafile where status='OFFLINE') loop
execute immediate 'alter database datafile '||i.file#||' online';
end loop;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-730459/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-730459/