【原创】有关logfile遭到破坏的实验

试验前：
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 198 52428800 1 NO CURRENT 4413306467 2007-4-8 22
2 1 197 52428800 1 NO INACTIVE 4413305914 2007-4-8 22
3 1 196 52428800 1 NO INACTIVE 4413305383 2007-4-8 21

[@more@] SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE /u02/data/xts/redo03.log NO
2 ONLINE /u02/data/xts/redo02.log NO
1 ONLINE /u02/data/xts/redo01.log NO

SQL>


下一个redo log 是group 3
删除redo03.log
[oracle@tsxserver xts]$ ls -l *.log
-rw-r----- 1 oracle dba 52429312 Apr 8 22:12 redo01.log
-rw-r--r-- 1 oracle dba 52429312 Apr 8 22:10 redo02.log
-rw-r--r-- 1 oracle dba 52429312 Apr 8 22:10 redo03.log
[oracle@tsxserver xts]$ rm ./redo03.log
[oracle@tsxserver xts]$
[oracle@tsxserver xts]$ ls -l *.log
-rw-r----- 1 oracle dba 52429312 Apr 8 22:12 redo01.log
-rw-r--r-- 1 oracle dba 52429312 Apr 8 22:10 redo02.log
[oracle@tsxserver xts]$

切换log
SQL> alter system switch logfile;

System altered

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 198 52428800 1 NO ACTIVE 4413306467 2007-4-8 22
2 1 197 52428800 1 NO INACTIVE 4413305914 2007-4-8 22
3 1 199 52428800 1 NO CURRENT 4413306806 2007-4-8 22


此时alert log 中没有报错只是写入了
current log# 3 seq# 199 mem# 0 : /u02/data/xts/redo03.log

促使group 1归档
SQL> alter system checkpoint;

System altered

SQL>
SQL>
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 198 52428800 1 NO INACTIVE 4413306467 2007-4-8 22
2 1 197 52428800 1 NO INACTIVE 4413305914 2007-4-8 22
3 1 199 52428800 1 NO CURRENT 4413306806 2007-4-8 22

目前的redo log是redo03.log，执行一些ddl和dml语句
SQL> create table test tablespace users as select * from scott.emp;

Table created

SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

SQL> update test set sal=10000;

14 rows updated

SQL> commit;

Commit complete

SQL>
SQL> select * from test;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 10000.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 10000.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 10000.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 10000.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 10000.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 10000.00 30
7782 CLARK MANAGER 7839 1981-6-9 10000.00 10
7788 SCOTT ANALYST 7566 1987-4-19 10000.00 20
7839 KING PRESIDENT 1981-11-17 10000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 10000.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 10000.00 20
7900 JAMES CLERK 7698 1981-12-3 10000.00 30
7902 FORD ANALYST 7566 1981-12-3 10000.00 20
7934 MILLER CLERK 7782 1982-1-23 10000.00 10

14 rows selected

SQL>


都是成功的，alert log里面没有任何报错

此时再切换日志
SQL> alter system switch logfile;

System altered


如此可以反复切换，就好像redo03.log仍旧存在一样





只有关闭instance之后重启的时候才会报错说找不到redo03.log文件
SQL> startup open;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 75499788 bytes
Database Buffers 83886080 bytes
Redo Buffers 7168000 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u02/data/xts/redo03.log'


此时只需删除日志组3再添加回来就可以了
SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('/u02/data/xts/redo03.log') size 50M;

Database altered.

SQL> alter database open;

Database altered.

SQL>





这和官方材料上说的不太一致
condition: all members of the next group are inaccessible to LGWR at a log switch because of media failure

action:Oracle Database returns an error and the instance shuts down ...
(page 6-5 of managing the redo log of Administrator's Guide 10g)


能解释一下吗？？

这样跟你讲吧，在unix系统下面，如果一个文件正在被用，有open file handler，就算你删除文件都没有影响的，因为file handler已经在内存里面存在了。

据个简单的例子，你发现一个logfile 一直的增长，你的空间不够了，这个时候你要做到的是fuser来找到打开这个文件的process，然后kill process，然后删除文件。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7437037/viewspace-911948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7437037/viewspace-911948/