当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

简单在线备份 script


  Backup
  
  rem -----------------------------------------------------------------------
  rem Filename:  backup.sql
  rem Purpose:  Generate script to do a simple on-line 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 500
  set head off
  set feed off
  spool backup.cmd
  declare
  copy_cmnd constant varchar2(30) := 'cp';    -- Use "ocopy" for NT
  copy_dest constant varchar2(30) := '/backup/'; -- C:\BACKUP\ for NT
  dbname varchar2(30);
  logmode varchar2(30);
  begin
  select name, log_mode
  into  dbname, logmode
  from  sys.v_$database;
  if logmode <> 'ARCHIVELOG' then
  raise_application_error(-20000,
  'ERROR: Database must be in ARCHIVELOG mode!!!');
  return;
  end if;
  dbms_output.put_line('spool backup.'||dbname||'.'||
  to_char(sysdate, 'ddMonyy')||'.log');
  -- Loop through tablespaces
  for c1 in (select tablespace_name ts
  from  sys.dba_tablespaces)
  loop
  dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
  -- Loop through tablespaces' data files
  for c2 in (select file_name fil
  from  sys.dba_data_files
  where tablespace_name = c1.ts)
  loop
  dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
  end loop;
  dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
  end loop;
  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
  copy_dest||'control.'||dbname||'.'||
  to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_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
  -- @backup.cmd
  -- exit
  
  End Backup
  
  rem -----------------------------------------------------------------------
  rem Filename:  end_backup2.sql
  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_backup2.log
  select 'alter database datafile '''||f.name||''' end backup;'
  from v$datafile f, v$backup b
  where b.file# = f.file#
  and b.status = 'ACTIVE'
  /
  spool off
  ! grep '^alter' end_backup2.log >end_backup2.log2
  @end_backup2.log2
  ! rm end_baclup.log
  ! rm end_backup.log2
  exit
  
  或者使用这个sql
  rem -----------------------------------------------------------------------
  rem Filename:  end_backup.sql
  rem Purpose:  This script will create a file called end_backup_script.sql
  rem       and run it to take all tablespaces out of backup mode.
  rem Author:   Frank Naude, Oracle FAQ
  rem -----------------------------------------------------------------------
  column cmd format a80 heading "Text"
  set feedback off
  set heading off
  set pagesize 0
  spool end_backup_script.sql
  select  'alter tablespace '||a.tablespace_name||' end backup;' cmd
  from   sys.dba_data_files a, sys.v_$backup b
  where  b.status = 'ACTIVE'
  and   b.file# = a.file_id
  group by a.tablespace_name
  /
  spool off
  set feedback on
  set heading on
  set pagesize 24
  set termout on
  start end_backup_script.sql
  
  #将所有错误记录到一个table里
  rem -----------------------------------------------------------------------
  rem Filename:  db-error.sql
  rem Purpose:  Log all database errors to a table
  rem       Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and
  rem GRANT SELECT ON SYS.V_$SESSION required
  rem Date:    21-Mar-2000
  rem Author:   Nico Booyse ([email protected])
  rem -----------------------------------------------------------------------
  drop trigger log_errors_trig;
  drop table  log_errors_tab;
  create table log_errors_tab (
  error   varchar2(30),
  timestamp date,
  username varchar2(30),
  osuser  varchar2(30),
  machine  varchar2(64),
  process  varchar2(8),
  program  varchar2(48));
  create or replace trigger log_errors_trig
  after servererror on database
  declare
  var_user   varchar2(30);
  var_osuser  varchar2(30);
  var_machine varchar2(64);
  var_process varchar2(8);
  var_program varchar2(48);
  begin
  select username, osuser, machine, process, program
  into  var_user, var_osuser, var_machine, var_process, var_program
  from  sys.v_$session
  where audsid = userenv('sessionid');
  insert into log_errors_tab
  values(dbms_standard.server_error(1),sysdate,var_user,
  var_osuser,var_machine,var_process,var_program);
  end;
  /
相关内容
赞助商链接