|
|
|
||||
|
1.1 Purpose & Scope ----- ----- --------- Advanced replication environments that support an update-anywhere data model can be challenging to configure and manage. To help administer advanced replication environments, Oracle provides Replication Manager. Additional Information can be found in : - Replication Manager Online Help - Oracle8i Replication Manual - Oracle8i Concepts Manual It is essential that every detail is correct before using replication manager, customers frequently miss out one of the initial steps and this causes the replication environment to behave inconsistently. This article is intended to guide customers through the initial configuration steps that must be performed before using Replication Manager to setup a replication system. It does not cover how to build replication groups or objects, Oracle Support Services recommends customers use Replication Manager for this. This article is also aimed at customers who wish to use the replication management API because they require the additional flexibility it provides or they wish to use the trusted / untrusted security models. *** NOTE *** All steps described in this article MUST be performed at EACH site involved in multi master replication. 1.2 Replication Manager RDBMS compatibility -------- ----- ------ ----------- The first supported version of Replication Manager that can be used with Oracle8i (8.1.x) is Release 2.1.0, this release can also be used with Oracle7 or Oracle8. Release 2.0.0 was designed for use with Oracle8i but was only made available as a beta release product and is not supported by Oracle Support Services. The current 2.1.0 release of Replication Manager is designed for use on the Windows and Solaris operating systems only. For operating system compatibility refer to [NOTE:69062.1] 2. Init.ora parameters ====================== The following initialisation parameters must be set or added to the initSID.ora file, uses these as a minimum guide : Parameter Name Recommended Initial Value -------- ----- ------ -------- ----- ------ COMPATIBLE 8.1.5.0.0 (minimum), recommended to equate to the server release SHARED_POOL_SIZE Additional 20M (for basic) and an additional 40M or more for most / complex configurations PROCESSES Add 12 to the current value GLOBAL_NAMES TRUE DB_DOMAIN extension component of the local databases Global Name OPEN_LINKS 4 (Add 2 per additional master) DISTRIBUTED_TRANSACTIONS 5 (Add 2 per additional master) REPLICATION_DEPENDENCY_TRACKING TRUE JOB_QUEUE_INTERVAL 10 Seconds JOB_QUEUE_PROCESSES 3 (Add 1 per additional master) PARALLEL_MAX_SERVERS 10 PARALLEL_MIN_SERVERS 2 -------- ----- ------ -------- ----- ------ --- Remove any SNAPSHOT_ or the JOB_QUEUE_KEEP_CONNECTIONS parameters as they are no longer supported. The PARALLEL_ parameters are included above so that parallel propagation can be used, if you are going to use serial propagation (which is not recommended) these parameters do not need to be set. Throughout this article it is assumed that the DB_DOMAIN parameter is unset in the initSID.ora and thus defaults to WORLD, replace the .WORLD extension used later in this article with the appropriate DB_DOMAIN. 3. Tablespace Requirements ========================== These are the recommended initial tablespace requirements : Tablespace Recommended Initial Value -------- ----- ------ ----- ----- --------- ----- ------ SYSTEM At least 40 Mb free ROLLBACK SEGMENTS At least 30 Mb free TEMPORARY At least 20 Mb free It is also recommended that rollback segments have the next extent size set to at least 200k. These recommendations should be considered as an absolute minimum. 4. Install the Replication Catalogue ==================================== If you did not install your database with a replication catalog then either use the Database Configuration Assistant to change the database configuration to include the Advanced Replication Option or complete the steps listed below :- A. Start Server Manager at the command prompt: SVRMGRL B. Connect to the database as INTERNAL/password. Check that the SYS and SYSTEM users default tablespaces are set to the SYSTEM tablespace : SELECT username, default_tablespace FROM DBA_USERS WHERE username IN ('SYS', 'SYSTEM'); If they are not change them with : ALTER USER <username> DEFAULT TABLESPACE SYSTEM; C. Execute the script CATREP.SQL once the database has started. This script is found in the %ORACLE_HOME%\RDBMS\ADMIN directory. Allow approximately one hour for this to run . Before running CATREP.SQL it is a good idea to spool the output to a log file. SVRMGR> SPOOL output.log Once CATREP.SQL has completed, turn off the output spooling. SVRMGR> SPOOL OFF Check OUTPUT.LOG which is placed in the directory where Server Manager was invoked for errors before proceeding. D. Confirm that CATREP.SQL ran correctly by running a query on ALL_OBJECTS where STATUS = 'INVALID'. For example, SVRMGR> SELECT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE STATUS = 'INVALID'; If you find that any of the SYS or SYSTEM package bodies have compiled incorrectly, recompile them manually. SVRMGR> ALTER PACKAGE <package name> COMPILE BODY; If CATREP.SQL has run successfully, a number of replication catalog tables are created in the SYSTEM tablespace. The database is now setup for advanced replication. 5. Setup NET8 ============= A Net8 listener must be running on each of the servers involved in the replication environment and the listener.ora file must contain an entry for the instance. Also on each SERVER the must be a tnsnames.ora alias for every other replicated site. It is easy to confuse your local tnsnames.ora alias with the entry on the SERVER machines tnsnames.ora, so it may be best to test the alias from the SERVER. 6. Replication Manager Setup Wizard =================================== If all the previous steps have been completed at all sites to be involved in multi master replication you may prefer to use the Replication Manager Setup Wizard to complete the configuration. Although customers are advised to check the Global Database names are correct as described at the beginning of section 8, prior to this. Oracle Support Services recommends the wizard for simple configurations, but for customers with more complex environments or wishing to use the trusted / untrusted security model follow the remaining steps in this article. 7. Defining and granting privileges to Replication Users ======================================================== There are many ways that the users / privileges can be setup for multi master replication, the following are the most common and are recommended by Oracle Support Services, for alternative strategies please refer to the Oracle8i Replication manual. For setup to succeed, the main users MUST have identical names at all sites, this is because the names of these users are passed over as arguments to remote procedure calls. Passwords can differ as long as they are correctly defined in the appropriate database links, but for the Trusted and Untrusted security models the propagator passwords should differ as a minimum. Make sure each section (7.1, 7.2, 7.3, 7.4) that follows is checked in turn : 7.1. Replication Administrator (referred to here as REPADMIN) -------- ----- ------ ----- ----- --------- ----- ---- This user configures the replicated environment and performs administration of all replicated schemas / groups : CONNECT system/<password> CREATE USER repadmin IDENTIFIED BY <password>; ALTER USER repadmin DEFAULT TABLESPACE <tablespace name>; ALTER USER repadmin TEMPORARY TABLESPACE <tablespace name>; GRANT connect, resource TO repadmin; EXECUTE dbms_repcat_admin.grant_admin_any_schema('repadmin'); GRANT comment any table TO repadmin; GRANT lock any table TO repadmin; Previous releases allowed separate administrators to manage separate groups this is no longer available under Oracle 8.1. Although if groups do not span schemas, administrators can be restricted to a schema with the procedure grant_admin_schema instead of grant_admin_any_schema. 7.2. Replication Propagator / Receiver -------- ----- ------ ----- The propagator user is responsible for forwarding deferred transactions from the source master site to the destination master site. The receiver user is responsible for applying these transactions at the destination master site. The replication administrator, propagator and receiver are usually the same user, this configuration is the simplest and recommended for the majority of replication systems. Listed below are the available options, follow either steps 7.2.a or 7.2.b. : 7.2.a Single User for Administrator, Propagator / Receiver The replication administrator must also be registered as the propagator : CONNECT system/<password> EXECUTE dbms_defer_sys.register_propagator('repadmin'); GRANT execute any procedure TO repadmin; 7.2.b Separate Propagator / Receiver from Replication Administrator Under the single administrator, propagator/receiver architecture a single user has access to all replication groups, objects and procedures at all master sites. This may not be desirable and local administrators may wish to retain autonomy, if this is the case use either the TRUSTED or UNTRUSTED security models described below. Irrespective of the security model used only one propagator is allowed at each site and this user has the "execute any procedure" privilege. Follow only one of 7.2.b.1 or 7.2.b.2 : 7.2.b.1 TRUSTED : With the trusted security model, the receiver has access to ALL local master groups and as the receiver performs activities on behalf of the propagator it also has access to ALL master groups. It is usual to have a separate user for the propagator and receiver with the TRUSTED model, because the propagator has the "execute any procedure" privilege. If the same user was used it would be able to access all the packages at the receiving site, i.e. it would be no more secure than with the single user for administrator, propagator and receiver. Another benefit of this architecture is no user has "execute any procedure" at a remote site (no even the replication administrator). With the trusted model only one receiver is required (multiple receivers have no purpose since the trusted receiver can access all groups). Creating the propagator (referred to here as repprop) and receiver (referred to here as reprecv) are described below : CONNECT system/<password> CREATE USER repprop IDENTIFIED BY <password>; ALTER USER repprop DEFAULT TABLESPACE <tablespace name>; ALTER USER repprop TEMPORARY TABLESPACE <tablespace name>; EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repprop'); CREATE USER reprecv IDENTIFIED BY <password>; ALTER USER reprecv DEFAULT TABLESPACE <tablespace name>; ALTER USER reprecv TEMPORARY TABLESPACE <tablespace name>; BEGIN dbms_repcat_admin.register_user_repgroup( username => 'reprecv', privilege_type => 'receiver', list_of_gnames => NULL); END; The only privileges required by the receiver are granted via the register_user_repgroup procedure, it grants execute on the packages dbms_defer_internal_sys and dbms_defer, it does not grant direct query or DML access to objects. Once register_user_repgroup has been run for the receiver, calls to generate replication support on a replication object grant execute on the package <schema>.<object name>$RP, to allow the receiver to apply changes at the receiving master site. The generate also grants execute on sys.dbms_repcat_internal_package to the owner of the object support is being generated upon. In contrast the register_propagator includes the "execute any procedure" privilege, therefore it is very important that each propagator has a different password. 7.2.b.2 UNTRUSTED : With the untrusted security model, the receiver is granted only the privileges required to work with specific master groups. Customers may wish to use this architecture if they have replication groups that have a common receiver site but different remote sites, for instance : Master Site 1 Master Site 2 Master Site 3 ----- ----- ---- ----- ----- ---- ----- ----- ---- Master Group A <----> Master Group A Master Group B <----> Master Group B Master Group C <----> Master Group C Under this configuration the master definition site would normally be Master Site 2. A receivers can be registered to a number of groups and a single site can have multiple receivers, but a group can only have a single receiver registered to it. The untrusted security model is not required for and should not be used for the two master site configurations used by many customers. With this security model the propagator and receiver have to be different users. If the same user was used it would be able to access all the packages at the receiving site, i.e. it would be no more secure than with the single user for administrator, propagator and receiver. Another benefit of this architecture is no user has "execute any procedure" at a remote site (not even the replication administrator). Creating the propagator (referred to here as repprop) and receiver (referred to here as reprecv) are described below : CONNECT system/<password> CREATE USER repprop IDENTIFIED BY <password>; ALTER USER repprop DEFAULT TABLESPACE <tablespace name>; ALTER USER repprop TEMPORARY TABLESPACE <tablespace name>; EXECUTE DBMS_DEFER_SYS.REGISTER_PROPAGATOR('repprop'); CREATE USER reprecv IDENTIFIED BY <password>; ALTER USER reprecv DEFAULT TABLESPACE <tablespace name>; ALTER USER reprecv TEMPORARY TABLESPACE <tablespace name>; Before a user can be registered as the receiver for a group the group must exist at the site where the user is being registered. It may be better to perform this step after the groups have been created in replication manager, to create them at this point : CONNECT repadmin/<password> EXECUTE dbms_repcat.create_master_repgroup(gname => '<group name>'); CONNECT system/<password> BEGIN dbms_repcat_admin.register_user_repgroup( username => 'reprecv', privilege_type => 'receiver', list_of_gnames => '<list of master groups>'); END; The only privileges required by the receiver are granted via the register_user_repgroup procedure, it grants execute on the package dbms_defer_internal_sys, it does not grant direct query or DML access to objects. Once register_user_repgroup has been run for the receiver, calls to generate replication support on a replication object grant execute on the package <schema>.<object name>$RP, to allow the receiver to apply changes at the receiving master site. This privilege is only granted if the gernated object is in one of the receivers registered groups. The generate also grants execute on sys.dbms_repcat_internal_package to the owner of the object support is being generated upon. In contrast the register_propagator includes the "execute any procedure" privilege, so it is very important that each propagator has a different password. Due to [BUG:1034579] which is fixed in Oracle9i the receiver must also be granted execute on dbms_defer when using parallel propagation : CONNECT sys/<password> GRANT execute ON sys.dbms_defer TO reprecv; The most likely errors to be returned by a push if this privilege is not granted will be one of the following, depending on the trace file checked : PLS-00201: identifier 'SYS.DBMS_DEFER@...' must be declared ORA-01085: ... in deferred rpc to "SYS.DBMS_DEFER.PURGE_TRANSACTION_LOG" ORA-23388: replication parallel push watermark error dbms_defer is a powerful interface, consider the implications of the receiver having access to this package before granting this. A workaround is to use serial propagation (i.e. in section 9 define the schedule_push with parallelism =>0) although using serial propagation will cause a degradation in propagation performance. Oracle Support Services recommends the use of parallel propagation with the above workaround. 7.3. Schema Owner (referred to here as REPDBA) -------- ----- ------ ------------- This user is usually responsible for the day to day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN / REPPROP, although a separate schema is recommended for security reasons. CONNECT system/<password> CREATE USER repdba IDENTIFIED BY <password>; ALTER USER repdba DEFAULT TABLESPACE <tablespace name>; ALTER USER repdba TEMPORARY TABLESPACE <tablespace name>; GRANT connect, resource TO repdba; 7.4. End Users ----- ----- ---- These users must be able to access the objects in the replicated schema, but have no administration privileges. 8. Database Links ================= Before the database links are setup it is important that all replicated databases have unique global names. The default global name is the same name as the database name, which can only be changed by recreating the controlfile. The global name can be changed by : ALTER DATABASE RENAME GLOBAL_NAME TO <new global name>; SELECT * FROM GLOBAL_NAME; During the configuration of Symmetric Replication it is essential that the correct database links are setup for the users involved. These links must be created between each and every site involved in multimaster replication. Make sure each section (8.1 through 8.6) that follows is checked in turn for each site involved in the replication system : 8.1. Public Database Links ----- ----- ---------------- A public database link is required to define the Net8 connection alias to be used to connect to the remote database, this alias will be used by all links of the same name subsequently defined : CONNECT system/<password> CREATE PUBLIC DATABASE LINK <remote databases global name.world> USING 'Net8 alias'; The Net8 alias must match the tnsnames entry ON THE SERVER where the database is running to the remote database. 8.2. Replication Administrator (referred to here as REPADMIN) -------- ----- ------ ----- ----- --------- ----- ---- A private database link is required between replication administrators to allow replicated administration requests to be passed between master sites : CONNECT repadmin/<password> CREATE DATABASE LINK <remote databases global name.world> CONNECT TO repadmin IDENTIFIED BY <password>; 8.3. Replication propagator / receiver links -------- ----- ------ ----------- The propagation model selected in section 7 determines which of the following links are required, complete the steps described in section 8.3.a or 8.3.b : 8.3.a Single User for Administrator / Propagator / Receiver No additional database links are required for data propagation. 8.3.b Separate Propagator / Receiver from Replication Administrator TRUSTED security model : ----- ----- -------------- If the propagator and receiver are not the Replication Administrator and the TRUSTED security model is in operation, a private database link must be created from the local propagator to the remote receiver : CONNECT repprop/<password> CREATE DATABASE LINK <remote databases global name.world> CONNECT TO reprecv IDENTIFIED BY <password>; UNTRUSTED security model : ----- ----- ---------------- If the propagator and receiver are not the Replication Administrator and the UNTRUSTED security model is in operation, a private database link must be created from the local propagator to the remote receiver : CONNECT repprop/<password> CREATE DATABASE LINK <remote databases global name.world> CONNECT TO reprecv IDENTIFIED BY <password>; At each master site that a group exists and for each master group, register the group with a receiver, this can only be completed after the group has been created so customers may wish to perform this step after all groups have been defined at all sites in replication manager : CONNECT system/<password> BEGIN dbms_repcat_admin.register_user_repgroup( username => 'reprecv', privilege_type => 'receiver', list_of_gnames => '<list of master groups>'); END; 8.4. Schema Owner (referred to here as REPDBA) -------- ----- ------ ------------- No database links are required for Schema Owners who have tables involved in replication. 8.5. End Users ----- ----- ---- No database links are required for End Users. 8.6 Testing Database Links ----- ----- ---------------- Before proceeding all the links should be tested manually using : CONNECT user/<password> SELECT * FROM DUAL@<database link name> 9. Using Replication Manager ============================ If you are sure that the steps described in section 1 to 8 have been completed successfully then you can now start using replication manager to setup replication groups and objects. Always perform administration of multimaster replication by connecting to the master definition site as REPADMIN. To setup the deferred queue to be pushed automatically run the following at each master site : CONNECT repadmin/<password> BEGIN dbms_defer_sys.schedule_push( destination => '<destination databases global name>.WORLD', interval => '/*10:Mins*/ sysdate + 10/(60*24)', next_date => sysdate, stop_on_error => FALSE, delay_seconds => 0, parallelism => 1); END; To setup the deferred queue to be purged automatically run the following at each master site : CONNECT repadmin/<password> BEGIN dbms_defer_sys.schedule_purge( next_date => sysdate, interval => '/*1:Hr*/ sysdate + 1/24', delay_seconds => 0, rollback_segment => ''); END; Alternatively use Replication Manager : - Scheduling --> Scheduled Links - Database Information --> Purge Job Tab 10. Related Documents ===================== [NOTE:117437.1] Scripts to create Trusted / Untrusted Multi Master Replication v8.1 [NOTE:120093.1] Initial steps required to a create Snapshot Replication environment v8.1 [NOTE:120094.1] Scripts to create Trusted / Untrusted Updateable Snapshot Replication v8.1 [NOTE:50593.1] Initial steps required to create Multi Master and Snapshot Replication v8.0 [NOTE:62398.1] Creating a Replication Environment in Oracle 8.0.x [NOTE:50576.1] Initial steps required to create Snapshot and Multi Master Replication v7.3 __________ ______ ____ __________ ______ ____ ________________ Oracle Support Services . |
|||||
|
|
|||||
|
|
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use. |
|
|||
|
