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/，如需轉載，請註明出處，否則將追究法律責任。