熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

簡單在線備份 script

2013-11-13 16:00:42  來源: Oracle 

  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
  dbname varchar();
  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
  where tablespace_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#
  and bstatus = 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 heading off
  set pagesize
  spool end_backup_scriptsql
  select  alter tablespace ||atablespace_name|| end backup; cmd
  from   sysdba_data_files a sysv_$backup b
  where  bstatus = ACTIVE
  and   bfile# = afile_id
  group by atablespace_name
  /
  spool off
  set feedback on
  set heading on
  set pagesize
  set termout on
  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
  rem GRANT 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
  username varchar()
  osuser  varchar()
  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_machine varchar();
  var_process varchar();
  var_program varchar();
  begin
  select username osuser machine process program
  into  var_user var_osuser var_machine var_process var_program
  from  sysv_$session
  where audsid = userenv(sessionid);
  insert into log_errors_tab
  values(dbms_standardserver_error()sysdatevar_user
  var_osuservar_machinevar_processvar_program);
  end;
  /
From:http://tw.wingwit.com/Article/program/Oracle/201311/17669.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.