Detailed example of Oracle data synchronization migration through LogMiner

This article brings you relevant knowledge about Oracle, which mainly introduces the issues related to Oracle data synchronization migration through LogMiner. Here is an introduction to how to achieve Oracle data synchronization. ,I hope everyone has to help.

Recommended tutorial: "Oracle Tutorial"

Written in front

Recently studying how to To achieve data synchronization between Oracle databases, there is indeed relatively little information on the Internet. The best Oracle database synchronization tool is: GoldenGate, and GoldenGate is charged. So is there any good way to synchronize Oracle data? That's right, LogMiner is used to achieve synchronous migration of Oracle data.

Implementation process

1. Create directory

Create /home/oracle/tools/oracle11g/oradata/orcl/ on the server LOGMNR directory to store dictionary information for the database, as shown below.

mkdir -p /home/oracle/tools/oracle11g/oradata/orcl/LOGMNR

2. Configure LogMiner

Log in to the server as the oracle user and log in to the Oracle database as the DBA, as shown below.

sqlplus /nolog
conn  / as sysdba

Next, create the dictionary folder configuration as shown below.

CREATE DIRECTORY utlfile AS '/home/oracle/tools/oracle11g/oradata/orcl/LOGMNR';alter system set utl_file_dir='/home/oracle/tools/oracle11g/oradata/orcl/LOGMNR' scope=spfile;

Note: Since utl_file_dir only exists in the file, the scope is written in the file mode "spfile", not both.

3. Turn on the log append mode

alter database add supplemental log data;

Only after turning it on can you see the DML operation record, otherwise you can only see the DML

4. Restart the database

SHUTDOWN IMMEDIATE;  STARTUP;

5. Create a data synchronization user

Create a data synchronization user. If there is already one, you can skip it and grant permissions directly.

create user SYNC identified by SYNC;  #如果存在用户，则不需要再次创建，直接授权即可GRANT CONNECT,RESOURCE,DBA,EXECUTE_CATALOG_ROLE TO SYNC;

6. Create a data dictionary

Switch to the user who wants to synchronize data, log in as DBA, and create a data dictionary file as shown below.

conn SYNC/SYNC@orcl as sysdba;  EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/tools/oracle11g/oradata/orcl/LOGMNR');

The command to create a data dictionary can also be implemented through Java code. If it is implemented using Java code, you can first connect to the Oracle database through JDBC, and then execute the following command.

BEGIN
dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location =>'/home/oracle/tools/oracle11g/oradata/orcl/LOGMNR');  END;

7. Add the log files that need to be analyzed

This step can be performed on the Oracle command line or implemented using Java code.

Use SQL statements to achieve the following.

EXECUTE dbms_logmnr.add_logfile(logfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/REDO01.LOG',options=>dbms_logmnr.NEW);  EXECUTE dbms_logmnr.add_logfile(logfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/REDO02.LOG',options=>dbms_logmnr.ADDFILE);  EXECUTE dbms_logmnr.add_logfile(logfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/REDO03.LOG',options=>dbms_logmnr.ADDFILE);

When using Java code analysis, the SQL statement used is as follows.

BEGIN  
dbms_logmnr.add_logfile(logfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/REDO01.LOG',options=>dbms_logmnr.NEW);  dbms_logmnr.add_logfile(logfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/REDO02.LOG',options=>dbms_logmnr.ADDFILE);  dbms_logmnr.add_logfile(logfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/REDO03.LOG',options=>dbms_logmnr.ADDFILE);  END;

8. Check the log file in use

SELECT group#, sequence#, status, first_change#, first_time FROM V$log ORDER BY first_change#;

The log file with status CURRENT is currently in use

9. Use Lominer to analyze logs

EXECUTE dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/LOGMNR/dictionary.ora');

When using Java code analysis, the SQL statement used is as follows.

BEGIN
dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/tools/oracle11g/oradata/orcl/LOGMNR/dictionary.ora'); END;

10. View the analysis results

SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='EMP' AND seg_owner='SYNC';

Note: The analysis results in the view v$logmnr_contents are only in the session when we run the process 'dbms_logmrn.start_logmnr' Exists in the life cycle. If you want to view new logs, you need to re-add the analysis log file and use LogMiner to analyze it.

11. Frequently Asked Questions

(1) If the "identifier 'SYS.DBMS_LOGMNR' must be declared" exception occurs, you need to create the user, Grant EXECUTE_CATALOG_ROLE permissions to the user as shown below.

GRANT CONNECT,RESOURCE,DBA,EXECUTE_CATALOG_ROLE TO SYNC;

(2) At this time, you need to specify the corresponding user for seg_owner in the query statement. For example, if I use the SYNC user to analyze through LogMiner, you need to specify seg_owner as the SYNC user, as shown below.

SELECT sql_redo, sql_undo, seg_owner FROM v$logmnr_contents WHERE seg_name='EMP' AND seg_owner='SYNC';

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Detailed example of Oracle data synchronization migration through LogMiner. For more information, please follow other related articles on the PHP Chinese website!