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

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

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value. Example result of sql select                  execution_name, advisor_name,                  to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,                  to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' order by execution_start; Check value of TIME_LIMIT`s parameter : SQL> column parameter_value for A35 SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'DEFAULT_EXECUTION_TYP...
Post a Comment
Read more

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...
2 comments
Read more