How to view Oracle logs

Oracle Log View

A Path to Oracle logs:

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 be slightly different)

Control01.ctl example01.dbf redo02.log sysaux01.dbf undotbs01.dbf

Control02.ctl Redo03.log system01.dbf users01.dbf

Control03.ctl Redo01.log shttest.dbf temp01.dbf

Three How to view Oracle logs:

Sql>select * from V$sql (#查看最近所作的操作)

Sql>select * FROMV $sqlarea (#查看最近所作的操作)

All changes to the Oracle database are recorded in the log, and 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, Oracle8i has logminer in subsequent releases, and Logminer is the tool that lets us read the log information, through which we can: identify logical changes to the database, detect and correct user errors, perform post-mortem audits, and perform change analysis.

Four Use of Logminer:

1. Create a 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. For example: Utl_file_dir = ($ORACLE _home\logs), restart the database so that the newly added parameters take effect:

Sql> shutdown;

sql>startup;

2). Then create a data dictionary file
Sql> Connect/as SYSDBA
Sql> Execute dbms_logmnr_d.build (dictionary_filename = ' Dict.ora ', dictionary_location = '/data1/oracle/ Logs ');

PL/SQL procedure successfully completed

2. Create a list of log files to analyze
1). Create an analysis list, which is the log you want to analyze
Sql>execute DBMS Logmnr.add logfile (logfilename = '/data1/oracle/oradata/akazamdb/redo01.log ', Options = Dbms_logmnr.new);
PL/SQL procedure successfully completeds
2). Add the analysis log file, add 1 at a time
Sql>execute dbms_ logmnr.add_ logfile (logfilename = '/data1/oracle/oradata/akazamdb/redo01.log ', Options = DBMS_LOGMNR. AddFile);
PL/SQL procedure successfully completed

3, using Logminer for log analysis (specifically to find out what content can be modified by themselves)
(1) Unrestricted conditions
Sql> EXECUTE DBMS_LOGMNR.START_LOGMNR (
Dictfilename=> '/data1/oracle/logs/v816dict.ora ');
(2) Restricted conditions
Through the process of 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 the operation of a certain user for a table, can be obtained by the following SQL query, the query can get the user db_zgxt to the table SB_DJJL all the work done.
Sql> SELECT Sql_redo from v$logmnr_contents WHERE username= ' db_zgxt ' and tablename= ' SB_DJJL ';
It is important to emphasize that the analysis results in view v$logmnr_contents only exist during the lifetime of the session ' DBMS_LOGMRN.START_LOGMNR ' that 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_callback
gv$logmnr_contents synonym for Gv_$logmnr_contents
Gv$logmnr_dictionary synonym for Gv_$logmnr_dictionary
Gv$logmnr_logfile synonym for Gv_$logmnr_logfile
Gv$logmnr_logs synonym for Gv_$logmnr_logs
Gv$logmnr_parameters synonym for Gv_$logmnr_parameters
gv$logmnr_process synonym for Gv_$logmnr_process
Gv$logmnr_region synonym for Gv_$logmnr_region
Gv$logmnr_session synonym for Gv_$logmnr_session
Gv$logmnr_stats synonym for Gv_$logmnr_stats
Gv$logmnr_transaction synonym for Gv_$logmnr_transaction
V$logmnr_callback synonym for V_$logmnr_callback
v$logmnr_contents synonym for V_$logmnr_contents
V$logmnr_dictionary synonym for V_$logmnr_dictionary
V$logmnr_logfile synonym for V_$logmnr_logfile
V$logmnr_logs synonym for V_$logmnr_logs
V$logmnr_parameters synonym for V_$logmnr_parameters
v$logmnr_process synonym for V_$logmnr_process
V$logmnr_region synonym for V_$logmnr_region
V$logmnr_session synonym for V_$logmnr_session
TABLE_NAME COMMENTS
------------------------------ --------------------------------------------------------------------------------
V$logmnr_stats synonym for V_$logmnr_stats
V$logmnr_transaction synonym for V_$logmnr_transaction
Gv$logmnr_logs is the analysis log list view
Analysis results in the 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 T.sql_redo like ' create% ';
If you cannot query the Gv$logmnr_contents view correctly and report the following error, ORA-01306: You must call DBMS_LOGMNR.START_LOGMNR () before you select from V$logmnr_contents. You can use the following methods:
CREATE TABLE LOGMNR3 as SELECT * from Gv$logmnr_contents;

Oracle Port: 1521

How to view Oracle logs