日誌檔案
============
增加日誌成員
============
SQL> alter database drop logfile group 1;-----刪除之前確保group 1不是當前組，並且處於INACTIVE狀態
Database altered.
SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01a.log','/u01/app/oracle/oradata/orcl/redo01b.log') size 10M;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03a.log','/u01/app/oracle/oradata/orcl/redo03b.log') size 10M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database drop logfile group 2;
alter database drop 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: '/u01/app/oracle/oradata/orcl/redo02.log'
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 15 CURRENT NO
2 1 1 14 ACTIVE YES
3 2 1 0 UNUSED YES
SQL> alter system switch logfile;
System altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 18 CURRENT NO
2 1 1 17 INACTIVE YES
3 2 1 16 INACTIVE YES
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/orcl/redo02a.log','/u01/app/oracle/oradata/orcl/redo02b.log') size 10M;
Database altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 18 CURRENT NO
2 2 1 0 UNUSED YES
3 2 1 16 INACTIVE YES
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
================
聯機重做日誌狀態
================
日誌檔案組的狀態一般有INACTIVE、ACTIVE、CURRENT、UNUSED、CLEARING、CLEARING_CURRNT等六種狀態：
SQL> SELECT STATUS FROM V$LOG;
UNUSED ： 表示該聯機重做日誌檔案組對應的檔案還從未被寫入過資料，通常剛剛建立的聯機重做日誌檔案組會顯示成這一狀態。當日志切換到這一組時，就會改變狀態。
CURRENT ： 表示當前正在使用的日誌檔案組。該聯機重做日誌組是活動的。當前Oracle資料庫正在使用的聯機重做日誌檔案組。
ACTIVE ： 表示該組是活動的但不是當前組，例項恢復時需要這組日誌。如果處於這一狀態，表示雖然當前並未使用，不過該檔案中內容尚未歸檔，或者檔案中的資料沒有全部寫入資料檔案，一旦需要
例項恢復，必須藉助該檔案中儲存的內容。
INACTIVE ： 表示例項恢復已不再需要這組聯機重做日誌組了。表示對應的聯機重做日誌檔案中的內容已被妥善處理，該組聯機重做日誌當前處於空閒狀態。
CLEARING ：表示該組重做日誌檔案正被重建（重建後該狀態會變成UNUSED）。
CLEARING_CURRENT ：表示該組重做日誌重建時出現錯誤。
日誌檔案的狀態有 STALE,INVALID 、DELETED 、空白 四種狀態。可以透過下面語句檢視
SELECT STATUS FROM V$LOGFILE
INVALID : 表示該檔案是不可以被訪問的。
STALE : 表示該檔案中的內容是不完全的。
空白 ： 表示該檔案正在使用。
DELETED : 表示該檔案已不再有用了。
ARCHIVED列值為 YES表示已經歸檔， NO表示未歸檔。 SEQUENCE列值表示日誌序列號，每進行一次日誌切換就+1。
oracle 檢查點（Checkpoint ）
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 7 ACTIVE YES
2 2 1 8 ACTIVE YES
3 2 1 9 CURRENT NO
SQL> alter system checkpoint local; --------手動產生檢查點，使ACTIVE狀態變為INACTIVE;
System altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 7 INACTIVE YES
2 2 1 8 INACTIVE YES
3 2 1 9 CURRENT NO
==================
日誌檔案丟失恢復
==================
================
資料庫開啟狀態下
================
一 ：丟失某個日誌組中的某個member
這種情況在日誌多路複用的情況下是不影響資料庫使用的，所以建議日誌組的成員數至少2個。
二 ：丟失的是非當前活動（INACTIVE ）日誌
1 如果日誌已經歸檔（YES），可以直接使用alter database clear logfile group x;來重建日誌檔案；
2 如果日誌檔案沒有歸檔（NO）,可以使用alter database clear unarchived logfile group x;強行clear，來重建日誌檔案即可；
有歸檔的日誌組建議在操作後立即對資料庫執行新的完全備份，因為日誌已丟失，所有日誌丟失之前的恢復將失效。
三 ：丟失當前活動（ACTIVE ）日誌
恢復方法同方法“二”；
四 ：如果丟失的是當前日誌, 當資料庫是開啟，恢復方法同方法“二”；
一：
[oracle@chen ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@chen orcl]$ rm -rf redo02a.log
SQL> shutdown immediate;
SQL> startup
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 18 INACTIVE YES
2 2 1 19 INACTIVE YES
3 2 1 20 CURRENT NO
SQL> select * from v$logfile;
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
[oracle@chen orcl]$ cp redo02b.log redo02a.log
SQL> alter system switch logfile;
System altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
二：
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 36 ACTIVE YES
2 2 1 37 CURRENT NO
3 2 1 35 INACTIVE YES
[oracle@chen orcl]$ rm -rf redo01a.log redo01b.log
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
。。。。。。。。。
在歸檔模式下，丟失了非CURRENT日誌組，這會在日誌切換時因無法歸檔導致資料庫hang住
SQL> ctrl+c
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
[oracle@chen trace]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/
[oracle@chen trace]$ vim alert_orcl.log
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01a.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01b.log'
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 0 UNUSED YES
2 2 1 40 INACTIVE NO
3 2 1 41 CURRENT NO
SQL> alter system switch logfile;
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 42 CURRENT NO
2 2 1 40 INACTIVE YES
3 2 1 41 ACTIVE YES
System altered.
SQL> ho ls /u01/app/oracle/oradata/orcl
control01.ctl redo01b.log redo02.log redo03.log temp01.dbf
example01.dbf redo02a.log redo03a.log sysaux01.dbf undotbs01.dbf
redo01a.log redo02b.log redo03b.log system01.dbf users01.dbf
三 ：同方法“二”
四 ：
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 63 INACTIVE YES
2 2 1 64 CURRENT NO
3 2 1 62 INACTIVE YES
[oracle@chen orcl]$ rm -rf redo02a.log redo02b.log
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> alter database drop logfile group 2; ---------沒有歸檔時直接刪除
alter database drop logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02a.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02b.log'
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02a.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02b.log'
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> ho ls
control01.ctl redo01b.log redo03a.log sysaux01.dbf undotbs01.dbf
example01.dbf redo02a.log redo03b.log system01.dbf users01.dbf
redo01a.log redo02b.log redo03.log temp01.dbf
SQL> alter system switch logfile;
System altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 69 ACTIVE YES
2 2 1 70 CURRENT NO
3 2 1 68 ACTIVE YES
================
資料庫關閉狀態下
================
一 ：丟失某個日誌組中的某個member ：
這種情況在日誌多路複用的情況下是不影響資料庫使用的，所以建議日誌組的成員數至少2個。
二 ：丟失的是非當前活動（INACTIVE ）日誌
1 如果日誌已經歸檔（YES），可以直接使用alter database clear logfile group x;來重建日誌檔案；
2 如果日誌檔案沒有歸檔（NO）,可以使用alter database clear unarchived logfile group x;強行clear，來重建日誌檔案即可；
有歸檔的日誌組建議在操作後立即對資料庫執行新的完全備份，因為日誌已丟失，所有日誌丟失之前的恢復將失效。
三 ：丟失當前活動（ACTIVE ）日誌
四 ：丟失當前日誌組 1 資料庫正常關閉(9i後面的版本)
2 9i版本
3 資料庫意外關閉
資料庫意外關閉，並且丟失的是當前日誌,可以透過設定隱含引數的方法強制開啟資料庫
二： 1
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 69 INACTIVE YES
2 2 1 70 CURRENT NO
3 2 1 68 INACTIVE YES
SQL> shutdown immediate
[oracle@chen orcl]$ rm -rf redo03a.log redo03b.log ---刪除INACTIVE,YES
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 838863760 bytes
Database Buffers 419430400 bytes
Redo Buffers 8859648 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10390
Session ID: 1 Serial number: 5
[oracle@chen orcl]$ vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
.........
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
恢復
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 838863760 bytes
Database Buffers 419430400 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> alter database clear logfile group 3;
Database altered.
SQL> alter database open;
Database altered.
SQL> ho ls
control01.ctl redo01b.log redo03a.log sysaux01.dbf undotbs01.dbf
example01.dbf redo02a.log redo03b.log system01.dbf users01.dbf
redo01a.log redo02b.log redo03.log temp01.dbf
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 69 INACTIVE YES
2 2 1 70 CURRENT NO
3 2 1 0 UNUSED YES
三：1 資料庫正常關閉
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 69 INACTIVE YES
2 2 1 70 ACTIVE YES
3 2 1 71 CURRENT NO
[oracle@chen orcl]$ rm -rf redo02a.log redo02b.log
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 838863760 bytes
Database Buffers 419430400 bytes
Redo Buffers 8859648 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 11405
Session ID: 1 Serial number: 5
[oracle@chen orcl]$ vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
..............
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_11405.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02a.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02b.log'
USER (ospid: 11405): terminating the instance due to error 313
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 838863760 bytes
Database Buffers 419430400 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
三 ：2 資料庫意外關閉
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03a.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01a.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01b.log NO
3 ONLINE /u01/app/oracle/oradata/orcl/redo03b.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02b.log NO
6 rows selected.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 2 1 69 INACTIVE YES
2 2 1 72 CURRENT NO
3 2 1 71 ACTIVE YES
SQL> shutdown abort
ORACLE instance shut down.
[oracle@chen orcl]$ rm -rf redo03a.log redo03b.log
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 838863760 bytes
Database Buffers 419430400 bytes
Redo Buffers 8859648 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03b.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03a.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;
STATUS
------------
MOUNTED
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: '/u01/app/oracle/oradata/orcl/redo02a.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02b.log'
SQL> alter database clear unarchived logfile group 2;
alter database clear unarchived 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: '/u01/app/oracle/oradata/orcl/redo02a.log'
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02b.log'
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
............
四 ：1 9i 之後版本，刪除當前日誌組
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 1 13 CURRENT NO
2 1 1 11 INACTIVE YES
3 1 1 12 INACTIVE YES
SQL> col member for a40
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
SQL> shutdown immediate
[oracle@chen orcl]$ rm -rf redo01.log
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10804
Session ID: 1 Serial number: 5
[oracle@chen orcl]$ vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10804.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
USER (ospid: 10804): terminating the instance due to error 313
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 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 orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database open;
Database altered.
SQL> ho ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 1 0 UNUSED YES
2 1 1 14 CURRENT NO
3 1 1 12 INACTIVE YES
SQL> alter system switch logfile;
System altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 1 15 CURRENT NO
2 1 1 14 ACTIVE YES
3 1 1 12 INACTIVE YES
四 ：2 9i
在oracle 9i中,可能無法對當前日誌進行clear,需要透過until
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 1 15 CURRENT NO
2 1 1 14 INACTIVE YES
3 1 1 12 INACTIVE YES
[oracle@chen orcl]$ rm -rf redo01.log
SQL> startup ;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 12583
Session ID: 1 Serial number: 5
[oracle@chen orcl]$ vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12583.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
USER (ospid: 12583): terminating the instance due to error 313
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> ho ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
四 ：3
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 1 1 INACTIVE YES
2 1 1 2 INACTIVE YES
3 1 1 3 CURRENT NO
[oracle@chen orcl]$ rm -rf redo03.log
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1269366784 bytes
Fixed Size 2212976 bytes
Variable Size 788532112 bytes
Database Buffers 469762048 bytes
Redo Buffers 8859648 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database until cancel;
ORA-00279: change 1059851 generated at 08/23/2014 23:21:00 needed for thread 1
ORA-00289: suggestion : /home/oracle/archive_1/orcl_1856393447_3.arc
ORA-00280: change 1059851 for thread 1 is in sequence #3
Specify log: {=suggested | filename | AUTO | CANCEL}
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/app/oracle/oradata/orcl/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
SQL> select group#,members,thread#,sequence#,status,archived from v$log;
GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC
---------- ---------- ---------- ---------- ---------------- ---
1 1 1 1 CURRENT NO
2 1 1 0 UNUSED YES
3 1 1 0 UNUSED YES
SQL> col member for a40
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-1314339/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【Oracle日誌】- 日誌檔案重建Oracle
- 日誌檔案和歸檔日誌檔案的關係以及如何切換日誌
- 刪除日誌檔案組與日誌檔案成員
- 管理日誌檔案
- dump日誌檔案
- APACHE日誌檔案Apache
- 控制檔案/歸檔日誌
- 增加日誌檔案，修改當前日誌檔案大小
- 對日誌檔案組和日誌檔案組成員的管理
- 【MySQL日誌】MySQL日誌檔案初級管理MySql
- PeopleSoft日誌檔案清理
- oracle 日誌檔案管理Oracle
- 日誌檔案的管理
- 清空SqlServer日誌檔案SQLServer
- 記錄日誌檔案
- 日誌檔案系統
- 日誌檔案的大小
- 日誌檔案問題
- 新增日誌檔案組與日誌檔案成員
- 備份歸檔日誌檔案
- 日誌檔案和歸檔管理
- 刪除歸檔日誌檔案
- 日誌檔案過大清理
- sybase iq日誌檔案管理
- MySQL InnoDB日誌檔案配置MySql
- 新增、刪除日誌檔案
- Oracle日誌檔案常用操作Oracle
- oracle 關於-日誌檔案Oracle
- 改變日誌檔案大小
- NCR Unix 重要日誌檔案
- RAC的告警日誌檔案
- linux的日誌檔案Linux
- MySQL 日誌檔案 說明MySql
- 轉儲日誌檔案頭
- logmnr挖掘歸檔日誌檔案
- 改變歸檔日誌檔案大小
- 日誌檔案的監測與檔案清理
- Oracle日誌組新增冗餘檔案和日誌組Oracle