Oracle热备份和恢复自动产生sql源代码
各变量含义为：
HOT_BACK_DIR： 热备份文件存放目录
SCRIPT_FILE： 此代码所生成的批处理文件路径及文件名称
LOG_FILE： 备份日志文件存放路径及文件名称
INT_PWD： internal用户的口令
以下代码存成sql文件后
可以通过COMMAND ：sqlplus internal/oracle@sqcdb @产生备份SQL代码的原文件名 来运行。
热备份SQL源代码如下：
define HOT_BACK_DIR = e:\oradb\hotbackups
define SCRIPT_FILE = e:\back\hotbackup.sql
define LOG_FILE= e:\back\hotbackup.log
define INT_PWD = oracle
/* Create Temporary Table with Tablespace Data */
drop table dsc_hot_stage;
create table dsc_hot_stage
(tablespace_name varchar2(30),
file_name varchar2(200)
);
insert into dsc_hot_stage
select rtrim(tablespace_name),rtrim(file_name) from sys.dba_data_files;
/**** Set up the parameters for the spool file */
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set termout oN
set echo off
spool &SCRIPT_FILE
select '/* ',
'This is the hot backup script and has been generated',
' by gen_hotbk.sql',
' ' to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') ,
' */'
from dual;
-- select 'connect internal/&INT_PWD''@sqcdb.sqc.com' from dual;
select '$md e:\oradb' from dual;
select '$md e:\oradb\hotbackups' from dual;
select '$md e:\oradb\hotbackups\ctrl' from dual;
select '$md e:\oradb\hotbackups\archive' from dual;
-- select '$md e:\oradb\hotbackups\ora' from dual;
-- Create Script to backup Control File to Trace
select 'alter database backup controlfile to trace; ' from dual;
select 'alter database backup controlfile to &HOT_BACK_DIR\CTRL\ctrsqcdb.ctl; ' from dual;
-- Create Script to backup Archive File
-- select 'alter system archive log all;' from dual;
select 'alter system archive log current;' from dual;
-- Create Script to backup actual data files to a Directory
select 'alter tablespace ' tablespace_name' begin backup;' c1 ,'$copy 'file_name' &HOT_BACK_DIR''\'substr(file_name,instr(rtrim(file_name),'\',-1,1)+1,length(rtrim(file_name))) c2, 'alter tablespace ' tablespace_name' end backup;' c3 from dsc_hot_stage
group by tablespace_name,file_name order by 1;
-- Create Script to backup Init.ora files
--select '$copy c:\oracle\admin\sqcdb\pfile\init.ora --&HOT_BACK_DIR\ora'' /y' from dual;
-- Create Script to backup Net Config files
--select '$copy C:\Oracle\Ora81\NETWORK\ADMIN\*.ora --&HOT_BACK_DIR\ora'' /y'from dual;
select 'spool &LOG_FILE ' from dual;
select 'prompt ' ,'COMPLETE HOT BACKUP DATABASE SQCDB ON ' ,to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
select 'spool off' from dual;
select 'exit' from dual;
spool off
@&SCRIPT_FILE
$del &SCRIPT_FILE
exit
热恢复SQL源代码如下
define HOT_BACK_DIR = e:\oradb\hotbackups
define SCRIPT_FILE = e:\back\hotrev.bat
define LOG_FILE= e:\back\hotrev.log
define INT_PWD = oracle
/* Create Temporary Table with Tablespace Data */
drop table dsc_hot_stage;
create table dsc_hot_stage
(
tablespace_name varchar2(30),
file_name varchar2(200)
);
insert into dsc_hot_stage
select rtrim(tablespace_name),rtrim(file_name) from sys.dba_data_files;
/**** Set up the parameters for the spool file */
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set termout oN
set echo off
set pages 0 feed off echo off time off
col a new_value b
col c new_value d
select value a,to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') c from v$parameter where name ='db_name';
spool &SCRIPT_FILE
select 'rem','This is the hot recover script and has been generated',' by gen_hotrev.sql'' on &d' from dual;
prompt set ShutDownFile=C:\ShutDown.SQL
prompt echo connect internal/&INT_PWD %ShutDownFile%
prompt echo shutdown immediate %ShutDownFile%
prompt echo startup %ShutDownFile%
prompt echo shutdown normal %ShutDownFile%
prompt echo exit %ShutDownFile%
prompt
prompt rem ***** SHUTDOWN THE DATABASE *******
prompt set oracle_sid=&b
prompt svrmgrl @%ShutDownFile%
-- Create Script to Recover actual data files to original directory
select 'copy ''&HOT_BACK_DIR''\' substr(file_name,instr(rtrim(file_name),'\',-1,1)+1,length(rtrim(file_name)))' ' file_name from dsc_hot_stage;
prompt set RevFile=C:\hotrev.SQL
prompt echo connect internal/&INT_PWD %RevFile%
prompt echo startup mount %RevFile%
prompt echo set autorecovery on %RevFile%
prompt echo recover database %RevFile%
prompt echo alter database open; %RevFile%
prompt rem ***** RECOVER THE DATABASE *******
prompt svrmgrl @%RevFile%
prompt
prompt del /Q %RevFile%
prompt set LogFile=&LOG_FILE
prompt echo COMPLETE HOT RECOVER FOR sqcdb DATABASE STARTED ON &d ... %logFile%
prompt rem exit
spool off
$&SCRIPT_FILE
$del &SCRIPT_FILE
