SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /home/oracle/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /home/oracle/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /home/oracle/app/oracle/oradata/orcl/redo01.log NO
3. 查看重做日志组信息
SQL> select group#,bytes,members from v$log;GROUP# BYTES MEMBERS---------- ---------- ----------1 52428800 12 52428800 13 52428800 1
SQL> select a.group#,a.member,b.status,b.bytes
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER STATUS BYTES
---------- -------------------------------------------------- ---------------- ----------
3 /home/oracle/app/oracle/oradata/orcl/redo03.log CURRENT 52428800
2 /home/oracle/app/oracle/oradata/orcl/redo02.log INACTIVE 52428800
1 /home/oracle/app/oracle/oradata/orcl/redo01.log INACTIVE 52428800
4. 添加重做日志组
每个重做日志组可以添加一个或者多个重做日志文件
语法:
alter database add logfile group 组序号('文件名1','文件名2',......'文件n') size 文件大小;
SQL> alter database add logfile group 4('/home/oracle/app/oracle/oradata/orcl/redo04_01.log',
'/home/oracle/app/oracle/oradata/orcl/redo04_02.log') size 100M;
SQL> select a.group#,a.member,b.bytes/1024/1024 "M",b.status
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER M STATUS
---------- -------------------------------------------------- ---------- ----------------
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 CURRENT
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 INACTIVE
1 /home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE
4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log 100 UNUSED
4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 UNUSED
绿色标记:刚创建的重做日志文件名称,大小,状态。其中文件名为redo04_01.log和red04_02.log,文件大小100M,
状态unused(因为刚创建,重做日志文件从未被使用)
知识扩展:redo四种状态
current:当前活动重做日志,进行实例恢复是必须的!
active:活动的非当前重做日志,实例恢复时会被用到。此状态意味checkpoint尚未完成.
inactive:非活动日志,在实例恢复时不需要,但介质恢复时需要。
unused:日志从未被写入,可能刚被添加或者resetlogs后被重置
5.添加重做日志文件
添加重置日志文件,不需要指定文件大小,新创建的重做日志文件大小与已经存在的重做日志大小一致
语法:
alter database add logfile member '文件名' to group 组序号SQL> alter database add logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_04.log' to group 4;
SQL> select a.group#,a.member,b.bytes/1024/1024,b.statusfrom v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER B.BYTES/1024/1024 STATUS---------- -------------------------------------------------- ----------------- ----------------3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 CURRENT2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 INACTIVE1 /home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log 100 UNUSED4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 UNUSED4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 UNUSED5.删除重做日志文件
不能删除组内唯一成员
重做日志文件被删除,物理文件是没有被删除,需要手动去删除物理文件
如果想删除当前处于current或者active状态的重做日志文件,必须要把当前的重做日志文件切换到inactive状态
语法:
alter database drop logfile member '文件名'
不能删除组内唯一成员
SQL> select a.group#,a.member,b.bytes,b.status,b.archived
from v$logfile a, v$log b
where a.group#=b.group#;
GROUP# MEMBER BYTES STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 52428800 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 52428800 INACTIVE YES
1 /home/oracle/app/oracle/oradata/orcl/redo01.log 52428800 CURRENT NO
4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log 104857600 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 104857600 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log 104857600 INACTIVE YES
删除重做日志状态为current,会提示错误信息
SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo03.log';
alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo03.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /home/oracle/app/oracle/oradata/orcl/redo03.log for group 3
如果想删除当前处于current或者active状态的重做日志文件,必须要把当前的重做日志文件切换到inactive状态
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,a.member,b.bytes/1024/1024,b.status,b.archived
2 from v$logfile a,v$log b
3 where a.group#=b.group#;
GROUP# MEMBER BYTES STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 52428800 ACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 52428800 INACTIVE YES
1 /home/oracle/app/oracle/oradata/orcl/redo01.log 52428800 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log 104857600 CURRENT NO
4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 104857600 CURRENT NO
4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log 104857600 CURRENT NO
如果删除重做日志处于current或者active状态,会报 the current log for thread 1 - cannot drop members
SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_01.log';
alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_01.log'
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo04_01.log'
ORA-00312: online log 4 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo04_02.log'
ORA-00312: online log 4 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo04_04.log'
重做日志文件被删除,物理文件是没有被删除,需要手动去删除物理文件
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,a.member,b.bytes/1024/1024 "M", b.status,b.archived
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER M STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
1 /home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_01.log 100 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 INACTIVE YES
SQL> alter database drop logfile member '/home/oracle/app/oracle/oradata/orcl/redo04_01.log';
Database altered.
[oracle@localhost orcl]$ ls -ls|grep redo
51260 -rw-r----- 1 oracle oinstall 52429312 12-10 14:16 redo01.log
51260 -rw-r----- 1 oracle oinstall 52429312 12-10 14:19 redo02.log
51260 -rw-r----- 1 oracle oinstall 52429312 12-10 14:11 redo03.log
102508 -rw-r----- 1 oracle oinstall 104858112 12-10 14:16 redo04_01.log
102508 -rw-r----- 1 oracle oinstall 104858112 12-10 14:16 redo04_02.log
102508 -rw-r----- 1 oracle oinstall 104858112 12-10 13:29 redo04_03.log
102508 -rw-r----- 1 oracle oinstall 104858112 12-10 14:16 redo04_04.log
需要手动删除物理文件哦!
rm -rf /home/oracle/app/oracle/oradata/orcl/redo04_01.log
6.删除重做日志组
一个实例必须要两组重做日志,如果重做日志组只有两组,不能被删除
删除重做日志组,需要手动删除物理文件(非OMF)
重做日志组内有重做日志文件处于current或者active状态,不能删除重做日志组
如果重做日志组内重做日志文件有null状态和inavtive状态并存,只能删除重做日志文件状态是inactive文件
语法:
alter database drop logfile group 组序号
重做日志组内有重做日志文件处于current或者active状态,不能删除重做日志组
SQL> select a.group#,a.member,b.bytes/1024/1024 "M", b.status,b.archived
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER M STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
1 /home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 INACTIVE YES
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo02.log'
删除重做日志组,需要手动删除物理文件(非OMF)
SQL> select a.group#,a.member,b.bytes/1024/1024 "M", b.status,b.archived
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER M STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
1 /home/oracle/app/oracle/oradata/orcl/redo01.log 50 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_02.log 100 INACTIVE YES
4 /home/oracle/app/oracle/oradata/orcl/redo04_04.log 100 INACTIVE YES
SQL> alter database drop logfile group 4 ;
Database altered.
需要手动删除物理文件哦
[oracle@localhost orcl]$ ls -ls|grep redo
51260 -rw-r----- 1 oracle oinstall 52429312 12-10 14:16 redo01.log
51260 -rw-r----- 1 oracle oinstall 52429312 12-10 14:32 redo02.log
51260 -rw-r----- 1 oracle oinstall 52429312 12-10 14:11 redo03.log
102508 -rw-r----- 1 oracle oinstall 104858112 12-10 14:16 redo04_02.log
102508 -rw-r----- 1 oracle oinstall 104858112 12-10 14:16 redo04_04.log
一个实例必须要两组重做日志
SQL> select a.group#,a.member,b.bytes/1024/1024 "M", b.status,b.archived
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER M STATUS ARC
---------- -------------------------------------------------- ---------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
一个实例必须要有两组重做日组,如果删除其中组就会报would leave less than 2 log files for instance orcl (thread 1)
SQL> alter database drop logfile group 3 ;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance orcl (thread 1)
ORA-00312: online log 3 thread 1: '/home/oracle/app/oracle/oradata/orcl/redo03.log'
6.清空重做日志组
清空重做日志:对重做日志文件内容全部初始化,相当于删除重做日志文件,并重新创建重做日志文件一个实例必须要两组重做日志,如果重做日志组只有两组,不能被被清空
重做日志组内有重做日志文件处于current或者active状态,不能清空重做日志组
SQL> select a.group#,a.member,b.bytes/1024/1024,b.status,b.archived
from v$logfile a,v$log b
where a.group#=b.group#;
GROUP# MEMBER B.BYTES/1024/1024 STATUS ARC
---------- -------------------------------------------------- ----------------- ---------------- ---
3 /home/oracle/app/oracle/oradata/orcl/redo03.log 50 INACTIVE YES
2 /home/oracle/app/oracle/oradata/orcl/redo02.log 50 CURRENT NO
5 /home/oracle/app/oracle/oradata/orcl/redo05_01.log 100 UNUSED YES
6 /home/oracle/app/oracle/oradata/orcl/redo06_01.log 100 INACTIVE NO清空重做日志组:
SQL> alter database clear logfile group 3;
清空重做日志文件
SQL> alter database clear logfile '/home/oracle/app/oracle/oradata/orcl/redo05_01.log';
如果重做日志处于未归档状态,必须使用unarchived 进行清空
SQL> alter database clear unarchived logfile group 6;
Database altered.
6.与重做日志有关的动态视图
v$log
v$logfile
v$log_history
v$log视图中重要的字段
Status of the log member:
UNUSED
- Online redo log has never been written to. This is the state of a redo log that was just added, or just after aRESETLOGS
, when it is not the current redo log.CURRENT
- Current redo log. This implies that the redo log is active. The redo log could be open or closed.ACTIVE
- Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.CLEARING
- Log is being re-created as an empty log after anALTER DATABASE CLEAR LOGFILE
statement. After the log is cleared, the status changes toUNUSED
.CLEARING_CURRENT
- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.INACTIVE
- Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
v$logfile视图中重要的字段:Status of the log member:
INVALID
- File is inaccessibleSTALE
- File's contents are incompleteDELETED
- File is no longer usednull - File is in use