oracle冷备份 代码,Oracle冷备份和恢复自动产生sql源代码

以下是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

阅读(161) | 评论(0) | 转发(0) |