oracle11g如何将dataguard的主库数据恢复到oracle单机实例？
来源：互联网 发布：python多线程selenium 编辑：程序博客网 时间：2024/05/17 08:24
需要将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-15using channel ORA_DISK_1channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 26-NOV-15RMAN> 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-15using channel ORA_DISK_1channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 26-NOV-15RMAN> 3，注册归档
要将db状态改成mount才行
RMAN> alter database mount; database mounted released channel: ORA_DISK_1RMAN> 在新控制文件中注册数据文件备份和归档备份
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=DISKCrosschecked 97 objects Finished implicit crosscheck backup at 26-NOV-15Starting implicit crosscheck copy at 26-NOV-15using channel ORA_DISK_1Crosschecked 2 objects Finished implicit crosscheck copy at 26-NOV-15searching for all files in the recovery areacataloging 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.logFile Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5036.bakFile Name: /data/impdp/2015-11-26/full_orcstu_20151126_5037.bakFile Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5038.bakDo 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.bakFile Name: /data/impdp/2015-11-26/full_orcstu_20151126_5037.bakFile Name: /data/impdp/2015-11-26/arch_orcstu_20151126_5038.bakList of Files Which Where Not Cataloged=======================================File Name: /data/impdp/2015-11-26/rman_backup.log RMAN-07517: Reason: The file header is corruptedRMAN> 4，开始恢复整个库
因为前面恢复了整个spfile已经controlfile，所以接下来恢复所有库的话，就不用带参数，直接恢复restore database就可以 ;
RMAN> restore database; Starting restore at 26-NOV-15 using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /home/oradata/orcstu/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /home/oradata/orcstu/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /home/oradata/orcstu/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /home/oradata/orcstu/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /home/oradata/orcstu/orcstuk01.dbfchannel ORA_DISK_1: restoring datafile 00006 to /home/oradata/orcstu/plas01.dbfchannel ORA_DISK_1: restoring datafile 00007 to /home/oradata/orcstu/pl01.dbfchannel ORA_DISK_1: restoring datafile 00008 to /home/oradata/orcstu/help01.dbfchannel ORA_DISK_1: restoring datafile 00009 to /home/oradata/orcstu/adobelc01.dbfchannel ORA_DISK_1: restoring datafile 00010 to /home/oradata/orcstu/sms01.dbfchannel ORA_DISK_1: restoring datafile 00011 to /home/oradata/orcstu/plcrm01.dbfchannel ORA_DISK_1: restoring datafile 00012 to /home/oradata/orcstu/orcstuk02.dbfchannel ORA_DISK_1: restoring datafile 00013 to /home/oradata/orcstu/datagm01.dbfchannel ORA_DISK_1: reading from backup piece /data/impdp/2015-11-26/full_orcstu_20151126_5037.bakchannel ORA_DISK_1: piece handle=/data/impdp/2015-11-26/full_orcstu_20151126_5037.bak tag=TAG20151126T030008 channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:32:50Finished restore at 26-NOV-15RMAN> 5，recover数据库
RMAN> recover database; Starting recover at 26-NOV-15 using channel ORA_DISK_1starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=39674channel ORA_DISK_1: reading from backup piece /data/impdp/2015-11-26/arch_orcstu_20151126_5038.bakchannel ORA_DISK_1: piece handle=/data/impdp/2015-11-26/arch_orcstu_20151126_5038.bak tag=TAG20151126T032346 channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03archived 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=39675RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 11/26/2015 21:48:13RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39675 and starting SCN of 11688009482RMAN> 后台alert日志： 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13Completed: alter database recover datafile list 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13alter database recover if needed start until cancel using backup controlfileMedia Recovery Start started logmerger processParallel Media Recovery started with 4 slavesORA-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.dbfORA-279 signalled during: alter database recover logfile '/oracle/app/oracle/flash_recovery_area/archivelog1_39674_821708334.dbf'...Thu Nov 26 21:48:13 2015alter database recover cancelMedia Recovery CanceledCompleted: 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_1starting 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:\Users\Administrator>tnsping TEST2_180.60TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 26-NOV-2015 21:59:33Copyright (c) 1997, 2010, Oracle. All rights reserved.Used parameter files:D:\app\Administrator\product\11.2.0\client_2\network\admin\sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 12.18.10.6)(PORT = 1521))) (CONNECT_DATA = (SID = orcstu)))TNS-12535: TNS:operation timed outC:\Users\Administrator>解决方案：关闭oracle数据库的防火墙。
8，取消dg设置成单机oracle
看到后台不停的报错日志：
Error 12154 received logging on to the standbyErrors in file /oracle/app/oracle/diag/rdbms/pdunq/orcstu/trace/orcstu_arc2_13889.trc:ORA-12154: TNS:could not resolve the connect identifier specifiedPING[ARC2]: Heartbeat failed to connect to standby 'PD_DG'. Error is 12154.Thu Nov 26 22:48:02 2015Error 12154 received logging on to the standbyErrors in file /oracle/app/oracle/diag/rdbms/pdunq/orcstu/trace/orcstu_arc2_13889.trc:ORA-12154: TNS:could not resolve the connect identifier specifiedPING[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归档日志传输，后台不会再报错了。
到此，迁移工作顺利圆满结束了。
4 0
- oracle11g如何将dataguard的主库数据恢复到oracle单机实例？
- ORACLE11G 将dataguard的rman备份恢复到测试环境的单机oracle中的详细过程
- Oracle RAC到单机的DataGuard搭建
- Oracle 12c RAC 到单机实例 DATAGUARD搭建
- Oracle Dataguard搭建-单机到单机
- oracle11g dataguard主库备份恢复到测试环境单库报错：ORA-19502: write error on file
- DataGuard 单机到单机
- oracle11g dataguard undo恢复
- oracle11g dataguard 备库数据同步的检查方法
- 如何将服务器上oracle11g数据dump出来导入到本地的oracle10版本
- OGG12 ORACLE11g RAC 到 ORACLE11g 单机的DML同步
- 如何将SQL Server的数据导入到Oracle
- ORACLE将表中的数据恢复到某一个时间点
- ORACLE将表中的数据恢复到某一个时间点
- ORACLE将表中的数据恢复到某一个时间点
- 如何将里面的数据恢复
- 如何将oracle数据库恢复到指定日期
- 主库为oracle 10G ASM RAC,备库为单实例ASM的DATAGUARD
- 解题笔记（11）——字符串转换为整数
- 小白刚入css 那些关于display的小事。
- 解题笔记（10）——约瑟夫环问题
- 设计模式C++实现（9）——享元模式
- 在html中禁用自动完成
- oracle11g如何将dataguard的主库数据恢复到oracle单机实例？
- 【Android导航 君子生非异也 善假于物也】优秀博客
- PowerDesigner15在生成SQL时报错Generation aborted due to errors detected during the verification of the mod
- 兼容IE、新版Chrome、Firefox，实现本地图片等比例缩放预览
- cocos2d-x学习之自动内存管理和常见宏
- STL运用的C++技术（2）——模板特化
- VC CComboBox用法
- Qt中MainWindow类实例
- openmq-2
