Oracle熱備份和恢復(fù)自動產(chǎn)生sql源代碼
各變量含義為：
HOT_BACK_DIR： 熱備份文件存放目錄
SCRipT_FILE： 此代碼所生成的批處理文件路徑及文件名稱
LOG_FILE： 備份日志文件存放路徑及文件名稱
INT_PWD： internal用戶的口令
以下代碼存成sql文件后
可以通過COMMAND ：sqlplus internal/oracle@sqcdb @產(chǎn)生備份SQL代碼的原文件名 <參數(shù)> 來運行。
熱備份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
熱恢復(fù)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
