Managing history v7
Replication Server maintains three types of history:
- Shadow table history. Records of each change (insert, update, or delete) that was applied to each target table during synchronization replications using the trigger-based method. The log-based method doesn't keep a shadow table history for synchronization replications.
- Replication history. Summary records of each replication.
- Event history. Records of each change that was applied to various control schema tables.
The size of the control schema tables that store these history records grows over time. You can use a number of methods to clean up these history records.
Shadow table history cleanup information is described in Scheduling shadow table history cleanup and Cleaning up shadow table history. For replication history cleanup, see Cleaning up replication history.
For event history cleanup, see Cleaning up event history.
Scheduling shadow table history cleanup
You can set a preference for each publication database definition to specify the schedule for shadow table history cleanup for all publications appearing under its corresponding Publication Database node. Shadow table history cleanup has no benefit for snapshot-only publications, so if all your publications under a Publication Database node are snapshot-only publications, then disable scheduled shadow table history cleanup.
Replication history isn't deleted by scheduling shadow table history cleanup. New publication database definitions include a scheduled default setting of every Sunday at 12:00 a.m. for shadow table history cleanup.
Note
A configuration option is available to force shadow table history cleanup after every synchronization replication. See Forcing immediate shadow table cleanup for information on this option.
Note
The cleanup of certain processed rows in the shadow tables might be delayed beyond the next scheduled cleanup. These roles are eventually removed in later cleanup events.
For Oracle only: For scheduling shadow table history cleanup on an Oracle publication database, use the Oracle DBMS_JOB package on the Oracle database server. The time you specify in the schedule for cleanup is passed and stored in DBMS_JOB without time zone translation.
For example, assume the publication server is running on a host in New York and the Oracle publication database is on a server in California, which has a three-hour time difference. Suppose you set shadow table history cleanup to run at 12:00 a.m. according to the New York-based publication server. The cleanup job on the California-based Oracle database starts at 12:00 a.m. Pacific Time, which is 3:00 a.m. Eastern Time.
For SQL Server only: For scheduling shadow table history cleanup on a SQL Server publication database, use SQL Server Agent on the host running SQL Server. The time you specify in the schedule for cleanup is passed to SQL Server Agent without time zone translation.
For Postgres only: For scheduling shadow table history cleanup on a Postgres publication database, use the Quartz scheduler on the host running the publication server based on the location of the controller database.
For example, assume the publication server is running on a host in New York and the Postgres publication database on which cleanup is scheduled is also the controller database and is on a host in California. If you set shadow table history cleanup to run at 12:00 a.m. according to the New York-based publication server, the cleanup job on the California-based Postgres database starts at 12:00 a.m. Pacific Time, which is 3:00 AM Eastern Time in New York.
By contrast, assume the publication server is running on a host in New York along with the controller database, and the Postgres publication database on which to schedule cleanup is on a host in California. If you set shadow table history cleanup to run at 12:00 a.m. according to the New York-based publication server and controller database, the cleanup job on the California-based Postgres database starts at 12:00 a.m. Eastern Time, which is 9:00 p.m. in California.
For Oracle only: The cleanup job on an Oracle publication database runs independently of the publication server, so the cleanup job runs regardless of whether the publication server is running.
For Postgres only: The publication server must be running for the cleanup job to run on a Postgres publication database.
Note
An alternative to using the Quartz scheduler when Postgres is the publication database is to use pgAgent job scheduling instead. See Using pgAgent job scheduling for information on how to use pgAgent job scheduling and its advantages.
To change the default setting:
Make sure the publication server whose node is the parent of the publication database definition whose cleanup scheduling preference you want to set is running and registered in the Replication Server console you are using. See Registering a publication server to learn how to start and register a publication server.
Select the Publication Database node whose cleanup scheduling preference you want to set.
Select Publication > Preferences.
In the Publication Server Preferences dialog box, clear the check box if you don't want to run a scheduled shadow table history cleanup job.
If you want to schedule shadow table history cleanup, instead make sure the Run Cleanup Job check box is selected. Select a radio button for the cleanup frequency:
- Every number of minutes/hours. Schedules shadow table history cleanup to run continuously at an interval in either minutes or hours that you specify. Select this option for huge volumes of updates to the publication tables during the course of the day, every day.
- Every Day at hour of day. Schedules shadow table history cleanup to run once a day at the hour you choose. Select this option if updates to the publication tables are frequent enough to require more than once-a-week cleanup but aren't needed more than once a day.
- Every selected day of week at hour of day. Schedules shadow table history cleanup to run once a week on the day and at the hour you choose. Select this option if updates to the publication tables are infrequent and you don't want to run cleanup manually.
- Cron Expression. Provides more flexibility for specifying a schedule. See Writing a cron expression.
Note
A configuration option is available to force shadow table history cleanup after every synchronization replication. See Forcing immediate shadow table cleanup for information on this option.
To accept the schedule, select OK.
Cleaning up the shadow table history
For publications that aren't snapshot-only (that is, publications on which synchronization replications occur) whose tables experience frequent changes, clean their shadow table history periodically. Otherwise the amount of disk space consumed by the shadow tables in the publication database can grow too rapidly.
Cleaning up shadow table history deletes the rows in the following Replication Server metadata tables:
- RREP_TXSET
- RREP_TXSET_LOG
- RRST_schema_table
For Oracle only: When Oracle is the publication database, these tables are located in the publication database in the schema of the publication database user.
For SQL Server only: When SQL Server is the publication database, these tables are located in the publication database in the schema you chose in SQL server publication catabase.
For Postgres only: When Postgres is the publication database, these tables are located in the publication database in schema _edb_replicator_pub. You can schedule shadow table history cleanup to run periodically (see Scheduling shadow table history cleanup) or on demand.
Note
The cleanup of certain processed rows in the shadow tables might be delayed beyond the next scheduled cleanup. When Oracle or SQL Server is the publication database for clusters created using versions 7.5 and earlier, the cleanup delay can occur due to uncommitted changes. These rows are eventually removed in later cleanup events. For new clusters created using version 7.5.1 and later, the shadow cleanup operation is accurate, and the delayed cleanup issue doesn't occur.
To run shadow table history cleanup on demand for a chosen publication:
Make sure the publication server whose node is the parent of the publication whose shadow table history you want to clean up is running and registered in the Replication Server console you're using. See Registering a publication server to learn how to start and register a publication server.
Select the Publication node of the publication whose the shadow table history you want to clean up.
Select Publication > Cleanup Shadow Table History.
In the Cleanup Synchronization History confirmation box, select Yes.
Select Yes in response to Shadow Table’s Transaction History Removed Successfully.
After shadow table history cleanup, if you select View Data in the Replication History tab, an information message appears stating that there's no synchronization history to view.
Cleaning up replication history
Cleaning up replication history deletes rows from the following tables in the control schema:
- xdb_pub_replog
- xdb_pub_table_replog
To run replication history cleanup for a chosen publication:
Make sure the publication server whose node is the parent of the publication whose replication history you want to clean up is running and registered in the Replication Server console you're using. See Registering a publication server to learn how to start and register a publication server.
Select the Publication node of the publication whose replication history you want to clean up.
Select Publication > Cleanup Replication History.
In the Cleanup Replication History confirmation box, select Yes.
Select Yes in response to Replication History Has Been Removed.
After replication history cleanup, no history records appear in the Replication History tab.
Cleaning up event history
Unlike shadow table history (Cleaning up shadow table history) and replication history (Cleaning up replication history), you can't view or remove event history using the Replication Server console.
Event history is a recording of various updates to the control schema tables that occur during Replication Server processing. Event history content grows significantly over time.
The tables containing event history to clean up are the following:
- xdb_events
- xdb_events_status
In addition, clean up the replication history tables. You can manually clean up these tables as described in Cleaning up replication history.
- xdb_pub_replog
- xdb_pub_table_replog
For Oracle, these tables are located in the schema of the publication database user. For SQL Server and Postgres, these tables are located in schema _edb_replicator_pub.
The event history and replication history data in the control schema are deleted on a scheduled, daily basis at 12 a.m., thus reducing the number of rows in tables xdb_events, xdb_events_status, xdb_pub_replog, and xdb_pub_table_replog.
Publication server configuration option historyCleanupDaysThreshold enables you to designate how old the completed data must be before its removal. The default setting is that completed data must be older than seven days before it is deleted during the daily 12 a.m. cleanup process.
To clean up all completed event and replication history, set historyCleanupDaysThreshold to a value of 0, and then restart the publication server. The cleanup occurs during the next scheduled 12 a.m. cleanup.
See Setting event history cleanup threshold for the historyCleanupDaysThreshold option.