shall 备份oracle脚本 spool,学习笔记:Oracle备份脚本 DBA常用数据库冷备份 热备份脚本命令案例...

天萃荷净

oracle之用户管理备份，汇总运维DBA日常工作所需要的Oracle数据库备份脚本，如：数据库冷备份脚本、数据库热备份脚本命令案例

一、Oracle数据库冷备份脚本

1、shutdown immedate

2、copy datafile/redofile/contorfile/spfile(pfile)/pwdfile

3、startup

--备份脚本(没备份spfile/pwdfile等文件)

sqlplus / as sysdba

set feedback off heading off verify off trimspool off

set pagesize 0 linesize 200

define dir='c:\user'

define ws='c:\user\user.sql'

spool &ws

select 'host copy '||name|| ' &dir' from v$datafile order by 1;

select 'host copy '||name|| ' &dir' from v$controlfile order by 1;

select 'host copy '||name|| ' &dir' from v$tempfile order by 1;

select 'host copy '||member|| ' &dir' from v$logfile order by 1;

select 'host copy '||value' &dir' from v$parameter where name like '%spfile%';--backup spfile

spool off

shutdown immediate

@&ws

startup

二、Oracle数据库热备份脚本

1)备份表空间

alter tablespace tabalepsacename begin backup;

copy

alter tablespace tabalepsacename endbackup;

2)备份整个数据库

alter database begin backup;

copy

alter database end backup;

--脚本备份表空间

set feedback off heading off verify off trimspool off

set pagesize 0 linesize 200

define dir='c:\user'

define ws='c:\user\user.sql'

define ts=&tablespace

alter tablespace &ts begin backup;

spool &ws

select 'host copy '|| file_name || ' &dir' from dba_data_files where tablespace_name =UPPER('&ts');

spool off

@&ws

alter tablespace &ts end backup;

--脚本备份整个数据库

set feedback off heading off verify off trimspool off

set pagesize 0 linesize 200

define dir='c:\user'

define ws='c:\user\user.sql'

alter database begin backup;

spool &ws

select 'host copy '||name|| ' &dir' from v$datafile order by 1;

select 'host copy '||name|| ' &dir' from v$controlfile order by 1;

select 'host copy '||name|| ' &dir' from v$tempfile order by 1;

select 'host copy '||member|| ' &dir' from v$logfile order by 1;

select 'host copy '||value' &dir' from v$parameter where name like '%spfile%';--backup spfile

spool off

@&ws

alter database end backup;

3)备份数据文件

alter datafile 4 begin backup;

copy

alter datafile 4 end backup;

三、查看Oracle backup备份状态

select * from v$backup;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle备份脚本 DBA常用数据库冷备份 热备份脚本命令案例