分享
 
 
 

简单在线备份 script

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

dbnamevarchar2(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

wheretablespace_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#

andb.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 headingoff

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

whereb.status = 'ACTIVE'

andb.file#= a.file_id

group by a.tablespace_name

/

spool off

set feedback on

set headingon

set pagesize 24

set termouton

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,

usernamevarchar2(30),

osuservarchar2(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_machinevarchar2(64);

var_processvarchar2(8);

var_programvarchar2(48);

begin

select username, osuser, machine, process, program

into var_user, var_osuser, var_machine, var_process, var_program

from sys.v_$session

whereaudsid = 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;

/

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有