|
Backup rem rem Filename: backupsql rem Purpose:Generate script to do a simple online database backup rem Notes:Adjust the copy_cmnd and copy_dest variables and run from rem sqlplus Uncomment last few lines to do the actual backup rem Author: Frank Naude Oracle FAQ rem set serveroutput on set trimspool on set line set head off set feed off spool backupcmd declare copy_cmnd constant varchar() := cp; Use ocopy for NT copy_dest constant varchar() := /backup/; C:\BACKUP\ for NT dbnamevarchar(); logmode varchar(); begin select name log_mode into dbname logmode from sysv_$database; if logmode <> ARCHIVELOG then raise_application_error( ERROR: Database must be in ARCHIVELOG mode!!!); return; end if; dbms_outputput_line(spool backup||dbname|||| to_char(sysdate ddMonyy)||log); Loop through tablespaces for c in (select tablespace_name ts from sysdba_tablespaces) loop dbms_outputput_line(alter tablespace ||cts|| begin backup;); Loop through tablespaces data files for c in (select file_name fil from sysdba_data_files wheretablespace_name = cts) loop dbms_outputput_line(!||copy_cmnd|| ||cfil|| ||copy_dest); end loop; dbms_outputput_line(alter tablespace ||cts|| end backup;); end loop; Backup controlfile and switch logfiles dbms_outputput_line(alter database backup controlfile to trace;); dbms_outputput_line(alter database backup controlfile to |||| copy_dest||control||dbname|||| to_char(sysdateDDMonYYHHMI)||||;); dbms_outputput_line(alter system switch logfile;); dbms_outputput_line(spool off); end; / spool off set head on set feed on set serveroutput off Unremark/uncomment the following line to run the backup script @backupcmd exit End Backup rem rem Filename: end_backupsql rem Purpose:Take database data files out of backup mode rem Notes: Run from SVRMGRL rem Author: Frank Naude Oracle FAQ rem connect internal spool end_backuplog select alter database datafile ||fname|| end backup; from v$datafile f v$backup b where bfile# = ffile# andbstatus = ACTIVE / spool off ! grep ^alter end_backuplog >end_backuplog @end_backuplog ! rm end_bacluplog ! rm end_backuplog exit 或者使用这个sql rem rem Filename: end_backupsql rem Purpose:This script will create a file called end_backup_scriptsql rem and run it to take all tablespaces out of backup mode rem Author: Frank Naude Oracle FAQ rem column cmd format a heading Text set feedback off set headingoff set pagesize spool end_backup_scriptsql select alter tablespace ||atablespace_name|| end backup; cmd from sysdba_data_files a sysv_$backup b wherebstatus = ACTIVE andbfile#= afile_id group by atablespace_name / spool off set feedback on set headingon set pagesize set termouton start end_backup_scriptsql #将所有错误记录到一个table里 rem rem Filename: dberrorsql rem Purpose:Log all database errors to a table rem Oraclei or above/ DBA or CREATE ANY TRIGGER privs/ and remGRANT SELECT ON SYSV_$SESSION required rem Date: Mar rem Author: Nico Booyse () rem drop trigger log_errors_trig; drop table log_errors_tab; create table log_errors_tab ( error varchar() timestamp date usernamevarchar() osuservarchar() machine varchar() process varchar() program varchar()); create or replace trigger log_errors_trig after servererror on database declare var_user varchar(); var_osuser varchar(); var_machinevarchar(); var_processvarchar(); var_programvarchar(); begin select username osuser machine process program into var_user var_osuser var_machine var_process var_program from sysv_$session whereaudsid = userenv(sessionid); insert into log_errors_tab values(dbms_standardserver_error()sysdatevar_user var_osuservar_machinevar_processvar_program); end; / |
