重庆思庄Oracle、Redhat认证学习论坛

标题: 确认oracle中表被truncate的具体时间 [打印本页]

作者: denglj 时间: 2023-1-5 11:11
标题: 确认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