Build a configuration file using a script
SharePlex provides the following scripts to automate the building of a configuration file to specify Oracle source objects.
- config.sql: configure all tables and optionally all sequences in the database.
- build_config.sql: configure multiple or all tables in a schema
Supported databases
Oracle
Use config.sql
The config.sql script enables you to build a configuration that lists all of the tables, and optionally all of the sequences, in all of the schemas of a database. This script saves time when establishing a high-availability replication strategy or other scenario where you want the entire database to be replicated to an identical secondary database.
Conditions for using config.sql
To run config.sql
-
Change directories to the config sub-directory of the SharePlex variable-data directory. The config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.
cd /vardir/config
- Log onto SQL*Plus as SYSTEM.
-
Run config.sql using the full path from the util sub-directory of the SharePlex product directory.
@ /proddir/util/config.sql
Refer to the following table when following the prompts.
| Target machine |
The name of the target machine, for example SystemB. |
| Source database SID |
The ORACLE_SID of the source (primary) Oracle instance, for example oraA. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
| Target database SID |
The ORACLE_SID of the target (destination) Oracle instance, for example oraB. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
| Replicate sequences |
Enter y to replicate sequences or n not to replicate sequences. |
| SharePlex oracle username |
The name of the SharePlex user in the source database. This entry prevents the SharePlex schema from being replicated, which would cause replication problems. If a valid name is not provided, the script fails. |
Note: The name assigned by SharePlex to the configuration is config.file. If you run the script again to create another configuration file, it overwrites the first file. To preserve the original file, rename it before you create the second one.
Next steps:
-
If any tables or owners are case-sensitive, open the configuration file with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.
sp_ctrl> edit config filename
-
To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.
sp_ctrl> list config
Use build_config.sql
The build_config.sql script enables you to build a configuration that contains multiple (or all) tables in a schema. It is an interactive script that prompts for each component of the configuration step by step. Instead of entering the information for each object and the routing individually, you can use a wildcard to select certain tables at once, or you can select all of the tables in the schema.
Conditions for using build_config.sql
- Source and target table names must be the same.
- The script does not support sequences. Before you activate the configuration that the script builds, you can use the edit config command in sp_ctrl to add entries for sequences.
- The script does not support partitioned replication. You can use the copy config command to copy the configuration that the script builds, then use the edit config command to add entries for the tables that use partitioned replication. Activate the new configuration, not the original.
- The script does not configure objects in the SYS, SYSTEM, and SharePlex schemas. These schemas cannot be replicated since they are system and/or instance-specific.
- You can run build_config.sql for different schemas, then combine those configurations into one configuration by using a text editor. Make certain to eliminate all but one Datasource:o.SID line, which is the first non-commented line of the file. Do not move the file out of the config sub-directory.
- You can use the edit config command to make any other changes as needed after the configuration is built.
To run build_config.sql
-
Change directories to the config sub-directory of the SharePlex variable-data directory. The build_config.sql script puts configurations in the current working directory, and SharePlex configurations must reside in the config sub-directory.
cd /vardir/config
- Log onto SQL*Plus as SYSTEM.
-
Run build_config.sql using the full path from the util sub-directory of the SharePlex product directory.
@ /proddir/util/build_config.sql
Refer to the following table when following the prompts.
| Target machine |
The name of the target machine, for example SystemB. |
| Source database SID |
The ORACLE_SID of the source (primary) Oracle instance, for example oraA. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
| Target database SID |
The ORACLE_SID of the target (destination) Oracle instance, for example oraB. Do not include the o. keyword. The ORACLE_SID is case-sensitive. |
| Owner of the source database tables |
The owner of the source tables. |
| Owner of the target database tables |
The owner of the target tables. |
| Table name to include (blank for all) |
Do one of the following:
- Press Enter to accept the default, which selects all tables that belong to the source owner.
- Enter a wildcard (%) character and a string to select certain tables, for example %e_salary%.
- Enter an individual table name.
|
| Name of the output file to create |
A name for the configuration. The script gives the file a .lst suffix, for example Scott_config.lst. |
Next steps:
-
If any tables or owners are case-sensitive, open the configuration with the edit config command in sp_ctrl, then use the text editor to enclose case-sensitive table and owner names within double-quote marks, for example “scott”.“emp”. The script does not add the quote marks required by Oracle to enforce case-sensitivity.
sp_ctrl> edit config filename
-
To ensure that the configuration is in the correct location, issue the list config command. If the name of the configuration is not shown, it was created in the wrong directory. Find the file and move it to the config sub-directory of the variable-data directory.
sp_ctrl> list config
Configure named queues
This chapter contains instructions for using the advanced SharePlex configuration options of named queues. These options provide an additional level of flexibility to divide and parallelize data to meet specific processing and routing requirements. Before proceeding, make certain you understand the concepts and processes in Configure data replication.
Contents
Configure named export queues
A named export queue is an optional, user-defined export queue that is attached to its own Export process. SharePlex creates each named Export queue and associated Export process in addition to the default export queue-process pair. When SharePlex creates a named export queue-process pair, it also creates a dedicated Import process, post queue, and Post process on the target to contain that data stream.
You direct SharePlex to create one or more named export queues when you create your configuration file. Any data that is not configured for processing through a named export queue is processed through the default export queue.
Supported sources
Oracle
Supported targets
All
Benefits of named export queues
Use named export queues to isolate the replication of:
- Individual configurations: By default, SharePlex sends data from all active configurations through one export queue-process pair per target system, but the use of named Export queues enables you to separate each of those replication streams into its own export queue and Export process. In this way, you ensure that purge config or abort config commands that are issued for one configuration do not affect any of the others.
- Selected database objects: You can use a named export queue to isolate certain objects such as tables that contain LOBs. Because each named export queue has its own Import process, post queue, and Post process on the target, you are able to isolate the data the entire way from source to target. For more information about the benefits of named post queues, see Configure named post queues.
Additional benefits:
- You can stop the Export or Import process for one data stream, while allowing the others to continue processing.
- You can set SharePlex parameters to different settings for each export queue-process pair. This enables you to tune the performance of the Export processes based on the objects replicating through each one.
Considerations when using named export queues
Note: If Post returns the error message "shs_SEMERR: an error occurred with the semaphore" on a Windows system, the number of semaphores may need to be increased to accommodate the queues that you created. For more information, see Post stopped .
Configure a named export queue
Use the following syntax to define a routing map that includes a named export queue.
source_host:export_queuename*target_host[@database]
Configuration with named export queue in routing map
| Datasource: o.SID |
| src_owner.table |
tgt_owner.table |
source_host:export_queue*target_host[@database_specification] |
| source_host |
The name of the source system. |
| export_queue |
The name of the export queue. Queue names are case-sensitive on all platforms. Use one word only. Underscores are permissible, for example:
sys1:export_q1*sys2@o.myora |
| target_host |
The name of the target system. |
| database specification |
One of the following for the datasource:
o.oracle_SID
r.database_name
One of the following if the target is a database:
o.oracle_SID
o.tns_alias
o.PDBname
r.database_name
c.oracle_SID |
NoteS:
- Allow no spaces between any components in the syntax of the routing map.
- For more information about the components of a configuration file, see Configure data replication.
Examples
The following configuration files show two different datasources that are being replicated to two different databases on the same target system. Each datasource is routed through a named export queue.
| Datasource:o.oraA |
|
|
| scott.emp |
scott.emp |
sysA:QueueA*sysB@o.oraC |
| scott.sales |
scott.sales |
sysA:QueueA*sysB@o.oraC |
| Datasource:o.oraB |
|
|
| scott.prod |
scott.prod |
sysA:QueueB*sysB@o.oraD |
| scott.cust |
scott.cust |
sysA:QueueB*sysB@o.oraD |
The following shows how to separate a table that contains LOBs from the rest of the tables by using named export queues.
| Datasource:o.oraA |
|
|
| scott.cust |
scott.cust |
sysA:QueueA*sysB@o.oraC |
| scott.sales |
scott.sales |
sysA:QueueA*sysB@o.oraC |
| scott.prod |
scott.prod |
sysA:QueueA*sysB@o.oraC |
| scott.emp_LOB |
scott.emp_LOB |
sysA:QueueB*sysB@o.oraC |
Alternatively, you could simply define a named export queue for the LOB table and allow the remaining tables to be processed through the default export queue.
| Datasource:o.oraA |
|
|
| scott.cust |
scott.cust |
sysB@o.oraC |
| scott.sales |
scott.sales |
sysB@o.oraC |
| scott.prod |
scott.prod |
sysB@o.oraC |
| scott.emp_LOB |
scott.emp_LOB |
sysA:lobQ*sysB@o.oraC |
How to identify named export queues
You can view named export queues through sp_ctrl:
- Use the qstatus command to view all queues on a system.
- Use the show command to view all Export processes with their queues.
See the SharePlex Reference Guide for more infomation about theses commands.
Configure named post queues
A named post queue is an optional component of the routing map in the configuration file. A named post queue is a user-defined post queue with its own Post process, which together operate in parallel to the default post queue and Post process. You can define one or more named post queue-process pairs to establish a set of parallel Post replication streams.
Supported sources
Oracle
Supported targets
All
Benefits of named post queues
You can use named post queues to isolate data from different tables into two or more separate Post streams. By using named post queues, you can improve posting performance by isolating objects such as the following that cause processing bottlenecks:
- large tables
- objects that have LOB columns. Named post queues are recommended for objects that contain LOBs.
- objects that involve large transactions.
- any objects whose operations you want to isolate.
Process the remaining objects through additional named post queues, or use the default post queue. Objects in the configuration file with a standard routing map (host@target) are replicated through a default post queue.
You can use horizontal partitioning to divide the rows of very large tables into separate named post queues as an added measure of parallelism.
You can set SharePlex parameters to different settings for each queue-process pair. This enables you to tune the performance of the Post processes based on the objects replicating through each one.
Considerations when using named post queues
- The analyze config command can help you determine how to organize your tables into named queues, based on any dependencies they have and their individual transactional activity. Run the command over a period of time that captures typical database activity, and then view the command output.
- Assign each post queue a unique name.
- If objects are linked by relational dependencies, process all of those objects through the same named post queue. If interdependent objects are not replicated through the same post queue, parent and child operations may be applied out of order and will cause database errors. As an alternative to processing interdependent objects through the same queue, you can disable their referential constraints on the target. This may be acceptable, because the constraints are satisfied on the source system and then replicated to the target.
- When using multiple Posts, the target objects might not be changed in the same order as the corresponding source objects, possibly causing the target database to be inconsistent with the source database at any given point in time.
- If you implement named post queues for objects in an active configuration (thus changing the routing) SharePlex locks those objects to update its internal directions.
-
SharePlex has a maximum number of allowed queues. For more information, see Routing specifications in a configuration file.
Note: If Post returns the error message "shs_SEMERR: an error occurred with the semaphore" on a Windows system, the number of semaphores may need to be increased to accommodate the queues that you created. For more information, see Post stopped .
Configure a named post queue
If you are using named export queues, SharePlex creates a named post queue-process pair for each one by default. If you are not using named export queues, use the following syntax to define a named post queue in the configuration file by adding the :queue component to the routing map:
host:queue@target
Configuration with named post queue in routing map
| Datasource: o.SID |
| src_owner.table |
tgt_owner.table |
host:queue[@database_specification] |
| host |
The name of the target system. |
| queue |
The unique name of the post queue. Queue names are case-sensitive on all platforms. One word only. Underscores are permissible, for example:
sys2:post_q1@o.myora |
| database_specification |
One of the following for the datasource:
o.oracle_SID
One of the following if the target is a database:
-
o.oracle_SID
o.tns_alias
o.PDBname
r.database_name
c.oracle_SID |
NoteS:
Examples
The following configuration creates one post queue named Queue1 that routes data from table scott.emp and another post queue named Queue2 that routes data from table scott.cust.
| Datasource:o.oraA |
|
|
| scott.emp |
scott.emp |
sysB:Queue1@o.oraC |
| scott.cust |
scott.cust |
sysB:Queue2@o.oraC |
The following shows how a named post queue is specified when you are routing data in a pass-through configuration using an intermediary system. For more information, see Configure replication to share or distribute data.
| Datasource:o.oraA |
|
|
| scott.emp |
scott.emp |
sysB*sysC:Queue1@o.oraC |
How to identify a named post queue
A named post queue is identified by the datasource (source of the data) and one of the following:
- the name of an associated named export queue (if the Import is linked to a named export queue)
- the user-assigned post-queue name (if the Import is linked to a default export queue).
You can view named post queues through sp_ctrl:
- Use the qstatus command to view all queues on a system.
- Use the show command to view all Post processes with their queues.
See the SharePlex Reference Guide for more infomation about theses commands.
