Oracle 日志文件

博主： Tehran
发布时间：2022 年 02 月 19 日
291 次浏览
暂无评论
22838字数
分类： Oracle

增加日志成员

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个。
[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_

二：：丢失的是非当前活动（INACTIVE ）日志

1 如果日志已经归档（YES），可以直接使用alter database clear logfile group x;来重建日志文件；
2 如果日志文件没有归档（NO）,可以使用alter database clear unarchived logfile group x;强行clear，来重建日志文件即可；
有归档的日志组建议在操作后立即对数据库执行新的完全备份，因为日志已丢失，所有日志丢失之前的恢复将失效。

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

三：丢失当前活动（ACTIVE ）日志

：同方法“二”

四：如果丢失的是当前日志, 当数据库是开启，恢复方法同方法“二”；

SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_

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_

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_

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_

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

最后修改：2022 年 02 月 27 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
# 请输入真实的邮箱地址便于接收回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

Oracle 日志文件

Tehran • 2022 年 02 月 19 日

<div class="tip share">请注意，本文编写于 796 天前，最后修改于 788 天前，其中某些信息可能已经过时。</div>

<h2>增加日志成员</h2>SQL&gt; alter database drop logfile group 1;-----删除之前确保group 1不是当前组，并且处于INACTIVE状态 Database altered. SQL&gt; 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&gt; alter database drop logfile group 3; Database altered. SQL&gt; 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&gt; alter system switch logfile; System altered. SQL&gt; 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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 15 CURRENT NO 2 1 1 14 ACTIVE YES 3 2 1 0 UNUSED YES SQL&gt; alter system switch logfile; System altered. SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 18 CURRENT NO 2 1 1 17 INACTIVE YES 3 2 1 16 INACTIVE YES SQL&gt; alter database drop logfile group 2; Database altered. SQL&gt; 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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 18 CURRENT NO 2 2 1 0 UNUSED YES 3 2 1 16 INACTIVE YES SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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.<h2>联机重做日志状态</h2>日志文件组的状态一般有INACTIVE、ACTIVE、CURRENT、UNUSED、CLEARING、CLEARING_CURRNT等六种状态： SQL&gt; 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 ）<pre><code>SQL&gt; 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&gt; alter system checkpoint local; --------手动产生检查点，使ACTIVE状态变为INACTIVE;
System altered.
SQL&gt; 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</code></pre><h2>日志文件丢失恢复</h2><h3>数据库打开状态下</h3><h4>一：：丢失某个日志组中的某个member</h4>这种情况在日志多路复用的情况下是不影响数据库使用的，所以建议日志组的成员数至少2个。 [oracle@chen ~]$ cd /u01/app/oracle/oradata/orcl/ [oracle@chen orcl]$ rm -rf redo02a.log SQL&gt; shutdown immediate; SQL&gt; startup SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 18 INACTIVE YES 2 2 1 19 INACTIVE YES 3 2 1 20 CURRENT NO SQL&gt; select * from v$logfile; SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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&gt; alter system switch logfile; System altered. SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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.<h4>二：：丢失的是非当前活动（INACTIVE ）日志</h4>1 如果日志已经归档（YES），可以直接使用alter database clear logfile group x;来重建日志文件； 2 如果日志文件没有归档（NO）,可以使用alter database clear unarchived logfile group x;强行clear，来重建日志文件即可； 有归档的日志组建议在操作后立即对数据库执行新的完全备份，因为日志已丢失，所有日志丢失之前的恢复将失效。SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>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&gt; alter system switch logfile; System altered. SQL&gt; alter system switch logfile; 。。。。。。。。。 在归档模式下，丢失了非CURRENT日志组，这会在日志切换时因无法归档导致数据库hang住 SQL&gt; ctrl+c SQL&gt; 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&gt; 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&gt; alter database clear unarchived logfile group 1; Database altered. SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 0 UNUSED YES 2 2 1 40 INACTIVE NO 3 2 1 41 CURRENT NO SQL&gt; alter system switch logfile; SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 42 CURRENT NO 2 2 1 40 INACTIVE YES 3 2 1 41 ACTIVE YES System altered. SQL&gt; 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<h4>三 ：丢失当前活动（ACTIVE ）日志</h4>：同方法“二”<h4>四 ：如果丢失的是当前日志, 当数据库是开启，恢复方法同方法“二”；</h4>SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>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&gt; alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL&gt; 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&gt; 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&gt; alter database clear unarchived logfile group 2; Database altered. SQL&gt; 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&gt; alter system switch logfile; System altered. SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 69 ACTIVE YES 2 2 1 70 CURRENT NO 3 2 1 68 ACTIVE YES<h3>数据库关闭状态下</h3>一 ：丢失某个日志组中的某个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&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 69 INACTIVE YES 2 2 1 70 CURRENT NO 3 2 1 68 INACTIVE YES SQL&gt; shutdown immediate [oracle@chen orcl]$ rm -rf redo03a.log redo03b.log ---删除INACTIVE,YES SQL&gt; 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&gt; 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&gt; alter database clear logfile group 3; Database altered. SQL&gt; alter database open; Database altered. SQL&gt; 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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 69 INACTIVE YES 2 2 1 70 CURRENT NO 3 2 1 0 UNUSED YES 三：1 数据库正常关闭 SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>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&gt; 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&gt; 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&gt; alter database clear logfile group 2; Database altered. SQL&gt; alter database open; Database altered. 三 ：2 数据库意外关闭 SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 2 1 69 INACTIVE YES 2 2 1 72 CURRENT NO 3 2 1 71 ACTIVE YES SQL&gt; shutdown abort ORACLE instance shut down. [oracle@chen orcl]$ rm -rf redo03a.log redo03b.log SQL&gt; 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&gt; select status from v$instance; STATUSMOUNTED SQL&gt; 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&gt; 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&gt; alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open ............ 四 ：1 9i 之后版本，删除当前日志组 SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 1 1 13 CURRENT NO 2 1 1 11 INACTIVE YES 3 1 1 12 INACTIVE YES SQL&gt; col member for a40 SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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&gt; shutdown immediate [oracle@chen orcl]$ rm -rf redo01.log SQL&gt; 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&gt; 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&gt; 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&gt; alter database clear unarchived logfile group 1; Database altered. SQL&gt; alter database open; Database altered. SQL&gt; ho ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 1 1 0 UNUSED YES 2 1 1 14 CURRENT NO 3 1 1 12 INACTIVE YES SQL&gt; alter system switch logfile; System altered. SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>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&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>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&gt; 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&gt; 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&gt; recover database until cancel; Media recovery complete. SQL&gt; alter database open resetlogs; Database altered. SQL&gt; ho ls control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf 四 ：3 SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>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&gt; shutdown abort ORACLE instance shut down. SQL&gt; 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&gt; select status from v$instance; STATUSMOUNTED SQL&gt; alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SQL&gt; shutdown immediate; SQL&gt; startup mount; SQL&gt; 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&gt; alter database open resetlogs; Database altered. SQL&gt; select group#,members,thread#,sequence#,status,archived from v$log; GROUP# MEMBERS THREAD# SEQUENCE# STATUS ARC<hr>1 1 1 1 CURRENT NO 2 1 1 0 UNUSED YES 3 1 1 0 UNUSED YES SQL&gt; col member for a40 SQL&gt; select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_<hr>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

Oracle 日志文件

增加日志成员

联机重做日志状态