Oracle联机日志文件添加删除记录
//描述联机日志表
SQL> desc v$log
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
BYTES NUMBER
MEMBERS NUMBER
ARCHIVED VARCHAR2(3)
STATUS VARCHAR2(16)
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
//查询联机日志表
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- --------------- ------------- --------------
1 1 37 10485760 2 NO CURRENT 129726 09-12月-09
2 1 36 10485760 2 NO INACTIVE 109595 08-12月-09
//设置显示行大小为120
SQL> set linesize 120
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 37 10485760 2 NO CURRENT 129726 09-12月-09
2 1 36 10485760 2 NO INACTIVE 109595 08-12月-09
//描述联机日志物理文件表
SQL> desc v$logfile
Name Null? Type
---------------------------------------------- --------- -----------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
//格式化输出MEMBER行
SQL> column member format a40
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01A.LOG NO
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01B.LOG NO
2 STALE ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02A.LOG NO
2 STALE ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02B.LOG NO
//切换日志文件
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 37 10485760 2 NO ACTIVE 129726 09-12月-09
2 1 38 10485760 2 NO CURRENT 130021 09-12月-09
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------------ ---
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01A.LOG NO
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01B.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02A.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02B.LOG NO
SQL> column member format a70
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01A.LOG NO
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01B.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02A.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02B.LOG NO
//添加日志文件组
SQL> alter database add logfile group 3
2 'D:/oracle/product/10.1.0/oradata/DB3/redo03a.log' size 10m;
Database altered.
//添加组成员
SQL> alter database add logfile member
2 'D:/oracle/product/10.1.0/oradata/DB3/redo03b.log' to group 3;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01A.LOG NO
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01B.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02A.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02B.LOG NO
3 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO03A.LOG NO
3 INVALID ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO03B.LOG NO
6 rows selected.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 37 10485760 2 NO INACTIVE 129726 09-12月-09
2 1 38 10485760 2 NO CURRENT 130021 09-12月-09
3 1 0 10485760 2 YES UNUSED 0
//进行日切后将新建组改变状态
SQL> alter system switch logfile;
System altered.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 37 10485760 2 NO INACTIVE 129726 09-12月-09
2 1 38 10485760 2 NO ACTIVE 130021 09-12月-09
3 1 39 10485760 2 NO CURRENT 130299 09-12月-09
SQL> select *from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------- ---
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01A.LOG NO
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01B.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02A.LOG NO
2 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO02B.LOG NO
3 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO03A.LOG NO
3 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO03B.LOG NO
6 rows selected.
//删除处于Inactive的日志组
SQL> alter database drop logfile group 2;
Database altered.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------
1 1 37 10485760 2 NO INACTIVE 129726 09-12月-09
3 1 39 10485760 2 NO CURRENT 130299 09-12月-09
SQL> select *from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01A.LOG NO
1 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO01B.LOG NO
3 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO03A.LOG NO
3 ONLINE D:/ORACLE/PRODUCT/10.1.0/ORADATA/DB3/REDO03B.LOG NO