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
remGRANT SELECT ON SYS.V_$SESSION required
rem Date:
21-Mar-2000
rem Author:
Nico Booyse (booysen@saps.org)
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;
/