Advanced Replication Management API Reference

Annotation Written by some of the world's top authors, this exciting eBook provides you with everything that you need to know to manage Oracle storage. Explained are how to monitor and reduce disk I/O, how to set up tablespaces for maximum efficiency, and how to define Oracle tables and indexes to make the most efficient use of disk storage. Not for the beginner, this is a guru-level advanced Oracle handbook with highly detailed explanations of Oracle storage internals. This eBook is laden with useful scripts that will allow you to quickly see inside your data files and tablespaces and tune them for optimal performance Contents......Page 3 Send Us Your Comments......Page 17 Preface......Page 19 Organization......Page 20 Related Documentation......Page 24 Conventions......Page 25 Documentation Accessibility......Page 28 Part I Configuring Your Replication Environment......Page 29 1 Overview of Replication......Page 31 Overview of Creating a Replication Environment......Page 32 Before You Start......Page 34 2 Create Replication Site......Page 35 Overview of Setting Up Replication Sites......Page 36 Setting Up orc1.world......Page 38 Setting Up orc2.world......Page 42 Setting Up orc3.world......Page 46 Creating Scheduled Links Between the Master Sites......Page 50 Setting Up Materialized View Sites......Page 54 Setting Up mv1.world......Page 55 Setting Up mv2.world......Page 61 3 Create a Master Group......Page 67 Overview of Creating a Master Group......Page 68 Before You Start......Page 69 Creating a Master Group......Page 71 4 Create a Deployment Template......Page 83 Oracle Deployment Templates Concepts......Page 84 Creating a Deployment Template......Page 85 Packaging a Deployment Template for Instantiation......Page 94 Packaging a Deployment Template for Offline Instantiation......Page 96 Packaging a Deployment Template for Online Instantiation......Page 97 Saving an Instantiation Script to File......Page 98 Distributing Instantiation Files......Page 100 Instantiating a Deployment Template......Page 101 Refreshing a Refresh Group After Instantiation......Page 103 5 Create Materialized View Group......Page 105 Overview of Creating a Materialized View Group......Page 106 Creating the Materialized View Group at mv1.world......Page 108 Creating the Materialized View Group at mv2.world......Page 116 6 Configure Conflict Resolution......Page 125 Preparing for Conflict Resolution......Page 126 Overwrite and Discard Conflict Resolution Methods......Page 127 Minimum and Maximum Conflict Resolution Methods......Page 130 Timestamp Conflict Resolution Methods......Page 133 Additive and Average Conflict Resolution Methods......Page 138 Priority Groups Conflict Resolution Methods......Page 141 Site Priority Conflict Resolution Methods......Page 145 Creating Conflict Resolution Methods for Uniqueness Conflicts......Page 151 Creating Conflict Avoidance Methods for Delete Conflicts......Page 157 Workflow......Page 162 Token Passing......Page 163 Obtaining Ownership......Page 166 Applying the Change......Page 167 Viewing Conflict Resolution Statistics......Page 168 Clearing Statistics Information......Page 169 Part II Managing and Monitoring Your Replication Environment......Page 171 7 Managing a Master Replication Environment......Page 173 Option 1: All Master Sites Are Available......Page 174 Option 2: The Old Master Definition Site Is Not Available......Page 175 Adding New Master Sites Without Quiescing the Master Group......Page 176 Using Full Database Export/Import or Change-Based Recovery......Page 181 Using Object-Level Export/Import......Page 193 Adding New Master Sites Using the ADD_MASTER_DATABASE Procedure......Page 206 Adding New Master Sites with Offline Instantiation Using Export/Import......Page 209 Removing a Master Site from a Master Group......Page 216 Removing an Unavailable Master Site......Page 219 Updating the Comments Fields in Data Dictionary Views......Page 220 Restrictions on Procedural Replication......Page 222 User-Defined Types and Procedural Replication......Page 224 Serializing Transactions......Page 225 Generating Support for Replicated Procedures......Page 226 8 Managing a Materialized View Replication Environment......Page 229 Refreshing Materialized Views......Page 230 Changing a Materialized View Group's Master Site......Page 231 Using the Public Version of DROP_SITE_INSTANTIATION......Page 232 Using the Private Version of DROP_SITE_INSTANTIATION......Page 235 Dropping a Materialized View Group Created Manually......Page 237 Dropping Objects at a Materialized View Site......Page 238 Cleaning Up After Dropping a Materialized View Group......Page 239 Cleaning Up Individual Materialized View Support......Page 243 Altering a Materialized View Log to Add Columns......Page 245 Managing Materialized View Log Space......Page 246 Truncating a Materialized View Log......Page 247 Truncating Masters......Page 249 Methods of Reorganizing a Database Table......Page 250 Method 2 for Reorganizing Table employees......Page 251 Method 4 for Reorganizing Table employees......Page 252 Dropping a Materialized View Log......Page 253 Performing an Offline Instantiation of a Materialized View Site Using Export/Import......Page 254 Using a Group Owner for a Materialized View Group......Page 267 9 Managing Replication Objects and Queues......Page 277 Altering a Replicated Object in a Quiesced Master Group......Page 278 Modifying Tables without Replicating the Modifications......Page 281 Disabling Replication......Page 282 Converting a LONG Column to a LOB Column in a Replicated Table......Page 283 Using the DIFFERENCES Procedure......Page 285 Using the RECTIFY Procedure......Page 286 Pushing the Deferred Transaction Queue......Page 290 Purging the Deferred Transaction Queue......Page 291 Using the AnyData Type to Determine the Value of an Argument in a Deferred Call......Page 292 Managing the Error Queue......Page 294 Reexecuting Error Transaction as the Receiver......Page 295 Reexecuting Error Transaction as Alternate User......Page 296 10 Monitoring a Replication Environment......Page 297 Listing General Information About a Master Site......Page 298 Listing the Master Sites Participating in a Master Group......Page 299 Listing General Information About Master Groups......Page 300 Listing Information About Materialized Views Based on a Master......Page 302 Listing Information About the Materialized View Logs at a Master......Page 303 Listing the Materialized Views That Use a Materialized View Log......Page 304 Listing Information About the Deployment Templates at a Master......Page 305 Listing General Information About a Materialized View Site......Page 307 Listing General Information About Materialized View Groups......Page 308 Listing the Properties of Materialized Views......Page 309 Listing Information About the Refresh Groups at a Materialized View Site......Page 311 Determining the Job ID for Each Refresh Job at a Materialized View Site......Page 312 Monitoring Administrative Requests......Page 313 Determining the Cause of Administrative Request Errors......Page 314 Listing General Information About the Job that Executes Administrative Requests......Page 315 Checking the Definition of Each do_deferred_repcat_admin Job......Page 316 Listing the Number of Deferred Transactions for Each Destination Master Site......Page 317 Listing General Information About the Push Jobs at a Replication Site......Page 318 Determining the Total Number of Transactions Queued for Propagation......Page 319 Listing General Information About the Purge Job......Page 320 Determining the Amount of Time Since the Last Purge......Page 321 Monitoring the Error Queue......Page 322 Listing General Information About the Error Transactions at a Replication Site......Page 323 Listing the Number of Error Transactions from Each Origin Master Site......Page 324 Determining the Error Operations at a Replication Site......Page 325 Tracking the Average Number of Row Changes in a Replication Transaction......Page 326 Determining the Average Network Traffic Created To Propagate a Transaction......Page 327 Determining the Percentage of Time the Parallel Propagation Job Spends Sleeping......Page 328 Monitoring Parallel Propagation of Deferred Transactions Using V$REPLPROP......Page 329 Determining the Transactions Currently Being Propagated to a Remote Master......Page 330 Part III Replication Management API Packages Reference......Page 333 11 Introduction to the Replication Management API Reference......Page 335 Examples of Using Oracle's Replication Management API......Page 336 The Replication Management Tool and the Replication Management API......Page 337 Abbreviations for Datetime and Interval Datatypes......Page 338 12 DBMS_DEFER......Page 339 Summary of DBMS_DEFER Subprograms......Page 340 CALL Procedure......Page 341 COMMIT_WORK Procedure......Page 342 datatype_ARG Procedure......Page 343 TRANSACTION Procedure......Page 346 13 DBMS_DEFER_QUERY......Page 347 Summary of DBMS_DEFER_QUERY Subprograms......Page 348 GET_ARG_FORM Function......Page 349 GET_ARG_TYPE Function......Page 350 GET_CALL_ARGS Procedure......Page 352 GET_datatype_ARG Function......Page 354 GET_OBJECT_NULL_VECTOR_ARG Function......Page 357 14 DBMS_DEFER_SYS......Page 359 Summary of DBMS_DEFER_SYS Subprograms......Page 360 CLEAR_PROP_STATISTICS Procedure......Page 362 DELETE_DEFAULT_DEST Procedure......Page 363 DELETE_ERROR Procedure......Page 364 DELETE_TRAN Procedure......Page 365 DISABLED Function......Page 366 EXCLUDE_PUSH Function......Page 367 EXECUTE_ERROR Procedure......Page 368 EXECUTE_ERROR_AS_USER Procedure......Page 369 PURGE Function......Page 370 PUSH Function......Page 372 REGISTER_PROPAGATOR Procedure......Page 375 SCHEDULE_PURGE Procedure......Page 376 SCHEDULE_PUSH Procedure......Page 378 SET_DISABLED Procedure......Page 380 UNREGISTER_PROPAGATOR Procedure......Page 382 UNSCHEDULE_PUSH Procedure......Page 383 15 DBMS_OFFLINE_OG......Page 385 Summary of DBMS_OFFLINE_OG Subprograms......Page 386 BEGIN_INSTANTIATION Procedure......Page 387 BEGIN_LOAD Procedure......Page 388 END_INSTANTIATION Procedure......Page 389 END_LOAD Procedure......Page 391 RESUME_SUBSET_OF_MASTERS Procedure......Page 392 16 DBMS_RECTIFIER_DIFF......Page 395 Summary of DBMS_RECTIFIER_DIFF Subprograms......Page 396 DIFFERENCES Procedure......Page 397 RECTIFY Procedure......Page 400 17 DBMS_REFRESH......Page 403 Summary of DBMS_REFRESH Subprograms......Page 404 ADD Procedure......Page 405 CHANGE Procedure......Page 406 MAKE Procedure......Page 408 REFRESH Procedure......Page 411 SUBTRACT Procedure......Page 412 18 DBMS_REPCAT......Page 413 Summary of DBMS_REPCAT Subprograms......Page 414 ADD_GROUPED_COLUMN Procedure......Page 420 ADD_MASTER_DATABASE Procedure......Page 421 ADD_NEW_MASTERS Procedure......Page 423 ADD_PRIORITY_datatype Procedure......Page 429 ADD_SITE_PRIORITY_SITE Procedure......Page 430 ADD_conflicttype_RESOLUTION Procedure......Page 431 ALTER_CATCHUP_PARAMETERS Procedure......Page 436 ALTER_MASTER_PROPAGATION Procedure......Page 438 ALTER_MASTER_REPOBJECT Procedure......Page 440 ALTER_MVIEW_PROPAGATION Procedure......Page 443 ALTER_PRIORITY Procedure......Page 445 ALTER_PRIORITY_datatype Procedure......Page 446 ALTER_SITE_PRIORITY Procedure......Page 447 ALTER_SITE_PRIORITY_SITE Procedure......Page 449 COMMENT_ON_COLUMN_GROUP Procedure......Page 450 COMMENT_ON_MVIEW_REPSITES Procedure......Page 451 COMMENT_ON_PRIORITY_GROUP Procedures......Page 452 COMMENT_ON_REPGROUP Procedure......Page 453 COMMENT_ON_REPOBJECT Procedure......Page 454 COMMENT_ON_REPSITES Procedure......Page 455 COMMENT_ON_SITE_PRIORITY Procedure......Page 457 COMMENT_ON_conflicttype_RESOLUTION Procedure......Page 458 COMPARE_OLD_VALUES Procedure......Page 460 CREATE_MASTER_REPGROUP Procedure......Page 462 CREATE_MASTER_REPOBJECT Procedure......Page 463 CREATE_MVIEW_REPGROUP Procedure......Page 467 CREATE_MVIEW_REPOBJECT Procedure......Page 468 DEFINE_COLUMN_GROUP Procedure......Page 471 DEFINE_PRIORITY_GROUP Procedure......Page 472 DEFINE_SITE_PRIORITY Procedure......Page 473 DO_DEFERRED_REPCAT_ADMIN Procedure......Page 474 DROP_COLUMN_GROUP Procedure......Page 475 DROP_GROUPED_COLUMN Procedure......Page 476 DROP_MASTER_REPGROUP Procedure......Page 478 DROP_MASTER_REPOBJECT Procedure......Page 479 DROP_MVIEW_REPGROUP Procedure......Page 481 DROP_MVIEW_REPOBJECT Procedure......Page 482 DROP_PRIORITY Procedure......Page 483 DROP_PRIORITY_GROUP Procedure......Page 484 DROP_PRIORITY_datatype Procedure......Page 485 DROP_SITE_PRIORITY Procedure......Page 486 DROP_SITE_PRIORITY_SITE Procedure......Page 487 DROP_conflicttype_RESOLUTION Procedure......Page 488 EXECUTE_DDL Procedure......Page 490 GENERATE_MVIEW_SUPPORT Procedure......Page 492 GENERATE_REPLICATION_SUPPORT Procedure......Page 493 MAKE_COLUMN_GROUP Procedure......Page 495 PREPARE_INSTANTIATED_MASTER Procedure......Page 497 PURGE_MASTER_LOG Procedure......Page 499 REFRESH_MVIEW_REPGROUP Procedure......Page 500 REGISTER_MVIEW_REPGROUP Procedure......Page 502 REGISTER_STATISTICS Procedure......Page 503 RELOCATE_MASTERDEF Procedure......Page 504 REMOVE_MASTER_DATABASES Procedure......Page 506 RENAME_SHADOW_COLUMN_GROUP Procedure......Page 507 REPCAT_IMPORT_CHECK Procedure......Page 508 RESUME_MASTER_ACTIVITY Procedure......Page 509 RESUME_PROPAGATION_TO_MDEF Procedure......Page 510 SEND_OLD_VALUES Procedure......Page 511 SET_COLUMNS Procedure......Page 514 SPECIFY_NEW_MASTERS Procedure......Page 516 SUSPEND_MASTER_ACTIVITY Procedure......Page 518 SWITCH_MVIEW_MASTER Procedure......Page 519 UNDO_ADD_NEW_MASTERS_REQUEST Procedure......Page 521 UNREGISTER_MVIEW_REPGROUP Procedure......Page 522 VALIDATE Function......Page 523 WAIT_MASTER_LOG Procedure......Page 526 19 DBMS_REPCAT_ADMIN......Page 527 Summary of DBMS_REPCAT_ADMIN Subprograms......Page 528 GRANT_ADMIN_SCHEMA Procedure......Page 529 REGISTER_USER_REPGROUP Procedure......Page 530 REVOKE_ADMIN_ANY_SCHEMA Procedure......Page 532 UNREGISTER_USER_REPGROUP Procedure......Page 533 20 DBMS_REPCAT_INSTANTIATE......Page 535 Summary of DBMS_REPCAT_INSTANTIATE Subprograms......Page 536 INSTANTIATE_OFFLINE Function......Page 537 INSTANTIATE_ONLINE Function......Page 540 21 DBMS_REPCAT_RGT......Page 543 Summary of DBMS_REPCAT_RGT Subprograms......Page 544 ALTER_REFRESH_TEMPLATE Procedure......Page 547 ALTER_TEMPLATE_OBJECT Procedure......Page 549 ALTER_TEMPLATE_PARM Procedure......Page 552 ALTER_USER_AUTHORIZATION Procedure......Page 553 ALTER_USER_PARM_VALUE Procedure......Page 555 COMPARE_TEMPLATES Function......Page 557 COPY_TEMPLATE Function......Page 559 CREATE_OBJECT_FROM_EXISTING Function......Page 561 CREATE_REFRESH_TEMPLATE Function......Page 563 CREATE_TEMPLATE_OBJECT Function......Page 565 CREATE_TEMPLATE_PARM Function......Page 568 CREATE_USER_AUTHORIZATION Function......Page 570 CREATE_USER_PARM_VALUE Function......Page 571 DELETE_RUNTIME_PARMS Procedure......Page 573 DROP_ALL_OBJECTS Procedure......Page 574 DROP_ALL_TEMPLATE_PARMS Procedure......Page 575 DROP_ALL_TEMPLATE_SITES Procedure......Page 576 DROP_ALL_USER_AUTHORIZATIONS Procedure......Page 577 DROP_ALL_USER_PARM_VALUES Procedure......Page 578 DROP_REFRESH_TEMPLATE Procedure......Page 579 DROP_SITE_INSTANTIATION Procedure......Page 580 DROP_TEMPLATE_OBJECT Procedure......Page 581 DROP_TEMPLATE_PARM Procedure......Page 582 DROP_USER_AUTHORIZATION Procedure......Page 583 DROP_USER_PARM_VALUE Procedure......Page 584 GET_RUNTIME_PARM_ID Function......Page 585 INSERT_RUNTIME_PARMS Procedure......Page 586 INSTANTIATE_OFFLINE Function......Page 588 INSTANTIATE_ONLINE Function......Page 590 LOCK_TEMPLATE_SHARED Procedure......Page 593 22 DBMS_REPUTIL......Page 595 Summary of DBMS_REPUTIL Subprograms......Page 596 REPLICATION_IS_ON Function......Page 597 GLOBAL_NAME Function......Page 598 MAKE_INTERNAL_PKG Procedure......Page 599 SYNC_UP_REP Procedure......Page 600 Part IV Replication Data Dictionary Reference......Page 601 23 Replication Catalog Views......Page 603 Summary of Replication Catalog Views......Page 604 Related Views:......Page 607 Related Views:......Page 608 Related Views:......Page 610 Related Views:......Page 612 Related Views:......Page 613 Related Views:......Page 614 Related Views:......Page 616 Related Views:......Page 618 Related Views:......Page 620 Related Views:......Page 621 Related Views:......Page 622 Related Views:......Page 623 Related Views:......Page 624 Related Views:......Page 625 Related Views:......Page 626 Related Views:......Page 627 Related Views:......Page 628 ALL_REPPRIORITY......Page 629 Related Views:......Page 630 ALL_REPPROP......Page 631 Related Views:......Page 632 Related Views:......Page 633 Related Views:......Page 634 Related Views:......Page 635 Related Views:......Page 636 DBA_REPCAT_TEMPLATE_PARMS......Page 637 DBA_REPCATLOG......Page 638 DBA_REPEXTENSIONS......Page 639 DBA_REPKEY_COLUMNS......Page 642 DBA_REPPROP......Page 643 DBA_REPRESOLUTION_STATISTICS......Page 644 DBA_REPSITES_NEW......Page 645 USER_REPCAT_REFRESH_TEMPLATES......Page 646 USER_REPCAT_USER_AUTHORIZATION......Page 647 USER_REPCOLUMN_GROUP......Page 648 USER_REPGROUPED_COLUMN......Page 649 USER_REPPRIORITY......Page 650 USER_REPRESOLUTION......Page 651 USER_REPSITES......Page 652 24 Replication Dynamic Performance Views......Page 653 V$REPLPROP......Page 654 V$REPLQUEUE......Page 656 25 Deferred Transaction Views......Page 657 DEFCALLDEST......Page 658 DEFERROR......Page 659 DEFSCHEDULE......Page 660 DEFTRAN......Page 663 DEFTRANDEST......Page 664 26 Materialized View and Refresh Group Views......Page 665 Part V Appendixes......Page 667 A Security Options......Page 669 Security Setup for Multimaster Replication......Page 670 Trusted Compared With Untrusted Security......Page 671 Security Setup for Materialized View Replication......Page 676 Trusted Compared With Untrusted Security......Page 677 B User-Defined Conflict Resolution Methods......Page 683 Conflict Resolution Method Parameters......Page 684 Resolving Uniqueness Conflicts......Page 685 Multitier Materialized Views and User-Defined Conflict Resolution Methods......Page 686 Column Subsetting Restrictions for User-Defined Conflict Resolution Methods......Page 687 Maximum User Function......Page 688 Additive User Function......Page 689 Creating a Conflict Notification Log......Page 690 Sample Conflict Notification Package......Page 691 Viewing Conflict Resolution Information......Page 694 Index......Page 695