Oracle Log View __oracle

Source: Internet
Author: User
Oracle Log View

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 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 the only way to analyze the Oracle logs at this point is to use the Logminer provided by Oracle company, because the original log information is not understood at all, Oracle8i later versions of the Logminer, and Logminer is to let us understand the log information tools, through this tool can: identify the logical changes in the database, detect and correct the user's misoperation, the implementation of post audit, the implementation of 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 files are 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 the 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 analysis log file, add 1 at a time advisable
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 inquire 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
Dbms_ LOGMNR through the process. You can narrow the range of log files you want to analyze by setting several different parameters in START_LOGMNR. By setting the start time and termination time parameters, we can limit the logs that analyze only 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 scope of the log to be analyzed 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. Dynamic performance View V$logmnr_contents contains all the information that Logminer analysis obtains.
SELECT Sql_redo from V$logmnr_contents;
If we just want to know what a user is doing with a table, you can get it from the following SQL query, which can get all the work the user db_zgxt on the table SB_DJJL.
Sql> SELECT Sql_redo from v$logmnr_contents WHERE username= ' db_zgxt ' and tablename= ' SB_DJJL ';
It should be emphasized that the results of the analysis in the view v$logmnr_contents only exist during the life of the session we are running ' DBMS_LOGMRN.START_LOGMNR '. This is because all Logminer storage is in PGA memory, all other processes are not visible to it, and as the process ends, the analysis results disappear.
Finally, use the process DBMS_LOGMNR. END_LOGMNR terminates the log parsing transaction, at which point the PGA memory area is cleared and the analysis results are no longer present.
5, view Logminer tool analysis Results
Sql> SELECT * 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 parse 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: DBMS_LOGMNR.START_LOGMNR () must be invoked before selecting from V$logmnr_contents. The following methods can be used:
CREATE TABLE LOGMNR3 as SELECT * from Gv$logmnr_contents;

Oracle Ports: 1521




is stored in the system table
Which group of log files is used by the query system:
SELECT * from V$log;

2. Query the log file for the group you are using:
SELECT * from V$logfile;

3. Force log Switching:
alter system switch logfile;

4. Query history log:
SELECT * from V$log_history;

5. Query Log archive mode:
Select Dbid,name,created,log_mode from V$database;

6. Query the archived log information:
Select Recid,stamp,thread#,sequence#,name from V$archived_log;

7. Add and delete log file groups
ALTER DATABASE Add logfile Group 1 ('/home1/oracle/oradata/ora8i/log1a.log '), '/home2/oracle/oradata/ora8i/log1b.log ' ) Size 100M;

ALTER DATABASE drop logfile Group 1;

8. Add and delete log members
ALTER DATABASE add logfile member '/home1/oracle/oradata/ora8i/log1a.log ' to group 1, '/home1/oracle/oradata/ora8i/ Log2a.log ' to group 2;

ALTER DATABASE drop logfile member '/home1/oracle/oradata/ora8i/log1a.log ';

9. log file Movement
Alter DATABASE rename file '/home1/oracle/oradata/ora8i/log1a.log ' to '/home2/oracle/oradata/ora8i/log1a.log ';
You must ensure that the log file is physically moved to the new directory before executing the command

10. Purge Log files
ALTER DATABASE clear logfile '/home1/oracle/oradata/ora8i/log1a.log ';
This command is used when a log cannot be deleted using the Delete Group and group member commands

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.