【備份恢復】Oracle 資料備份與恢復微實踐
《Oracle 資料備份與恢復微實踐》
新年新群招募： 中國Oracle精英聯盟 170513055
群介紹：本群是大家的一個技術分享社群，在這裡可以領略大師級的技術講座，還有機會參加Oracle舉辦的技術沙龍，與興趣相投的小夥伴一起笑談風雲起，感悟職場情!
一 資料庫版本
SYS@LEO1>show user
USER is "SYS"
SYS@LEO1>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.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>archive log list 資料庫處於非歸檔狀態
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 71
Current log sequence 73
我們要先做一個RMAN全備，首先啟動歸檔功能
[oracle@leonarding1 oracle]$ pwd
/u02/app/oracle
[oracle@leonarding1 oracle]$ mkdir archdata 建立一個歸檔日誌目錄
在ORACLE10g和11g版本，ORACLE預設的日誌歸檔路徑為閃回恢復區，但我們也可以修改為自己指定的目錄路徑
SYS@LEO1>alter system set log_archive_dest_1='location=/u02/app/oracle/archdata' scope=both;
System altered.
SYS@LEO1>set linesize 300 pagesize 999
設定的歸檔日誌儲存路徑已經生效
SYS@LEO1>select dest_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>shutdown immediate 關庫
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup mount mount狀態
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>alter database archivelog; 啟動歸檔模式
Database altered.
SYS@LEO1>alter database open; 開啟資料庫
Database altered.
注：凡是alter database操作都是對控制檔案進行修改
凡是alter system 操作都是對引數檔案進行修改
SYS@LEO1>alter system switch logfile; 手工切換日誌（不會觸發檢查點，自動切換會）
System altered.
SYS@LEO1>select sequence#,name,archived,applied from v$archived_log; 檢視已經歸檔的日誌資訊
SEQUENCE# NAME ARC APPLIED
---------------------------------------------------------------------------------------------------------------------------------------------
73 /u02/app/oracle/archdata/1_73_813654649.dbf YES NO
作業系統層面檢視，沒有問題也生成了
[oracle@leonarding1 archdata]$ ll
total 5624
-rw-r----- 1 oracle asmadmin 5757952 Apr 25 21:28 1_73_813654649.dbf
SYS@LEO1>archive log list
Database log mode Archive Mode 歸檔模式
Automatic archival Enabled 自動歸檔啟動
Archive destination /u02/app/oracle/archdata 歸檔日誌目錄
Oldest online log sequence 72 舊線上日誌序號
Next log sequence to archive 74 下一個歸檔日誌序號
Current log sequence 74 當前日誌序號
下面我們就要進行RMAN全庫備份了，在此之前還需要設定一下RMAN的環境變數
6）登陸RMAN
[oracle@leonarding1 archdata]$ 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.
connected to target database: LEO1 (DBID=1692458681) 只有連線到目標庫才能顯示環境變數，這些後設資料是存放在控制檔案中的
顯示當前RMAN的環境變數
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; # default
建立RMAN預設備份介質儲存目錄/u02/app/oracle/backup
[oracle@leonarding1 oracle]$ mkdir backup
RMAN> configure channel device type disk format '/u02/app/oracle/backup/DB_%U';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
new RMAN configuration parameters are successfully stored 新man配置引數生效
配置控制檔案自動備份並儲存到/u02/app/oracle/backup/control目錄
[oracle@leonarding1 backup]$ mkdir control
[oracle@leonarding1 control]$ pwd
/u02/app/oracle/backup/control
RMAN> configure controlfile autobackup on; 啟動控制檔案自動備份
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> configure controlfile autobackup format for device type disk to '/u02/app/oracle/backup/control/cf_%F';
new RMAN configuration parameters: 配置控制檔案自動備份目錄和格式
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/app/oracle/backup/control/cf_%F';
new RMAN configuration parameters are successfully stored
調整備份介質保留期為7天
RMAN> configure retention policy to recovery window of 7 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
顯示配置後RMAN環境變數
RMAN> show all;
RMAN configuration parameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/app/oracle/backup/control/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u02/app/oracle/backup/DB_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; # default
帶顏色的就是我們剛剛修改過的變數
7）啟動RMAN的壓縮備份功能對資料庫進行全備Oracle 10g只壓縮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 as compressed backupset full database include current controlfile plus archivelog delete all input;
Starting backup at 26-APR-13 備份時間
current log archived
allocated channel: ORA_DISK_1 預設通道磁碟
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set 先壓縮備份的歸檔日誌
channel ORA_DISK_1: specifying archived log(s) in backup set 備份了73 74歸檔日誌
input archived log thread=1 sequence=73 RECID=1 STAMP=813706084
input archived log thread=1 sequence=74 RECID=2 STAMP=813739820
channel ORA_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_01o81bpd_1_1 tag=TAG20130426T065020 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 備份完成用時1秒
channel ORA_DISK_1: deleting archived log(s) 刪除已備份的歸檔日誌73 74
archived log file name=/u02/app/oracle/archdata/1_73_813654649.dbf RECID=1 STAMP=813706084
archived log file name=/u02/app/oracle/archdata/1_74_813654649.dbf RECID=2 STAMP=813739820
Finished backup at 26-APR-13
Starting backup at 26-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set 再壓縮備份資料檔案
channel ORA_DISK_1: specifying datafile(s) in backup set 指定備份如下資料檔案
input datafile file number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafile file number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_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_02o81bpf_1_1 tag=TAG20130426T065022 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45備份完成用時1分45秒
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set 這個是備份資料檔案的同時包含備份控制檔案
channel ORA_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
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 備份完成用時1秒
Finished backup at 26-APR-13
Starting backup at 26-APR-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=75 RECID=3 STAMP=813739930 備份75歸檔日誌
channel ORA_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_04o81bsq_1_1 tag=TAG20130426T065210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 備份完成用時1秒
channel ORA_DISK_1: deleting archived log(s) 刪除已備份的歸檔日誌75
archived log file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=3 STAMP=813739930
Finished backup at 26-APR-13
Starting Control File and SPFILE Autobackup at 26-APR-13 啟動控制檔案和引數檔案自動備份
piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-APR-13 完成自動備份
在作業系統上都可以找到對應的備份集並且已經刪除了備份過的舊歸檔日誌
[oracle@leonarding1 backup]$ pwd
/u02/app/oracle/backup
[oracle@leonarding1 backup]$ ll
total 249468
drwxr-xr-x 2 oracle oinstall 4096 Apr 26 06:52 control
-rw-r----- 1 oracle asmadmin 2790912 Apr 26 06:50 DB_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:52 DB_03o81bso_1_1
-rw-r----- 1 oracle asmadmin 7168 Apr 26 06:52 DB_04o81bsq_1_1
[oracle@leonarding1 backup]$ cd control/
[oracle@leonarding1 control]$ ll
total 9600
-rw-r----- 1 oracle asmadmin 9830400 Apr 26 06:52 cf_c-1692458681-20130426-00
[oracle@leonarding1 archdata]$ pwd
/u02/app/oracle/archdata
[oracle@leonarding1 archdata]$ ll 歸檔日誌全沒有了
total 0
新的歸檔日誌是從76號開始，75號之前都已經備份並刪除
SYS@LEO1>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
Oldest online log sequence 74
Next log sequence to archive 76
Current log sequence 76
到此我們的備份準備已經完成，稍微休息一下：）坐車上班班
8）SYS@LEO1>select status from v$instance; 檢查資料庫狀態
STATUS
------------
OPEN
LEO1@LEO1>show parameter control_files 我們檢查一下控制檔案個數
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u02/app/oracle/oradata/LEO1/control01.ctl,
/u02/app/oracle/oradata/LEO1/control02.ctl
[oracle@leonarding1 LEO1]$ ll 作業系統上也是2個沒有問題
total 2618136
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 09:01 control01.ctl
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 09:01 control02.ctl
一般控制檔案丟失大多數都是被誤刪除了，用rm 命令刪除control01.ctl檔案
[oracle@leonarding1 trace]$ pwd
/u02/app/oracle/diag/rdbms/leo1/LEO1/trace
[oracle@leonarding1 trace]$ tail -10f alert_LEO1.log 實時監控告警日誌看看有什麼變化
Fri Apr 26 06:50:20 2013
Thread 1 advanced to log sequence 75 (LGWR switch)
Current log# 3 seq# 75 mem# 0: /u02/app/oracle/oradata/LEO1/redo03.log
Archived Log entry 2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:
Fri Apr 26 06:52:10 2013
ALTER SYSTEM ARCHIVE LOG
Fri Apr 26 06:52:10 2013
Thread 1 advanced to log sequence 76 (LGWR switch)
Current log# 1 seq# 76 mem# 0: /u02/app/oracle/oradata/LEO1/redo01.log
Archived Log entry 3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:
[oracle@leonarding1 LEO1]$ rm control01.ctl 模擬control01檔案丟失的場景
LEO1@LEO1>create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off;
create tablespace test 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_64 Error: 2: No such file or directory 找不到這個檔案，好恐怖bless，趕緊看看alert日誌
Additional information: 3
Alert_LEO1.log日誌內容
create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off
ORA-210 signalled during: create tablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off...
Fri Apr 26 09:14:15 2013
Errors in file /u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_m000_7975.trc:
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_64 Error: 2: No such file or directory
Additional information: 3
是不是和上面報的錯誤資訊一樣啊，由於是我們自己搞的鬼，所以我們明白是怎麼回事，如果在生產庫上就要首先檢視日誌資訊進行分析啦，好了現在我們開始修復吧->start on
思路：我們首先要清楚oracle為了保證其穩定性，控制檔案都是多路複用的，如果使用dbca安裝10g預設有3個控制檔案 11g有兩個，我們刪除了其中一個，可能有的朋友會說，我們映象出來一個控制檔案不就好了麼，沒錯思路很正確，但前提是要關閉資料庫使所有的SCN號一致，也有可能你會遇上無法立即關閉的情況，木有辦法只能強制關閉了，
SYS@LEO1>shutdown immediate
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_64 Error: 2: No such file or directory
Additional information: 3
SYS@LEO1>shutdown abort
ORACLE instance shut down.
[oracle@leonarding1 LEO1]$ cp control02.ctl control01.ctl 使用完好的控制檔案恢復被刪除的控制檔案
[oracle@leonarding1 LEO1]$ ll
total 2618136
-rw-r----- 1 oracle oinstall 9748480 Apr 26 09:34 control01.ctl
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 09:28 control02.ctl
SYS@LEO1>startup mount 啟動到mount狀態沒有報錯，說明我們恢復成功了
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>select checkpoint_change# from v$database; 資料庫全域性SCN號，放在控制檔案裡
CHECKPOINT_CHANGE#
--------------------------------
909922
SYS@LEO1>select name,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>select name,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>select name,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>select status from v$instance;
STATUS
------------
MOUNTED
我們在開打資料庫的一霎那就會啟動例項恢復，看alert日誌即可
SYS@LEO1>alter database open;
Database altered.
Alert日誌內容
Fri Apr 26 09:50:08 2013
alter database open 開啟資料庫
Beginning crash recovery of 1 threads 進行例項恢復
parallel recovery started with 2 processes 啟動2個恢復程式
Started redo scan
Completed redo scan
read 30 KB redo, 31 data blocks need recovery
Started redo application at
Thread 1: logseq 76, block 23074
Recovery of Online Redo Log: Thread 1 Group 1 Seq 76 Reading mem 0 從76號日誌為起始位置
Mem# 0: /u02/app/oracle/oradata/LEO1/redo01.log 應用redo日誌進行恢復
Completed redo application of 0.02MB
Completed crash recovery 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 26 09:50:08 2013
LGWR: STARTING ARCH PROCESSES 啟動歸檔程式進行歸檔
Fri Apr 26 09:50:08 2013
ARC0 started with pid=22, OS id=8396
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Fri Apr 26 09:50:09 2013
ARC1 started with pid=23, OS id=8400
Fri Apr 26 09:50:09 2013
ARC2 started with pid=24, OS id=8404
ARC1: Archival started
Fri Apr 26 09:50:09 2013
ARC3 started with pid=25, OS id=8408
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 77 (thread open)
Thread 1 opened at log sequence 77
Current log# 2 seq# 77 mem# 0: /u02/app/oracle/oradata/LEO1/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Apr 26 09:50:09 2013
SMON: enabling cache recovery SMON程式負責例項的恢復
Archived Log entry 4 added for thread 1 sequence 76 ID 0x64e13fb9 dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Fri Apr 26 09:50:14 2013
QMNC started with pid=26, OS id=8412
Completed: alter database open 到此我們完成資料庫的open動作，例項恢復完畢
Fri Apr 26 09:50:18 2013
Starting background process CJQ0
Fri Apr 26 09:50:18 2013
CJQ0 started with pid=30, OS id=8436
從alter日誌的流程上我們就可以看出，oracle例項恢復的內容過程是什麼樣的，通過以上案例我們即瞭解了控制檔案的恢復過程又瞭解了資料庫例項的恢復過程，可謂一舉兩得，大家好好的消化消化，休息一下，該上班工作啦：）
9）下了班我們繼續，上次講到了，使用copy方式來恢復控制檔案，下面再講一種使用備份集來恢復控制檔案的方法。
【參】Books-> Backup and Recovery Reference -> RESTORE 和 RECOVER 參考官方文件是個好習慣
SYS@LEO1>shutdown immediate 我們先關閉資料庫
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@leonarding1 LEO1]$ rm -rf control01.ctl 刪除控制檔案
SYS@LEO1>startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
ORA-00205: error in identifying control file, check alert log for more info
指定的控制檔案錯誤，檢查alert日誌獲取更多資訊
[oracle@leonarding1 trace]$ tail -20f alert_LEO1.log
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u02/app/oracle/oradata/LEO1/control01.ctl' 這寫著1號控制檔案丟失
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory 找不到這個檔案
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Apr 26 19:44:05 2013
Checker run found 1 new persistent data failures
SYS@LEO1>shutdown immediate 關庫
ORA-01507: database not mounted
ORACLE instance shut 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 to target database (not started) 庫沒有啟動
RMAN> startup nomount 把庫啟動到nomount狀態
Oracle instance started
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 150996424 bytes
Database Buffers 310378496 bytes
Redo Buffers 8241152 bytes
有一個地方容易出錯，大家都喜歡用這條語句來恢復
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
Starting restore at 26-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130426
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130425
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130424
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130423
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130422
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130421
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130420
channel ORA_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: no AUTOBACKUP found or specified handle is not a valid copy or piece
報錯：說找不到指定的備份集
RMAN> show all;
RMAN configuration parameters for database with db_unique_name LEO1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE 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 restore at 26-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u02/app/oracle/oradata/LEO1/control01.ctl
output file name=/u02/app/oracle/oradata/LEO1/control02.ctl
Finished restore at 26-APR-13
[oracle@leonarding1 LEO1]$ ll
total 2618136
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 20:17 control01.ctl
-rw-r----- 1 oracle asmadmin 9748480 Apr 26 20:17 control02.ctl
我們一起恢復了所有的控制檔案
RMAN> alter database mount; 現在資料庫可以正常載入了對吧
database mounted
released channel: ORA_DISK_1
那我們可以alter database open來開啟資料庫嗎，顯然是不行的，大家知道為什麼嗎？
你想想如果這個控制檔案是從10天之前的一個備份還原的與當前的資料庫物理結構能一致嗎！
顯然是不可以的，我們只有用備份在重新同步資料庫
RMAN> restore database; RMAN備份還原restore（複製）資料檔案
Starting restore at 26-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
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 /u02/app/oracle/oradata/LEO1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/LEO1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/LEO1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_02o81bpf_1_1
channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1 tag=TAG20130426T065022
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
Finished restore at 26-APR-13
RMAN> recover database; 應用redo日誌恢復recover（同步）資料庫
Starting recover at 26-APR-13
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 75 is already on disk as file /u02/app/oracle/oradata/LEO1/redo03.log
archived log for thread 1 with sequence 76 is already on disk as file /u02/app/oracle/oradata/LEO1/redo01.log
archived log for thread 1 with sequence 77 is already on disk as file /u02/app/oracle/oradata/LEO1/redo02.log
archived log file name=/u02/app/oracle/oradata/LEO1/redo03.log thread=1 sequence=75
archived log file name=/u02/app/oracle/oradata/LEO1/redo01.log thread=1 sequence=76
archived log file name=/u02/app/oracle/oradata/LEO1/redo02.log thread=1 sequence=77
media recovery complete, elapsed time: 00:00:14 這就是介質恢復，要把資料檔案同步到損壞的前一刻
因為這是不完全恢復，因此我們不能用alter database open來開啟資料庫
RMAN> alter database open resetlogs;
database opened
SYS@LEO1>select status from v$instance; 資料庫已開啟，可以正常使用
STATUS
------------
OPEN
小結：因為我們進行了不完全恢復，恢復到之前的某一點，現在資料庫以這點為一個新的起點（相當是一個煥然一新的庫），resetlogs就是重置歸檔日誌從1開始編碼，之前的歸檔全部無效。
SYS@LEO1>alter system switch logfile; 我們重新切換一次
System altered.
[oracle@leonarding1 oracle]$ cd archdata
[oracle@leonarding1 archdata]$ ll
total 14028
-rw-r----- 1 oracle asmadmin 222720 Apr 26 21:05 1_1_813790699.dbf
-rw-r----- 1 oracle asmadmin 5632 Apr 26 20:58 1_75_813654649.dbf
-rw-r----- 1 oracle asmadmin 11844608 Apr 26 20:58 1_76_813654649.dbf
-rw-r----- 1 oracle asmadmin 2284032 Apr 26 20:58 1_77_813654649.dbf
注意：歸檔日誌編碼重置了，從1開始了，75 76 77歸檔日誌全部無效了，為了不礙眼你可以刪除掉。
2.模擬狀態為inactive的日誌損壞的恢復實驗(完全恢復)
Redo log：是用於記錄資料庫所有變化資訊的檔案，只要有底層的塊變化就會產生資訊，當遇到需要恢復的場景，我們可以利用這些redo log進行例項恢復和介質恢復。Redo log日誌通常情況下都是分成若干組存在資料庫中，每個組都會有2個以上的成員互為映象（保證高可用），每個組迴圈切換使用。下面我們來看看redo log的恢復操作。
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 52428800 YES INACTIVE
2 1 52428800 NO CURRENT
3 1 52428800 YES UNUSED
現在有3個redo組，每個組只有一個成員，每個成員大小為50M，顯然這是不合理的，保證每個日誌檔案組中包含不少於一個日誌檔案成員，那麼我們現在可以新增一下，要記住哦如果在生產環境下，redo日誌組成員要放在不同的磁碟上，防止磁碟級損壞。
[oracle@leonarding1 LEO1]$ mkdir disk2 我們建立一個disk2目錄模擬第二塊磁碟
SYS@LEO1>alter database add logfile member
'/u02/app/oracle/oradata/LEO1/disk2/redo01_b.log' to group 1, 給組1新增成員
'/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log' to group 2, 給組2新增成員
'/u02/app/oracle/oradata/LEO1/disk2/redo03_b.log' to group 3; 給組3新增成員
2 3 4
Database altered.
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES UNUSED
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2 /u02/app/oracle/oradata/LEO1/redo02.log
3 /u02/app/oracle/oradata/LEO1/redo03.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log INVALID
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log INVALID
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log INVALID
6 rows selected.
Ok，日誌組成員新增完成，每個大小和原來成員一致50M，路徑放在disk2目錄上
SYS@LEO1>archive log list 已經是歸檔模式
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
我們做幾次日誌切換，把所有redo log 都使用一遍
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
我們切換了3次生成了3個歸檔日誌2 3 4號，下一次該歸檔的是5號
[oracle@leonarding1 archdata]$ ll
total 30708
-rw-r----- 1 oracle asmadmin 222720 Apr 26 21:05 1_1_813790699.dbf
-rw-r----- 1 oracle asmadmin 17069056 Apr 28 07:03 1_2_813790699.dbf
-rw-r----- 1 oracle asmadmin 1024 Apr 28 07:03 1_3_813790699.dbf
-rw-r----- 1 oracle asmadmin 3072 Apr 28 07:03 1_4_813790699.dbf
現在我們看到已經切換到組2為當前redo 組了，組1和組3已經全部歸檔
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
如果想把inactive的日誌恢復首先要了解日誌各種狀態含義
Current：Oracle當前正在使用的redo log，就是LGWR程式正在寫入的redo log，在例項恢復時會用到此日誌，這個redo log記錄著資料庫最後的SCN號。
Active：表示日誌是活動的但不是當前正在使用的redolog，active意味著checkpoint動作尚未完成（髒資料還沒有完全刷到磁碟上）or 歸檔模式下該日誌的內容還沒有完全歸檔，這兩種情況下都會讓日誌為active狀態，在例項恢復時也會用到此日誌檔案，因此該日誌檔案不能被覆蓋。
Inactive：表示日誌是不活動的，checkpoint動作已經完成，日誌內容已經完全歸檔，可以被後續redo資料覆蓋，例項恢復時不在需要，但在介質恢復時可能需要
Unused：表示日誌從未使用過，裡面沒有任何的舊資料，可能是剛剛新增的日誌 or resetlogs之後被重置的日誌。
Clearing：表示日誌在alter database clear logfile命令之後被重新建立為一個空日誌，原來日誌中的內容被全部清空，日誌被清空後狀態重置為unused。
Clearing_current：表示日誌在current狀態下被清空了，如果發生切換，在寫日誌檔案頭時會報一個I/O錯誤，一定要警惕這種狀態，可能會導致資料永久丟失。
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
現在是第一組和第三組為inactive，我們直接刪除第一組中的一個成員redo01.log
[oracle@leonarding1 LEO1]$ rm redo01.log 作業系統級別刪除
由於是不活動日誌此時資料庫不受影響會繼續執行，我們關庫再重啟看看會發生什麼
SYS@LEO1>shutdown immediate 關庫
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup 重啟
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log INVALID
2 /u02/app/oracle/oradata/LEO1/redo02.log
3 /u02/app/oracle/oradata/LEO1/redo03.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
6 rows selected.
SYS@LEO1>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/archdata
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
沒有報錯資訊，驗證奇蹟的時刻，按理說oracle在檢測到redolog日誌丟失的時候會啟動告警，實際上什麼也沒有發生，不科學，難道oracle沒有檢測到redolog丟失嘛？？？
Errors in file /u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_lgwr_10850.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/LEO1/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
非也非也，我們在alert日誌中發現了告警蹤跡“開啟組1成員失敗並且指出了失敗檔案路徑”，那為什麼在sqlplus中沒有告警資訊呢？？？從Oracle設計的理念上可以窺視出，它想讓資料庫儘可能的活下來，只要不需要的動作就可以先忽略掉（儘管已經檢測出來了，會在狀態上標註為invalid）。那麼ok，我們開始恢復
【參】Books->Administrator’s Guide -> 6 Managing the Redo Log ->Dropping Redo Log Groups and Members
SYS@LEO1>shutdown immediate 關庫
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup mount 啟動到mount狀態
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
剛才我們是從作業系統層面刪除的，但控制檔案中還保留了redolog資訊，我們在從資料庫層面刪除
SYS@LEO1>ALTER DATABASE DROP LOGFILE MEMBER '/u02/app/oracle/oradata/LEO1/redo01.log';
Database altered.
切記一定要先刪除，再建立否則會報檔案已經存在的錯誤：
ORA-01577: cannot add log file '/u02/app/oracle/oradata/LEO1/redo01.log' – file already part of database
SYS@LEO1>alter database add logfile member '/u02/app/oracle/oradata/LEO1/redo01.log' to group 1;
Database altered.
SYS@LEO1>alter database open; 開啟資料庫
Database altered.
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log INVALID
2 /u02/app/oracle/oradata/LEO1/redo02.log
3 /u02/app/oracle/oradata/LEO1/redo03.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log
怎麼還是顯示invalid無效呢，上面說過只要不需要就可以先忽略掉
6 rows selected.
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 2 52428800 YES INACTIVE
2 2 52428800 NO CURRENT
3 2 52428800 YES INACTIVE
我們做2次切換，把當前組切換為組1
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
這次是不是狀態變成正常的啦，用到了自動監測，到此我們模擬inactive日誌恢復完成
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2 /u02/app/oracle/oradata/LEO1/redo02.log
3 /u02/app/oracle/oradata/LEO1/redo03.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
3 /u02/app/oracle/oradata/LEO1/disk2/redo03_b.log
附加另一種方法，還可以先刪除損壞組，在增加新組的方式來恢復
SYS@LEO1>ALTER DATABASE DROP LOGFILE GROUP 3;
Database altered.
這個只是在控制檔案上刪除，你還需要在作業系統層面上刪除
[oracle@leonarding1 LEO1]$ rm redo03.log
[oracle@leonarding1 disk2]$ rm redo03_b.log
SYS@LEO1>ALTER DATABASE ADD LOGFILE GROUP 5 ('/u02/app/oracle/oradata/LEO1/redo05.log','/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log') SIZE 50M;
Database altered.
我們增加了第五組，狀態良好
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2 /u02/app/oracle/oradata/LEO1/redo02.log
5 /u02/app/oracle/oradata/LEO1/redo05.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
6 rows selected.
新新增的第五組為unused狀態，還沒有使用
SYS@LEO1>select group#,members,bytes,archived,status from v$log;
GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 2 52428800 NO CURRENT
2 2 52428800 YES INACTIVE
5 2 52428800 YES UNUSED
補充：使用alter database clear logfile n 命令恢復 inactive狀態損壞的日誌檔案
Clearing：表示日誌在alter database clear logfile命令之後被重新建立為一個空日誌，原來日誌中的內容被全部清空，日誌被清空後狀態重置為unused。
原理：這個方法的恢復原理就是把損壞的日誌檔案重新建立一次，建立成一個新的空日誌，表面上看相當於把日誌內容給清空了保證一致性，因為這種狀態的redolog已經歸檔完畢，裡面的資料已經沒有意義了，清空or覆蓋都不影響資料庫的執行。
舉例
SQL> alter
database clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
小結：在進行恢復redolog日誌之前一定要先掌握其狀態要領，瞭解redolog正處於什麼狀態，會不會丟失資料，根據其特點採用合理的恢復方案。
3.模擬狀態為active的日誌損壞的資料恢復實驗（不完全恢復）
首先我們先看看Active日誌特點
Active：表示日誌是活動的但不是當前正在使用的redolog，active意味著checkpoint動作尚未完成（髒資料還沒有完全刷到磁碟上）or 歸檔模式下該日誌的內容還沒有完全歸檔，這兩種情況下都會讓日誌為active狀態，在例項恢復時也會用到此日誌檔案，因此該日誌檔案不能被覆蓋。
Active和Current redolog日誌有一個共同之處就是例項恢復時會用到這個日誌檔案 ，它們都是checkpoint檢查點還沒有完成時保護資料安全的最後屏障，如果它們損壞or勿刪除了會導致資料的丟失，這是非常危險的，就算有RMAN備份也不能恢復current 狀態的資料。
有的朋友會很奇怪不是說有全庫備份就可以完事大吉了嘛！這是一種完美主義，沒有一種四海皆准的備份，如果你在RMAN備份時候仔細檢視了備份日誌就會發現一個驚奇的祕密，RMAN是不會備份
Redolog的，但會備份archivelog，redolog是用多路複用方式映象備份的，因此如果刪除了current 重做日誌那麼就會丟失資料，我們要警惕這種場景，做一名優秀的DBA！
注：基本上，如果是當前線上日誌受損壞，很難不丟資料。但最差的情況下是可以恢復到上一個可用的歸檔日誌時間點的。
恢復方法：
A 使用映象檔案來恢復，不會丟失資料
B 隱含引數_allow_resetlogs_corruption=TRUE 進行不一致性恢復，會丟失資料
C RMAN不完全恢復，有全備，有歸檔，可以保證資料的一致性，會丟失資料
###################################################################################
A 使用映象檔案來恢復，不會丟失資料
SYS@LEO1>select group#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
---------- ---------- ---------- --- ---------- ----------------
1 2 52428800 YES 7 INACTIVE
2 2 52428800 NO 9 CURRENT
5 2 52428800 YES 8 INACTIVE
第二組為當前正在使用的redo組
[oracle@leonarding1 LEO1]$ mv redo02.log redo02.log.bak 我們改個名字
SYS@LEO1>shutdown abort 我們強制關庫，按理說應該使用current log進行例項恢復，但current log備我們刪除了，連庫都起不來，如何恢復呢
SYS@LEO1>startup 啟動的時候是可以正常啟動的，但在告警日誌裡面會報錯
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
DDE: Problem Key 'ORA 313' was flood controlled (0x1) (no incident)
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00313: open failed for members of log group 2 of thread 1
我們很奇怪為什麼我們可以啟動呢？
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2 /u02/app/oracle/oradata/LEO1/redo02.log INVALID
5 /u02/app/oracle/oradata/LEO1/redo05.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
哦原來第二組有2個成員互為映象如果第一個成員不可用時oracle就會標記為invalid，把redo資料寫入到redo02_b.log第二個成員中繼續支援oracle正常執行
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>select group#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
---------- ---------- ---------- --- ---------- ----------------
1 2 52428800 YES 16 INACTIVE
2 2 52428800 NO 18 CURRENT
5 2 52428800 YES 17 ACTIVE
看還可以正常切換，正常使用，但目前只有一個成員可用，也是非常危險的，我們要恢復原狀
SYS@LEO1>alter database drop logfile member '/u02/app/oracle/oradata/LEO1/redo02.log';
Database altered.
SYS@LEO1>alter database add logfile member '/u02/app/oracle/oradata/LEO1/redo02.log' to group 2;
Database altered.
我們先刪除在建立，然後多切換幾次，redolog就恢復了
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/redo01.log
2 /u02/app/oracle/oradata/LEO1/redo02.log
5 /u02/app/oracle/oradata/LEO1/redo05.log
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
小結：使用映象檔案來恢復，不會丟失資料，因為沒有影響到oracle正常的執行
################################################################################
B 隱含引數_allow_resetlogs_corruption=true 進行不一致性恢復，會丟失資料
第二種方法，我把所有的current log全部改名，看看能否恢復
SYS@LEO1>shutdown abort 強制關庫
ORACLE instance shut down.
[oracle@leonarding1 LEO1]$ mv redo02.log redo02.log.bak 修改第一個成員
[oracle@leonarding1 disk2]$ mv redo02_b.log redo02_b.log.bak 修改第二個成員
SYS@LEO1>startup 啟動庫
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1 開啟redo日誌失敗
ORA-00312: online log 2 thread 1: '/u02/app/oracle/oradata/LEO1/redo02.log' 指明哪個日誌
ORA-27037: unable to obtain file status 獲取不到日誌狀態
Linux-x86_64 Error: 2: No such file or directory 因為沒有這個檔案
Additional information: 3
ORA-00312: online log 2 thread 1:
'/u02/app/oracle/oradata/LEO1/disk2/redo02_b.log' 第一個成員找不到我們看看第二個成員有沒有
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory 也是沒有這個檔案對吧
Additional information: 3
SYS@LEO1>select status from v$instance; 現在例項正在處於mount狀態
STATUS
------------
MOUNTED
我們可以使用隱含引數_allow_resetlogs_corruption=true 進行不一致恢復，這是oracle給予我們提供的一種臨時急救方法，一般不到萬不得已不建議使用，因為這樣會丟失資料的，而丟失資料是不能接受的。我們繼續
SYS@LEO1>alter system set "_allow_resetlogs_corruption"=true scope=spfile; 靜態修改需重啟生效
System altered.
SYS@LEO1>shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@LEO1>startup mount 啟動到mount狀態
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
SYS@LEO1>recover database until cancel;
until cancel：一直恢復到資料庫能夠恢復的最後一個日誌，盡最大努力恢復
場景：current/active log有丟失情況下或者有歸檔日誌丟失的情況下，一直可恢復到丟失前的最後一個日誌，則中止。
SYS@LEO1>recover database until cancel;
ORA-00279: change 1056653 generated at 04/30/2013 08:35:09 needed for thread 1
ORA-00289: suggestion : /u02/app/oracle/archdata/1_19_813790699.dbf
ORA-00280: change 1056653 for thread 1 is in sequence #19
Specify log: {
cancel 這時需手工輸入cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u02/app/oracle/oradata/LEO1/system01.dbf'
需要resetlogs方式開打資料庫，也就是非一致性開啟
Resetlogs做的幾件事：
1）資料檔案頭scn號為準，同步控制檔案和線上日誌檔案scn號
2）重新建立redolog日誌（建立一個空日誌），重置為unused
3）重置歸檔日誌序號從1開始編碼
-rw-r----- 1 oracle asmadmin 222720 Apr 26 21:05 1_1_813790699.dbf
-rw-r----- 1 oracle asmadmin 7168 Apr 30 10:25 1_1_814098124.dbf
4）讓資料庫重新進入一個新的生命週期
SYS@LEO1>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error 當前會話被終止
ORA-00600: internal error code, arguments: [2662], [0], [1056672], [0],
[1056735], [12583120], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [2662], [0], [1056671], [0],
[1056735], [12583120], [], [], [], [], [], []
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1056668], [0],
[1056735], [12583120], [], [], [], [], [], []
Process ID: 9651
Session ID: 125 Serial number: 5
SYS@LEO1>startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
Database opened.
SYS@LEO1>select status from v$instance;
STATUS
------------
OPEN
SYS@LEO1>col member for a50
SYS@LEO1>select group#,member,status from v$logfile;
GROUP# MEMBER STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/LEO1/disk2/redo01_b.log
2 /u02/app/oracle/oradata/LEO1/disk2/redo02_b.log
5 /u02/app/oracle/oradata/LEO1/redo05.log
1 /u02/app/oracle/oradata/LEO1/redo01.log
2 /u02/app/oracle/oradata/LEO1/redo02.log
5 /u02/app/oracle/oradata/LEO1/disk2/redo05_b.log
6 rows selected.
SYS@LEO1>select group#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
---------- ---------- ---------- --- ---------- ----------------
1 2 52428800 YES 1 INACTIVE
2 2 52428800 NO 2 CURRENT
5 2 52428800 YES 0 UNUSED
小結：這種方式開啟後的資料庫要立刻全備一次，之前的備份已經無效，保證資料的安全性
C RMAN不完全恢復，有全備，有歸檔，可以保證資料的一致性，會丟失資料，這種方法我們在下面的實驗中證明
4.假設在有最後一次全庫備份之後，你誤刪除了一張表，請使用備份+歸檔來將資料庫恢復到刪除表之前的狀態(不完全恢復)
進入Rman做全庫備份
[oracle@leonarding1 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 30 11:08:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEO1 (DBID=1692458681)
RMAN> backup 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;
2> 3> 4>
Starting backup at 30-APR-13
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 04/30/2013 11:13:27
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u02/app/oracle/archdata/1_75_813654649.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
75號歸檔日誌缺失，Rman在作業系統上找不到對應的日誌檔案
當手工刪除了歸檔日誌以後，Rman備份會檢測到日誌缺失，從而無法進一步繼續執行。
所以此時需要手工執行crosscheck過程，之後Rman備份可以恢復正常
RMAN> crosscheck archivelog all; 交叉檢查，有3個日誌被手工刪除
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
validation failed for archived log
archived log file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9 STAMP=813790708
validation failed for archived log
archived log file name=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7 STAMP=813790702
validation failed for archived log
archived log file name=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8 STAMP=813790706
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206
validation succeeded for archived log
archived log file name=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824
Crosschecked 27 objects
RMAN> delete expired archivelog all; 刪除所有過期歸檔日誌
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
List of Archived Log Copies for database with db_unique_name LEO1
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
9 1 75 X 26-APR-13
Name: /u02/app/oracle/archdata/1_75_813654649.dbf
7 1 76 X 26-APR-13
Name: /u02/app/oracle/archdata/1_76_813654649.dbf
8 1 77 X 26-APR-13
Name: /u02/app/oracle/archdata/1_77_813654649.dbf
Do you really want to delete the above objects (enter YES or NO)? y 75 76 77號日誌被刪除
deleted archived log
archived log file name=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=9 STAMP=813790708
deleted archived log
archived log file name=/u02/app/oracle/archdata/1_76_813654649.dbf RECID=7 STAMP=813790702
deleted archived log
archived log file name=/u02/app/oracle/archdata/1_77_813654649.dbf RECID=8 STAMP=813790706
Deleted 3 EXPIRED objects
RMAN> backup full database format 現在Rman備份可以恢復正常，同時刪除備份過的歸檔日誌
'/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;2> 3> 4>
Starting backup at 30-APR-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=10 STAMP=813791152
input archived log thread=1 sequence=2 RECID=11 STAMP=813913400
input archived log thread=1 sequence=3 RECID=12 STAMP=813913401
input archived log thread=1 sequence=4 RECID=13 STAMP=813913405
input archived log thread=1 sequence=5 RECID=14 STAMP=813933703
input archived log thread=1 sequence=6 RECID=15 STAMP=813933734
input archived log thread=1 sequence=7 RECID=16 STAMP=814090435
input archived log thread=1 sequence=8 RECID=17 STAMP=814090645
input archived log thread=1 sequence=9 RECID=18 STAMP=814090877
input archived log thread=1 sequence=10 RECID=19 STAMP=814091008
input archived log thread=1 sequence=11 RECID=20 STAMP=814091028
input archived log thread=1 sequence=12 RECID=21 STAMP=814091032
input archived log thread=1 sequence=13 RECID=22 STAMP=814091036
input archived log thread=1 sequence=14 RECID=23 STAMP=814091057
input archived log thread=1 sequence=15 RECID=24 STAMP=814091184
input archived log thread=1 sequence=16 RECID=25 STAMP=814091638
input archived log thread=1 sequence=17 RECID=26 STAMP=814091658
input archived log thread=1 sequence=18 RECID=27 STAMP=814091709
input archived log thread=1 sequence=19 RECID=28 STAMP=814092394
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0co8cds4112.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_1_813790699.dbf RECID=10 STAMP=813791152
archived log file name=/u02/app/oracle/archdata/1_2_813790699.dbf RECID=11 STAMP=813913400
archived log file name=/u02/app/oracle/archdata/1_3_813790699.dbf RECID=12 STAMP=813913401
archived log file name=/u02/app/oracle/archdata/1_4_813790699.dbf RECID=13 STAMP=813913405
archived log file name=/u02/app/oracle/archdata/1_5_813790699.dbf RECID=14 STAMP=813933703
archived log file name=/u02/app/oracle/archdata/1_6_813790699.dbf RECID=15 STAMP=813933734
archived log file name=/u02/app/oracle/archdata/1_7_813790699.dbf RECID=16 STAMP=814090435
archived log file name=/u02/app/oracle/archdata/1_8_813790699.dbf RECID=17 STAMP=814090645
archived log file name=/u02/app/oracle/archdata/1_9_813790699.dbf RECID=18 STAMP=814090877
archived log file name=/u02/app/oracle/archdata/1_10_813790699.dbf RECID=19 STAMP=814091008
archived log file name=/u02/app/oracle/archdata/1_11_813790699.dbf RECID=20 STAMP=814091028
archived log file name=/u02/app/oracle/archdata/1_12_813790699.dbf RECID=21 STAMP=814091032
archived log file name=/u02/app/oracle/archdata/1_13_813790699.dbf RECID=22 STAMP=814091036
archived log file name=/u02/app/oracle/archdata/1_14_813790699.dbf RECID=23 STAMP=814091057
archived log file name=/u02/app/oracle/archdata/1_15_813790699.dbf RECID=24 STAMP=814091184
archived log file name=/u02/app/oracle/archdata/1_16_813790699.dbf RECID=25 STAMP=814091638
archived log file name=/u02/app/oracle/archdata/1_17_813790699.dbf RECID=26 STAMP=814091658
archived log file name=/u02/app/oracle/archdata/1_18_813790699.dbf RECID=27 STAMP=814091709
archived log file name=/u02/app/oracle/archdata/1_19_813790699.dbf RECID=28 STAMP=814092394
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=30 STAMP=814098353
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0do8cdsc113.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_1_814098124.dbf RECID=30 STAMP=814098353
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=29 STAMP=814092405
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0eo8cdsd114.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_20_813790699.dbf RECID=29 STAMP=814092405
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=31 STAMP=814100979
input archived log thread=1 sequence=3 RECID=32 STAMP=814101206
input archived log thread=1 sequence=4 RECID=33 STAMP=814101824
input archived log thread=1 sequence=5 RECID=34 STAMP=814102404
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0fo8cdse115.rmn tag=TAG20130430T113324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_2_814098124.dbf RECID=31 STAMP=814100979
archived log file name=/u02/app/oracle/archdata/1_3_814098124.dbf RECID=32 STAMP=814101206
archived log file name=/u02/app/oracle/archdata/1_4_814098124.dbf RECID=33 STAMP=814101824
archived log file name=/u02/app/oracle/archdata/1_5_814098124.dbf RECID=34 STAMP=814102404
Finished backup at 30-APR-13
Starting backup at 30-APR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf
input datafile file number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn tag=TAG20130430T113336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/full_bk1_0ho8cdv5117.rmn tag=TAG20130430T113336 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-APR-13
Starting backup at 30-APR-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=35 STAMP=814102503
channel ORA_DISK_1: starting piece 1 at 30-APR-13
channel ORA_DISK_1: finished piece 1 at 30-APR-13
piece handle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn tag=TAG20130430T113503 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/app/oracle/archdata/1_6_814098124.dbf RECID=35 STAMP=814102503
Finished backup at 30-APR-13
Starting Control File and SPFILE Autobackup at 30-APR-13
piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130430-01 comment=NONE
Finished Control File and SPFILE Autobackup at 30-APR-13
我們現在進入資料庫建立一個表leo1並插入三條記錄
SYS@LEO1>create table leo1 (name varchar2(20),age number,riqi date);
Table created.
SYS@LEO1>insert into leo1 values('leonarding',28,sysdate);
1 row created.
SYS@LEO1>insert into leo1 values('sun_vn',26,sysdate);
1 row created.
SYS@LEO1>insert into leo1 values('tiger',18,sysdate);
1 row created.
SYS@LEO1>commit;
Commit complete.
SYS@LEO1>select * from leo1; 完成
NAME AGE RIQI
-------------------- ---------- ---------
leonarding 28 30-APR-13
sun_vn 26 30-APR-13
tiger 18 30-APR-13
SYS@LEO1>select group#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
---------- ---------- ---------- --- ---------- ----------------
1 2 52428800 NO 7 CURRENT
2 2 52428800 YES 5 INACTIVE
5 2 52428800 YES 6 INACTIVE
我們建立表和插入資訊是寫入當前日誌組1
SYS@LEO1>alter system switch logfile; 切換日誌組
System altered.
SYS@LEO1>alter system switch logfile;
System altered.
SYS@LEO1>select group#,members,bytes,archived,sequence#,status from v$log;
GROUP# MEMBERS BYTES ARC SEQUENCE# STATUS
---------- ---------- ---------- --- ---------- ----------------
1 2 52428800 YES 7 INACTIVE
2 2 52428800 YES 8 INACTIVE
5 2 52428800 NO 9 CURRENT
現在組1已經完成歸檔，如果我們此時勿刪除了leo1表，我們可以把資料庫恢復到7號歸檔日誌的那一點，就可以恢復勿刪除的leo1表了。我們順便把當前redolog日誌也損壞了，看看可不可以扶起資料庫來。
SYS@LEO1>drop table leo1 purge; 刪除表
我們刪除第五組的2個成員
[oracle@leonarding1 LEO1]$ rm -rf redo05.log
[oracle@leonarding1 LEO1]$ cd disk2/
[oracle@leonarding1 disk2]$ rm -rf redo05_b.log
SYS@LEO1>startup
ORACLE instance started.
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 171967944 bytes
Database Buffers 289406976 bytes
Redo Buffers 8241152 bytes
Database mounted.
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1:
'/u02/app/oracle/oradata/LEO1/disk2/redo05_b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 1: '/u02/app/oracle/oradata/LEO1/redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
找不到第五組redo，實際上已經被我們刪除了，進入rman進行恢復操作
[oracle@leonarding1 backup]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 30 12:47:55 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEO1 (DBID=1692458681, not open)
RMAN> restore database;
Starting restore at 30-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
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 /u02/app/oracle/oradata/LEO1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/LEO1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/LEO1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/LEO1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn
channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/full_bk1_0go8cdsg116.rmn tag=TAG20130430T113336
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 30-APR-13
我們只需恢復到7號歸檔日誌狀態點，就可以找回我們刪除了的leo1表
RMAN> recover database until sequence 7 thread 1;
Starting recover at 30-APR-13
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=6
channel ORA_DISK_1: reading from backup piece /u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn
channel ORA_DISK_1: piece handle=/u02/app/oracle/backup/arch_bk1_0io8cdv7118.rmn tag=TAG20130430T113503
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/archdata/1_6_814098124.dbf thread=1 sequence=6
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-APR-13
SYS@LEO1>alter database open resetlogs;
Database altered.
resetlogs做的幾件事：
1）資料檔案頭scn號為準，同步控制檔案和線上日誌檔案scn號
2）重新建立redolog日誌（建立一個空日誌），重置為unused
3）重置歸檔日誌序號從1開始編碼
4）讓資料庫重新進入一個新的生命週期
SYS@LEO1>select * from leo1;
NAME AGE RIQI
-------------------- ---------- ---------
leonarding 28 30-APR-13
sun_vn 26 30-APR-13
tiger 18 30-APR-13
這個表存在說明應用了 sequence為7的歸檔
小結：到此我們的系列恢復實驗完美完成，裡面闡述了各種不同環境下的恢復測試，簡明瞭重做日誌和歸檔日誌關係，在什麼情況下可以恢復到什麼級別，牢實掌握如上資訊，可以讓你遇事不慌張事半功倍。
控制檔案恢復，重做日誌恢復，完全恢復，不完全恢復，備份
劉盛Leonarding
2013.4.30
天津&spring
分享技術~成就夢想
Blog：www.leonarding.com
##########################################################################################
如果喜歡我的文章就請掃下面二維碼吧！關注微訊號：leonarding_public
在這裡你能得到技術、實事、熱點訊息等新興事物的思考和觀點，別的地方可能沒有的東西。我將為大家提供最新技術與資訊動態，傳遞正能量。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-1475577/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復：polardb資料庫備份與恢復資料庫
- RAC備份恢復之Voting備份與恢復
- Oracle 備份 與 恢復 概述Oracle
- Mysql資料備份與恢復MySql
- 備份與恢復oracle_homeOracle
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- OceanBase物理備份恢復實踐
- 資料庫備份恢復資料庫
- RabbitMQ如何備份與恢復資料MQ
- postgresql備份與恢復資料庫SQL資料庫
- Oracle 備份恢復之 FlashbackOracle
- ORACLE備份&恢復案例(轉)Oracle
- Jenkins備份與恢復Jenkins
- Postgresql 備份與恢復SQL
- MySQL 備份與恢復MySql
- Oracle 12c 備份與恢復Oracle
- Mysql備份與恢復(1)---物理備份MySql
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- MySQL備份與恢復——實操MySql
- MySQL 非常規恢復與物理備份恢復MySql
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- 資料庫備份與恢復技術資料庫
- oracle 增量備份恢復驗證Oracle
- Oracle 備份和恢復介紹Oracle
- ORACLE備份&恢復案例三(轉)Oracle
- ORACLE備份&恢復案例二(轉)Oracle
- ORACLE本地磁碟備份恢復Oracle
- Mysql備份與恢復(2)---邏輯備份MySql
- mydumper備份恢復
- Mysql備份恢復MySql
- 備份和恢復
- DB的備份與恢復
- GitLab的備份與恢復Gitlab
- GitLab的自動備份、清理備份與恢復Gitlab
- MySQL-19.資料庫備份與恢復MySql資料庫
- Mysql資料庫備份及恢復MySql資料庫
- 達夢資料庫備份恢復資料庫
- gitlab的資料備份和恢復Gitlab
- 磁碟資料恢復及備份工具資料恢復