3, the use of Oracle Logminer Demo

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The use of Oracle Logminer Demo

1 Introduction Demo

1.1 Demo assumption

The Oracle LogMiner configuration to use and the use of LogMiner for parsing the log file properties, in this article we use LogMiner to realize data synchronization, from the source to the target database to the target database data synchronization. Because of the LogMiner support is version 8.1 and above, so the Oracle version of the database data synchronization must be 8.1 or above.

Of course, in this paper is the introduction of LogMiner to synchronize data example, can also carry out functions of data auditing, data operation tracking using LogMiner, because these from the operating principle is the same, do not discuss in this paper.

The 1.2 framework map

clip_image002[6]

1.3 flow chart

clip_image002[4]

The L configuration stage

1, The control end: Specifies the source, destination database information, LOGMINER time synchronization configuration information;

L source synchronous data acquisition

2, Console: by periodically polling the way to detect whether the arriving data synchronization time, if the data synchronization is carried out, otherwise continue polling,

3, Source: timing database load database archive log files to dynamic table v$logmnr_contents,

4, The source database: according to the condition reads the specified SQL statement,

L target data.

5, The source database: executing the SQL statement.

The 2 code analysis

2.1 directory and environment configuration

In the Demo project to introduce Oracle JDBC driver package, the project is divided into four categories:

1. Start.java: Program entrance method,

2. SyncTask.java: Data synchronization of Demo core, generating a dictionary file and read the log file, the target database by executing a SQL statement.,

3. DataBase.java: The database operation class,

4. Constants.java: The source database, the target database configuration, the dictionary file and archive file path.

clip_image006[4]

The 2.2 code analysis

2.2.1 Constants.java

The data synchronization to start SCN, the source database configuration, database configuration and object dictionary file / log file path is arranged in the class. Note that there are two users in the source database configuration: one is to call LogMiner the user, the user needs to have the dbms_logmnr, dbms_logmnr_d two permissions in the Demo process, the use of sync; another is LogMiner reads the user operation in the Demo SQL statement, the use is LOGMINER.

package com.constants;

/**
 * [Constants]|Description: the Logminer configuration parameters
 * @Author: ***
 * @Date: 2013-1-15 01:53:57 this afternoon
 * @Revision history:
 */
public class Constants {
    
    /** The last data synchronization in SCN */
    public static String LAST_SCN = "0";

    /** The source database configuration */
    public static String DATABASE_DRIVER="oracle.jdbc.driver.OracleDriver";
    public static String SOURCE_DATABASE_URL="jdbc:oracle:thin:@127.0.0.1:1521:practice";
    public static String SOURCE_DATABASE_USERNAME="sync";
    public static String SOURCE_DATABASE_PASSWORD="sync";
    public static String SOURCE_CLIENT_USERNAME = "LOGMINER";
    
    /** The target database configuration */
    public static String SOURCE_TARGET_URL="jdbc:oracle:thin:@127.0.0.1:1521:target";
    public static String SOURCE_TARGET_USERNAME="target";
    public static String SOURCE_TARGET_PASSWORD="target";
    
    /** The log file path */
    public static String LOG_PATH = "D:\\oracle\\oradata\\practice";
    
    /** Data dictionary path */
    public static String DATA_DICTIONARY = "D:\\oracle\\oradata\\practice\\LOGMNR";
}
2.2.2 SyncTask.java

There are two methods in this class, the first method is createDictionary, for generating the data dictionary file, another is startLogmur, this method is LogMiner analysis of synchronous method.

/**
 * <p>Method name: createDictionary| Description: calling logminer to generate the data dictionary file</p>
 * @param SourceConn source database connection
 * @throws Exception exception information
 */
public void createDictionary(Connection sourceConn) throws Exception{
    String createDictSql = "BEGIN dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'"+Constants.DATA_DICTIONARY+"'); END;";
    CallableStatement callableStatement = sourceConn.prepareCall(createDictSql);
    callableStatement.execute();
}

/**
 * <p>Method name: startLogmur| Description: start logminer analysis </p>
 * @throws Exception
 */
public void startLogmur() throws Exception{
    
    Connection sourceConn = null;
    Connection targetConn = null;
    try {
        ResultSet resultSet = null;
        
        // Access to the source database connection
        sourceConn = DataBase.getSourceDataBase();
        Statement statement = sourceConn.createStatement();
        
        // Add all the log files, online redo log only the code analysis
        StringBuffer sbSQL = new StringBuffer();
        sbSQL.append(" BEGIN");
        sbSQL.append(" dbms_logmnr.add_logfile(logfilename=>'"+Constants.LOG_PATH+"\\REDO01.LOG', options=>dbms_logmnr.NEW);");
        sbSQL.append(" dbms_logmnr.add_logfile(logfilename=>'"+Constants.LOG_PATH+"\\REDO02.LOG', options=>dbms_logmnr.ADDFILE);");
        sbSQL.append(" dbms_logmnr.add_logfile(logfilename=>'"+Constants.LOG_PATH+"\\REDO03.LOG', options=>dbms_logmnr.ADDFILE);");
        sbSQL.append(" END;");
        CallableStatement callableStatement = sourceConn.prepareCall(sbSQL+"");
        callableStatement.execute();
        
        // Analysis of log file information in print
        resultSet = statement.executeQuery("SELECT db_name, thread_sqn, filename FROM v$logmnr_logs");
        while(resultSet.next()){
            System.out.println("The log file has been added==>"+resultSet.getObject(3));
        }
        
        System.out.println("To analyze the log file, the initial SCN number:"+Constants.LAST_SCN);
        callableStatement = sourceConn.prepareCall("BEGIN dbms_logmnr.start_logmnr(startScn=>'"+Constants.LAST_SCN+"',dictfilename=>'"+Constants.DATA_DICTIONARY+"\\dictionary.ora',OPTIONS =>DBMS_LOGMNR.COMMITTED_DATA_ONLY+dbms_logmnr.NO_ROWID_IN_STMT);END;");
        callableStatement.execute();
        System.out.println("To complete the analysis log file");
        
        // Query results
        System.out.println("Query results");
        resultSet = statement.executeQuery("SELECT scn,operation,timestamp,status,sql_redo FROM v$logmnr_contents WHERE seg_owner='"+Constants.SOURCE_CLIENT_USERNAME+"' AND seg_type_name='TABLE' AND operation !='SELECT_FOR_UPDATE'");
        
        // Connection to the target database, the redo statement is executed the target database
        targetConn = DataBase.getTargetDataBase();
        Statement targetStatement = targetConn.createStatement();
        
        String lastScn = Constants.LAST_SCN;
        String operation = null;
        String sql = null;
        boolean isCreateDictionary = false;
        while(resultSet.next()){
            lastScn = resultSet.getObject(1)+"";
            if( lastScn.equals(Constants.LAST_SCN) ){
                continue;
            }
            
            operation = resultSet.getObject(2)+"";
            if( "DDL".equalsIgnoreCase(operation) ){
                isCreateDictionary = true;
            }
            
            sql = resultSet.getObject(5)+"";
            
            // Replace the user
            sql = sql.replace("\""+Constants.SOURCE_CLIENT_USERNAME+"\".", "");
            System.out.println("scn="+lastScn+",Automatic execution of SQL=="+sql+"");
            
            try {
                targetStatement.executeUpdate(sql.substring(0, sql.length()-1));
            } catch (Exception e) {
                System.out.println("Test, has been performed.");
            }
        }
        
        // Update SCN
        Constants.LAST_SCN = (Integer.parseInt(lastScn))+"";
        
        // DDL changes, updates the data dictionary
        if( isCreateDictionary ){
            System.out.println("DDL changes, updates the data dictionary");
            createDictionary(sourceConn);
            System.out.println("To complete the update data dictionary");
            isCreateDictionary = false;
        }
        
        System.out.println("The completion of a work unit");
        
    }
    finally{
        if( null != sourceConn ){
            sourceConn.close();
        }
        if( null != targetConn ){
            targetConn.close();
        }
        
        sourceConn = null;
        targetConn = null;
    }
}

3 operating results

The 3.1 source database operations

1, Create the AAAAA table, and insert data

clip_image008[4]

2, Create table EMP1

clip_image010[4]

3.2 running Demo

The following output log in the console

clip_image012[4]

3.3 the target database results

Create the AAAAA and EMP1 tables, and insert data in AAAAA

clip_image014[4]

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Vivian at December 02, 2013 - 5:37 PM