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

不念不忘少年蓝@ 2022-10-21 01:11 128阅读 0赞

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

各变量含义为：

HOT\_BACK\_DIR： 热备份文件存放目录

SCRIPT\_FILE： 此代码所生成的批处理文件路径及文件名称

LOG\_FILE： 备份日志文件存放路径及文件名称

INT\_PWD： internal用户的口令

以下代码存成sql文件后

可以通过COMMAND ：sqlplus @产生备份SQL代码的原文件名 来运行。

热备份SQL源代码如下：

define HOT\_BACK\_DIR = e:oradbhotbackups

define SCRIPT\_FILE = e:backhotbackup.sql

define LOG\_FILE= e:backhotbackup.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 from dual;

select '$md e:oradb' from dual;

select '$md e:oradbhotbackups' from dual;

select '$md e:oradbhotbackupsctrl' from dual;

select '$md e:oradbhotbackupsarchive' from dual;

\-- select '$md e:oradbhotbackupsora' 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\_DIRCTRLctrsqcdb.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:oracleadminsqcdbpfileinit.ora --&HOT\_BACK\_DIRora'||' /y' from dual;

\-- Create Script to backup Net Config files

\--select '$copy C:OracleOra81NETWORKADMIN\*.ora --&HOT\_BACK\_DIRora'||' /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:oradbhotbackups

define SCRIPT\_FILE = e:backhotrev.bat

define LOG\_FILE= e:backhotrev.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%