查看日志闪回

1 查看I/O目录SQL>show parameter utl_file_dirNAME TYPE VALUE------------------------------------ ----------- ------------------------------utl_file_dir stringSQL> alter system set utl_file_dir='d:/logs' scope=spfile;系统已更改。2 重启数据库SQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startupORACLE 例程已经启动。Total System Global Area 535662592 bytesFixed Size 1334380 bytesVariable Size 226493332 bytesDatabase Buffers 301989888 bytesRedo Buffers 5844992 bytes数据库装载完毕。数据库已经打开。3 启动补充日志SQL> alter database add supplemental log data;4 生成数据字典文件SQL> desc dbms_logmnr_dPROCEDURE BUILD参数名称 类型 输入/输出默认值?------------------------------ ----------------------- ------ -------- DICTIONARY_FILENAME VARCHAR2 IN DEFAULT DICTIONARY_LOCATION VARCHAR2 IN DEFAULT OPTIONS NUMBER IN DEFAULTPROCEDURE SET_TABLESPACE参数名称 类型 输入/输出默认值?------------------------------ ----------------------- ------ -------- NEW_TABLESPACE VARCHAR2 INSQL> execute dbms_logmnr_d.build(DICTIONARY_FILENAME=>'log.ora',DICTIONARY_LOCATION=>'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 DATESQL> select GROUP#,STATUS from v$log 2 ; GROUP# STATUS---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVESQL> 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--------------------------------------------- 3F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO03.LOG 2F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO02.LOG 1F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO01.LOGSQL> execute dbms_logmnr.ADD_LOGFILE(LOGFILENAME=>'F:/SOFTWARE/ORACLE/ORADATA/ORCL/REDO01.LOG',OPTIONS=>dbms_logmnr.new);PL/SQL 过程已成功完成。SQL> select SQL_UNDO, SQL_REDO,timestamp,username,SEG_NAME from v$logmnr_contents 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 = 'AAARVaAAEAAAAHkAAA';update "SCOTT"."TTEST" set "SAL" = '1000' where "SAL" = '800' and ROWID = 'AAARVaAAEAAAAHkAAA';20-4月 -10 UNKNOWNSQL_UNDO--------------------------------------------------------------------------------SQL_REDO--------------------------------------------------------------------------------TIMESTAMP USERNAME-------------- ------------------------------SEG_NAME--------------------------------------------------------------------------------TTEST