合 Oracle rac数据库的备份和恢复

2022年11月11日

0 371 1

Tags： Oracle rac 备份恢复

👉 本文共约16623个字，系统预计阅读时间或需63分钟。

前言部分
导读
实验环境介绍
本文简介
实验部分
实验目标
查看rac环境
创建测试数据
source库执行备份操作
删除spfile、控制文件和数据文件
恢复数据库过程
恢复spfile
恢复控制文件
还原及恢复数据文件
RESETLOGS打开数据库并验证数据
修复另一个节点的pfile
实验总结
总结

前言部分

导读

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~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

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

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

channel c1: deleting archived log(s)

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_1_seq_1.357.881255367 RECID=5 STAMP=881255375

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_2_seq_1.355.881252641 RECID=4 STAMP=881252641

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_1_seq_2.362.881256605 RECID=7 STAMP=881256606

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_3_seq_1.361.881255613 RECID=6 STAMP=881255612

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_2_seq_2.363.881257349 RECID=8 STAMP=881257349

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_1_seq_3.365.881257705 RECID=9 STAMP=881257704

archived log file name=+ARCH/orastrac/archivelog/2015_06_01/thread_2_seq_3.367.881257709 RECID=12 STAMP=881257709

channel c2: finished piece 1 at 02-JUN-2015 13:39:03

piece handle=/home/oracle/rman_back/arch_ORASTRAC_20150602_15_1.bak tag=TAG20150602T133859 comment=NONE

channel c2: backup set complete, elapsed time: 00:00:00

channel c2: deleting archived log(s)

archived log file name=+ARCH/orastrac/archivelog/2015_06_02/thread_1_seq_6.359.881329131 RECID=14 STAMP=881329136

archived log file name=+ARCH/orastrac/archivelog/2015_06_02/thread_2_seq_5.370.881329139 RECID=16 STAMP=881329139

archived log file name=+ARCH/orastrac/archivelog/2015_06_02/thread_1_seq_7.358.881329137 RECID=15 STAMP=881329138

Finished backup at 02-JUN-2015 13:39:03

Starting backup at 02-JUN-2015 13:39:05

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 02-JUN-2015 13:39:06

channel c1: finished piece 1 at 02-JUN-2015 13:39:07

piece handle=/home/oracle/rman_back/ctl_ORASTRAC_20150602_16_1.bak tag=TAG20150602T133905 comment=NONE

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JUN-2015 13:39:07

released channel: c1

released channel: c2

RMAN>

RMAN> list backupset summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

------- -- -- - ----------- -------------------- ------- ------- ---------- ---

7 B F A DISK 02-JUN-2015 13:38:30 1 1 NO TAG20150602T133632

8 B F A DISK 02-JUN-2015 13:38:35 1 1 NO TAG20150602T133632

9 B F A DISK 02-JUN-2015 13:38:40 1 1 NO TAG20150602T133632

10 B F A DISK 02-JUN-2015 13:38:42 1 1 NO TAG20150602T133632

11 B A A DISK 02-JUN-2015 13:39:01 1 1 NO TAG20150602T133859

12 B A A DISK 02-JUN-2015 13:39:01 1 1 NO TAG20150602T133859

13 B A A DISK 02-JUN-2015 13:39:03 1 1 NO TAG20150602T133859

14 B F A DISK 02-JUN-2015 13:39:06 1 1 NO TAG20150602T133905

RMAN>

删除spfile、控制文件和数据文件

删除asm文件，毁掉整个数据库：

[root@node2 rman_back]# srvctl stop database -d ORASTRAC

[root@node2 rman_back]# srvctl status database -d ORASTRAC

Instance orastrac1 is not running on node node1

Instance orastrac2 is not running on node node2

[root@node2 rman_back]# su - grid

[grid@node2 ~]$ asmcmd

ASMCMD> cd data

ASMCMD> ls

DB/

DB_UNKNOWN/

JMRAC/

MYRAC/

ORA11G/

ORASTRAC/

ASMCMD> rm -rf ORASTRAC/

ASMCMD> ls

DB/

DB_UNKNOWN/

JMRAC/

MYRAC/

ORA11G/

ASMCMD>

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 2 13:55:46 2015

Connected to an idle instance.

SQL> startup

ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/orastrac/parameterfile/spfile.335.881250575'

ORA-17503: ksfdopn:2 Failed to open file +DATA/orastrac/parameterfile/spfile.335.881250575

ORA-15056: additional error message

ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orastrac/parameterfile/spfile.335.881250575

ORA-17503: ksfdopn:2 Failed to open file +DATA/orastrac/parameterfile/spfile.335.881250575

ORA-15173: entry 'orastrac' does not exist in directory '/'

ORA-06512: at line 4

SQL>

恢复数据库过程

恢复spfile

首先利用rman恢复spfile：

[oracle@node2 ~]$ ORACLE_SID=orastrac2

[oracle@node2 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 2 14:15:52 2015

connected to target database (not started)

RMAN> set dbid 1317814272

executing command: SET DBID

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/orastrac/parameterfile/spfile.335.881250575'

ORA-17503: ksfdopn:2 Failed to open file +DATA/orastrac/parameterfile/spfile.335.881250575

ORA-15056: additional error message

ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/orastrac/parameterfile/spfile.335.881250575

ORA-17503: ksfdopn:2 Failed to open file +DATA/orastrac/parameterfile/spfile.335.881250575

ORA-15173: entry 'orastrac' does not exist in directory '/'

ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2211448 bytes

Variable Size 96469384 bytes

Database Buffers 54525952 bytes

Redo Buffers 5455872 bytes

RMAN> restore spfile to '+DATA/orastrac/parameterfile/spfileorastrac.ora' from '/home/oracle/rman_back/full_ORASTRAC_20150602_881329118_12_1.bak';

Starting restore at 02-JUN-2015 14:16:47

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/rman_back/full_ORASTRAC_20150602_881329118_12_1.bak

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 06/02/2015 14:16:49

ORA-19870: error while restoring backup piece /home/oracle/rman_back/full_ORASTRAC_20150602_881329118_12_1.bak

ORA-32015: unable to restore SPFILE

ORA-17502: ksfdcre:4 Failed to create file +DATA/orastrac/parameterfile/spfileorastrac.ora

ORA-15056: additional error message

ORA-17502: ksfdcre:4 Failed to create file +DATA/orastrac/parameterfile/spfileorastrac.ora

ORA-15173: entry 'orastrac' does not exist in directory '/'

创建相关路径后继续执行：

ASMCMD> mkdir orastrac

ASMCMD> cd orastrac

ASMCMD> mkdir parameterfile

ASMCMD> pwd

+data/orastrac

ASMCMD> cd parameterfile

RMAN> restore spfile to '+DATA/orastrac/parameterfile/spfileorastrac.ora' from '/home/oracle/rman_back/full_ORASTRAC_20150602_881329118_12_1.bak';

Starting restore at 02-JUN-2015 14:18:07

using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/rman_back/full_ORASTRAC_20150602_881329118_12_1.bak

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 02-JUN-2015 14:18:08

RMAN>

查看spfile：

ASMCMD> ls

spfileorastrac.ora

ASMCMD>

修改pfile文件后重启数据库：

[oracle@node2 dbs]$ more initorastrac2.ora

SPFILE='+DATA/orastrac/parameterfile/spfileorastrac.ora'

[oracle@node2 dbs]$

[oracle@node2 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 2 14:21:18 2015

connected to target database: DUMMY (not mounted)

RMAN> shutdown abort;

using target database control file instead of recovery catalog

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)

Oracle instance started

Total System Global Area 409194496 bytes

Fixed Size 2213856 bytes

Variable Size 360712224 bytes

Database Buffers 41943040 bytes

Redo Buffers 4325376 bytes

RMAN>

恢复控制文件

[oracle@node2 dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 2 14:22:52 2015

connected to target database: ORASTRAC (not mounted)

RMAN> startup nomount force;

Oracle instance started

Total System Global Area 409194496 bytes

Fixed Size 2213856 bytes

Variable Size 360712224 bytes

Database Buffers 41943040 bytes

Redo Buffers 4325376 bytes

RMAN> restore controlfile from '/home/oracle/rman_back/ctl_ORASTRAC_20150602_16_1.bak';

Starting restore at 02-JUN-2015 14:23:30

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=33 instance=orastrac2 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DATA/orastrac/controlfile/control01.ctl

output file name=+DATA/orastrac/controlfile/current.329.881331811

output file name=+ARCH/orastrac/controlfile/current.364.881257391

Finished restore at 02-JUN-2015 14:23:32

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： Oracle rac 备份恢复

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 Oracle rac数据库的备份和恢复

前言部分

导读

实验环境介绍

本文简介

实验部分

实验目标

查看rac环境

创建测试数据

source库执行备份操作

删除spfile、控制文件和数据文件

恢复数据库过程

恢复spfile

恢复控制文件

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 Oracle rac数据库的备份和恢复

前言部分

导读

实验环境介绍

本文简介

实验部分

实验目标

查看rac环境

创建测试数据

source库执行备份操作

删除spfile、控制文件和数据文件

恢复数据库过程

恢复spfile

恢复控制文件

相关文章

您可能还喜欢...

【DB宝6】 Oracle 2019 OCP考试简介

12c RMAN新特性之UNTIL AVAILABLE REDO--自动恢复到REDO终点的步骤简化

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复