[轉載] Oracle blockrecover limits

Oracle blockrecover limits

Ref: http://dongyongjun.blog.sohu.com/258775237.html

BLOCKRECOVER 的使用限制:

1、資料庫必須在ARCHIVELOG模式下才可以使用這個功能;

2、資料庫必須是mount或open狀態;

3、不能對一個offline的datafile進行BLOCKRECOVER;

4、blockrecover需要通過一個full backups 恢復data block,不能使用incremental backups進行錯誤恢復;

5、blockrecover不能跨越archivelog丟失或損壞,也就是說在恢復的過程中,不能丟失需要的歸檔日誌;

6、blockrecover不能恢復邏輯壞塊,邏輯壞塊必須首先restore損壞的資料檔案,然後在用recover進行恢復;

7、blockrecvoer不能恢復file header block.(block=1).

-----上網找了一下關於官方文件 :  (10.2) Restrictions and Usage Notes

You must have a full backup of the file containing the corrupt blocks: block media recovery cannot use incremental backups.

==>必須要有一次的全備份

If RMAN fails to access a specific archived redo log file needed for block media recovery, it performs restore failover, trying all other backups listed in the RMAN repository that are suitable for use in this operation, and only fails if no suitable backup is available. See Oracle Database Backup and Recovery Advanced User's Guide for details on restore failover.

==>在修復的過程中, 不能loss掉需要的archive log, 如果有壞掉的archivelog, RMAN會嘗試找尋可用的incremental backup, 來確保恢復能正常進行下去

Block media recovery cannot survive a missing or inaccessible archived log, although it can sometimes survive missing or inaccessible records (Oracle Database Backup and Recovery Advanced User's Guide).
==>在修復的過程中, 不能loss掉需要的archive log

The datafile header block (block 1) cannot be recovered.

==>datafile header是無法被修復的

You cannot perform block media recovery in NOARCHIVELOG mode.

==>必須在archivelog mode

-----

這邊再補充一點, 

Oracle 11g 如果要透過建議的方式來修復corrupted block,

1.只能在single instance 上面使用

list failure; 

advise failure; 

repair failure; 

2.要更方便修復的話, 可以用舊的方法

2.1 讓rman檢查有哪些壞塊

backup validate database;

2.2 找出有哪些壞掉的block

select * from V$DATABASE_BLOCK_CORRUPTION; 

2.3 修復之

blockrecover datafile 6 block 10, 42, 34, 50, 26;

or

BLOCKRECOVER CORRUPTION LIST;

[心得] Oracle 10g RMAN Image Copy 功能...

Oracle 10g RMAN Image Copy 功能...
RMAN backup有兩種方式，一種是備份backupset/ 一種是備份image copy ,

一般來說rman如果備份資料庫多久, 還原資料庫也會需要相對應的時間

而Oracle 10g以後的new feature : 
rman image copy讓資料庫在第一次備份時,

直接對datafile 做全備份, 備份每一個datafile的結構到recovery area空間去, (傳統使用打包的方式把多個datafile 包成一個backupset)

而在做了第二次incremental 備份的時候, 在這期間異動過的blocks(between full backup time and incremental backup time), 就被直接疊加到full image copy file上面去…

Incremental updated backups exampe :
RMAN> recover copy of database with tag 'daily_inc';
RMAN> backup incremental level 1 for recover of copy with tag 'daily_inc' database;

合併full + incremental Level 0 的image copy
1.如果沒有level 0, level 1, 那麼就沒有效果
2.如果第一次做level 1, 則效果等同於level 1
--

於災害發生的時候, DBA 就可以讓instance mount, 並讀寫最新的image backup, 
alter database datafile xx offline;
switch database to copy;
recover database;
or
alter database datafile xx offlline;
switch datfile 1 to copy...
switch datafile 2 to copy....
recover datafile 1;
recover datafile 2;
.
.
.
再搭配set untile time …. ;  
recover database快速開起來這些檔案,

讓資料庫直接把備份檔案當作datafile來recovery...(當然要先還原 control file才可以做)

如此就降低了資料庫還原所需要的時間/ MTTR(Mean Time To Repair), 減少資料庫down time, 最終也增加了服務的可用性.

最後小提醒,
1.RMAN備份要加速的話, 最好要enable RMAN的 block change tracking feature...與設定CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY;

2.RMAN 還原要加速的話, 可以善用image copy的還原方式,

3.但是image copy 會使用copy的方式來做備份, 如果datafile 有1TB, 但只用了10mb, 也是會需要等1TB的檔案備完, 所以使用image copy的方式, 等於是用備份空間/ 時間換取還原的效率

4.@2017/12/1 update
發現也可以對Image Copy的備份方式進行壓縮, 其中的含意除了壓縮以外, 還會備份僅有用到的data blocks指令如下
  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 FOR RECOVER OF COPY...

--

Ref:
http://www.snapdba.com/2013/03/incrementally-updated-image-copy-backups-with-rman/

http://dba-tips.blogspot.tw/2011/11/switch-database-to-rman-copy-backup-and.html

http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17recovery-087778.html
https://oracle-base.com/articles/misc/incrementally-updated-image-copy-backups

[RMAN] list backup of archivelog all 與 list archivelog all 的差別

RMAN>  list backup of archivelog all;  ==> 會列出backuppiece 與archive logs

不要小看此指令, 當backupset 的檔名亂取時, 判斷arc log 資訊就是靠它

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -----------------

2       54.22M     DISK        00:00:00     20140726 01:32:53

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: BACKUP_ORCL_000001_072614013149

        Piece Name: /oracle/db102/dbs/03peari5_1_1

  List of Archived Logs in backup set 2

  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time

  ---- ------- ---------- ----------------- ---------- ---------

  1    31      426604     20140726 01:11:00 441429     20140726 01:15:19

  1    32      441429     20140726 01:15:19 444127     20140726 01:32:52

RMAN> list archivelog all;

List of Archived Log Copies

Key     Thrd Seq     S Low Time          Name

------- ---- ------- - ----------------- ----

1       1    31      A 20140726 01:11:00 /oracle/arch/1_31_853894793.dbf

2       1    32      A 20140726 01:15:19 /oracle/arch/1_32_853894793.dbf

RMAN>

RMAN recover時 資料庫居然跟我要 兩天前的archive log

最近在還原資料庫時居然發生此問題
百思不得其解
正常的RMAN recover 只會需要 Backup window期間所有的archive log 才對

直到看到這篇文章提到了...
RMAN was requesting archived logs that were generated almost a week back

某位路人說 The controlfile is may be older and datafiles are newer

是跟我類似的情境耶, 忽然恍然大悟...

原來rman script 中的channel 1 是做bkset 備份 , 做八個小時.
                                  channel 2 是做bkset , archive log 備份, 而channel 2 做了將近三天
如此則會導致還原時~ 遇到的問題一再發生...
下次如果要還原時確定一下control file的時間到底為幾號備的
  或是使用以下方法

SQL>
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL TIME 'YYYY-MM-DD:HH24:MI:SS';

以上的效果為: When Oracle starts recovery, it looks at the datafiles to know the last time a checkpoint was performed on the datafile. Oracle now knows to start applying recovery to the datafile for all SCNs after the SCN in the datafile header

RMAN 還原如何開啟debug mode

Syntax:

%rman target / log rman.log trace rman.trc debug

今天測試RMAN還原controlfile 時遇到ORA-27191: sbtinfo2 returned error ,

建議可以開啟debug mode 去測試

#for Veritas Netbackup

rman target / nocatalog log rman.log append trace rman.trc debug <<EOF

set DBID=1234567890

run {

allocate channel ch00 type 'sbt_tape'; 

SEND 'NB_ORA_SERV=serv1,NB_ORA_CLIENT=dbtest';

  restore controlfile to '/restore/oradata/orcl/control_01_ctl'

from 'cntrl_1344_1_123476561';

}

EOF

Oracle 如何用RMAN 註冊backuppiece (檔案路徑有修改)

最近與原廠交流學到的恢復技巧 先留下來mark先 ...當然其中太多小眉角...等有空來測試一下.

特殊RMAN回復技巧(Oracle 10g 以上)

1.透過RMAN部分回復datafiles(DB : mount 狀態)

  1.1 刪除所有catalog 的backupset from controlfile.
      --如何查詢 : list backup;

      SQL>change backuppiece piece_num# uncatalog;

  1.2 手動註冊備份檔, 僅註冊想要的備份(最常使用在異機恢復, 並非所有RMAN備份檔都在異機 , 所以手動把最新備份註冊到control file 去 , 假設使用舊的control file , 就不會有新的備份資料資訊 , 假設使用最新的control file 則不會有此問題 , 只要設定recover until 條件就可順利恢復 )

  # Catalog specific backup piece.

  CATALOG BACKUPPIECE '/backup/MYSID/01dmsbj4_1_1.bcp';

  或

  # Catalog all files and the contents of directories which

  # begin with the pattern "/backup/MYSID/arch".

  CATALOG START WITH '/backup/MYSID/arch';

2.透過RMAN回復僅需要的archive logs(異機空間不足的前提下 , 用此方法很方便)

rman log=restore_arch.log << EOF

connect target /

run

{

set archivelog destination to '/home/oracle/arch';

restore archivelog from sequence 189713 until sequence 189810 thread 1;

restore archivelog from sequence 217940 until sequence 218057 thread 2;

}

EOF

---
For Oracle 9i  http://docs.oracle.com/cd/B10501_01/server.920/a96565/rcmsynta11.htm#1005792

Examples

Cataloging an Archived Redo Log: Example

This example assumes that you made operating system copies of archived logs or transferred them from another location, and then added them to the RMAN repository:

CATALOG ARCHIVELOG '?/oradata/archive1_30.dbf', '?/oradata/archive1_31.dbf', 
                   '?/oradata/archive1_32.dbf';

Cataloging a File Copy as an Incremental Backup: Example

The following example catalogs datafile copy users01.bak as an incremental level 0 backup:

CATALOG DATAFILECOPY '?/oradata/users01.bak' LEVEL 0;

Note that you can create datafile copies both with the RMAN COPY command and by using operating system utilities in conjunction with ALTER TABLESPACE BEGIN/END BACKUP.

[RMAN] Check archive log status in v$archived_log view

SQL>

select name , deleted , status from gv$archived_log;

如果DELETED=YES , Status=D 則代表已經被RMAN備份 且刪除

如果DELETED=NO , Status=A 則代表還未被RMAN備份 且會占用ASM/OS 空間.

SQL>

--檢查那些archive log 是否還沒被RMAN刪除 ( 兩天以前)

select name , deleted , status from gv$archived_log where FIRST_TIME < sysdate -2 and DELETED=NO ;