分类： Oracle
2009-03-20 17:43:45
为了保证有个干净的Archivelog.
SQL> connect as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
测试表数据.
SQL> create table tlogs(id number,name varchar2(1024));
Table created
SQL> insert into tlogs select object_id,object_name from user_objects where rownum<=10;
10 rows inserted
SQL> commit;
Commit complete
SQL> select * from tlogs;
ID NAME
---------- --------------------------------------------------------------------------------
65146 ETL_TRANSFER_FUNCTION
65149 ETL_TRANSFER_FUNCTION
49753 AQ$_AQ_T1_N
49754 SYNC_LOG_SEQ
49756 SYS_IOT_OVER_49755
49755 AQ$_AQ_T1_G
49757 SYS_IOT_TOP_49755
49758 AQ$_AQ_T1_H
49759 SYS_IOT_TOP_49758
49760 AQ$_AQ_T1_I
10 rows selected
SQL> truncate table tlogs;
Table truncated
SQL> select * from tlogs;
ID NAME
---------- --------------------------------------------------------------------------------
SQL>
生成Archivelog.
SQL> alter system switch logfile;
System altered.
查询生成的Archivelog
SELECT * FROM V$ARCHIVED_LOG order by recid desc
571 675443476 C:\SOFTWARE\ORACLE\FLASH_RECOVERY_AREA\TEST2\ARCHIVELOG\2009_01_06\O1_MF_1_348_4P610N0K_.ARC
分析Archivelog
BEGIN
DBMS_LOGMNR.ADD_LOGFILE('C:\SOFTWARE\ORACLE\FLASH_RECOVERY_AREA\TEST2\ARCHIVELOG\2009_01_06\O1_MF_1_348_4P610N0K_.ARC');
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
END;
/
查询结果...
SELECT SCN, SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS ORDER BY SCN;
找到估的truncate 操作
SCN SQL_REDO SQL_UNDO
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
6621359906
truncate table tlogs
;
6621359906 update "SYS"."TAB$" set "DATAOBJ#" = '89769', "TS#" = '4', "FILE#" = '4', "BLOCK update "SYS"."TAB$" set "DATAOBJ#" = '89768', "TS#" = '4', "FILE#" = '4', "BLOCK
6621359906 update "SYS"."SEG$" set "TYPE#" = '5', "BLOCKS" = '8', "EXTENTS" = '1', "INIEXTS update "SYS"."SEG$" set "TYPE#" = '5', "BLOCKS" = '8', "EXTENTS" = '1', "INIEXTS
6621359906 update "SYS"."OBJ$" set "OBJ#" = '89768', "DATAOBJ#" = '89769', "TYPE#" = '2', " update "SYS"."OBJ$" set "OBJ#" = '89768', "DATAOBJ#" = '89768', "TYPE#" = '2', "
6621359906 commit;
6621359906 set transaction read write;
6621359906 commit;
6621359906 set transaction read write;
6621359906 insert into "SYS"."SMON_SCN_TIME"("THREAD","TIME_MP","TIME_DP","SCN_WRP","SCN_BA delete from "SYS"."SMON_SCN_TIME" where "THREAD" = '0' and "TIME_MP" = '67544289
6621359906
6621359906
6621359906 update "SYS"."SMON_SCN_TIME" set "TIME_MP" = '675443231', "TIME_DP" = TO_DATE('2 update "SYS"."SMON_SCN_TIME" set "TIME_MP" = '668776950', "TIME_DP" = TO_DATE('2
6621359906 delete from "SYS"."SMON_SCN_TIME" where "THREAD" = '0' and "TIME_MP" = '66877723 insert into "SYS"."SMON_SCN_TIME"("THREAD","TIME_MP","TIME_DP","SCN_WRP","SCN_BA
执行Redo sql
这个SQL 比较长就省了.
清空 下面的两个池
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
重新查询测试表..
select * from tlogs
ORA-08103: object no longer exists
产生这个错误...
在Matelink上面查询一下是这样描述的这个问题....
Error Description:
This error can occur, if the header block has an invalid block type or data_object_id (seg/obj) stored in the block is different than the data_object_id stored in the segment header. This error can be treated as a block corruption.
如果再重新redo一下上面的SQL...这个表可以查询正常.但是数据没有办法恢复...
