UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement

UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statement

--在看oracle 10g Administrator's guide中Clearing a Redo Log File这章节时有以下这么一段话

If you want to clear an unarchived redo log that is needed to bring an offline
tablespace online, use the UNRECOVERABLE DATAFILE clause in the ALTER
DATABASE CLEAR LOGFILE statement.
If you clear a redo log needed to bring an offline tablespace online, you will not be able
to bring the tablespace online again. You will have to drop the tablespace or perform
an incomplete recovery. Note that tablespaces taken offline normal do not require
recovery.

感觉有点不对劲,因为我们都知道在tablespace offline时会做checkpoint,所以在online时不会用到redo.
而datafile在offline时与tablespace的区别就是没有做checkpoint,所以在online时要用到redo,所以如果一个被offline过的datafile,如果online时要用到被clear日志文件,
这时是无法clear成功的,一定要加上unrecovery datafile clause.并且在online时要恢复.

Notice:测试表明一个tablespace被offline后,如果再去clear unarchived logfile时也是要用到unrecoverable datafile clsuse的.只是online时不用恢复.

详细测试如下

select">SYS@yxyup>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 253 10485760 1 YES INACTIVE 1115637 2007-09-11 07:52:20
2 1 254 10485760 1 YES INACTIVE 1115663 2007-09-11 07:53:20
3 1 256 10485760 1 NO CURRENT 1122109 2007-09-11 11:08:18
4 1 255 10485760 2 YES ACTIVE 1118604 2007-09-11 09:16:44

alter">SYS@yxyup>alter database datafile '/opt/oracle/oradata/yxyup/yxyup03.dbf' offline;

Database altered.

alter">SYS@yxyup>alter system switch logfile;

System altered.

select">SYS@yxyup>select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 257 10485760 1 NO CURRENT 1122388 2007-09-11 11:13:48
2 1 254 10485760 1 YES INACTIVE 1115663 2007-09-11 07:53:20
3 1 256 10485760 1 YES ACTIVE 1122109 2007-09-11 11:08:18
4 1 255 10485760 2 YES INACTIVE 1118604 2007-09-11 09:16:44

alter">SYS@yxyup>alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-00393: log 3 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/yxyup/redo03.log'
ORA-01110: data file 11: '/opt/oracle/oradata/yxyup/yxyup03.dbf'

alter">SYS@yxyup>alter database clear unarchived logfile group 3 unrecoverable datafile;

Database altered.

SYS@yxyup> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 257 10485760 1 NO CURRENT 1122388 2007-09-11 11:13:48
2 1 254 10485760 1 YES INACTIVE 1115663 2007-09-11 07:53:20
3 1 0 10485760 1 YES UNUSED 1122109 2007-09-11 11:08:18
4 1 0 10485760 2 YES UNUSED 1118604 2007-09-11 09:16:44

alter">SYS@yxyup>alter database datafile '/opt/oracle/oradata/yxyup/yxyup03.dbf' online;
alter database datafile '/opt/oracle/oradata/yxyup/yxyup03.dbf' online
*
ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: '/opt/oracle/oradata/yxyup/yxyup03.dbf'

recover">SYS@yxyup>recover datafile '/opt/oracle/oradata/yxyup/yxyup03.dbf' ;
ORA-00279: change 1122331 generated at 09/11/2007 11:12:55 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelogs/yxyup_1_256_632008925.log
ORA-00280: change 1122331 for thread 1 is in sequence #256

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

SYS@yxyup> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 257 10485760 1 NO CURRENT 1122388 2007-09-11 11:13:48
2 1 254 10485760 1 YES INACTIVE 1115663 2007-09-11 07:53:20
3 1 0 10485760 1 YES UNUSED 1122109 2007-09-11 11:08:18
4 1 0 10485760 2 YES UNUSED 1118604 2007-09-11 09:16:44

SYS@yxyup>

-----tablespace testing------

r">SYS@yxyup>r
1* select * from v$log

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 257 10485760 1 YES INACTIVE 1122388 2007-09-11 11:13:48
2 1 254 10485760 1 YES INACTIVE 1115663 2007-09-11 07:53:20
3 1 258 10485760 1 YES INACTIVE 1122599 2007-09-11 11:21:09
4 1 259 10485760 2 NO CURRENT 1122605 2007-09-11 11:21:24

alter">SYS@yxyup>alter tablespace yxyup offline;

Tablespace altered.

alter">SYS@yxyup>alter system switch logfile;

System altered.

select">SYS@yxyup>select * from v$log ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 257 10485760 1 YES INACTIVE 1122388 2007-09-11 11:13:48
2 1 260 10485760 1 NO CURRENT 1123699 2007-09-11 11:54:45
3 1 258 10485760 1 YES INACTIVE 1122599 2007-09-11 11:21:09
4 1 259 10485760 2 YES ACTIVE 1122605 2007-09-11 11:21:24

alter">SYS@yxyup>alter database clear unarchived logfile group 4;
alter database clear unarchived logfile group 4
*
ERROR at line 1:
ORA-00393: log 4 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 4 thread 1: '/opt/oracle/oradata/yxyup/redo41.log'
ORA-00312: online log 4 thread 1: '/opt/oracle/oradata/yxyup/redo42.log'
ORA-01110: data file 5: '/opt/oracle/oradata/yxyup/yxyup01.dbf'
ORA-01110: data file 10: '/opt/oracle/oradata/yxyup/yxyup02.dbf'
ORA-01110: data file 11: '/opt/oracle/oradata/yxyup/yxyup03.dbf'

alter">SYS@yxyup>alter database clear unarchived logfile group 4 unrecoverable datafile;

Database altered.

alter">SYS@yxyup>alter tablespace yxyup online;

Tablespace altered.

SYS@yxyup>
SYS@yxyup>
select">SYS@yxyup>select * from v$log ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 257 10485760 1 YES INACTIVE 1122388 2007-09-11 11:13:48
2 1 260 10485760 1 NO CURRENT 1123699 2007-09-11 11:54:45
3 1 258 10485760 1 YES INACTIVE 1122599 2007-09-11 11:21:09
4 1 0 10485760 2 YES UNUSED 1122605 2007-09-11 11:21:24

SYS@yxyup>

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

转载于:http://blog.itpub.net/7364032/viewspace-17487/