日志组的3钟状态:
CURRENT:表示LGWR正在写日志文件;
ACTIVE:表示LGWR正在写的文件,实例恢复时需要这个文件;
INACTIVE:表示LGWR正在写的文件,实例恢复时不需要这个文件。
非归档日志下的研究
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select group#,thread#,members,archived,status from v$log;
GROUP# THREAD# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 1 NO CURRENT
2 1 1 NO ACTIVE
3 1 1 NO INACTIVE
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 dg1 (thread 1)
ORA-00312: online log 2 thread 1: '/oracle/oradata/dg1/redo02.log'
SQL> alter database clear unarchived logfile group 2;
为什么需要加“UNARCHIVED”的参数呢?
如果初始化没有归档,那么就需要增加这个参数。所以对ACTIVE的日志组,需要使用“alter database clear unarchive logfile group 2";
如果初始化已经归档,比如日志组在INACTIVE状态时,需要使用"alter database clear logfile group 2"就可以。
日志组由"ACTIVE”转化成“INACTIVE”有2种方法:
1、alter system switch logfile;这样需要切换很多次;
2、alter system checkpiont ;强制日志写入归档;
比如:
SQL> alter system checkpoint;
System altered.
SQL> select group#,thread#,members,archived,status from v$log;
GROUP# THREAD# MEMBERS ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 1 NO INACTIVE
2 1 1 NO CURRENT
3 1 1 NO INACTIVE
两个慎用的参数:ARCHIVE_LAG_TARGET和unrecoverble datafile
ARCHIVE_LAG_TARGET控制日志切换的时间间隔,默认60S到7200S,可以设置切换时间间隔来提高日志切换频率;如果ARCHIVE_LAG_TARGET设置为0的时候,表示不基于日志切换机制。
使用alter database clear logfile group 2 unrecoverable datafile;
这个时候,如果有OFFLINE的表空间,那么这个表空间就不能重新再线。
创建表空间:
SQL> create tablespace test
2 datafile '/oracle/oradata/dg1/test.dbf' size 50M
3 extent management local uniform. size 128K
4 segment space management auto;
Tablespace created.
SQL> create user test identified by test default tablespace test;
User created.
SQL> grant resource,connect to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> create table test (a int);
Table created.
SQL> insert into test values (10);
1 row created.
SQL> select * from test;
A
----------
10
SQL> conn sys/oracle as sysdba
Connected.
SQL> alter tablespace test offline;
Tablespace altered.
onnected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/oracle/oradata/dg1/test.dbf'
SQL> conn sys/oracle as sysdba
Connected.
SQL> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE
5 ONLINE
6 OFFLINE
6 rows selected.
SQL> select group#,thread#,members,status from v$log;
GROUP# THREAD# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1 1 CURRENT
2 1 1 UNUSED
3 1 1 INACTIVE
SQL> alter database clear logfile group 3 unrecoverable datafile;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,thread#,members,status from v$log;
GROUP# THREAD# MEMBERS STATUS
---------- ---------- ---------- ----------------
1 1 1 ACTIVE
2 1 1 ACTIVE
3 1 1 CURRENT
SQL> alter system checkpoint;
System altered.
SQL> alter tablespace test online;
Tablespace altered.
SQL> conn test/test
Connected.
SQL> select * from test;
A
----------
10
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25658563/viewspace-709776/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25658563/viewspace-709776/