book

Oracle Distributed Systems

by Charles Dye

April 1999

Intermediate to advanced

552 pages

15h 39m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Audience for This Book
Part IPart IIPart III

Terminology and Concepts
TunabilityPlatform AutonomyFault ToleranceScalabilityLocation TransparencySite AutonomyEnhanced Security
The Single Database with Multiple SchemaDatabase Instances Devoted to a Single Application
Export/ImportDatabase LinksRead-Only SnapshotsUpdateable SnapshotsAdvanced ReplicationProcedural Replication
SecurityData ConsistencyTransaction ManagementMonitoringRecoveryPerformance
Protocol OverviewDistributed ProcessingNetwork Transparency and Network IndependenceMultiple Network Protocol InteroperabilityOracle Names
SQL*Net/Net8, TNS, and the OSI Reference ModelApplication layerPresentation layerSession layerTransport, network, data link, and physical layersSQL*Net/Net8 ElementsConnection ScenariosBequeathed and Redirected ConnectionsHow a bequeathed connection is established on UnixHow a redirected connection is establishedExample: Connecting to a Multi-Threaded ServerExample: Connecting to a Prespawned Server Process
Do You Have a Problem?Tuning the multi-threaded serverTuning multi-threaded server dispatchers in Oracle8Tuning multi-threaded server server processesMeasuring m ulti-threaded server server activityTuning Dedicated Processes and Prespawned ProcessesBreak Out the Sniffer
Multiple TNS Listeners and Multi-Threaded Server with a Single Database InstanceMultiple TNS Listeners and Multi-Threaded Server with Multiple Database InstancesMultiple TNS Listeners and Dedicated Processes
Connection PoolingSession MultiplexingScalability: Summary
Dead Connection DetectionTracing and LoggingDefault DomainsOracle Names ParametersAdditional Parameters
Configuring SNMP SupportUsing SNMP
Initialization ParametersCOMMIT_POINT_STRENGTHDB_DOMAINDBLINK_ENCRYPT_LOGIN (Oracle8)DISTRIBUTED_LOCK_TIMEOUTDISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIMEDISTRIBUTED_TRANSACTIONSGLOBAL_NAMESJOB_QUEUE_INTERVALJOB_QUEUE_PROCESSESMAX_TRANSACTION_BRANCHESOPEN_LINKSOPEN_LINKS_PER_INSTANCE (Oracle8)REMOTE_DEPENDENCIES_MODEREMOTE_LOGIN_PASSWORD_FILEREMOTE_OS_AUTHENTREMOTE_OS_ROLESREPLICATION_DEPENDENCY_TRACKING (Oracle8)SNAPSHOT_REFRESH_INTERVALSNAPSHOT_REFRESH_PROCESSES
Global Names and Database LinksPublic, Private, and Global Database LinksWhen to use public database linksWhen to use private database linksWhen to use global database linksCreating Database LinksPrerequisites for creating database linksThe PUBLIC qualifierThe SHARED qualifierThe connection qualifierThe CONNECT clauseThe CURRENT_USER qualifierThe USING clauseThe AUTHENTICATED clauseDropping Database LinksAccessing Data over a Database LinkHow Database Links Are ResolvedThe algorithmExample of database link resolutionListing Information About Database LinksData Relocation with Database LinksRestrictions on Distributed Operations over Database Links
Behind the Scenes of a Distributed TransactionTwo-phase commit: The participantsTwo-phase commit: ExplainedWhen Things Go WrongTypes of distributed transaction failuresForcing commits and rollbacks of distributed transactionsTesting recovery of failed distributed transactionsRestrictions on Distributed Transactions
Distributed RecoverySnapshotsBackup Strategy Considerations
Privilege ManagementThe Simplistic ApproachAdvantages of the simplistic approachDisadvantages of the simplistic approachThe Mirrored Account ApproachAdvantages of the mirrored account approachDisadvantages of the mirrored account approachThe Local View ApproachAdvantages of the local view approachDisadvantages of the local view approachThe Local Wrapper ApproachAdvantages of the local wrapper approachDisadvantages of the local wrapper approachConclusions on Privilege Management
Database AuthenticationWrite a wrapper command around sqlplusUse operating system authenticated (OPS$) accountsDon’t invoke programs with username and password on command lineOperating System AuthenticationCreating OPS$ accountsThe assumed risks of OPS$ accountsExternal Authentication
Characteristics of a Distributed SystemDistributed System ObjectivesLocal autonomyNo reliance on a single siteContinuous operationLocation transparencyFragmentation independenceReplication independenceDistributed query processingDistributed transaction managementHardware independenceOperating system independenceNetwork independenceRDBMS independenceDistributed System ClassificationsHomogeneous distributed systemsHeterogeneous distributed systemsFederated database systemsRedundant backup systemsDistributed system classifications: Summary
Placement of the Global Data DictionaryObject NamingThe Local Data DictionaryManagement of Interdatabase Integrity ConstraintsManagement of User Accounts and Privileges
Replication ArchitectureLog-based replicationAdvantagesDisadvantagesTransactional replicationAdvantagesDisadvantagesSoftware CompatibilityData ConsistencyData ExtractionPrimary KeysConflict Avoidance
Identify Users, Locations, and ActivitiesAssess Existing Network and Hardware InfrastructureIdentify Coordinated Recovery RequirementsMap Processes to DataAssess Global RequirementsPropose and Validate Data Locations
Enforcing Business Rules in the Database TierEnforcing Business Rules in the Presentation TierCreating a Third TierHow Many Tiers Are Right for You?
Meeting the 12 Objectives with OracleLocal AutonomyNo Reliance on a Single SiteContinuous OperationLocation TransparencyFragmentation IndependenceReplication IndependenceDistributed Query ProcessingDistributed Transaction ManagementHardware IndependenceOperating System IndependenceNetwork IndependenceRDBMS Independence.Conclusions
Global NamingData Dictionary Views and Location Transparency
The High-Availability SystemThe Hot Standby DatabaseAdvantages and disadvantages of the hot standby databaseOracle Parallel ServerAdvantages and disadvantages of Oracle parallel serverAdvanced ReplicationAdvantages and disadvantages of advanced replication
Schema Design and IntegrationInterdatabase Referential IntegrityNaming ConventionsDistributed Queries and TransactionsControl the driving siteControl index usageMaintenance of Database Link ConnectionsError Handling
Transactional ConsistencySchema Differences and PartitioningRow-Level Replication or Procedural Replication?Primary Keys and Unique IndexesForeign Keys and Referential IntegrityTriggers on Replicated TablesDatatypesTimeSequencesUsing a single sequence at a master siteAllocating sequence ranges to sitesUsing a multicolumn primary keyMultiple Character Sets
What Is Oracle Replication?
The QueuesThe Triggers and PackagesThe read-only snapshot mechanismThe updateable snapshot mechanismThe multi-master replication mechanismThe Background Processes
A pplication- and Trigger-Based ReplicationExport/ImportCOPY/CREATE TABLE AS SELECT
Initialization Parameters
Configuring Oracle7 for the Mirrored User Access ModelConfiguring Oracle7 for the Global Access ModelConfiguring Oracle8 for Advanced Replication
About Read-Only Snapshots
Initialization ParametersSystem PrivilegesRestrictions
The Snapshot STORAGE ClauseThe REFRESH ClauseThe Defining Query
Restrictions on Snapshot LogsCreation TipsSnapshot Logs for ROWID Snapshots
Restrictions on Subquery SubsetsSubquery Subset Snapshot Base TablesA Special Case
Creating and Destroying Refresh GroupsCreating a snapshot refresh group of read-only snapshotsCreating a snapshot refresh group of read-only snapshots with specialized parametersCreating a snapshot r efresh group that uses parallel propagation (Oracle8 only)Dropping a refresh group
Tuning SnapshotsAdministrative TasksReorganizing a Master Table in Oracle8Steps for reorganizing a master table using truncationSteps for reorganizing a master table by renamingOffline Instantiation of SnapshotsTroubleshootingSnapshots are not refreshingSnapshots refreshing continuouslySnapshot logs are growing uncontrollably
Concepts and TerminologyDeferred TransactionReplication GroupQuiescenceMaster Definition SiteMaster SiteReplication SupportConflictPropagation LatencyInstantiation
The Quick-and-Dirty SetupA Quick-and-Dirty Example
API CallsNaming ConventionsWhich Tables Belong in the Same Replication Group?How to Drop a Replication Group
API CallsAdding a Master SiteCreating the REGIONS tableCreating constraints and indexes on REGIONS tableCreating user-defined triggers on the REGIONS tableDropping a Master SiteRelocating a Master Definition Site
API CallsAbout the ParametersParallel PropagationManaging parallel propagationChecking parallel pushesParallel push errorsSynchronous versus asynchronous propagationScheduling multiple push intervals for the same database
Replication Catalog Data Dictionary ViewsPushing repcatlog EntriesMonitoring ProgressCorrecting Errors
API CallsColumn GroupsMinimum Communication and SEND_AND_COMPARE_OLD_VALUESTriggers on Replicated TablesA trigger to populate fields at the originating site onlyA trigger to populate a field from a sequence on inserts and fields used for conflict resolutionReplicating triggers themselvesUsing Offline InstantiationOffline instantiation caveatsAn alternative to DBMS_OFFLINE_OGAdding and Removing TablesAdding replicated tables.Removing replicated tablesDropping replicated tables: caveatsPartially dropped tables (Oracle8 only)
RestrictionsExamplesCreating an indexCompiling a replicated package bodyAltering a column in a replicated tableManually Executing Entries in the repcatlogDeleting Errors from the repcatlog
Monitoring Queued TransactionsMonitoring Deferred Transaction ErrorsMonitoring Snapshot LogsMonitoring: Summary
About Updateable SnapshotsRestrictions
Preliminary StepsPreparing the Master TablePreparing the Snapshot SiteUser-Defined Triggers on Updateable Snapshots
Real-Time (Synchronous) PropagationOnce-a-Day PropagationPropagation on Demand
Altering the Master TableDropping a Replicated Snapshot ObjectMaster table still existsRemastering a snapshot
When to Use Procedural Replication
cr_seq_audit_admin.sqlcr_audit_admin.sqlpl_useradmin.sqlrep_useradmin.sql
Data Integrity Versus Data Convergence
NormalizeDesignate a Governing Column for Column GroupsStandardize on a Time ZoneIdentify WorkflowConsider Token PassingPerform Strategic Administration
Limitations of Delete Conflict ResolutionDefining a delete conflict handlerLimitations of NULL Values in Conflict ResolutionReferential Integrity Violations and Conflict ResolutionConflicts Arising from Procedural Replication
Column GroupsHow the column group resolution mechanism worksAPIs for column groupsPriority GroupsSite PriorityAPIs for priority groups and site priority
Minimum Value/Maximum ValueEarliest Timestamp/Latest TimestampOverwrite Update/Discard UpdateAverage/AdditiveAppend Site Name/Append SequenceIgnore Insert/Discard Insert
DBMS_DEFER: Building Deferred CallsHow the Package Is UsedInstallation and AccessDBMS_DEFER ProceduresDBMS_DEFER ExceptionsDBMS_DEFER Nonprogram Elements
How the Package Is UsedInstallation and AccessDBMS_DEFER_QUERY ProceduresDBMS_DEFER_QUERY Nonprogram Elements
How the Package Is UsedInstallation and AccessDBMS_DEFER_SYS ProceduresDBMS_DEFER_SYS ExceptionsDBMS_DEFER_SYS Nonprogram Elements
How the Package Is UsedInstallation and AccessDBMS_OFFLINE_OG ProceduresDBMS_OFFLINE_OG ExceptionsDBMS_OFFLINE_OG Nonprogram Elements
How the Package Is UsedInstallation and AccessDBMS_OFFLINE_SNAPSHOT ProceduresDBMS_OFFLINE_SNAPSHOT Exceptions
How the Package Is UsedInstallation and AccessDBMS_RECTIFIER_DIFF ProceduresDBMS_RECTIFIER_DIFF Exceptions
How the Package Is UsedInstallation and AccessDBMS_REFRESH ProceduresDBMS_REFRESH Nonprogram Elements
How the Package Is UsedInstallation and AccessDBMS_REPCAT ProceduresDBMS_REPCAT ExceptionsDBMS_REPCAT Nonprogram ElementsDBMS_REPCAT.ADD_conflicttype_RESOLUTIONParametersExceptionsRestrictions
How the Package Is UsedInstallation and AccessDBMS_REPCAT_ADMIN ProceduresDBMS_REPCAT_ADMIN Exceptions
How the Package Is UsedInstallation and AccessDBMS_REPCAT_AUTH ProceduresDBMS_REPCAT_AUTH Exceptions
How the Package Is UsedInstallation and AccessDBMS_REPUTIL ProceduresDBMS_REPUTIL Nonprogram Elements
How the Package Is UsedInstallation and AccessDBMS_SNAPSHOT Procedures
busycirc.sql

Content preview from Oracle Distributed Systems

DBMS_REPCAT_ADMIN: Setting Up Administrative Accounts

The first step in creating an advanced replication environment is to create administrative and end user accounts. The DBMS_REPCAT_AUTH and DBMS_REPCAT_ADMIN packages contain programs that grant and revoke the privileges required in such an environment.

How the Package Is Used

The replication administration account or a DBA account uses the procedures in DBMS_REPCAT_ADMIN to grant or revoke the specified privileges.

Installation and Access

The DBMS_REPCAT_ADMIN package iscreated when the Oracle database is installed. The dbmsrepc.sqlscript (found in the built-in packages source directory) contains the source code for this package’s specification. This script is called by catrep.sql, which must be run to install the advanced replication packages. The wrapped SQL script prvtrepc.sqlcreates the public synonym DBMS_REPCAT_ADMIN. No EXECUTE privileges are granted on DBMS_REPCAT_ADMIN; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

DBMS_REPCAT_ADMIN Procedures

Note

Oracle8 documents only the REPGROUP procedures listed here, although the REPSCHEMA procedures also exist. The functionality is identical.

Procedure Name	Description
GRANT_ADMIN_ANY_REPGROUP (Oracle8)	Grants privileges required to administer any replication group at the current site
GRANT_ADMIN_ANY_REPSCHEMA	Grants privileges required to administer any replication schema at the current site
GRANT_ADMIN_REPGROUP (Oracle8)	Grants ...