集中备份

数据库集中备份

李知伟 2011/11/10

目录

1项目环境 ---------------------------------------------------------------------------------------------------------- 2

1.1生产库环境 ---------------------------------------------------------------------------------------------- 2 1.2备份服务器环境 ---------------------------------------------------------------------------------------- 2 2拓扑结构 ---------------------------------------------------------------------------------------------------------- 2 3搭建并配置Recovery Catalog ------------------------------------------------------------------------------- 2

3.1备份服务器上建立catalog数据库，创建监听和TNS --------------------------------------- 2

3.1.1备份服务器上使用DBCA建立数据库catalog----------------------------------------- 2 3.1.2配置TNS ---------------------------------------------------------------------------------------- 3 3.2 在catalog库中创建的表空间 ---------------------------------------------------------------- 4 3.3在catalog库上创建的SCHEMA，用来记录备份信息并授予相关权限 ---------- 4 3.4在生产库上通过rman连接catalog数据库和目标数据库 ----------------------------------- 4 4远程备份—NFS挂载 ------------------------------------------------------------------------------------------ 4

4.1在备份服务器上共享/u01这个目录，在/etc/exports文件中添加如下内容： ---------- 4 4.2在生产库服务器上（AIX）检查备份服务器端共享目录/u01是否存在：-------------- 5 4.2挂载备份服务器上共享目录/u01到生产库服务器的/backup ------------------------------- 5 5备份策略 ---------------------------------------------------------------------------------------------------------- 6

5.1设置recovery窗口时间和备份冗余 --------------------------------------------------------------- 6 5.2备份服务器上建立相应目录并创建备份脚本 -------------------------------------------------- 6 5.3创建rman脚本 ----------------------------------------------------------------------------------------- 7 5.4创建shell脚本 ------------------------------------------------------------------------------------------ 8 6定时任务 --------------------------------------------------------------------------------------------------------- 10 7恢复测试 --------------------------------------------------------------------------------------------------------- 10

7.1远程恢复 ------------------------------------------------------------------------------------------------ 10

7.1.1拷贝生产库的pfile到远程服务器的相关目录下并修改： ------------------------ 11 7.1.2创建相应目录： ------------------------------------------------------------------------------ 11 7.1.3恢复数据库 ------------------------------------------------------------------------------------- 12 7.2本地恢复 ------------------------------------------------------------------------------------------------ 13

7.2.1拷贝pfile，修改有关参数 ------------------------------------------------------------------ 13 7.2.2创建相应目录： ------------------------------------------------------------------------------ 14 7.3恢复数据库： ------------------------------------------------------------------------------------------ 14

集中备份

1项目环境

1.1生产库环境

主机 操作系统 数据库版本

存储 数据库大小

IBM RS/6000 AIX 5.3.0 Oracle10.2.0 光前阵列raid5

20GB

1.2备份服务器环境

主机 操作系统 数据库版本

存储 数据库大小

普通PC RedHat Linnx4.6 Oracle10.2.0 SCSI阵列raid5

60GB

2拓扑结构

3搭建并配置Recovery Catalog

3.1备份服务器上建立catalog数据库，创建监听和TNS

3.1.1备份服务器上使用DBCA建立数据库catalog

（过程省略，与普通建库步骤一样）

3.1.2配置TNS ·生产库

在tnsnames.ora文件中添加以下内容：

catalog =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.227)(PORT = 1521)) )

(CONNECT_DATA =

(SERVICE_NAME = catalog) ) )

·Catalog库

在tnsnames.ora文件中添加以下内容：

prod =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.197)(PORT = 1521)) )

(CONNECT_DATA = (SERVICE_NAME = prod) ) )

3.2 在catalog库中创建的表空间

SQL>createtablespacecatatbsdatafile '/u01/app/oracle/oradata/catalog/catatbs01.dbf' size 100m;

3.3在catalog库上创建的SCHEMA，用来记录备份信息并授予相关权限

创建rman用户

SQL> create user rman identified by rmandefault tablespacecatatbs;

授予rman用户相关权限

SQL> grant connect,resource,recovery_catalog_owner to rman;

3.4在生产库上通过rman连接catalog数据库和目标数据库

-bash-3.00$ rman target / catalog rman/rman@catalog

·在rman中创建catalog

RMAN> create catalog;

·注册数据库

RMAN> register database;

4远程备份—NFS挂载

4.1在备份服务器上共享/u01这个目录，在/etc/exports文件中添加如下内容：

[root@backup u01]# vi /etc/exports /u01 *(rw,no_root_squash,sync)

4.2在生产库服务器上（AIX）检查备份服务器端共享目录/u01是否存在：

-bash-3.00# showmount -e 192.168.8.227 export list for 192.168.8.227: /u01 *

若出现1832-019 Program not registered错误则需要重新启动portmap和nfs的服务：

-bash-3.00# stopsrc -g nfs -bash-3.00# stopsrc -s portmap -bash-3.00# startsrc -s portmap -bash-3.00# startsrc –g nfs

4.2挂载备份服务器上共享目录/u01到生产库服务器的/backup

若出现以下错误提示：

mount: giving up on： testcom:/nfs/ vmount: Not owner

生产库服务器上需要检查nfs_use_reserved_ports是否为1：

-bash-3.00# nfso -a | grepnfs_use_reserved_ports nfs_use_reserved_ports = 0

设置nfs_use_reserved_ports的值：

-bash-3.00# nfso -o nfs_use_reserved_ports=1

再次进行挂载

5备份策略

备份要求：Recovery窗口时间为2天，2个备份冗余 由于实验环境所限现把每天更换为每20分钟一次

5.1设置recovery窗口时间和备份冗余

RMAN> configure retention policy to recovery window of 2 days; RMAN> configure retention policy to redundancy 2;

5.2备份服务器上建立相应目录并创建备份脚本

·创建目录存放rman脚本，日志以及临时文件

[oracle@backup ~]$ mkdir -p rman/scripts/bin [oracle@backup ~]$ mkdir -p rman/scripts/logs [oracle@backup ~]$ mkdir -p rman/scripts/tmp

·创建目录存放shell脚本，日志以及临时文件

[oracle@backup ~]$ mkdir -p scripts/bin

[oracle@backup ~]$ mkdir -p scripts/logs [oracle@backup ~]$ mkdir -p scripts/tmp

5.3创建rman脚本

·0级rman备份脚本

[oracle@backup ~]$ cdrman/scripts/bin [oracle@backup bin]$ vi level0_backup.rcv connect catalog rman/rman connect target sys/oracle@prod run{

allocate channel c1 device type disk; allocate channel c2 device type disk; allocate channel c3 device type disk;

backup incremental level 0 database format'/backup/level_0_%U.bak'; sql'alter system archive log current';

backuparchivelog all format'/backup/archivelog_%U.bak'; release channel c1; release channel c2; release channel c3; }

crosscheck backup; delete expired backup; delete obsolete; resync catalog;

·2级rman备份脚本

[oracle@backup ~]$ cdrman/scripts/bin [oracle@backup bin]$ vi level2_backup.rcv connect catalog rman/rman connect target sys/oracle@prod run{

allocate channel c1 device type disk;

backup incremental level 2 database format'/backup/level1_%d_%s_%p_%u.bak'; sql'alter system archive log current';

backuparchivelog all format'/backup/archivelog_1_%d_%s_%p_%u.bak' delete all input;

release channel c1; }

crosscheck backup; delete expired backup; delete obsolete; resync catalog;

5.4创建shell脚本

·Shell脚本level0_backup.sh（用来调用0级rman备份脚本）

[oracle@backup ~]$ cd scripts/bin [oracle@backup bin]$ vi level0_backup.sh #!/bin/bash cd $HOME source .bash_profile cd $HOME/rman/scripts/bin

rmancmdfile=level0_backup.rcv msglog=$HOME/scripts/logs/level0_backup.log

·Shell脚本level2_backup.sh（用来调用2级rman备份脚本）

[oracle@backup ~]$ cd scripts/bin [oracle@backup bin]$ vi level2_backup.sh #!/bin/bash cd $HOME source .bash_profile cd $HOME/rman/scripts/bin

rmancmdfile=level2_backup.rcv msglog=$HOME/scripts/logs/level2_backup.log

·创建文件wc.dbf用来存放变量levelbackup（levelbackup表示backup.sh调用次数）把初始值置为0

[oracle@backup ~]$ cd scripts/tmp [oracle@backuptmp]$ viwc.dbf levelbackup=0

·Shell脚本backup.sh用来调用level0_backup.sh和level2_backup.sh

[oracle@backup ~]$ cd scripts/bin [oracle@backup bin]$ vibackup.sh #!/bin/bash

levelbackup=`more /home/oracle/scripts/bin/wc.dbf|tail -1|awk -F= '{print $2}'` if [ $levelbackup -eq 0 ] then

sh /home/oracle/scripts/bin/level0_backup.sh levelbackup=`expr $levelbackup + 1`

echo \"levelbackup=\"$levelbackup>/home/oracle/scripts/bin/wc.dbf elif [ $levelbackup -le 6 ] then

sh /home/oracle/scripts/bin/level2_backup.sh levelbackup=`expr $levelbackup + 1`

echo \"levelbackup=\"$levelbackup>/home/oracle/scripts/bin/wc.dbf else

sh /home/oracle/scripts/bin/level0_backup.sh levelbackup=`expr 1`

echo \"levelbackup=\"$levelbackup>/home/oracle/scripts/bin/wc.dbf fi

6定时任务

编写crontab每20分钟执行一次备份并把标准输出和错误输出写入日志

[oracle@backup ~]$ crontab -l

*/20 * * * * /home/oracle/scripts/bin/backup.sh /home/oracle/scripts/logs/backup.log 2>&1

>>

7恢复测试

7.1远程恢复

远程恢复服务器：

主机 操作系统 数据库版本

IBM RS/6000 AIX 5.3.0 Oracle10.2.0

存储 光前阵列raid5 挂载备份服务器上共享目录/u01到远程恢复服务器上/backup02（操作同上述nfs挂载）

7.1.1拷贝生产库的pfile到远程服务器的相关目录下并修改： 远程恢复服务器：

-bash-3.00$scp 192.168.8.197:/u01/app/oracle/product/10.2.0/db_1 /dbs/initprod.ora /u02/app/oracle/product/10.2.0/db_1/dbs/

修改pfile文件，修改内容如下：

*.audit_file_dest='/u02/app/oracle/admin/port/adump' *.background_dump_dest='/u02/app/oracle/admin/port/bdump'

*.control_files='/u02/app/oracle/oradata/port/control01.ctl','/u02/app/oracle/oradata/port/control02.ctl','/u02/app/oracle/oradata/port/control03.ctl' *.core_dump_dest='/u02/app/oracle/admin/port/cdump' *.db_name='prod'

*.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area'

*.log_archive_dest_1='location=/u02/app/oracle/product/10.2.0/db_1/dbs/archive' *.user_dump_dest='/u02/app/oracle/admin/port/udump'

7.1.2创建相应目录：

-bash-3.00$ mkdir /u02/app/oracle/admin/port/adump -bash-3.00$ mkdir/u02/app/oracle/admin/port/bdump -bash-3.00$ mkdir/u02/app/oracle/oradata/port/ -bash-3.00$ mkdir/u02/app/oracle/admin/port/cdump -bash-3.00$ mkdir/u02/app/oracle/flash_recovery_area

-bash-3.00$ mkdir/u02/app/oracle/product/10.2.0/db_1/dbs/archive

-bash-3.00$ mkdir/u02/app/oracle/admin/port/udump

7.1.3恢复数据库

7.1.3.1远程恢复库，启动数据库到nomount状态：

-bash-3.00$ export ORACLE_SID=prod -bash-3.00$ sqlplus / as sysdba SQL> startup nomount

运用rman连接目标数据库和catalog数据库：

-bash-3.00$ export ORACLE_SID=prod

-bash-3.00$ rman target / catalog rman/rman@catalog

转储控制文件，并启动到mount状态：

RMAN> restore controlfilefrom '/backup/level1_PROD_1_1_54mpd7su.bak'; SQL> startup mount

对redolog文件进行改名操作：

SQL> alter database rename file '/u01/app/oracle/oradata/prod/redo03.log' to '/u02/app/oracle/oradata/prod/redo03.log';

SQL> alter database rename file '/u01/app/oracle/oradata/prod/redo02.log' to '/u02/app/oracle/oradata/prod/redo02.log';

SQL> alter database rename file '/u01/app/oracle/oradata/prod/redo01.log' to '/u02/app/oracle/oradata/prod/redo01.log';

执行恢复操作：

RMAN>run{

setnewname for datafile 1 to '/u02/app/oracle/oradata/prod/system01.dbf'; setnewname for datafile 2 to '/u02/app/oracle/oradata/prod/undotbs01.dbf';

setnewname for datafile 3 to '/u02/app/oracle/oradata/prod/sysaux01.dbf'; setnewname for datafile 4 to '/u02/app/oracle/oradata/prod/users01.dbf'; setnewname for datafile 5 to '/u02/app/oracle/oradata/prod/example01.dbf'; restore database; recover database;

alter database open resetlogs; }

打开数据库：

SQL>alter database open; 打开成功恢复完成

7.2本地恢复

7.2.1拷贝pfile，修改有关参数

-bash-3.00$ cd $ORACLE_HOME/dbs -bash-3.00$ cpinitprod.orainitcuug.ora

修改pfile参数，修改内容如下：

*.audit_file_dest='/u01/app/oracle/admin/cuug/adump' *.background_dump_dest='/u01/app/oracle/admin/cuug/bdump'

*.control_files='/u01/app/oracle/oradata/cuug/control01.ctl','/u01/app/oracle/oradata/cuug/control02.ctl','/u01/app/oracle/oradata/c uug/control03.ctl'#Restore Controlfile

*.core_dump_dest='/u01/app/oracle/admin/cuug/cdump' *.db_name='prod'

*.log_archive_dest_1='location=/u01/app/oracle/product/10.2.0/db_1/dbs/archive' *.user_dump_dest='/u01/app/oracle/admin/cuug/udump'

DB_UNIQUE_NAME=cuug

7.2.2创建相应目录：

-bash-3.00$ mkdir /u01/app/oracle/admin/cuug/adump -bash-3.00$ mkdir/u01/app/oracle/admin/cuug/bdump -bash-3.00$ mkdir/u02/app/oracle/oradata/cuug/ -bash-3.00$ mkdir/u02/app/oracle/admin/cuug/cdump

-bash-3.00$ mkdir/u01/app/oracle/product/10.2.0/db_1/dbs/archive -bash-3.00$ mkdir /u01/app/oracle/admin/cuug/udump

7.3恢复数据库：

启动恢复数据库cuug到nomount状态:

-bash-3.00$ export ORACLE_SID=cuug -bash-3.00$ sqlplus / as sysdba SQL> startup nomount

运用rman连接目标数据库和catalog数据库：

-bash-3.00$ export ORACLE_SID=cuug

-bash-3.00$ rman target / catalog rman/rman@catalog

转储控制文件，并启动到mount状态：

RMAN> restore controlfilefrom '/backup/level1_PROD_1_1_54mpd7su.bak'; SQL> startup mount

对redolog文件进行改名操作：

SQL> alter database rename file '/u01/app/oracle/oradata/prod/redo03.log' to '/u01/app/oracle/oradata/cuug/redo03.log';

SQL> alter database rename file '/u01/app/oracle/oradata/prod/redo02.log' to '/u01/app/oracle/oradata/cuug/redo02.log';

SQL> alter database rename file '/u01/app/oracle/oradata/prod/redo01.log' to '/u01/app/oracle/oradata/cuug/redo01.log';

执行恢复操作：

RMAN>run{

setnewname for datafile 1 to '/u01/app/oracle/oradata/cuug/system01.dbf'; setnewname for datafile 2 to '/u01/app/oracle/oradata/cuug/undotbs01.dbf'; setnewname for datafile 3 to '/u01/app/oracle/oradata/cuug/sysaux01.dbf'; setnewname for datafile 4 to '/u01/app/oracle/oradata/cuug/users01.dbf'; setnewname for datafile 5 to '/u01/app/oracle/oradata/cuug/example01.dbf'; restore database; recover database;

alter database open resetlogs; }

打开数据库：

SQL>alter database open; 打开成功恢复完成