第一章 Redo檔案丢失的恢複
1.1 online redolog file 丢失
聯機Redo日志是Oracle資料庫中比較核心的檔案，當Redo日志檔案異常之後，資料庫就無法正常啟動，而且有丢失據的風險，強烈建議在條件允許的情況下，對Redo日志進行多路鏡像。需要注意的是，RMAN不能備份聯機Redo日志檔案。是以，聯機Redo日志一旦出現故障，則隻能進行清除日志了。清除日志檔案即表明可以重用該檔案。
1.1.1 資料庫歸檔/非歸檔模式下inactive redo異常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.
ORA-00327: log 2 of thread 1, physical size less than needed
SQL> alter database drop logfile group 2;
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> 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#
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;
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 297799040 bytes
Database Buffers 100663296 bytes
Redo Buffers 8503296 bytes
ORA-03113: end-of-file on communication channel
Process ID: 15390
Session ID: 125 Serial number: 5
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
告警日志：
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
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:48:39 2015
Connected to an idle instance.
SQL> startup mount;
SQL> alter database clear logfile group 1;
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.1.2 正常關閉資料庫current redo異常ORA-00316 ORA-01623
1.1.2.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'
---------- ---------- ---------- ----------------- ---------------- ----------------------------------
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
ORA-00316: log 1 of thread 1, type 0 in header is not log file
SQL> ALTER DATABASE drop logfile group 1;
ALTER DATABASE drop logfile group 1
ORA-01623: log 1 is current log for instance XFF (thread 1) - cannot drop
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
1.1.2.2 例二
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:52:49 2015
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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
Process ID: 15837
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
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
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:54:28 2015
ORA-00350: log 1 of instance oratest (thread 1) needs to be archived
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
SQL> ALTER DATABASE OPEN;
1 21 2 1229347 CURRENT /u02/app/oracle/oradata/oratest/redo02.log
1 0 1 1229346 UNUSED /u02/app/oracle/oradata/oratest/redo01.log
1.1.3 資料庫異常關閉current/active redo異常ORA-00316 ORA-01624 ORA-01194
1 8 2 686310 CURRENT /u01/oracle/oradata/XFF/redo02.log
1 7 1 686294 ACTIVE /u01/oracle/oradata/XFF/redo01.log
1 6 3 686289 INACTIVE /u01/oracle/oradata/XFF/redo03.log
ORA-01624: log 1 needed for crash recovery of instance XFF (thread 1)
ORA-00279: change 686294 generated at 04/20/2013 01:37:16 needed for thread 1
ORA-00289: suggestion : /u01/oracle/oracle/product/10.2.0/db_1/dbs/arch1_7_813202529.dbf
ORA-00280: change 686294 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oracle/oradata/XFF/redo01.log
ORA-00308: cannot open archived log '/u01/oracle/oradata/XFF/redo01.log'
ORA-27047: unable to read the header block of file
Additional information: 2
cancel
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: '/u01/oracle/oradata/XFF/system01.dbf'
ORA-01112: media recovery not started
alter database open resetlogs
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
ORA-01109: database not open
在這樣的情況下,資料庫異常關閉,current/active redo異常,通過使用隐含參數可能可以僥幸的恢複資料庫,但是也可能導緻資料丢失.這裡因為是模拟情況,無業務是以在很多較為繁忙的業務系統中,使用隐含參數resetlogs過程中可能還會遇到如下很多常見的錯誤,進一步增加了恢複難度。
current/active redo異常後附帶其他錯誤
ORA-600[2662]
Wed Dec 07 13:02:49 2011
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc (incident=216664):
ORA-00600: 内部錯誤代碼, 參數: [2662], [2], [1153862134], [2], [1153864845], [12582921], [], []
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_216664\hzyl_ora_3388_i216664.trc
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_3388.trc:
Error 600 happened during db open, shutting down database
USER (ospid: 3388): terminating the instance due to error 600
ORA-00600[4000]
Thu Feb 28 19:29:10 2013
Thu Feb 28 19:29:11 2013
Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc:
ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
Thu Feb 28 19:29:13 2013
Incremental checkpoint up to RBA [0x1.3.0], current log tail at RBA [0x1.3.0]
ORA-00704: bootstrap process failure
ORA-00704 ORA-00604 ORA-01555
Fri May 4 21:04:21 2012
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 40 with name "_SYSSMU40$" too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1286288
ORA-1092 signalled during: alter database open resetlogs...
current/active redo異常還可能報如下錯誤
redo檔案損壞報錯
Started redo scan
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc (incident=214262):
ORA-00353: 日志損壞接近塊 12014 更改 9743799889 時間 12/05/2011 09:21:11
ORA-00312: 聯機日志 3 線程 1: 'R:\ORADATA\HZYL\REDO03.LOG'
Incident details in: d:\app\administrator\diag\rdbms\hzyl\hzyl\incident\incdir_214262\hzyl_ora_2960_i214262.trc
Aborting crash recovery due to error 368
Errors in file d:\app\administrator\diag\rdbms\hzyl\hzyl\trace\hzyl_ora_2960.trc:
ORA-00368: 重做日志塊中的校驗和錯誤
ORA-368 signalled during: ALTER DATABASE OPEN...
redo檔案被其他執行個體占用報錯
Wed May 16 17:03:11 2012
Errors in file /oracle/admin/odsdb/udump/odsdb1_ora_2040024.trc:
ORA-00305: log 14 of thread 1 inconsistent; belongs to another database
ORA-00312: online log 14 thread 1: '/dev/rods_redo1_2_2'
ORA-00312: online log 14 thread 1: '/dev/rods_redo1_2_1'
ORA-305 signalled during: ALTER DATABASE OPEN...
存儲整體異常
Mon Oct 17 09:35:09 2011
Errors in file /oracle/app/admin/orcl/bdump/orcl2_lgwr_348814.trc:
ORA-00340: IO error processing online log 4 of thread 2
ORA-00345: redo log write error block 6732 count 2
ORA-00312: online log 4 thread 2: '/dev/rredo21'
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 6: No such device or address
Additional information: -1
Additional information: 1024
LGWR: terminating instance due to error 340
存儲IO異常
Fri Feb 21 08:44:42 2014
Thread 1 advanced to log sequence 591 (LGWR switch)
Current log# 1 seq# 591 mem# 0: J:\ORADATA\ORCL\REDO01.LOG
Fri Feb 21 15:31:20 2014
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_10312.trc:
ORA-00316: log 1 of thread 1, type 286 in header is not log file
ORA-00312: online log 1 thread 1: 'J:\ORADATA\ORCL\REDO01.LOG'
使用_disable_logging參數
Sat May 14 23:16:49 2005
Errors in file d:\oracle\admin\rman\bdump\rman_arc0_736.trc:
ORA-16038: log 3 sequence# 72 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\RMAN\REDO03.LOG'
1.1.3.1 current狀态日志丢失
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 7 52428800 1 NO CURRENT 661005 11-MAR-15
2 1 5 52428800 1 YES INACTIVE 660997 11-MAR-15
3 1 6 52428800 1 YES INACTIVE 660999 11-MAR-15
SQL> ho ls /u03/app/oracle/oradata/ora1024g/redo01.log
ls: cannot access /u03/app/oracle/oradata/ora1024g/redo01.log: No such file or directory
SQL> shutdown abort;
Total System Global Area 448790528 bytes
Fixed Size 2084616 bytes
Variable Size 130023672 bytes
Database Buffers 310378496 bytes
Redo Buffers 6303744 bytes
ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/ora1024g/redo01.log'
ORA-01624: log 1 needed for crash recovery of instance ora1024g (thread 1)
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
SQL> recover database using backup controlfile;
ORA-00279: change 662207 generated at 03/12/2015 10:08:02 needed for thread 1
ORA-00289: suggestion :
/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_7_%u_.arc
ORA-00280: change 662207 for thread 1 is in sequence #7
ORA-00308: cannot open archived log
'/u03/app/oracle/flash_recovery_area/ORA1024G/archivelog/2015_03_12/o1_mf_1_7_%u_.arc'
SQL> startup mount force;
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u03/app/oracle/oradata/ora1024g/system01.dbf'
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 10:20:50 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Database opened.
SQL> alter system reset "_allow_resetlogs_corruption" scope=spfile sid='*';
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
重做日志檔案在資料庫中是要求最高的元件，首先其對磁盤的IO要求極高，其次一旦CURRENT組發生故障，資料庫會立即崩潰，并且100%會發生資料丢失，是以ORACLE建議至少每個組需要兩個成員，并且在資料庫運作過程中日志檔案會一直被鎖定，以防不測。
Redo log的恢複分為兩種：CURRENT 和 非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> alter database clear logfile group 3 ;
SQL>alter database open;
（2）如果STATUS 是ACTIVE ，表示正在歸檔， 此時需要使用如下語句：
SQL> alter database clear unarchived logfile group 3 ;
current online log 損壞有兩種恢複方法：
這裡主要看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發生了變化為
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發生了變化為
1020708
lion@ORCL> alter system archive log current; --對日志進行歸檔時提示錯誤發生
alter system archive log current
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; --在日志未完成自動歸檔前，删除日志的後資料全部丢失
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; 改變資料庫清除未歸檔日志檔案組3;
Which action should you perform immediately after using this command?
你在使用此指令後應立即執行哪一個活動？
A. You should perform a log switch
B. You should make a backup of the database你應該做一個資料庫備份
C. You should switch the database to the NONARCHIVELOG mode
D. You should shut down the database instance and perform a complete database recovery
Answer: B
As soon as you discover that you have lost an online redo log, if the database is still functioning, what
should be your first action?
A. Shut down the database
B. Clear the online redo log
C. Back up the database
D. Checkpoint the database
E. Call Oracle support
1.1.4.2 Loss of a Redo Log File
If a member of a redo log file group is lost and if the group still has at least one member, note the following results:
1. Normal operation of the instance is not affected.
2. You receive a message in the alert log notifying you that a member cannot be found.
3. You can restore the missing log file by dropping the lost redo log member and adding a new member.
4. If the group with the missing log file has been archived you can clear the log group to re-create the missing file.
一、 第一種辦法
Recovering from the loss of a single redo log group member should not affect the running instance.
To perform this recovery:
1.Determine whether there is a missing log file by examining the alert log.
2.Restore the missing file by first dropping the lost redo log member:
SQL> ALTER DATABASE DROP LOGFILE MEMBER'+DATA/orcl/onlinelog/group_1.261.691672257';
Then add a new member to replace the lost red log member:
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
Enterprise Manager can also be used to drop and re-create the log file member.
Note: If using OMF for your redo log files and you use the above syntax to add a new redo log member to an existing group, that new redo log member file will not be an OMF file. If you want to ensure that the new redo log member is an OMF file, then the easiest recovery option would be to create a new redo log group and then drop the redo log group that had the missing redo log member.
3.If the media failure is due to the loss of a disk drive or controller, rename the missing file.
4.If the group has already been archived, or if you are in NOARCHIVELOG mode, you may choose to solve the problem by clearing the log group to re-create the missing file or files. Select the appropriate group and then select the Clear Logfile action. You can also clear the affected group manually with the following command:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP #;
二、 第二種辦法
執行：alter database clear logfile group 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
3.Your database is in ARCHIVELOG mode.You have two online redo log groups,each of which contains one redo member.When you attempt to start the database,you receive the following errors:
ORA-00313:open failed for members of log group 1 of thread 1
ORA-00312:online log 1 thread 1:'D:\REDO01.LOG'
You discover that the online redo log file of the current redo group is corrupted.
Which statement should you use to resolve this issue?
A.ALTER DATABASE DROP LOGFILE GROUP 1;
B.ALTER DATABASE CLEAR LOGFILE GROUP 1;
C.ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
D.ALTER DATABASE DROP LOGFILE MEMBER'D:\REDO01.LOG';
Answer:C
(答案解析：
參考：http://docs.oracle.com/cd/E11882_01/backup.112/e10642/osadvsce.htm#BRADV90052
[oracle@rtest~]$oerr ora 313
00313,00000,"open failed for members of log group%s of thread%s"
//*Cause:The online log cannot be opened.May not be able to find file.
//*Action:See accompanying errors and make log available.
[oracle@rtest~]$oerr ora 312
00312,00000,"online log%s thread%s:'%s'"
//*Cause:This message reports the filename for details of another message.
//*Action:Other messages will accompany this message.See the
//associated messages for the appropriate action to take.
你的資料庫在歸檔記錄模式。你有兩個線上重做日志組，其中每個都包含一個重做成員。當您嘗試啟動資料庫時，您會收到以下錯誤：
ORA-00313：線程1日志組1成員打開失敗
ORA-00312：聯機日志1線程1：'D：\REDO01.LOG
你發現目前的重做組聯機重做日志檔案被損壞。你應該使用哪種說法來解決這個問題？
Clearing Inactive,Unarchived Redo
Clearing a not-yet-archived redo log allows it to be reused without archiving it.This action makes backups unusable if they were started before the last change in the log,unless the file was taken offline before the first change in the log.Hence,if you need the cleared log file for recovery of a backup,then you cannot recover that backup.Clearing a not-yet-archived-redo-log,prevents complete recovery from backups due to the missing log.
To clear an inactive,online redo log group that has not been archived:
If the database is shut down,then start a new instance and mount the database:
SQL>STARTUP MOUNT
Clear the log using the UNARCHIVED keyword.
For example,to clear log group 2,issue the following SQL statement:
SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;)
A database has three online redo log groups with one member each. A redo log member with the
status ACTICE is damages while the database is running.
What is the first step you should take to solve this problem?
A.Attempt to Issue a checkpoint.
B.Restart the database using the RESETLOGS option.
C.Drop the redo log number and create it in a different location.
D.Perform and incomplete recovery up to the most recent available redo log.
Answer:A
題目解答 注意在runing 且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
解答：
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後觸發ckpt 然後在clear的。 很明顯7 不需要
SQL> ALTER DATABASE CLEAR logfile group 1;
ALTER DATABASE CLEAR logfile group 1
SQL> ALTER DATABASE CLEAR unarchived logfile group 1;
SQL> ALTER DATABASE CLEAR unarchived logfile group 2;
SQL> ALTER DATABASE CLEAR unarchived logfile group 3;
1.1.4.4 loss all online redo logs
You have lost all your online redo logs. As a result, your database has crashed. You have tried to restart the database and clear the online redo log files, but when you try to open the database you get the following error.
Total System Global Area 167395328 bytes
Fixed Size 1298612 bytes
Variable Size 142610252 bytes
Database Buffers 20971520 bytes
Redo Buffers 2514944 bytes
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: ,,/oracle01/oradata/orcl/redo02a.log
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory
ORA-00312: online log 2 thread 1: ,,/oracle01/oradata/orcl/redo02.log
ORA-27037: unable to obtain file
status Linux Error: 2: No such file or directory Additional information: 3
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
* ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1) ORA-00312: online log 2 thread
1: ,,/oracle01/oradata/orcl/redo02.log ORA-00312: online log 2 thread
1: ,,/oracle01/oradata/orcl/redo02a.log
What steps must you take to resolve the error?
A. Issue the recover database redo logs command.
B. Issue the Startup Mount command to mount the database.
C. Restore the last full database backup.
D. Perform a point-in-time recovery, applying all archived redo logs that are available.
E. Restore all archived redo logs generated during and after the last full database backup.
F. Open the database using the alter database open resetlogs command.
G. Issue the alter database open command.
A. b, a, f
B. e, b, a, f
C.e, b, a, g
D.b, a, g
E. c, e, b, d, f
Answer: E
You are trying to recover your database. During the recovery process, you receive the following error:
ORA-00279: change 5033391 generated at 08/17/2008 06:37:40 needed for thread 1ORA-00289:
suggestion:
/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17
/o1_mf_1_11_%u_.arc
ORA-00280: change 5033391 for thread 1 is in sequence #11
ORA-00278: log
file ,,/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17
/o1_mf_1_10_4bj6wnqm_.arc no longer needed for this recovery Specify log:
{=suggested | filename | AUTO | CANCEL}
,,/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3
How do you respond to this error? (Choose two.)
A. Restore the archived redo log that is missing and attempt recovery again.
B. Recovery is complete and you can open the database.
C. Recovery needs redo that is not available in any archived redo log. Attempt to apply an online redo log if available.
D. Recover the entire database and apply all archived redo logs again.
E. Recovery is not possible because an archived redo log has been lost.
Answer: AC
1.1.4.5 資料庫未挂掉的情況下的恢複
參考：【RMAN】rm -rf 誤操作的恢複過程----資料庫在無備份且open情況下的恢複
1.2 恢複archivelog介紹
Given the following RMAN commands, choose the option that reflects the order required to restore your currently operational ARCHIVELOG-mode database.
A. restore database;
B. recover database;
C. shutdown immediate
D. startup
E. restore archivelog all;
F. alter database open
A. a, b, c, d, e, f
B. c, b, a, d, e, f
C. c, b, a, d, f
D. c, a, b, d
E. c, a, e, b, d, f
還原歸檔日志通常情況下是Oracle在recover時自動完成的。當資料庫出現問題,但不需要restore隻需recover時,發現要用到的archivelog已經備份并删除了,因為我們備份archivelog一般是采用delete input的,這時先需要restore archivelog,然後才能做recover,下面介紹一下restore archivelog的用法:
restore archivelog後面可以跟的參數有"all, from, high, like, logseq, low, scn, sequence, time, until"
1.2.1 恢複歸檔
備份所有歸檔日志檔案
RMAN> backup archivelog all delete input;
1.2.1.1 restore archivelog 的各種選項
1.恢複全部歸檔日志檔案
RMAN> restore archivelog all;
2.隻恢複5到8這四個歸檔日志檔案
RMAN> restore archivelog from logseq 5 until logseq 8;
3.恢複從第5個歸檔日志起
RMAN> restore archivelog from logseq 5;
4.恢複7天内的歸檔日志
RMAN> restore archivelog from time 'sysdate-7';
5. sequence between 寫法
RMAN> restore archivelog sequence between 1 and 3;
6.恢複到哪個日志檔案為止
RMAN> restore archivelog until logseq 3;
7.從第五個日志開始恢複
RMAN> restore archivelog low logseq 5;
8.到第5個日志為止
RMAN> restore archivelog high logseq 5;
恢複指定的archivelog：restore archivelog sequence 18;
--若歸檔日志不在本地，則需要恢複相應的歸檔日志到本地目錄。
run {allocate channel ci type disk;
set archivelog destination to '/tmp';
restore archvielog from logseq xxx until logseq xxx;
release channel ci;
};
1.列出已經備份的archivelog
list backup of archivelog all;
2.預覽恢複出程,但不真正恢複,可以在你執行恢複前先看看恢複過程,也可以驗證一下你的文法是否寫對
restore archivelog all preview; 即在你要執行的restore archivelog指令後加preview
restore archivelog sequence 18 preview;
3.恢複指定時間範圍的archivelog
3.1 顯示2008-08-13 10:00:00到2008-08-13 11: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 10:00:00到2008-08-13 11: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')" preview;
3.3 真正恢複2008-08-13 10:00:00到2008-08-13 11: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.恢複指定的archivelog
restore archivelog sequence 18;
恢複sequence為18的archivelog
5.restore archivelog like恢複模糊查詢出來的archivelog,這個隻能用于通過catalog的備份,用nocatalog的會報錯
restore archivelog like '%18%';
6.恢複指定sequence範圍的archivelog
restore archivelog from sequence 18 until sequence 20;
或restore archivelog low sequence 18 high sequence 20;
或restore archivelog low logseq 18 high logseq 20;
restore archivelog from logseq 5;
7.指定archivelog的恢複目的地,如你想把archivelog恢複到一個臨時目錄時有用,但這個必須包含在run{}裡面才能用
set archivelog destination to 'e:\temp';
以上基本上可以解決你恢複archivelog的需求,我是在10.2.0.4版本中測試通過的---------------------------------------------
以下部分是在本人正式環境中遇到的實際情況，非轉載:
備份日志中有以下内容:
通道 t1: 正在指定備份集中的存檔日志
輸入存檔日志線程 =1 序列 =18070 記錄 ID=35794 時間戳=671966051
輸入存檔日志線程 =1 序列 =18071 記錄 ID=35796 時間戳=671966351
輸入存檔日志線程 =1 序列 =18072 記錄 ID=35798 時間戳=671966652
輸入存檔日志線程 =1 序列 =18073 記錄 ID=35800 時間戳=671966952
輸入存檔日志線程 =1 序列 =18074 記錄 ID=35802 時間戳=671967249
輸入存檔日志線程 =1 序列 =18075 記錄 ID=35804 時間戳=671967550
輸入存檔日志線程 =1 序列 =18076 記錄 ID=35806 時間戳=671967850
輸入存檔日志線程 =1 序列 =18077 記錄 ID=35808 時間戳=671968151
輸入存檔日志線程 =1 序列 =18078 記錄 ID=35810 時間戳=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(
skipping archive log file D:\ARCHPAUL\PUBTEST_1_4.DBF; already backed up 1 time(
skipping archive log file D:\ARCHPAUL\PUBTEST_1_5.DBF; already backed up 1 time(
skipping archive log file D:\ARCHPAUL\PUBTEST_1_6.DBF; already backed up 1 time(
skipping archive log file D:\ARCHPAUL\PUBTEST_1_7.DBF; already backed up 1 time(
skipping archive log file D:\ARCHPAUL\PUBTEST_1_8.DBF; already backed up 1 time(
skipping archive log file D:\ARCHPAUL\PUBTEST_1_9.DBF; already backed up 1 time(
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
skipping archive log file D:\ARCHPAUL\PUBTEST_1_12.DBF; already backed up 1 time
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
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
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=1
archive log thread=1 sequence=2
archive log thread=1 sequence=3
archive log thread=1 sequence=4
archive log thread=1 sequence=5
archive log thread=1 sequence=6
archive log thread=1 sequence=7
archive log thread=1 sequence=8
archive log thread=1 sequence=9
archive log thread=1 sequence=10
archive log thread=1 sequence=11
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
archive log thread=1 sequence=13
piece handle=D:\BACKUP\2QJHUA76_1_1 tag=TAG20080602T162510 params=NULL
Finished restore at 02-JUN-08
RMAN>
2.隻恢複 5到8這四個歸檔日志檔案
RMAN> restore archivelog from logseq 5 until logseq 8;
3.恢複從第5個歸檔日志起
RMAN> restore archivelog from logseq 5;
4.恢複7天内的歸檔日志
RMAN> restore archivelog from time 'sysdate-7';
5. sequence between 寫法
RMAN> restore archivelog sequence between 1 and 3;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
6.恢複到哪個日志檔案為止
RMAN> restore archivelog until logseq 3;
6.從第五個日志開始恢複
RMAN> restore archivelog low logseq 5;
7.到第5個日志為止
RMAN> restore archivelog high logseq 5;
channel ORA_DISK_1: sid=14 devtype=DISK
如果想改變恢複到另外路徑下則可用下面語句
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
channel ci: starting archive log restore to user-specified destination
archive log destination=d:\backup
channel ci: restoring archive log
channel ci: restored backup piece 1
channel ci: restore complete
released channel: ci
--------------------------------------------------------------------------------------------
Oracle資料庫使用RMAN備份的時候，會把歸檔日志壓成備份集（backup set），而有時候我們為了恢複資料的需要可能需要從這些備份集中解析出歸檔日志（archive log），這時可以用restore這個指令。
該指令的參數可以用SCN、SEQUENCE、TIME等，也可以附加preview參數先檢視計劃，該參數和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、把備份集檔案COPY到預設的歸檔路徑中
我這裡是/orabak/arch，從第一步檔案清單的Piece Name也可以看出來歸檔的路徑。
否則在restore過程中會報以下錯誤：
channel ORA_DISK_1: reading from backup piece /orabak/arch/ERPDB_arch_20090624_690383375_14453_1
ORA-19870: error reading backup piece /orabak/arch/ERPDB_arch_20090624_690383375_14453_1
ORA-19505: failed to identify file "/orabak/arch/ERPDB_arch_20090624_690383375_14453_1"
IBM AIX RISC System/6000 Error: 2: No such file or directory
3、執行restore指令，一般如果是臨時需要這些檔案，可以指定歸檔日志恢複到其他的目錄，這時必須用run指令：
2> set archivelog destination to '/orabak/testarch';
3> SQL 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"';
4> restore archivelog time between '2009-06-24 09:00:00' and '2009-06-24 12:10:00';
5> }
using target database control file instead of recovery catalog
sql statement: ALTER SESSION SET NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS"
Starting restore at 29-JUN-09
......
Finished restore at 29-JUN-09
如果我們明确要恢複哪些歸檔日志，可以用SEQUENCE BETWEEN integer1 AND integer2指令來操作。
同一個RUN塊中允許同時出現多個SET ARCHIVELOG指令，也就是說可以通過在不同位置設定不同的歸檔路徑的方式，将歸檔恢複到不同的目錄，例如：
1.RMAN>RUN{
2.2>SET ARCHIVELOG DESTINATION TO'F:\ORACLE\BACKUP\ARCLOG1'; 3.3> RESTORE ARCHIVELOG SEQUENCEBETWEEN 15 AND 20;
4.4>SET ARCHIVELOG DESTINATION TO'F:\ORACLE\BACKUP\ARCLOG2'; 5.5> RESTORE ARCHIVELOG SEQUENCEBETWEEN 21 AND 30;
6.6>SET ARCHIVELOG DESTINATION TO'F:\ORACLE\BACKUP\ARCLOG3'; 7.7> RESTORE ARCHIVELOG SEQUENCEBETWEEN 31 AND 40; 8.8> }
1.2.2 我的例子
[oracle@rhel6_lhr ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 5 16:09:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLASM (DBID=3424884828)
RUN
{
SET ARCHIVELOG DESTINATION TO '/home/oracle';
restore archivelog from time 'sysdate-1';
Starting restore at 05-JAN-15
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting archived log restore to user-specified destination
archived log destination=/home/oracle
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=910
archived log thread=1 sequence=911
archived log thread=1 sequence=912
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/orclasm/arch_ORCLASM_20150105_200_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/orclasm/arch_ORCLASM_20150105_200_1.bak tag=TAG20150105T120650
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log thread=1 sequence=913
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/orclasm/arch_ORCLASM_20150105_201_1.bak
channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/orclasm/arch_ORCLASM_20150105_201_1.bak tag=TAG20150105T120650
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 05-JAN-15
2> SET ARCHIVELOG DESTINATION TO '/home/oracle';
3> restore archivelog from time 'sysdate-1';
4> }
archived log for thread 1 with sequence 910 is already on disk as file /home/oracle/1_910_850260255.dbf
archived log for thread 1 with sequence 911 is already on disk as file /home/oracle/1_911_850260255.dbf
archived log for thread 1 with sequence 912 is already on disk as file /home/oracle/1_912_850260255.dbf
archived log for thread 1 with sequence 913 is already on disk as file /home/oracle/1_913_850260255.dbf
restore not done; all files read only, offline, or already restored
RMAN> show all
2> ;
RMAN configuration parameters for database with db_unique_name ORCLASM are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/oracle_bk/orclasm/control_%F.bak';
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 '/home/oracle/oracle_bk/orclasm/%U_%d.bak';
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 BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orclasm.f'; # default
2> SET ARCHIVELOG DESTINATION TO '/home/oracle';
3> restore archivelog low logseq 914;
archived log for thread 1 with sequence 914 is already on disk as file +FRA/orclasm/archivelog/2015_01_05/thread_1_seq_914.453.868206267
archived log for thread 1 with sequence 915 is already on disk as file +FRA/orclasm/archivelog/2015_01_05/thread_1_seq_915.435.868206269
archived log for thread 1 with sequence 916 is already on disk as file +FRA/orclasm/archivelog/2015_01_05/thread_1_seq_916.310.868206275
archived log for thread 1 with sequence 917 is already on disk as file +FRA/orclasm/archivelog/2015_01_05/thread_1_seq_917.307.868206283
archived log for thread 1 with sequence 918 is already on disk as file +FRA/orclasm/archivelog/2015_01_05/thread_1_seq_918.374.868206387
archived log for thread 1 with sequence 919 is already on disk as file +FRA/orclasm/archivelog/2015_01_05/thread_1_seq_919.361.868206393
Starting backup at 05-JAN-15
skipping archived logs of thread 1 from sequence 910 to 913; already backed up
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=914 RECID=914 STAMP=868206269
input archived log thread=1 sequence=915 RECID=915 STAMP=868206269
input archived log thread=1 sequence=916 RECID=916 STAMP=868206275
input archived log thread=1 sequence=917 RECID=917 STAMP=868206282
input archived log thread=1 sequence=918 RECID=918 STAMP=868206386
input archived log thread=1 sequence=919 RECID=919 STAMP=868206392
input archived log thread=1 sequence=920 RECID=920 STAMP=868206525
channel ORA_DISK_1: starting piece 1 at 05-JAN-15
channel ORA_DISK_1: finished piece 1 at 05-JAN-15
piece handle=/home/oracle/oracle_bk/orclasm/6cprvhtt_1_1_ORCLASM.bak tag=TAG20150105T162845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_914.453.868206267 RECID=914 STAMP=868206269
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_915.435.868206269 RECID=915 STAMP=868206269
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_916.310.868206275 RECID=916 STAMP=868206275
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_917.307.868206283 RECID=917 STAMP=868206282
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_918.374.868206387 RECID=918 STAMP=868206386
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_919.361.868206393 RECID=919 STAMP=868206392
archived log file name=+FRA/orclasm/archivelog/2015_01_05/thread_1_seq_920.318.868206525 RECID=920 STAMP=868206525
archived log file name=/home/oracle/1_910_850260255.dbf RECID=911 STAMP=868205361
archived log file name=/home/oracle/1_911_850260255.dbf RECID=912 STAMP=868205362
archived log file name=/home/oracle/1_912_850260255.dbf RECID=910 STAMP=868205357
archived log file name=/home/oracle/1_913_850260255.dbf RECID=913 STAMP=868205368
Finished backup at 05-JAN-15
Starting Control File and SPFILE Autobackup at 05-JAN-15
piece handle=/home/oracle/oracle_bk/orclasm/control_c-3424884828-20150105-03.bak comment=NONE
Finished Control File and SPFILE Autobackup at 05-JAN-15
RMAN> run {
3> restore archivelog low logseq 914;}
archived log thread=1 sequence=914
archived log thread=1 sequence=915
archived log thread=1 sequence=916
archived log thread=1 sequence=917
archived log thread=1 sequence=918
archived log thread=1 sequence=919
archived log thread=1 sequence=920
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle_bk/orclasm/6cprvhtt_1_1_ORCLASM.bak
channel ORA_DISK_1: piece handle=/home/oracle/oracle_bk/orclasm/6cprvhtt_1_1_ORCLASM.bak tag=TAG20150105T162845
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
SET ARCHIVELOG DESTINATION TO '/tmp/';
RESTORE ARCHIVELOG SEQUENCE between 919 and 920;
Starting restore at 2015-01-20 17:09:38
channel ORA_DISK_1: SID=413 device type=DISK
archived log destination=/tmp/
Finished restore at 2015-01-20 17:09:40
1.2.3 怎樣清除v$archived_log視圖中的過期資訊
在作業系統上删除這些歸檔并不會在控制檔案中有記錄，是以檢視v$archived_log時還會有記錄，考慮用RMAN來删除：
RMAN>delete archivelog all;
之後再檢視v$archived_log發現還有記錄。google了下怎麼解決，有三種方法。
相關閱讀： Oracle手工恢複案例（非歸檔模式） http://www.linuxidc.com/Linux/2013-06/86718.htm
1.重建控制檔案，例如 backup controlfileto trace後重建該控制檔案，但要求有資料檔案均存在。
2.設定control_file_record_keep_time=0 然後等待記錄被重用，這樣很不好。
3.使用包來清理，注意不要在生産庫上這麼做
PROCEDURE resetCfileSection(record_typeINbinary_integer);
– This procedure attemptsto reset the circular controlfilesection.
– Input parameters:
– record_type
– The circular record type whose controlfile sectionisto be reset.
execute sys.dbms_backup_restore.resetCfileSection(11); ==> 清理v$ARCHIVED_LOG對應的記錄
execute sys.dbms_backup_restore.resetCfileSection(28); ==>清理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 = '
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列為空
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，資訊已經被清除
no rows selected
但是這樣是把所有的v$archive_log資訊都清除了，包括未過期的也會不清除。下面再将未過期的歸檔檔案資訊注冊進來。
--我測試環境上歸檔日志都在/oradata/archive/中
RMAN> catalog start with '/oradata/archive/';
--再次查詢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
About Me
...............................................................................................................................
● 本文作者：小麥苗，隻專注于資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）和個人微信公衆号（xiaomaimiaolhr）上有同步更新
● 本文itpub位址：http://blog.itpub.net/26736162/abstract/1/
● 本文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 在魔都完成
● 文章内容來源于小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
【恢複】Redo日志檔案丢失的恢複 【恢複】Redo日志檔案丢失的恢複 【恢複】Redo日志檔案丢失的恢複