-
Title
Considerations for when using "copy table" or "append table" for a very large table. -
Description
Starting with Shareplex 6.0 and up, the copy table has been introduced to sync tables. It uses Oracle's Export/Import utility and has a number of options available which includes synchronizing tables not involved in replication. The command invokes Oracle Export with CONSISTENT=y and is usually preferred over another command named "compare table" when synchronizing large tables. The solution delves on some common problems that can occur when running Oracle Export with CONSISTENT option.
-
Cause
N.A.
-
Resolution
The commands "copy table" or "append table" invoke Export with CONSISTENT = y and this can require adequately sized rollback segments (RBS) to avoid ORA-01555 depending on the size and the number of tables and/or the quantum of the user activity hitting such tables. Most of the tables in question may be large tables that would otherwise fail to sync up if compare were to be used so the copy command is expected to be tried on such tables, and there is a likelihood of encountering ORA-1555 when running copy table. The failure would be logged in Oracle alert log (*alert.log). The resolution would involve increasing the size of the existing rollback segments or adding rollback segments of larger size and then running the command again. Another way to alleviate the problem is to run the command when there is less user activity on the database.
-
Additional Information
The following type of error show up in the copy server log on source and the target can also get similar error when running into ORA-1555 during copy:
sp_ctrl (2100)> copy status
Job ID : nnn
Host : hostname
Started : 05-NOV-08 nn:nn:nn
Job Type : Copy
Status : Failed
ORA-01555: snapshot too old: rollback segment number n with name "<name>" too small
See sync_svr_nnn log file for details
ID Tablename Total Rows %Comp Status Status Time Total Time
------ ------------------------------------ ---------- ----- ---------- ----------- ----------
1 "owner"."tablename" Unknown Failed N/A nn:nn:nn