A Path to Oracle log: Login: Sqlplus "/as sysdba" View path:sql> select * from V$logfile; Sql> select * from V$logfile; (#日志文件路径) two. What the Oracle log file contains: (The number of logs may vary slightly) Control01.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbfcontrol02.ctl Redo03.log system01.dbf users01.dbfcontrol03.ctl redo01.log shttest.dbf temp01.dbf three. Oracle Log Viewing Method: Sql>select * from V$sql (#查看最近所作的操作) sql>select * fromv $sqlarea (#查看最近所作的操作) All changes to the Oracle database are recorded in the log. For the time being, the only way to analyze Oracle logs is to use the Logminer provided by Oracle, because the original log information is not understood at all, and Oracle8i comes with Logminer in subsequent releases. And Logminer is the tool that allows us to read the log information through this tool: to find out the logical changes of the database, to detect and correct the user's misoperation, perform the post-mortem audit, perform the change analysis. Four Logminer use: 1, create data dictionary file (data-dictionary) 1). First, in the Init.ora initialization parameter file, add a parameter Utl_file_dir, which is the directory where the data dictionary file is placed in the server. such as: Utl_file_dir = ($ORACLE _home\logs), restart the database, so that the newly added parameters to take effect:sql> shutdown; SQL>STARTUP;2). Then create a data dictionary file sql> connect/as sysdbasql> execute dbms_logmnr_d.build (dictionary_filename = ' Dict.ora ', dictionary_location = '/data1/oracle/logs ');P l/sql procedure successfully completed2, creating a list of log files to analyze 1). Create an analysisList, which is the log to be analyzed Sql>execute DBMS logmnr.add logfile (logfilename = '/data1/oracle/oradata/akazamdb/redo01.log ', Options = dbms_logmnr.new);P l/sql procedure successfully completeds2). Add Analysis log files, add 1 at a time Sql>execute Dbms_ Logmnr.add_ logfile (logfilename = '/data1/oracle/oradata/akazamdb/redo01.log ', Options = DBMS_LOGMNR. AddFile);P L/sql procedure successfully completed3, using Logminer for log analysis (specifically to query what can be modified by itself) (1) No restrictions sql> EXECUTE dbms_ LOGMNR.START_LOGMNR (dictfilename=> '/data1/oracle/logs/v816dict.ora '); (2) There are restrictions on the process Dbms_ LOGMNR. START_LOGMNR the settings of several different parameters, you can narrow the range of log files to be analyzed. By setting the start time and end time parameters we can limit the log that only analyzes a certain time range. As the following example, we only analyze the September 18, 2007 Log:sql> EXECUTE dbms_logmnr.start_logmnr (dictfilename = '/data1/oracle/logs/ V816dict.ora ', StartTime = to_date (' 2007-9-18 00:00:00 ', ' yyyy-mm-dd HH24:MI:SS ') EndTime = to_date (' 2007-9-18 23:59:59 ', ' yyyy-mm-dd HH24:MI:SS '); You can also limit the range of logs that you want to analyze by setting the start SCN and up to SCN:sql> EXECUTE DBMS_LOGMNR.START_LOGMNR ( Dictfilename = '/data1/oracle/logs/v816dict.Ora ', STARTSCN = 20,ENDSCN = 50); 4. Observation and Analysis Results (v$logmnr_contents) So far, we have analyzed the contents of the Redo log file. The dynamic performance View v$logmnr_contents contains all the information that the Logminer analysis obtains. SELECT Sql_redo from v$logmnr_contents; if we just want to know what a user is doing with a table, it can be obtained from the following SQL query, which can get the user db_zgxt all the work done on the table SB_DJJL. Sql> SELECT Sql_redo from v$logmnr_contents WHERE username= ' db_zgxt ' and tablename= ' SB_DJJL '; one thing to emphasize is that the view V$logmnr_ The results of the analysis in contents only exist during the lifetime of the session ' DBMS_LOGMRN.START_LOGMNR ' we are running. This is because all Logminer storage is in PGA memory, and all other processes are not visible, and as the process ends, the results of the analysis disappear. Finally, use the procedure DBMS_LOGMNR. END_LOGMNR terminates the log parsing transaction, at which time the PGA memory area is cleared and the results of the analysis are no longer present. 5. View Logminer Tool Analysis Results sql> SELECT * from Dict t where t.table_name like '%logmnr% ';-See all LOGMNR related views table_name COMMENTS------------------------------------------------------------------------------------------------------- -------gv$logmnr_callback synonym for gv_$logmnr_callbackgv$logmnr_contents synonym for GV_$LOGMNR_CONTENTSGV$LOGMNR _dictionary synonym for gv_$logmnr_dictionarygv$logmnr_logfile synonym for Gv_$logmnr_logfilegv$logmnr_logs Synonym for gv_$logmnr_logsgv$logmnr_parameters synonym for gv_$logmnr_parametersgv$logmnr_process Synonym for GV_$LOGMNR_ processgv$logmnr_region synonym for gv_$logmnr_regiongv$logmnr_session synonym for Gv_$logmnr_sessiongv$logmnr_stats Synonym for gv_$logmnr_statsgv$logmnr_transaction synonym for Gv_$logmnr_transactionv$logmnr_callback Synonym for V_$ logmnr_callbackv$logmnr_contents synonym for V_$logmnr_contentsv$logmnr_dictionary Synonym for V_$LOGMNR_DICTIONARYV $LOGMNR _logfile synonym for v_$logmnr_logfilev$logmnr_logs synonym for V_$logmnr_logsv$logmnr_parameters Synonym for V_ $LOGMNR _parametersv$logmnr_process synonym for v_$logmnr_processv$logmnr_region synonym for V_$LOGMNR_REGIONV$LOGMNR _session synonym for V_$logmnr_sessiontable_name COMMENTS--------------------------------------------------------- -----------------------------------------------------v$logmnr_stats synonym for V_$logmnr_statsv$logmnr_ TRANSACTION synonym for v_$logmnr_transactiongv$logmnr_logs is the analysis Log List view analysis results in GV$LOGMNR_contents view, you can query by the following statement: Select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd# , Session#,serial#,username,session_info,sql_redo,sql_undo from Logmnr3 t where to t.sql_redo like ' create% '; If you can't query it correctly gv$ Logmnr_contents view, and reports the following error, ORA-01306: You must call DBMS_LOGMNR.START_LOGMNR () before selecting from V$logmnr_contents. The following methods can be used: CREATE TABLE LOGMNR3 as SELECT * from gv$logmnr_contents;
Oracle Log View
