注册登录
提 问
English中文版
文盲筱烨
作者文盲筱烨·2015-07-30 15:56
数据库运维工程师·SDOUG
关注6

使用RMAN备份将RAC数据库还原到单节点系统

字数 15692阅读 1981评论 0赞 0

RAC数据恢复至单机环境

一、概括

随着越来越多的RAC环境，将RAC数据迁移至单机环境下的需求也随之增加，怎样才能更有效的迁移呢，方法有许多逻辑迁移（exp/expdp）、RMAN、DG、goldengate等，我们可根据自身环境及需求选择相应的迁移方式，下面简单介绍一下使用RMAN将RAC数据迁移至单机环境中的步骤。

 

二、 模拟环境：整套RAC环境无故宕机，暂无法恢复，我们将之前备份的数据进行恢复
将最近一次全备份文件上传至目标环境，并查看文件信息

[oracle@oradb1 backup]$ pwd

/oracle/backup

[oracle@oradb1 backup]$ ls -lrt

总计 264312

-rw-r----- 1 oracle oinstall    132608 02-17 13:24 arch_0lp0ro84

-rw-r----- 1 oracle oinstall     19456 02-17 13:24 arch_0ip0ro4u

-rw-r----- 1 oracle oinstall   1114112 02-17 13:24 full_0kp0ro7v_1_1

-rw-r----- 1 oracle oinstall 269107200 02-17 13:24 full_0jp0ro50_1_

 

通过上述我们可以看到，归档文件有两个，由于是测试环境，数据量及备份信息较简单，数据文件也是两个，由此得知文件full_0kp0ro7v_1_1存放参数文件和控制文件。
设置环境变量

[oracle@oradb1 ~]$ vi ~/.bash_profile

修改参数为：export ORACLE_SID=racdb1

 

恢复spfile文件

[oracle@oradb1 backup]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 17 11:37:45 2014

 

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

 

connected to target database (not started)

 

RMAN> startup force nomount;

 

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

LRM-00109: could not open parameter file '/oracle/db_1/dbs/initracdb1.ora'

 

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

 

Total System Global Area    1068937216 bytes

 

Fixed Size                     2260088 bytes

Variable Size                281019272 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5517312 bytes

 

RMAN> restore spfile from '/oracle/backup/full_0kp0ro7v_1_1';

 

Starting restore at 17-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

 

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/backup/full_0kp0ro7v_1_1

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-FEB-14

 

RMAN> restore spfile from '/oracle/backup/full_0kp0ro7v_1_1';

 

Starting restore at 17-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

 

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /oracle/backup/full_0kp0ro7v_1_1

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 17-FEB-14

 

登录到数据库，生成pfile文件，并编辑，删除与集群即节点2相关参数，修改控制文件及其他目录路径，并根据需求创建相应目录（如闪回区、oracle基础目录）

idle> create pfile from spfile;

 

File created.

 

创建spfile，并重启实例


idle> create spfile from pfile;

 

File created.

idle> show parameter pfile

 

NAME                                 TYPE        VALUE

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

spfile                               string

idle> show parameter spfile

 

NAME                                 TYPE        VALUE

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

spfile                               string

idle> shutdown abort

ORACLE instance shut down.

idle> startup nomount

ORACLE instance started.

 

Total System Global Area  534462464 bytes

Fixed Size                  2254952 bytes

Variable Size             213911448 bytes

Database Buffers          314572800 bytes

Redo Buffers                3723264 bytes

idle> show parameter pfile

 

NAME                             TYPE        VALUE

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

spfile                              string      /oracle/db_1/dbs/spfileracdb1.

                                                 Ora

 

恢复控制文件，并启动数据库到mount模式


[oracle@oradb1 oracle]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 17 12:45:58 2014

 

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

 

connected to target database: RACDB (not mounted)

 

RMAN> restore controlfile from '/oracle/backup/full_0bp0rlcu_1_1';

 

Starting restore at 17-FEB-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

 

channel ORA_DISK_1: restoring control file

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

output file name=/oracle/oradata/racdb/current.273.839446247

output file name=/oracle/oradata/racdb/current.274.839446249

Finished restore at 17-FEB-14

 

RMAN> sql 'alter database mount';

 

sql statement: alter database mount

released channel: ORA_DISK_1

 

查看备份集信息


RMAN> list backup;

 

 

List of Backup Sets

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

 

 

BS Key  Size       Device Type Elapsed Time Completion Time

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

18      18.50K     DISK        00:00:00     17-FEB-14     

        BP Key: 18   Status: AVAILABLE  Compressed: YES  Tag: TAG20140217T133110

        Piece Name: /oracle/backup/arch_0ip0ro4u

 

  List of Archived Logs in backup set 18

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    112     1482625    17-FEB-14 1482667    17-FEB-14

  1    113     1482667    17-FEB-14 1482671    17-FEB-14

  1    114     1482671    17-FEB-14 1482704    17-FEB-14

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

19      Full    256.63M    DISK        00:01:34     17-FEB-14     

        BP Key: 19   Status: AVAILABLE  Compressed: YES  Tag: TAG20140217T133111

        Piece Name: /oracle/backup/full_0jp0ro50_1_1

  List of Datafiles in backup set 19

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 1482715    17-FEB-14 +DATA/racdb/datafile/system.279.839446279

  2       Full 1482715    17-FEB-14 +DATA/racdb/datafile/sysaux.280.839446345

  3       Full 1482715    17-FEB-14 +DATA/racdb/datafile/undotbs1.281.839446447

  4       Full 1482715    17-FEB-14 +DATA/racdb/datafile/undotbs2.283.839446487

  5       Full 1482715    17-FEB-14 +DATA/racdb/datafile/users.284.839446515

 

根据备份信息，恢复数据文件及数据库


RMAN> run{

2> set newname for datafile 1 to '/oracle/oradata/racdb/system01.dbf';

3> set newname for datafile 3 to '/oracle/oradata/racdb/undotbs01.dbf';

4> set newname for datafile 2 to '/oracle/oradata/racdb/sysaux01.dbf';

5> set newname for datafile 5 to '/oracle/oradata/racdb/users01.dbf'; 

6> set newname for datafile 4 to '/oracle/oradata/racdb/undotbs02.dbf';

7> restore database;

8> switch datafile all;

9> }

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 17-FEB-14

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00001 to /oracle/oradata/racdb/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /oracle/oradata/racdb/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oracle/oradata/racdb/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oracle/oradata/racdb/undotbs02.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oracle/oradata/racdb/users01.dbf

channel ORA_DISK_1: reading from backup piece /oracle/backup/full_0jp0ro50_1_1

channel ORA_DISK_1: piece handle=/oracle/backup/full_0jp0ro50_1_1 tag=TAG20140217T133111

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 17-FEB-14

 

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=839771072 file name=/oracle/oradata/racdb/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=7 STAMP=839771072 file name=/oracle/oradata/racdb/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=8 STAMP=839771072 file name=/oracle/oradata/racdb/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=839771072 file name=/oracle/oradata/racdb/undotbs02.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=10 STAMP=839771072 file name=/oracle/oradata/racdb/users01.dbf

 

RMAN> run{

2> set until scn 1482715; --scn 号根据备份集信息得出

recover database;

}3> 4>

 

executing command: SET until clause

 

Starting recover at 17-FEB-14

using channel ORA_DISK_1

 

starting media recovery

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

 

Finished recover at 17-FEB-14


打开数据库

RMAN> sql 'alter database open resetlogs';

 

sql statement: alter database open resetlogs

 

查看redo log 信息，并删除无效日志组（节点2日志）

sys@RACDB> select THREAD#, STATUS, ENABLED from v$thread;

 

   THREAD# STATUS ENABLED

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

         1 OPEN   PUBLIC

         2 CLOSED PUBLIC

 

sys@RACDB>  select group# from v$log where THREAD#=2;

 

    GROUP#

----------

         3

         4

 

删除日志组3、4


sys@RACDB> alter database disable thread 2;

 

Database altered.
sys@RACDB>  alter database clear unarchived logfile group 3;

 

Database altered.

sys@RACDB> alter database drop logfile group 3;

 

Database altered.

 

查看undo表空间，并删除节点2（在此不使用）的undo表空间

sys@RACDB> show parameter undo

 

NAME                                 TYPE        VALUE

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

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

sys@RACDB> select tablespace_name from dba_tablespaces where contents='UNDO';

 

TABLESPACE_NAME

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

UNDOTBS1

UNDOTBS2

 

sys@RACDB> drop tablespace UNDOTBS2 including contents and datafiles;

 

修改临时表空间


sys@RACDB> select name from v$tempfile;

 

NAME

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

/oracle/admin/racdb/RACDB/datafile/o1_mf_temp_9j38pmmk_.tmp

 

sys@RACDB> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

 

TABLESPACE_NAME

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

TEMP

 

sys@RACDB>  create temporary tablespace TEMP1  tempfile '/oracle/oradata/racdb/temp01.dbf'  size 50M autoextend off;

 

Tablespace created.

 

sys@RACDB> alter database default temporary tablespace TEMP1;

 

Database altered.

 

sys@RACDB> drop tablespace TEMP including contents and datafiles;

   

Tablespace dropped.

 

查看监听状态并配置，编辑tnsname.ora文件。通过以下命令创建密码文件，注意密码文件的位置

[oracle@oradb1 dbs]$ orapwd file=orapwracdb1 password=oracle entries=30

 

附：                                                                                                                                                      

设置备份片

RMAN> catalog backuppiece '/oracle/backup/arch2_08p0rhm1';

 

cataloged backup piece

backup piece handle=/oracle/backup/arch2_08p0rhm1 RECID=4 STAMP=839766295

 

三、总结
   在恢复过程中也遇到不少问题，起初没太仔细看备份集信息及归档日志不完全，造成scn号不准确，以至于数据库无法完成恢复。作为数据库管理人员，在对数据库操作时，请仔细核对相关信息，修改文件前最好先保留一份原始文件。详细RMAN管理语句、命令请参考oracle官方文档。

数据库rman
著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

赞0

添加新评论0 条评论

Ctrl+Enter 发表

作者其他文章

  • 【DATAGUARD】DG系列之RACtoONE物理备库的搭建
    评论 1 · 赞 1
  • 记Oracle一次高危漏洞补丁修复过程
    评论 0 · 赞 1

    • 相关文章

  • Redis 的优势是如何在保单业务中发挥出来的
  • 南大通用对数据库领域国家标准编制与实施的贡献浅析
  • 企业传统Oracle数据库迁移国产数据库核心难点总结
  • 招聘：建信金科基础技术中心数据库产品团队
  • 数据库运维管理平台面面观

    • 相关问题

  • 对于业务系统的历史数据存放、查询，哪种类型的数据库更为合适？
  • 超融合能否扛起全闪做支撑交易类数据库最佳平台的大旗？
  • 金融行业传统数据库一般如何解决io需求？
  • 信创环境下，哪些国产数据库能上银行核心系统？
  • 哪个国产数据库更值得去学习，各国产数据库的特点及缺点？

    • 相关资料

  • Redhat7.6 Oracle 19C RAC部署手册
  • IDC+中国金融行业国产分布式数据库白皮书
  • Redhat7.0 Oracle 12C RAC部署手册
  • Redhat7.5 Oracle 18C RAC部署手册
  • 海通证券核心数据库系统高可用及存储设备选型经验分享

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广