Repair and fix Out of Sync LONGRAW using Oracle import export by using rowid (4279756)

Repair and fix Out of Sync LONGRAW using Oracle import export by using rowid

Out of sync table with LONGRAW column has only a few rows out of sync and the table has 10 million + rows. Regular Compare/Repair is taking a long time.

WORKAROUND 1:

Use compare/repair with where clause and specify the key column value for the specified rows in where clause. You can get the key column value from the SID_errlog.sql.

sp_ctrl> compare table owner.table_name to owner.table_name target target_host@o.trgt_SID where "keycol='xxxxxx'" repair


WORKAROUND 2:

Use oracle export/import with where clause.

1. Once retrieve the original rowid from error.sql file as mentioned then we use this export command at the source side.

2. Run oracle export with where clause specifying source rowid
source# exp file=xxxx.dmp log=xxx.log tables=owner.table_name query=\"where
rowid \= \'AAAK9XAINAADR1NAAH\'\"

3. ftp the export dump file to target and used oracle import to repair this row on target:

target# imp file=xxx.dmp log=xxx.log fromuser=source_schema_owner touser=target_schema_owner ignore=y

NOTE: A script can be written to extract all the rowids from the $SP_SYS_VARDIR/log/*errorlog.sql files for that table and export these rows from source and import these rowid into target.