current online redo logfile 丟失的處理方法，redologfile
昨天做了rm -rf操作後的恢複演練，而且是在沒有任何備份的情況下。今天在做破壞性操作前，做了個rman全備，然後線上刪除全部資料庫檔案，包括控制檔案，資料檔案，線上記錄檔，歸檔檔案等。來看看有什麼方法可以讓資料庫恢複運行，由於是current redo logfile丟失，那麼本次測試是會遺失資料的，所以可以作為不完全恢複的一個案例。
--登入資料庫進行dml操作
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 13:40:37 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from aaron8219.test1;
INT
----------
1
2
SQL> insert into aaron8219.test1 values(3);
1 row created.
SQL> select group#,status,sequence# from v$log;
GROUP# STATUS SEQUENCE#
---------- ---------------- ----------
1 INACTIVE 1
2 CURRENT 2
3 INACTIVE 0
--不提交，另開一個session進行rm -rf操作
[root@ora10g ~]# cd /u01/app/oracle/oradata
[root@ora10g oradata]# ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Aug 25 16:09 ora10g
[root@ora10g oradata]# rm -rf ora10g/
[root@ora10g oradata]# ll
total 0
--原session直接abort關閉，類比current online redo logfile丟失
SQL> shutdown abort
--用之前剛產生的rman全備來還原資料庫各類檔案
[oracle@ora10g ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 26 13:44:58 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ora10g (not mounted)
RMAN> restore controlfile from '/rmanbak/full_ORA10G_4175411955_20140826_02pgtq5h_1_1.bak';
Starting restore at 26-AUG-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/app/oracle/oradata/ora10g/control01.ctl
output filename=/u01/app/oracle/oradata/ora10g/control02.ctl
output filename=/u01/app/oracle/oradata/ora10g/control03.ctl
Finished restore at 26-AUG-14
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 26-AUG-14
Starting implicit crosscheck backup at 26-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 26-AUG-14
Starting implicit crosscheck copy at 26-AUG-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 26-AUG-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORA10G/autobackup/2014_08_26/o1_mf_s_856615092_9zr3snsq_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/ora10g/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/ora10g/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/ora10g/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/ora10g/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/ora10g/example01.dbf
channel ORA_DISK_1: reading from backup piece /rmanbak/full_ORA10G_4175411955_20140826_01pgtq36_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/rmanbak/full_ORA10G_4175411955_20140826_01pgtq36_1_1.bak tag=TAG20140826T123653
channel ORA_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 26-AUG-14
RMAN> exit
Recovery Manager complete.
由於rman並不備份線上記錄檔，剛才的rman只是做了個全庫備份和控制檔案的備份，可以看到redo01.log，redo02.log，redo03.log這3個檔案並不存在
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 26 14:52:35 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> col member for a60
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------
3 /u01/app/oracle/oradata/ora10g/redo03.log
2 /u01/app/oracle/oradata/ora10g/redo02.log
1 /u01/app/oracle/oradata/ora10g/redo01.log
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ora10g/system01.dbf'
由於只是restore了資料庫，還未recover，資料庫是無法開啟的
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc
ORA-00280: change 502729 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 502729 generated at 08/26/2014 15:36:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_08_26/o1_mf_1_3_%u_.arc
ORA-00280: change 502729 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
由於之前的歸檔沒有用rman備份，而現在也不存在了，無法recover資料庫，就算用resetlogs也無法open資料庫。此時，只有通過隱含參數_allow_resetlogs_corruption來open資料庫了
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileora10g.ora
--建立pfile
SQL> create pfile from spfile;
File created.
--編輯$ORACLE_HOME/dbs/initora10g.ora初始化參數檔案，在檔案末尾添加*._allow_resetlogs_corruption = true
[oracle@ora10g ora10g]$ vi $ORACLE_HOME/dbs/initora10g.ora
ora10g.__db_cache_size=180355072
ora10g.__java_pool_size=4194304
ora10g.__large_pool_size=4194304
ora10g.__shared_pool_size=92274688
ora10g.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ora10g/adump'
*.background_dump_dest='/u01/app/oracle/admin/ora10g/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/ora10g/control01.ctl','/u01/app/oracle/oradata/ora10g/control02.ctl','/u01/app/oracle/oradata/ora10g/control03.ctl'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/admin/ora10g/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ora10g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ora10g/udump'
*._allow_resetlogs_corruption=TRUE
--關閉資料庫執行個體，用pfile啟動
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=$ORACLE_HOME/dbs/initora10g.ora
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 100664912 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--最後用resetlogs開啟資料庫
SQL> alter database open resetlogs;
Database altered.
SQL>
[oracle@ora10g ora10g]$ ll
total 1051020
-rw-r----- 1 oracle oinstall 7061504 Aug 26 16:03 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 26 16:03 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Aug 26 16:03 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Aug 26 16:03 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Aug 26 16:03 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Aug 26 16:03 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Aug 26 16:03 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Aug 26 16:03 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Aug 26 16:03 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Aug 26 16:03 temp01.dbf
-rw-r----- 1 oracle oinstall 31465472 Aug 26 16:03 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Aug 26 16:03 users01.dbf
[oracle@ora10g ora10g]$
再次查看資料檔案，發現重建了3個online redo logfile了
--resetlogs把SEQUENCE#序號重新變成1
SQL> select group#,sequence# from v$log;
GROUP# SEQUENCE#
---------- ----------
1 0
2 1
3 0
--測試表只留下了原來的資料庫，因為沒有commit，新插入的第3條記錄丟失，就算commit，也是會丟資料的，因為redo logfile也被刪除了
SQL> select * from aaron8219.test1;
INT
----------
1
2
注意，最後要把剛才設定的隱含參數_allow_resetlogs_corruption = true去掉，並重新建立spfile，否則資料庫將來會有引起不一致的風險，設定該隱含參數只是下下策，為了能讓資料庫open而不得已為之，遺失資料庫是肯定的了，因為這是在極端情況下得測試，通常只有在丟失了狀態為current的online redo logfile才會遺失資料，如果只是inactive的redo logfile，就算使用alter database open resetlogs;也不意味著100%丟資料。另外，要注意的是，當使用using backup controlfile關鍵詞時，必須要配合使用open resetlogs來開啟資料庫。以resetlogs方式open資料庫後，必須重新做一次資料庫全備，因為一旦incarnation改變之後，資料庫原來的備份組就失效了。
oracle current online redo log 丟失 資料庫會不會遺失資料
同組日誌有做鏡像嗎？一般都是同組日誌多個成員放在不同的磁碟上。
沒有的話current日誌中的內容肯定會丟失。
只能不完全恢複，可以使用之前的全備來跑歸檔，resetlogs開啟資料庫，resetlogs開啟之後記得再次備份。
如果沒有全備的話，那隻能修改_allow_resetlogs_corruption=ture這個參數，
mount狀態下recover database until cancel然後cancel掉，然後重新startup資料庫。
oracle 記錄檔丟了，怎解決？ 開啟了歸檔日誌
記錄檔丟失的話，如果是非當前日誌可以通過clear logfile解決，如果是當前日誌 那麼需要使用特殊手段才能開啟資料庫， online redolog 丟失 下 和開啟歸檔沒有直接關係。
如果自己搞不定可以找詩檀軟體專業ORACLE資料庫修複團隊成員幫您恢複!
