【原創】模擬控制檔案丟失的資料庫恢復
一 資料庫版本
SYS@LEO1>showuser
USER is"SYS"
SYS@LEO1>select* from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux:Version 11.2.0.1.0 - Production
NLSRTL Version11.2.0.1.0 - Production
1.模擬控制檔案丟失後的資料庫恢復(完全恢復)。
今天的主題是備份與恢復，目的就是保護資料的安全性，眾所周知Oracle之所以在市場上佔據了50%的份額，與它提供了強大的資料保護措施是分不開的，下面我們就來簡捷的介紹一下。
1）物理備份
冷備：這是最原始的一種備份方法，又是最簡單可行的，就和copy一份檔案一樣，直接把庫shutdown複製一份即可，操作簡單，恢復快。當在一個沒有專業人員的場合下，告訴他們這麼操作是簡單可行的，不是不可能只是你沒遇到，一切皆有可能哦！
熱備：Oracle專業備份工具RMAN，這是在8i就有的東東，很強大，可以在很多維度層面進行備份恢復，利用RMAN可以在聯機的情況下進行線上備份與恢復。
2）邏輯備份
Exp/Imp：表級 使用者級 資料庫級進行邏輯備份，邏輯是對於業務層面而言的，例如我只想備份 person employment address表的內容用這種方法將會非常簡單，它的亮點更在於備份出來的檔案非常好遷移，相容不同版本
Expdp/Impdp：這是上面2個工具的高階版，可壓縮 速度更快 傳輸表空間的最佳利器，但只能用在伺服器端
3）例項恢復
例項是什麼，就是記憶體區+後臺程式，那麼例項恢復也就是恢復記憶體資料，例如 突然當機 掉電 強制關庫等，在你startup 啟動時候後臺會自動進行例項恢復。SMON程式負責執行
4）介質恢復
就是恢復硬碟資料，例如 檔案被誤刪除 壞塊等，需要手工恢復
在介紹了幾種備份恢復方法後，我們進入topic，如何進行控制檔案丟失恢復，先看一下資料庫各種狀態
5）SYS@LEO1>archivelog list 資料庫處於非歸檔狀態
Database logmode No Archive Mode
Automaticarchival Disabled
Archivedestination /u02/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online logsequence 71
Current logsequence 73
我們要先做一個RMAN全備，首先啟動歸檔功能
[oracle@leonarding1oracle]$ pwd
/u02/app/oracle
[oracle@leonarding1oracle]$ mkdir archdata 建立一個歸檔日誌目錄
在ORACLE10g和11g版本，ORACLE預設的日誌歸檔路徑為閃回恢復區，但我們也可以修改為自己指定的目錄路徑
SYS@LEO1>altersystem set log_archive_dest_1='location=/u02/app/oracle/archdata' scope=both;
System altered.
SYS@LEO1>setlinesize 300 pagesize 999
設定的歸檔日誌儲存路徑已經生效
SYS@LEO1>selectdest_name,destination,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';
DEST_NAME DESTINATION STATUS ERROR
------------------------------------------------------------------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_1 /u02/app/oracle/archdata VALID
啟動到mount狀態，啟動歸檔模式
SYS@LEO1>shutdownimmediate 關庫
Database closed.
Databasedismounted.
ORACLE instanceshut down.
SYS@LEO1>startupmount mount狀態
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>alterdatabase archivelog; 啟動歸檔模式
Database altered.
SYS@LEO1>alterdatabase open; 開啟資料庫
Database altered.
注：凡是alter database操作都是對控制檔案進行修改
凡是alter system 操作都是對引數檔案進行修改
SYS@LEO1>altersystem switch logfile; 手工切換日誌（不會觸發檢查點，自動切換會）
System altered.
SYS@LEO1>selectsequence#,name,archived,applied from v$archived_log; 檢視已經歸檔的日誌資訊
SEQUENCE# NAME ARC APPLIED
---------------------------------------------------------------------------------------------------------------------------------------------
73 /u02/app/oracle/archdata/1_73_813654649.dbf YES NO
作業系統層面檢視，沒有問題也生成了
[oracle@leonarding1archdata]$ ll
total 5624
-rw-r----- 1oracle asmadmin 5757952 Apr 25 21:28 1_73_813654649.dbf
SYS@LEO1>archivelog list
Database logmode Archive Mode 歸檔模式
Automaticarchival Enabled 自動歸檔啟動
Archivedestination /u02/app/oracle/archdata 歸檔日誌目錄
Oldest online logsequence 72 舊線上日誌序號
Next log sequenceto archive 74 下一個歸檔日誌序號
Current logsequence 74 當前日誌序號
下面我們就要進行RMAN全庫備份了，在此之前還需要設定一下RMAN的環境變數
6）登陸RMAN
[oracle@leonarding1archdata]$ rman target sys/oracle
Recovery Manager:Release 11.2.0.1.0 - Production on Fri Apr 26 06:05:24 2013
Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.
connectedto target database: LEO1 (DBID=1692458681) 只有連線到目標庫才能顯示環境變數，這些後設資料是存放在控制檔案中的
顯示當前RMAN的環境變數
RMAN> show all;
using targetdatabase control file instead of recovery catalog
RMAN configurationparameters for database with db_unique_name LEO1 are:
CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUPOPTIMIZATION OFF; # default
CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default
CONFIGURECONTROLFILE AUTOBACKUP OFF; # default
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICETYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOGBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGUREMAXSETSIZE TO UNLIMITED; # default
CONFIGUREENCRYPTION FOR DATABASE OFF; # default
CONFIGUREENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default
CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default
建立RMAN預設備份介質儲存目錄/u02/app/oracle/backup
[oracle@leonarding1oracle]$ mkdir backup
RMAN> configure channel device type disk format'/u02/app/oracle/backup/DB_%U';
new RMANconfiguration parameters:
CONFIGURE CHANNELDEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
new RMANconfiguration parameters are successfully stored 新man配置引數生效
配置控制檔案自動備份並儲存到/u02/app/oracle/backup/control目錄
[oracle@leonarding1backup]$ mkdir control
[oracle@leonarding1control]$ pwd
/u02/app/oracle/backup/control
RMAN> configure controlfile autobackup on; 啟動控制檔案自動備份
new RMANconfiguration parameters:
CONFIGURECONTROLFILE AUTOBACKUP ON;
new RMANconfiguration parameters are successfully stored
RMAN> configure controlfile autobackup format for device type diskto '/u02/app/oracle/backup/control/cf_%F';
new RMANconfiguration parameters: 配置控制檔案自動備份目錄和格式
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';
new RMANconfiguration parameters are successfully stored
調整備份介質保留期為7天
RMAN> configureretention policy to recovery window of 7 days;
new RMANconfiguration parameters:
CONFIGURERETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMANconfiguration parameters are successfully stored
顯示配置後RMAN環境變數
RMAN> show all;
RMAN configurationparameters for database with db_unique_name LEO1 are:
CONFIGURERETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUPOPTIMIZATION OFF; # default
CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default
CONFIGURECONTROLFILE AUTOBACKUP ON;
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';
CONFIGURE DEVICETYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGUREARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURECHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
CONFIGUREMAXSETSIZE TO UNLIMITED; # default
CONFIGUREENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTIONALGORITHM 'AES128'; # default
CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default
CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default
帶顏色的就是我們剛剛修改過的變數
7）啟動RMAN的壓縮備份功能對資料庫進行全備Oracle10g只壓縮RMAN後設資料11g真正壓縮了資料
backup as compressed backupset full database format 命令列中直接指定壓縮選項即可
'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn'include current controlfile
plus
archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input; 全部備份完之後，刪除備份過的舊歸檔日誌
如果我們想使用預設通道預設配置備份一次資料庫，同時刪除備份過的歸檔日誌，那麼命令為
RMAN> backup ascompressed backupset full database include current controlfile plus archivelogdelete all input;
Starting backup at26-APR-13 備份時間
current logarchived
allocated channel:ORA_DISK_1 預設通道磁碟
channelORA_DISK_1: SID=140 device type=DISK
channelORA_DISK_1: starting compressed archived log backup set 先壓縮備份的歸檔日誌
channel ORA_DISK_1:specifying archived log(s) in backup set 備份了73 74歸檔日誌
input archived logthread=1 sequence=73 RECID=1 STAMP=813706084
input archived logthread=1 sequence=74 RECID=2 STAMP=813739820
channelORA_DISK_1: starting piece 1 at 26-APR-13 啟動備份片
channelORA_DISK_1: finished piece 1 at 26-APR-13 完成備份片
piece handle= /u02/app/oracle/backup/DB_01o81bpd_1_1 tag=TAG20130426T065020 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 備份完成用時1秒
channelORA_DISK_1: deleting archived log(s) 刪除已備份的歸檔日誌73 74
archived log filename=/u02/app/oracle/archdata/1_73_813654649.dbf RECID=1 STAMP=813706084
archived log filename=/u02/app/oracle/archdata/1_74_813654649.dbf RECID=2 STAMP=813739820
Finished backup at26-APR-13
Starting backup at26-APR-13
using channelORA_DISK_1
channelORA_DISK_1: starting compressed full datafile backup set 再壓縮備份資料檔案
channelORA_DISK_1: specifying datafile(s) in backup set 指定備份如下資料檔案
input datafilefile number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafilefile number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafilefile number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafilefile number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafilefile number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channelORA_DISK_1: starting piece 1 at 26-APR-13 啟動備份片
channelORA_DISK_1: finished piece 1 at 26-APR-13 完成備份片
piece handle= /u02/app/oracle/backup/DB_02o81bpf_1_1 tag=TAG20130426T065022 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:01:45備份完成用時1分45秒
channelORA_DISK_1: starting compressed full datafile backup set
channelORA_DISK_1: specifying datafile(s) in backup set
includingcurrent control file in backupset 這個是備份資料檔案的同時包含備份控制檔案
channelORA_DISK_1: starting piece 1 at 26-APR-13
channel ORA_DISK_1:finished piece 1 at 26-APR-13
piece handle= /u02/app/oracle/backup/DB_03o81bso_1_1 tag=TAG20130426T065022 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 備份完成用時1秒
Finished backup at26-APR-13
Starting backup at26-APR-13
current logarchived
using channelORA_DISK_1
channelORA_DISK_1: starting compressed archived log backup set
channelORA_DISK_1: specifying archived log(s) in backup set
input archived logthread=1 sequence=75 RECID=3 STAMP=813739930 備份75歸檔日誌
channel ORA_DISK_1:starting piece 1 at 26-APR-13
channelORA_DISK_1: finished piece 1 at 26-APR-13
piece handle= /u02/app/oracle/backup/DB_04o81bsq_1_1 tag=TAG20130426T065210 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01 備份完成用時1秒
channelORA_DISK_1: deleting archived log(s) 刪除已備份的歸檔日誌75
archived log filename=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=3 STAMP=813739930
Finished backup at26-APR-13
Starting ControlFile and SPFILE Autobackup at 26-APR-13 啟動控制檔案和引數檔案自動備份
piece handle= /u02/app/oracle/backup/control/cf_c-1692458681-20130426-00 comment=NONE
Finished ControlFile and SPFILE Autobackup at 26-APR-13 完成自動備份
在作業系統上都可以找到對應的備份集並且已經刪除了備份過的舊歸檔日誌
[oracle@leonarding1backup]$ pwd
/u02/app/oracle/backup
[oracle@leonarding1backup]$ ll
total 249468
drwxr-xr-x 2oracle oinstall 4096 Apr 26 06:52control
-rw-r-----1 oracle asmadmin 2790912 Apr 26 06:50DB_01o81bpd_1_1
-rw-r-----1 oracle asmadmin 251551744 Apr 26 06:52 DB_02o81bpf_1_1
-rw-r-----1 oracle asmadmin 1097728 Apr 26 06:52DB_03o81bso_1_1
-rw-r-----1 oracle asmadmin 7168 Apr 26 06:52DB_04o81bsq_1_1
[oracle@leonarding1 backup]$ cd control/
[oracle@leonarding1control]$ ll
total 9600
-rw-r-----1 oracle asmadmin 9830400 Apr 26 06:52 cf_c-1692458681-20130426-00
[oracle@leonarding1archdata]$ pwd
/u02/app/oracle/archdata
[oracle@leonarding1archdata]$ ll 歸檔日誌全沒有了
total 0
新的歸檔日誌是從76號開始，75號之前都已經備份並刪除
SYS@LEO1>archivelog list
Database logmode Archive Mode
Automaticarchival Enabled
Archivedestination /u02/app/oracle/archdata
Oldest online logsequence 74
Next log sequenceto archive 76
Current logsequence 76
到此我們的備份準備已經完成，稍微休息一下：）坐車上班班
8）SYS@LEO1>selectstatus from v$instance; 檢查資料庫狀態
STATUS
------------
OPEN
LEO1@LEO1>showparameter control_files 我們檢查一下控制檔案個數
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_files string /u02/app/oracle/oradata/LEO1/control01.ctl,
/u02/app/oracle/oradata/LEO1/control02.ctl
[oracle@leonarding1LEO1]$ ll 作業系統上也是2個沒有問題
total 2618136
-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:01control01.ctl
-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:01control02.ctl
一般控制檔案丟失大多數都是被誤刪除了，用rm 命令刪除control01.ctl檔案
[oracle@leonarding1trace]$ pwd
/u02/app/oracle/diag/rdbms/leo1/LEO1/trace
[oracle@leonarding1 trace]$ tail -10falert_LEO1.log 實時監控告警日誌看看有什麼變化
Fri Apr 2606:50:20 2013
Thread 1 advancedto log sequence 75 (LGWR switch)
Current log# 3 seq# 75 mem# 0:/u02/app/oracle/oradata/LEO1/redo03.log
Archived Log entry2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:
Fri Apr 2606:52:10 2013
ALTER SYSTEMARCHIVE LOG
Fri Apr 2606:52:10 2013
Thread 1 advancedto log sequence 76 (LGWR switch)
Current log# 1 seq# 76 mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log
Archived Log entry3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:
[oracle@leonarding1LEO1]$ rm control01.ctl 模擬control01檔案丟失的場景
LEO1@LEO1>createtablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10mautoextend off;
create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
l 我們建立一個表空間，此時突然報錯
ERROR at line 1:
ORA-00210:cannot open the specified control file 不能開啟指定的控制檔案
ORA-00202:control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' 控制檔案丟失
ORA-27041:unable to open file 無法開啟這個檔案
Linux-x86_64Error: 2: No such file or directory 找不到這個檔案，好恐怖bless，趕緊看看alert日誌
Additionalinformation: 3
Alert_LEO1.log日誌內容
create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
ORA-210 signalledduring: create tablespace test datafile'/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off...
Fri Apr 2609:14:15 2013
Errors in file/u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_m000_7975.trc:
ORA-00210: cannotopen the specified control file
ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unableto open file
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
是不是和上面報的錯誤資訊一樣啊，由於是我們自己搞的鬼，所以我們明白是怎麼回事，如果在生產庫上就要首先檢視日誌資訊進行分析啦，好了現在我們開始修復吧->start on
思路：我們首先要清楚oracle為了保證其穩定性，控制檔案都是多路複用的，如果使用dbca安裝10g預設有3個控制檔案 11g有兩個，我們刪除了其中一個，可能有的朋友會說，我們映象出來一個控制檔案不就好了麼，沒錯思路很正確，但前提是要關閉資料庫使所有的SCN號一致，也有可能你會遇上無法立即關閉的情況，木有辦法只能強制關閉了，
SYS@LEO1>shutdownimmediate
ORA-00210: cannotopen the specified control file
ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'
ORA-27041: unableto open file
Linux-x86_64Error: 2: No such file or directory
Additionalinformation: 3
SYS@LEO1>shutdownabort
ORACLE instanceshut down.
[oracle@leonarding1LEO1]$ cp control02.ctl control01.ctl 使用完好的控制檔案恢復被刪除的控制檔案
[oracle@leonarding1LEO1]$ ll
total 2618136
-rw-r----- 1oracle oinstall 9748480 Apr 26 09:34control01.ctl
-rw-r----- 1oracle asmadmin 9748480 Apr 26 09:28control02.ctl
SYS@LEO1>startupmount 啟動到mount狀態沒有報錯，說明我們恢復成功了
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>selectcheckpoint_change# from v$database; 資料庫全域性SCN號，放在控制檔案裡
CHECKPOINT_CHANGE#
--------------------------------
909922
SYS@LEO1>selectname,checkpoint_change# from v$datafile; 資料檔案SCN號，放在控制檔案裡
NAME CHECKPOINT_CHANGE#
----------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/LEO1/system01.dbf 909922
/u02/app/oracle/oradata/LEO1/sysaux01.dbf 909922
/u02/app/oracle/oradata/LEO1/undotbs01.dbf 909922
/u02/app/oracle/oradata/LEO1/users01.dbf 909922
/u02/app/oracle/oradata/LEO1/leo1.dbf 909922
SYS@LEO1>selectname,checkpoint_change# from v$datafile_header; 資料檔案頭SCN號，放在資料檔案頭裡
NAME CHECKPOINT_CHANGE#
----------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/LEO1/system01.dbf 909922
/u02/app/oracle/oradata/LEO1/sysaux01.dbf 909922
/u02/app/oracle/oradata/LEO1/undotbs01.dbf 909922
/u02/app/oracle/oradata/LEO1/users01.dbf 909922
/u02/app/oracle/oradata/LEO1/leo1.dbf 909922
SYS@LEO1>selectname,last_change# from v$datafile; 資料檔案結束SCN號，放在控制檔案裡
NAME LAST_CHANGE#
----------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/LEO1/system01.dbf
/u02/app/oracle/oradata/LEO1/sysaux01.dbf
/u02/app/oracle/oradata/LEO1/undotbs01.dbf
/u02/app/oracle/oradata/LEO1/users01.dbf
/u02/app/oracle/oradata/LEO1/leo1.dbf
這個LAST_CHANGE為NULL，我們要知道Oracle做不做例項恢復就是看這個SCN是否為NULL
如果資料庫非正常關閉值為NULL
如果資料庫正常關閉值為xxxxxx
特例：資料庫為open狀態時LAST_CHANGE也為NULL，但現在我們是mount狀態
SYS@LEO1>selectstatus from v$instance;
STATUS
------------
MOUNTED
我們在開打資料庫的一霎那就會啟動例項恢復，看alert日誌即可
SYS@LEO1>alterdatabase open;
Database altered.
Alert日誌內容
Fri Apr 2609:50:08 2013
alter databaseopen 開啟資料庫
Beginningcrash recovery of 1 threads 進行例項恢復
parallel recovery started with 2 processes 啟動2個恢復程式
Started redo scan
Completed redoscan
read 30 KB redo, 31 data blocks need recovery
Started redoapplication at
Thread 1: logseq 76, block 23074
Recovery of OnlineRedo Log: Thread 1 Group 1 Seq 76 Reading mem 0 從76號日誌為起始位置
Mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log 應用redo日誌進行恢復
Completed redoapplication of 0.02MB
Completed crashrecovery at
Thread 1: logseq 76, block 23134, scn 934394 一直應用到redo最後一個SCN號
31 data blocks read, 31 data blocks written,30 redo k-bytes read 恢復了31個資料塊，讀取了30K redo
Fri Apr 2609:50:08 2013
LGWR: STARTINGARCH PROCESSES 啟動歸檔程式進行歸檔
Fri Apr 2609:50:08 2013
ARC0 started withpid=22, OS id=8396
ARC0: Archivalstarted
LGWR: STARTINGARCH PROCESSES COMPLETE
ARC0: STARTINGARCH PROCESSES
Fri Apr 2609:50:09 2013
ARC1 started withpid=23, OS id=8400
Fri Apr 2609:50:09 2013
ARC2 started withpid=24, OS id=8404
ARC1: Archivalstarted
Fri Apr 2609:50:09 2013
ARC3 started withpid=25, OS id=8408
ARC2: Archivalstarted
ARC1: Becoming the'no FAL' ARCH
ARC1: Becoming the'no SRL' ARCH
ARC2: Becoming theheartbeat ARCH
Thread 1 advancedto log sequence 77 (thread open)
Thread 1 opened atlog sequence 77
Current log# 2 seq# 77 mem# 0:/u02/app/oracle/oradata/LEO1/redo02.log
Successful open ofredo thread 1
MTTR advisory isdisabled because FAST_START_MTTR_TARGET is not set
Fri Apr 2609:50:09 2013
SMON: enablingcache recovery SMON程式負責例項的恢復
Archived Log entry4 added for thread 1 sequence 76 ID 0x64e13fb9 dest 1:
ARC3: Archivalstarted
ARC0: STARTINGARCH PROCESSES COMPLETE
Successfullyonlined Undo Tablespace 2.
Verifying fileheader compatibility for 11g tablespace encryption..
Verifying 11g fileheader compatibility for tablespace encryption completed
SMON: enabling txrecovery
DatabaseCharacterset is ZHS16GBK
No ResourceManager plan active
replication_dependency_trackingturned off (no async multimaster replication found)
Startingbackground process QMNC
Fri Apr 2609:50:14 2013
QMNC started withpid=26, OS id=8412
Completed: alterdatabase open 到此我們完成資料庫的open動作，例項恢復完畢
Fri Apr 2609:50:18 2013
Startingbackground process CJQ0
Fri Apr 2609:50:18 2013
CJQ0 started withpid=30, OS id=8436
從alter日誌的流程上我們就可以看出，oracle例項恢復的內容過程是什麼樣的，透過以上案例我們即瞭解了控制檔案的恢復過程又瞭解了資料庫例項的恢復過程，可謂一舉兩得，大家好好的消化消化，休息一下，該上班工作啦：）
9）下了班我們繼續，上次講到了，使用copy方式來恢復控制檔案，下面再講一種使用備份集來恢復控制檔案的方法。
【參】Books-> Backupand Recovery Reference -> RESTORE 和 RECOVER 參考官方文件是個好習慣
SYS@LEO1>shutdownimmediate 我們先關閉資料庫
Database closed.
Databasedismounted.
ORACLE instanceshut down.
[oracle@leonarding1LEO1]$ rm -rf control01.ctl 刪除控制檔案
SYS@LEO1>startup
ORACLE instancestarted.
Total SystemGlobal Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
ORA-00205: errorin identifying control file, check alert log for more info
指定的控制檔案錯誤，檢查alert日誌獲取更多資訊
[oracle@leonarding1trace]$ tail -20f alert_LEO1.log
ALTERDATABASE MOUNT
ORA-00210: cannotopen the specified control file
ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl' 這寫著1號控制檔案丟失
ORA-27037: unableto obtain file status
Linux-x86_64Error: 2: No such file or directory 找不到這個檔案
Additionalinformation: 3
ORA-205 signalledduring: ALTER DATABASE MOUNT...
Fri Apr 2619:44:05 2013
Checker run found1 new persistent data failures
SYS@LEO1>shutdownimmediate 關庫
ORA-01507:database not mounted
ORACLE instanceshut down.
[oracle@leonarding1~]$ rman target sys/oracle 連結RMAN
Recovery Manager:Release 11.2.0.1.0 - Production on Fri Apr 26 19:49:00 2013
Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected totarget database (not started) 庫沒有啟動
RMAN> startupnomount 把庫啟動到nomount狀態
Oracle instancestarted
Total System GlobalArea 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 8241152 bytes
有一個地方容易出錯，大家都喜歡用這條語句來恢復
RMAN> RESTORECONTROLFILE FROM AUTOBACKUP;
Starting restoreat 26-APR-13
using channelORA_DISK_1
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130426
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130425
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130424
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130423
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130422
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130421
channelORA_DISK_1: looking for AUTOBACKUP on day: 20130420
channelORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002:failure of restore command at 04/26/2013 20:01:04
RMAN-06172: noAUTOBACKUP found or specified handle is not a valid copy or piece
報錯：說找不到指定的備份集
RMAN> show all;
RMAN configurationparameters for database with db_unique_name LEO1 are:
CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUPOPTIMIZATION OFF; # default
CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default
CONFIGURECONTROLFILE AUTOBACKUP OFF; # default
CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
原來我們配置的控制檔案自動備份儲存目錄是不是失效啦，這是為什麼呢，原來rman後設資料資訊是寫在controlfile中的，而現在控制檔案又損壞了不能開啟，一個不能開啟的檔案我們是不是讀不到裡面的內容啊，我們只啟動到了nomount狀態只讀取引數檔案資訊，因此我們在恢復控制檔案的時候指定一下“原來備份的儲存目錄”告訴rman從哪個路徑下可以找到備份集就可以了。
RMAN> restore controlfile from'/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00';
Starting restoreat 26-APR-13
using channelORA_DISK_1
channelORA_DISK_1: restoring control file
channelORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u02/app/oracle/oradata/LEO1/control01.ctl
output filename=/u02/app/oracle/oradata/LEO1/control02.ctl
Finished restoreat 26-APR-13
[oracle@leonarding1LEO1]$ ll
total 2618136
-rw-r----- 1oracle asmadmin 9748480 Apr 26 20:17control01.ctl
-rw-r----- 1oracle asmadmin 9748480 Apr 26 20:17control02.ctl
我們一起恢復了所有的控制檔案
RMAN> alterdatabase mount; 現在資料庫可以正常載入了對吧
database mounted
released channel:ORA_DISK_1
那我們可以alter database open來開啟資料庫嗎，顯然是不行的，大家知道為什麼嗎？
你想想如果這個控制檔案是從10天之前的一個備份還原的與當前的資料庫物理結構能一致嗎！
顯然是不可以的，我們只有用備份在重新同步資料庫
RMAN> restoredatabase; RMAN備份還原restore（複製）資料檔案
Starting restoreat 26-APR-13
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=134 device type=DISK
channelORA_DISK_1: starting datafile backup set restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1:restoring datafile 00001 to /u02/app/oracle/oradata/LEO1/system01.dbf
channelORA_DISK_1: restoring datafile 00002 to/u02/app/oracle/oradata/LEO1/sysaux01.dbf
channelORA_DISK_1: restoring datafile 00003 to/u02/app/oracle/oradata/LEO1/undotbs01.dbf
channelORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf
channelORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channelORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_02o81bpf_1_1
channelORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1tag=TAG20130426T065022
channelORA_DISK_1: restored backup piece 1
channelORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restoreat 26-APR-13
RMAN> recoverdatabase; 應用redo日誌恢復recover（同步）資料庫
Starting recoverat 26-APR-13
using channelORA_DISK_1
starting mediarecovery
archived log forthread 1 with sequence 75 is already on disk as file/u02/app/oracle/oradata/LEO1/redo03.log
archived log forthread 1 with sequence 76 is already on disk as file/u02/app/oracle/oradata/LEO1/redo01.log
archived log forthread 1 with sequence 77 is already on disk as file/u02/app/oracle/oradata/LEO1/redo02.log
archived log filename=/u02/app/oracle/oradata/LEO1/redo03.log thread=1 sequence=75
archived log filename=/u02/app/oracle/oradata/LEO1/redo01.log thread=1 sequence=76
archived log filename=/u02/app/oracle/oradata/LEO1/redo02.log thread=1 sequence=77
mediarecovery complete, elapsed time: 00:00:14 這就是介質恢復，要把資料檔案同步到損壞的前一刻
因為這是不完全恢復，因此我們不能用alter database open來開啟資料庫
RMAN> alterdatabase open resetlogs;
database opened
SYS@LEO1>selectstatus from v$instance; 資料庫已開啟，可以正常使用
STATUS
------------
OPEN
小結：因為我們進行了不完全恢復，恢復到之前的某一點，現在資料庫以這點為一個新的起點（相當是一個煥然一新的庫），resetlogs就是重置歸檔日誌從1開始編碼，之前的歸檔全部無效。
SYS@LEO1>altersystem switch logfile; 我們重新切換一次
System altered.
[oracle@leonarding1oracle]$ cd archdata
[oracle@leonarding1archdata]$ ll
total 14028
-rw-r-----1 oracle asmadmin 222720 Apr 26 21:051_1_813790699.dbf
-rw-r----- 1oracle asmadmin 5632 Apr 26 20:581_75_813654649.dbf
-rw-r----- 1oracle asmadmin 11844608 Apr 26 20:58 1_76_813654649.dbf
-rw-r----- 1oracle asmadmin 2284032 Apr 26 20:581_77_813654649.dbf
注意：歸檔日誌編碼重置了，從1開始了，75 76 77歸檔日誌全部無效了，為了不礙眼你可以刪除掉。
2013.4.26
天津&spring
分享技術~成就夢想
Blog：
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-759452/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 模擬控制檔案丟失進行恢復。
- 丟失一個控制檔案並恢復資料庫資料庫
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 【RMAN】SYSTEM表空間資料檔案丟失恢復模擬
- 恢復丟失的控制檔案
- 模擬資料檔案丟失
- 資料檔案丟失的恢復
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- 控制檔案丟失的RMAN恢復
- 控制檔案丟失恢復(二)
- 控制檔案全部丟失恢復
- 引數檔案控制檔案和資料檔案丟失的恢復
- 資料檔案丟失如何恢復
- Sql Server資料庫檔案丟失的恢復方法SQLServer資料庫
- 完全恢復之所有資料庫檔案丟失資料庫
- 資料庫各種檔案丟失恢復大全。資料庫
- 【資料庫資料恢復】Sql Server資料庫檔案丟失的資料恢復過程資料庫資料恢復SQLServer
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- RMAN恢復案例:丟失全部資料檔案恢復
- DATA GUARD主庫丟失資料檔案的恢復(2)
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- RMAN完全恢復丟失的資料檔案
- 普通資料檔案丟失的恢復方法
- 資料檔案丟失損壞的恢復--
- 電腦檔案丟失資料恢復資料恢復
- 恢復測試：擁有當時的全部歸檔，控制檔案，恢復丟失的資料檔案。
- 當前控制檔案全部丟失恢復
- 無備份丟失部分資料檔案和控制檔案恢復 [轉]
- 【虛擬機器資料恢復】Hyper-V虛擬化檔案丟失的資料恢復案例虛擬機資料恢復
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- 資料庫資料恢復—MongoDB資料庫檔案丟失，啟動報錯的資料恢復案例資料庫資料恢復MongoDB
- MongoDB資料庫報錯，資料庫檔案丟失資料恢復案例MongoDB資料庫資料恢復
- 丟失已歸檔日誌檔案下恢復資料庫資料庫
- 歸檔模式下資料檔案丟失的恢復模式
- RMAN資料庫恢復 之歸檔模式有(無)備份-丟失資料檔案的恢復資料庫模式