Oracle Database 19c: Data Guard Administration Workshop

Introduction to Oracle Data Guard

• Objectives
• What Is Oracle Data Guard?
• Types of Standby Databases
• Types of Data Guard Services
• Role Transitions: Switchover and Failover
• Oracle Data Guard Broker Framework
• Choosing an Interface for Administering a Data Guard Configuration
• Oracle Data Guard: Architecture (Overview)
• Primary Database Processes
• Standby Database Processes
• Physical Standby Database: Redo Apply Architecture
• Logical Standby Database: SQL Apply Architecture
• Automatic Gap Detection and Resolution
• Data Protection Modes
• Data Guard Operational Requirements: Hardware and Operating System
• Data Guard Operational Requirements: Oracle Database Software
• Benefits of Implementing Oracle Data Guard
• Quiz
• Summary
• Practice 1: Overview

Oracle Net Services in a Data Guard Environment

• Objectives
• Oracle Net Services Overview
• Configuring Oracle Net Services in a Data Guard Environment
• Understanding Name Resolution
• Local Naming Configuration Files
• Connect-Time Failover: Planning for Role Reversal
• Listener Configuration: listener.ora
• Dynamic Service Registration
• Static Listener Entries: listener.ora
• Optimizing Oracle Net for Data Guard
• Quiz
• Summary
• Practice 2: Overview

Creating a Physical Standby Database by Using SQL and RMAN Commands

• Objectives
• Steps to Create a Physical Standby Database
• Preparing the Primary Database
• FORCE LOGGING Mode
• Database Nologging Enhancements
• Configuring Standby Redo Logs
• Standby Redo Log Usage
• Using SQL to Create Standby Redo Logs
• Viewing Standby Redo Log Information
• Setting Initialization Parameters on the Primary Database to Control Redo Transport
• Setting LOG_ARCHIVE_CONFIG
• Setting LOG_ARCHIVE_DEST_n
• Specifying Role-Based Destinations
• Combinations for VALID_FOR
• Defining the Redo Transport Mode
• Setting Initialization Parameters on the Primary Database
• Specifying Values for DB_FILE_NAME_CONVERT
• Specifying a Value for STANDBY_FILE_MANAGEMENT
• Specifying a Value for FAL_SERVER
• Example: Setting Initialization Parameters on the Primary Database
• Creating an Oracle Net Service Name on Primary for Your Physical Standby Database
• Creating a Listener Entry for Your Standby Database
• Copying Your Primary Database Password File to the Physical Standby Database Host
• Creating an Initialization Parameter File for the Physical Standby Database
• Creating Directories for the Physical Standby Database
• Starting the Physical Standby Database
• Creating an RMAN Script to Create the Physical Standby Database
• Creating the Physical Standby Database
• Real-Time Apply (Default)
• Starting Redo Apply in Real Time
• Preventing Primary Database Data Corruption from Affecting the Standby Database
• Detecting Lost Writes with DBMS_DBCOMP.DBCOMP
• DBMS_DBCOMP.DBCOMP Usage Cases
• Creating a Physical Standby Database by Using Enterprise Manager
• Using the Add Standby Database Wizard
• Creating a Data Guard Standby by Using DBCA
• Example: Physical Standby Creation
• Creating a Physical Standby by Using SQL
• Standby Database on the Same System
• Quiz
• Summary
• Practice 3: Overview

Managing Physical Standby Files After Structural Changes on the Primary Database

• Objectives
• Scenario 1: Adding a Data File or Creating a Tablespace
• Action Required on Physical Standby
• Scenario 2: Using Transportable Tablespaces with a Physical Standby Database
• Action Required on Physical Standby
• Scenario 3: Renaming a Data File in the Primary Database
• Action Required on Physical Standby
• Scenario 4: Adding or Dropping a Redo File Group
• Action Required on Physical Standby
• Scenario 5: NOLOGGING or Unrecoverable Operations
• Action Required on Physical Standby
• Scenario 6: Resetting the TDE Master Encryption Key
• Scenario 7: Refreshing the Password File
• Scenario 8: Controlling PDB Replication
• Example: ENABLED_PDBS_ON_STANDBY
• Scenario 9: Instantiating a PDB on a Standby
• Quiz
• Summary
• Practice 4: Overview

Using Oracle Active Data Guard: Supported Workloads in Read-Only Standby

• Objectives
• Oracle Active Data Guard
• Using Real-Time Query
• Enabling Real-Time Query
• Disabling Real-Time Query
• Checking the Standby’s Open Mode
• Understanding Lag in an Active Data Guard Configuration
• Monitoring Apply Lag: V$DATAGUARD_STATS
• Monitoring Apply Lag: V$STANDBY_EVENT_HISTOGRAM
• Allowed Staleness of Standby Query Data
• Configuring Zero Lag Between the Primary and Standby Databases
• Setting STANDBY_MAX_DATA_DELAY by Using an AFTER LOGON Trigger
• Example: Setting STANDBY_MAX_DATA_DELAY by Using an AFTER LOGON Trigger
• Forcing Redo Apply Synchronization
• Creating an AFTER LOGON Trigger for Synchronization
• DDL on Global Temporary Tables
• DML on Global Temporary Tables
• DML/DDL on Private Temporary Tables
• Creating a Private Temporary Table
• Support for Global Sequences
• Support for Session Sequences
• Benefits: Temporary Undo and Sequences
• DML Operations on Active Data Guard Standby Databases
• Configuring Automatic Redirection of DML operations
• Example: Performing DML on a Physical Standby
• PL/SQL Operations on Active Data Guard Standby Databases
• IM Column Store in an Active Data Guard Standby Database
• Quiz
• Summary
• Practice 5: Overview

Using Oracle Active Data Guard: Far Sync and Real-Time Cascading

• Objectives
• Oracle Active Data Guard
• Oracle Data Guard: Far Sync
• Far Sync: Redo Transport
• Far Sync: Alternate Redo Transport Routes
• Far Sync Instance Creation by Using RMAN
• Far Sync Instance Creation by Using SQL
• Benefits: Far Sync
• Far Sync: Alternate Design
• Real-Time Cascade
• Traditional Multi-Standby Database Architecture
• Benefits: Real-Time Cascade
• Quiz
• Summary
• Practice 6: Overview

Creating and Managing a Snapshot Standby Database

• Objectives
• Snapshot Standby Databases: Overview
• Snapshot Standby Database: Architecture
• Converting a Physical Standby Database to a Snapshot Standby Database
• Activating a Snapshot Standby Database: Issues and Cautions
• Snapshot Standby Database: Target Restrictions
• Viewing Snapshot Standby Database Information
• Snapshot Standby Space Requirements
• Activating a Snapshot Standby Database: Issues and Cautions
• Quiz
• Summary
• Practice 7: Overview

Creating a Logical Standby Database

• Objectives
• Benefits of Implementing a Logical Standby Database
• Logical Standby Database: SQL Apply Architecture
• SQL Apply Process: Architecture
• Preparing to Create a Logical Standby Database
• Unsupported Objects
• Unsupported Data Types
• Identifying Internal Schemas
• Checking for Unsupported Tables
• Checking for Tables with Unsupported Data Types
• SQL Commands That Do Not Execute on the Standby Database
• Unsupported PL/SQL-Supplied Packages
• Ensuring Unique Row Identifiers
• Adding a Disabled Primary Key RELY Constraint
• Creating a Logical Standby Database by Using SQL Commands
• Step 1: Create a Physical Standby Database
• Step 2: Stop Redo Apply on the Physical Standby Database
• Step 3: Prepare the Primary Database to Support Role Transitions
• Step 4: Build a LogMiner Dictionary in the Redo Data
• Step 5: Transition to a Logical Standby Database
• Step 6: Open the Logical Standby Database
• Step 7: Verify That the Logical Standby Database Is Performing Properly
• Creating a Logical Standby Database by Using Enterprise Manager
• Using the Add Standby Database Wizard
• Securing Your Logical Standby Database
• Automatic Deletion of Redo Log Files by SQL Apply
• Managing Remote Archived Log File Retention
• Creating SQL Apply Filtering Rules
• Deleting SQL Apply Filtering Rules
• Viewing SQL Apply Filtering Settings
• Using DBMS_SCHEDULER to Create Jobs on a Logical Standby Database
• Quiz
• Summary
• Practice 8: Overview

Oracle Data Guard Broker: Overview

• Objectives
• Oracle Data Guard Broker: Features
• Data Guard Broker: Components
• Data Guard Broker: Configurations
• Data Guard Broker: Management Model
• Data Guard Broker: Architecture
• Data Guard Monitor: DMON Process
• Benefits of Using the Data Guard Broker
• Comparing Configuration Management With and Without the Data Guard Broker
• Data Guard Broker Interfaces
• Broker Controlled Database Initialization Parameters
• Using the Command-Line Interface of the Data Guard Broker
• Using Oracle Enterprise Manager Cloud Control
• Data Guard Overview Page
• Benefits of Using Enterprise Manager
• Quiz
• Summary

Creating a Data Guard Broker Configuration

• Objectives
• Data Guard Broker: Requirements
• Data Guard Broker and the SPFILE
• Data Guard Monitor: Configuration File
• Data Guard Broker: Log Files
• Creating a Broker Configuration
• Clear Redo Transport Network Locations on Primary
• Connecting to the Primary Database with DGMGRL
• Defining the Broker Configuration and the Primary Database Profile
• Adding a Standby Database to the Configuration
• Adding a Far Sync to the Configuration
• Enabling the Configuration
• Broker Support for Complex Redo Routing
• Defining RedoRoutes by Using DGMGRL
• RedoRoutes Usage Guidelines
• How to Read Redo Routing Rules
• Far Sync Example with RedoRoutes
• Changing Database Properties and States
• Managing Redo Transport Services by Using DGMGRL
• Managing the Redo Transport Service by Using the LogXptMode Property
• Setting LogXptMode to ASYNC
• Setting LogXptMode to FASTSYNC
• New Commands to Set, Modify, and Display the Initialization Parameters:
• The EDIT DATABASE SET PARAMETER Command
• The EDIT DATABASE RESET PARAMETER Command
• The EDIT DATABASE FAR_SYNC RESET PARAMETER Command
• The EDIT RECOVERY_APPLIANCE SET PARAMETER Command
• The EDIT RECOVERY_APPLIANCE RESET PARAMETER Command
• The SET TRACE_LEVEL Command
• Example: SET TRACE_LEVEL command
• The EDIT FAR_SYNC SET PARAMETER Command
• Data Guard Broker Deprecated and Desupported Properties
• Export Broker Configuration
• Import Broker Configuration
• Disabling Broker Management of the Configuration or Standby Database
• Removing the Configuration or Standby Database
• Quiz
• Summary
• Practice 10: Overview

Monitoring a Data Guard Broker Configuration

• Objectives
• Monitoring the Data Guard Configuration by Using Enterprise Manager Cloud Control
• Viewing the Data Guard Configuration Status
• Monitoring Data Guard Performance
• Viewing Log File Details
• Enterprise Manager Metrics and Alerts
• Data Guard Metrics
• Managing Data Guard Metrics
• Viewing Metric Value History
• Viewing Data Guard Diagnostic Information
• Using Monitorable Database Properties to Identify a Failure
• Using the SHOW CONFIGURATION DGMGRL Command to Monitor the Configuration
• Using the SHOW DATABASE VERBOSE DGMGRL Command to Monitor the Configuration
• The VALIDATE DATABASE DATAFILE Command
• New Data Guard Broker Commands: VALIDATE DATABASE SPFILE
• New Data Guard Broker Commands: VALIDATE NETWORK CONFIGURATION
• New Data Guard Broker Commands: VALIDATE STATIC CONNECT IDENTIFIER
• Miscellaneous New Data Guard Broker Commands: SET ECHO and SHOW ALL
• Viewing Standby Redo Log Information in V$LOGFILE
• Viewing Standby Redo Log Information in V$STANDBY_LOG
• Viewing Standby Redo Log Information in V$LOGFILE
• Viewing Standby Redo Log Information in V$STANDBY_LOG
• Identifying Destination Settings
• Viewing Standby Redo Log Information in V$STANDBY_LOG
• Viewing Redo Transport Errors by Querying V$ARCHIVE_DEST
• Evaluating Redo Data by Querying V$DATAGUARD_STATS
• Viewing Data Guard Status Information by Querying V$DATAGUARD_STATUS
• Monitoring Redo Apply by Querying V$DATAGUARD_PROCESS
• Monitoring SQL Apply by Querying V$LOGSTDBY_TRANSACTION
• Quiz
• Summary
• Practice 11: Overview

Configuring Data Protection Modes

• Objectives
• Data Protection Modes and Redo Transport Modes
• Data Protection Modes
• Maximum Protection Mode
• Maximum Availability Mode
• Maximum Performance Mode
• Comparing Data Protection Modes
• Setting the Data Protection Mode by Using Enterprise Manager
• Setting the Data Protection Mode by Using SQL
• Quiz
• Summary
• Practice 12: Overview

Optimizing and Tuning a Data Guard Configuration

• Objectives
• Monitoring Configuration Performance by Using Enterprise Manager Cloud Control
• Optimizing Redo Transport Services
• Setting the ReopenSecs Database Property
• Setting the NetTimeout Database Property
• Setting the DataGuardSyncLatency Property
• Optimizing Redo Transmission by Using Redo Transport Compression
• Compressing Redo Data by Setting the RedoCompression Property
• Delaying the Application of Redo
• Setting the DelayMins Database Property to Delay the Application of Redo
• Using Enterprise Manager to Delay the Application of Redo
• Optimizing SQL Apply
• Adjusting the Number of APPLIER Processes
• Adjusting the Number of PREPARER Processes
• Tuning Automatic Outage Resolution
• Diagnostic Tools in Active Data Guard (Read-Only) Standby Databases
• AWR Unified Management Framework Topology
• Remote Snapshot Configuration for ADG Database
• Quiz
• Summary
• Practice 13: Overview

Performing Role Transitions

• Objectives
• Role Management Services
• Role Transitions: Switchover and Failover
• Buffer Cache Preservation During Role Change
• Keeping Physical Standby Sessions Connected During Role Transition
• Switchover
• Switchover: Before
• Switchover: After
• Performing a Switchover by Using Enterprise Manager
• Validating Databases for Switchover by Using DGMGRL
• Performing a Switchover by Using DGMGRL
• Preparing for a Switchover Using SQL
• Performing a Switchover by Using SQL
• Considerations When Performing a Switchover to a Logical Standby Database
• Situations That Prevent a Switchover
• Failover
• Types of Failovers
• Failover Considerations
• Performing a Failover by Using Enterprise Manager
• Performing a Failover to a Physical Standby Database
• Performing a Manual Failover by Using DGMGRL
• Re-enabling Disabled Databases by Using DGMGRL
• Quiz
• Summary
• Practice 14: Overview

Using Flashback Database in a Data Guard Configuration

• Objectives
• Using Flashback Database in a Data Guard Configuration
• Overview of Flashback Database
• Configuring Flashback Database
• Configuring Flashback Database by Using Enterprise Manager
• Using Flashback Database Instead of Apply Delay
• Using Flashback Database and Real-Time Apply
• Using Flashback Database After RESETLOGS
• Flashback Through Standby Database Role Transitions
• Using Flashback Database After Failover
• Automatic Propagation of Restore Points to All Standby Databases
• Automatic Flashback on Physical Standby Databases
• Quiz
• Summary
• Practice 15: Overview

Enabling Fast-Start Failover

• Objectives
• Fast-Start Failover: Overview
• When Does Fast-Start Failover Occur?
• Installing the Observer Software
• Fast-Start Failover Prerequisites
• Configuring Fast-Start Failover
• Step 1: Specify the Target Standby Databases
• Dynamically Change Fast-start Failover Target
• Setting a Fast Start Failover Target Using NOWAIT Mode
• Step 2: Set the Protection Mode
• Step 3: Set the Fast-Start Failover Threshold
• Step 4: (Optional) Set Additional Fast-Start Failover Properties
• Setting the Lag-Time Limit
• Configuring the Primary Database to Shut Down Automatically
• Automatic Reinstatement After Fast-Start Failover
• Configuring Automatic Reinstatement of the Primary Database
• Setting a Connect Identifier for the Observer
• Setting an Observer Override
• Setting Observer Reconnection Frequency
• Step 5: Configure Additional Fast-Start Failover Conditions
• Configuring Fast-Start Failover Conditions
• Step 6: Enable Fast-Start Failover
• Configuring Fast-Start Failover in Observe-only Mode
• Step 7: Start the Observer
• Data Guard Broker Property: ConfigurationWideServiceName
• Multiple Observers Using a Single Configuration
• Master Observer and Backup Observers
• Starting Observers as Background Processes
• Step 8: Verify the Configuration
• Initiating Fast-Start Failover from an Application
• Viewing Fast-Start Failover Information
• Determining the Reason for a Fast-Start Failover
• Prohibited Operations After Enabling Fast-Start Failover
• Disabling Fast-Start Failover
• Disabling Fast-Start Failover Conditions
• Using the FORCE Option
• Stopping the Observer
• Performing Manual Role Changes
• Manually Reinstating the Database
• Using Enterprise Manager to Enable Fast-Start Failover
• Changing the Protection Mode and Disabling Fast-Start Failover
• Using Enterprise Manager to Disable Fast-Start Failover
• Using Enterprise Manager to Suspend Fast-Start Failover
• Moving the Observer to a New Host
• Quiz
• Summary
• Practice 16: Overview

Backup and Recovery Considerations in an Oracle Data Guard Configuration

• Objectives
• Using RMAN to Back Up and Restore Files in a Data Guard Configuration
• Offloading Backups to a Physical Standby
• Restrictions and Usage Notes
• Association and Accessibility of RMAN Backups
• Backup and Recovery of a Logical Standby Database
• Using the RMAN Recovery Catalog in a Data Guard Configuration
• Creating the Recovery Catalog
• Registering a Database in the Recovery Catalog
• Setting Persistent Configuration Settings
• Setting RMAN Persistent Configuration Parameters on the Primary Database
• Setting RMAN Persistent Configuration Parameters on the Physical Standby Database
• Setting RMAN Persistent Configuration Parameters on the Other Standby Databases
• Enabling Block Change Tracking on a Physical Standby Database
• Configuring Daily Incremental Backups
• Recovering from the Loss of a Data File on the Primary Database
• Using a Backup to Recover a Data File on the Primary Database
• Restoring and Recovering a Data File on the Primary Database Over the Network
• Automated Standby Synchronization from Primary Database
• Enhancements to Block Media Recovery
• Executing the RECOVER BLOCK Command
• Excluding the Standby Database
• Quiz
• Summary
• Practice 17: Overview

Enhanced Client Connectivity in a Data Guard Environment

• Objectives
• Connecting to the Appropriate Environment
• Understanding Client Connectivity in a Data Guard Configuration
• Preventing Clients from Connecting to the Wrong Database
• Managing Services
• Creating Services for the Data Guard Configuration Databases
• Connecting Clients to the Correct Database
• Creating the AFTER DB_ROLE_CHANGE Trigger for Role-Based Services
• Configuring Service Names in the tnsnames.ora File
• Configuring Role-Based Services Using Oracle Clusterware
• Adding Standby Databases to Oracle Restart Configuration
• Example: Configuring Role-Based Services
• Automatic Failover of Applications to a New Primary Database
• Client Failover: Components
• Data Guard Broker and Fast Application Notification (FAN)
• Automating Client Failover by Using Transaction Guard and Application Continuity
• What Is Transaction Guard?
• What Is Application Continuity?
• Data Guard and Application Continuity
• Using Application Continuity
• Configure Services on Single Instance Databases to Use Application Continuity
• Configuring Client: JDBC-Thin Driver
• Configuring Client: OCI Driver
• Configuring Client: ODP.NET Unmanaged Provider Driver
• Quiz
• Summary
• Practice 18: Overview

Patching and Upgrading Databases in a Data Guard Configuration

• Objectives
• Data Guard Standby-First Patch Apply
• Upgrading an Oracle Data Guard Broker Configuration
• Upgrading Oracle Database in a Data Guard Configuration with a Physical Standby Database
• Upgrading Oracle Database in a Data Guard Configuration with a Logical Standby Database
• Using SQL Apply to Upgrade the Oracle Database
• Requirements for Using SQL Apply to Perform a Rolling Upgrade
• Performing a Rolling Upgrade by Using SQL Apply
• Identifying Unsupported Data Types
• Case 1: Performing a Rolling Upgrade by Using an Existing Logical Standby Database
• Performing a Rolling Upgrade by Using an Existing Logical Standby Database
• Case 2: Performing a Rolling Upgrade by Creating a New Logical Standby Database
• Case 3: Performing a Rolling Upgrade by Using a Physical Standby Database
• Performing a Rolling Upgrade by Using a Physical Standby Database
• Rolling Upgrades Using DBMS_ROLLING and Active Data Guard
• DBMS_ROLLING: Concepts
• DBMS_ROLLING: Overview
• Database Rolling Upgrade: Specification and Compilation Stages
• Example: Specification Stage
• Example: Compilation Stage
• Database Rolling Upgrade: Execution Stage
• Rolling Upgrade Support for Multitenant Databases
• Quiz
• Summary