【備份恢復】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

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

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

我們很奇怪為什麼我們可以啟動呢？

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: {=suggested | filename | AUTO | CANCEL}

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

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