Where and when to create the SharePlex groups on Unix and Linux depends on whether you install SharePlex as a root or non-root user.
* The groups must exist because the installer adds the SharePlex Administrator user to the spadmin group during the installation process. In a cluster, this user is only added to the primary node. You must add the SharePlex Administrator user to the other nodes.
To create the groups in /etc/group :
# groupadd spadmin
# groupadd spopr
# groupadd spview
To assign a user to a group:
Add the Unix or Linux user name to the appropriate group. To assign a list of user names to a group, use a comma-separated list (see the following example).
spadmin:*:102:spadmin,root,jim,jane,joyce,jerry
If the password field is null, no password is associated with the group. In the example, the asterisk (*) represents the password, “102” represents the numerical group ID, and spadmin is the group. The group ID must be unique.
Save the file.
Users can verify their authorization levels by issuing the authlevel command in sp_ctrl.
Set up Oracle logging to support SharePlex
Set up Oracle database objects for replication
Set up an Oracle database to support SharePlex
This chapter contains instructions for preparing an Oracle source or target database environment for replication. The tasks outlined in this chapter must be performed before you perform initial synchronization to start replication in your environment. Perform all tasks that apply to your data and to your replication objectives.
SharePlex captures from the online and archived Oracle redo logs. SharePlex supports redo logs and datafiles that are stored on raw devices, on filesystem devices, and ASM instances.
If the Capture process stops (or is stopped by a SharePlex user) while replication is active, Capture records its location in the redo log and continues from that point when started again. However, Capture may need to read the archive logs instead of the redo logs if the following conditions occur:
To support uninterrupted capture when an online log is not available, archive logging must be enabled on the source system and any other system from which SharePlex will capture data, such as an intermediate system in a cascading replication strategy. Otherwise, if the online logs wrap before Capture is finished processing them, a resynchronization of the source and target data will be necessary.
To avoid Capture issues, configure archive logging to support faster and uninterrupted replication as follows:
Requirement | Description |
---|---|
Time compression and removal properly | Do not compress or remove the archive logs until SharePlex is finished processing them. Otherwise, SharePlex returns a "log wrap detected" message and will stop because it cannot process the data. To determine the current log for SharePlex, issue the show capture command with the detail option in sp_ctrl on the source system. You can compress any logs that were generated before the current one. |
Specify a non-default archive location | If you are storing the archive logs in a location other than the Oracle default, set the SP_OCT_ARCH_LOC parameter to the full path name of the directory where the archive logs are located. If the redo logs wrap, SharePlex searches for the archive logs in Oracle’s archive log list. If SharePlex does not find the archive log there, it searches the directory or directories specified by the SP_OCT_ARCH_LOC parameter. To have Capture go directly to the SP_OCT_ARCH_LOC location and skip the read of the Oracle log list, set the SP_OCT_CK_LOC_FIRST to 1. |
Configure Capture to wait for log-management processes | If using SP_OCT_ARCH_LOC and you are using an automated method to move the logs to that location, you can configure Capture to wait a certain amount of time for the move to be completed. This prevents Capture from stopping because a log it needs is not yet available. Capture waits, checks for the logs, stops if they are not yet available, and continues checking and stopping until the logs are available. To configure Capture to wait, set the SP_OCT_LOGWRAP_RESTART parameter to the number of seconds that you want Capture to wait. Monitor these processes regularly to prevent replication latency. |
Disable archive logging on the target | You can disable archive logging on the target systems to eliminate unnecessary Oracle activity on that system, except for high availability or peer-to-peer strategies. |
Do not place logs at the root ASM location |
If the database uses ASM, the Oracle redo logs (online and archive) cannot be located under the ASM root directory. SharePlex will not be able to read them at that location. |
ASM raw device permission | The ASM 'oracle' user must have permission to access the raw device. For example, when the raw device permission default is u:root g:disk add the 'oracle' user group 'disk'. Giving permission only to the 'grid' user is insufficient. |
Ideally, the redo logs should be configured so that SharePlex can avoid reading the archive logs. In most cases, reading the online logs is faster than reading the archives. Ensure that the online redo logs are large and numerous enough to minimize processing from the archive logs. At minimum, there should be enough redo log capacity to hold several hours worth of data without wrapping.
To test an appropriate online log configuration:
In pre-production testing, you can determine if Capture is reading the archive logs by doing the following:
Determine the log that SharePlex is processing by querying the SHAREPLEX_ACTID table.
SQL> select seqno from splex.shareplex_actid
Determine the log that Oracle is writing to by querying Oracle's V$LOG table.
SQL> select sequence# from v$log where status='CURRENT'
Important: If Capture falls behind the pace at which Oracle generates redo volume, the following may apply:
In addition to minimal supplemental logging, it is strongly recommended that you enable both primary key and unique key supplemental logging, or that you create a supplemental log group on unique columns for every table in replication. When the key column values for a row update are in the redo log, SharePlex does not have to fetch them from the database. On a busy system, this greatly improves the performance of the Read process. Some SharePlex features require primary key and unique key logging to be enabled.
Note:
ALTER TABLE DDL commands that change the rowid of a table can affect subsequent DML operations if the primary or unique keys of the tables in replication are not being logged. When the keys are not logged, SharePlex fetches their values based on the rowid. Any operation that changes the rowid, such as ALTER TABLE...MOVE, can cause the wrong key values to be used for subsequent DML operations. |
This topic provides information about how to configure certain characteristics of Oracle database objects that you will be replicating with SharePlex.
SharePlex must have a way to ensure that the row it is changing on the target is the correct one that matches the source row. This is accomplished by ensuring a one-to-one relationship through the use of keys and indexes.
SharePlex works fastest when there is a primary key or a unique key on all source and target tables being replicated, especially large tables and tables containing LONG columns. When choosing a key to use, SharePlex uses the best available key column in the following order of priority.
Unique key with the fewest columns.
For best performance, it is recommended that you enable primary and unique key supplemental logging.
If a table does not have a primary or unique key, or if Oracle logs the wrong unique key for SharePlex, you can specify columns for SharePlex to use as a key when you create the configuration file. This is known as a key definition and is specified in the configuration file. For more information, see Define a Unique Key in the SharePlex Administration Guide.
An alternative to a key definition is to create or use a unique index based on one or more columns that establish uniqueness.
When primary and unique key supplemental logging is enabled and a table does not have a primary key, Oracle has to decide what type of unique key to log. If the table has multiple unique keys, Oracle determines the best key to use and logs those column values for every UPDATE. If a table does not have any type of key, Oracle logs all columns that are not a LONG or a LOB.
SharePlex must also identify a key to use to replicate data. Like Oracle, SharePlex chooses a key in this order:
If a table being replicated by SharePlex does not have a primary key, but does have multiple unique keys, it is possible that the key columns that Oracle logs are not those that SharePlex requires.
If SharePlex cannot detect a key or unique index on a table, it constructs a key using all of the columns except LONGs and LOBs. This key is maintained internally and is not created on the table itself.
This is not a desirable option, because the resultant WHERE clause causes Oracle to perform a full table scan on the target table to find the row, and that significantly slows replication. Additionally, row uniqueness cannot be enforced.
For example, if there is a possibility that non-LONG columns in different rows could contain identical values, but the LONG columns could have different values, the table can be out of synchronization without being detected by you or SharePlex. The following example illustrates the problem. The rows in the table are identical except for the LONG columns, and there is no primary or unique key.
COLUMN A | COLUMN B | COLUMN C (LONG) |
---|---|---|
10 | 20 | 100 |
10 | 20 | 200 |
10 | 20 | 300 |
Suppose a user on the source system changes Column A to 15 in the first row. When constructing the SQL statement to apply the change to the target table, SharePlex constructs a key using Columns A and B (UPDATE tablename SET Column A to 15 WHERE Column A = 10 and Column B = 20) to locate the row to change. There are three rows that meet this criteria, so SharePlex could post the change to the wrong row.
If a key permits NULLs, SharePlex cannot ensure the uniqueness of a row for UPDATEs and DELETES, so there is a possibility it could change the wrong row on the target system. To control how SharePlex addresses keys that permit NULLs, set the SP_SYS_IN_SYNC parameter. See the SharePlex Reference Guide for more information.
SharePlex handles changes to the value of a key column without any special settings. However, if sequences are used for keys and if there is a possibility that those values will be updated, create the sequences so that the updates cannot result in a duplication of keys on the target system. Otherwise, if a new value is used to apply an operation, and that value already exists as a key in another row in the target table, SharePlex will return unique-key constraint violations and out-of-sync errors. This type of error can happen when you update values using an “x +n” formula, where n is an incremental increase. It is possible for one of the “x +n” values to equal an existing value.
The following is an example, where the values in the key column are increased by 1:
Key_Col
1
4
5
7
SQL> update table X set a=a+1; commit
The new values become the following, and they are replicated to the target system:
Key_Col
2
5
6
8
SharePlex performs the update in the order that the operations enter the redo log:
update x set a=2 where a=1; (Succeeds)
update x set a=5 where a=4; (Fails because a value of 5 already exists.)
update x set a=6 where a=5; (Succeeds)
update x set a=8 where a=7; (Succeeds)
The pre-image value that Post uses for the target sequence is the same as the increased value replicated from the source. Oracle rejects the operation as a unique constraint violation. Another example would be a transaction that updates A to B and then updates B to C.
Important! If you plan to use peer-to-peer replication, there are additional requirements for keys. For more information, see the Configure Replication to Maintain Multiple Peer Databases section in the SharePlex Administration Guide.
The correct use of indexes is important in a replication environment. An index maintains the integrity of the target data.
If tables have foreign keys, make sure the appropriate columns are indexed so that modifications to the foreign keys do not cause full table scans.
If there are too many indexes on a target table, Oracle must update them all as rows are added and deleted. This slows the entire system, including replication. Consider limiting the number of indexes to the ones that have the most utility.
For applications that perform mostly one type of DML, consider the following:
If you run large batch jobs that do millions of SQL operations, remove unnecessary indexes before the batch job, then rebuild them at the end. This makes SharePlex run faster, and you will have more organized indexes afterward.
For performance purposes, avoid the use of bitmap indexes while the Post process is applying the data. These indexes can adversely affect the performance of the Post process.
If you must use bitmap indexes on target tables, weigh their benefit for queries against the their impact on the transactions applied by Post:
Note: Replicating bitmap indexes is not recommended. Every time you change a table with a bitmap index, the index is rebuilt. This associated cost (Oracle time and resources) to rebuild is added to your SQL UPDATE statement.
DML changes resulting from a trigger firing on the source system enter the redo log and are replicated to the target database by SharePlex. Consequently, if the same trigger fires on the target system and initiates the same DML changes (which already were made through replication) there will be out-of-sync errors.
For example, if an INSERT to TableA on the source system triggers an INSERT into TableB, SharePlex replicates both INSERTs to the target system. The Post process applies the first INSERT to TableA on the target system, triggering an INSERT into TableB. Thus, when Post attempts to post the replicated INSERT to TableB, there will be a unique-key violation. The row already exists because the trigger fired for TableA.
Triggers can be handled as follows, depending on your replication strategy:
Replication strategy | How to handle triggers on the target |
---|---|
High availability and Peer-to-peer |
|
Reporting, data sharing, other basic one-way replication |
|
Triggers on objects not in the replication configuration can remain active.
For important information about how to use the trigger scripts, see theSharePlex Reference Guide.
Integrity constraints have a effect on replication. Follow these guidelines to ensure that they are handled.
Foreign key constraints must be disabled on the target tables. SharePlex replicates the results of the source foreign key constraints. Tables with foreign keys to one another must all be included in the replication configuration for accurate replication of the source foreign key results. All tables with referential constraints must exist in the target database. If you leave one or more out, the referential integrity could become corrupted.
Note: If constraints are DEFERRED on the target tables, the Post transaction can fail on the constraint validation. To work around this problem, enable the SP_OPO_DISABLE_OBJNUM parameter to allow the transaction to succeed. The underlying target table still remains out-of-sync until it is resynchronized.
SharePlex provides a feature that allows ON DELETE CASCADE constraints to remain enabled on the target tables, but it must be enabled explicitly through a parameter setting. Post detects the ON DELETE CASCADE dependencies and suppresses the posting of any replicated cascaded deletes to the child tables.
If you do not enable this support through SharePlex you must disable these constraints manually on the target. Otherwise SharePlex replicates both the primary delete and the cascaded deletes, which results in conflicts and errors when the deletes cascade on the target.
To enable ON DELETE CASCADE support:
Set the following SharePlex parameters:
NOTE: In peer-to-peer replication, SP_OPO_REDUCED_KEY must be set to 0.
Disable check constraints on the target system. They add unnecessary overhead. These checks are redundant in a well maintained, synchronized replication environment because they are satisfied on the source system. For high-availability purposes, you can build a script that re-enables the constraints as part of the failover procedure.
In all scenarios except peer-to-peer replication, the SharePlex database user must be the only user allowed to perform DML or DDL on the target objects. If DML or DDL changes are made to target objects by other individuals, jobs or applications, the target data may no longer reflect the state of the data on the source system. For more information, see the Understand the Concept of Synchronization section in the SharePlex Administration Guide.
SharePlex replicates changes to Oracle sequences that are made in an ALTER SEQUENCE and DROP SEQUENCE commands and during DML transactions. It may not be necessary to replicate sequences in certain replication strategies.
High availability: Yes
The way that SharePlex replicates sequences allows users to begin using the failover database immediately without worrying about incrementing the sequences or reusing one.
Reporting, data sharing, other basic one-way replication: No
If sequences are unnecessary on the target system, do not replicate them. It can slow down replication. Even if a sequence is used to generate keys in a source table, the sequence values are part of the key columns when the replicated rows are inserted on the target system. The sequence itself does not have to be replicated.
Peer-to-peer: No
SharePlex does not support peer-to-peer replication of identical sequences. For more information, see the Configure Replication to Maintain Multiple Peer Databases section in the SharePlex Administration Guide.
To configure sequences for replication:
To replicate sequences, the supplemental logging of primary and unique keys must be enabled at the database level, or you must enable supplemental logging for primary keys on the sys.seq$ table.
To ensure the uniqueness of sequences on the target system, the start value of the target sequence must be larger than the start value of the source sequence. Use the following formula to determine the target START_WITH value:
source_current_value+ (source_INCREMENT_BY_value x source_CACHE_value) =target_START_WITH_value
Important! (source_INCREMENT_BY_value x source_CACHE_value) should not exceed 2 GB, or replication of sequences will fail.
SharePlex uses the ALTER SEQUENCE command to update sequences in a target database as follows:
Change the increment value to:
source_INCREMENT_BY_valuexsource_CACHE_value
ALTER the sequence(s) again by setting the following values:
Increment_value=source_INCREMENT_BY_value
Cache_value=source_CACHE_value
SharePlex treats ALTER SEQUENCE operations like a simple SELECT (UPDATE) to a sequence because the redo log records make no distinctions between the two operations.
© ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center