查看日志闪回

1 查看I/O目录 SQL>show parameter utl_file_dir NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ utl_file_dir string SQL> alter system set utl_file_dir='d:/logs' scope=spfile; 系统已更改。 2 重启数据库 SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup ORACLE 例程已经启动。 Total System Global Area 535662592 bytes Fixed Size 1334380 bytes Variable Size 226493332 bytes Database Buffers 301989888 bytes Redo Buffers 5844992 bytes 数据库装载完毕。 数据库已经打开。 3 启动补充日志 SQL> alter database add supplemental log data; 4 生成数据字典文件 SQL> desc dbms_logmnr_d PROCEDURE BUILD 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- DICTIONARY_FILENAME VARCHAR2 IN DEFAULT DICTIONARY_LOCATION VARCHAR2 IN DEFAULT OPTIONS NUMBER IN DEFAULT PROCEDURE SET_TABLESPACE 参数名称 类型 输入/输出默认值? ------------------------------ ----------------------- ------ -------- NEW_TABLESPACE VARCHAR2 IN SQL> execute dbms_logmnr_d.build(DICTIONARY_FILENAME=>'log.ora',DICTIONARY_LOCAT ION=>'d:/logs'); PL/SQL 过程已成功完成。 SQL> desc v$log 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- GROUP# NUMBER THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER MEMBERS NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(16) FIRST_CHANGE# NUMBER FIRST_TIME DATE SQL> select GROUP#,STATUS from v$log 2 ; GROUP# STATUS ---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVE SQL> desc v$logfile 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- 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 --------------------------------------------- 3 F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO03.LOG 2 F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO02.LOG 1 F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO01.LOG SQL> execute dbms_logmnr.ADD_LOGFILE(LOGFILENAME=>'F:/SOFTWARE/ORACLE/ORADATA/OR CL/REDO01.LOG',OPTIONS=>dbms_logmnr.new); PL/SQL 过程已成功完成。 SQL> select SQL_UNDO, SQL_REDO,timestamp,username,SEG_NAME from v$logmnr_conten ts where seg_name='TTEST' and operation='UPDATE'; SQL_UNDO -------------------------------------------------------------------------------- SQL_REDO -------------------------------------------------------------------------------- TIMESTAMP USERNAME -------------- ------------------------------ SEG_NAME -------------------------------------------------------------------------------- update "SCOTT"."TTEST" set "SAL" = '800' where "SAL" = '1000' and ROWID = 'AAARV aAAEAAAAHkAAA'; update "SCOTT"."TTEST" set "SAL" = '1000' where "SAL" = '800' and ROWID = 'AAARV aAAEAAAAHkAAA'; 20-4月 -10 UNKNOWN SQL_UNDO -------------------------------------------------------------------------------- SQL_REDO -------------------------------------------------------------------------------- TIMESTAMP USERNAME -------------- ------------------------------ SEG_NAME -------------------------------------------------------------------------------- TTEST