重庆思庄oracle技术分享-确认oracle中表被truncate的具体时间

文档课题：确认oracle中表被truncate的具体时间.

数据库：oracle11.2.0.4

1、场景准备

SYS@orcl>create table leo.tspitr_emp tablespace leo_ts as select * from scott.emp;

Tablecreated.

SYS@orcl10:19:45> select * from v$log;

    GROUP#   THREAD#  SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARCSTATUS           FIRST_CHANGE# FIRST_TINEXT_CHANGE# NEXT_TIM

-------------------- ---------- ---------- ---------- ---------- --- ----------------------------- -------- ------------ --------

         1          1         74  52428800        512          1 YES ACTIVE                 3234061 09:45:08      3235739 10:19:30

         2          1         72  52428800        512          1 YES INACTIVE               3229699 09:19:29      3234056 09:44:59

         3          1         73  52428800        512          1 YES INACTIVE               3234056 09:44:59      3234061 09:45:08

        11          1         75  52428800        512          2 NO CURRENT                323573910:19:30   2.8147E+14

        12          1         68  52428800        512          2 YES INACTIVE               3193114 11:27:02      3200620 14:51:46

        13          1         69  52428800        512          2 YES INACTIVE               3200620 14:51:46      3206240 17:16:24

        14          1         70  52428800        512          2 YES INACTIVE               3206240 17:16:24      3206704 17:29:54

        15          1         71  52428800        512          2 YES INACTIVE               3206704 17:29:54      3229699 09:19:29

8 rowsselected.

SYS@orcl10:19:55> truncate table leo.tspitr_emp;

Tabletruncated.

2、truncate时间确认2.1、logminer方案

说明：由于测试库redo较少，从v$log判断truncate操作在75号日志中.此处采用logminer找回truncate误操作的具体时间.生产环境恢复时需确认到准确的时间点，以减少数据的丢失.

--确认该时间段的归档日志，其实此处可以直接分析redo日志，并不一定非要分析归档日志.

SYS@orcl10:47:57> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",NAME fromv$archived_log where first_time between to_date('2022-12-24 10','yyyy-mm-ddhh24') and to_date('2022-12-24 11','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

no rowsselected

SYS@orcl10:56:34> alter system switch logfile;

Systemaltered.

SYS@orcl10:57:03> col name for a60

SYS@orcl10:57:28> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') asTIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",NAME fromv$archived_log where first_time between to_date('2022-12-24 10','yyyy-mm-ddhh24') and to_date('2022-12-24 11','yyyy-mm-dd hh24') and dest_id=1 group byfirst_time,name,sequence# order by 1 desc;

SEQUENCE# TIME                   Size(M) NAME

----------------------------- ----------------------------------------------------------------------

        75 2022-12-24 10:19:30          1/u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf

--进行归档日志分析.

SYS@orcl10:57:29> executedbms_logmnr.add_logfile('/u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf',dbms_logmnr.new);

PL/SQLprocedure successfully completed.

SYS@orcl11:03:41> execdbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQLprocedure successfully completed.

SYS@orcl11:06:57> col sql_redo for a50

SYS@orcl11:07:12> select a.scn,a.timestamp,a.sql_redo from v$logmnr_contents a wheretable_name='TSPITR_EMP' and operation='DDL' order by a.scn;

       SCN TIMESTAM SQL_REDO

------------------ --------------------------------------------------

   3235800 10:20:54 truncate tableleo.tspitr_emp;

说明：由此确认到执行truncate的时间为10:20:54.

2.2、dba_objects方案

也可以通过视图dba_objects中的LAST_DDL_TIME字段来确认truncate时间,如下所示：

SYS@orcl10:39:07> select owner,object_name,object_type,to_char(created,'yyyy-mm-ddhh24:mi:ss'),to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from dba_objectswhere object_name='TSPITR_EMP';

OWNER      OBJECT_NAME     OBJECT_TYPE         TO_CHAR(CREATED,'YYTO_CHAR(LAST_DDL_TI

------------------------- ------------------- ------------------- -------------------

LEO        TSPITR_EMP      TABLE               2022-12-24 09:51:32 2022-12-2410:20:54

2.3、trace日志

--也可以在trace日志中查看到具体的truncate时间.

Sat Dec24 10:20:54 2022

truncatetable leo.tspitr_emp

相关视图：

dba_tab_modifications中timestamp字段.