【恢復】Redo日誌檔案丟失的恢復
第一章 Redo日誌檔案丟失的恢復
1.1 online re dolog file 丟失
聯機 Redo日誌 是Oracle資料庫 中 比較核心 的 檔案 ， 當Redo 日誌檔案 異常之後 ， 資料庫 就 無法正常啟動 ， 而且有丟失據的風險 ， 強烈建議 在 條件允許 的情況下，對 Redo 日誌進行 多路映象 。 需要注意的是，RMAN不能備份聯機 Redo 日誌一旦出現故障，則只能進行清除日誌了。 清除日誌檔案即表明可以重用該檔案。
1.1.1 資料庫歸檔 / 非歸檔模式下 ORA-00316 ORA-00327
1.1.1.1 例一
SQL> startup mount
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 2 of thread 1, type in header is not log file
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log'
SQL> col member for a40
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- -----------------------------------
1 15 3 665697 CURRENT /u01/oracle/oradata/XFF/redo03.log
1 14 2 645619 INACTIVE /u01/oracle/oradata/XFF/redo02.log
1 13 1 625540 INACTIVE /u01/oracle/oradata/XFF/redo01.log
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00327: log 2 of thread 1, physical size less than needed
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log'
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database add logfile group 2 ('/u01/oracle/oradata/XFF/redo02.log') size 50M reuse;
Database altered.
1.1.1.2 例二
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:46:16 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 16 3 1209020 CURRENT /u02/app/oracle/oradata/oratest/redo03.log
1 15 1 1209017 INACTIVE /u02/app/oracle/oradata/oratest/redo01.log
1 14 2 1209012 INACTIVE /u02/app/oracle/oradata/oratest/redo02.log
SQL> ! rm -rf /u02/app/oracle/oradata/oratest/redo01.log
SQL>
SQL> startup force;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 15390
Session ID: 125 Serial number: 5
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
告警日誌：
Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_lgwr_15484.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/oratest/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:48:39 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> alter database clear logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 17 1 1229024 CURRENT /u02/app/oracle/oradata/oratest/redo01.log
1 16 3 1209020 INACTIVE /u02/app/oracle/oradata/oratest/redo03.log
1 14 2 1209012 INACTIVE /u02/app/oracle/oradata/oratest/redo02.log
SQL>
1.1.2 正常關閉資料庫 current redo 異常 ORA-00316 ORA-01623
1.1.2.1 例一
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type in header is not log file
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- ----------------------------------
1 16 1 685918 CURRENT /u01/oracle/oradata/XFF/redo01.log
1 15 3 665697 INACTIVE /u01/oracle/oradata/XFF/redo03.log
1 0 2 0 UNUSED /u01/oracle/oradata/XFF/redo02.log
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> ALTER DATABASE drop logfile group 1;
ALTER DATABASE drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance XFF (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
1.1.2.2 例二
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:52:49 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 20 1 1229346 CURRENT /u02/app/oracle/oradata/oratest/redo01.log
1 19 3 1229343 INACTIVE /u02/app/oracle/oradata/oratest/redo03.log
1 18 2 1229340 INACTIVE /u02/app/oracle/oradata/oratest/redo02.log
SQL> ! rm -rf /u02/app/oracle/oradata/oratest/redo01.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 15837
Session ID: 125 Serial number: 5
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
告警日誌：
Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_15949.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'
Wed May 06 13:53:47 2015
ARC1 started with pid=21, OS id=15976
USER (ospid: 15949): terminating the instance due to error 313
System state dump requested by (instance=1, osid=15949), summary=[abnormal instance termination].
System State dumped to trace file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_diag_15919.trc
Dumping diagnostic data in directory=[cdmp_20150506135347], requested by (instance=1, osid=15949), summary=[abnormal instance termination].
Instance terminated by USER, pid = 15949
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:54:28 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-00350: log 1 of instance oratest (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> col member for a50
SQL> set lines 120
SQL> SELECT thread#,
2 a.sequence#,
3 a.group#,
4 TO_CHAR (first_change#, '9999999999999999') "SCN",
5 a.status,
6 MEMBER
7 FROM v$log a, v$logfile b
8 WHERE a.group# = B.GROUP#
9 ORDER BY a.sequence# DESC;
THREAD# SEQUENCE# GROUP# SCN STATUS MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------
1 21 2 1229347 CURRENT /u02/app/oracle/oradata/oratest/redo02.log
1 19 3 1229343 INACTIVE /u02/app/oracle/oradata/oratest/redo03.log
1 0 1 1229346 UNUSED /u02/app/oracle/oradata/oratest/redo01.log
SQL>
1.1.3 資料庫異常關閉current/active redo異常 ,current/active redo 異常 , 但是也可能導致資料丟失 , 無業務所以在很多較為繁忙的業務系統中 resetlogs 過程中可能還會遇到如下很多常見的錯誤 CURRENT 組發生故障，資料庫會立即崩潰，並且 ORACLE 建議至少每個組需要兩個成員，並且在資料庫執行過程中日誌檔案會一直被鎖定，以防不測。
Redo log的恢復分為兩種： CURRENT
3.1 CURRENT 情況
造成 redo 損壞，很多情況是與突然斷電有關。這種情況下是比較麻煩的。
（ 1 ）如果有歸檔和備份，可以用不完全恢復。
SQL>startup mount;
SQL>recover database until cancel; 先選擇 auto ，儘量恢復可以利用的歸檔日誌，然後重新執行：
SQL>recover database until cancel; 這次輸入 cancel ，完成不完全恢復 ,
用 resetlogs 開啟資料：
SQL>alter database open resetlogs ； 開啟資料庫
（ 2 ）強制恢復， 這種方法可能會導致資料不一致
sql>startup mount;
sql>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
sql>recover database until cancel;
sql>alter database open resetlogs;
運氣好的話，資料庫能正常開啟，但是由於使用 _allow_resetlogs_corruption 方式開啟，會造成資料的丟失，且資料庫的狀態不一致。因此，這種情況下 Oracle 建議透過 EXP 方式匯出資料庫。重建新資料庫後，再匯入。
redo 的損壞，一般還容易伴隨以下 2 種錯誤： ORA-600[2662] （ SCN 有關）和 ORA-600[4000] （回滾段有關）。
metalink 上的兩篇文章介紹了兩種情況的處理方法：
TECH: Summary For Forcing The Database Open With `_ALLOW_RESETLOGS_CORRUPTION` with Automatic Undo Management [ID 283945.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/29/6106083.aspx
ORA-600 [2662] Block SCN is ahead of Current SCN [ID 28929.1]
http://blog.csdn.net/tianlesoftware/archive/2010/12/29/6106130.aspx
這兩種情況下的恢復有點複雜，回頭單獨做個測試，在補充進來。
3.2 非 CURRENT 情況
這種情況下的恢復比較簡單，因為 redo log 是已經完成歸檔或者正在歸檔。 沒有正在使用。可以透過 v$log 檢視 redo log 的狀態。
（ 1 ）如果 STATUS 是 INACTIVE, 則表示已經完成了歸檔，直接清除掉這個 redo log 即可。
SQL>startup mount;
SQL> alter database clear logfile group 3 ;
SQL>alter database open;
（ 2 ）如果 STATUS 是 ACTIVE ，表示正在歸檔， 此時需要使用如下語句：
SQL>startup mount;
SQL> alter database clear unarchived logfile group 3 ;
SQL>alter database open;
current online log 損壞有兩種恢復方法：
（ 1 ）如果有歸檔和備份，可以用不完全恢復。
SQL>startup mount;
SQL>recover database until cancel; 先選擇 auto ，儘量恢復可以利用的歸檔日誌，然後重新執行：
SQL>recover database until cancel; 這次輸入 cancel ，完成不完全恢復 ,
用 resetlogs 開啟資料：
SQL>alter database open resetlogs ； 開啟資料庫
（ 2 ）強制恢復， 這種方法可能會導致資料不一致
sql>startup mount;
sql>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
sql>recover database until cancel;
sql>alter database open resetlogs;
這裡主要看 2 點：
（ 1 ）使用了 _allow_resetlogs_corruption 引數
（ 2 ）這種情況下，可能會報 ORA-600[2662] （ SCN 有關）和 ORA-600[4000] （回滾段有關）的錯誤。
使用 _allow_resetlogs_corruption 引數，強制的開啟資料庫，可能會導致邏輯的壞塊，從而影響資料字典。 所以，即使使用該引數正常開啟後，也需要做的一個操作：邏輯匯出資料。 重建例項，匯入例項。 消除邏輯壞塊的可能性。
如果使用 _allow_resetlogs_corruption 引數啟動報了 undo segment 的錯誤而無法啟動，處理方法參考第二節中 undo 的處理情況。 只要 DB 能正常 open ，就匯出資料，重建例項，在匯入。
1.1.4 其他
以下命令需要在 sqlplus 中執行：
Which of the following does the recover command not do? 下列哪項是恢復命令不能做？
A. Restore archived redo logs. 還原歸檔重做日誌。
B. Apply archived redo logs.
C. Restore incremental backups.
D. Apply incremental backups.
E. Restore datafile images.
The recover command does not restore datafile images. It does restore and apply archived redo logs and incremental backup images during the recovery process.
Answer: A
Which statement about recovering from the loss of a redo log group is true? 有關重做日誌組的損失中恢復，哪種說法是真的？
A. If the lost redo log group is ACTIVE, you should first attempt to clear the log file. B.
If the lost redo log group is CURRENT, you must clear the log file.
C. If the lost redo log group is ACTIVE, you must restore, perform cancel-based incomplete recovery, and
open the database using the RESETLOGS option.
D. If the lost redo log group is CURRENT, you must restore, perform cancel-based incomplete recovery, and
open the database using the RESETLOGS option. 如果丟失的重做日誌組是最新的，你必須恢復，執行基於
取消的不完全恢復，並使用重置日誌選項開啟資料庫。
Answer: D
1.1.4.1 聯機重做日誌檔案的恢復 (online redo log )
當資料庫置為 mount 狀態，且將要轉換為 open 狀態時，資料檔案，聯機日誌檔案被開啟，因此聯機日誌的丟失可以在 mount 狀態完成
恢復步驟
a . 啟動到 mount 狀態 ( startup mount force )
b . 還原資料庫 ( restore database )
c . 恢復資料庫 ( recover database )
下面對刪除日誌並進行恢復
lion@ORCL > select * from tb2 ;
ID NAME
---------- ---------------
2 Jackson
lion@ORCL > select current_scn from v$database ; -- 檢視資料庫當前的 SCN
CURRENT_SCN
-----------
1020638
lion@ORCL > insert into tb2 select 1 , 'Johnson' from dual ; -- 為表 tb2 新增一條記錄
lion@ORCL > commit ;
lion@ORCL > select current_scn from v$database ; -- 資料庫當前的 SCN 發生了變化為
CURRENT_SCN
-----------
1020685
lion@ORCL > select file# , checkpoint_change# from v$datafile_header ; -- 資料檔案頭部的 checkpoint_change
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1020368
2 1020368
3 1020368
4 1020368
5 1020368
6 1020368
lion@ORCL > ho rm - f $ORACLE_BASE / oradata / orcl /*.log -- 刪除所有的日誌檔案 */
lion@ORCL > insert into tb2 select 2 , 'wilson' from dual ; -- 為表插入新記錄
lion@ORCL > commit ;
lion@ORCL > select current_scn from v$database ; -- 資料庫當前的 SCN 發生了變化為
CURRENT_SCN
-----------
1020708
lion@ORCL > alter system archive log current ; -- 對日誌進行歸檔時提示錯誤發生
alter system archive log current
*
ERROR at line 1 :
ORA - 16038 : log 1 sequence# 1 cannot be archived
ORA - 00312 : online log 1 thread 1 : '/u01/oracle/oradata/orcl/redo01.log'
lion@ORCL > conn / as sysdba
sys@ORCL > startup mount force ;
[oracle@oradb ~]$ uniread rman target / catalog rman / rman@asmdb -- 退出 RMAN 後並重新連線
RMAN > run {
2 > allocate channel ch1 device type disk ;
3 > restore database ;
4 > recover database ;
5 > release channel ch1 ;}
RMAN - 06054 : media recovery requesting unknown log : thread 1 seq 1 lowscn 1020365
sys@ORCL > recover database until cancel ; -- 回到 SQLPlus 直接使用 until cancel 來進行恢復
sys@ORCL > alter database open resetlogs ; -- 執行 opensetlogs 開啟資料庫
sys@ORCL > select * from lion . tb2 ; -- 在日誌未完成自動歸檔前，刪除日誌的後資料全部丟失
ID NAME
---------- ---------------
2 Jackson
Which are the correct steps, in order , to deal with the loss of an online redo log if the database has not yet
crashed? 如果資料庫尚未崩潰，哪些是按順序的正確步驟，以處理聯機重做日誌的丟失？
a. Issue a checkpoint.
b. Shut down the database.
c. Issue an alter database open command to open the database.
d. Startup mount the database.
e. Issue an alter database clear logfile command.
f. Recover all database datafiles.
a. 發出一個檢查點。
b. 關閉資料庫。
d. 啟動掛載資料庫。
e. 發出改變資料庫清除日誌檔案命令。
c. 發出改變資料庫開啟命令來開啟資料庫。
A. a, b, c, d
B. b, d, e, c
C. a, b, d, e, c 阿扁得逞
D. b, f, d, f, c
E. b, d, a, c
Answer: C
The database is running in the ARCHIVELOG mode. It has three redo log groups with one member each. One of the redo log groups has become corrupted. You have issued the following command during the recovery of a damaged redo log file: 資料庫執行在歸檔記錄模式。它每一個成員都有三個重做日誌組。重做日
志組之一已損壞。損壞的重做日誌檔案的恢復過程中，您已發出以下命令：
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3; 改變資料庫清除未歸檔日誌檔案組 first
1.1.4.3 Loss of a Redo Log Group
Recovering from the Loss of a Redo Log Group
If you have lost an entire redo log group, then all copies of the log files for that group are unusable or gone.
The simplest case is where the redo log group is in the INACTIVE state. That means it is not currently being written to, and it is no longer needed for instance recovery. If the problem is temporary, or you are able to fix the media, then the database continues to run normally, and the group is reused when enough log switch events occur. Otherwise, if the media cannot be fixed, you can clear the log file. When you clear a log file, you are indicating that it can be reused.
If the redo log group in question is ACTIVE, then, even though it is not currently being written to, it is still needed for instance recovery. If you are able to perform a checkpoint, then the log file group is no longer needed for instance recovery, and you can proceed as if the group were in the inactive state.
If the log group is in the CURRENT state, then it is, or was, being actively written to at the time of the loss. You may even see the LGWR process fail in this case. If this happens, the instance crashes. Your only option at this point is to restore from backup, perform cancel-based point-in-time recovery, and then open the database with the RESETLOGS option.
Clearing a Log File
Clear a log file using this command:
ALTER DATABASE CLEAR [UNARCHIVED] LOGFILE GROUP
[UNRECOVERABLE DATAFILE]
When you clear a log file, you are indicating that it can be reused. If the log file has already been archived, the simplest form of the command can be used. Use the following query to determine which log groups have been archived:
SQL> SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;
For example, the following command clears redo log group 3, which has already been archived:
SQL> ALTER DATABASE CLEAR LOFGILE GROUP 3;
If the redo log group has not been archived, then you must specify the UNARCHIVED keyword. This forces you to acknowledge that it is possible that there are backups that rely on that redo log for recovery, and you have decided to forgo that recovery opportunity. This may be satisfactory for you, especially if you take another backup right after you correct the redo log group problem; you then no longer need that redo log file.
It is possible that the redo log is required to recover a data file that is currently offline.
ocp
A database is running In ARCHIVBXXMS mode. It has two online redo log groups and each group has one member.A LGWR Input/output (I/O) fells due to permanent media failure that has resulted In the loss of redo log file and the LWGR terminates causing the instance to crash. The steps to recover from the loss of a current redo log group member in the random order are as follow.
1) Restore the corrupted redo log group.
2) Restore from a whole database backup.
3) Perform incomplete recovery.
4) Relocate by renaming the member of the damaged online redo log group to a new location.
5) Open the database with the RESETLOGS option.
6) Restart the database instance.
7) Issue a checkpoint and clear the log.
Identify the option with the correct sequential steps to accomplish the task efficiently.
A. 1, 3, 4, and 5
B. 7, 3, 4. and 5
C. 2, 3, 4, and 5
D. 7, 4, 3. and 5
E. Only 6 is required
Answer: C
解答：
To recover from loss of an active online redo log group in ARCHIVELOG mode:
1. Begin incomplete media recovery, recovering up through the log before the damaged log.
2. Ensure that the current name of the lost redo log can be used for a newly created file.
If not, then rename the members of the damaged online redo log group to a new location. For example, enter:
3. ALTER DATABASE RENAME FILE "/disk1/oradata/trgt/redo01.log" TO "/tmp/redo01.log";
4. ALTER DATABASE RENAME FILE "/disk1/oradata/trgt/redo02.log" TO "/tmp/redo02.log";
5. Open the database using the RESETLOGS option:
6. ALTER DATABASE OPEN RESETLOGS;
先不完全恢復在更改日誌路徑後開啟資料庫 所以 後面為3,4,5 到底是還原備份還是清空日誌呢？
Losing an Active Online Redo Log Group
If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If the operation is successful, then the active redo log becomes inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If the operation is unsuccessful, or if your database has halted, then perform one of procedures in this section, depending on the archiving mode.
The current log is the one LGWR is currently writing to. If a LGWR I/O operation fails, then LGWR terminates and the instance fails. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.
所以先還原備份但如果資料庫沒有關閉情況下，可以執行 switch logfile後觸發 clear 的。 很明顯 【 RMAN】 ---- 資料庫在無備份且 5 到 5 個歸檔日誌起
RMAN> restore archivelog from logseq 5;
4.恢復 5 個日誌為止
RMAN> restore archivelog high logseq 5;
恢復指定的 archivelog： archivelog
list backup of archivelog all;
2.預覽恢復出程 , 可以在你執行恢復前先看看恢復過程 preview
restore archivelog sequence 18 preview;
3.恢復指定時間範圍的 2008-08-13 10:00:00 到 archivelog
list backup of archivelog time between "to_date('2008-08-13 10:00:00','yyyy-mm-dd hh24:mi:ss')" and "to_date('2008-08-13 11:00:00','yyyy-mm-dd hh24:mi:ss')";
3.2 預覽恢復 2008-08-13 11:00:00 之間的 2008-08-13 10:00:00 到 archivelog
restore archivelog time between "to_date('2008-08-13 10:00:00','yyyy-mm-dd hh24:mi:ss')" and "to_date('2008-08-13 11::00','yyyy-mm-dd hh24:mi:ss')"
4.恢復指定的 18 的 archivelog, 這個只能用於透過 , 用 =18070 記錄 =671966051
輸入存檔日誌執行緒 =1 序列 ID=35796 時間戳 =18072 記錄 =671966652
輸入存檔日誌執行緒 =1 序列 ID=35800 時間戳 =18074 記錄 =671967249
輸入存檔日誌執行緒 =1 序列 ID=35804 時間戳 =18076 記錄 =671967850
輸入存檔日誌執行緒 =1 序列 ID=35808 時間戳 =18078 記錄 =671968451
單獨恢復 18071 到18076
rman> run
{ allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
restore archivelog from logseq 18071 until logseq 18076 ;
release channel t1;
}
1.備份所有歸檔日誌檔案
RMAN> backup archivelog all delete input;
Starting backup at 02-JUN-08
current log archived
using channel ORA_DISK_1
skipping archive log file D:\ARCHPAUL\ARC00001.001; already backed up 1 time(s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_2.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_3.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_4.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_5.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_6.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_7.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_8.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_9.DBF; already backed up 1 time(
s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_10.DBF; already backed up 1 time
(s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_11.DBF; already backed up 1 time
(s)
skipping archive log file D:\ARCHPAUL\PUBTEST_1_12.DBF; already backed up 1 time
(s)
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=13 recid=128 stamp=656353510
channel ORA_DISK_1: starting piece 1 at 02-JUN-08
channel ORA_DISK_1: finished piece 1 at 02-JUN-08
piece handle=D:\BACKUP\2QJHUA76_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=D:\ARCHPAUL\PUBTEST_1_13.DBF recid=128 stamp=656353510
channel ORA_DISK_1: deleting archive log(s)
archive log filename=D:\ARCHPAUL\ARC00001.001 recid=116 stamp=656352824
archive log filename=D:\ARCHPAUL\PUBTEST_1_2.DBF recid=117 stamp=656353339
archive log filename=D:\ARCHPAUL\PUBTEST_1_3.DBF recid=118 stamp=656353340
archive log filename=D:\ARCHPAUL\PUBTEST_1_4.DBF recid=119 stamp=656353340
archive log filename=D:\ARCHPAUL\PUBTEST_1_5.DBF recid=120 stamp=656353369
archive log filename=D:\ARCHPAUL\PUBTEST_1_6.DBF recid=121 stamp=656353370
archive log filename=D:\ARCHPAUL\PUBTEST_1_7.DBF recid=122 stamp=656353375
archive log filename=D:\ARCHPAUL\PUBTEST_1_8.DBF recid=123 stamp=656353376
archive log filename=D:\ARCHPAUL\PUBTEST_1_9.DBF recid=124 stamp=656353382
archive log filename=D:\ARCHPAUL\PUBTEST_1_10.DBF recid=125 stamp=656353384
archive log filename=D:\ARCHPAUL\PUBTEST_1_11.DBF recid=126 stamp=656353386
archive log filename=D:\ARCHPAUL\PUBTEST_1_12.DBF recid=127 stamp=656353465
Finished backup at 02-JUN-08
Starting Control File and SPFILE Autobackup at 02-JUN-08
piece handle=D:\BACKUP\C-799229701-20080602-0C comment=NONE
Finished Control File and SPFILE Autobackup at 02-JUN-08
第二 : restore archivelog 的各種選項
1.restore archivelog all 恢復全部歸檔日誌檔案
RMAN> restore archivelog all;
Starting restore at 02-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
RMAN>
2.只恢復 5到8這四個歸檔日誌檔案
RMAN> restore archivelog from logseq 5 until logseq 8;
Starting restore at 02-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
3.恢復從第5個歸檔日誌起
RMAN> restore archivelog from logseq 5;
Starting restore at 02-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
RMAN>
4.恢復7天內的歸檔日誌
RMAN> restore archivelog from time 'sysdate-7';
Starting restore at 02-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
RMAN>
5. sequence between 寫法
RMAN> restore archivelog sequence between 1 and 3;
Starting restore at 02-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
6.恢復到哪個日誌檔案為止
RMAN> restore archivelog until logseq 3;
Starting restore at 02-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
6.從第五個日誌開始恢復
RMAN> restore archivelog low logseq 5;
Starting restore at 02-JUN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=6
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=7
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=13
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
RMAN>
7.到第5個日誌為止
RMAN> restore archivelog high logseq 5;
Starting restore at 02-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=2
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=3
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=4
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=5
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 02-JUN-08
如果想改變恢復到另外路徑下 則可用下面語句
set archivelog destination to 'd:\backup';
RMAN> run
2> {allocate channel ci type disk;
3> set archivelog destination to 'd:\backup';
4> restore archivelog all;
5> release channel ci;
6> }
allocated channel: ci
channel ci: sid=10 devtype=DISK
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 02-JUN-08
channel ci: starting archive log restore to user-specified destination
archive log destination=d:\backup
channel ci: restoring archive log
archive log thread=1 sequence=1
channel ci: restoring archive log
archive log thread=1 sequence=2
channel ci: restoring archive log
archive log thread=1 sequence=3
channel ci: restoring archive log
archive log thread=1 sequence=4
channel ci: restoring archive log
archive log thread=1 sequence=5
channel ci: restoring archive log
archive log thread=1 sequence=6
channel ci: restoring archive log
archive log thread=1 sequence=7
channel ci: restoring archive log
archive log thread=1 sequence=8
channel ci: restoring archive log
archive log thread=1 sequence=9
channel ci: restoring archive log
archive log thread=1 sequence=10
channel ci: restoring archive log
archive log thread=1 sequence=11
channel ci: restoring archive log
archive log thread=1 sequence=12
channel ci: restored backup piece 1
piece handle=D:\BACKUP\2OJHUA5Q_1_1 tag=TAG20080602T162426 params=NULL
channel ci: restore complete
channel ci: starting archive log restore to user-specified destination
archive log destination=d:\backup
channel ci: restoring archive log
archive log thread=1 sequence=13
channel ci: restored backup piece 1
piece handle=D:\BACKUP\2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL
channel ci: restore complete
Finished restore at 02-JUN-08
released channel: ci
--------------------------------------------------------------------------------------------
Oracle資料庫使用 backup set ），而有時候我們為了恢復資料的需要可能需要從這些備份集中解析出歸檔日誌（ restore 這個命令。
該命令的引數可以用 SCN、 TIME 等，也可以附加 list backup of archivelog 是等效的。
1 、根據時間檢視需要的備份集：
ERPDB1@/orabak>rman target /
RMAN> list backup of archivelog time between "to_date('2009-06-24 08:00:00','yyyy-mm-dd hh24:mi:ss')" and "to_date('2009-06-24 13:00','yyyy-mm-dd hh24:mi:ss')";
以下是示例，並非原來的檔案列表：
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18021 104.97M DISK 00:00:25 02-APR-10
BP Key: 21243 Status: AVAILABLE Compressed: YES Tag: TAG20100402T213015
Piece Name: /orabak/arch/CNDERPDB_arch_20100402_715296294_18088_1
List of Archived Logs in backup set 18021
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 130930 12425302024 02-APR-10 12425464067 02-APR-10
1 130931 12425464067 02-APR-10 12425612482 02-APR-10
1 130932 12425612482 02-APR-10 12425741312 02-APR-10
1 130933 12425741312 02-APR-10 12425903002 02-APR-10
1 130934 12425903002 02-APR-10 12426033120 02-APR-10
1 130935 12426033120 02-APR-10 12426231614 02-APR-10
1 130936 12426231614 02-APR-10 12426258334 02-APR-10
或者用 preview 檢視：
RMAN> restore archivelog time between "to_date('2009-06-24 08:00:00','yyyy-mm-dd hh24:mi:ss')" and "to_date('2009-06-24 13:00','yyyy-mm-dd hh24:mi:ss')" preview;
也可以先指定時間格式，然後就可以不用 to_date 函式了：
RMAN> SQL 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"';
RMAN> restore archivelog time between '2009-06-24 09:00:00' and '2009-06-24 12:00:00' preview;
2、把備份集檔案 restore 命令，一般如果是臨時需要這些檔案，可以指定歸檔日誌恢復到其他的目錄，這時必須用 RMAN 來刪除：
RMAN>delete archivelog all;
之後再檢視 v$archived_log發現還有記錄。
1.重建控制檔案，例如 control_file_record_keep_time=0 然後等待記錄被重用，這樣很不好。
3. 使用包來清理，注意不要在生產庫上這麼做
PROCEDURE resetCfileSection(record_typeINbinary_integer);
– Input parameters:
– The circular record type whose controlfile sectionisto be reset.
execute sys.dbms_backup_restore.resetCfileSection(11); ==> 清理 v$rman_status 對應的記錄
Removing entries in v$archived_log referencing a particluar DEST_ID [ID 845361.1]
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.3 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
Goal
This note provides instructions on how to clear the section in the controlfile which contains data referencing v$archived_log.
For example v$archived_log may contain data from dest_id = 1 & dest_id=2.
This note will guide you through the process of only keeping entries from one distinct location
Solution
It is possible to clear different section of the controlfile.
Section 11 refers to the v$archived_log entries.
SQL>execute sys.dbms_backup_restore.resetCfileSection( 11);
This will clear all files in v$archived_log;
Then using RMAN we can catalog the DEST=1 file back in.
Assume that all archivelogs reside in /recovery_area/archives
RMAN> catalog start with '/recovery_area/archives';
This will update the controlfile with these entries only.
NOTE:
If you clear a controlfile section using undocumented event, then you also need to update high_al_recid in the node table for that database to 0 in
recovery catalog.
For 11g recovery catalog schema and above:
update node set high_al_recid = 0 where db_unique_name = '<your target="" database="" db_unique_name'.
For 10gR2 recovery catalog schema and below:
update dbinc set high_al_recid = 0 where db_name = '';
在使用 RMAN命令刪除歸檔後，查詢v$archived_log檢視會發現name列為空了，但其他列的資訊還保留，時間長了會留下很多過期的資訊，影響維護工作，需要將過期的資訊刪除。首先模擬下問題的出現過程：
--刪除歸檔日誌之前檢視v$archived_log檢視，情況正常
SQL > select dest_id , sequence# , name , blocks from v$archived_log ;
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 101 / oradata / archive / orcl_1_101_851966182 . arc 2730
1 102 / oradata / archive / orcl_1_102_851966182 . arc 95711
1 103 / oradata / archive / orcl_1_103_851966182 . arc 94813
1 104 / oradata / archive / orcl_1_104_851966182 . arc 95048
1 105 / oradata / archive / orcl_1_105_851966182 . arc 94677
1 106 / oradata / archive / orcl_1_106_851966182 . arc 97494
1 107 / oradata / archive / orcl_1_107_851966182 . arc 94300
1 108 / oradata / archive / orcl_1_108_851966182 . arc 97494
--使用RAMN命令刪除歸檔
RMAN > delete archivelog all ;
--再次查詢v$archived_log檢視，name列為空
SQL > select dest_id , sequence# , name , blocks from v$archived_log ;
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 101 2730
1 102 95711
1 103 94813
1 104 95048
1 105 94677
1 106 97494
1 107 94300
1 108 97494
出現這樣的現象是因為使用RMAN命令在刪除歸檔日誌的時候不能夠清楚控制檔案中的內容，導致v$archived_log留下的過期的不完整資訊。下面將歸檔資訊進行清除：
--清除控制檔案中關於v$archived_log的資訊
SQL > execute sys . dbms_backup_restore . resetCfileSection ( 11 );
PL / SQL procedure successfully completed .
--再次查詢v$archived_log，資訊已經被清除
SQL > select dest_id , sequence# , name , blocks from v$archived_log ;
no rows selected
但是這樣是把所有的v$archive_log資訊都清除了，包括未過期的也會不清除。下面再將未過期的歸檔檔案資訊註冊進來。
--我測試環境上歸檔日誌都在/oradata/archive/中
RMAN > catalog start with '/oradata/archive/' ;
--再次查詢v$archived_log，未被刪除的歸檔資訊可以查詢到了
SQL > select dest_id , sequence# , name , blocks from v$archived_log ;
DEST_ID SEQUENCE# NAME BLOCKS
---------- ---------- --------------------------------------------- ----------
1 110 / oradata / archive / orcl_1_110_851966182 . arc 1
1 111 / oradata / archive / orcl_1_111_851966182 . arc 2
1 109 / oradata / archive / orcl_1_109_851966182 . arc 31079
需要注意的是，對於命令 “ SYS.DBMS_BACKUP_RESTORE.RESETCFILESECTION(11) ”中的 11 代表的是歸檔檔案（其實就是控制檔案中的“ record type ”），那麼其它數字分別代表什麼含義呢？其實，可以透過檢視 V$CONTROLFILE_RECORD_SECTION 來檢視：
SYS@lhrdb> SELECT ROWNUM-1 "NUMBER", TYPE FROM V$CONTROLFILE_RECORD_SECTION; number TYPE ---------- -------------------------------------------------------- 0 DATABASE 1 CKPT PROGRESS 2 REDO THREAD 3 REDO LOG 4 DATAFILE 5 FILENAME 6 TABLESPACE 7 TEMPORARY FILENAME 8 RMAN CONFIGURATION 9 LOG HISTORY 10 OFFLINE RANGE 11 ARCHIVED LOG 12 BACKUP SET 13 BACKUP PIECE 14 BACKUP DATAFILE 15 BACKUP REDOLOG 16 DATAFILE COPY 17 BACKUP CORRUPTION 18 COPY CORRUPTION 19 DELETED OBJECT 20 PROXY COPY 21 BACKUP SPFILE 22 DATABASE INCARNATION 23 FLASHBACK LOG 24 RECOVERY DESTINATION 25 INSTANCE SPACE RESERVATION 26 REMOVABLE RECOVERY FILES 27 RMAN STATUS 28 THREAD INSTANCE NAME MAPPING 29 MTTR 30 DATAFILE HISTORY 31 STANDBY DATABASE MATRIX 32 GUARANTEED RESTORE POINT 33 RESTORE POINT 34 DATABASE BLOCK CORRUPTION 35 ACM OPERATION 36 FOREIGN ARCHIVED LOG 37 PDB RECORD 38 AUXILIARY DATAFILE COPY 39 MULTI INSTANCE REDO APPLY 40 PDBINC RECORD 41 TABLESPACE KEY HISTORY
因此，輸入不同的數字，會對不同的檢視資料進行清理，例如：
EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(11); /** CLEAR V$ARCHIVED_LOG */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(12) ; /** CLEAR V$BACKUP_SET */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(13) ; /** CLEAR V$BACKUP_PIECE */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(17); /** CLEAR V$BACKUP_CORRUPTION */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(18); /** CLEAR V$COPY_CORRUPTION */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(19); /** CLEAR V$DELETED_OBJECT */ EXECUTE DBMS_BACKUP_RESTORE.RESETCFILESECTION(28); /** CLEAR V$RMAN_STATUS */
& 說明：
有關 清除 V$ARCHIVED_LOG 檢視中的過期資訊 的更多內容 可以參考我的 BLOG ： http://blog.itpub.net/26736162/viewspace-2141732/
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（ http://blog.itpub.net/26736162 ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人微信公眾號（ xiaomaimiaolhr ）上有同步更新
● 本文itpub地址： http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址： http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群： 230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用 微信客戶端 掃描下邊的 左邊 圖片來關注小麥苗的微信公眾號： xiaomaimiaolhr，掃描 右邊 的二維碼加入小麥苗的QQ群， 學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2141732/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【備份恢復】恢復 丟失已歸檔重做日誌檔案
- oracle丟失日誌檔案的恢復( 轉)Oracle
- 非歸檔丟失日誌檔案的恢復
- 恢復案例：無歸檔，丟失全部控制檔案、日誌檔案恢復案例
- 丟失聯機重做日誌檔案的恢復
- REDO檔案丟失或者損壞的恢復
- RMAN恢復案例:無恢復目錄，丟失全部資料檔案、控制檔案、日誌檔案恢復
- rman恢復--丟失聯機重做日誌的恢復
- 在歸檔模式下丟失日誌檔案的恢復模式
- Oracle Redo丟失恢復方案Oracle
- 丟失所有重做日誌檔案的恢復例子丟失所有重做日誌檔案的恢復例子如下:
- 備份恢復之redo日誌組member成員丟失
- 控制檔案丟失恢復
- 【控制檔案丟失恢復】
- 恢復丟失的控制檔案
- 聯機重做日誌丟失的恢復
- 丟失非活動日誌組的恢復
- 丟失已歸檔日誌檔案下恢復資料庫資料庫
- oracle丟失inactive日誌檔案的恢復操作過程Oracle
- Oracle重做日誌檔案損壞或丟失後的恢復Oracle
- RMAN恢復案例:丟失全部資料檔案恢復
- 控制檔案丟失恢復(二)
- 控制檔案全部丟失恢復
- 從丟失日誌組中恢復流程
- 資料檔案丟失的恢復
- 控制檔案全部丟失的恢復
- 控制檔案部分丟失的恢復
- Oracle Password檔案丟失的恢復Oracle
- 控制檔案丟失的RMAN恢復
- 沒備份，歸檔日誌存在，丟失資料檔案的恢復
- 利用incremental backup恢復丟失日誌的standbyREM
- oracle丟失active或current日誌檔案的恢復操作過程Oracle
- oracle歸檔日誌丟失後的資料庫恢復Oracle資料庫
- 剪下的檔案還能恢復嗎，恢復剪貼丟失的檔案
- Oracle備份與恢復【丟失資料檔案的恢復】Oracle
- undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
- undo表空間檔案丟失恢復（3）--無備份無redo的情況下恢復
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式