dbms_logmnr挖掘归档日志

最新推荐文章于 2024-01-22 09:11:10 发布

baihaisheng

最新推荐文章于 2024-01-22 09:11:10 发布

阅读量554

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： Oracle dbms_logmnr

本文链接：https://blog.youkuaiyun.com/qq_41128383/article/details/86476726

数据库专栏收录该内容

44 篇文章

订阅专栏

本文详细介绍了如何使用Oracle数据库的归档日志，通过SQL查询获取特定时间段内的归档日志信息，包括序列号、注册信息、申请状态及时间戳。进一步展示了如何利用DBMS_LOGMNR过程来解析这些日志，从中抽取与特定数据库表相关的更改记录，并讨论了在解析过程中可能遇到的错误及其解决方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select thread#,
SEQUENCE#,
REGISTRAR,
APPLIED,
to_char(first_TIME, ‘yyyy-mm-dd-hh24:mi:ss’),
to_char(COMPLETION_TIME, ‘yyyy-mm-dd-hh24:mi:ss’)
from v$archived_log
order by first_TIMe;

1 544 ARCH NO 2017-11-29-10:22:13 2017-11-29-10:22:19
1 545 ARCH NO 2017-11-29-10:22:17 2017-11-29-10:25:01

SQL> select name from v$archived_log where sequence#=544;

NAME

+ORADAT/hw/archivelog/2017_11_29/thread_1_seq_544.1016.961323739

SQL> select name from v$archived_log where sequence#=545;

NAME

+ORADAT/hw/archivelog/2017_11_29/thread_1_seq_545.1014.961323901

execute dbms_logmnr.add_logfile(logfilename=>’+ORADAT/hw/archivelog/2017_11_29/thread_1_seq_544.1016.961323739’,options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(logfilename=>’+ORADAT/hw/archivelog/2017_11_29/thread_1_seq_545.1014.961323901’,options=>dbms_logmnr.addfile);
execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
select username,scn,timestamp,sql_redo from v$logmnr_contents where seg_name=upper(‘TEST2’);
execute dbms_logmnr.end_logmnr;

SQL> select count(*) from v$logmnr_contents;

COUNT(*)

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL> select count(*) from v $logmnr_contents; select count(*) from v$ logmnr_contents
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from
v$logmnr_contents