Oracle Database 11g Streams introduces advancements in the DBMS_COMPARISON package.OracleStreams now provides functionality that has been in Quest Shar eplex since Oracle 8i.Shareplex is one of Quest’s enterprise products that performs redo and archivelog mining and provides a similar level of functio nality to Oracle Streams.
Lots of Oracle customers have preferred to utilize Quest Shareplex over Oracle Streams and Oracle Advanced Replication in the past for data replication because of its ease of use and maintenance.Oracle Streams now makes another big stride and fills this gap with the new data comparison and convergence APIs.
Even though the DBMS_COMPARISON package is introduced as a Streams feature, it can be use independently of Streams.You can use the DBMS_COMPARISON package to detect divergences and rectify discrepancies in a non-Streams environment.
Quest always had the capability to perform what they call a compare and repair operation.A compare and repair operation provides you with the ability to compare row for row of a source table against a remote target table.
As of Oracle Database 11g,Oracle provides similar functionality that enables the comparison of the rows in an object,such as a table, against another object in the same or a different database.If there are deltas in the database objects,then the DBMS_COMPARISON package can be used to converge the database objects so that they are consistent.
The compare procedure does not support comparison of LONG, LONG RAW,ROWID, CLOB, NCLOB, BLOB, BFILE, user-defined types, or Oracle-supplied types such as XMLTypes.
The DBMS_COMPARISON package has database release requirements.The localdatabase must be at least at Oracle version 11.1, and the remote database must be at least at Oracle version 10.1.0. DBMS_COMPARISON also requires that at least one index be available on the objects being compared, preferably a NUMBER column.In addition,the database character set of the source and target comp arison database must be the same.
Supported Database Object Types
The DBMS_COMPARISION package can compare databases between live sources for the following types of database objects:
Data does not have to be compared against the same object type.You can compare a table with a view.You can also compare a table in one database against a materialized view on another database. Database objects can be of different types.Furthermore,the columns names can also be different as long as they are of the same datatype.
Behind the scenes, Oracle does not perform row-for-row comparisons as you would expect.Oracle’s comparison algorithm uses the ora_hash function to compute a hash value for a specified number of rows called a bucket.
Buckets improve the performance of table scans by splitting the table into ranges of rows.Each range of rows is processed independently.
The number of buckets determined for acompared object will be the number of rows in a specified table divided by the MAX_NUM_BUCKETS parameter in theDBMS_COMPARISION. CREATE_COMPARISON procedure.
The MAX_NUM_BUCKETS parameter determines the number of rows performed per scan.If there are no differences found in the specified bucket of rows between the source and target tables,the bucket is split, and another scan is performed.
If the split bucket from the previous proces returns different results in the hash value, the buckets are split again, and another hash value comparison is performed.This process is repeated until the differing rows are identified or until the MIN_ROWS_IN_BUCKET threshold is reached.
When the MIN_ROWS_IN_BUCKET is reached, the result is posted to theDBA_COMPARISON_SCAN_SUMMARY view.
By default,the number of MAX_NUM_BUCKETS and MIN_ROWS_IN_BUCKET parameters are set to 1000 and 10000, respectively, as shown in a snippet of code from the $ORACLE_HOME/rdbms/admin/dbmscmp.sql file:-- Other Default values
Begin a comparison by first defining comparison boundaries including a table and column level of granularity using the create_comparison procedure:begin
Two additional parameters to the create_comparison procedure, SCAN_PERCENT and SCAN_MODE, allow you to specify the level of comparison and percentage of the table to scan.The following are valid values for SCAN_MODE:
Please note that data comparisons can affect the performance of theinvolved databases.You should consider the cyclic level of comparison since you can allocate,for example,10 percent of the rowsets at a time and continue until the comparison completes.By using this approach,you can mitigate possible performance bottlenecks to the production database environment.
The SCAN_PERCENT parameter is applicable when the SCAN_MODE parameter is set to either CMP_SCAN_MODE_RANDOM or CMP_SCAN_MODE_CYCLIC.
Once the comparison is defined, we can run the comparison difference algo rithm and collect the deltas between the source and the target.For each execution of the compare function,a scan ID is produced:declare
In this example,PERFORM_ROW_DIF is set to TRUE.With this parameter set, theindividual row deviation’s information will be collected.By setting this parameter to FALSE, deviation information will be collected only at the table level.Your output should look similar to the following:Scan ID: 99
For optimal performance for object-level comparisons, you can adjust theMAX_NUM_BUCKETS and MIN_ROWS_IN_BUCKET parameters in the create_ comparisonprocedure.
After much iteration of data comparisons,you may need to purge previous comparison collections. The nls_timestamp_format can be specified at the database level in the initialization parameter:nls_timestamp_format='RRRR-MM-DD HH24:MI:SS'
To purge all previous comparisons, you can use the following procedure, specifying nulls for SCAN_ID and PURGE_TIME:begin
On the other hand,you can also purge an individual scan of a comparison.The requirement to perform this task are the comparison name and the scan ID.You can determine the SCAN_ID for a specified comparison name by querying theDBA_COMPARISON_ SCAN_SUMMARY view:select distinct root_scan_id
Once the SCAN_ID is identified, you can perform the actual purge:begin
You can also purge a plan for a specified period. All scans prior to that time period will be deleted.To purge all comparisons since April 15, 2007, as of noon, you can use the following:begin
You can also drop the comparison and all associated scans using the drop_comparison procedure: exec dbms_comparison.drop_comparison('COMPARE_DOCS');
Rechecking a Prior Comparison
You can recheck the previous comparison scans using theDBMS_COMPARISION.RECHECK function. This function is capable of capturing in-flight data.It can also be used to provide a quick check of rows that are different.
The recheck function checks the current data in the database objects for differences that were recorded in the specific comparison scan.The recheck function takes a parameter of the comparison name,scan ID,and row difference.Let’s perform a recheck for a comparison on the DOCS table, which generated a SCAN_ID of 99:set serveroutput on
Your output is similar to the following:
Deltas were found.PL/SQL procedure successfully completed.
Converging Shared Database Objects (Repair)
The primary purpose of the converge procedure is to resynchronize the differences from the source compared to the target database. Once you identify what is considered to be the “truth”database,albeit the target or the source, you can manually sync the row differences across the database link.The best option is to let Oracle synchronize the row differences identified in a com parison scan.
You can converge and specify whether the source or target is the master and should win on the conflict resolution.The following example specifies that the local database is the conflict winner:set serveroutput on
you can specify the remote database server to be the master source.There are several DBA_ additional information captured by the DBMS_COMPARISON package.You can join the DBA_COMPARISON and DBA_COMPARISON_SCAN_SUMMARY views to produce a high-level report indicating the number of records that are out of sync using the following query:select c.comparison_name, c.schema_name,
Other pertinent views relevant to comparisons are as follows:
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.