合 Oracle rac数据库备份集恢复到单机环境

发布日期 2022年11月11日 · 已更新 2022年12月1日

0 509 1

Tags： Oracle rac 备份恢复单机

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

前言部分
导读
实验环境介绍
实验部分
实验目标
源rac库执行
查看rac环境及创建测试表
生成pfile文件
执行备份操作
将备份传递到target库
target库上执行
修改pfile文件生成spfile文件、生成pfile中的文件路径
启动到nomount状态并还原控制文件
启动到mount状态并还原和恢复整个数据库
restore 数据库
recover数据库
RESETLOGS打开数据库并验证数据
后续收尾操作
清除未使用的redo
清除多余的 undo 文件
实验总结
总结
How To Restore RMAN Disk backups of RAC Database to Single Instance On Another Node (文档 ID 415579.1)
APPLIES TO:
GOAL
SOLUTION
REFERENCES

前言部分

导读

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① rac数据库的备份集是如何恢复到单实例的数据库

② ASM文件系统到OS文件系统的转换

③ 一般的备份恢复过程

实验环境介绍

源库：11.2.0.1 rac库 2个节点

目标库：11.2.0.1 RHEL6.5

本文也可以理解成rac环境下的如何数据库迁移到单实例的数据库环境下，默认目标库已经安装好了同源库一样的数据库版本。

实验部分

实验目标

将11.2.0.1下的rac库备份并恢复到11.2.0.1下的单实例环境下。

源rac库执行

rac库需要执行备份并传递到目标库。

查看rac环境及创建测试表

[root@node2 ~]# cat /etc/hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

#public

192.168.1.31 node1

192.168.1.32 node2

#vip

192.168.1.131 node1-vip

192.168.1.132 node2-vip

#priv

9.9.9.31 node1-priv

9.9.9.32 node2-priv

#scan

192.168.1.35 cluster-scan

[root@node2 ~]# ifconfig

eth0 Link encap:Ethernet HWaddr 00:0C:29:79:BA:86

inet addr:192.168.1.32 Bcast:192.168.1.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:150190 errors:0 dropped:0 overruns:0 frame:0

TX packets:109804 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:205303912 (195.7 MiB) TX bytes:20182601 (19.2 MiB)

eth0:1 Link encap:Ethernet HWaddr 00:0C:29:79:BA:86

inet addr:192.168.1.132 Bcast:192.168.1.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

eth1 Link encap:Ethernet HWaddr 00:0C:29:79:BA:90

inet addr:9.9.9.32 Bcast:9.9.9.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:49075 errors:0 dropped:0 overruns:0 frame:0

TX packets:49811 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:23642469 (22.5 MiB) TX bytes:31528595 (30.0 MiB)

lo Link encap:Local Loopback

inet addr:127.0.0.1 Mask:255.0.0.0

UP LOOPBACK RUNNING MTU:16436 Metric:1

RX packets:16496 errors:0 dropped:0 overruns:0 frame:0

TX packets:16496 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:15118447 (14.4 MiB) TX bytes:15118447 (14.4 MiB)

[root@node2 ~]#

[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 ~]#

[oracle@node2 ~]$ ORACLE_SID=jmrac2

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 10:58:42 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 cluster

NAME TYPE VALUE

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

cluster_database boolean TRUE

cluster_database_instances integer 2

cluster_interconnects string

SQL> show parameter name

NAME TYPE VALUE

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

db_file_name_convert string

db_name string jmrac

db_unique_name string jmrac

global_names boolean FALSE

instance_name string jmrac2

lock_name_space string

log_file_name_convert string

service_names string HAHA

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 41

Next log sequence to archive 42

Current log sequence 42

SQL>

SQL> create table lhr.rac_to_single_test as select * from dba_objects;

Table created.

SQL> select count(1) from lhr.rac_to_single_test ;

COUNT(1)

----------

72510

SQL>

SQL> set line 9999 pagesize 9999

SQL> col FILE_NAME format a60

SQL> select 'datafile' file_type, file#,name FILE_NAME,status,enabled from v$datafile

2 union all

3 select 'tempfile',file#,name FILE_NAME,status,enabled from v$tempfile

4 union all

5 select 'logfile',group# file#,member FILE_NAME,status,'' from v$logfile

6 union all

7 select 'controlfile', to_number('') ,name FILE_NAME,status,'' from v$controlfile

8 ;

FILE_TYPE FILE# FILE_NAME STATUS ENABLED

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

datafile 1 +DATA/jmrac/datafile/system.268.877470209 SYSTEM READ WRITE

datafile 2 +DATA/jmrac/datafile/sysaux.269.877470211 ONLINE READ WRITE

datafile 3 +DATA/jmrac/datafile/undotbs1.270.877470213 ONLINE READ WRITE

datafile 4 +DATA/jmrac/datafile/users.271.877470213 ONLINE READ WRITE

datafile 5 +DATA/jmrac/datafile/example.279.877470401 ONLINE READ WRITE

datafile 6 +DATA/jmrac/datafile/undotbs2.280.877470779 ONLINE READ WRITE

tempfile 1 +DATA/jmrac/tempfile/temp.278.877470381 ONLINE READ WRITE

logfile 2 +DATA/jmrac/onlinelog/group_2.276.877470349

logfile 2 +DATA/jmrac/onlinelog/group_2.277.877470349

logfile 1 +DATA/jmrac/onlinelog/group_1.274.877470345

logfile 1 +DATA/jmrac/onlinelog/group_1.275.877470345

logfile 3 +DATA/jmrac/onlinelog/group_3.281.877470929

logfile 3 +DATA/jmrac/onlinelog/group_3.282.877470931

logfile 4 +DATA/jmrac/onlinelog/group_4.283.877470937

logfile 4 +DATA/jmrac/onlinelog/group_4.284.877470943

controlfile +DATA/jmrac/controlfile/current.273.877470341

controlfile +DATA/jmrac/controlfile/current.272.877470343

17 rows selected.

SQL>

我后续将在192.168.1.32即rac的第二个节点上执行操作，db_name为jmrac，数据库为归档模式，创建测试表lhr.rac_to_single_test，数据量为72510 行，其中有个crsstat命令，可以参考：【RAC】如何让Oracle RAC crs_stat 命令显示完整 http://blog.itpub.net/26736162/viewspace-1610957/

生成pfile文件

SQL> show parameter instance_n

NAME TYPE VALUE

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

instance_name string jmrac2

instance_number integer 2

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string +DATA/jmrac/spfilejmrac.ora

SQL> create pfile='/home/oracle/rman_back/initjmrac.ora' from spfile;

File created.

SQL>

执行备份操作

备份脚本如下：

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 Fri May 29 11:12:51 2015

connected to target database: JMRAC (DBID=1916705604)

{

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> }

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=55 instance=jmrac2 device type=DISK

allocated channel: c2

channel c2: SID=57 instance=jmrac2 device type=DISK

Starting backup at 29-MAY-2015 11:12:59

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/jmrac/datafile/system.268.877470209

input datafile file number=00004 name=+DATA/jmrac/datafile/users.271.877470213

input datafile file number=00006 name=+DATA/jmrac/datafile/undotbs2.280.877470779

channel c1: starting piece 1 at 29-MAY-2015 11:13:00

channel c2: starting full datafile backup set

channel c2: specifying datafile(s) in backup set

input datafile file number=00002 name=+DATA/jmrac/datafile/sysaux.269.877470211

input datafile file number=00005 name=+DATA/jmrac/datafile/example.279.877470401

input datafile file number=00003 name=+DATA/jmrac/datafile/undotbs1.270.877470213

channel c2: starting piece 1 at 29-MAY-2015 11:13:00

channel c1: finished piece 1 at 29-MAY-2015 11:15:35

piece handle=/home/oracle/rman_back/full_JMRACxxx_20150529_880974780_5_1.bak tag=TAG20150529T111259 comment=NONE

channel c1: backup set complete, elapsed time: 00:02:35

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

channel c2: finished piece 1 at 29-MAY-2015 11:15:35

piece handle=/home/oracle/rman_back/full_JMRACxxx_20150529_880974780_6_1.bak tag=TAG20150529T111259 comment=NONE

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

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 29-MAY-2015 11:15:35

including current control file in backup set

channel c1: starting piece 1 at 29-MAY-2015 11:15:38

channel c2: finished piece 1 at 29-MAY-2015 11:15:38

piece handle=/home/oracle/rman_back/full_JMRACxxx_20150529_880974935_8_1.bak tag=TAG20150529T111259 comment=NONE

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

channel c1: finished piece 1 at 29-MAY-2015 11:15:40

piece handle=/home/oracle/rman_back/full_JMRACxxx_20150529_880974935_7_1.bak tag=TAG20150529T111259 comment=NONE

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

Finished backup at 29-MAY-2015 11:15:40

sql statement: alter system archive log current

Starting backup at 29-MAY-2015 11:15:53

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=2 sequence=31 RECID=50 STAMP=879502099

input archived log thread=1 sequence=24 RECID=52 STAMP=879511365

input archived log thread=2 sequence=32 RECID=51 STAMP=879502100

input archived log thread=1 sequence=25 RECID=55 STAMP=879527440

input archived log thread=2 sequence=33 RECID=53 STAMP=879522769

input archived log thread=2 sequence=34 RECID=54 STAMP=879527240

input archived log thread=2 sequence=35 RECID=57 STAMP=879586992

input archived log thread=1 sequence=26 RECID=56 STAMP=879527447

input archived log thread=1 sequence=27 RECID=60 STAMP=879590456

input archived log thread=2 sequence=36 RECID=58 STAMP=879586995

input archived log thread=2 sequence=37 RECID=59 STAMP=879590456

input archived log thread=1 sequence=28 RECID=61 STAMP=879590457

channel c1: starting piece 1 at 29-MAY-2015 11:16:05

channel c2: starting archived log backup set

channel c2: specifying archived log(s) in backup set

input archived log thread=2 sequence=38 RECID=63 STAMP=880971338

input archived log thread=1 sequence=29 RECID=62 STAMP=880971333

input archived log thread=2 sequence=39 RECID=64 STAMP=880971341

input archived log thread=1 sequence=30 RECID=65 STAMP=880972786

input archived log thread=2 sequence=40 RECID=66 STAMP=880972787

input archived log thread=2 sequence=41 RECID=67 STAMP=880972787

input archived log thread=1 sequence=31 RECID=68 STAMP=880974598

channel c2: starting piece 1 at 29-MAY-2015 11:16:05

channel c1: finished piece 1 at 29-MAY-2015 11:16:20

piece handle=/home/oracle/rman_back/arch_JMRAC_20150529_9_1.bak tag=TAG20150529T111603 comment=NONE

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

channel c1: deleting archived log(s)

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_2_seq_31.359.879502097 RECID=50 STAMP=879502099

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_1_seq_24.356.879511361 RECID=52 STAMP=879511365

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_2_seq_32.357.879502099 RECID=51 STAMP=879502100

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_1_seq_25.352.879527441 RECID=55 STAMP=879527440

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_2_seq_33.353.879522767 RECID=53 STAMP=879522769

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_2_seq_34.351.879527239 RECID=54 STAMP=879527240

archived log file name=+DATA/jmrac/archivelog/2015_05_13/thread_2_seq_35.333.879586991 RECID=57 STAMP=879586992

archived log file name=+DATA/jmrac/archivelog/2015_05_12/thread_1_seq_26.335.879527445 RECID=56 STAMP=879527447

archived log file name=+DATA/jmrac/archivelog/2015_05_13/thread_1_seq_27.331.879590457 RECID=60 STAMP=879590456

archived log file name=+DATA/jmrac/archivelog/2015_05_13/thread_2_seq_36.334.879586995 RECID=58 STAMP=879586995

archived log file name=+DATA/jmrac/archivelog/2015_05_13/thread_2_seq_37.332.879590455 RECID=59 STAMP=879590456

archived log file name=+DATA/jmrac/archivelog/2015_05_13/thread_1_seq_28.329.879590457 RECID=61 STAMP=879590457

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=2 sequence=42 RECID=70 STAMP=880974952

input archived log thread=1 sequence=32 RECID=69 STAMP=880974952

input archived log thread=1 sequence=33 RECID=72 STAMP=880974959

input archived log thread=2 sequence=43 RECID=71 STAMP=880974953

channel c1: starting piece 1 at 29-MAY-2015 11:16:23

channel c2: finished piece 1 at 29-MAY-2015 11:16:23

piece handle=/home/oracle/rman_back/arch_JMRAC_20150529_10_1.bak tag=TAG20150529T111603 comment=NONE

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

channel c2: deleting archived log(s)

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_2_seq_38.330.880971315 RECID=63 STAMP=880971338

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_1_seq_29.327.880971317 RECID=62 STAMP=880971333

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_2_seq_39.326.880971341 RECID=64 STAMP=880971341

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_1_seq_30.328.880972785 RECID=65 STAMP=880972786

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_2_seq_40.325.880972785 RECID=66 STAMP=880972787

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_2_seq_41.324.880972787 RECID=67 STAMP=880972787

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_1_seq_31.322.880974595 RECID=68 STAMP=880974598

channel c1: finished piece 1 at 29-MAY-2015 11:16:23

piece handle=/home/oracle/rman_back/arch_JMRAC_20150529_11_1.bak tag=TAG20150529T111603 comment=NONE

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

channel c1: deleting archived log(s)

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_2_seq_42.321.880974951 RECID=70 STAMP=880974952

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_1_seq_32.323.880974951 RECID=69 STAMP=880974952

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_1_seq_33.318.880974959 RECID=72 STAMP=880974959

archived log file name=+DATA/jmrac/archivelog/2015_05_29/thread_2_seq_43.320.880974953 RECID=71 STAMP=880974953

Finished backup at 29-MAY-2015 11:16:23

Starting backup at 29-MAY-2015 11:16:24

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 29-MAY-2015 11:16:25

channel c1: finished piece 1 at 29-MAY-2015 11:16:26

piece handle=/home/oracle/rman_back/ctl_JMRAC_20150529_12_1.bak tag=TAG20150529T111624 comment=NONE

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

Finished backup at 29-MAY-2015 11:16:26

released channel: c1

released channel: c2

RMAN>

RMAN> exit

Recovery Manager complete.

[oracle@node2 ~]$ cd rman_back/

[oracle@node2 rman_back]$ ll

total 1313928

-rw-r----- 1 oracle asmadmin 85005824 May 29 11:16 arch_JMRAC_20150529_10_1.bak

-rw-r----- 1 oracle asmadmin 14320128 May 29 11:16 arch_JMRAC_20150529_11_1.bak

-rw-r----- 1 oracle asmadmin 34693632 May 29 11:16 arch_JMRAC_20150529_9_1.bak

-rw-r----- 1 oracle asmadmin 18579456 May 29 11:16 ctl_JMRAC_20150529_12_1.bak

-rw-r----- 1 oracle asmadmin 648372224 May 29 11:15 full_JMRACxxx_20150529_880974780_5_1.bak

-rw-r----- 1 oracle asmadmin 524435456 May 29 11:15 full_JMRACxxx_20150529_880974780_6_1.bak

-rw-r----- 1 oracle asmadmin 18579456 May 29 11:15 full_JMRACxxx_20150529_880974935_7_1.bak

-rw-r----- 1 oracle asmadmin 98304 May 29 11:15 full_JMRACxxx_20150529_880974935_8_1.bak

-rw-r--r-- 1 oracle asmadmin 1371 May 29 11:08 initjmrac.ora

[oracle@node2 rman_back]$

[oracle@node2 rman_back]$ ll -h

total 1.3G

-rw-r----- 1 oracle asmadmin 82M May 29 11:16 arch_JMRAC_20150529_10_1.bak

-rw-r----- 1 oracle asmadmin 14M May 29 11:16 arch_JMRAC_20150529_11_1.bak

-rw-r----- 1 oracle asmadmin 34M May 29 11:16 arch_JMRAC_20150529_9_1.bak

-rw-r----- 1 oracle asmadmin 18M May 29 11:16 ctl_JMRAC_20150529_12_1.bak

-rw-r----- 1 oracle asmadmin 619M May 29 11:15 full_JMRACxxx_20150529_880974780_5_1.bak

-rw-r----- 1 oracle asmadmin 501M May 29 11:15 full_JMRACxxx_20150529_880974780_6_1.bak

-rw-r----- 1 oracle asmadmin 18M May 29 11:15 full_JMRACxxx_20150529_880974935_7_1.bak

-rw-r----- 1 oracle asmadmin 96K May 29 11:15 full_JMRACxxx_20150529_880974935_8_1.bak

-rw-r--r-- 1 oracle asmadmin 1.4K May 29 11:08 initjmrac.ora

[oracle@node2 rman_back]$

将备份传递到target库

这个方法就多了，可以采用ftp上传下载，也可以采用NFS网络文件系统，或者scp命令都可以，这里我们采用scp直接传递。

源库：

[oracle@node2 rman_back]$ scp -r /home/oracle/rman_back oracle@192.168.59.129:/home/oracle

ssh: connect to host 192.168.59.129 port 22: Network is unreachable

lost connection

由于source db的IP为192.168.1.32，而目标库的IP为192.168.59.129，不在同一个网段，所以我对目标库再添加一块网卡，所以目标库的IP配置如下：

目标库再添加一块网卡后：

[oracle@orcltest ~]$ ifconfig

eth0 Link encap:Ethernet HWaddr 00:0C:29:E7:E6:B0

inet addr:192.168.59.129 Bcast:192.168.59.255 Mask:255.255.255.0

inet6 addr: fe80::20c:29ff:fee7:e6b0/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:165 errors:0 dropped:0 overruns:0 frame:0

TX packets:108 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:17969 (17.5 KiB) TX bytes:17510 (17.0 KiB)

eth1 Link encap:Ethernet HWaddr 00:0C:29:E7:E6:A6

inet addr:192.168.1.128 Bcast:192.168.1.255 Mask:255.255.255.0

inet6 addr: fe80::20c:29ff:fee7:e6a6/64 Scope:Link

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:3 errors:0 dropped:0 overruns:0 frame:0

TX packets:8 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:746 (746.0 b) TX bytes:1152 (1.1 KiB)

lo Link encap:Local Loopback

inet addr:127.0.0.1 Mask:255.0.0.0

inet6 addr: ::1/128 Scope:Host

UP LOOPBACK RUNNING MTU:16436 Metric:1

RX packets:5558 errors:0 dropped:0 overruns:0 frame:0

TX packets:5558 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:354142 (345.8 KiB) TX bytes:354142 (345.8 KiB)

源库scp操作：

[oracle@node2 rman_back]$ scp -r /home/oracle/rman_back oracle@192.168.1.128:/home/oracle

The authenticity of host '192.168.1.128 (192.168.1.128)' can't be established.

RSA key fingerprint is 77:e6:11:1a:7c:c7:81:7c:88:c9:21:18:51:2a:84:d1.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.1.128' (RSA) to the list of known hosts.

oracle@192.168.1.128's password:

ctl_JMRAC_20150529_12_1.bak 100% 18MB 17.7MB/s 00:01

arch_JMRAC_20150529_10_1.bak 100% 81MB 27.0MB/s 00:03

arch_JMRAC_20150529_9_1.bak 100% 33MB 16.5MB/s 00:02

full_JMRACxxx_20150529_880974935_7_1.bak 100% 18MB 17.7MB/s 00:01

full_JMRACxxx_20150529_880974780_5_1.bak 100% 618MB 12.4MB/s 00:50

initjmrac.ora 100% 1371 1.3KB/s 00:00

full_JMRACxxx_20150529_880974780_6_1.bak 100% 500MB 15.2MB/s 00:33

arch_JMRAC_20150529_11_1.bak 100% 14MB 3.4MB/s 00:04

full_JMRACxxx_20150529_880974935_8_1.bak 100% 96KB 96.0KB/s 00:00

[oracle@node2 rman_back]$

目标库查看结果：

[oracle@orcltest rman_back]$ ll -h

total 1.3G

-rw-r----- 1 oracle oinstall 82M May 29 12:26 arch_JMRAC_20150529_10_1.bak

-rw-r----- 1 oracle oinstall 14M May 29 12:28 arch_JMRAC_20150529_11_1.bak

-rw-r----- 1 oracle oinstall 34M May 29 12:26 arch_JMRAC_20150529_9_1.bak

-rw-r----- 1 oracle oinstall 18M May 29 12:26 ctl_JMRAC_20150529_12_1.bak

-rw-r----- 1 oracle oinstall 619M May 29 12:27 full_JMRACxxx_20150529_880974780_5_1.bak

-rw-r----- 1 oracle oinstall 501M May 29 12:28 full_JMRACxxx_20150529_880974780_6_1.bak

-rw-r----- 1 oracle oinstall 18M May 29 12:26 full_JMRACxxx_20150529_880974935_7_1.bak

-rw-r----- 1 oracle oinstall 96K May 29 12:28 full_JMRACxxx_20150529_880974935_8_1.bak

-rw-r--r-- 1 oracle oinstall 1.4K May 29 12:27 initjmrac.ora

[oracle@orcltest rman_back]$

至此，源库rac上需要操作的内容已完成。

target库上执行

修改pfile文件生成spfile文件、生成pfile中的文件路径

主要有两方面的修改：

修改含文件路径的参数，达到符合当前服务器环境的实际情况，如audit_file_dest，control_files，db_recovery_file_dest
修改多实例相关的参数,如 cluster_database，带有实例名的前缀

源pfile文件内容：

[oracle@orcltest rman_back]$ cp initjmrac.ora initjmrac.ora_bk

[oracle@orcltest rman_back]$ more initjmrac.ora_bk

jmrac1.__db_cache_size=16777216

jmrac2.__db_cache_size=16777216

jmrac1.__java_pool_size=4194304

jmrac2.__java_pool_size=4194304

jmrac1.__large_pool_size=4194304

jmrac2.__large_pool_size=4194304

jmrac1.__pga_aggregate_target=209715200

jmrac2.__pga_aggregate_target=209715200

jmrac1.__sga_target=314572800

jmrac2.__sga_target=314572800

jmrac1.__shared_io_pool_size=0

jmrac2.__shared_io_pool_size=0

jmrac1.__shared_pool_size=281018368

jmrac2.__shared_pool_size=281018368

jmrac1.__streams_pool_size=0

jmrac2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/jmrac/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.0.0'

*.control_files='+DATA/jmrac/controlfile/current.273.877470341','+DATA/jmrac/controlfile/current.272.877470343'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='jmrac'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=jmracXDB)'

jmrac2.instance_number=2

jmrac1.instance_number=1

*.log_archiveformat='%t%s_%r.dbf'

*.memory_target=524288000

*.nls_date_format='YYYY-MM-DD HH24:mi:ss'

*.open_cursors=300

*.processes=1500

*.remote_listener='remote_lsnr_jmrac'

*.remote_login_passwordfile='exclusive'

jmrac2.thread=2

jmrac1.thread=1

jmrac2.undo_tablespace='UNDOTBS2'

jmrac1.undo_tablespace='UNDOTBS1'

[oracle@orcltest rman_back]$

最终修改完之后，这里的初始化参数如下，比如原来的文件精简不少：

[oracle@orcltest rman_back]$ more initjmrac.ora

*.audit_file_dest='/u01/app/oracle/admin/jmrac/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/jmrac/control01.ctl','/u01/app/oracle/oradata/jmrac/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='jmrac'

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

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=jmracXDB)'

*.log_archiveformat='%t%s_%r.dbf'

*.memory_target=524288000

*.nls_date_format='YYYY-MM-DD HH24:mi:ss'

*.open_cursors=300

*.processes=1500

*.remote_login_passwordfile='exclusive'

创建相关路径：

[oracle@orcltest onlinelog]$ mkdir -p /u01/app/oracle/admin/jmrac/adump

[oracle@orcltest onlinelog]$ mkdir -p /u01/app/oracle/oradata/jmrac/

[oracle@orcltest onlinelog]$ sqlplus -v

SQL*Plus: Release 11.2.0.1.0 Production

[oracle@orcltest onlinelog]$

[oracle@orcltest ~]$ env | grep ORA

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@orcltest ~]$ cd /u01/app/oracle/

生成spfile文件：

[oracle@orcltest dbs]$ ORACLE_SID=jmrac

[oracle@orcltest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 13:58:37 2015

Connected to an idle instance.

SQL> create spfile from pfile='/home/oracle/rman_back/initjmrac.ora';

File created.

SQL> exit

Disconnected

[oracle@orcltest dbs]$ cd $ORACLE_HOME/dbs

[oracle@orcltest dbs]$ ll spfilejmrac.ora

-rw-r----- 1 oracle asmadmin 2560 May 29 13:59 spfilejmrac.ora

[oracle@orcltest dbs]$

启动到nomount状态并还原控制文件

[oracle@orcltest dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 29 14:45:56 2015

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 521936896 bytes

Fixed Size 2214936 bytes

Variable Size 482345960 bytes

Database Buffers 29360128 bytes

Redo Buffers 8015872 bytes

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

Starting restore at 2015-05-29 14:47:09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1146 device type=DISK

channel ORA_DISK_1: restoring control file

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

output file name=/u01/app/oracle/oradata/jmrac/control01.ctl

output file name=/u01/app/oracle/oradata/jmrac/control02.ctl

Finished restore at 2015-05-29 14:47:13

RMAN>

控制文件已经还原，注意此处控制文件的还原路径是spfile中指定的路径，接下来还原数据文件及恢复数据库。

启动到mount状态并还原和恢复整个数据库

restore 数据库

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> list backupset summary;

List of Backups

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

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

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

5 B F A DISK 2015-05-29 11:15:26 1 1 NO TAG20150529T111259

6 B F A DISK 2015-05-29 11:15:31 1 1 NO TAG20150529T111259

7 B F A DISK 2015-05-29 11:15:38 1 1 NO TAG20150529T111259

8 B F A DISK 2015-05-29 11:15:39 1 1 NO TAG20150529T111259

9 B A A DISK 2015-05-29 11:16:13 1 1 NO TAG20150529T111603

10 B A A DISK 2015-05-29 11:16:17 1 1 NO TAG20150529T111603

11 B A A DISK 2015-05-29 11:16:23 1 1 NO TAG20150529T111603

RMAN>

RMAN> list backupset of archivelog all;

List of Backup Sets

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

BS Key Size Device Type Elapsed Time Completion Time

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

9 33.09M DISK 00:00:08 2015-05-29 11:16:13

BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20150529T111603

Piece Name: /home/oracle/rman_back/arch_JMRAC_20150529_9_1.bak

List of Archived Logs in backup set 9

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 24 1389153 2015-05-10 17:55:23 1442215 2015-05-12 12:42:40

1 25 1442215 2015-05-12 12:42:40 1466390 2015-05-12 17:10:39

1 26 1466390 2015-05-12 17:10:39 1466392 2015-05-12 17:10:40

1 27 1466392 2015-05-12 17:10:40 1512521 2015-05-13 10:40:54

1 28 1512521 2015-05-13 10:40:54 1512530 2015-05-13 10:40:56

2 31 1389149 2015-05-10 17:55:22 1419988 2015-05-12 10:06:07

2 32 1419988 2015-05-12 10:06:07 1419992 2015-05-12 10:06:07

2 33 1444571 2015-05-12 13:34:16 1453906 2015-05-12 15:52:46

2 34 1454056 2015-05-12 15:57:38 1466360 2015-05-12 17:07:19

2 35 1466388 2015-05-12 17:10:39 1489679 2015-05-13 09:43:06

2 36 1489679 2015-05-13 09:43:06 1489698 2015-05-13 09:43:08

2 37 1490870 2015-05-13 10:00:32 1512524 2015-05-13 10:40:55

BS Key Size Device Type Elapsed Time Completion Time

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

10 81.07M DISK 00:00:12 2015-05-29 11:16:17

BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20150529T111603

Piece Name: /home/oracle/rman_back/arch_JMRAC_20150529_10_1.bak

List of Archived Logs in backup set 10

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 29 1513517 2015-05-13 10:42:36 1591218 2015-05-29 10:15:08

1 30 1591218 2015-05-29 10:15:08 1613556 2015-05-29 10:39:43

1 31 1613556 2015-05-29 10:39:43 1621589 2015-05-29 11:09:52

2 38 1512524 2015-05-13 10:40:55 1570420 2015-05-29 10:11:10

2 39 1570420 2015-05-29 10:11:10 1570422 2015-05-29 10:11:11

2 40 1592133 2015-05-29 10:20:48 1613554 2015-05-29 10:39:43

2 41 1613554 2015-05-29 10:39:43 1613562 2015-05-29 10:39:44

BS Key Size Device Type Elapsed Time Completion Time

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

11 13.66M DISK 00:00:01 2015-05-29 11:16:23

BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20150529T111603

Piece Name: /home/oracle/rman_back/arch_JMRAC_20150529_11_1.bak

List of Archived Logs in backup set 11

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48

1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58

2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49

2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53

RMAN>

这里需要注意的是，数据文件的转换，由于原rac库是asm存储的，所以到新环境需要采用set newname来转换一下，相关的可以参考哥的blog：【oracle官网】 Restoring a Database on a New Host http://blog.itpub.net/26736162/viewspace-1548104/，这里就直接操作了。

先得到转换的脚本：

SQL> set pagesize 200 linesize 200

SQL> select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'

2 from v$datafile a

3 union all

4 select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'

5 from v$tempfile a

6 union all

7 SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || ''''' to ''''' ||

8 a.MEMBER || ''''' ";'

9 FROM v$logfile a;

'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'

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

set newname for datafile 1 to "+DATA/jmrac/datafile/system.268.877470209";

set newname for datafile 2 to "+DATA/jmrac/datafile/sysaux.269.877470211";

set newname for datafile 3 to "+DATA/jmrac/datafile/undotbs1.270.877470213";

set newname for datafile 4 to "+DATA/jmrac/datafile/users.271.877470213";

set newname for datafile 5 to "+DATA/jmrac/datafile/example.279.877470401";

set newname for datafile 6 to "+DATA/jmrac/datafile/undotbs2.280.877470779";

set newname for tempfile 1 to "+DATA/jmrac/tempfile/temp.278.877470381";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.276.877470349'' to ''+DATA/jmrac/onlinelog/group_2.276.877470349'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.277.877470349'' to ''+DATA/jmrac/onlinelog/group_2.277.877470349'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.274.877470345'' to ''+DATA/jmrac/onlinelog/group_1.274.877470345'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.275.877470345'' to ''+DATA/jmrac/onlinelog/group_1.275.877470345'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_3.281.877470929'' to ''+DATA/jmrac/onlinelog/group_3.281.877470929'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_3.282.877470931'' to ''+DATA/jmrac/onlinelog/group_3.282.877470931'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_4.283.877470937'' to ''+DATA/jmrac/onlinelog/group_4.283.877470937'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_4.284.877470943'' to ''+DATA/jmrac/onlinelog/group_4.284.877470943'' ";

15 rows selected.

SQL>

修改后如下：

RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

set newname for datafile 1 to "/u01/app/oracle/oradata/jmrac/system01.dbf";

set newname for datafile 2 to "/u01/app/oracle/oradata/jmrac/sysaux01.dbf";

set newname for datafile 3 to "/u01/app/oracle/oradata/jmrac/undotbs01.dbf";

set newname for datafile 4 to "/u01/app/oracle/oradata/jmrac/users01.dbf";

set newname for datafile 5 to "/u01/app/oracle/oradata/jmrac/example01.dbf";

set newname for datafile 6 to "/u01/app/oracle/oradata/jmrac/undotbs02.dbf";

set newname for tempfile 1 to "/u01/app/oracle/oradata/jmrac/temp01.dbf";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.276.877470349'' to ''/u01/app/oracle/oradata/jmrac/redo02_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.277.877470349'' to ''/u01/app/oracle/oradata/jmrac/redo02_2.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.274.877470345'' to ''/u01/app/oracle/oradata/jmrac/redo01_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.275.877470345'' to ''/u01/app/oracle/oradata/jmrac/redo01_2.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_3.281.877470929'' to ''/u01/app/oracle/oradata/jmrac/redo03_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_3.282.877470931'' to ''/u01/app/oracle/oradata/jmrac/redo03_2.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_4.283.877470937'' to ''/u01/app/oracle/oradata/jmrac/redo04_1.log'' ";

SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_4.284.877470943'' to ''/u01/app/oracle/oradata/jmrac/redo04_2.log'' ";

RESTORE DATABASE;

SWITCH DATAFILE ALL;

SWITCH TEMPFILE ALL;

}

rman中还原数据文件：

RMAN> RUN

2> {

3> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

4> set newname for datafile 1 to "/u01/app/oracle/oradata/jmrac/system01.dbf";

5> set newname for datafile 2 to "/u01/app/oracle/oradata/jmrac/sysaux01.dbf";

6> set newname for datafile 3 to "/u01/app/oracle/oradata/jmrac/undotbs01.dbf";

7> set newname for datafile 4 to "/u01/app/oracle/oradata/jmrac/users01.dbf";

8> set newname for datafile 5 to "/u01/app/oracle/oradata/jmrac/example01.dbf";

9> set newname for datafile 6 to "/u01/app/oracle/oradata/jmrac/undotbs02.dbf";

10> set newname for tempfile 1 to "/u01/app/oracle/oradata/jmrac/temp01.dbf";

11> SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.276.877470349'' to ''/u01/app/oracle/oradata/jmrac/redo02_1.log'' ";

12> SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.277.877470349'' to ''/u01/app/oracle/oradata/jmrac/redo02_2.log'' ";

13> SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.274.877470345'' to ''/u01/app/oracle/oradata/jmrac/redo01_1.log'' ";

14> SQL "ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.275.877470345'' to ''/u01/app/oracle/oradata/jmrac/redo01_2.log'' ";

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

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

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

打赏赞(1)

标签： Oracle rac 备份恢复单机

小麦苗

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

发表回复取消回复

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

合 Oracle rac数据库备份集恢复到单机环境

前言部分

导读

实验环境介绍

实验部分

实验目标

源rac库执行

查看rac环境及创建测试表

生成pfile文件

执行备份操作

将备份传递到target库

target库上执行

修改pfile文件生成spfile文件、生成pfile中的文件路径

启动到nomount状态并还原控制文件

启动到mount状态并还原和恢复整个数据库

restore 数据库

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

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库执行

查看rac环境及创建测试表

生成pfile文件

执行备份操作

将备份传递到target库

target库上执行

修改pfile文件生成spfile文件、生成pfile中的文件路径

启动到nomount状态并还原控制文件

启动到mount状态并还原和恢复整个数据库

restore 数据库

相关文章

您可能还喜欢...

12c中报错ORA-28040 No matching authentication protocol 或 ORA-01017 invalid username password

【故障处理】ORA-12545: Connect failed because target host or object does not exist

Oracle 10g和11g手动建库过程

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复