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 less than it used.

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 issues 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. A lot of companies are now starting to develop their own control tools. such as the DSG based on rowID, minus-based alignment and so on. But the database itself also provides us with a data-pair interface, and that is the Dbms_comparison package.

Dbms_comparision Brief Introduction:

This package is a comparison of what Oracle provides to be able to do an object between two data. And assuming that the source-side data and the target-side data are inconsistent during the alignment process. Then the choice is to copy the data from the source to the target side. or from the target side in the copy to the source side. Finally, the results of data consistency are achieved. 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

The third step: we are going to look at the comparison results, the corresponding record will be recorded in different views such as the following:

Dba_comparison_scan

User_comparison_scan

Dba_comparison_scan_values

User_comparison_scan_values

Dba_comparison_row_dif

User_comparison_row_dif

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

As you might say, if I had two data pairs, then how to differentiate, that is, Oracle will design a logo for you. This function is recheck.

Perhaps introduce:

Another problem. That's what data the package can do?

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

Dbms_comparision Restrictions:

Of course, no matter what a tool has its own limitations, then this package?

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

2. For all database objects, you must be a shared object. That is, each object must have a consistent column count and column type. Assuming 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 more easy to understand limit, the following is the limit of the index column:

1, in the full-Library comparison mode, must have a single index column in number, timestamp, interval, or DATE data type, or only a composite index containing these data types, but this composite index must be designed to the column is 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.

Assume that the database does not meet these requirements. Then this package will not be able to perform data comparison.

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 two kinds of examples:

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)

All right. After understanding these, we will start to do it personally, the light said no practice that can not.

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:

By the way, forget to mention permission. For this package, you have permissions such as the following:

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.

When the columns of the source and destination data objects are inconsistent, such as the following error occurs:

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, assuming that you are 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!

On Oracle Data Alignment (Dbms_comparison)