数据库备份方案
Oracle 恢复管理器 (RMAN)
 
 
 
 
 
 
 
 

Oracle版本：
11.2G
操作系统：
Linux 5.4
版本号：
1.0初稿
制订日期：
2011-9-6
制定部门：
 

 
 
 
 
 
 
目    录
 
 
随着计算机的普及和信息技术的进步，特别是计算机网络的飞速发展，信息安全的重要性日趋明显。只要发生数据传输、数据存储和数据交换，就有可能产生系统失效、数据丢失或遭到破坏。如果没有采取数据备份和数据恢复手段与措施，就会导致数据丢失或损毁，给数据中心造成的损失是无法弥补与估量的。
以最短的时间恢复因人为原因或硬件介质所产生的损坏。
 
Ø 可使用RMAN进行增量备份，备份的大小不取决于数据库的大小而取决于数据库内的活动程度，因为增量备份跳过未改动的，用其它普通的办法是无法时行增量备份的。
Ø 可联机修补数据文件部分有误数据块，不需要从备份复原文件
Ø 二进制压缩特性降低了保存在磁盘上的备份大小，但会占用较多的系统资源
Ø 可方便的克隆数据库和维护数据库
 
恢复时间取决于以下因素：
 
Ø 归档日志位于何种介质之上。如果是磁带则需时间要比磁盘长很多
Ø 使用并行恢复减少恢复数据库所需时间
Ø 需要替换磁盘还是copy至其它地方。Copy到其它地方因恢复时需要把数据copy回所需时间长
Ø 执行备份的频率有多高？备份越频繁，应用的日志越多，时间越长。
 
四、   RMAN备份种类
 
 
 
 
 恢复时数据库必需要在mount状态，在启动mount状态必需有spfile和控制文件本实例采用此方案备份数据 ，控制必需要是copy方式的保存
以数据库保存信息方式，需要一台新的电脑作为catalog服务器，相对功能比较多。
 
A.   备份数据保留7天
B.   采用星期天0级备份其它时间都采用1增量备份
C.   本实例方法使用nocatalog 方式备份与恢复 所有备份信息写在控制文件中，有可能超过控制文件大小,需要设定备份信息保留的时间，需少要大于RMAM中的备份策略中的天数
  
6.1       配置oracle系统参数
 
SQL> alter system set CONTROL_FILE_RECORD_KEEP_TIME=15 scope=both;
设置备份信息保留天数
SQL> alter database enable block changes tracking using file '/home/oracle/change_tracking.dbf';启用块跟踪功能
 
6.2       连接rman进行参数配置
$ rman target /
RMan> configure device type disk parallelism 2;
 (设置备份的并行级别，通道数)
RMAN> configure channel 1 device type disk fromat '/backup1/backup_%s_%p_%t';
 (设置备份的文件格式，只适用于磁盘设备)
RMAN> configure channel 2 device type disk fromat '/backup2/backup_%s_%p_%t ';
(设置备份的文件格式，只适用于磁盘设备) 
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup1/ctl_%s_%p_%t';
(设置控制文件与服务器参数文件自动备份的文件格式)
RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 2 backup type to compressed backupset;
进行压缩可以减少数据空间，但需要cpu的时间一般不采用，所需时间是正常备份所需时间的好几倍
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
删除时先确定是否已经应用
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
设定备份数据保留天数
   
6.3       编写0级和1级备份脚本
$cd $ORACLE_HOME/dbs/
$vi rman_level0.rman
run {
 backup incremental level 0 tag='db0' database
skip offline # 表示跳过离线文件
skip inaccessible # 表示跳过不可读的文件
skip readonly ; #跳过只读文件
backup archivelog all not backed up 1 times;
 sql 'alter system archive log current';
 copy current controlfile to '/store/control_%s_%p_%t.ctl';
CROSSCHECK BACKUPSET;
 DELETE NOPROMPT EXPIRED BACKUPSET;
}
 
保存退出
$vi rman_level1.rman
 
run {
 backup incremental level 1 tag='db1' database
skip offline # 表示跳过离线文件
skip inaccessible # 表示跳过不可读的文件
skip readonly ; #跳过不可读文件
backup archivelog all not backed up 1 times;
 sql 'alter system archive log current';
 copy current controlfile to '/store/control_%s_%p_%t.ctl';
CROSSCHECK BACKUPSET;
 DELETE NOPROMPT EXPIRED BACKUPSET;
delete noprompt obsoltet;
delete noprompt redundancy =3;
delete noprompt redundancy window of 7 days;
}
保存退出
 
6.4   编写执行0级与1级备份的操作系统脚本文件
Vi runback0.sh
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2.0/db
export ORACLE_SID=orcl
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_TERM=xterm
export NLS_LANG=american_america.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export PATH=$PATH:$ORACLE_HOME/bin
$ORACLE_HOME/bin/rman target / cmdfile=$ORACLE_HOME/rman_level0.rman log=$ORACLE_HOME/myrman.log append
 
保存退出
$vi runback1.sh
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2.0/db
export ORACLE_SID=orcl
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export PATH=$PATH:$ORACLE_HOME/bin
NLS_LANG=american_america.ZHS16GBK; export NLS_LANG
 
$ORACLE_HOME/bin/rman target / cmdfile=$ORACLE_HOME/rman_level1.rman log=$ORACLE_HOME/myrman.log append
 
保存退出
$ crontab -e 每天晚上1点29分开始执行 星期天进行执行0级备份其它都为1级
 
29 01 * * 0 /opt/oracle/product/10.2.0/db/./runbacko0.sh
29 01 * * 1 /opt/oracle/product/10.2.0/db/./runback1.sh
29 01 * * 2 /opt/oracle/product/10.2.0/db/./runback1.sh
29 01 * * 3 /opt/oracle/product/10.2.0/db/./runback1.sh
29 01 * * 4 /opt/oracle/product/10.2.0/db/./runback1.sh
29 01 * * 5 /opt/oracle/product/10.2.0/db/./runback1.sh
29 01 * * 6 /opt/oracle/product/10.2.0/db/./runback1.sh
保存退出
如果没用启用定时功能，则使用root用户启动此服务操作
l $su root
l $cd /etc/init.d
l ./crond restart
7    
7.1 Spfile丢失
Starting restore at 08-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=19 device type=DISK
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
 
 
connected to target database (not started)
Oracle instance started
database mounted
database opened
 
Total System Global Area     146472960 bytes
 
Fixed Size                    1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   2531328 bytes
 
Report of database schema for database with db_unique_name DBP
 
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    680      SYSTEM               ***     /opt/oracle/oradata/orcl/system01.dbf
2    540      SYSAUX               ***     /opt/oracle/oradata/orcl/sysaux01.dbf
3    95       UNDOTBS1             ***     /opt/oracle/oradata/orcl/undotbs01.dbf
4    37       USERS                ***     /opt/oracle/oradata/orcl/users01.dbf
5    0        TB1                  ***     /opt/oracle/oradata/tbuser1.dbf
6    50       TB2                  ***     /opt/oracle/oradata/tbuser2.dbf
 
现datafile users01.dbf丢失 序号为4
using target database control file instead of recovery catalog
sql statement: alter database datafile 4 offline
 
Starting restore at 08-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /store/backup_0nmluq8o_1_1.bak
channel ORA_DISK_1: piece handle=/store/backup_0nmluq8o_1_1.bak tag=DB0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-SEP-11
 
Starting recover at 08-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /opt/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /store/backup_0vmluvqr_1_1.bak
channel ORA_DISK_1: piece handle=/store/backup_0vmluvqr_1_1.bak tag=DB1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
 
sql statement: alter database datafile 4 online
 
database opened
 
using target database control file instead of recovery catalog
sql statement: ALTER TABLESPACE TB2 OFFLINE IMMEDIATE
Starting restore at 08-SEP-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=17 device type=DISK
 
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/tbuser2.dbf
channel ORA_DISK_1: reading from backup piece /store/backup_0nmluq8o_1_1.bak
channel ORA_DISK_1: piece handle=/store/backup_0nmluq8o_1_1.bak tag=DB0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 08-SEP-11
 
Starting recover at 08-SEP-11
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00006: /opt/oracle/oradata/tbuser2.dbf
channel ORA_DISK_1: reading from backup piece /store1/backup_10mluvqr_1_1.bak
channel ORA_DISK_1: piece handle=/store1/backup_10mluvqr_1_1.bak tag=DB1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-SEP-11
 
sql statement: ALTER TABLESPACE TB2 ONLINE