当前位置:  数据库>oracle

Oracle 11g如何将dataguard的主库数据恢复到Oracle单机实例？

    来源: 互联网  发布时间：2017-06-21

    本文导语:  需要将dataguard的主库数据恢复到Oracle单机实例中，所以记录下详细过程 1，从线上重新拉一个参数文件出来，copy到测试环境 SQL> create pfile='/oracle/pfile01.ora'from spfile; ...

需要将dataguard的主库数据恢复到Oracle单机实例中，所以记录下详细过程 1，从线上重新拉一个参数文件出来，copy到测试环境 
SQL> create pfile='/oracle/pfile01.ora'from spfile;                                                                                                                                           

File created.

SQL>

然后根据复制来的参数文件再在测试库上建立spfile

create spfile frompfile='/data/pfile01.ora';
SQL> create spfile frompfile='/data/impdp/pfile01.ora';                                                                                                                                      

File created.

SQL>

然后将测试库启动到nomount

        SQL> startup nomount                                                                                                                                                                           
        ORA-00845: MEMORY_TARGET not supported onthis system                                                                                                                                         
        SQL>
2，恢复控制文件

restore controlfile to ‘/home/oradata/orcstu/control01.ctl’ from’/data/impdp/c-3391761643-20151126-01’

RMAN> restore controlfile to '/home/oradata/orcstu/control01.ctl' from'/data/impdp/c-3391761643-20151126-01'                                                                                 
2> ;                                                                                                                                                                                           

Starting restore at 26-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file                                                                                                                                                     
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01                                                                                                                                   
Finished restore at 26-NOV-15

RMAN>    

  RMAN> restore controlfile to '/oracle/app/oracle/flash_recovery_area/orcstu/control02.ctl' from '/data/impdp/c-3391761643-20151126-01';                                                      

Starting restore at 26-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file                                                                                                                                                     
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01                                                                                                                                   
Finished restore at 26-NOV-15

RMAN>
3，注册归档

要将db状态改成mount才行

RMAN> alter database mount;                                                                                                                                                                    

database mounted                                                                                                                                                                               
released channel: ORA_DISK_1

RMAN>
  • 1

在新控制文件中注册数据文件备份和归档备份

catalog start with'/data/impdp/2015-11-26/';   

RMAN> catalog start with'/data/impdp/2015-11-26/';                                                                                                                                             

Starting implicit crosscheck backup at 26-NOV-15                                                                                                                                               
allocated channel: ORA_DISK_1                                                                                                                                                                  
channel ORA_DISK_1: SID=767 device type=DISK
Crosschecked 97 objects                                                                                                                                                                        
Finished implicit crosscheck backup at 26-NOV-15

Starting implicit crosscheck copy at 26-NOV-15
using channel ORA_DISK_1
Crosschecked 2 objects                                                                                                                                                                         
Finished implicit crosscheck copy at 26-NOV-15

searching for all files in the recovery area
cataloging files...
no files cataloged                                                                                                                                                                             

searching for all files that match the pattern /data/impdp/2015-11-26/                                                                                                                         

List of Files Unknown to the Database
=====================================
File Name: /data/impdp/2015-11-26/rman_backup.log
File Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5036.bak
File Name: /data/impdp/2015-11-26/full_orcstu_20151126_5037.bak
File Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5038.bak

Do you really want to catalog the above files (enter YES or NO)? YES                                                                                                                           
cataloging files...
cataloging done                                                                                                                                                                                

List of Cataloged Files
=======================
File Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5036.bak
File Name: /data/impdp/2015-11-26/full_orcstu_20151126_5037.bak
File Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5038.bak

List of Files Which Where Not Cataloged
=======================================
File Name: /data/impdp/2015-11-26/rman_backup.log
  RMAN-07517: Reason: The file header is corrupted

RMAN>
4，开始恢复整个库

因为前面恢复了整个spfile已经controlfile，所以接下来恢复所有库的话，就不用带参数，直接恢复restore database就可以 ;

RMAN> restore database;                                                                                                                                                                        

Starting restore at 26-NOV-15                                                                                                                                                                  
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 /home/oradata/orcstu/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oradata/orcstu/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oradata/orcstu/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oradata/orcstu/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oradata/orcstu/orcstuk01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /home/oradata/orcstu/plas01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /home/oradata/orcstu/pl01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oradata/orcstu/help01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oradata/orcstu/adobelc01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /home/oradata/orcstu/sms01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oradata/orcstu/plcrm01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /home/oradata/orcstu/orcstuk02.dbf
channel ORA_DISK_1: restoring datafile 00013 to /home/oradata/orcstu/datagm01.dbf
channel ORA_DISK_1: reading from backup piece /data/impdp/2015-11-26/full_orcstu_20151126_5037.bak
channel ORA_DISK_1: piece handle=/data/impdp/2015-11-26/full_orcstu_20151126_5037.bak tag=TAG20151126T030008                                                                                 
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:32:50
Finished restore at 26-NOV-15

RMAN>
  • 1
5，recover数据库 
RMAN> recover database;                                                                                                                                                                        

Starting recover at 26-NOV-15                                                                                                                                                                  
using channel ORA_DISK_1

starting media recovery                                                                                                                                                                        

channel ORA_DISK_1: starting archived log restore to default destination                                                                                                                       
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39674
channel ORA_DISK_1: reading from backup piece /data/impdp/2015-11-26/arch_orcstu_20151126_5038.bak
channel ORA_DISK_1: piece handle=/data/impdp/2015-11-26/arch_orcstu_20151126_5038.bak tag=TAG20151126T032346                                                                                 
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/oracle/app/oracle/flash_recovery_area/archivelog1_39674_821708334.dbf thread=1 sequence=39674                                                                          
unable to find archived log                                                                                                                                                                    
archived log thread=1 sequence=39675
RMAN-00571: ===========================================================                                                                                                                        
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/26/2015 21:48:13
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39675 and starting SCN of 11688009482

RMAN>  

后台alert日志：
 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13
Completed: alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13
alter database recover if needed
 start until cancel using backup controlfile
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 4 slaves
ORA-279 signalled during: alter database recover if needed
 start until cancel using backup controlfile
...
alter database recover logfile '/oracle/app/oracle/flash_recovery_area/archivelog1_39674_821708334.dbf'
Media Recovery Log /oracle/app/oracle/flash_recovery_area/archivelog1_39674_821708334.dbf
ORA-279 signalled during: alter database recover logfile '/oracle/app/oracle/flash_recovery_area/archivelog1_39674_821708334.dbf'...
Thu Nov 26 21:48:13 2015
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel

可见，出先此错误的原因是恢复需要的归档日志记录在控制文件或恢复目录中找不到。解决方法分两种情况：
1.如果相关的日志存在且可用的话，就将此日志记录添加到控制文件或恢复目录中。
2.如果相关的日志已经被删除了或不可用了，那么就按照错误的提示scn将数据库恢复到此scn，这里是11688009482。也就是说此时数据库只能进行不完全恢复了，在打开数据库时得使用resetlogs打开。

这里就只恢复到11688009482这个scn点为止：

recover database until scn 11688009482;
RMAN> recover database until scn 11688009482;                                                                                                                                                  

Starting recover at 26-NOV-15                                                                                                                                                                  
using channel ORA_DISK_1

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

Finished recover at 26-NOV-15                                                                                                                                                                  

RMAN>
6，打开数据库 
RMAN> alter database open resetlogs;                                                                                                                                                           

database opened                                                                                                                                                                                

RMAN>

修改密码

SQL> alter user orcstuk identified by "pd141287l118";                                                                                                                                            

User altered.

SQL> exit
7，远程连接报错 
C:UsersAdministrator>tnsping TEST2_180.60

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 26-NOV-2
015 21:59:33

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
D:appAdministratorproduct11.2.0client_2networkadminsqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 12.18.10.6)(PORT = 1521))) (CONNECT_DATA = (SID = orcstu)))
TNS-12535: TNS:operation timed out

C:UsersAdministrator>

解���方案：关闭oracle数据库的防火墙。

8，取消dg设置成单机oracle

看到后台不停的报错日志：

Error 12154 received logging on to the standby
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/orcstu/trace/orcstu_arc2_13889.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC2]: Heartbeat failed to connect to standby 'PD_DG'. Error is 12154.
Thu Nov 26 22:48:02 2015
Error 12154 received logging on to the standby
Errors in file /oracle/app/oracle/diag/rdbms/pdunq/orcstu/trace/orcstu_arc2_13889.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC2]: Heartbeat failed to connect to standby 'PD_DG'. Error is 12154.

经过分析，是因为我的rman备份是从dg环境中的primary上获取的，备份的时候自然而然就带上了dg传输归档日志到standby的功能，所以将rman备份恢复到这台单机的时候，就会在dg归档日志传输的时候报错了，因为单机嘛。解决办法，关闭归档的日志传输，如下所示：

SQL> ALTER system SET log_archive_dest_state_2 ='defer';                                                                                                                                       

System altered.

SQL>

这样就停止了oracle dg归档日志传输，后台不会再报错了。

到此，迁移工作顺利圆满结束了。

：


    
 
 

您可能感兴趣的文章:

  • oracle drop table(表)数据恢复方法
  • Oracle数据库恢复后心得
  • 关于系统重装后Oracle数据库完全恢复的解决办法
  • Oracle数据库备份和恢复工具 RMan
  • 实战Oracle数据库备份与恢复
  • Oracle数据库冷备份的异地恢复
  • oracle下实现恢复一个丢失的数据文件的代码
  • Oracle数据库备份恢复最佳实践
  • Oracle数据库文件恢复以及备份思路
  • oracle数据库创建备份与恢复脚本整理
  • Oracle数据库的备份与恢复完全攻略
  • ORACLE 数据库RMAN备份恢复
  • 一次误操作引起的Oracle数据库大恢复
  • oracle drop table(表)数据恢复方法 iis7站长之家
  • Oracle数据库的备份及恢复策略研究
  • 超级复杂困难之Oracle数据库大恢复
  • Oracle数据库的备份与恢复技术方案
  • 优化Oracle停机时间及数据库恢复
  • oracle bbed恢复删除数据实例
    •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle，但没有光盘，请大家推荐一个oracle下载站点（unix版本的）。谢谢！！！！
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE，用root用户和用oracle用户安装有什么区别么？
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接，远程连接Oracle服务器？？


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3