程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle11g如何將dataguard的主庫數據恢復到oracle單機實例？

oracle11g如何將dataguard的主庫數據恢復到oracle單機實例？

編輯：Oracle教程

oracle11g如何將dataguard的主庫數據恢復到oracle單機實例？


需要將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>

在新控制文件中注冊數據文件備份和歸檔備份

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>

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:\Users\Administrator>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:\app\Administrator\product\11.2.0\client_2\network\admin\sqlnet.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:\Users\Administrator>

解決方案：關閉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歸檔日志傳輸，後台不會再報錯了。

到此，遷移工作順利圓滿結束了。

  1. 上一頁:
  2. 下一頁:
Oracle教程
簡單記錄一次REDO文件損壞報錯ORA-00333重做日志讀取塊出錯

簡單記錄一次REDO文件損壞報錯ORA-00333重做日志讀
WIN7上安裝Oracle12c圖解

WIN7上安裝Oracle12c圖解 環境: WIN7

spoon kettle連接數據庫失敗解決方法，spoonkettle

spoon kettle連接數據庫失敗解決方法，spoonk
oracle安裝之check if the DISPLAY variable is set錯誤

oracle安裝之check if the DISPLAY

ORACLE學習之PL/SQL編程——本地動態SQL，oracle本地動態

ORACLE學習之PL/SQL編程——本地動態SQL，ora
OracleÊý¾Ý¿âÎÞ·¨Ê¹ÓÃlocalhostºÍ127.0.0.1·ÃÎÊ

OracleÊý¾Ý¿âÎÞ·¨Ê¹ÓÃlocalhostº
相關文章
閱讀排行榜
oracle中drop、delete和truncate的區別， Oracle WebLogic Server 12.2.1.2安裝部署教程，weblogic12.2.1.2 PL/SQL編程_概述，plsql編程概述 Oracle物理和邏輯備庫健康監測的一個依據 oracle修改列的類型 Oracle 更新Opatch、打補丁 [Oracle]-性能優化工具（2）-ASH Oracle數據庫數據文件位置修改方法 Oracle中IMU技術和redoprivatestrand技術 Oracle體系結構總體概述 Oraclerownum影響執行計劃
熱門圖文
新手SqlServer數據庫dba需要注意的一些小細節，sqlserverdba 連接Oracle數據庫時報ORA-12541:TNS:無監聽程序的圖文解決教程，ora-12541tns 聯接JOIN使用大全 優化MySQL數據庫性能的實際操作方案 db2數據庫遷移到mssql記錄之一 mysql-cluster中的sql node（主）與單獨的mysql server（從）做主從復制（錯誤解決） 表分區(學習筆記)，表分區學習筆記 oracleacfs基本知識。
欄目導航
關於Oracle數據庫Oracle數據庫基礎Oracle教程
Copyright © 程式師世界 All Rights Reserved