rman 還原歸檔日誌(restore archivelog

 聽說過還原(restore)資料庫，表空間及資料庫檔案，使用歸檔日誌恢復(recover)資料庫，表空間，資料庫檔案。咦，還有還原歸檔日誌這一說法呢？沒錯，可能我們忽略了還原歸檔日誌這一個過程，原因是還原歸檔日誌通常情況下是oracle在recover時自動完成的。大多數情況下我們是先還原資料庫，恢復資料庫，開啟資料庫。實際上在恢復資料庫之前有一個動作，那就是還原歸檔日誌，也就是將日誌檔案還原到預設的歸檔位置，如果我們在備份歸檔日誌時使用了delete [all] input子句的話。本文對此給出了單獨還原歸檔日誌以及恢復歸檔日誌的示例以及restore archivelog的一些用法，僅僅是為了更好來的理解還原與恢復的過程，因為大多數情形下,資料檔案被還原到預設路徑。如果是還原到非預設路徑，那就需要手動restore archivelog。

1、理解還原與恢復     還原(restore): 還原指將資料檔案(可能受損)用之前的備份來替代或者複製到新的路徑，這個是大多數情形和通常的說法。     恢復(recover): 將備份之後的歸檔日誌apply到資料庫，也就根據歸檔日誌的事務將資料庫重新整理到特定或最新狀態(通常在還原之後操作)。對於歸           檔日誌中那些已提交的事務進行前滾，未提交的事務進行回滾。     還原歸檔日誌: 還原歸檔日誌是位於還原資料庫與恢復資料庫之間的這麼一個過程。它會將那些在備份歸檔日誌時使用delete [all] input方式           刪除的歸檔日誌還原到預設的歸檔位置。在還原資料庫之後，如果要做recover，也就是作介質恢復那就需要用到歸檔日誌。那還原之後進行           recover需要的歸檔日誌在哪裡呢？歸檔日誌在指定的歸檔路徑那裡，那到底有沒有呢？如果有，還原時出現提示，歸檔日誌已經在指定位置。           如果沒有，但是備份的歸檔備份集那裡有，也行啊。備份集裡包含備份片，也就是打包了歸檔日誌。那既然打包就要解包，解包到預設路徑           或指定路徑。這就是還原歸檔日誌。

 2、示例演示還原歸檔日誌--演示環境
--為了較好的模擬還原歸檔日誌,我們僅僅使用了一個特定的資料檔案進行copy方式備份，然後備份歸檔日誌(備份時刪除歸檔日誌)
--接下來破壞資料檔案，還原資料檔案，還原歸檔日誌檔案，恢復日誌檔案。
[oracle@linux3 ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m

[oracle@linux3 ~]$ sqlplus -V

SQL*Plus: Release 11.2.0.1.0 Production

a、備份資料檔案及歸檔日誌
RMAN> list backup of archivelog all;   --->列出當前資料庫已經備份的歸檔日誌

specification does not match any backup in the repository

RMAN> list backupset;                  --->列出當前資料庫已存在的備份集

specification does not match any backup in the repository
 
SQL> select username,default_tablespace from dba_users where username='SCOTT';  -->檢視使用者scott所在的表空間及資料檔案

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT                          USERS

SQL> select name,file# from v$datafile where tablespace_name='USERS';

NAME                                                              FILE#
------------------------------------------------------------ ----------
/u01/database/sybo3/oradata/users01.dbf                               4

SQL> conn scott/tiger;
Connected.
SQL> select name,sequence#,status,COMPLETION_TIME from v$archived_log where status='A';  -->當前系統無任何歸檔日誌

no rows selected

SQL> host;

RMAN> copy datafile 4 to '/u01/database/sybo3/fra/SYBO3/backupset/2013_07_26/users01.dbf';  -->使用rman copy方式備份資料檔案

RMAN> list copy;

using target database control file instead of recovery catalog
specification does not match any control file copy in the repository
specification does not match any archived log in the repository
List of Datafile Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time          
------- ---- - ------------------- ---------- -------------------
3       4    A 2013/07/26 20:10:31 961662     2013/07/26 20:10:31
        Name: /u01/database/sybo3/fra/SYBO3/backupset/2013_07_26/users01.dbf
        Tag: TAG20130726T201031

-->準備測試表用於驗證還原恢復是否成功       
SQL> create table t1 (seq varchar2(10),who varchar2(20));

SQL> insert into t1 select 'First','Robin' from dual;

SQL> commit;

SQL> alter system archive log current;   -->產生歸檔日誌

SQL> select name,sequence#,status,COMPLETION_TIME from v$archived_log where status='A';

NAME                                                                              SEQUENCE# S COMPLETION_TIME
-------------------------------------------------------------------------------- ---------- - -----------------
/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_27_8z4sy5ft_.arc             27 A 20130726 20:12:53

SQL> insert into t1 select 'Second','Robinson' from dual;

SQL> commit;

SQL> alter system archive log current;   -->再次產生歸檔日誌

SQL> select name,sequence#,status,COMPLETION_TIME from v$archived_log where status='A';

NAME                                                                              SEQUENCE# S COMPLETION_TIME
-------------------------------------------------------------------------------- ---------- - -----------------
/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_27_8z4sy5ft_.arc             27 A 20130726 20:12:53
/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_28_8z4t1q0s_.arc             28 A 20130726 20:14:47

-->下面備份歸檔日誌並刪除已備份的歸當日志
RMAN> backup archivelog all delete input;

Starting backup at 2013/07/26 20:16:39
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
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=27 RECID=23 STAMP=821823173
input archived log thread=1 sequence=28 RECID=24 STAMP=821823287
input archived log thread=1 sequence=29 RECID=25 STAMP=821823400
channel ORA_DISK_1: starting piece 1 at 2013/07/26 20:16:40
channel ORA_DISK_1: finished piece 1 at 2013/07/26 20:16:41
piece handle=/u01/database/sybo3/fra/SYBO3/backupset/2013_07_26/o1_mf_annnn_TAG20130726T201640_8z4t58tn_.bkp tag=TAG20130726T201640 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=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_27_8z4sy5ft_.arc RECID=23 STAMP=821823173
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_28_8z4t1q0s_.arc RECID=24 STAMP=821823287
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_29_8z4t585k_.arc RECID=25 STAMP=821823400
Finished backup at 2013/07/26 20:16:41

b、模擬破壞資料檔案
SQL> insert into t1 select 'Last','End of test' from dual;

SQL> commit;

SQL> ho cat /dev/null>/u01/database/sybo3/oradata/users01.dbf   --->破壞資料檔案

SQL> select * from t1;     --->此時buffer cache依舊可以查詢到資料

SEQ        WHO
---------- --------------------
First      Robin
Second     Robinson
Last       End of test

SQL> alter system checkpoint;  --->實施檢查點程式

System altered.

SQL> select * from t1;         --->此時資料檔案不可訪問
select * from t1
              *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/u01/database/sybo3/oradata/users01.dbf'

SQL> select * from v$recover_file;
select * from v$recover_file
              *
ERROR at line 1:
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: '/u01/database/sybo3/oradata/users01.dbf'

c、還原與恢復受損的資料檔案
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name='USERS'; --->tablespace 依舊是Online

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          ONLINE

SQL> alter tablespace users offline immediate;   --->offline受損的tablespace

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';  --->此時狀態為offline

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          OFFLINE

RMAN> restore datafile 4;   --->此時使用restore datafile 方式提示失敗

Starting restore at 2013/07/26 20:30:20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/26/2013 20:30:20
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: '/u01/database/sybo3/oradata/users01.dbf'
RMAN-06010: error while looking up datafile: 4

RMAN> restore tablespace users; --->此時使用restore tablespace 方式提示失敗，看來，對於copy方式的備份，必須要copy回去
                                      --->後來看了一下語法，restore (datafile 4) FROM DATAFILECOPY方式可以搞定，括號不能省略
Starting restore at 2013/07/26 20:31:12
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/26/2013 20:31:12
ORA-01135: file 4 accessed for DML/query is offline
ORA-01110: data file 4: '/u01/database/sybo3/oradata/users01.dbf'
RMAN-06019: could not translate tablespace name "USERS"

-->下面直接使用copy方式進行還原
SQL> ho cp /u01/database/sybo3/fra/SYBO3/backupset/2013_07_26/users01.dbf /u01/database/sybo3/oradata/users01.dbf

-->接下來我們還原歸檔日誌,制定了from sequence子句，實際上，如果我們沒有指定restore archivelog，在recover時也會自動完成還原歸檔日誌
RMAN> restore archivelog from sequence 27;

Starting restore at 2013/07/26 20:36:55
using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log restore to default destination   --->這個地方是關鍵提示，還原到預設位置
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=28
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=29
channel ORA_DISK_1: reading from backup piece /u01/database/sybo3/fra/SYBO3/backupset/2013_07_26/o1_mf_annnn_TAG20130726T201640_8z4t58tn_.bkp
channel ORA_DISK_1: piece handle=/u01/database/sybo3/fra/SYBO3/backupset/2013_07_26/o1_mf_annnn_TAG20130726T201640_8z4t58tn_.bkp tag=TAG20130726T201640
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2013/07/26 20:36:57

--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612

-->此時在預設的路徑下可以看到已經被還原的歸檔日誌檔案
SQL> ho ls -hltr /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/*
-rw-r----- 1 oracle oinstall  13K Jul 26 20:36 /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_29_8z4vc85w_.arc
-rw-r----- 1 oracle oinstall 4.5K Jul 26 20:36 /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_28_8z4vc85z_.arc
-rw-r----- 1 oracle oinstall 4.0M Jul 26 20:36 /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_27_8z4vc85o_.arc

-->進行介質恢復
RMAN> recover datafile 4;

Starting recover at 2013/07/26 20:39:56
using channel ORA_DISK_1

starting media recovery   ---->下面提示歸檔日誌已經存在，是因為我們之前做了restore archivelog

archived log for thread 1 with sequence 27 is already on disk as file /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_27_8z4vc85o_.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_28_8z4vc85z_.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_29_8z4vc85w_.arc
archived log file name=/u01/database/sybo3/fra/SYBO3/archivelog/2013_07_26/o1_mf_1_27_8z4vc85o_.arc thread=1 sequence=27
media recovery complete, elapsed time: 00:00:00
Finished recover at 2013/07/26 20:39:56

-->online tablespace
SQL> alter tablespace users online;

Tablespace altered.

-->驗證結過成功
SQL> select * from t1;

SEQ        WHO
---------- --------------------
First      Robin
Second     Robinson
Last       End of test

3、restore archivelog 的其它用法     restore archivelog all;   還原全部歸檔日誌檔案     restore archivelog from logseq 27 ;  還原log sequence為27之後的所有歸檔日誌     restore archivelog from logseq 27 until logseq 29; 還原log sequence為27到29這幾個歸檔日誌      restore archivelog from time 'sysdate-7'; 還原七天以內的歸檔日誌     restore archivelog until logseq 29; 還原到seqence 為29的日誌檔案為止     set archivelog destination to '/u01/database/sybo5/arch';設定還原日誌檔案到新路徑，如     run{     set archivelog destination to '/u01/database/sybo5/arch';     restore archivelog low logseq 27;}