Skip to main content

Oracle 11g new features: DBMS_COMPARISON


Another interesting feature for Oracle 11g is DBMS_COMPARISON package which planned to use compare data within two schemas or between database`s schema. You can use this package for several reason: if replication/streams broken, or checking between production and test databases data etc.

To use DBMS_COMPARISON you have to grant. If you connected as SYSDBA you already have had grant to execute. Other users need execute grants on DBMS_COMPARISON and that user will need to be granted execute_catalog_role too.

Note: The database character sets must be the same for the databases that contain the database objects being compared.

For all scan modes to be supported by the DBMS_COMPARISON package, the database objects must have one of the following types of indexes:
·         A single-column index on a number, timestamp, interval, or DATE datatype column
·         A composite index that only includes number, timestamp, interval, or DATE datatype columns. Each column in the composite index must either
have a NOT NULL constraint or be part of the primary key.

For the scan modes CMP_SCAN_MODE_FULL and CMP_SCAN_MODE_CUSTOM to be supported, the database objects must have one of the following types of indexes:
·         A single-column index on a number, timestamp, interval, DATE, VARCHAR2, or CHAR datatype column
·         A composite index that only includes number, timestamp, interval, DATE, VARCHAR2, or CHAR columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.
  
The DBMS_COMPARISON package can compare the following types of database objects:
  • Tables
  • Single-table views
  • Materialized views
  • Synonyms for tables, single-table views, and materialized views

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 be compared and converged with this package.

  
The DBMS_COMPARISON package supports all Oracle Data Types (VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP etc) except the following:

LONG
LONG RAW
ROWID
UROWID
CLOB
NCLOB
BLOB
BFILE

There are useful data dictionary views:
·         DBA/USER_COMPARISON_SCAN
·         DBA/USER_COMPARISON_SCAN_SUMMARY
·         DBA/USER_COMPARISON_SCAN_VALUES
·         DBA/USER_COMPARISON_ROW_DIF

Let`s check.

[oracle@localhost admin]$ export ORACLE_SID=testdb
[oracle@localhost admin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 13 17:22:59 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             285215104 bytes
Database Buffers          130023424 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.
SQL> conn ulfet/ulfet
Connected.


--Created clone of hr.employees table without data
SQL> create table ulfet.emp_clone as select * from hr.employees where 1=2;

Table created.

SQL> 

--Hr.employees`s table has 107 rows
SQL> select count(1) from hr.employees;

  COUNT(1)
----------
       107

SQL> 

--Now to synchronize the data:


SQL> set serveroutput on
DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
--  dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
dbms_comparison.create_comparison(comparison_name    => 'comp_emp',
schema_name        => 'ulfet',
object_name        => 'emp_clone',
dblink_name        => null,
remote_schema_name => 'hr',
remote_object_name => 'employees'
);

consistent := dbms_comparison.compare(comparison_name => 'comp_emp', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'comp_emp',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33  
DECLARE
*
ERROR at line 1:
ORA-23626: No eligible index on table ULFET.EMP_CLONE
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 7

--That you do not have an index on source table, Oracle compares the data by index let`s create it

SQL> alter table emp_clone add constraint emp_pk primary key (employee_id);

Table altered.

--Try again

SQL> set serveroutput on
DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
--  dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
dbms_comparison.create_comparison(comparison_name    => 'comp_emp',
schema_name        => 'ulfet',
object_name        => 'emp_clone',
dblink_name        => null,
remote_schema_name => 'hr',
remote_object_name => 'employees'
);

consistent := dbms_comparison.compare(comparison_name => 'comp_emp', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'comp_emp',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33  
Differences were found.
Scan ID: 13
Local Rows Merged:107
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0

PL/SQL procedure successfully completed.

SQL> 

Now, we can check result:

SQL> select count(1) from emp_clone;

  COUNT(1)
----------
       107

SQL> select count(1) from hr.employees;

  COUNT(1)
----------
       107

SQL> 
  
SQL> column a.owner format a6
column comp_name format a10
column schema format a6
column object format a12
select a.owner, a.comparison_name comp_name , a.schema_name schema, a.object_name object, z.current_dif_count different_count 
from dba_comparison a, dba_comparison_scan_summary z
where a.comparison_name=z.comparison_name
and a.owner=z.owner; 

OWNER                          COMP_NAME  SCHEMA OBJECT       DIFFERENT_COUNT
------------------------------ ---------- ------ ------------ ---------------
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0

OWNER                          COMP_NAME  SCHEMA OBJECT       DIFFERENT_COUNT
------------------------------ ---------- ------ ------------ ---------------
ULFET                          COMP_EMP   ULFET  EMP_CLONE                  0

12 rows selected.


Now, change some data on emp_clone table and repeat above action. This time different will be on emp_clone table not hr.employees.

SQL> show user;
USER is "ULFET"
SQL> update emp_clone set first_name='Ulfet' where employee_id=101;

1 row updated.

SQL> delete from emp_clone where employee_id=102;

1 row deleted.

SQL> commit;

Commit complete.

As you see we changed 2 rows data (1 row updated, 1 row deleted)

Create new comparison name to prevent conflict

SQL> set serveroutput on
DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
dbms_comparison.create_comparison(comparison_name    =>'comp_emp_new',
schema_name        => 'ulfet',
object_name        => 'emp_clone',
dblink_name        => null,
remote_schema_name => 'hr',
remote_object_name => 'employees'
);

consistent := dbms_comparison.compare(comparison_name => 'comp_emp_new', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'comp_emp_new',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32  
Differences were found.
Scan ID: 25
Local Rows Merged:2 
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0

PL/SQL procedure successfully completed.
  
SQL> select employee_id, first_name from emp_clone where employee_id in (101,102);

EMPLOYEE_ID FIRST_NAME
----------- --------------------
        101 Neena
        102 Lex

SQL> 

That`s all.


For more detail please refer to: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm#CJHCBCCI



Comments

Post a Comment

Popular posts from this blog

Rman encryption and other techniques

Today I will demonstrate Recovery Manager features, especially encryption and some other techniques. First of all let me note about RMAN backup encryption. Staring from Oracle 10g RMAN now creates encrypted backups that cannot be restored by unauthorized people. There are 3 modes of backup encryption: * Transparent encryption * Password encryption * Dual-mode encryption using either transparent or password encryption All RMAN backups are not encrypted but you can encrypt any RMAN backup in the form of a backup set. In this tutorial I will show you how to configure Password encryption. Let`s finish talking and start to demonstrate. check parameters with "SHOW ALL" command, by default encryption is OFF RMAN> show all; RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTR...
4 comments
Read more

ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range

Today when I want to change max size of datafile I got strange error. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32GB ORA-03206: maximum file size of (5242880) blocks in AUTOEXTEND clause is out of range Here is reason: The maximum file size for an autoextendable file has exceeded the maximum number of blocks allowed. After research internet I found that instead of using 32GB we can use 32767M. (same values but in MB). Or you may use 31GB too. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/users01.dbf' AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M;
Post a Comment
Read more

Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open. SQL> select name from v$database; NAME --------- TEST SQL> shut abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 6597406720 bytes Fixed Size 2265664 bytes Variable Size 3204451776 bytes Database Buffers 3372220416 bytes Redo Buffers 18468864 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6552 Session ID: 191 Serial number: 3  What`s wrong?  SQL> alter database open resetlogs; ERROR:    ORA-03114: not connected to ORACLE    SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...
9 comments
Read more