产生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编辑过]

郑全

比如 一个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编辑过]

郑全

在执行

 

$ 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编辑过]

郑全

 

 

$ sqlplus / as sysdba @prod.sql

 

即可.

郑全

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

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

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