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

热备份和恢复自动产生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

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