【RAC】将RAC备份集恢复为单实例数据库

时间:2021-05-27 04:46:01

RAC】将RAC备份集恢复为单实例数据库

一.1  BLOG文档结构图

【RAC】将RAC备份集恢复为单实例数据库 

一.2  前言部分

 

一.2.1  导读

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

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

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

一般的备份恢复过程

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

一.2.2  实验环境介绍

 

源库:11.2.0.1  rac库 2个节点

目标库:11.2.0.1  RHEL6.5 

 

 

一.2.3  相关参考文章链接

 

RAC系列 

 

【推荐】 【RAC】如何让Oracle RAC crs_stat 命令显示完整 

http://blog.itpub.net/26736162/viewspace-1610957/ 

【推荐】 一步一步搭建11gR2 rac+dg之结尾篇(十) 

http://blog.itpub.net/26736162/viewspace-1328156/ 

【推荐】 一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九) 

http://blog.itpub.net/26736162/viewspace-1328050/ 

一步一步搭建11gR2 rac+dg之配置单实例的DG(八) 

http://blog.itpub.net/26736162/viewspace-1298735/ 

一步一步搭建11gR2 rac+dg之DG 机器配置(七) 

http://blog.itpub.net/26736162/viewspace-1298733/ 

一步一步搭建11gR2 rac+dg之安装rac出现问题解决(六) 

http://blog.itpub.net/26736162/viewspace-1297128/ 

一步一步搭建oracle 11gR2 rac+dg之database安装(五) 

http://blog.itpub.net/26736162/viewspace-1297113/ 

一步一步搭建 oracle 11gR2 rac+dg之grid安装(四) 

http://blog.itpub.net/26736162/viewspace-1297101/ 

【推荐】 一步一步搭建oracle 11gR2 rac+dg之共享磁盘设置(三) 

http://blog.itpub.net/26736162/viewspace-1291144/ 

【推荐】 一步一步搭建oracle 11gR2 rac+dg之环境准备(二) 

http://blog.itpub.net/26736162/viewspace-1290416/ 

【推荐】 一步一步搭建 oracle 11gR2 rac + dg 之前传 (一) 

http://blog.itpub.net/26736162/viewspace-1290405/ 

 

 

RMAN 备份恢复系列 

 

【推荐】 【RMAN】rm -rf 误操作的恢复过程 

http://blog.itpub.net/26736162/viewspace-1623938/ 

【推荐】 【RMAN】利用备份片还原数据库(中)-附加 

http://blog.itpub.net/26736162/viewspace-1621938/ 

【推荐】 【RMAN】利用备份片还原数据库(下) 

http://blog.itpub.net/26736162/viewspace-1621672/ 

【推荐】 【RMAN】利用备份片还原数据库(中) 

http://blog.itpub.net/26736162/viewspace-1621661/ 

【推荐】 【RMAN】利用备份片还原数据库(上) 

http://blog.itpub.net/26736162/viewspace-1621581/ 

【推荐】 【RMAN】RMAN跨版本恢复(下) 

http://blog.itpub.net/26736162/viewspace-1562583/ 

【推荐】  Oracle 组件 系列 小结 

http://blog.itpub.net/26736162/viewspace-1562441/ 

【推荐】 【RMAN】RMAN跨版本恢复(中) 

http://blog.itpub.net/26736162/viewspace-1561352/ 

【推荐】 【RMAN】RMAN跨版本恢复(上) 

http://blog.itpub.net/26736162/viewspace-1561185/ 

【推荐】  关于在不同版本和平台之间进行还原或复制的常见问题 

http://blog.itpub.net/26736162/viewspace-1549041/ 

 

 

 

【推荐】 undo表空间文件丢失恢复(4)--无备份无recover的情况下恢复 

http://blog.itpub.net/26736162/viewspace-1458787/ 

【推荐】 undo表空间文件丢失恢复(3)--无备份无redo的情况下恢复 

http://blog.itpub.net/26736162/viewspace-1458750/ 

【推荐】 undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复 

http://blog.itpub.net/26736162/viewspace-1458663/ 

【推荐】 undo表空间文件丢失恢复(1)--有备份 

http://blog.itpub.net/26736162/viewspace-1458654/ 

 

 

【推荐】 ORACLE 数据泵之NETWORK_LINK 

http://blog.itpub.net/26736162/viewspace-1432591/ 

 

 

【推荐】 oracle控制文件在缺失归档日志的情况下的恢复 

http://blog.itpub.net/26736162/viewspace-1426552/ 

【推荐】 ORACLE 只读数据文件备份与恢复 

http://blog.itpub.net/26736162/viewspace-1425283/ 

 

 

【推荐】 热备下的测试库搭建 

http://blog.itpub.net/26736162/viewspace-1405324/ 

 

 

【推荐】 oracle 异构平台迁移之传输表空间一例 

http://blog.itpub.net/26736162/viewspace-1391913/ 

【推荐】 oracle 传输表空间一例 

http://blog.itpub.net/26736162/viewspace-1375260/ 

 

 

【推荐】 利用rman来实现linux平台数据库复制到windows平台数据库 

http://blog.itpub.net/26736162/viewspace-1352436/ 

【推荐】 直接复制数据文件实现linux平台数据库复制到windows平台数据库 

http://blog.itpub.net/26736162/viewspace-1352243/ 

 

 

【推荐】 使用OEM复制数据库 

http://blog.itpub.net/26736162/viewspace-1224865/ 

【推荐】 采用DUPLICATE 把asm数据库复制到文件系统 

http://blog.itpub.net/26736162/viewspace-1224861/ 

Duplicating a Database Without Recovery Catalog or Target Connection 

http://blog.itpub.net/26736162/viewspace-1223253/ 

【推荐】 Duplicating an Active Database 

http://blog.itpub.net/26736162/viewspace-1223247/ 

 

 

 

一.2.4  本文简介

 

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

 

 

另外注意,BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方。

  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

 

 

一.3  实验部分

 

一.3.1  实验目标

 

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

 

一.3.2  rac库执行

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

 

一.3.2.1  查看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

 

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 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.32rac的第二个节点上执行操作,db_namejmrac,数据库为归档模式,创建测试表lhr.rac_to_single_test,数据量为72510 行,其中有个crsstat命令,可以参考:【RAC】如何让Oracle RAC crs_stat 命令显示完整  http://blog.itpub.net/26736162/viewspace-1610957/  

 

 

一.3.2.2  生成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>

 

 

一.3.2.3  执行备份操作

备份脚本如下:

run

{

allocate channel c1 type disk;

allocate channel c2 type disk;

backup database format  '/home/oracle/rman_back/full_%n_%T_%t_%s_%p.bak';

sql 'alter system archive log current';

backup archivelog all format '/home/oracle/rman_back/arch_%d_%T_%s_%p.bak' delete input;

backup current controlfile format '/home/oracle/rman_back/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

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

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/rman_back/full_%n_%T_%t_%s_%p.bak';

6>  sql 'alter system archive log current';

7>  backup archivelog all format '/home/oracle/rman_back/arch_%d_%T_%s_%p.bak' delete input;

8>  backup current controlfile format '/home/oracle/rman_back/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]$

 

 

 

 

一.3.2.4  将备份传递到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 dbIP192.168.1.32,而目标库的IP192.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上需要操作的内容已完成。

 

 

一.3.3  target库上执行

一.3.3.1  修改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_archive_format='%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_archive_format='%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

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

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]$

 

 

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

[oracle@orcltest dbs]$ rman target /

 

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

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

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中指定的路径,接下来还原数据文件及恢复数据库。

 

 

 

一.3.3.3  启动到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'' ";

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

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

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

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

19>  

20>   RESTORE DATABASE;

21>   SWITCH DATAFILE ALL;

22>   SWITCH TEMPFILE ALL; 

23> }

 

allocated channel: c1

channel c1: SID=1137 device type=DISK

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.276.877470349''  to  ''/u01/app/oracle/oradata/jmrac/redo02_1.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_2.277.877470349''  to  ''/u01/app/oracle/oradata/jmrac/redo02_2.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.274.877470345''  to  ''/u01/app/oracle/oradata/jmrac/redo01_1.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_1.275.877470345''  to  ''/u01/app/oracle/oradata/jmrac/redo01_2.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_3.281.877470929''  to  ''/u01/app/oracle/oradata/jmrac/redo03_1.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_3.282.877470931''  to  ''/u01/app/oracle/oradata/jmrac/redo03_2.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_4.283.877470937''  to  ''/u01/app/oracle/oradata/jmrac/redo04_1.log''

 

sql statement: ALTER DATABASE RENAME FILE ''+DATA/jmrac/onlinelog/group_4.284.877470943''  to  ''/u01/app/oracle/oradata/jmrac/redo04_2.log''

 

Starting restore at 2015-05-29 15:16:46

 

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00002 to /u01/app/oracle/oradata/jmrac/sysaux01.dbf

channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/jmrac/undotbs01.dbf

channel c1: restoring datafile 00005 to /u01/app/oracle/oradata/jmrac/example01.dbf

channel c1: reading from backup piece /home/oracle/rman_back/full_JMRACxxx_20150529_880974780_6_1.bak

channel c1: piece handle=/home/oracle/rman_back/full_JMRACxxx_20150529_880974780_6_1.bak tag=TAG20150529T111259

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

channel c1: starting datafile backup set restore

channel c1: specifying datafile(s) to restore from backup set

channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/jmrac/system01.dbf

channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/jmrac/users01.dbf

channel c1: restoring datafile 00006 to /u01/app/oracle/oradata/jmrac/undotbs02.dbf

channel c1: reading from backup piece /home/oracle/rman_back/full_JMRACxxx_20150529_880974780_5_1.bak

channel c1: piece handle=/home/oracle/rman_back/full_JMRACxxx_20150529_880974780_5_1.bak tag=TAG20150529T111259

channel c1: restored backup piece 1

channel c1: restore complete, elapsed time: 00:00:35

Finished restore at 2015-05-29 15:17:57

 

datafile 1 switched to datafile copy

input datafile copy RECID=8 STAMP=880989478 file name=/u01/app/oracle/oradata/jmrac/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=9 STAMP=880989478 file name=/u01/app/oracle/oradata/jmrac/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=10 STAMP=880989478 file name=/u01/app/oracle/oradata/jmrac/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=11 STAMP=880989478 file name=/u01/app/oracle/oradata/jmrac/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=12 STAMP=880989478 file name=/u01/app/oracle/oradata/jmrac/example01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=13 STAMP=880989479 file name=/u01/app/oracle/oradata/jmrac/undotbs02.dbf

 

renamed tempfile 1 to /u01/app/oracle/oradata/jmrac/temp01.dbf in control file

released channel: c1

 

RMAN>

 

 

告警日志:

Fri May 29 15:16:45 2015

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_2.276.877470349

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_2.277.877470349

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_1.274.877470345

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_1.275.877470345

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_3.281.877470929

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_3.282.877470931

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_4.283.877470937

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

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

Deleted Oracle managed file +DATA/jmrac/onlinelog/group_4.284.877470943

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

Fri May 29 15:16:51 2015

Full restore complete of datafile 3 to datafile copy /u01/app/oracle/oradata/jmrac/undotbs01.dbf.  Elapsed time: 0:00:02

  checkpoint is 1624119

  last deallocation scn is 1529290

  Undo Optimization current scn is 1542977

Full restore complete of datafile 5 to datafile copy /u01/app/oracle/oradata/jmrac/example01.dbf.  Elapsed time: 0:00:09

  checkpoint is 1624119

  last deallocation scn is 1379034

Fri May 29 15:17:18 2015

Full restore complete of datafile 2 to datafile copy /u01/app/oracle/oradata/jmrac/sysaux01.dbf.  Elapsed time: 0:00:30

  checkpoint is 1624119

  last deallocation scn is 1348692

Full restore complete of datafile 6 to datafile copy /u01/app/oracle/oradata/jmrac/undotbs02.dbf.  Elapsed time: 0:00:00

  checkpoint is 1624083

  last deallocation scn is 1549684

  Undo Optimization current scn is 1542977

Full restore complete of datafile 4 to datafile copy /u01/app/oracle/oradata/jmrac/users01.dbf.  Elapsed time: 0:00:01

  checkpoint is 1624083

Fri May 29 15:17:52 2015

Full restore complete of datafile 1 to datafile copy /u01/app/oracle/oradata/jmrac/system01.dbf.  Elapsed time: 0:00:29

  checkpoint is 1624083

  last deallocation scn is 1547365

  Undo Optimization current scn is 1542977

Fri May 29 15:18:00 2015

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14335.trc:

ORA-19625: error identifying file +DATA/jmrac/datafile/system.268.877470209

ORA-17503: ksfdopn:2 Failed to open file +DATA/jmrac/datafile/system.268.877470209

ORA-15012: ASM file '+DATA/jmrac/datafile/system.268.877470209' does not exist

Switch of datafile 1 complete to datafile copy

  checkpoint is 1624083

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14335.trc:

ORA-19625: error identifying file +DATA/jmrac/datafile/sysaux.269.877470211

ORA-17503: ksfdopn:2 Failed to open file +DATA/jmrac/datafile/sysaux.269.877470211

ORA-15012: ASM file '+DATA/jmrac/datafile/sysaux.269.877470211' does not exist

Switch of datafile 2 complete to datafile copy

  checkpoint is 1624119

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14335.trc:

ORA-19625: error identifying file +DATA/jmrac/datafile/undotbs1.270.877470213

ORA-17503: ksfdopn:2 Failed to open file +DATA/jmrac/datafile/undotbs1.270.877470213

ORA-15012: ASM file '+DATA/jmrac/datafile/undotbs1.270.877470213' does not exist

Switch of datafile 3 complete to datafile copy

  checkpoint is 1624119

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14335.trc:

ORA-19625: error identifying file +DATA/jmrac/datafile/users.271.877470213

ORA-17503: ksfdopn:2 Failed to open file +DATA/jmrac/datafile/users.271.877470213

ORA-15012: ASM file '+DATA/jmrac/datafile/users.271.877470213' does not exist

Switch of datafile 4 complete to datafile copy

  checkpoint is 1624083

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14335.trc:

ORA-19625: error identifying file +DATA/jmrac/datafile/example.279.877470401

ORA-17503: ksfdopn:2 Failed to open file +DATA/jmrac/datafile/example.279.877470401

ORA-15012: ASM file '+DATA/jmrac/datafile/example.279.877470401' does not exist

Switch of datafile 5 complete to datafile copy

  checkpoint is 1624119

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14335.trc:

ORA-19625: error identifying file +DATA/jmrac/datafile/undotbs2.280.877470779

ORA-17503: ksfdopn:2 Failed to open file +DATA/jmrac/datafile/undotbs2.280.877470779

ORA-15012: ASM file '+DATA/jmrac/datafile/undotbs2.280.877470779' does not exist

Fri May 29 15:18:01 2015

Signalling error 1152 for datafile 5!

Switch of datafile 6 complete to datafile copy

  checkpoint is 1624083

Signalling error 1152 for datafile 6!

Checker run found 2 new persistent data failures

 

查看数据文件是否已经还原:

[oracle@orcltest jmrac]$ ll -h

total 1.5G

-rw-r----- 1 oracle asmadmin  18M May 29 15:18 control01.ctl

-rw-r----- 1 oracle asmadmin  18M May 29 15:18 control02.ctl

-rw-r----- 1 oracle asmadmin 101M May 29 15:16 example01.dbf

-rw-r----- 1 oracle asmadmin 541M May 29 15:17 sysaux01.dbf

-rw-r----- 1 oracle asmadmin 691M May 29 15:17 system01.dbf

-rw-r----- 1 oracle asmadmin  91M May 29 15:16 undotbs01.dbf

-rw-r----- 1 oracle asmadmin  26M May 29 15:17 undotbs02.dbf

-rw-r----- 1 oracle asmadmin  27M May 29 15:17 users01.dbf

[oracle@orcltest jmrac]$

 

 

二、 recover数据库

由前边的备份集中可以看出,备份集中的thread 1的最大日志号为33thread 2的最大日志号为43,所以不完全恢复如下:

 

RMAN> RUN

2> {

3> set until sequence 33 thread 1;

set until sequence 43 thread 2;

recover database;

}

4> 5> 6>

executing command: SET until clause

 

executing command: SET until clause

 

Starting recover at 2015-05-29 15:28:05

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1146 device type=DISK

 

starting media recovery

 

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=2 sequence=42

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=32

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=33

channel ORA_DISK_1: reading from backup piece /home/oracle/rman_back/arch_JMRAC_20150529_11_1.bak

channel ORA_DISK_1: piece handle=/home/oracle/rman_back/arch_JMRAC_20150529_11_1.bak tag=TAG20150529T111603

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_2_42_bpj5896k_.arc thread=2 sequence=42

archived log file name=/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_32_bpj5897f_.arc thread=1 sequence=32

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_32_bpj5897f_.arc RECID=74 STAMP=880990089

archived log file name=/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_33_bpj5897n_.arc thread=1 sequence=33

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_2_42_bpj5896k_.arc RECID=75 STAMP=880990089

channel default: deleting archived log(s)

archived log file name=/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_33_bpj5897n_.arc RECID=73 STAMP=880990089

media recovery complete, elapsed time: 00:00:02

Finished recover at 2015-05-29 15:28:12

 

RMAN>

 

 

告警日志:

Fri May 29 15:28:06 2015

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list

1 , 2 , 3 , 4 , 5 , 6

Completed: alter database recover datafile list

1 , 2 , 3 , 4 , 5 , 6

alter database recover if needed

start until cancel using backup controlfile

Media Recovery Start

started logmerger process

Parallel Media Recovery started with 2 slaves

ORA-279 signalled during: alter database recover if needed

start until cancel using backup controlfile

...

alter database recover logfile '/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_2_42_bpj5896k_.arc'

Media Recovery Log /u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_2_42_bpj5896k_.arc

ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_2_42_bpj5896k_.arc'...

alter database recover logfile '/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_32_bpj5897f_.arc'

Media Recovery Log /u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_32_bpj5897f_.arc

ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_32_bpj5897f_.arc'...

alter database recover logfile '/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_33_bpj5897n_.arc'

Media Recovery Log /u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_33_bpj5897n_.arc

ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/flash_recovery_area/JMRAC/archivelog/2015_05_29/o1_mf_1_33_bpj5897n_.arc'...

alter database recover cancel

Media Recovery Canceled

Completed: alter database recover cancel

 

一.3.3.4  RESETLOGS打开数据库并验证数据

 

RMAN>  alter database open resetlogs;

 

database opened

 

RMAN>

 

告警日志:

Fri May 29 15:30:56 2015

alter database open

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORA-1589 signalled during: alter database open...

alter database open resetlogs

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 3 of thread 2

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 3 of thread 2

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 4 of thread 2

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 4 of thread 2

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

RESETLOGS after incomplete recovery UNTIL CHANGE 1625245

Resetting resetlogs activation ID 1916751680 (0x723f4f40)

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/jmrac/redo01_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jmrac/redo02_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 3 of thread 2

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 3 of thread 2

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/jmrac/redo03_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 4 of thread 2

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_ora_14414.trc:

ORA-00313: open failed for members of log group 4 of thread 2

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_2.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/jmrac/redo04_1.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Fri May 29 15:31:08 2015

Setting recovery target incarnation to 3

Fri May 29 15:31:08 2015

Assigning activation ID 1920208641 (0x72740f01)

LGWR: STARTING ARCH PROCESSES

Fri May 29 15:31:09 2015

ARC0 started with pid=22, OS id=14444

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Fri May 29 15:31:10 2015

ARC1 started with pid=26, OS id=14446

Fri May 29 15:31:10 2015

ARC2 started with pid=27, OS id=14448

ARC1: Archival started

Fri May 29 15:31:10 2015

ARC3 started with pid=28, OS id=14450

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Thread 1 opened at log sequence 1

  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/jmrac/redo01_1.log

  Current log# 1 seq# 1 mem# 1: /u01/app/oracle/oradata/jmrac/redo01_2.log

Successful open of redo thread 1

Fri May 29 15:31:10 2015

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Fri May 29 15:31:10 2015

SMON: enabling cache recovery

Redo thread 2 internally disabled at seq 1 (CKPT)

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC1: Archiving disabled thread 2 sequence 1

Archived Log entry 76 added for thread 2 sequence 1 ID 0x0 dest 1:

Successfully onlined Undo Tablespace 2.

Dictionary check beginning

Fri May 29 15:31:14 2015

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_dbw0_14084.trc:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/u01/app/oracle/oradata/jmrac/temp01.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/jmrac/jmrac/trace/jmrac_dbw0_14084.trc:

ORA-01186: file 201 failed verification tests

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '/u01/app/oracle/oradata/jmrac/temp01.dbf'

File 201 not verified due to error ORA-01157

Dictionary check complete

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Re-creating tempfile /u01/app/oracle/oradata/jmrac/temp01.dbf

Database Characterset is ZHS16GBK

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Fri May 29 15:31:22 2015

Starting background process QMNC

Fri May 29 15:31:22 2015

QMNC started with pid=29, OS id=14454

LOGSTDBY: Validating controlfile with logical metadata

LOGSTDBY: Validation complete

Completed: alter database open resetlogs

Fri May 29 15:31:33 2015

Starting background process CJQ0

Fri May 29 15:31:33 2015

CJQ0 started with pid=35, OS id=14472

 

 

 

验证数据:

[oracle@orcltest dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 29 15:33:02 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, 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      jmrac

db_unique_name                       string      jmrac

global_names                         boolean     FALSE

instance_name                        string      jmrac

lock_name_space                      string

log_file_name_convert                string

service_names                        string      jmrac

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

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 /u01/app/oracle/oradata/jmrac/system01.dbf                   SYSTEM  READ WRITE

datafile             2 /u01/app/oracle/oradata/jmrac/sysaux01.dbf                   ONLINE  READ WRITE

datafile             3 /u01/app/oracle/oradata/jmrac/undotbs01.dbf                  ONLINE  READ WRITE

datafile             4 /u01/app/oracle/oradata/jmrac/users01.dbf                    ONLINE  READ WRITE

datafile             5 /u01/app/oracle/oradata/jmrac/example01.dbf                  ONLINE  READ WRITE

datafile             6 /u01/app/oracle/oradata/jmrac/undotbs02.dbf                  ONLINE  READ WRITE

tempfile             1 /u01/app/oracle/oradata/jmrac/temp01.dbf                     ONLINE  READ WRITE

logfile              2 /u01/app/oracle/oradata/jmrac/redo02_1.log

logfile              2 /u01/app/oracle/oradata/jmrac/redo02_2.log

logfile              1 /u01/app/oracle/oradata/jmrac/redo01_1.log

logfile              1 /u01/app/oracle/oradata/jmrac/redo01_2.log

logfile              3 /u01/app/oracle/oradata/jmrac/redo03_1.log

logfile              3 /u01/app/oracle/oradata/jmrac/redo03_2.log

logfile              4 /u01/app/oracle/oradata/jmrac/redo04_1.log

logfile              4 /u01/app/oracle/oradata/jmrac/redo04_2.log

controlfile            /u01/app/oracle/oradata/jmrac/control01.ctl

controlfile            /u01/app/oracle/oradata/jmrac/control02.ctl

 

17 rows selected.

 

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

 

  COUNT(1)

----------

    72510

 

SQL>

 

 

可以看到数据已经恢复。

一.3.3.5  后续收尾操作

 

做到这步,都还没有完啊,只是说目的基本达成,最后还需要收尾的工作。清除未使用线程的 redo 日志组,操作如下:

一、 清除未使用的redo

 

 

SQL> col instance format a8

SQL> select thread#,instance,status,enabled from v$thread;

 

   THREAD# INSTANCE STATUS ENABLED

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

         1 jmrac    OPEN   PUBLIC

         2 jmrac2   CLOSED PUBLIC

 

SQL> select group#,thread#,archived,status from v$log;

 

    GROUP#    THREAD# ARC STATUS

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

         1          1 NO  CURRENT

         2          1 YES UNUSED

         3          2 YES ACTIVE

         4          2 YES UNUSED

 

SQL> alter database disable thread 2 ;

 

Database altered.

 

SQL> alter database drop logfile group 3 ;

 

Database altered.

 

 

SQL> alter database drop logfile group 4 ;

 

Database altered.

 

SQL>

 

SQL>  select thread#,instance,status,enabled from v$thread;

 

   THREAD# INSTANCE STATUS ENABLED

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

         1 jmrac    OPEN   PUBLIC

 

SQL>

SQL> select group#,thread#,archived,status from v$log;

 

    GROUP#    THREAD# ARC STATUS

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

         1          1 NO  CURRENT

         2          1 YES UNUSED

 

SQL>

 

 

生产环境可以再增加一些日志组。

 

 

 

二、 清除多余的 undo 文件

我们知道rac中每个节点使用的都是自己的undo,所以有2undo文件,这里可以清除,也可以不用清除,因为有的时候undo坏了可以很迅速的切换到另外的undo空间,清理过程如下:

SQL> select name from v$tablespace where name like 'UNDO%';

 

NAME

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

UNDOTBS1

UNDOTBS2

 

SQL>  show parameter undo_tablespace;

 

NAME                                 TYPE        VALUE

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

undo_tablespace                      string      UNDOTBS1

SQL>  drop tablespace undotbs2 including contents and datafiles;

 

Tablespace dropped.

 

SQL>

 

 

 

 

一.3.4  实验总结

 

rac数据库迁移到单实例环境下的步骤和单实例的数据库迁移到单实例环境基本是一样的,只是在最后还原的时候需要设置2thread即可。

 

一.4  总结

 

至此,rac数据库迁移到单实例环境下的操作步骤基本完毕,至于配置监听和tns等工作都是基本的,大家自己完成即可,这里就不再演示了,另外实验中需要关注的几个地方,我都特别做了说明。

 

 

 

一.5  about me

 

...........................................................................................................................................................................................

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

ITPUB BLOG:http://blog.itpub.net/26736162

本文地址:http://blog.itpub.net/26736162/viewspace-1682255/

本文pdf版:http://yunpan.cn/QCwUAI9bn7g7w  提取码:af2d

QQ:642808185 若加QQ请注明你所正在读的文章标题

创作时间地点:2015-05-29 10:00~ 2015-05-29 19:00 于上海外汇交易中心

<版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任!>

...........................................................................................................................................................................................