重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛

标题: 产生oracle手工方式备份的脚本 [打印本页]

作者: 郑全 时间: 2013-12-29 11:56
标题: 产生oracle手工方式备份的脚本

   select 'alter tablespace ' || tablespace_name || ' begin backup;' || chr(10) || chr(13) ||
           '! cp ' || file_name || ' /home/oracle/' || substr(file_name,33) || chr(10) || chr(13) ||
           'alter tablespace ' || tablespace_name || ' end backup;'
   from dba_data_files;

结果如下:

SQL> r

'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'||CHR(10)||CHR(13)||'!CP'||FIL
--------------------------------------------------------------------------------
alter tablespace USERS begin backup;
! cp /u01/app/oracle/oradata/sztech1/users01.dbf /home/oracle/users01.dbf
alter tablespace USERS end backup;

alter tablespace UNDOTBS1 begin backup;
! cp /u01/app/oracle/oradata/sztech1/undotbs01.dbf /home/oracle/undotbs01.dbf
alter tablespace UNDOTBS1 end backup;

alter tablespace SYSAUX begin backup;
! cp /u01/app/oracle/oradata/sztech1/sysaux01.dbf /home/oracle/sysaux01.dbf
alter tablespace SYSAUX end backup;

alter tablespace SYSTEM begin backup;
! cp /u01/app/oracle/oradata/sztech1/system01.dbf /home/oracle/system01.dbf
alter tablespace SYSTEM end backup;

alter tablespace EXAMPLE begin backup;
! cp /u01/app/oracle/oradata/sztech1/example01.dbf /home/oracle/example01.dbf
alter tablespace EXAMPLE end backup;

alter tablespace TBSBC begin backup;
! cp /u01/app/oracle/oradata/sztech1/tbsbc12.dbf /home/oracle/tbsbc12.dbf
alter tablespace TBSBC end backup;

6 rows selected.

[此贴子已经被作者于2013-12-29 12:06:51编辑过]

作者: 郑全 时间: 2013-12-29 11:57
标题: 也可以写成一个sql文件

比如一个sql 文件test.sql:

[oracle@dbserver ~]$ more test.sql
set head off
set echo off
set feedback off
spool /home/oracle/prod.sql
select 'alter tablespace ' || tablespace_name || ' begin backup;' || chr(10) || chr(13) ||
'! cp ' || file_name || ' /home/oracle/' || substr(file_name,33) || chr(10) || chr(13) ||
'alter tablespace ' || tablespace_name || ' end backup;'
from dba_data_files;
spool off
exit

[此贴子已经被作者于2013-12-29 12:07:14编辑过]

作者: 郑全 时间: 2013-12-29 11:59

在执行

$ sqlplus / as sysdba @test.sql

结果如下:

[oracle@dbserver ~]$ ls -ltr prod.sql
total 16472
-rw-r--r-- 1 oracle oinstall 1460 Dec 29 09:35 prod.sql
[oracle@dbserver ~]$

查看结果:

[oracle@dbserver ~]$ more prod.sql

alter tablespace USERS begin backup;
! cp /u01/app/oracle/oradata/sztech1/users01.dbf /home/oracle/users01.dbf
alter tablespace USERS end backup;

alter tablespace UNDOTBS1 begin backup;
! cp /u01/app/oracle/oradata/sztech1/undotbs01.dbf /home/oracle/undotbs01.dbf
alter tablespace UNDOTBS1 end backup;

alter tablespace SYSAUX begin backup;
! cp /u01/app/oracle/oradata/sztech1/sysaux01.dbf /home/oracle/sysaux01.dbf
alter tablespace SYSAUX end backup;

alter tablespace SYSTEM begin backup;

! cp /u01/app/oracle/oradata/sztech1/system01.dbf /home/oracle/system01.dbf
alter tablespace SYSTEM end backup;

alter tablespace EXAMPLE begin backup;
! cp /u01/app/oracle/oradata/sztech1/example01.dbf /home/oracle/example01.dbf
alter tablespace EXAMPLE end backup;

alter tablespace TBSBC begin backup;
! cp /u01/app/oracle/oradata/sztech1/tbsbc12.dbf /home/oracle/tbsbc12.dbf
alter tablespace TBSBC end backup;

[此贴子已经被作者于2013-12-29 12:09:40编辑过]

作者: 郑全 时间: 2013-12-29 12:00
标题: 调用 prod.sql文件进行备份

$ sqlplus / as sysdba @prod.sql

即可.

作者: 郑全 时间: 2013-12-29 12:03
标题: 难点

对每个文件需要开启备份模式,再备份,再关闭备份.

这个通过回车换行 chr(10) chr(13) 来完成,

另外,有列头,有多少行的提示 ,通过 set head off,set feedback off来完成.

欢迎光临重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)