2. it
  3. 集中备份项目实施方案

集中备份项目实施方案

mac2022-06-30  98

目录一、集中备份 ........................................................................................................................................31.项目目的 .....................................................................................................................................32.项目目标 .....................................................................................................................................33.环境的搭建.................................................................................................................................34.制定备份策略 .............................................................................................................................44.1 rman 的设置 ....................................................................................................................44.2 编写备份脚本 ..................................................................................................................44.3 执行备份计划 ..................................................................................................................54.4 rman 的维护 .............................................................................................................6二、恢复测试 ........................................................................................................................................71.恢复环境的设置 .........................................................................................................................71.1 初始化参数： ..................................................................................................................71.2 创建对应目录： ..............................................................................................................71.3 复制备份文件到 /backup 目录.............................................................................................82.恢复过程 .....................................................................................................................................82.1 数据库启动到 nomount 恢复控制文件： ...................................................................82.2 数据库启动到 mount 查看备份状况： .......................................................................92.3 查看备份和归档状况确认 recover 的 scn 号： ........................................................112.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 catalogRMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 2;CONFIGURE BACKUP OPTIMIZATION ON;CONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE 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 envexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export PATH=$PATH:$ORACLE_HOME/binexport ORACLE_SID=prodexport ARC_PATH= /backup/rman_backup/archexport RMAN_BAK_PATH=/backup/rman_backupDATE=`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;EOF1级备份脚本#!/usr/bin/bash#set envexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export PATH=$PATH:$ORACLE_HOME/binexport ORACLE_SID=prodexport ARC_PATH= /backup/rman_backup/archexport RMAN_BAK_PATH=/backup/rman_backupDATE=`date +%Y%m%d-%H:%M:%S`rman target / log /home/oracleuser/scripts/log/L1_bk_$DATE.log << EOFrun {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 envexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export PATH=$PATH:$ORACLE_HOME/binexport ORACLE_SID=prodexport ARC_PATH= /backup/rman_backup/archexport RMAN_BAK_PATH=/backup/rman_backupDATE=`date +%Y%m%d-%H:%M:%S`rman target / log /home/oracleuser/scripts/log/L2_bk_$DATE.log << EOFrun {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.152export 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 /]#bdfFilesystem kbytes used avail %used Mounted on/dev/vg00/lvol3 204800 159560 44992 78%/dev/vg00/lv_u05 65536 36184 29136 55% /home192.168.8.152:/backup/cuug1013417584 5946680 6789336 47% /backup/dev/vg00/lvol5 65536655364.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.shbash-2.04$ cat dml.sh#!/usr/bin/shexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1export ORACLE_SID=shennaoexport PATH=$PATH:$ORACLE_HOME/bin:/usr/binwhile truedosqlplus scott/tiger <<EOFinsert 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;EOFdone二、恢复测试恢复测试的目的是为确认备份的有效性，恢复分本地恢复和远程恢复两种，以下是远程恢复的过程。恢复过程要求最短时间完成且达到恢复的目的。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/prod1.3 复制备份文件到 /backup 目录[root@aix191 /backup]#ls -l-rw-r--r--1 200 200 9568256 Nov 29 09:13ctl_AMBER_c-3061253012-20121129-02.bk-rw-r--r--1 200 200 9666560 Nov 29 09:46ctl_AMBER_c-3061253012-20121129-03.bk-rw-r--r--1 200200 10649600 Nov 29 10:51ctl_AMBER_c-3061253012-20121129-04.bk-rw-r--r--1 200 200 10649600 Nov 29 11:08ctl_AMBER_c-3061253012-20121129-05.bk-rw-r--r---rw-r--r---rw-r--r---rw-r--r--1 2001 2001 2001 200200200200200948379648 Nov 29 09:13 full_244.bk3151806464 Nov 29 09:46 full_246.bk2166110208 Nov 29 10:51 full_248.bk599107584 Nov 29 11:07 full_250.bk因为之后的恢复需要 restore 数据文件和归档，确认空间是否足够。[oracle@aix191 prod]$bdfFilesystem kbytes/dev/vg00/lvol3 1048576used146280avail %used Mounted on896456 14% //dev/vg00/lvol1/dev/vg00/lvol8/dev/vg00/lvol7505392 68504 3863442613248 957776 16436162334720 1614760 71437615% /stand37% /var69% /usr/dev/vg00/u01/dev/vg00/lvol4/dev/vg00/lvol610240000 4997449 4919974 50% /u012048000 995248 1045288 49% /tmp3506176 2435224 1062640 70% /opt/dev/vg00/lvol520480003112 20292880% /home2.恢复过程2.1 数据库启动到 nomount 恢复控制文件：SQL> startup nomount;ORACLE instance started.Total System Global Area 201326592 bytesFixed Size 2005184 bytesVariable Size 75499328 bytesDatabase Buffers 121634816 bytesRedo Buffers 2187264 bytes[oracle@aix191 amber]$rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Fri Nov 30 16:29:27 2012Copyright (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-12using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=155 devtype=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output filename=/u01/app/oracle/oradata/orcl2/control01.ctloutput filename=/u01/app/oracle/oradata/orcl2/control02.ctloutput filename=/u01/app/oracle/oradata/orcl2/control03.ctlFinished restore at 25-DEC-122.2 数据库启动到 mount 查看备份状况：RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1RMAN> list backup of database;BS KeyType LV SizeDevice Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------241Full904.44M DISK00:19:1029-NOV-12BP Key: 241 Status: AVAILABLE Compressed: NO Tag: TAG20121129T103308Piece Name: /u01/backup/full_244.bkList of Datafiles in backup set 241File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----1 Full 73453529-NOV-12 /u01/app/oracle/oradata/amber/system01.dbf2 Full 734535 29-NOV-12 /u01/app/oracle/oradata/amber/undotbs01.dbf345Full 734535Full 734535Full 73453529-NOV-12 /u01/app/oracle/oradata/amber/sysaux01.dbf29-NOV-12 /u01/app/oracle/oradata/amber/users01.dbf29-NOV-12 /u01/app/oracle/oradata/amber/example01.dbf2.3 restore 数据文件：RMAN> restore database;Starting restore at 30-NOV-12using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to /u01/app/oracle/oradata/amber/system01.dbfrestoring datafile 00002 to /u01/app/oracle/oradata/amber/undotbs01.dbfrestoring datafile 00003 to /u01/app/oracle/oradata/amber/sysaux01.dbfrestoring datafile 00004 to /u01/app/oracle/oradata/amber/users01.dbfrestoring datafile 00005 to /u01/app/oracle/oradata/amber/example01.dbfchannel ORA_DISK_1: reading from backup piece /bk/full_244.bkchannel ORA_DISK_1: restored backup piece 1piece handle=/backup/full_244.bk tag=TAG20121129T103308channel ORA_DISK_1: restore complete, elapsed time: 00:02:35Finished restore at 30-NOV-12[oracle@aix191 prod]$ls -ltotal 4238582-rw-r------rw-r------rw-r------rw-r------rw-r-----1 oracle1 oracle1 oracle1 oracle1 oracleoinstalloinstalloinstalloinstalloinstall157294592 Nov 30 17:11 example01.dbf251666432 Nov 30 17:20 sysaux01.dbf503324672 Nov 30 17:18 system01.dbf571482112 Nov 30 17:18 undotbs01.dbf524296192 Nov 30 17:16 users01.dbf2.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:32266 765012 765609 2012-11-29 10:48:53267 765609 766001 2012-11-29 10:49:09268 766001 766744 2012-11-29 10:49:26269 766744 767410 2012-11-29 10:52:04270 767410 767987 2012-11-29 10:54:07271 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:48SQL> select checkpoint_change# from v$datafile_header;CHECKPOINT_CHANGE#------------------7667447667447667447667447667442.5 用 resetlogs 方式打开数据库确认恢复状况：已经成功恢复，restore 数据文件时间是 00:02:35，restore 归档时间是 00:06:07，Recover 数据文件时间是 00:05:48。SQL> alter database open resetlogs;Database altered.SQL> conn cuug/cuugConnected.SQL> select * from tab;TNAME TABTYPEEMP TABLETEST TABLE

转载于:https://www.cnblogs.com/iyoume2008/p/9071578.html

最新回复(0)