On Oracle Data Alignment (Dbms_comparison)

Today is 2014-08-19, I received csdn to send me the application of blog experts invited, I feel really ashamed ah. I haven't had the energy to write something since I changed my job. Today, a colleague of mine posted a packet of data pairs in the group (Dbms_comparison), but the package was relatively small. So let's talk about the use of this toolkit today.

For a friend who often finishes data migration, one of the most important and critical concerns after data movement is whether the data on the target and the source are consistent. Whether data consistency is related to the success or failure of large-scale Oracle database Data migration. At present, many companies are developing their own comparison tools, such as the DSG based on rowID, based on minus and so on. But the database itself also provides us with a data-pair interface, and that is the Dbms_comparison package.

Dbms_comparision Introduction:

This package is provided by Oracle and can be compared between two data to do object. And what if the source-side data and the target-side data are inconsistent during the alignment process, then you can choose whether to copy the data from the source to the target, or from the target side to the source, and finally achieve the result of data consistency. The package is also implemented by creating Dblink. The use of this tool is broadly divided into four steps:

The first step: use Create_compare to create a comparison action

Step Two: Use the Compare function to make a comparison between the data objects

Step three: We're going to look at the comparison results, and the corresponding record will be recorded in the different views as follows:

Dba_comparison_scan

User_comparison_scan

Dba_comparison_scan_values

User_comparison_scan_values

Dba_comparison_row_dif

User_comparison_row_dif

IV: If the data is inconsistent, you can use convert to synchronize the data

You might say that if I did two data comparison, then how to differentiate, that is, Oracle himself will give you a logo design. This function is recheck. Follow-up introduction:

There is one more problem, that is, what data does the package do?

The answer is: for tables, views, materialized views, synonyms, etc.

Dbms_comparision Restrictions:

Of course, any tool has its own limitations, so what about this package?

1, for the source-side database version must be higher than 11.1, for the target-side database version must be higher than 10.1

2. For all database objects that match, you must be a shared object, that is, the number of columns and the type of columns must be the same for each object. If the columns are inconsistent, you need to make a list of the column_list columns using the.

Database objects of different types can be compared and converged at different databases. For example, a table at one database and a materialized view at another database can is compared and converged with this p Ackage.

The above is a relatively easy to understand limit, the following is the limit of the index column:

1. In the full-Library comparison mode, you must have a single index column in number, timestamp, interval, or DATE data type, or just a composite index that includes these data types, but the columns designed in this composite index must all be not Null or one of the columns is a primary key column.

2.

For the scan modes CMP_SCAN_MODE_FULL CMP_SCAN_MODE_CUSTOM and to be supported, the database objects must has one of the following types of indexes:

• A single-column index on a number, timestamp, interval, DATE , VARCHAR2 , or CHAR data type column

• A composite index that is only includes number, timestamp, interval,, DATE VARCHAR2 , or CHAR columns. Each column in the composite index must either has a constraint or be part of the NOT NULL primary key.

If the database does not meet these requirements, then the package will not be able to perform data alignment.

If the database objects has only one index, and it's a composite index that includes a NUMBER column and an NCHAR column, Then the package does is not a support DBMS_COMPARISON them.

If These constraints is not present on a table, then use the and parameters in the index_schema_name index_name procedure to CREATE_COMPARISON specify An index whose columns satisfy this requirement.

When a single index value identifies both a local row and a remote row, the both rows must is copies of the same row in the Replicated tables. In addition, each pair of copies of the same row must always has the same index value.

Data types not supported by Dbms_comparison:

LONG, LANG RAW, ROWID, Urowid, CLOB, NCLOB, blob, bfile There are also the following two kinds:

1. UDT (user-defined types,including object types, REF s, varrays, and nested tables)

2, oracle-supplied type (including any types, XML types, spatial types, and media types)

Well, after we get to know this, we'll start to do it ourselves, it's not good to say no practice.

First create Dblink:

Sql> SELECT * from Dba_sys_privs Rhys where Rhys.privilege like Upper ('%link% '); GRANTEE PRIVILEGE ADM----------------------------------------------                            ---------------------------SYS DROP public DATABASE LINK Nosys                     Create DATABASE link noowb$client CREATE DATABASE link Noimp_full_database Create public DATABASE LINK Norecovery_catalog_owner Create DATA BASE link nodba DROP public DATABASE LINK Yesimp_full_databa                     SE DROP Public Database link Noowbsys CREATE DATABASE link Yesimp_full_database CREATE DATABASE LINK nosys creat                      E Public DATABASE LINK NODBA      CREATE public DATABASE LINK yesgrantee PRIVILEGE ADM-------------------------------------------------------------------------DBA CREATE databa SE LINK YES12 rows selected. Sql> Grant CREATE DATABASE link to Scott; Grant succeeded.

Sql> CREATE DATABASE link Comparison_link Connect to Scott identified by Root using ' orac1 ';D atabase link created.     Sql> show Useruser is ' SCOTT ' sql> select * from [email protected]_link; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO DEP----------------------------------- -------------------------------------------------------------------------7369 SMITH Clerk 7902 1         7-dec-80 7499 ALLEN salesman 7698 20-feb-81 1600 300         7521 WARD salesman 7698 22-feb-81 1250 7566 JONES MANAGER       7839 02-apr-81 2975 7654 MARTIN salesman 7698 28-sep-81 1250      1400 7698 BLAKE MANAGER 7839 01-may-81 2850-7782 CLARK MANAGER 7839 09-jun-81 2450 10     7788 SCOTT ANALYST 7566 19-apr-87 7839 KING president         17-nov-81 7844 TURNER salesman 7698 08-sep-81 1500 0              7876 ADAMS Clerk 7788 23-may-87 1100 EMPNO ename JOB MGR hiredate SAL COMM DEPTNO DEP-------------------------------------------------------                    -----------------------------------------------------7902 AMY ANALYST 7566 03-dec-81 3000 Rows selected.  Sql>

The second step is to create a comparison task:

To forget to mention the permission, for the package, you have the following permissions:

Sql>  begin  2   Dbms_comparison.create_comparison (  3   comparison_name=> ' test1 ',  4   schema_name=> ' SCOTT ',  5   object_name=> ' DEPT ',  6   dblink_name=> ' Comparison_link '  7   );  8   end;  9  

Okay, so we're done with the first step.

The following error occurs when the columns of the source and destination data objects are inconsistent:

Sql>  begin  2   Dbms_comparison.create_comparison (  3   comparison_name=> ' test1 ',  4   schema_name=> ' SCOTT ',  5   object_name=> ' EMP ',  6   dblink_name=> ' Comparison_link '  7   );  8   end;  9  /Begin*error at line 1:ora-23625:table shapes of SCOTT. EMP and [email Protected]_link did not match. Ora-06512:at "SYS. Dbms_comparison ", line 5008ora-06512:at" SYS. Dbms_comparison ", Line 448ora-06512:at Line 2

So what do we do? And I said is to do a column_list;

The second step starts the data comparison:

Sql> declare  2   compare_info dbms_comparison.comparison_type;  3   Compare_return Boolean;  4   begin  5   Compare_return: = Dbms_comparison.compare (comparison_name=> ' test1 ',  6   Scan_info =>compare_info,  7   perform_row_dif=>true);  8     9  If Compare_return=true ten then one   dbms_output.put_line (' The tables is equivalent. '); 12   Else   dbms_output.put_line (' bad news ... there is data divergence. ');   dbms_output.put_line (' Check The Dba_comparison and Dba_comparison_scan_summary views for locate the differences for scan_id: ' | | COMPARE_INFO.SCAN_ID);   End If;   17   

The third step is to look at the comparison results:

Sql> SELECT * from User_comparison_scan  2  ; Comparison_name         scan_id parent_scan_id root_scan_id STATUS           current_dif_count initial_dif_count COUNT_ROWS S Last_update_time---------------------------------------------------------------------------------------------- ---------------------------------------------------------------TEST1                         1                           1 SUC                              0                 0          4 N 19- AUG-14 11.05.42.780593 PMTEST1                         2                           2 SUC                              0                 0          4 N 19-aug-14 11.11.37.613343 pmsql> SELECT * FROM User_comparison_row_dif;no rows Selectedsql>

Well, it's simple.

Of course if you dare to be interested in testing the others yourself.

I'm going to clear out the data:

Sql> begin  2  Dbms_comparison.purge_comparison (  3  comparison_name=> ' test1 ');  4  end;  5  

That ' s all!