oracle备份之备份测试脚本的方法(冷备、热备、rman)

1、数据库环境

数据库DBID及打开模式

SQL> select dbid,open_mode from v$database; 
DBID OPEN_MODE
---------- ----------
4106451204 READ WRITE

数据文件:

SQL> select file#,status,enabled,name from v$datafile;

FILE# STATUS ENABLED NAME
---------- ------- ---------- --------------------------------------------------------------------------------
1 SYSTEM READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf
2>
SQL> select status,name from v$controlfile;

STATUS NAME
------- --------------------------------------------------------------------------------
/u01/app/oracle/ocmdb/cfile/ora_control1
/u01/app/oracle/ocmdb/cfile/ora_control2
/u01/app/oracle/ocmdb/cfile/control3

联机日志:

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
1>2、数据库备份脚本

冷备份脚本

#rem script:coldbak.sh
#rem creater:chon
#rem date:2011
#rem desc:offline full backup database

#--connect database
sqlplus / as sysdba <<EOF
#--shutdown database
shutdown immediate;
#--Copy Data file
!cp /u01/app/oracle/oradata/ocmdb/dfile/*.dbf /backup
!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup
#--Copy Control file
!cp /u01/app/oracle/ocmdb/cfile/* /backup
#--Copy Log file
!cp /u01/app/oracle/oradata/ocmdb/lfile/*.log /backup
#--Copy archive log
!cp -R /u01/app/oracle/rmanbak/OCMDB/archivelog/* /backup
#--startup database
startup;

说明:
1、以上脚本在数据库关闭状态下备份数据库所有的数据文件,联机日志,控制文件,归档日志(在一个目录下),如果成功备份,所有文件是一致的。
2、没有备份参数文件,参数文件可以另外备份,没有必要每次都备份,只需要在改变设置后备份一次。
3、如果以上命令没有成功依次执行,那么备份将是无效的,如连接数据库不成功,那么肯定关闭数据库也不成功,那么备份则无效
4、冷备份建议下人工干预下执行。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

OS热备份
查看表空间和数据文件对应关系

SQL> select file_id,tablespace_name,file_name from dba_data_files;

FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ --------------------------------------------------------------------------------
7 RMAN_TS /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf
6 BIG_TBS /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf
5 TBS3 /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf
4 TBS_1 /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf
3 SYSAUX /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf
2 UNDOTBS /u01/app/oracle/oradata/ocmdb/undotbs01.dbf
1 SYSTEM /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf

数据库OS热全备份脚本

#rem script:hotbak.sh
#rem creater:chon
#rem date:2011
#rem desc:backup all database datafile in archive

#--connect database
sqlplus / as sysdba <<EOF

#--archive
alter system archive log current;

#--start hotbak
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf /backup
alter tablespace system end backup;

alter tablespace UNDOTBS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup
alter tablespace UNDOTBS end backup;

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

alter tablespace TBS_1 begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf /backup
alter tablespace TBS_1 end backup;

alter tablespace TBS3 begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf /backup
alter tablespace TBS3 end backup;

alter tablespace BIG_TBS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf /backup
alter tablespace BIG_TBS end backup;

alter tablespace RMAN_TS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf /backup
alter tablespace RMAN_TS end backup;
#--end

#--bak control file
#--binary
alter database backup controlfile to '/backup/controlbinbak.000';
#--ascii
alter database backup controlfile to trace;

alter system archive log current;

说明:
1、热备份必须在数据库归档方式下才可以运行
2、以上脚本可以在数据库运行状态下备份数据库所有的数据文件(除了临时数据文件),没有必要备份联机日志。
3、归档日志至少需要一次完整备份之后的所有日志。

oracle备份之备份测试脚本的方法(冷备、热备、rman)

扫一扫手机访问