Data Guard
Using dbms_comparison to troubleshoot logical standby databases
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 05 April 2018 11:33
-
Last Updated on Thursday, 05 April 2018 12:17
-
Published on Thursday, 05 April 2018 11:33
-
Written by Guy Lambregts
-
Hits: 4553
Using dbms_comparison to troubleshoot logical standby databases.
We ran into an issue with a synchronization of a logical standby database.
The logical standby apply stopped, we found in the alert file there were data content issues
( no data found errros, duplicate key found errors )
Since we need to bring the logical standby in sync as fast as possible we restarted a few time the logical standby apply with the command
alter database start logical standby apply immediate skip failed transaction
The standby apply resumes and the logical standby apply is back in syn with the primary database ( dba_logstdby_progress ).
However we know there must be a data content mismatch at level of the problem table for which we skipped transactions.
We decide to use dbms_compare to compare the tablespace between source and target database.
begin
dbms_comparison.create_comparison ( comparison_name => 'compare_MY_TABLE',
schema_name => 'MY_SCHEMA',
object_name => 'MY_TABLE',
dblink_name => 'TO_PRIMARY_DB.MY_DOMAIN.BE',
scan_mode => dbms_comparison.cmp_scan_mode_full );
end;
/
PL/SQL procedure successfully completed.
declare
v_comp boolean;
v_scan_info dbms_comparison.comparison_type;
begin
v_comp := dbms_comparison.compare
(comparison_name => 'compare_MY_TABLE',
scan_info => v_scan_info,
perform_row_dif => true);
dbms_output.put_line ('Scan ID: '||v_scan_info.scan_id);
if v_comp = TRUE then
dbms_output.put_line ('No deltas were found.');
else
dbms_output.put_line ('Deltas were found.');
end if;
end;
/
Scan ID: 6
Deltas were found.
PL/SQL procedure successfully completed.
select count(*) from dba_comparison_row_dif;
COUNT(*)
----------
10
1 row selected.
select * from dba_comparison_row_dif;
OWNER
--------------------------------------------------------------------------------------------------------------------------------
COMPARISON_NAME SCAN_ID LOCAL_ROWID REMOTE_ROWID
-------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------ ------------------
INDEX_VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STA LAST_UPDATE_TIME
--- ---------------------------------------------------------------------------
DBA_USER
COMPARE_MY_TABLE 2 AACEgkAAfAAAP7VAAe
673702
DIF 30-MAR-18 09.42.53.871505 AM
DBA_USER
COMPARE_MY_TABLE 3 AACEgkAAfAAAP7SAAb AABw26AAfAACkHOABX
674002
DIF 30-MAR-18 09.42.53.893746 AM
DBA_USER
COMPARE_MY_TABLE 4 AACEgkAAfAAAAWDABi AABw26AAfAACkHOABe
674023
DIF 30-MAR-18 09.42.53.897114 AM
DBA_USER
COMPARE_MY_TABLE 4 AACEgkAAfAAAP7SAAc AABw26AAfAACkHOABd
674022
DIF 30-MAR-18 09.42.53.897114 AM
DBA_USER
COMPARE_MY_TABLE 5 AACEgkAAfAAAAWDABp
674042
DIF 30-MAR-18 09.42.53.900130 AM
DBA_USER
COMPARE_MY_TABLE 7 AACEgkAAfAAAP7VAAe
673702
DIF 30-MAR-18 09.44.39.648114 AM
DBA_USER
COMPARE_MY_TABLE 8 AACEgkAAfAAAP7SAAb AABw26AAfAACkHOABX
674002
DIF 30-MAR-18 09.44.39.656730 AM
DBA_USER
COMPARE_MY_TABLE 9 AACEgkAAfAAAAWDABi AABw26AAfAACkHOABe
674023
DIF 30-MAR-18 09.44.39.662445 AM
DBA_USER
COMPARE_MY_TABLE 9 AACEgkAAfAAAP7SAAc AABw26AAfAACkHOABd
674022
DIF 30-MAR-18 09.44.39.662445 AM
DBA_USER
COMPARE_MY_TABLE 10 AACEgkAAfAAAAWDABp
674042
DIF 30-MAR-18 09.44.39.665216 AM
10 rows selected.
--- We stop the logical standby apply
alter database stop logical standby apply;
Database altered.
--- We reinstantiate the table
begin
dbms_logstdby.instantiate_table('MY_SCHEMA','MY_TABLE','TO_PRIMARY_DB.MY_DOMAIN.BE');
end;
/
PL/SQL procedure successfully completed.
--- We resume logical standby apply
alter database start logical standby apply immediate;
Database altered.
declare
v_comp boolean;
v_scan_info dbms_comparison.comparison_type;
begin
v_comp := dbms_comparison.compare
(comparison_name => 'compare_MY_TABLE',
scan_info => v_scan_info,
perform_row_dif => true);
dbms_output.put_line ('Scan ID: '||v_scan_info.scan_id);
if v_comp = TRUE then
dbms_output.put_line ('No deltas were found.');
else
dbms_output.put_line ('Deltas were found.');
end if;
end;
/
Scan ID: 15
No deltas were found.
PL/SQL procedure successfully completed.
One can also temporary skip the replication of an object with dbms_logstdby.skip
Most annoying of these errors are primary key / foreign key relantionships between tables.
When you skip temporary a table from the replication you can run into issues at the level of the child tables with parent key not found errors.