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;}
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2221013/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle rman備份歸檔日誌需要先切換日誌嗎Oracle
- [20221121]rman刪除歸檔日誌問題.txt
- DM7使用dmrestore工具利用歸檔日誌還原REST
- RMAN備份整庫和歸檔日誌的方法
- RMAN刪除歸檔日誌出現RMAN-0813錯誤的處理
- 【Oracle】歸檔日誌管理-設定歸檔日誌路徑以及歸檔日誌冗餘Oracle
- 通過RMAN設定standby接收日誌後主庫歸檔日誌才可刪除
- 歸檔日誌挖掘
- PostgreSQL 歸檔日誌SQL
- oracle歸檔日誌Oracle
- Oracle設定多個歸檔路徑生成多份歸檔日誌，Rman備份時也只備份其中的一份歸檔日誌Oracle
- 配置rman來自動刪除應用過的歸檔日誌
- 歸檔oracle alert日誌Oracle
- 14. 日誌歸檔
- Oracle歸檔日誌清理Oracle
- RMAN正確地刪除Archivelog以及設定有備庫的歸檔刪除策略Hive
- 手工rm刪除歸檔日誌對備份歸檔日誌的影響
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫
- guarantee restore points-Flashback after RMAN restoreREST
- 批量註冊歸檔日誌
- [重慶思莊每日技術分享]-備庫使用RMAN獲得歸檔日誌
- Rman在run命令塊裡臨時設定歸檔目錄，歸檔日誌能否恢復主要看預設的歸檔路徑
- DG歸檔日誌缺失恢復
- ArgoWorkflow教程(四)---Workflow & 日誌歸檔Go
- logminer異機挖掘歸檔日誌
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- oracle11G歸檔日誌管理Oracle
- oracle dg 歸檔日誌恢復情況Oracle
- oracle 刪除過期的歸檔日誌Oracle
- 【SQL】Oracle 歸檔日誌暴增原因分析SQLOracle
- PostgreSQL的xlog/Wal歸檔及日誌清理SQL
- Oracle歸檔日誌暴增排查優化Oracle優化
- RMAN-ERROR：因為找不到過期和丟失的歸檔日誌而備份失敗Error
- rman備份archivelog出現ORA-19625Hive
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- Oracle使用備份檔案集恢復歸檔日誌Oracle
- Oracle DataGuard歸檔日誌丟失處理方法Oracle
- oracle資料庫歸檔日誌量陡增分析Oracle資料庫