http://blog.csdn.net/leshami/article/details/5749556
一、Oracle中的几类日志文件
- Redo log files -->联机重做日志
- Archive log files -->归档日志
- Alert log files -->告警日志
- Trace files -->跟踪日志
- user_dump_dest -->用户跟踪日志
- backupground_dump_dest -->进程跟踪日志
二、联机重做日志的规划管理
1.联机重做日志
记录了数据的所有变化(DML,DDL或管理员对数据所作的结构性更改等)提供恢复机制(对于意外删除或宕机利用日志文件实现数据恢复)可以被分组管理
2.联机重做日志组
由一个或多个相同的联机日志文件组成一个联机重做日志组至少两个日志组，每组一个成员(建议每组两个成员，分散放开到不同的磁盘),由LGWR后台进程同时将日志内容写入到一个组的所有成员
LGWR的触发条件
- 在事务提交的时候(COMMIT)
- Redo Log Buffer 三分之一满
- Redo Log Buffer 多于一兆的变化记录
- 在DBWn写入数据文件之前
3.联机重做日志成员
重做日志组内的每一个联机日志文件称为一个成员
一个组内的每一个成员具有相同的日志序列号(log sequence number),且成员的大小相同
每次日志切换时，Oracle服务器分配一个新的LSN号给即将写入日志的日志文件组
LSN号用于唯一区分每一个联机日志组和归档日志
处于归档模式的联机日志，LSN号在归档时也被写入到归档日志之中
4.日志文件的工作方式
日志文件采用按顺序循环写的方式
当一组联机日志组写满，LGWR则将日志写入到下一组，当最后一组写满则从第一组开始写入
写入下一组的过程称为日志切换
切换时发生检查点过程
检查点的信息同时写入到控制文件
5.联机日志文件的规划
总原则
分散放开，多路复用
日志所在的磁盘应当具有较高的I/O
一般日志组大小应满足自动切换间隔至少15-20分钟左右业务需求
建议使用rdo结尾的日志文件名，避免误删日志文件。如redo1.rdo,redo2.rdo
规划样例
Redo Log Group1 Redo Log Group2 Redo Log Group3
Member1 Member1 Member1 -->Physical Disk 1
Member2 Member2 Member2 -->Physical Disk 2
Member3 Member3 Member3 -->Physical Disk 3
6.日志切换和检查点切换
ALTER SYSTEM SWITCH LOGFILE; --强制手动切换
ALTER SYSTEM CHECKPOINT;
强制设置检查点间隔
ALTER SYSTEM SET FAST_START_MTTR_TARGET = n
7.添加日志文件组
ALTER DATABASE ADD LOGFILE [GROUP n]
('$ORACLE_BASE/oradata/u01/logn1.rdo',
'$ORACLE_BASE/oradata/u01/logn2.rdo')
SIZE mM;
8.添加日志成员
ALTER DATABASE ADD LOGFILE MEMBER
'$ORACLE_BASE/oradata/u01/logn1.rdo' TO GROUP 1,
'$ORACLE_BASE/oradata/u01/logn2.rdo' TO GROUP 2;
9.删除日志成员
不能删除组内的唯一一个成员
不能删除处于active 和current 状态组内的成员
删除处于active 和current 状态组内的成员，应使用日志切换使其处于INACTIVE状态后再删除
对于组内如果一个成员为NULL 值，一个为INVALID，且组处入INACTIVE，仅能删除INVALID状态成员
删除日志成员，物理文件并没有真正删除，需要手动删除
删除日志文件后，控制文件被更新
对于处于归档模式下的数据库，删除成员时确保日志已被归档，查看v$log视图获得归档信息
ALTER DATABASE DROP LOGFILE MEMBER '$ORACLE_BASE/oradata/u01/logn1.rdo'
10.删除日志组
一个实例至少需要两个联机日志文件组
活动或当前的日志组不能被删除
组内成员状态有NULL 值或INVALID状态并存，组不可删除
日志组被删除后，物理文件需要手动删除(对于非OMF)
ALTER DATABASE DROP LOGFILE GROUP n
11.日志的重定位及重命名
所需权限
ALTER DATABASE 系统权限
复制文件到目的位置操作系统权限(写权限)
CURRENT状态组内的成员不能被重命名
建议该行为之前备份数据库
重命名或重定位之后建议立即备份控制文件
重定位及重命名的两种方法
添加一个新成员到日志组，然后删除一个旧的成员
使用ALTER DATABASE RENAME FILE 命令(不区分归档与非归档模式)
复制联机日志文件到新路径:ho cp <oldfile> <newfile>
执行ALTER DATABASE RENAME FILE '<oldfile>' TO '<newfile>'
对于处于CURRENT状态的需要改名且不切换的情况下
办法是切换到MOUNT状态下再执行上述操作
12.清空日志文件组
ALTER DATABASE CLEAR LOGIFLE GROUP n
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP n --使用unarchived 避免归档
13. 查看Oracle日志切换频率
column h0 format 999 column h1 format 999 column h2 format 999 column h3 format 999 column h4 format 999 column h5 format 999 column h6 format 999 column h7 format 999 column h8 format 999 column h9 format 999 column h10 format 999 column h11 format 999 column h12 format 999 column h13 format 999 column h14 format 999 column h15 format 999 column h16 format 999 column h17 format 999 column h18 format 999 column h19 format 999 column h20 format 999 column h21 format 999 column h22 format 999 column h23 format 999 column avg format 999.99 column day format a6 SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", to_char(ROUND (COUNT (1) / 24, 2),'fm99999999990.00') "Avg" FROM gv$log_history WHERE first_time >= trunc(SYSDATE) - 30 and thread# = inst_id GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy') ORDER BY 1 DESC; Date Day Total H0 h1 h2 h3 h4 h5 h6 h7 h8 h9 h10 h11 h12 h13 h14 h15 h16 h17 h18 h19 h20 h21 h22 h23 Avg ------------------- ------ ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --------------------------------------------- 2017-09-14 00:00:00 Thu 6 6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0.25 2017-09-13 00:00:00 Wed 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0 0 0.17
SYS@userdata>select to_char(first_time,'yyyy-mm-dd hh24:mi:ss'),group# from v$log; TO_CHAR(FIRST_TIME,'YYYY-MM-DDHH24:MI:SS') GROUP# --------------------------------------------------------- ---------- 2017-09-14 10:02:55 1 2017-09-14 10:03:01 2 2017-09-14 10:02:28 3
14. 查看与日志有关的动态性能视图
V$LOG
V$LOGFILE
V$LOG中STATUS的状态值
UNUSED: 从未对该联机日志写入任何内容，一般为新增加联机日志文件或是使用resetlog后的状态
CURRENT：当前重做日志文件，表示该重做日志文件为活动状态，能够被打开和关闭
ACTIVE：处于活动状态，不属于当前日志，崩溃恢复需要该状态，可用于块恢复，可能归档，也可能未归档
CLEARING：表示在执行alter database clear logfile命令后正将该日志重建为一个空日志，重建后状态变为unused
CLEARING_CURRENT:当前日志处于关闭线程的清除状态。如日志某些故障或写入新日志标头时发生I/O错误
INACTIVE:实例恢复不在需要联机重做文件日志组，可能归档也可能未归档
V$LOGFILE中STATUS的状态值
INVALID :表明该文件不可访问
STALE :表明文件内容不完全
DELETED : 表明该文件不再使用
NULL ：表明文件正在使用
三、redo log实验
1.添加日志组成员
SYS@userdata>select GROUP#,BYTES/1024/1024 as MB,MEMBERS from v$log; GROUP# MB MEMBERS ---------- ---------- ---------- 1 100 1 2 100 1 3 100 1 SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO SYS@userdata>alter database add logfile member '/u01/app/oracle/oradata/userdata/redo0102.log' to group 1; Database altered. SYS@userdata>alter database add logfile member '/u01/app/oracle/oradata/userdata/redo0202.log' to group 2; Database altered. SYS@userdata>alter database add logfile member '/u01/app/oracle/oradata/userdata/redo0302.log' to group 3; Database altered. SYS@userdata>select GROUP#,BYTES/1024/1024 as MB,MEMBERS from v$log; GROUP# MB MEMBERS ---------- ---------- ---------- 1 100 2 2 100 2 3 100 2 SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO 1 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0102.log NO 2 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0202.log NO 3 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0302.log NO 6 rows selected.
2.添加日志组
SYS@userdata>alter database add logfile group 4 ('/u01/app/oracle/oradata/userdata/redo04.log','/u01/app/oracle/oradata/userdata/redo0402.log') size 100m; Database altered. SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO 1 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0102.log NO 2 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0202.log NO 3 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0302.log NO 4 ONLINE /u01/app/oracle/oradata/userdata/redo04.log NO 4 ONLINE /u01/app/oracle/oradata/userdata/redo0402.log NO 8 rows selected. SYS@userdata>select GROUP#,BYTES/1024/1024 as MB,MEMBERS from v$log; GROUP# MB MEMBERS ---------- ---------- ---------- 1 100 2 2 100 2 3 100 2 4 100 2
3.删除日志组成员
SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 7 104857600 2 YES INACTIVE 1232950 2017-09-14 00:59:10 2 1 8 104857600 2 YES INACTIVE 1232952 2017-09-14 00:59:10 3 1 9 104857600 2 NO CURRENT 1232954 2017-09-14 00:59:12 4 1 0 104857600 2 YES UNUSED 0 SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO 1 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0102.log NO 2 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0202.log NO 3 INVALID ONLINE /u01/app/oracle/oradata/userdata/redo0302.log NO 4 ONLINE /u01/app/oracle/oradata/userdata/redo04.log NO 4 ONLINE /u01/app/oracle/oradata/userdata/redo0402.log NO 8 rows selected. SYS@userdata>alter database drop logfile member '/u01/app/oracle/oradata/userdata/redo0102.log'; Database altered. SYS@userdata>alter database drop logfile member '/u01/app/oracle/oradata/userdata/redo0202.log'; Database altered. SYS@userdata>alter database drop logfile member '/u01/app/oracle/oradata/userdata/redo0402.log'; Database altered. SYS@userdata>alter database drop logfile member '/u01/app/oracle/oradata/userdata/redo0302.log'; alter database drop logfile member '/u01/app/oracle/oradata/userdata/redo0302.log' * ERROR at line 1: ORA-01609: log 3 is the current log for thread 1 - cannot drop members ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/userdata/redo03.log' ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/userdata/redo0302.log' SYS@userdata>alter system switch logfile; System altered. SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 7 104857600 1 YES INACTIVE 1232950 2017-09-14 00:59:10 2 1 8 104857600 1 YES INACTIVE 1232952 2017-09-14 00:59:10 3 1 9 104857600 2 YES ACTIVE 1232954 2017-09-14 00:59:12 4 1 10 104857600 1 NO CURRENT 1234365 2017-09-14 09:58:56 SYS@userdata>alter database drop logfile member '/u01/app/oracle/oradata/userdata/redo0302.log'; Database altered. SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 7 104857600 1 YES INACTIVE 1232950 2017-09-14 00:59:10 2 1 8 104857600 1 YES INACTIVE 1232952 2017-09-14 00:59:10 3 1 9 104857600 1 YES ACTIVE 1232954 2017-09-14 00:59:12 4 1 10 104857600 1 NO CURRENT 1234365 2017-09-14 09:58:56 SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO 4 ONLINE /u01/app/oracle/oradata/userdata/redo04.log NO
4.删除日志组
SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 11 104857600 1 YES ACTIVE 1234551 2017-09-14 10:01:57 2 1 12 104857600 1 YES ACTIVE 1234564 2017-09-14 10:02:17 3 1 13 104857600 1 NO CURRENT 1234569 2017-09-14 10:02:28 4 1 10 104857600 1 YES ACTIVE 1234365 2017-09-14 09:58:56 SYS@userdata>alter database drop logfile group 4; alter database drop logfile group 4 * ERROR at line 1: ORA-01624: log 4 needed for crash recovery of instance userdata (thread 1) ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/userdata/redo04.log' SYS@userdata>alter system switch logfile; System altered. SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 11 104857600 1 YES ACTIVE 1234551 2017-09-14 10:01:57 2 1 12 104857600 1 YES ACTIVE 1234564 2017-09-14 10:02:17 3 1 13 104857600 1 YES ACTIVE 1234569 2017-09-14 10:02:28 4 1 14 104857600 1 NO CURRENT 1234576 2017-09-14 10:02:46 SYS@userdata>alter system switch logfile; System altered. SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 15 104857600 1 NO CURRENT 1234580 2017-09-14 10:02:55 2 1 12 104857600 1 YES INACTIVE 1234564 2017-09-14 10:02:17 3 1 13 104857600 1 YES INACTIVE 1234569 2017-09-14 10:02:28 4 1 14 104857600 1 YES ACTIVE 1234576 2017-09-14 10:02:46 SYS@userdata>alter system switch logfile; System altered. SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 15 104857600 1 YES INACTIVE 1234580 2017-09-14 10:02:55 2 1 16 104857600 1 NO CURRENT 1234582 2017-09-14 10:03:01 3 1 13 104857600 1 YES INACTIVE 1234569 2017-09-14 10:02:28 4 1 14 104857600 1 YES INACTIVE 1234576 2017-09-14 10:02:46 SYS@userdata>alter database drop logfile group 4; Database altered. SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 15 104857600 1 YES INACTIVE 1234580 2017-09-14 10:02:55 2 1 16 104857600 1 NO CURRENT 1234582 2017-09-14 10:03:01 3 1 13 104857600 1 YES INACTIVE 1234569 2017-09-14 10:02:28
CURRENT和ACTIVE状态的不能被删除,且删除组或者删除成员redo log的物理文件并不会被删除.
5.日志文件重命名
这种方式需要重命名的redo log一定是inactive状态
SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 15 104857600 1 YES INACTIVE 1234580 2017-09-14 10:02:55 2 1 16 104857600 1 NO CURRENT 1234582 2017-09-14 10:03:01 3 1 13 104857600 1 YES INACTIVE 1234569 2017-09-14 10:02:28 SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO SYS@userdata>host cp /u01/app/oracle/oradata/userdata/redo03.log /u01/app/oracle/oradata/userdata/redo0301.log SYS@userdata>alter database rename file '/u01/app/oracle/oradata/userdata/redo03.log' to '/u01/app/oracle/oradata/userdata/redo0301.log'; Database altered. SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo0301.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO SYS@userdata>select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------- 1 1 15 104857600 1 YES INACTIVE 1234580 2017-09-14 10:02:55 2 1 16 104857600 1 NO CURRENT 1234582 2017-09-14 10:03:01 3 1 13 104857600 1 YES INACTIVE 1234569 2017-09-14 10:02:28
6.日志文件异常处理
SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/userdata/redo01.log' SYS@userdata>host cat /u01/app/oracle/admin/userdata/bdump/alert_userdata.log ........ Errors in file /u01/app/oracle/admin/userdata/udump/userdata_ora_4755.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/userdata/redo01.log' ORA-27046: file size is not a multiple of logical block size ........ SYS@userdata>select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARCHIVED STATUS ---------- ---------- --------- ------------------------------------------------ 1 15 YES INACTIVE 3 13 YES INACTIVE 2 16 NO CURRENT SYS@userdata>alter database clear logfile group 1; Database altered. SYS@userdata>alter database open; Database altered.
如果是该日志组还没有归档，则需要用SQL>alter database clear unarchived logfile group 3；
7.日志文件组丢失处理
SYS@userdata>select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVE ---------- --------------------- --------------------- -------------------------------------------------- --------- 3 ONLINE /u01/app/oracle/oradata/userdata/redo03.log NO 2 ONLINE /u01/app/oracle/oradata/userdata/redo02.log NO 1 ONLINE /u01/app/oracle/oradata/userdata/redo01.log NO SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>host rm -rf /u01/app/oracle/oradata/userdata/redo01.log SYS@userdata>host ls /u01/app/oracle/oradata/userdata/redo01.log ls: /u01/app/oracle/oradata/userdata/redo01.log: No such file or directory SYS@userdata>startup ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/userdata/redo01.log' SYS@userdata>alter system set "_allow_resetlogs_corruption"=true scope=spfile; System altered. SYS@userdata>recover database using backup controlfile; ORA-00279: change 1238577 generated at 09/14/2017 12:08:09 needed for thread 1 ORA-00289: suggestion : /arch/1_1_954677154.arch ORA-00280: change 1238577 for thread 1 is in sequence #1 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} ORA-00308: cannot open archived log '/arch/1_1_954677154.arch' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SYS@userdata>SYS@userdata>shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SYS@userdata>startup mount; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. SYS@userdata>alter database open resetlogs; Database altered. SYS@userdata>alter system set "_allow_resetlogs_corruption"=false scope=spfile; System altered. SYS@userdata>host ls /u01/app/oracle/oradata/userdata/redo01.log /u01/app/oracle/oradata/userdata/redo01.log SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>startup ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. Database opened.
归档模式不需要加隐藏参数