Release 1 (9.0.1)
Part Number A87502-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i
Replication Management API Reference Release 1 (9.0.1) Part Number A87502-01 |
|
As your data delivery needs change due to growth, shrinkage, or emergencies, you are undoubtedly going to need to change the configuration of your replication environment. This chapter discusses managing the master sites of your replication environment. Specifically, this section describes altering and reconfiguring your master sites.
This chapter contains these topics:
Many replication administrative tasks can be performed only from the master definition site. Use the
RELOCATE_MASTERDEF procedure in the DBMS_REPCAT package to move the master
definition site to another master site. This API is especially useful when the master definition site
becomes unavailable and you need to specify a new master definition site (see "Option 2: The Old Master Definition Site Is Not Available").
Perform the actions in this section to change the master definition site if all master sites are available. Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Any Master Site
Replication Status: Running Normally (Not Quiesced)
Complete the following steps:
CONNECT repadmin/repadmin@orc1.world
BEGIN DBMS_REPCAT.RELOCATE_MASTERDEF ( gname => 'hr_repg', old_masterdef => 'orc1.world', new_masterdef => 'orc2.world', notify_masters => TRUE, include_old_masterdef => TRUE); END; /
Perform the actions in this section to change the master definition site if the old master definition site is not available. Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Any Master Site
Replication Status: Normal
Complete the following steps:
CONNECT repadmin/repadmin@orc3.world
BEGIN DBMS_REPCAT.RELOCATE_MASTERDEF ( gname => 'hr_repg', old_masterdef => 'orc1.world', new_masterdef => 'orc2.world', notify_masters => TRUE, include_old_masterdef => FALSE); END; /
As your replication environment expands, you may need to add new master sites to a master group. You can either add new master sites to a master group that is running normally or to a master group that is quiesced. If the master group is not quiesced, then users can perform data manipulation language (DML) operations on the data while the new master sites are being added. However, more administrative actions are required when adding new master sites if the master group is not quiesced.
Follow the instructions in the appropriate section to add new master sites to a master group:
This section contains procedures for adding new master sites to an existing master group that is not quiesced. These new sites may or may not already be replication sites (master sites and/or materialized view sites) in other replication groups.
You can use one of the following methods when you are adding a new master site without quiescing the master group:
Use full database export/import and change-based recovery to add all of the replication groups at the master definition site to the new master sites. When you use this method, the following conditions apply:
If your environment does not meet all of these conditions, then you must use object-level export/import to add the new master sites. Figure 7-1 summarizes these conditions.
Use object-level export/import to add a master group to master sites that already have other replication groups or to add a master group to master sites that do not currently have any replication groups. This method can add one or more master groups to new master sites at a time, and you can choose a subset of the master groups at the master definition site to add to the new master sites during the operation.
If you use object-level export/import and there are integrity constraints that span more than one master
group, then you must temporarily disable these integrity constraints on the table being added to a new
master site, if the other tables to which these constraints refer already exist at the new master site.
Initially, there are two rows in the DEFSCHEDULE data dictionary view that refer to the new
master sites. When propagation is caught up, there is one row in this view, and when propagation from all
the master sites to the new master site is caught up, you can re-enable the integrity constraints you
disabled.
Again, the two methods for adding new master sites without quiescing the master groups are the following:
When you use either method, propagation of deferred transactions to the new master site is partially or completely disabled while the new master sites are being added. Therefore, make sure each existing master site has enough free space to store the largest unpropagated deferred transaction queue that you may encounter.
In addition, the following restrictions apply to both methods:
DBA_NEW_REPSITES data dictionary view at the master definition site, then
the process is started and is not yet complete for that master group.
DBA_NEW_REPSITES data dictionary view, then the process is started and is not yet complete for
that master group.
hq1.world is the master definition site for mgroup1
and hq2.world is the master definition site for mgroup2, then you cannot add
hq1.world to mgroup2 and hq2.world to mgroup1 at the
same time.
COMPATIBLE initialization parameter. If
any master sites are lower than 9.0.0 compatibility level, then the master group must be quiesced to extend
it with new master sites. In this case, follow the instructions in "Adding New
Master Sites to a Quiesced Master Group".
Also, before adding new master sites with either method, make sure you properly set up your new master sites for multimaster replication.
|
See Also:
|
Figure 7-2 shows the major steps for using full database
export/import or change-based recovery to add new master sites to a master group without quiescing. The
following example script adds the new master sites orc4.world and orc5.world to
the hr_repg and oe_repg master groups. In this example, orc4.world
is added using full database export/import and orc5.world is added using change-based
recovery.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At:
Replication Status: Running Normally (Not Quiesced)
Complete the following steps to use full database export/import or change-based recovery to add sites to a master group.
/************************* BEGINNING OF SCRIPT ******************************
This step is not required if you are using change-based recovery.
*/ PAUSE Press <RETURN> when the databases for the new master sites are created. /*
*/ CONNECT repadmin/repadmin@orc1.world /*
Before you begin, create the required scheduled links between existing master sites and each new master site if they do not already exist.
|
See Also:
|
*/ BEGIN DBMS_REPCAT.SPECIFY_NEW_MASTERS ( gname => 'hr_repg', master_list => 'orc4.world,orc5.world'); END; / BEGIN DBMS_REPCAT.SPECIFY_NEW_MASTERS ( gname => 'oe_repg', master_list => 'orc4.world,orc5.world'); END; / /*
You can begin to track the extension process by querying the following data dictionary views in another SQL*Plus session:
Before running the following procedure, ensure that there are an adequate number of background jobs running at each new master site. If you are using full database export/import, then make sure there is enough space in your rollback segments or undo tablespace for the export before you run this procedure.
|
See Also:
|
*/ VARIABLE masterdef_flashback_scn NUMBER; VARIABLE extension_id VARCHAR2(32); BEGIN DBMS_REPCAT.ADD_NEW_MASTERS ( export_required => true, available_master_list => NULL, masterdef_flashback_scn => :masterdef_flashback_scn, extension_id => :extension_id, break_trans_to_masterdef => false, break_trans_to_new_masters => false, percentage_for_catchup_mdef => 80, cycle_seconds_mdef => 60, percentage_for_catchup_new => 80, cycle_seconds_new => 60); END; / /*
The values for masterdef_flashback_scn and extension_id are saved into variables
to be used later in the process. To see these values, you can query the DBA_REPSITES_NEW and
DBA_REPEXTENSIONS data dictionary views.
If you need to undo the changes made to a particular master site by the SPECIFY_NEW_MASTERS
and ADD_NEW_MASTERS procedures, then use the UNDO_ADD_NEW_MASTERS_REQUEST
procedure.
For the export_required parameter, true is specified because
orc4.world is being added using full database export/import. Although orc5.world
is using change-based recovery, the true setting is correct because at least one new master
site is added using export/import.
After successfully executing this procedure, monitor its progress by querying the
DBA_REPCATLOG data dictionary view in another SQL*Plus session. Do not proceed to Step 5 until there is no remaining information in this view about adding the new
master sites. Assuming no extraneous information exists in DBA_REPCATLOG from other
operations, you can enter the following statement:
SELECT COUNT(*) FROM DBA_REPCATLOG;
All of the processing is complete when this statement returns zero (0).
*/ PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty. /*
masterdef_flashback_scn parameter
in Step 4 for the FLASHBACK_SCN export parameter.
You can query the DBA_REPEXTENSIONS data dictionary view for the FLASHBACK_SCN
value:
SELECT FLASHBACK_SCN FROM DBA_REPEXTENSIONS;
In this example, orc4.world is using full database export/import. Therefore, perform the full
database export of the master definition database so that it can be imported into orc4.world during a later
step. However, the orc5.world database is using change-based recovery. Therefore, the export
would not be required if you were adding only orc5.world.
The following is an example of an export statement:
exp system/manager FILE=fulldb_orc1.dmp FULL=y DIRECT=n GRANTS=y ROWS=y COMPRESS=y STATISTICS=compute LOG=exp_orc1.log FLASHBACK_SCN=124723
Consider the following when you run the Export utility:
DBA role or the
EXP_FULL_DATABASE role can export in full database mode.
UNDO_RETENTION initialization
parameter is set correctly before performing the export.
CONSISTENT export parameter.
See Also:
*/
PAUSE Press <RETURN> to continue when the export is complete. /*
Running the following procedure indicates that export is effectively finished and propagation can be enabled for both extended and unaffected master groups at the master sites.
*/ BEGIN DBMS_REPCAT.RESUME_PROPAGATION_TO_MDEF ( extension_id => :extension_id); END; / /*
You can find the extension_id by querying the DBA_REPSITES_NEW data dictionary
view.
Using FTP or some other method, transfer the export dump file to the other new master sites that are being added with full database export/import. You will need this export dump file at each new site to perform the import described in the next step.
*/ PAUSE Press <RETURN> to continue after transferring the dump file. /*
JOB_QUEUE_PROCESSES initialization
parameter to zero for each new master site.
*/ PAUSE Press <RETURN> to continue after JOB_QUEUE_PROCESSES is set to zero at each new master site. /*
The following is an example of an import statement:
imp system/manager FILE=fulldb_orc1.dmp FULL=y BUFFER=30720 IGNORE=y GRANTS=y ROWS=y DESTROY=y COMMIT=y LOG=import.log
Only users with the DBA role or the IMP_FULL_DATABASE role can import in full database mode.
*/ PAUSE Press <RETURN> to continue when the import is complete. /*
masterdef_flashback_scn parameter in Step 4. You can query
the DBA_REPEXTENSIONS data dictionary view for the masterdef_flashback_scn value.
You can perform a change-based recovery in one of the following ways:
RECOVER command. See
the Oracle9i User-Managed Backup and Recovery Guide
for instructions.
DUPLICATE
command. See the Oracle9i Recovery Manager User's
Guide for instructions.
Connect to the site where you will perform the change-based recovery:
*/ CONNECT repadmin/repadmin@orc5.world PAUSE Press <RETURN> to continue when the change-based recovery is complete. You can use a separate terminal window to perform the change-based recovery. /*
hr and oe.
SPECIFY_NEW_MASTERS
procedure that you ran in Step 3. You can query the DBLINK
column in the DBA_REPSITES_NEW data dictionary view to see the global name for each new master
site.
You can set the global name using the ALTER DATABASE statement, as in the
following example:
ALTER DATABASE RENAME GLOBAL_NAME TO ORC4.WORLD;
*/ PAUSE Press <RETURN> when you have completed the these steps. /*
The following procedure enables the propagation of deferred transactions from other prepared new master sites and existing master sites to the invocation master site. This procedure also enables the propagation of deferred transactions from the invocation master site to the other new master sites and existing master sites.
*/ CONNECT repadmin/repadmin@orc4.world BEGIN DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER ( extension_id => :extension_id); END; / CONNECT repadmin/repadmin@orc5.world BEGIN DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER ( extension_id => :extension_id); END; / /*
/************************* END OF SCRIPT **********************************/
Figure 7-3 shows the major steps for using object-level export/import
to add new master sites to a master group without quiescing. The following example procedure adds the new
master sites orc4.world and orc5.world to the hr_repg and
oe_repg master groups. An object-level export/import involves exporting and importing the
tables in a master group. When you export and import the tables, other dependent database objects, such as
indexes, are exported and imported as well.
If you have an integrity constraint that spans two master groups, then you have a child table in one master group (the child master group) and a parent table in a different master group (the parent master group). In this case, Oracle Corporation recommends that you add new master sites to both master groups at the same time. However, if you cannot do this, then you must quiesce the child master group before adding new master sites to it. Here, the child table includes a foreign key, which makes it dependent on the values in the parent table. If you do not quiesce the child master group, then conflicts may result when you add master sites to it. You can still add master sites to the parent master group without quiescing it.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At:
Replication Status: Running Normally (Not Quiesced)
Also, make sure the replicated schemas exist at the new master sites before you begin. In this example, the
replicated schemas are hr and oe.
Complete the following steps to use object-level export/import to add sites to a master group.
/************************* BEGINNING OF SCRIPT ******************************
*/ CONNECT repadmin/repadmin@orc1.world /*
*/ PAUSE Press <RETURN> to continue the required scheduled links have been created. /*
|
See Also:
|
*/ BEGIN DBMS_REPCAT.SPECIFY_NEW_MASTERS ( gname => 'hr_repg', master_list => 'orc4.world,orc5.world'); END; / BEGIN DBMS_REPCAT.SPECIFY_NEW_MASTERS ( gname => 'oe_repg', master_list => 'orc4.world,orc5.world'); END; / /*
You can begin to track the extension process by querying the following data dictionary views in another SQL*Plus session:
Before running the following procedure, ensure that there are an adequate number of background jobs running at each new master site. Also, make sure there is enough space in your rollback segments or undo tablespace for the export before you run this procedure.
|
See Also:
|
*/ VARIABLE masterdef_flashback_scn NUMBER; VARIABLE extension_id VARCHAR2(32); BEGIN DBMS_REPCAT.ADD_NEW_MASTERS ( export_required => true, available_master_list => 'orc4.world,orc5.world', masterdef_flashback_scn => :masterdef_flashback_scn, extension_id => :extension_id, break_trans_to_masterdef => false, break_trans_to_new_masters => false, percentage_for_catchup_mdef => 80, cycle_seconds_mdef => 60, percentage_for_catchup_new => 80, cycle_seconds_new => 60); END; / /*
The sites specified for the available_master_list parameter must be same as the sites
specified in the SPECIFY_NEW_MASTERS procedure in Step 3.
The values for masterdef_flashback_scn and extension_id are saved into variables
to be used later in the process. To see these values, you can also query the DBA_REPSITES_NEW
and DBA_REPEXTENSIONS data dictionary views.
If you need to undo the changes made to a particular master site by the SPECIFY_NEW_MASTERS
and ADD_NEW_MASTERS procedures, then use the UNDO_ADD_NEW_MASTERS_REQUEST
procedure.
After successfully executing this procedure, monitor its progress by querying the
DBA_REPCATLOG data dictionary view in another SQL*Plus session. Do not proceed to Step 5 until there is no remaining information in this view about adding the new
master sites. Assuming there is no extraneous information in DBA_REPCATLOG from other
operations, you can enter the following statement:
SELECT COUNT(*) FROM DBA_REPCATLOG;
All of the processing is complete when this statement returns zero (0).
*/ PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty. /*
At the master definition database, perform an object-level export for each master table in the master groups that will be created at the new master sites. An object-level export includes exports performed in table mode, user mode, or tablespace mode.
Use the system change number (SCN) returned by the masterdef_flashback_scn parameter in Step
4 for the FLASHBACK_SCN export parameter. You can query the
DBA_REPEXTENSIONS data dictionary view for the FLASHBACK_SCN value:
SELECT FLASHBACK_SCN FROM DBA_REPEXTENSIONS;
The following is an example of an object-level export that exports the entire hr schema in
user mode:
exp system/manager FILE=hr_schema.dmp OWNER=hr DIRECT=n GRANTS=y ROWS=y COMPRESS=y INDEXES=y CONSTRAINTS=y STATISTICS=compute FLASHBACK_SCN=3456871
The following is an example of an object-level export that exports the tables in the oe schema
in table mode:
exp system/manager FILE=oe_tables.dmp TABLES=(oe.customers, inventories, orders, order_items, product_descriptions, product_information, warehouses) DIRECT=n GRANTS=y ROWS=y COMPRESS=y INDEXES=y CONSTRAINTS=y STATISTICS=compute FLASHBACK_SCN=3456871
When you export tables, their indexes are exported automatically.
Consider the following when you run the Export utility:
UNDO_RETENTION initialization
parameter is set correctly before performing the export.
CONSISTENT export parameter.
See Also:
UNDO_RETENTION initialization parameter
*/ PAUSE Press <RETURN> to continue when the export is complete. /*
Running the following procedure indicates that export is effectively finished and propagation can be enabled for both extended and unaffected master groups at the master sites.
*/ BEGIN DBMS_REPCAT.RESUME_PROPAGATION_TO_MDEF ( extension_id => :extension_id); END; / /*
You can find the extension_id by querying the DBA_REPSITES_NEW data dictionary
view.
Using FTP or some other method, transfer the export dump files to the other new master sites that are being added with object-level export/import. You will need these export dump files at each new site to perform the import described in the next step.
*/ PAUSE Press <RETURN> to continue when the export dump files have been transfered to the new master sites that are being added with object-level export/import. /*
The following is an example of an object-level import that imports the entire hr schema:
imp system/manager FILE=hr_schema.dmp FROMUSER=hr BUFFER=30720 IGNORE=y GRANTS=y ROWS=y DESTROY=y COMMIT=y
The following is an example of an object-level import that imports the tables in the oe
schema:
imp system/manager FILE=oe_tables.dmp FROMUSER=oe BUFFER=30720 TABLES=(oe.customers, inventories, orders, order_items, product_descriptions, product_information, warehouses) IGNORE=y GRANTS=y ROWS=y DESTROY=y COMMIT=y
Other objects, such as the indexes based on the tables, are imported automatically.
Connect to the site where you will perform the object-level imports and then perform the imports at each site:
*/ CONNECT repadmin/repadmin@orc4.world PAUSE Press <RETURN> to continue when the imports are complete at this site. You can use a separate terminal window to perform the object-level imports. CONNECT repadmin/repadmin@orc5.world PAUSE Press <RETURN> to continue when the imports are complete at this site. You can use a separate terminal window to perform the object-level imports. /*
The following procedure enables the propagation of deferred transactions from other prepared new master sites and existing master sites to the invocation master site. This procedure also enables the propagation of deferred transactions from the invocation master site to the other new master sites and existing master sites.
*/ CONNECT repadmin/repadmin@orc4.world BEGIN DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER ( extension_id => :extension_id); END; / CONNECT repadmin/repadmin@orc5.world BEGIN DBMS_REPCAT.PREPARE_INSTANTIATED_MASTER ( extension_id => :extension_id); END; / /*
/************************* END OF SCRIPT **********************************/
You can add new master sites to a quiesced master group in one of the following ways:
Typically, you should only use the ADD_MASTER_DATABASE procedure if you have a relatively
small master group or if you plan to precreate the replication tables and load the data into them at the
new master sites. If this is not the case, the ADD_MASTER_DATABASE procedure may not be a good
option because the entire master group is copied over the network. For larger master groups, either
precreate the objects in the master group at the new master sites or use offline instantiation.
You can use the ADD_MASTER_DATABASE procedure to add additional master sites to an existing
master group that is quiesced. Executing this procedure replicates existing master objects to the new site.
If any master site is lower than 9.0.0 compatibility level, then you must use the following procedure. That
is, the master group must be quiesced to extend it with new master sites. You control the compatibility
level of a database with the COMPATIBLE initialization parameter.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Master Definition Site
Replication Status: Quiesced
Complete the following steps to use the ADD_MASTER_DATABASE procedure to add sites to a master
group.
/************************* BEGINNING OF SCRIPT ******************************
Make sure the appropriate schema and database links have been created before adding your new master site. Be sure to create the database links from the new master site to each of the existing masters sites. Also, create a database link from each of the existing master sites to the new master site. After the database links have been created, make sure that you also define the scheduled links for each of the new database links.
*/ PAUSE Press <RETURN> to the new master site has been set up. /*
*/ CONNECT repadmin/repadmin@orc1.world /*
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
ADD_MASTER_DATABASE procedure
to add the new master sites.
This example assumes that the replicated objects do not exist at the new master site. Therefore, the
copy_rows parameter is set to true to copy the rows in the replicated objects at
the master definition site to the new master site, and the use_existing_objects parameter is
set to false so that Oracle Replication creates the replicated objects at the new site. If the
replicated objects already exist at the new site but do not contain any data, then set
use_existing_objects to true.
*/ BEGIN DBMS_REPCAT.ADD_MASTER_DATABASE ( gname => 'hr_repg', master => 'orc4.world', use_existing_objects => FALSE, copy_rows => TRUE, propagation_mode => 'ASYNCHRONOUS'); END; / /*
You should wait until the DBA_REPCATLOG view is empty. This view has temporary information
that is cleared after successful execution. Execute the following SELECT statement in another
SQL*Plus session to monitor the DBA_REPCATLOG view:
SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';
All of the processing is complete when this statement returns zero (0).
*/ PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty. /*
RESUME_MASTER_ACTIVITY procedure.
*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'hr_repg'); END; /
/************************* END OF SCRIPT **********************************/
Expanding established replication environments can cause network traffic when you add a new master site to
your replication environment using the ADD_MASTER_DATABASE procedure. This is caused by
propagating the entire contents of the table or materialized view through the network to the new replicated
site.
To minimize such network traffic, you can expand your replication environment by using the offline instantiation procedure. Offline instantiation takes advantage of Oracle's Export and Import utilities, which allow you to create an export file and transfer the data to the new site through another storage medium, such as CD-ROM, tape, and so on.
The following script is an example of how to perform an offline instantiation of a master site. This script can potentially eliminate large amounts of network traffic caused by the normal method of adding a new master site to an existing master group.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Master Definition Site and New Master Site
Replication Status: Quiesced and Partial
Complete the following steps to use offline instantiation to add sites to a master group.
/************************* BEGINNING OF SCRIPT ******************************
Make sure the appropriate schema and database links have been created before performing the offline instantiation of your new master site. Be sure to create the database links from the new master site to each of the existing masters sites. Also, create a database link from each of the existing master sites to the new master site. After the database links have been created, make sure that you also define the scheduled links for each of the new database links.
*/ PAUSE Press <RETURN> to the new master site has been set up. /*
*/ CONNECT repadmin/repadmin@orc1.world /*
You need to suspend master activity for the existing master sites before exporting your master data and beginning the offline instantiation process.
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
This includes pushing any outstanding deferred transactions, resolving any error transactions, and/or pushing any administrative transactions. This step must be performed at each of the existing master sites.
Check the error transaction queue.
SELECT * FROM DEFERROR;
If any deferred transactions have been entered into the error queue, then you need to resolve the error situation and then manually reexecute the deferred transaction. The following is an example:
BEGIN DBMS_DEFER_SYS.EXECUTE_ERROR ( deferred_tran_id => '128323', destination => 'orc1.world'); END; /
Check for outstanding administrative requests.
SELECT * FROM DBA_REPCATLOG;
If any administrative requests remain, then you can manually push these transactions or wait for them to be
executed automatically. You may need to execute the DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN
procedure several times, because some administrative operations have multiple steps. The following is an
example:
BEGIN DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN ( gname => 'hr_repg', all_sites => TRUE); END; / */ PAUSE Press <RETURN> to continue when you have verified that there are no pending requests. /*
*/ BEGIN DBMS_OFFLINE_OG.BEGIN_INSTANTIATION ( gname => 'hr_repg', new_site => 'orc4.world'); END; / /*
You should wait until the DBA_REPCATLOG view is empty. This view has temporary information
that is cleared after successful execution. Execute the following SELECT statement in another
SQL*Plus session to monitor the DBA_REPCATLOG view:
SELECT * FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG'; */ PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty. /*
hr/hr to export.
Use the Oracle Export utility to generate the export file that you will transfer to the new master site. The export file contains the replicated objects to be added at the new master site.
The following is an example of an export command for the hr schema:
exp hr/hr@orc1.world */ PAUSE Press <RETURN> to continue when the export is complete. /*
Because it may take some time to complete the offline instantiation process, you can resume replication
activity for the remaining master sites (excluding the new master site) by executing the
RESUME_SUBSET_OF_MASTERS procedure in the DBMS_OFFLINE_OG package after the
export is complete. In the example below, replication activity is resumed at all master sites except the
new master site -- orc4.world.
*/ BEGIN DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS ( gname => 'hr_repg', new_site => 'orc4.world'); END; / /*
*/ CONNECT repadmin/repadmin@orc4.world /*
After transferring the export file from the master definition site to the new master site, you must prepare the new site to import the data in your export file. Make sure you execute the following procedure at the new master site.
*/ BEGIN DBMS_OFFLINE_OG.BEGIN_LOAD ( gname => 'hr_repg', new_site => 'orc4.world'); END; / /*
After importing the export file that you generated earlier, you have transferred the data from your master definition site to your new master site.
The following is an example of an import command for the hr schema:
imp hr/hr@orc4.world FULL=y IGNORE=y */ PAUSE Press <RETURN> to continue when the import is complete. /*
After importing the export file, you are ready to complete the offline instantiation process at the new
master site. Executing the DBMS_OFFLINE_OG.END_LOAD procedure prepares the new site for normal
replication activity.
*/ BEGIN DBMS_OFFLINE_OG.END_LOAD ( gname => 'hr_repg', new_site => 'orc4.world'); END; / /*
*/ CONNECT repadmin/repadmin@orc1.world /*
After completing the steps at the new master site, you are ready to complete the offline instantiation
process. Executing the END_INSTANTIATION procedure in the DBMS_OFFLINE_OG package
completes the process and resumes normal replication activity at all master sites. Make sure you execute
the following procedure at the master definition site.
*/ BEGIN DBMS_OFFLINE_OG.END_INSTANTIATION ( gname => 'hr_repg', new_site => 'orc4.world'); END; /
/************************* END OF SCRIPT **********************************/
When it becomes necessary to remove a master site from a master group, use the
REMOVE_MASTER_DATABASES procedure to drop one or more master sites.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Master Definition Site
Replication Status: Quiesced
Complete the following steps to remove a master site.
/************************* BEGINNING OF SCRIPT ******************************
*/ CONNECT repadmin/repadmin@orc1.world /*
*/ BEGIN DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'hr_repg'); END; / /*
REMOVE_MASTER_DATABASES procedure.
*/ BEGIN DBMS_REPCAT.REMOVE_MASTER_DATABASES ( gname => 'hr_repg', master_list => 'orc4.world'); END; / /*
You should wait until the DBA_REPCATLOG view is empty. Execute the following
SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:
SELECT * FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG'; */ PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty for the master group. /*
*/ BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'hr_repg'); END; /
/************************* END OF SCRIPT **********************************/
The sites being removed from a master group do not have to be accessible. When a master site will not be available for an extended period of time due to a system or network failure, you might decide to drop the master site from the master group.
However, because the site is unavailable, you most likely cannot suspend replication activity for the
master group. You can use the REMOVE_MASTER_DATABASES procedure in the
DBMS_REPCAT package to remove master sites from a master group, even if the master group is
not quiesced.
If this is the case, you are responsible for:
Specifically, the next time that you suspend replication activity for a master group, you must complete the following steps as soon as possible after the unavailable master sites are removed:
DBMS_DEFER_SYS.DELETE_TRAN procedure at the master site.
Several procedures in the DBMS_REPCAT package enable you to update the comment information in
the various data dictionary views associated with replication. Table 7-1 lists the appropriate procedure to call for each view.
|
View |
|
See for Parameter Information |
|---|---|---|
DBA_REPGROUP |
COMMENT_ON_REPGROUP( |
|
DBA_REPOBJECT |
COMMENT_ON_REPOBJECT( |
|
DBA_REPSITES |
COMMENT_ON_REPSITES( |
|
DBA_REPCOLUMN_GROUP |
COMMENT_ON_COLUMN_GROUP( |
|
DBA_REPPRIORITY_GROUP |
COMMENT_ON_PRIORITY_GROUP( |
"COMMENT_ON_PRIORITY_GROUP/COMMENT_ON_SITE_PRIORITY Procedures". |
DBA_REPPRIORITY_GROUP |
COMMENT_ON_SITE_PRIORITY( |
"COMMENT_ON_PRIORITY_GROUP/COMMENT_ON_SITE_PRIORITY Procedures". |
DBA_REPRESOLUTION |
COMMENT_ON_UNIQUE_RESOLUTION( |
The parameters for the |
DBA_REPRESOLUTION |
COMMENT_ON_UPDATE_RESOLUTION( |
The parameters for the |
DBA_REPRESOLUTION |
COMMENT_ON_DELETE_RESOLUTION( |
The parameters for the |
Procedural replication can offer performance advantages for large batch-oriented operations operating on large numbers of rows that can be run serially within a replication environment.
A good example of an appropriate application is a purge operation, also referred to as an archive operation, that you run infrequently (for example, once in each quarter) during off hours to remove old data, or data that was "logically" deleted from the online database. An example using procedural replication to purge deleted rows is described in the "Avoiding Delete Conflicts" section in Chapter 5, "Conflict Resolution Concepts and Architecture", of Oracle9i Replication.
All parameters for a replicated procedure must be IN parameters; OUT and
IN/OUT modes are not supported. The following datatypes are supported for these parameters:
VARCHAR2
NVARCHAR2
NUMBER
DATE
RAW
ROWID
CHAR
NCHAR
BLOB)
CLOB)
NCLOB)
Oracle cannot detect update conflicts produced by replicated procedures. Replicated procedures must detect and resolve conflicts themselves. Because of the difficulties involved in writing your own conflict resolution routines, it is best to simply avoid the possibility of conflicts altogether.
Adhering to the following guidelines helps you ensure that your tables remain consistent at all sites when you plan to use procedural replication:
SYSDATE.
For example, if you have a procedure named sal_raise in master group A on master site
db1, then you cannot run the sal_raise procedure if master group B on master site
db1 is quiesced, even if master group A is replicating normally.
For example, suppose a replication environment includes two master sites named msite1 and
msite2 and two materialized view sites named mview1 and mview2. If
procedural replication is initiated at mview1, then the procedure is run at
mview1 and the procedure call is propagated to the two master sites, msite1 and
msite2, where the procedure is also run. However, the procedure call is not propagated to
mview2. Therefore, during the next refresh, mview2 pulls down all of the changes
made by the procedure at its master site.
When using procedural replication, the user-defined types and the objects referenced in the procedure must meet the following conditions:
CREATE TYPE cust_address_typ AS OBJECT (street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(10), country_id CHAR(2)); /
At all replication sites, street_address must be the first attribute, postal_code
must be the second attribute, city must be the third attribute, and so on.
You can meet these conditions by always using distributed schema management to create or modify any replicated object, including object types, tables with column objects, and object tables. If you do not use distributed schema management to create and modify object types, then replication errors may result.
Serial execution ensures that your data remains consistent. The replication facility propagates and executes replicated transactions one at a time. For example, assume that you have two procedures, A and B, that perform updates on local data. Now assume that you perform the following actions, in order:
The replicas of A and B on the other nodes are executed completely serially, in the same order that they were committed at the originating site. If A and B execute concurrently at the originating site, however, then they may produce different results locally than they do remotely. Executing A and B serially at the originating site ensures that all sites have identical results. Propagating the transaction serially ensures that A and B are executing in serial order at the target site in all cases.
Alternatively, you could write the procedures carefully, to ensure serialization. For example, you could
use SELECT... FOR UPDATE for queries to ensure serialization at the
originating site and at the target site if you are using parallel propagation.
You must disable row-level replication support at the start of your procedure, and then re-enable support at the end. This operation ensures that any updates that occur as a result of executing the procedure are not propagated to other sites. Row-level replication is enabled and disabled by calling the following procedures, respectively:
When you generate replication support for your replicated package, Oracle creates a wrapper package in the schema of the replication propagator.
The wrapper package has the same name as the original package, but its name is prefixed with the string you
supply when you generate replication support for the procedure. If you do not supply a prefix, then Oracle
uses the default prefix, defer_. The wrapper procedure has the same parameters as the
original, along with two additional parameters: call_local and call_remote. These
two CHAR parameters determine where the procedure is executed. When call_local is
'Y', the procedure is executed locally. When call_remote is 'Y', the
procedure will ultimately be executed at all other master sites in the replication environment.
The remote procedures are called directly if you are propagating changes synchronously, or calls to these
procedures are added to the deferred transaction queue if you are propagating changes asynchronously. By
default, call_local is 'N', and call_remote is 'Y'.
Oracle generates replication support for a package in two phases. The first phase creates the package specification at all sites. Phase two generates the package body at all sites. These two phases are necessary to support synchronous replication.
For example, suppose you create the package emp_mgmt containing the procedure
new_dept, which takes one argument, email. To replicate this package to all
master sites in your system, you can use the Replication Management tool to add the package to a master
group and then generate replication support for the object. After completing these steps, an application
can call procedure in the replicated package as follows:
BEGIN defer_emp_mgmt.new_dept( email => 'jones', call_local => 'Y', call_remote => 'Y'); END; /
As shown in Figure 7-4, the logic of the wrapper procedure ensures
that the procedure is called at the local site and subsequently at all remote sites. The logic of the
wrapper procedure also ensures that when the replicated procedure is called at the remote sites,
call_remote is false, ensuring that the procedure is not further propagated.
If you are operating in a mixed replication environment with static partitioning of data ownership (that is, if you are not preventing row-level replication), then Oracle Replication preserves the order of operations at the remote node, because both row-level and procedural replication use the same asynchronous queue.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|