合 Oracle rac数据库的备份和恢复
前言部分
导读
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① rac环境下的数据库备份与还原
② rman恢复数据库的一般步骤
③ rac环境的简单操作
实验环境介绍
目标库:11.2.0.1 RHEL6.5 rac环境
本文简介
实验的一些数据库环境参考如下表格:
项目 | source db | target db |
---|---|---|
db 类型 | rac环境 | rac环境 |
db version | 11.2.0.1 | 11.2.0.1 |
ORACLE_SID | orastrac1 和 orastrac2 | orastrac1 和 orastrac2 |
db_name | orastrac | orastrac |
主机IP地址: | 192.168.1.31 192.168.1.32 | 192.168.1.31 192.168.1.32 |
实验部分
实验目标
验证rac数据库环境下的备份和恢复操作。
查看rac环境
target库已经有4个库,3个rac库处于close状态,orastrac处于online状态。
[root@node2 ~]# crsstat
Name Type Target State Host
------------------------------ -------------------------- ---------- --------- -------
ora.ARCH.dg ora.diskgroup.type ONLINE ONLINE node1
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE node1
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE node1
ora.LISTENER_SCAN1.lsnr ora.scan_listener.type ONLINE ONLINE node1
ora.OVDISK.dg ora.diskgroup.type ONLINE ONLINE node1
ora.TEST.dg ora.diskgroup.type ONLINE ONLINE node1
ora.asm ora.asm.type ONLINE ONLINE node1
ora.db.db ora.database.type OFFLINE OFFLINE
ora.eons ora.eons.type ONLINE ONLINE node1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora.jmrac.db ora.database.type ONLINE ONLINE node1
ora.jmrac.haha.svc ora.service.type ONLINE ONLINE node1
ora.net1.network ora.network.type ONLINE ONLINE node1
ora.node1.ASM1.asm application ONLINE ONLINE node1
ora.node1.LISTENER_NODE1.lsnr application ONLINE ONLINE node1
ora.node1.gsd application OFFLINE OFFLINE
ora.node1.ons application ONLINE ONLINE node1
ora.node1.vip ora.cluster_vip_net1.type ONLINE ONLINE node1
ora.node2.ASM2.asm application ONLINE ONLINE node2
ora.node2.LISTENER_NODE2.lsnr application ONLINE ONLINE node2
ora.node2.gsd application OFFLINE OFFLINE
ora.node2.ons application ONLINE ONLINE node2
ora.node2.vip ora.cluster_vip_net1.type ONLINE ONLINE node2
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE node1
ora.ora11g.db ora.database.type OFFLINE OFFLINE
ora.registry.acfs ora.registry.acfs.type ONLINE ONLINE node1
ora.scan1.vip ora.scan_vip.type ONLINE ONLINE node1
[root@node2 ~]# crsstat | grep ora.database.type
ora.db.db ora.database.type OFFLINE OFFLINE
ora.jmrac.db ora.database.type OFFLINE OFFLINE
ora.ora11g.db ora.database.type OFFLINE OFFLINE
[root@node2 ~]#
[root@node2 ~]# crsstat | grep ora.database.type
ora.db.db ora.database.type ONLINE OFFLINE
ora.jmrac.db ora.database.type OFFLINE OFFLINE
ora.ora11g.db ora.database.type OFFLINE OFFLINE
ora.orastrac.db ora.database.type ONLINE ONLINE node1
[root@node2 ~]#
创建测试数据
[oracle@node2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 2 13:32:34 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orastrac
db_unique_name string orastrac
global_names boolean FALSE
instance_name string orastrac2
lock_name_space string
log_file_name_convert string
service_names string orastrac
SQL> create table lhr.rac_test as select * from dba_objects;
Table created.
SQL> select count(1) from lhr.rac_test;
COUNT(1)
----------
72469
SQL>
source库执行备份操作
备份脚本如下,注意控制文件需要最后备份,在节点2上执行备份:
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/home/oracle/rmanback/full%n%T%t%s%p.bak';
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rmanback/arch%d%T%s_%p.bak' delete input;
backup current controlfile format '/home/oracle/rmanback/ctl%d%T%s_%p.bak';
release channel c1;
release channel c2;
}
执行过程如下:
[oracle@node2 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 2 13:36:21 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORASTRAC (DBID=1317814272)
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> run
2> {
3> allocate channel c1 type disk;
4> allocate channel c2 type disk;
5> backup database format '/home/oracle/rmanback/full%n%T%t%s%p.bak';
6> sql 'alter system archive log current';
7> backup archivelog all format '/home/oracle/rmanback/arch%d%T%s_%p.bak' delete input;
8> backup current controlfile format '/home/oracle/rmanback/ctl%d%T%s_%p.bak';
9> release channel c1;
10> release channel c2;
11> }
allocated channel: c1
channel c1: SID=33 instance=orastrac2 device type=DISK
allocated channel: c2
channel c2: SID=51 instance=orastrac2 device type=DISK
Starting backup at 02-JUN-2015 13:36:32
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orastrac/datafile/system.330.881251509
input datafile file number=00004 name=+DATA/orastrac/datafile/users.327.881251511
input datafile file number=00006 name=+DATA/orastrac/datafile/undotbs2.351.881252419
channel c1: starting piece 1 at 02-JUN-2015 13:36:33
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orastrac/datafile/sysaux.331.881251461
input datafile file number=00005 name=+DATA/orastrac/datafile/example.332.881251463
input datafile file number=00003 name=+DATA/orastrac/datafile/undotbs1.329.881251463
channel c2: starting piece 1 at 02-JUN-2015 13:36:33
channel c1: finished piece 1 at 02-JUN-2015 13:38:38
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150602_881328992_9_1.bak tag=TAG20150602T133632 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:05
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
channel c2: finished piece 1 at 02-JUN-2015 13:38:38
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150602_881328993_10_1.bak tag=TAG20150602T133632 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:05
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c2: starting piece 1 at 02-JUN-2015 13:38:39
including current control file in backup set
channel c1: starting piece 1 at 02-JUN-2015 13:38:42
channel c2: finished piece 1 at 02-JUN-2015 13:38:42
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150602_881329118_12_1.bak tag=TAG20150602T133632 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 02-JUN-2015 13:38:43
piece handle=/home/oracle/rman_back/full_ORASTRAC_20150602_881329118_11_1.bak tag=TAG20150602T133632 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-JUN-2015 13:38:43
sql statement: alter system archive log current
Starting backup at 02-JUN-2015 13:38:56
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=881255375
input archived log thread=2 sequence=1 RECID=4 STAMP=881252641
input archived log thread=1 sequence=2 RECID=7 STAMP=881256606
input archived log thread=3 sequence=1 RECID=6 STAMP=881255612
input archived log thread=2 sequence=2 RECID=8 STAMP=881257349
input archived log thread=1 sequence=3 RECID=9 STAMP=881257704
input archived log thread=2 sequence=3 RECID=12 STAMP=881257709
channel c1: starting piece 1 at 02-JUN-2015 13:39:00
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=10 STAMP=881257707
input archived log thread=1 sequence=5 RECID=11 STAMP=881257708
input archived log thread=2 sequence=4 RECID=13 STAMP=881329134
channel c2: starting piece 1 at 02-JUN-2015 13:39:00
channel c2: finished piece 1 at 02-JUN-2015 13:39:01
piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150602_14_1.bak tag=TAG20150602T133859 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:01
channel c2: deleting archived log(s)
archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_1_seq_4.366.881257705 RECID=10 STAMP=881257707
archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_1_seq_5.368.881257709 RECID=11 STAMP=881257708
archived log file name=+ARCH/orastrac/archivelog/2015_06_02/thread_2_seq_4.360.881329131 RECID=13 STAMP=881329134
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=14 STAMP=881329136
input archived log thread=2 sequence=5 RECID=16 STAMP=881329139
input archived log thread=1 sequence=7 RECID=15 STAMP=881329138
channel c2: starting piece 1 at 02-JUN-2015 13:39:03
channel c1: finished piece 1 at 02-JUN-2015 13:39:03
piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150602_13_1.bak tag=TAG20150602T133859 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03