Oracle物理热备份脚本(ZT)

http://hi.baidu.com/edeed/blog/item/0fb9526684b6d327ab184c8c.html

Rem 热备份脚本
Rem 执行该脚本必须保证数据库处于归档模式
Rem 张乐奕 2004-3-24

Rem
Rem 设置SQL*Plus环境参数
Rem

set feedback off
set pagesize 0
set heading off
set verify off
set linesize 100
set trimspool on

Rem 设置需要使用的SQL*Plus用户变量
Rem for Windows
define dir = 'E:\oracle\oradata.bak\hotbak'
define fil = '.\open_backup_commands.sql'
define spo = '&dir\open_backup_output.lst'
define cpy = 'copy'

Rem 设置Linux环境中的SQL*Plus用户变量
Rem define dir = '/oradata/PRACTICE/backup/user/ch05'
Rem define fil = '/tmp/open_backup_commands.sql'
Rem define spo = '&dir/open_backup_output.lst'
Rem define cpy = 'cp'

prompt *** Spooling to &fil

Rem 产生备份命令脚本文件

set serveroutput on
spool &fil
prompt spool &spo
prompt archive log list;;
prompt alter system switch logfile;;
prompt alter system archive log all;;

DECLARE
CURSOR cur_tablespace IS
SELECT tablespace_name
FROM dba_tablespaces;

CURSOR cur_datafile (tn VARCHAR) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = tn;
BEGIN
FOR ct IN cur_tablespace LOOP
IF ct.tablespace_name!='TEMP' then
dbms_output.put_line ('alter tablespace '||ct.tablespace_name||' begin backup;');
FOR cd IN cur_datafile (ct.tablespace_name) LOOP
dbms_output.put_line ('host &cpy '||cd.file_name||' &dir');
END LOOP;
dbms_output.put_line ('alter tablespace '||ct.tablespace_name||' end backup;');
end if;
END LOOP;

END;
/

prompt alter system switch logfile;;
prompt alter database backup controlfile to '&dir\backcontrol.ctl' REUSE;;

prompt archive log list;;
prompt spool off
spool off;

Rem 执行生成的脚本文件

@&fil

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-730893/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17252115/viewspace-730893/