oracle冷備份和恢復自動產生sql源代碼
2024-08-29 13:38:41
供稿：網友
以下是Oracle冷備份及恢復自動產生sql源代碼
各變量含義為：
COLD_BACK_DIR： 冷備份目錄
SCRipT_FILE： 此代碼所生成的批處理文件路徑及文件名稱
LOG_FILE： 備份日志文件存放路徑及文件名稱
INT_PWD： internal用戶的口令
以下代碼存成sql文件后
可以通過COMMAND ：sqlplus internal/oracle@sqcdb @產生備份SQL代碼的原文件名 <參數> 來運行。
冷備份SQL源代碼如下：
--You must have select PRivileges on the v$parameter
--v$logfile v$datafile and v$controlfile data
--dictionary views belonging to SYS to run this program
define COLD_BACK_DIR = e:/oradb/coldbackups
define SCRIPT_FILE = e:/back/coldbackup.bat
define LOG_FILE= e:/back/coldbackup.log
define INT_PWD = oracle
set pages 0 feed off echo off time off
set verify 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
prompt rem ***** COLDBACKUP ORACLE DATABASE FOR &b ON WINDOWS NT ON &d*****
prompt
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%
prompt
-- prompt net stop OracleStart%oracle_sid%
prompt net stop OracleService%oracle_sid%
prompt
prompt rem ***** SET BACKUP FILES DirectorY *****
prompt md e:/oradb
prompt md e:/oradb/coldbackups
prompt
prompt rem ***** COPY FILES *****
prompt rem Redo logs
prompt
select 'copy 'member' &COLD_BACK_DIR /y' from v$logfile;
prompt
prompt rem Datafile;
prompt
select 'copy 'name' &COLD_BACK_DIR /y' from v$datafile;
prompt
prompt rem Control files
prompt
select 'copy 'name' &COLD_BACK_DIR /y' from v$controlfile;
prompt
prompt rem Init.ora files
prompt
select 'copy c:/oracle/admin/sqcdb/pfile/init.ora ''&COLD_BACK_DIR /y' from dual;
prompt
prompt rem Net Config files
prompt
select 'copy C:/Oracle/Ora81/NETWORK/ADMIN/*.ora ''&COLD_BACK_DIR /y'from dual;
prompt
prompt rem *********START UP DATABASE AND SERVICES********
prompt net start OracleService%oracle_sid%
-- prompt net start OracleStart%oracle_sid%
prompt
prompt set StartFile=C:/start.SQL
prompt echo connect internal/&INT_PWD > %StartFile%
prompt echo startup >> %StartFile%
prompt echo exit >> %StartFile%
prompt
prompt svrmgrl @%StartFile%
prompt
prompt del /Q %ShutDownFile%
prompt del /Q %StartFile%
prompt set LogFile=&LOG_FILE
prompt echo COMPLETE COLD BACKUP FOR "&b" DATABASE STARTED ON &d ...> %logFile%
prompt exit
spool off
$&SCRIPT_FILE
$del &SCRIPT_FILE
exit
冷恢復SQL源代碼如下：
--You must have select privileges on the v$parameter
--v$logfile v$datafile and v$controlfile data
--dictionary views belonging to SYS to run this program
define COLD_BACK_DIR = e:/oradb/coldbackups
define SCRIPT_FILE = e:/back/coldrecover.bat
define LOG_FILE= e:/back/coldcov.log
define INT_PWD = oracle
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
prompt rem ***** COLDRECOVER ORACLE DATABASE FOR &b ON WINDOWS NT ON &d*****
prompt
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%
prompt
-- prompt net stop OracleStart%oracle_sid%
prompt net stop OracleService%oracle_sid%
prompt
prompt rem ***** SET BACKUP FILES DIRECTORY *****
prompt cd &COLD_BACK_DIR
prompt
prompt rem ***** COPY FILES *****
-- select 'xcopy e:/oradb/backups /E /V 'substr(member,1,length(member)-11) from v$logfile
-- where group#=1;
prompt rem Redo logs
prompt
select 'copy 'substr(member,instr(rtrim(member),'/',-1,1)+1,length(rtrim(member)))' 'member from v$logfile;
prompt
prompt rem Datafile
prompt
select 'copy 'substr(name,instr(rtrim(name),'/',-1,1)+1,length(rtrim(name)))' 'name from v$datafile;
prompt
prompt rem Control files
prompt
select 'copy 'substr(name,instr(rtrim(name),'/',-1,1)+1,length(rtrim(name)))' 'name from v$controlfile;
prompt
prompt rem Init.ora files
prompt
select 'rename c:/oracle/admin/sqcdb/pfile/init.ora init.ora.bak' from dual;
select 'copy init.ora c:/oracle/admin/sqcdb/pfile ' from dual;
prompt
prompt rem Net Config files
prompt
select 'rename C:/Oracle/Ora81/NETWORK/ADMIN/*.ora *.ora.bak' from dual;
select 'copy *.ora C:/Oracle/Ora81/NETWORK/ADMIN 'from dual;
prompt
prompt rem *********START UP DATABASE AND SERVICES********
prompt net start OracleService%oracle_sid%
-- prompt net start OracleStart%oracle_sid%
prompt
prompt set StartFile=C:/start.SQL
prompt echo connect internal/&INT_PWD > %StartFile%
prompt echo startup >> %StartFile%
prompt echo exit >> %StartFile%
prompt
prompt svrmgrl @%StartFile%
prompt
prompt del /Q %ShutDownFile%
prompt del /Q %StartFile%
prompt set LogFile=&LOG_FILE
prompt echo COMPLETE COLD RECOVER FOR "&b" DATABASE ON &d ...> %logFile%
prompt exit
spool off
$&SCRIPT_FILE
$del &SCRIPT_FILE
exit
