Activate replication with an Oracle hot backup on an active database
Use this procedure to use an Oracle hot backup to establish a target Oracle instance and activate replication without quieting the source database. This procedure involves using the reconcile command to ensure that transactions which occurred after the point of backup are applied to the target, while eliminating redundant replicated transactions that were already captured by the backup.
Preliminary considerations
Read these points before you proceed.
Supported databases
Oracle source and Oracle target
Supported replication strategies
All replication strategies are supported with the following limitations:
| Consolidated replication (many sources to one target) |
To establish consolidated replication, the use of a hot backup from all source systems is not possible. A backup from one source will override the data that was applied by a backup from a different source. You can use a hot backup of one of the source instances to establish a target instance, and then use another copy method to apply the objects from the other source instances. Possible methods include:
|
| Peer-to-peer |
To establish peer-to-peer replication, you must:
- Quiet all of the systems except the trusted source system for the duration of this procedure.
- Move all users to the trusted source system, and then follow this procedure.
Only after this procedure has been performed on all of the secondary systems may users may resume activity on them. |
Requirements
- [Unix and Linux systems] Verify that the ORACLE_SID and ORACLE_HOME in the oratab file are correct for the instance you will be establishing with the hot backup. The SID must be the SID used in the routing map in the configuration file that you will be activating.
- Read the requirements before you start this procedure. For more information, see Requirements for Activating a Configuration.
- Make certain a SharePlex database account exists in the source database (only). This account usually is created when SharePlex first is installed. See the SharePlex Installation and Setup Guide for more information.
- Before you start, review this procedure and see theSharePlex Reference Guide for more information about the commands that are used.
Troubleshooting
If the configuration fails to activate, you can find information about the failure in these places:
- Use the show log command to view the event_log.
- View the activation process log, which is a file named SID_oconf##.log in the log sub-directory of the SharePlex variable data directory.
See also Solve database setup problems for Oracle.
Procedures
There are two procedures for activation with a hot backup, depending on your replication strategy.
Activation with hot backup: all strategies except cascading
Activation with hot backup: cascading replication
Activation with hot backup: all strategies except cascading
Use this procedure for all replication strategies except cascading replication where SharePlex will be posting to a database on the intermediary system.
Perform the following steps to activate with hot backup for all strategies except cascading:
- On the source and target systems, go to the bin sub-directory of the SharePlex product directory, and start sp_cop and sp_ctrl. In a cluster, the source is the primary node where the cluster VIP is running.
-
On both systems, verify that the SharePlex processes are running.
sp_ctrl> status
-
On the target system (primary node of a target cluster), stop the Post process. This allows replicated data to accumulate in the post queue until the database has been recovered and reconciled.
sp_ctrl> stop post
- On the source system, run the Oracle hot backup.
-
When the backup is finished, activate the configuration on the source system.
sp_ctrl> activate config filename
-
On the source system, monitor activation status.
Note: The command retains control of sp_ctrl until activation is finished.
-
When activation is complete, switch log files on the source system.
On-premises database:
svrmgr1> alter system switch logfile;
Amazon RDS database:
Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.
-
Do one of the following:
-
On the target system, do one of the following:
- If recovering to a sequence number, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log from the previous step.
- If recovering to a SCN, recover the database from the hot backup using the UNTIL CHANGE scn option in the RECOVER clause, and cancel the recovery after Oracle has applied the logs matching the SCN from the previous step.
- On the target system, open the database with the RESETLOGS option.
-
On the target system, run Database Setup for Oracle on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).
Would you like to create a new SharePlex user [y]. n
-
[Optional] If you are using named post queues and are unsure of the queue names, issue the qstatus command.
sp_ctrl> qstatus
-
On the target system, issue the reconcile command as follows, depending on the recovery option you chose. If you are using named post queues, issue the command for each one.
-
If recovering to a sequence number, substitute the sequence number of the log that you noted previously.
sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number
Example: reconcile queue SysA for o.oraA-o.oraA seq 1234
-
If recovering to a SCN, substitute the SCN that you noted previously.
sp_ctrl> reconcile queue queuename for datasource-datadest scn scn_number
Example: reconcile queue SysA for o.oraA-o.oraA scn 0123456789
Note: The command retains control of sp_ctrl until the reconcile process is finished.
- On the target system, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
- On the target system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
- On the target system, disable check constraints and scheduled jobs that perform DML.
- [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.
-
[High availability only] On the target (secondary) system, stop Export.
sp_ctrl> stop export
-
[High availability and peer-to-peer only] On the target (secondary) system, activate the configuration so that SharePlex is ready in the event of failover.
sp_ctrl> activate config filename
-
On the target system, start the Post process. The two instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.
sp_ctrl> start post
- [Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.
Activation with hot backup: cascading replication
Use this procedure for cascading replication where SharePlex will be posting to a database on the intermediary system.
You will apply the backup to the intermediary system first (represented as “sysB”), and then to the target system (represented as “sysC”).
Perform the following steps to activate with hot backup for cascading replication:
- On all systems, go to the bin sub-directory of the SharePlex product directory, and start sp_cop and sp_ctrl
- On all systems, verify that the SharePlex processes are running.
sp_ctrl> status
- On the intermediary and target systems, stop the Post process. This allows replicated data to accumulate in the post queue until the databases are recovered.
-
sp_ctrl> stop post
- On the source system, run the Oracle hot backup to the intermediary and target systems.
-
When the backup is finished, activate the configuration on the source system.
sp_ctrl> activate config filename
-
On the source system, view activation status.
Note: The command retains control of sp_ctrl until activation is finished.
-
When activation is complete, switch log files on the source system.
On-premises database:
svrmgr1> alter system switch logfile;
Amazon RDS database:
Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.
- Make a note of the highest archive-log sequence number.
- On the intermediary system, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log from the previous step.
- On the intermediary system, open the database with the RESETLOGS option.
-
On the intermediary system, run Database Setup on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).
Would you like to create a new SharePlex user [y]. n
-
[Optional] If you are using named post queues and are unsure of the queue names, issue the qstatus command.
sp_ctrl> qstatus
- On the intermediary system, issue the reconcile command for each post queue. For seq sequence_number, substitute the sequence number of the log that you noted previously.
sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number
Example: reconcile queue SysA for o.oraA-o.oraA seq 1234
-
On the intermediary system, run the cleanup.sql script to truncate all of the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
- On the intermediary system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
- On the intermediary system, disable check constraints and scheduled jobs that perform DML.
-
On the intermediary system, set the SP_OCT_REPLICATE_POSTER parameter to 1. This directs SharePlex to capture posted changes on that system and replicate them to the target system.
sp_ctrl> set param SP_OCT_REPLICATE_POSTER 1
-
[Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.
Important! Do not start any Post processes yet.
-
On the target system, recover the database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle has fully applied the log that you reconciled to in the previous steps taken on the intermediary system.
- On the target system, open the database with the RESETLOGS option.
-
On the target system, run Database Setup on the database. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).
Would you like to create a new SharePlex user [y]. n
Note: SharePlex can remain running during the setup process. For more information about Database Setup, see Database Setup Utilities in the SharePlex Reference Guide.
-
On the target system, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
- On the target system, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
- On the target system, disable check constraints and scheduled jobs that perform DML.
-
[Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables.
-
On the intermediary system, activate the configuration file.
sp_ctrl> activate config filename
-
On the intermediary system, monitor activation status.
Note: The command retains control of sp_ctrl until activation is finished.
-
On the intermediary and target systems, start the Post process. All instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.
sp_ctrl> start post
-
[Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.
Activate replication with PostgreSQL hot backup on an active database
Use this procedure to use PostgreSQL hot backup to establish a target PostgreSQL instance and activate replication without quieting the source database. This procedure involves using the reconcile command to ensure that transactions which occurred after the point of backup are applied to the target, while eliminating redundant replicated transactions that were already captured by the backup.
Supported databases
PostgreSQL to PostgreSQL
Supported replication strategies
All replication strategies are supported with the following limitations
| Peer-to-peer |
To establish peer-to-peer replication, you must:
-
Quiet all the systems except the trusted source system for the duration of this procedure.
-
Move all users to the trusted source system, and then follow this procedure.
Only after this procedure has been performed on all the secondary systems, users may resume activity on them. |
Requirements
-
Read the requirements for activating a configuration file as mentioned in the user guide.
-
Make certain a SharePlex database account exists in the source database (only). This account is usually created when SharePlex is installed for the first time. See the SharePlex Installation and Setup Guide for more information.
-
Before you start, review this procedure and see the User Guide for more information about the commands that are used.
Troubleshooting
-
If the configuration fails to activate, you can find information about the failure in these places:
-
Use the show log command to view the event_log.
-
View the activation process log, which is a file named dbname_oconf##.log in the log sub-directory of the SharePlex variable data directory.
Procedure
SharePlex uses the "Activation with hot backup: all strategies except cascading" procedure for activation with a hot backup.
Activation with hot backup: all strategies except cascading
Use this procedure for all replication strategies except cascading replication, where SharePlex will be posting to a database on the intermediary system.
Perform the following steps to activate with hot backup for all strategies except cascading:
-
On the source system, navigate to the bin sub-directory and run the ‘pg_setup’ utility. On the target system, the database will be in the stopped state as backup has not been applied yet; hence, pg_setup cannot be executed at this point.
-
On both the systems, navigate to the bin sub-directory of the SharePlex product directory, and start sp_cop and sp_ctrl.
-
On the source system, activate the configuration.
sp_ctrl> activate config filename
-
On the target system, the Post process will be in the idle or stopped due to error (if pg_setup was executed before) state as the database is not running, hence stop the Post process if it is in the idle state. This allows replicated data to accumulate in the Post queue until the target database has been recovered and reconciled.
sp_ctrl>stop post
-
On the target system, execute the pg_basebackup utility, which will take a hot backup from the source system and prepare a data directory for the target PostgreSQL database. Once it is completed, a database can be started on the target system.
Follow the below steps to start the database at the target system:
-
Add an entry for the target system in the pg_hba.conf file at the source system.
| host |
replication |
all |
<ip_target>/32 |
trust |
|
Following is the description of field used:
ip_target : IP address of the target system where the backup needs to be restored |
-
Take a backup of the existing database and create an empty PostgreSQL data directory.
mv <pg_data_dir_target> <pg_data_dir_target>_old
mkdir <pg_data_dir_target>
-
Execute the pg_basebackup utility from the bin directory of PostgreSQL installation at the target system.
./pg_basebackup -h <source_ip> -p <source_port> -U <user_id> -X f -D <pg_data_dir_target>
Description of the fields used:
-
source_ip : IP address of the source system where the PostgreSQL database is running
-
source_port : Port of the PostgreSQL database at the source system
-
user_id : Database user ID with replication role (SharePlex user has replication role, so it can be used here)
-
pg_data_dir_target : PostgreSQL data directory path at target system
-
Once the backup is done on the target system, a backup_manifest file will be created in pg_data_dir_target. Open this file, navigate to the end, and find End-LSN under the WAL-Ranges tag. Keep note of the End-LSN value.
-
On the target system, run the pg_setup utility. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).
NOTE: SharePlex can remain running during the setup process.
-
[Optional] If you are using named post queues and are unsure of the queue names, issue the qstatus command.
sp_ctrl>qstatus
-
On the target system, issue the reconcile command as follows. If you are using named post queues, issue the command for each one. The LSN number is the one which we have noted down in step 5, which is End-LSN.
sp_ctrl>reconcile queue queuename for datasource-datadest pglsn lsn_number
Example:reconcile queue SysA for r.dbA-r.dbB pglsn 0/C7000100
NOTE: The command retains control of sp_ctrl until the reconcile process is finished.
-
On the target system, run the cleanup_pg.sql script to truncate the SharePlex internal tables if setup is configured as a source during ‘pg_setup’. It is present in bin sub-directory of the SharePlex product directory. To execute this, login to psql using the \i <path_to_script> command.
-
[High availability and peer-to-peer only] On the target (secondary) system, activate the configuration.
sp_ctrl>activate config filename
-
On the target system, start the Post process. The two instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.
sp_ctrl>start post
Activate replication with an Oracle hot backup on a quiet database
Use this procedure to use an Oracle hot backup to establish a target Oracle instance and activate replication if user activity can be stopped while the procedure is performed. This procedure can be used for all replication configurations.
Preliminary considerations
Read these points before you proceed.
Supported databases
Oracle source and Oracle target
Supported replication strategies
All but high-availability. This procedure is not appropriate for a high-availability strategy because it requires the source database to be quieted while the backup is taken and the configuration file is being activated.
Certain limitations apply:
| Consolidated replication (many sources to one target) |
To establish consolidated replication, the use of a hot backup from all source systems is not possible. A backup from one source will override the data that was applied by a backup from a different source. You can use a hot backup of one of the source instances to establish a target instance, and then use another copy method to apply the objects from the other source instances. Possible methods include:
|
Requirements
- [Unix and Linux systems] Verify that the ORACLE_SID and ORACLE_HOME in the oratab file are correct for the instance you will be establishing with the hot backup. The SID must be the SID used in the routing map in the configuration file that you will be activating.
- Read the requirements before you start this procedure. For more information, see Requirements for Activating a Configuration.
- Users must stop accessing the production database while the hot backup and configuration activation take place.
- Make certain a SharePlex database account exists in the source database (only). This account usually is created during SharePlex installation. See the SharePlex Installation and Setup Guide for more information.
- Before you start, review this procedure and see theSharePlex Reference Guide for more information about the commands that are used.
Procedure
Note: If you are not using cascading replication, ignore all references to an intermediary system. For more information, see Configure Replication through an Intermediary System.
- On the source system, complete the Oracle hot backup.
- On the source system, stop user access to the source database by shutting it down and opening it in restricted mode.
-
On the source system, switch the redo logs.
On-premises database:
svrmgr1> alter system switch logfile;
Amazon RDS database:
Run the Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.
- Keep a record of the sequence number of the current log.
- On all systems, start sp_cop and sp_ctrl from the bin sub-directory of the SharePlex product directory.
-
On all systems, verify that sp_cop and sp_ctrl are running.
sp_ctrl> status
-
On the intermediary and target systems, stop Post. Stopping Post allows replicated data to accumulate in the post queue until the databases have been recovered.
sp_ctrl> stop post
-
On the source system, activate the configuration file.
sp_ctrl> activate config filename
-
On the source system, view activation status.
Note: The command retains control of sp_ctrl until activation is finished.
- When the activation is finished, allow users to resume access to the source database.
- List the archive logs on the intermediary and target systems. Delete any logs made after the one for which you made a record.
- On the intermediary and target systems, recover the database to the log number that you recorded. Make sure a full recovery is performed.
- On the intermediary and target systems, open the database.
-
On the intermediary and target systems, run the Database Setup utility for the target instance. When prompted for the SharePlex database user, enter n to choose the existing user and password (these were copied in the backup).
Would you like to create a new SharePlex user [y].n
- On the intermediary and target systems, run the cleanup.sql script to truncate the SharePlex internal tables. Instructions for running this script are in the SharePlex Reference Guide.
- On the intermediary and target systems, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
- On the intermediary and target systems, disable check constraints and scheduled jobs that perform DML.
- [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables on the intermediary and target systems.
-
[Intermediary system only] On the intermediary system, set the SP_OCT_REPLICATE_POSTER parameter to 1. This directs SharePlex to capture posted changes on that system and replicate them to the target system.
sp_ctrl> set param SP_OCT_REPLICATE_POSTER 1
-
On the intermediary system, activate the configuration file.
sp_ctrl> activate config filename
-
On the intermediary system, monitor activation status.
Note: The command retains control of sp_ctrl until activation is finished.
-
When activation is finished, start the Post process on the intermediary and target systems. All instances are now in synchronization, and SharePlex will continue replicating to maintain synchronization.
sp_ctrl> start post
-
[Optional] If this was only a partial backup, drop the tablespaces that were not copied over during the hot backup.
Activate replication with Oracle transportable tablespaces
Use this procedure to use the Oracle transportable tablespaces feature to establish a target Oracle instance and activate replication. It enables you to synchronize and resynchronize numerous objects quickly and with minimal downtime. It allows you to export just the metadata (data dictionary) and then copy the data files. This method also moves indexes so that there is no need to rebuild them in the target database, and you can move multiple tablespaces at one time.
Note: This document does not provide instructions for how to use transportable tablespaces. This procedure should be performed by someone who has a solid understanding of database copy methods.
Preliminary considerations
Read these points before you proceed.
Supported databases
Oracle source and Oracle target
Supported replication strategies
All replication strategies. This procedure may not appropriate for a high-availability strategy if the source database cannot be quieted even briefly.
Requirements
Naming conventions used
In this procedure, the "source" system is one of the following:
- The source system of a single-direction replication configuration, including cascading replication.
- All source systems of a consolidated replication configuration.
- The trusted source system in a peer-to-peer replication configuration.
- The primary node of a cluster (where the cluster VIP is running).
In this procedure, the "intermediary" system only needs to be part of this procedure if SharePlex will be posting to, and capturing from, an intermediary system in a cascading configuration.
In this procedure, the "target" system is one of the following:
- The target system of a single-direction replication configuration, including cascading and consolidated replication.
- The secondary systems in a peer-to-peer replication configuration.
- The primary node (where the cluster VIP is running) of the target cluster.
In this procedure, the SharePlex commands in the procedure apply to all sp_cop instances that apply to the replication strategy you are using (for example, all sp_cop processes on a target in consolidated replication).
Procedure
-
On the source system, set the source tablespaces that you want to copy to READ ONLY.
svrmgr1> alter tablespace name read only;
-
On the source system, activate the configuration file.
sp_ctrl> activate config filename
- On the source system, start sp_cop and sp_ctrl from the bin sub-directory of the SharePlex product directory.
-
On the source system, verify that sp_cop and sp_ctrl are running.
sp_ctrl> status
-
On the intermediary and target systems, stop Post. Stopping Post allows replicated data to accumulate in the post queue until the databases have been recovered.
sp_ctrl> stop post
- On the source system, export the metadata to an export file.
- When the export is finished, copy the datafiles to another location on the source system. This minimizes the impact on the source database of copying the files to the target system.
-
Set the source tablespaces back to read/write mode.
svrmgr1> alter Tablespace name read write;
- If any of the copied datafiles and tablespaces exist in the intermediary or target database, drop them so that the copied files can be applied.
- Copy the files from the new location on the source system to the intermediary and target systems.
- On the intermediary and target systems, use the Oracle import utility to import the metadata and the tablespace definitions.
- On the intermediary and target systems, set the tablespace(s) to read/write mode.
- On the intermediary and target systems, open the Oracle instances.
-
On the intermediary and target systems, disable triggers on the tables, or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
- On the intermediary and target systems, disable check constraints and scheduled jobs that perform DML.
- [Partitioned replication only] If you are using vertically partitioned or horizontally partitioned replication for any tables, delete the unneeded columns and rows from those tables on the intermediary and target systems.
-
[Intermediary system only] Set the SP_OCT_REPLICATE_POSTER parameter to 1. This directs SharePlex to capture posted changes on that system and replicate them to the target system.
sp_ctrl> set param SP_OCT_REPLICATE_POSTER 1
-
[Intermediary system only] Activate the configuration file.
sp_ctrl> activate config filename
-
[High availability] On the target system, stop the Export process.
sp_ctrl> stop export
-
[High availability and peer-to-peer replication] Activate the configuration on the target system(s).
sp_ctrl> activate config filename
-
Start Post on the intermediary and target systems. SharePlex begins executing the SQL statements that have been collecting in the post queue, keeping the source and target data in sync.
sp_ctrl> start post
- [Peer-to-peer replication] Allow users to access the databases on all systems.
