Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

131

积分

1

好友

13

主题
1#
发表于 2012-2-21 14:17:50 | 查看: 6370| 回复: 6
SQL> select GROUP#  ,THREAD#  ,SEQUENCE#  , MEMBERS   , ARCHIVED ,STATUS   from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1         31          1 YES INACTIVE
         2          1         32          1 NO  CURRENT
         3          1         30          1 YES INACTIVE

SQL> connect clm/clm
Connected.
SQL> delete from test;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> connect /as sysdba
Connected.
SQL> select GROUP#  ,THREAD#  ,SEQUENCE#  , MEMBERS   , ARCHIVED ,STATUS   from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
         1          1         31          1 YES INACTIVE
         2          1         32          1 NO  CURRENT
         3          1         30          1 YES INACTIVE

SQL> desc v$logfile;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GROUP#                                             NUMBER
STATUS                                             VARCHAR2(7)
TYPE                                               VARCHAR2(7)
MEMBER                                             VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

SQL> select  GROUP#      ,MEMBER   from v$logfile;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         1
/home/oracle/app/oradata/ora10/redo01.log

         2
/home/oracle/app/oradata/ora10/redo02.log

         3
/home/oracle/app/oradata/ora10/redo03.log



SQL> execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/app/oradata/ora10/redo02.log',options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.



SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/app/logs/dict.ora');

PL/SQL procedure successfully completed.


SQL> create table te1 as select * from GV$LOGMNR_CONTENTS ;

Table created.

SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

但是现在使用logmnr得到的结果是这样的;
select t.sql_redo,t.sql_undo from te1 t where t.table_name='TEST';

           SQL_REDO        SQL_UNDO
1        insert into "CLM"."TEST"("ID") values ('2');        delete from "CLM"."TEST" where "ID" = '2' and ROWID = 'AAAL5WAAEAAAAAMAAG';


为啥删除了5行数据结果只显示了delete from test where id=2这一条那?
2#
发表于 2012-2-21 14:22:38
难道需要打开附加日志才能显示正确吗?

回复 只看该作者 道具 举报

3#
发表于 2012-2-21 14:22:47
如果没有设置最小附加日志,挖掘信息会有遗漏,详见ORACLE support文档:Effect of Supplemental Logging on LogMiner with Example [ID 750198.1],介绍了最小附加日志对日志挖掘结果的影响。

回复 只看该作者 道具 举报

4#
发表于 2012-2-21 14:24:04
原帖由 aaaaaaaa2000 于 2012-2-21 14:22 发表
难道需要打开附加日志才能显示正确吗?



建议在使用ORACLE提供的功能前,阅读相关配置说明文档。

回复 只看该作者 道具 举报

5#
发表于 2012-2-21 14:43:16
多谢版主的解答!!!!!

回复 只看该作者 道具 举报

6#
发表于 2012-2-21 20:37:40
dbms_logmnr Unsupported SQLREDO

Question:

Try Testing Oracle Logminer



SQL> create table maclean (t1 varchar2(100)) tablespace users;

Table created.

SQL> insert into maclean values (‘MACLEAN’);

1 row created.

SQL> commit;

Commit complete.

after start logmnr:
sql> …add log file ….
sql> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.committed_data_only);
sql> select sql_redo from v$logmnr_contents;
create table maclean (t1 varchar2(100)) tablespace users;
Unsupported
commit;
In the system, some statement will got sqltext from sqlredo column and other is ‘Unsupported’. The got sqltext have INSERT, DELETE, UPDATE; and ‘Unsupported’ sql have INSERT, DELETE, UPDATE too.



Answer:

Have you enabled supplemental logging prior to mining the redo / archive logs ? If not then please enable the supplemental logging.

To enable minimal supplemental logging execute the following SQL statement:
SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

If you had not enabled supplemental logging earlier then the redo logs will not contain sufficient information to mine the logs. Additionally logminer will not always populate all the fields of the v$logmnr_contents this is because the redo may/may not have all the information that we need for every column. Adding Supplemental Logging will help in more info being logged in the redo being generated, helping populate more values.



Exactly, supplemental logging is mandatory, Oracle recommends that you at least enable minimal supplemental logging for LogMiner. By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging prior to generating log files which will be analyzed by LogMiner.
Once supplemental logging is enabled the log file generated later on will be elgible for log miner.



You must enable at least database minimal supplemental logging prior to generate log files which will be analyzed by LogMiner.

http://www.oracledatabase12g.com ... ported-sqlredo.html

回复 只看该作者 道具 举报

7#
发表于 2012-2-23 17:41:48
楼主是你理解错了,没仔细看,真正这个是最后insert 2的语句,也就是抓到是最后的一条增加2的,而不是删除的
而你看到的删除语句,是对应增加的反操作恢复语句。

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-24 00:27 , Processed in 0.048072 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569