集中备份项目实施方案

目录
一、集中备份 ........................................................................................................................................3
1.项目目的 .....................................................................................................................................3
2.项目目标 .....................................................................................................................................3
3.环境的搭建.................................................................................................................................3
4.制定备份策略 .............................................................................................................................4
4.1 rman 的设置 ....................................................................................................................4
4.2 编写备份脚本 ..................................................................................................................4
4.3 执行备份计划 ..................................................................................................................5
4.4 rman 的维护 .............................................................................................................6
二、恢复测试 ........................................................................................................................................7
1.恢复环境的设置 .........................................................................................................................7
1.1 初始化参数： ..................................................................................................................7
1.2 创建对应目录： ..............................................................................................................7
1.3 复制备份文件到 /backup 目录.............................................................................................8
2.恢复过程 .....................................................................................................................................8
2.1 数据库启动到 nomount 恢复控制文件： ...................................................................8
2.2 数据库启动到 mount 查看备份状况： .......................................................................9
2.3 查看备份和归档状况确认 recover 的 scn 号： ........................................................11
2.4 用 resetlogs 方式打开数据库确认恢复状况： .........................................................12


生产服务器 服务器型号 IBM RS6000 操作系统 AIX 5.3.0.8 数据库版本 10.2.0.1.0 备份服务器 服务器型号 操作系统 存储 Sun T3阵列 恢复服务器 服务器型号 操作系统 数据库版本 10.2.0.1.0
一、集中备份
1.项目目的
为了实现 oracle 数据库的保护，实施 oracle 数据库集中备份项目，通
过交换机和生产服务器、备份服务器、恢复服务器相连，组成一个内部备份恢复
局域网，之后通过将备份服务器上的存储介质以 NFS 方式分别挂载到生产服
务器与恢复服务器上，使用 RMAN 实现生产数据的集中备份与远程恢复。
2.项目目标
对生产库的数据进行备份，制定数据库备份策略、编写备份执行脚本。
确定最少的恢复时间，尽量减少对数据库的影响，同时，为确保数据的安全。
在备份时要遵循存储空间与资源最节省原则，并且对 RMAN 的元数据进行保护，
可以使生产库能够完成对 Oracle 数据库备份数据的集中管理。
3.环境的搭建
3


原理图如下：
Server4 Server5 Server6
备份服务器
Server1 server2 server3
测试服务器

拓扑图如下：
IBM P-SERIES 630 AIX 191 IBM P-SERIES 630 AIX 197 IBM P-SERIES 630 AIX 199
生产库 备份服务器 测试服务器
SAN 光纤交换机
SAN T3 存储
在生产服务器上安装 oracle 搭建生产库，在恢复服务器安装 oracle 软件，设置 nfs 让
生产库的备份可以放到备份服务器上为之后的统一管理。


4 备份策略
4.1 rman 的设置
根据备份要求 2 个冗余，设置如下：
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
以周为单位
周日做 0级备份
周 1 周2 做2级备份
周3做1级备份
周4 周5 周6 做 2级备份
周日再做 0级备份

4.2 编写备份脚本
零级备份脚本
#!/usr/bin/bash
#set env
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=prod
export ARC_PATH= /backup/rman_backup/arch
export RMAN_BAK_PATH=/backup/rman_backup
DATE=`date +%Y%m%d-%H:%M:%S`
rman target / log /home/oracleuser/scripts/log/L0_bk_$DATE.log << EOF 保存rman 备份产生的日志
run {
allocate channel c1 type disk ;
allocate channel c2 type disk ;
allocate channel c3 type disk ;
backup incremental level 0 tag 'dbL0' format '$RMAN_BAK_PATH/L0_%U_%t.bak' database ;
sql 'alter system archive log current';
backup filesperset 3 format '$ARC_PATH/arch%t.arc' archivelog all delete input ;
delete noprompt obsolete ;
release channel c1;
release channel c2;
release channel c3;
}
exit;
EOF
1级备份脚本
#!/usr/bin/bash
#set env
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=prod
export ARC_PATH= /backup/rman_backup/arch
export RMAN_BAK_PATH=/backup/rman_backup
DATE=`date +%Y%m%d-%H:%M:%S`
rman target / log /home/oracleuser/scripts/log/L1_bk_$DATE.log << EOF
run {
allocate channel c1 type disk ;
allocate channel c2 type disk ;
allocate channel c3 type disk ;
backup incremental level 1 tag 'dbL1' format '$RMAN_BAK_PATH/L0_%U_%t.bak' database ;
sql 'alter system archive log current';
backup filesperset 3 format '$ARC_PATH/arch%t.arc' archivelog all delete input ;
delete noprompt obsolete ;
release channel c1;
release channel c2;
release channel c3;
}
exit;

4.3 执行备份计划
2级备份脚本
#!/usr/bin/bash
#set env
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=prod
export ARC_PATH= /backup/rman_backup/arch
export RMAN_BAK_PATH=/backup/rman_backup
DATE=`date +%Y%m%d-%H:%M:%S`
rman target / log /home/oracleuser/scripts/log/L2_bk_$DATE.log << EOF
run {
allocate channel c1 type disk ;
allocate channel c2 type disk ;
allocate channel c3 type disk ;
backup incremental level 2 tag 'dbL2' format '$RMAN_BAK_PATH/L0_%U_%t.bak' database ;
sql 'alter system archive log current';
backup filesperset 3 format '$ARC_PATH/arch%t.arc' archivelog all delete input ;
delete noprompt obsolete ;
release channel c1;
release channel c2;
release channel c3;
}
exit;
EOF
挂载备份服务器备份目录：
[root@aix191 /]#showmount -e 192.168.8.152
export list for 192.168.8.152:
/backup/cuug10 aix205,aix191
/backup/cuug09 aix191
[root@aix191 /]#mount 192.168.8.152:/backup/cuug10 /backup 挂载备份服务器目录
[root@aix191 /]#bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 204800 159560 44992 78%
/dev/vg00/lv_u05 65536 36184 29136 55% /home
192.168.8.152:/backup/cuug10
13417584 5946680 6789336 47% /backup
/dev/vg00/lvol5 65536
65536
4.4 RMAN 的维护
因为设置了 2 个冗余，长时间后有陈旧备份用以下命令对数据库备份检查：
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup; ###删除过期备份
delete noprompt obsolete; ###删除陈旧备份
为模拟正常的生产库的日常业务，编写了以下脚本对数据更新：
创建计划任务：
bash-2.04$ crontab -e
* * * * 0 /home/oracle/scripts/bin/l0_bk.sh
* * * * 1 2 /home/oracle/scripts/bin/l2_bk.sh
* * * * 3 /home/oracle/scripts/bin/l1_bk.sh
* * * * 4 5 6 /home/oracle/scripts/bin/l2_bk.sh
bash-2.04$ cat dml.sh
#!/usr/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=shennao
export PATH=$PATH:$ORACLE_HOME/bin:/usr/bin
while true
do
sqlplus scott/tiger <<EOF
insert into test select * from emp;
insert into test select * from emp;
commit;
update test set ename='CUUG';
rollback;
delete from test where empno=7788;
commit;
EOF
done

二、恢复测试
恢复测试的目的是为确认备份的有效性，恢复分本地恢复和远程恢复两种，以下是远程
恢复的过程。恢复过程要求最短时间完成且达到恢复的目的。
1.恢复环境的设置
1.1 修改初始化参数：
[oracle@aix191 dbs]$cat initprod.ora
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.background_dump_dest='/u01/app/oracle/admin/prod/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/disk1/prod/control01.ctl','/u01/disk2/prod/control02.ctl'
*.core_dump_dest='/u01/app/oracle/admin/prod/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch'
*.log_archive_format='arch_%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=96468992
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=200455552
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/prod /udump'
1.2 创建对应目录：
[oracle@aix191 dbs]$mkdir -p /u01/app/oracle/admin/prod/adump
[oracle@aix191 dbs]$mkdir -p /u01/app/oracle/admin/prod/bdump
[oracle@aix191 dbs]$mkdir -p /u01/app/oracle/admin/prod/cdump
[oracle@aix191 dbs]$mkdir -p /u01/app/oracle/admin/prod/udump


[oracle@aix191 dbs]$mkdir -p /u01/disk1/prod/
[oracle@aix191 dbs]$mkdir -p /u01/disk2/prod/
[oracle@aix191 dbs]$mkdir -p /u01/app/oracle/oradata/prod
1.3 复制备份文件到 /backup 目录
[root@aix191 /backup]#ls -l
-rw-r--r--
1 200 200 9568256 Nov 29 09:13
ctl_AMBER_c-3061253012-20121129-02.bk
-rw-r--r--
1 200 200 9666560 Nov 29 09:46
ctl_AMBER_c-3061253012-20121129-03.bk
-rw-r--r--
1 200
200 10649600 Nov 29 10:51
ctl_AMBER_c-3061253012-20121129-04.bk
-rw-r--r--
1 200 200 10649600 Nov 29 11:08
ctl_AMBER_c-3061253012-20121129-05.bk
-rw-r--r--
-rw-r--r--
-rw-r--r--
-rw-r--r--
1 200
1 200
1 200
1 200
200
200
200
200
948379648 Nov 29 09:13 full_244.bk
3151806464 Nov 29 09:46 full_246.bk
2166110208 Nov 29 10:51 full_248.bk
599107584 Nov 29 11:07 full_250.bk
因为之后的恢复需要 restore 数据文件和归档，确认空间是否足够。
[oracle@aix191 prod]$bdf
Filesystem kbytes
/dev/vg00/lvol3 1048576
used
146280
avail %used Mounted on
896456 14% /
/dev/vg00/lvol1
/dev/vg00/lvol8
/dev/vg00/lvol7
505392 68504 386344
2613248 957776 1643616
2334720 1614760 714376
15% /stand
37% /var
69% /usr
/dev/vg00/u01
/dev/vg00/lvol4
/dev/vg00/lvol6
10240000 4997449 4919974 50% /u01
2048000 995248 1045288 49% /tmp
3506176 2435224 1062640 70% /opt
/dev/vg00/lvol5
2048000
3112 2029288
0% /home
2.恢复过程
2.1 数据库启动到 nomount 恢复控制文件：
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 201326592 bytes
Fixed Size 2005184 bytes
Variable Size 75499328 bytes
Database Buffers 121634816 bytes
Redo Buffers 2187264 bytes

[oracle@aix191 amber]$rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Nov 30 16:29:27 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: amber (not mounted)
RMAN> restore controlfile from '/backup/rman_controlfile/****.bak' ;
Starting restore at 25-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/orcl2/control01.ctl
output filename=/u01/app/oracle/oradata/orcl2/control02.ctl
output filename=/u01/app/oracle/oradata/orcl2/control03.ctl
Finished restore at 25-DEC-12
2.2 数据库启动到 mount 查看备份状况：
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> list backup of database;
BS Key
Type LV Size
Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
241
Full
904.44M DISK
00:19:10
29-NOV-12
BP Key: 241 Status: AVAILABLE Compressed: NO Tag: TAG20121129T103308
Piece Name: /u01/backup/full_244.bk
List of Datafiles in backup set 241
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 734535
29-NOV-12 /u01/app/oracle/oradata/amber/system01.dbf
2 Full 734535 29-NOV-12 /u01/app/oracle/oradata/amber/undotbs01.dbf
3
4
5
Full 734535
Full 734535
Full 734535
29-NOV-12 /u01/app/oracle/oradata/amber/sysaux01.dbf
29-NOV-12 /u01/app/oracle/oradata/amber/users01.dbf
29-NOV-12 /u01/app/oracle/oradata/amber/example01.dbf


2.3 restore 数据文件：
RMAN> restore database;
Starting restore at 30-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/amber/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/amber/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/amber/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/amber/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/amber/example01.dbf
channel ORA_DISK_1: reading from backup piece /bk/full_244.bk
channel ORA_DISK_1: restored backup piece 1
piece handle=/backup/full_244.bk tag=TAG20121129T103308
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 30-NOV-12
[oracle@aix191 prod]$ls -l
total 4238582
-rw-r-----
-rw-r-----
-rw-r-----
-rw-r-----
-rw-r-----
1 oracle
1 oracle
1 oracle
1 oracle
1 oracle
oinstall
oinstall
oinstall
oinstall
oinstall
157294592 Nov 30 17:11 example01.dbf
251666432 Nov 30 17:20 sysaux01.dbf
503324672 Nov 30 17:18 system01.dbf
571482112 Nov 30 17:18 undotbs01.dbf
524296192 Nov 30 17:16 users01.dbf
2.4 查看备份和归档状况确认 recover 的 scn 号：
从备份的数据文件看 scn 是 734535 花费时间是 19:10，归档的记录可
份
SQL> select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') “time” from v$archived_log;
SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# time
------------------------------------------------------------------------------------------------------------------
216 734199 735024 2012-11-29 10:31:32
266 765012 765609 2012-11-29 10:48:53
267 765609 766001 2012-11-29 10:49:09
268 766001 766744 2012-11-29 10:49:26
269 766744 767410 2012-11-29 10:52:04
270 767410 767987 2012-11-29 10:54:07
271 767987 768483 2012-11-29 10:54:20


以上的信息确认可以恢复到 scn=766744 就可以把库打开。
RMAN> recover database until scn 766744;
channel ORA_DISK_1: restore complete, elapsed time: 00:06:07
………………………………………………………………………………………………
media recovery complete, elapsed time: 00:05:48
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
766744
766744
766744
766744
766744
2.5 用 resetlogs 方式打开数据库确认恢复状况：
已经成功恢复，
restore 数据文件时间是 00:02:35，
restore 归档时间是 00:06:07，
Recover 数据文件时间是 00:05:48。
SQL> alter database open resetlogs;
Database altered.
SQL> conn cuug/cuug
Connected.
SQL> select * from tab;
TNAME TABTYPE
EMP TABLE
TEST TABLE