oracle sqlplus执行脚本_Oracle实时数据采集（二）技术选型

logminer简介

总的说来，logminer工具的主要用途有：
　 1． 跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。
　 2． 回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。
　 3． 优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。

logminer安装与开启归档

安装LogMiner工具

必须首先要运行下面这样两个脚本，
　　l $ORACLE_HOME/rdbms/admin/dbmslm.sql
　　2 $ORACLE_HOME/rdbms/admin/dbmslmd.sql.
　　这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。

开启归档

参见porter用户操作手册-CDC配置文档: https://porter-s.ironz.com/web/#/2?page_id=24

logmienr主要函数及参数说明

类型	过程名	用途
过程	Dbms_logmnr_d.build	创建一个数据字典文件
过程	Dbms_logmnr.add_logfile	在类表中增加日志文件以供分析
过程	Dbms_logmnr.start_logmnr	使用一个可选的字典文件和前面确定要分析日志文件来启动LogMiner
过程	Dbms_logmnr.end_logmnr	停止LogMiner分析

logminer主要表/视图

1、v$loglist 它用于显示历史日志文件的一些信息
2、v$logmnr_dictionary 因logmnr可以有多个字典文件，该视图用于显示这方面信息。
3、v$logmnr_parameters 它用于显示logmnr的参数
4、v$logmnr_logs 它用于显示用于分析的日志列表信息。

5, v$logmnr_contents

Column	Datatype	Description
SCN	NUMBER	System change number (SCN) when the database change was made
CSCN	NUMBER	System change number (SCN) when the transaction committed; only meaningful if theCOMMITTED_DATA_ONLYoption was chosen in aDBMS_LOGMNR.START_LOGMNR()invocation
TIMESTAMP	DATE	Timestamp when the database change was made
COMMIT_TIMESTAMP	DATE	Timestamp when the transaction committed; only meaningful if theCOMMITTED_DATA_ONLYoption was chosen in aDBMS_LOGMNR.START_LOGMNR()invocation
THREAD#	NUMBER	Number of the thread that made the change to the database
LOG_ID	NUMBER	This column is deprecated.
XIDUSN	NUMBER	Transaction ID undo segment number of the transaction that generated the change
XIDSLT	NUMBER	Transaction ID slot number of the transaction that generated the change
XIDSQN	NUMBER	Transaction ID sequence number of the transaction that generated the change
PXIDUSN	NUMBER	Parent transaction ID undo segment number of a parallel transaction
PXIDSLT	NUMBER	Parent transaction ID slot number of a parallel transaction
PXIDSQN	NUMBER	Parent transaction ID sequence number of a parallel transaction
RBASQN	NUMBER	Sequence# associated with the Redo Block Address (RBA) of the redo record associated with the change
RBABLK	NUMBER	RBA block number within the log file
RBABYTE	NUMBER	RBA byte offset within the block
UBAFIL	NUMBER	Undo Block Address (UBA) file number identifying the file containing the undo block
UBABLK	NUMBER	UBA block number for the undo block
UBAREC	NUMBER	UBA record index within the undo block
UBASQN	NUMBER	UBA undo block sequence number
ABS_FILE#	NUMBER	Data block absolute file number of the block changed by the transaction
REL_FILE#	NUMBER	Data block relative file number. The file number is relative to the tablespace of the object
DATA_BLK#	NUMBER	Data block number within the file
DATA_OBJ#	NUMBER	Data block object number identifying the object
DATA_OBJD#	NUMBER	Data block data object number identifying the object within the tablespace
SEG_OWNER	VARCHAR2(32)	Owner of the modified segment
SEG_NAME	VARCHAR2(256)	Name of the modified data segment
TABLE_NAME	VARCHAR2(32)	Name of the modified table (in case the redo pertains to a table modification)
SEG_TYPE	NUMBER	Type of the modified data segment. Possible values are: 0 = UNKNOWN 1 = INDEX 2 = TABLE 19 = TABLE PARTITION 20 = INDEX PARTITION 34 = TABLE SUBPARTITION All other values = UNSUPPORTED
SEG_TYPE_NAME	VARCHAR2(32)	Segment type name. Possible values are:UNKNOWN INDEX TABLE TABLE PARTITION UNSUPPORTED TABLE_SPACE
TABLE_SPACE	VARCHAR2(32)	Name of the tablespace containing the modified data segment. This column is not populated for rows where the value of theOPERATIONcolumn is DDL. This is because DDL may operate on more than one tablespace.
ROW_ID	VARCHAR2(18)	Row ID of the row modified by the change (only meaningful if the change pertains to a DML) This will be NULL if the redo record is not associated with a DML.
SESSION#	NUMBER	Session number of the session that made the change
SERIAL#	NUMBER	Serial number of the session that made the change
USERNAME	VARCHAR2(30)	Name of the user who executed the transaction
SESSION_INFO	VARCHAR2(4000)	Information about the database session that executed the transaction. Contains process information, machine name from which the user logged in etc. A possibleSESSION_INFOcolumn may contain the following:login_username = HR client_info =Machine_name = nirvan OS_terminal = pts/31 OS_program_name = sqlplus@nirvan (TNS V1-V3)
TX_NAME	VARCHAR2(256)	Name of the transaction that made the change. This is only meaningful if the transaction is a named transaction.
ROLLBACK	NUMBER	1 = if the redo record was generated because of a partial or a full rollback of the associated transaction 0 = otherwise
OPERATION	VARCHAR2(32)	User level SQL operation that made the change. Possible values are: INSERT = change was caused by an insert statement UPDATE = change was caused by an update statement DELETE = change was caused by a delete statement DDL = change was caused by a DDL statement START = change was caused by the start of a transaction COMMIT = change was caused by the commit of a transaction ROLLBACK = change was caused by a full rollback of a transaction LOB_WRITE = change was caused by an invocation of DBMS_LOB.WRITE LOB_TRIM = change was caused by an invocation ofDBMS_LOB.TRIM LOB_ERASE = change was caused by an invocation ofDBMS_LOB.ERASE SELECT_FOR_UPDATE = operation was aSELECT FOR UPDATEstatement SEL_LOB_LOCATOR = operation was aSELECTstatement that returns a LOB locator MISSING_SCN = LogMiner encountered a gap in the redo records. This is most likely because not all redo logs were registered with LogMiner. INTERNAL = change was caused by internal operations initiated by the database UNSUPPORTED = change was caused by operations not currently supported by LogMiner (for example, changes made to tables with ADT columns)
OPERATION_CODE	NUMBER	Number of the operation code. Possible values are: 0 = INTERNAL 1 = INSERT 2 = DELETE 3 = UPDATE 5 = DDL 6 = START 7 = COMMIT 9 = SELECT_LOB_LOCATOR 10 = LOB_WRITE 11 = LOB_TRIM 25 = SELECT_FOR_UPDATE 28 = LOB_ERASE 34 = MISSING_SCN 36 = ROLLBACK 255 = UNSUPPORTED
SQL_REDO	VARCHAR2(4000)	Reconstructed SQL statement that is equivalent to the original SQL statement that made the change. Please refer toOracle Database Utilitiesbefore executing SQL_REDO to your database. LogMiner does not generate SQL redo for temporary tables. In such a case, this column will contain the string”/ No SQL_REDO for temporary tables /“.
SQL_UNDO	VARCHAR2(4000)	Reconstructed SQL statement that can be used to undo the effect of the original statement that made the change. DDL statements have no corresponding SQL_UNDO. Please refer toOracle Database Utilitiesbefore executing SQL_UNDO to your database. LogMiner does not generate SQL undo for temporary tables. In such a case, this column will contain the string”/ No SQL_UNDO for temporary tables /“.
RS_ID	VARCHAR2(32)	Record set ID. The tuple (RS_ID, SSN) together uniquely identifies a row inV$LOGMNR_CONTENTS. RS_ID uniquely identifies the redo record that generated the row.
SEQUENCE#	NUMBER	Sequence number of the redo log that contained the redo record corresponding to the database change
SSN	NUMBER	SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a row in theV$LOGMNR_CONTENTSview.
CSF	NUMBER	Continuation SQL flag. Possible values are: 0 = indicates SQL_REDO and SQL_UNDO is contained within the same row 1 = indicates that either SQL_REDO or SQL_UNDO is greater than 4000 bytes in size and is continued in the next row returned by the view
INFO	VARCHAR2(32)	Informational message about the row. For instance, the string “USER DDL” in INFO column indicates that the DDL statement returned in SQL_REDO column was the top-level DDL executed by the user and the string “INTERNAL DDL” in INFO column indicates that DDL statement returned in SQL_REDO column was executed internally by the RDBMS.
STATUS	NUMBER	0 indicates that the reconstructed SQL statements as shown in the SQL_REDO and SQL_UNDO columns are valid executable SQL statements. Otherwise, the reconstructed SQL statements are not executable. This may be due to the fact that no data dictionary was provided to LogMiner for the analysis, or that the data dictionary provided did not have the definition of the object being mined.
REDO_VALUE	NUMBER	Used as input to theDBMS_LOGMNR.MINE_VALUE()andDBMS_LOGMNR.COLUMN_PRESENT()functions
UNDO_VALUE	NUMBER	Used as input to theDBMS_LOGMNR.MINE_VALUE()andDBMS_LOGMNR.COLUMN_PRESENT()functions
SQL_COLUMN_TYPE	VARCHAR2(30)	This column is deprecated.
SQL_COLUMN_NAME	VARCHAR2(30)	This column is deprecated.
REDO_LENGTH	NUMBER	This column is deprecated.
REDO_OFFSET	NUMBER	This column is deprecated.
UNDO_LENGTH	NUMBER	This column is deprecated.
UNDO_OFFSET	NUMBER	This column is deprecated.
DATA_OBJV#	NUMBER	Version number of the table being modified
SAFE_RESUME_SCN	NUMBER	Reserved for future use
XID	RAW(8)	Raw representation of the transaction identifier
PXID	RAW(8)	Raw representation of the parent transaction identifier
AUDIT_SESSIONID	NUMBER	Audit session ID associated with the user session making the change

logminer字典

　 　前面已经谈到，LogMiner工具实际上是由两个新的PL/SQL内建包((DBMSLOGMNR 和 DBMS LOGMNR_D)和四个V$动态性能视图(视图是在利用过程DBMS_LOGMNR.START_LOGMNR启动LogMiner时创建)组成。在使 用LogMiner工具分析redo log文件之前，可以使用DBMS_LOGMNR_D 包将数据字典导出为一个文本文件。该字典文件是可选的，但是如果没有它，LogMiner解释出来的语句中关于数据字典中的部分(如表名、列名等)和数值 都将是16进制的形式，我们是无法直接理解的。例如，下面的sql语句：
　　INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, ‘张三’);
　　LogMiner解释出来的结果将是下面这个样子，
　　insert into Object#308(col#1, col#2) values (hextoraw(‘c30rte567e436’), hextoraw(‘4a6f686e20446f65’));
　 　创建数据字典的目的就是让LogMiner引用涉及到内部数据字典中的部分时为他们实际的名字，而不是系统内部的16进制。数据字典文件是一个文本文 件，使用包DBMS_LOGMNR_D来创建。如果我们要分析的数据库中的表有变化，影响到库的数据字典也发生变化，这时就需要重新创建该字典文件。另外 一种情况是在分析另外一个数据库文件的重作日志时，也必须要重新生成一遍被分析数据库的数据字典文件。

从官方提供的图可知,logminer 基本可分为两类，三种：

Oracle 11g 指定Logminer字典有三种方法

1)使用 online catalog 作为记录数据字典信息的方式进行数据抽取(源库自身的数据字典)；
常用方式,这种方式不需要指定数据字典
2)使用外部数据库已创建过redo log文件中记录数据字典信息的方式进行数据挖掘(外部库的数据字典)；
3)使用外部文件作为数据字典的方式，在其他库中进行数据挖掘(利用外部库的字典文件，需要创建UTL_FILE_DIR)；
　　首先在init.ora初始化参数文件中，指定数据字典文件的位置，也就是添加一个参数UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。如：
　　UTL_FILE_DIR = (e:\Oracle\logs)
　　重新启动数据库，使新加的参数生效，然后创建数据字典文件：
　　SQL> CONNECT SYS
　　SQL> EXECUTE dbms_logmnr_d.build(
　　dictionary_filename => ‘ v816dict.ora’,
　　dictionary_location => ‘e:\oracle\logs’);

如果使用第二种或者第三种外部数据库的方式,当指定redo已经归档，则使用归档，

可使用下面方法确定哪些归档有记录过数据字典信息：

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN=’YES’;

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END=’YES’;

logminer性能指标

为了模拟实际运行环境，加入了Logminer运行背景环境，分别测试数据库在无操作、300个插入/秒操作、500个插入/秒操作情况下运行情况，并且对比日志文件50M、100M大小下运行情况

测试数据如下:

测试结论如下:

a) logminer加载分析过程随机器根据负载不同在6~21秒完成；

b) 加载分析过程并不随日志文件个数增加在时间、CPU、内存有较大变化；

c) 加载分析过程中受分析日志文件个数最大是内存，其次是CPU，耗时应影响较小；

logminer注意事项

我们可以利用logminer日志分析工具来分析其他数据库实例产生的重作日志文件，而不仅仅用来分析本身安装logminer的数据库实例的redo logs文件。使用logminer分析其他数据库实例时，有几点需要注意：

　　1. logminer必须使用被分析数据库实例产生的字典文件，而不是安装logminer的数据库产生的字典文件，另外必须保证安装logminer数据库的字符集和被分析数据库的字符集相同。

　　2. 被分析数据库平台必须和当前logminer所在数据库平台一样，也就是说如果我们要分析的文件是由运行在UNIX平台上的Oracle 9i产生的，那么也必须在一个运行在UNIX平台上的Oracle实例上运行logminer，而不能在其他如Microsoft NT上运行logminer。当然两者的硬件条件不一定要求完全一样。

　　3. logminer日志分析工具仅能够分析Oracle 8以后的产品，对于8以前的产品，该工具也无能为力

logminer简单示例

1, 使用下面SQL选取需要分析的日志

SELECT * FROM V$ARCHIVED_LOG

2, 使用下面SQL执行开启/停止logminer

DECLARE

begin

DBMS_OUTPUT.PUT_LINE(to_char(systimestamp,'hh24:mi:ss.ff')||'-准备添加文件');

DBMS_LOGMNR.ADD_LOGFILE('/u01/app/oracle/backup/archivelog/1_5148_1025536311.dbf');

DBMS_OUTPUT.PUT_LINE(to_char(systimestamp,'hh24:mi:ss.ff')||'-添加文件完成');

dbms_logmnr.start_logmnr(Options=>dbms_logmnr.dict_from_online_catalog);

DBMS_OUTPUT.PUT_LINE(to_char(systimestamp,'hh24:mi:ss.ff')||'-已经调用了start命令');

DBMS_Logmnr.END_LOGMNR();

end;