oracle执行脚本文件的命令_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.dbf2 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/undotbs01.dbf3 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf4 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf5 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf6 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf7 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf

控制文件：

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 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo01.log1 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo11.log3 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo03.log2 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo02.log5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo05.log5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo55.log4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo04.log4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo44.log

2、数据库备份脚本

冷备份脚本

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

#--connect databasesqlplus / as sysdba <<EOF#--shutdown databaseshutdown 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 databasestartup;

说明：
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.dbf6 BIG_TBS /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf5 TBS3 /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf4 TBS_1 /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf3 SYSAUX /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf2 UNDOTBS /u01/app/oracle/oradata/ocmdb/undotbs01.dbf1 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 databasesqlplus / as sysdba <<EOF

#--archivealter system archive log current;

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

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

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

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

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

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

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

#--bak control file#--binaryalter database backup controlfile to '/backup/controlbinbak.000';#--asciialter database backup controlfile to trace;

alter system archive log current;

说明：
1、热备份必须在数据库归档方式下才可以运行
2、以上脚本可以在数据库运行状态下备份数据库所有的数据文件（除了临时数据文件），没有必要备份联机日志。
3、归档日志至少需要一次完整备份之后的所有日志。
4、如果以上命令没有成功依次执行，那么备份也是无效的，如连接数据库不成功，那么备份则无效

—————————————————————————————————————————————————————————————————————————

RMAN备份脚本
–本地

创建文件夹

mkdir /home/oracle/backupmkdir /home/oracle/backup/scriptmkdir /home/oracle/backup/logmkdir /home/oracle/backup/export

–备份目的地

mkdir /home/oracle/rman_backup

创建备份表空间

create tablespace rman_tbs datafile '/oradata/luke/rman_tbs01.dbf' size 1024M;

创建备份用户

create user rman identified by rman default tablespace rman_tbs temporary tablespace temp;

grant connect,resource ,recovery_catalog_owner to rman;

rman catalog rman/rmancreate catalog tablespace rman_tbs;connect target sys/lukewhx@lukeregister database;report schema;

configure retention policy to redundancy 2;configure retention policy to recovery window of 7 days;

--倒出RMAN用户数据脚本exp_rman.par (即备份catalog库）userid=rman/rmanfile=/home/oracle/backup/export/rman.dmplog=/home/oracle/backup/log/rman.log

-- 倒出RMAN数据SHELL脚本exp_rman.sh#!/bin/bashcd $HOME. .bash_profilecd $HOME/backup/scriptexp parfile=exp_rman.par

-- 零级备份RMAN脚本level0_backup.rcvconnect catalog rman/rmanconnect target sys/unumall@unu2run {

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;CONFIGURE CONTROLFILE AUTOBACKUP ON;allocate channel d1 type disk;allocate channel d2 type disk;backup incremental level 0 database format '/home/oracle/rman_backup/level0_%d_%s_%p_%u.bak'tag='level 0' include current controlfile;sql 'alter system archive log current';backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;release channel d2;release channel d1;}crosscheck backup;delete noprompt expired backup;delete noprompt obsolete;resync catalog;

--零级备份SHELL脚本的level0_backup.sh#!/bin/bashcd $HOME. .bash_profilecd $HOME/backup/scriptrman cmdfile=level0_backup.rcv msglog=$HOME/backup/log/level0_backup.log. /home/oracle/backup/script/exp_rman.sh

--一级差异增量备份RMAN脚本 level1_backup.rcvconnect catalog rman/rmanconnect target sys/luke@lukerun {allocate channel d1 type disk;backup incremental level 1 format '/home/oracle/rman_backup/level1_%d_%s_%p_%u.bak' tag = 'level 1' database;sql 'alter system archive log current';backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;release channel d1;}crosscheck backup;delete noprompt expired backup;delete noprompt obsolete;resync catalog;

--一级差异增量备份SHELL脚本level1_backup.sh

#!/bin/bashcd $HOME. .bash_profilecd /home/oracle/backup/scriptrman cmdfile=level1_backup.rcv msglog=$HOME/backup/log/level1.log. /home/oracle/backup/script/exp_rman.sh

--二级差异增量备份RMAN脚本 level2_backup.rcvconnect catalog rman/rmanconnect target sys/luke@lukerun {allocate channel d1 type disk;backup incremental level 2 format '/home/oracle/rman_backup/level2_%d_%s_%p_%u.bak' tag = 'level 2' database;sql 'alter system archive log current';backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;release channel d1;}crosscheck backup;delete noprompt expired backup;delete noprompt obsolete;resync catalog;

–二级差异增量备份SHELL脚本level2_backup.sh

#!/bin/bashcd $HOME. .bash_profilecd /home/oracle/backup/scriptrman cmdfile=level2_backup.rcv msglog=$HOME/backup/log/level2.log. /home/oracle/backup/script/exp_rman.sh

提高 RMAN增量备份性能

alter database enable block change tracking using file'/u01/app/oracle/admin/devdb/bdump/luke.log';

desc v$block_change_tracking;

linux下定时执行备份脚本

crontab -e -u oracleSHELL=/bin/bash --以下脚本在bash下执行MAILTO=oracle --执行日志以邮件形式邮给oracle用户，可以/var/spool/mail/oracle下查收10 1 * * 0 /home/oracle/backup/script/level0_backup.sh10 1 * * 1 /home/oracle/backup/script/level2_backup.sh10 1 * * 2 /home/oracle/backup/script/level2_backup.sh10 1 * * 3 /home/oracle/backup/script/level1_backup.sh10 1 * * 4 /home/oracle/backup/script/level2_backup.sh10 1 * * 5 /home/oracle/backup/script/level2_backup.sh10 1 * * 6 /home/oracle/backup/script/level2_backup.sh

一周差异备份策略：
备份目标库和catalog库
周日0级全备，周一周二为2级，周三为1级，周四周五周六为2级。
每天凌晨1点10分开始备份

零级备份

backup incremental level 0 database;

一级差异增量 差异增量是默认增量备份方式

backup incremental level 1 database;

一级累计增量

backup incremental level 1 cumulative database;