Oracle10g data guard overview logical standby bill sutton saic suttonbi@saic com l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 76

Oracle10g Data Guard Overview / Logical Standby Bill Sutton SAIC [email protected] PowerPoint PPT Presentation


  • 205 Views
  • Uploaded on
  • Presentation posted in: General

Oracle10g Data Guard Overview / Logical Standby Bill Sutton SAIC [email protected] Oracle10g Data Guard Overview. Objectives. Explain the benefits of Data Guard and why we might use it Describe the basic components of Oracle Data Guard

Download Presentation

Oracle10g Data Guard Overview / Logical Standby Bill Sutton SAIC [email protected]

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle10g data guard overview logical standby bill sutton saic suttonbi@saic com l.jpg

Oracle10g Data GuardOverview / Logical StandbyBill [email protected]


Oracle10g data guard overview l.jpg

Oracle10g Data GuardOverview


Objectives l.jpg

Objectives

  • Explain the benefits of Data Guard and why we might use it

  • Describe the basic components of Oracle Data Guard

  • Understand the difference between Physical and Logical Standby databases

  • Gain detailed insight into Logical Standby databases:

    • Things-you-need-to-be-aware-of

    • Creation

    • Maintenance

    • Tuning


Benefits of oracle data guard l.jpg

Benefits of Oracle Data Guard

  • Configure the system to meet business protection and recovery requirements

  • Continuous service through a disaster or crippling data failure

  • Complete data protection against corruptions and data loss

  • Reduce overhead on primary systems by offloading:

    • adhoc queries

    • reporting

    • backups

  • Centralized management (Data Guard Broker, Data Guard Manager via Enterprise Manager)


Data loss causes l.jpg

44%

Human Error

32%

Computer Viruses

7%

Software Corruption

14%

Natural Disasters

3%

Data Loss Causes…

Hardware & System Error

* Protect Data 2006


What is data guard l.jpg

What is Data Guard?

Primary

Database

Standby

Database

Redo

Oracle Net

Database

Database Copy

Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2)“Ch. 1 Introduction to Oracle Data Guard “


Support compliment high availability l.jpg

Support/Compliment High Availability…

Failover

System

Failure

Failover

Data Failureand Disaster

UnplannedDowntime

Standby Lag/Failover

Human

Error

Switchover

SystemMaintenance

Concurrent Update of Standby

Data

Maintenance

PlannedDowntime

Backout Support (Failover)

Oracle

Upgrade


Standby databases l.jpg

Standby Databases

  • There are two types of standby databases:

    • Physical standby database

    • Logical standby database


Data guard services l.jpg

Data Guard Services

  • There are three types of services provided between the Primary and Standby databases:

    • Redo Transport: (2 types: Archival or Real-Time)

      • Controls the transfer of redo data from the primary database to the standby database.

    • Log Apply: (2 types: Redo Apply or SQL Apply)

      • Applies redo data on the standby database to maintain synchronization with the primary database.

    • Role Transitions: (2 types: Switchover or Failover)

      • Change the role of a database:

        SWITCHOVER: standby to primary + primary to standby

        FAILOVER: standby to primary


Data protection modes l.jpg

Data Protection Modes

  • There are three types of Data Protection Modes:

    • Maximum Protection

    • Maximum Availability (hybrid)

    • Maximum Performance

REMEMBER: 2323... There will be a test sooner or later…


2323 test l.jpg

2323 Test…

  • 2, Types of standby databases:

    • Physical and Logical

  • 3, Types of services:

    • Redo Transport

    • Log Apply

    • Role Transitions

  • 2, Types of features for each service:

    • Redo Transport (Archival, Real-Time)

    • Log Apply (Redo Apply, SQL Apply)

    • Role Transitions (Switchover, Failover)

  • 3, Types of Protection Modes

    • Maximum Protection

    • Maximum Availability

    • Maximum Performance


  • Physical standby database l.jpg

    Physical Standby Database

    Physical standby database:

    • Identical to the primary database on a block-for-block, byte-for-byte, bit-for-bit basis

    • Redo Transport : Archival or Real-Time

    • Log Apply : Redo Apply only

    • Role transition : Switchover or Failover

    • Maintained in a managed recovery mode (MOUNT)

    • Can be opened as READ ONLY

    • Can be opened as READ/WRITE

      * Must use FLASHBACK DATABASE to return to time prior to READ/WRITE in order to resume managed recovery mode OR restore from backup made prior to activation.


    Logical standby database l.jpg

    Logical Standby Database

    Logical standby database:

    • NOT physically identical to primary but instead data identical, or data subset.

    • Redo Transport : Archival or Real-Time

    • Log Apply : SQL Apply only

    • Role transition : Switchover or Failover (atypical)

    • Maintained in an non-recovery mode (OPEN)

    • Replicated primary data is READ ONLY

    • Non-Primary data is READ WRITE

    • Contains some, or all, of the primary schema definition

    • Some data types, storage types, PL/SQL supplied packages, structures, and DDL are not supported


    Data guard services redo transport l.jpg

    Data Guard ServicesRedo Transport

    • Redo transport services perform the following tasks:

      • Transmits redo data from the primary to one or more standbys (9 max*) using ARCH or LGWR (LNSn)

      • Enforces the database protection mode

      • Automatically detects missing or corrupted archived redo log files on the standby and retrieves replacement archived redo log files from the primary, or another standby database

        i.e.: “GAP” RESOLUTION

      • Archival or Real-Time


    Data guard services no standby no redo transport l.jpg

    Data Guard ServicesNo Standby – No REDO Transport

    Primary

    Database

    Transactions

    LGWR

    Online

    Redo Log Files

    ARC0

    Archived

    Redo Log Files


    Data guard services redo transport archival l.jpg

    Data Guard Services Redo Transport (Archival)

    Primary

    Database

    Transactions

    Physical

    Standby

    Database

    LGWR

    RFS

    MSRP

    Archival

    Transport

    Online

    Redo Log Files

    Redo

    Apply

    ARC1

    ARC0

    ARC0

    Archived

    Redo Log Files

    Archived

    Redo Log Files


    Data guard services redo transport real time l.jpg

    Data Guard Services Redo Transport (Real-Time)

    Logical

    Standby

    Database

    Primary

    Database

    Transactions

    LGWR

    RFS

    LSN0

    LSP

    LGWR

    Online

    Redo Log Files

    Standby

    Redo Log Files

    (Required)

    SQL

    Apply

    Real-Time

    Transport

    ARC0

    ARC0

    Archived

    Redo Log Files

    Archived

    Redo Log Files


    Data guard services redo transport configuration l.jpg

    Data Guard ServicesRedo Transport Configuration

    • Redo transport is configured via parameters:

    • LOG_ARCHIVE_DEST_n (n = { 1..10 })

      • One destination must be LOCAL and must be MANDATORY. This destination, usually n = 1, is always archived first when using ARCH for redo transport

      • Destination n = 10 is the default FLASHBACK RECOVERY area.

    • LOG_ARCHIVE_DEST_STATE_n

      • Allows one to ENABLE, DEFER, or RESET a specific destination

    • STANDBY_ARCHIVE_DEST (standby only)

      • If no LOG_ARCHIVE_DEST_n locations specified then provides default location for archived redo logs


    Data guard services redo transport configuration19 l.jpg

    Data Guard ServicesRedo Transport Configuration

    LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] =

    { null_string | { LOCATION=path_name | SERVICE=service_name }

    [ { MANDATORY | OPTIONAL } ]

    [ REOPEN[=seconds] ]

    [ DELAY[=minutes] ]

    [ NOREGISTER ]

    [ TEMPLATE=template ]

    [ ALTERNATE=LOG_ARCHIVE_DEST_n ]

    [ DEPENDENCY=LOG_ARCHIVE_DEST_n ]

    [ MAX_FAILURE=count ]

    [ ARCH | LGWR ]

    [ SYNC | ASYNC ]

    [ AFFIRM | NOAFFIRM ]

    [ NET_TIMEOUT=seconds ]

    [ VALID_FOR=(redo_log_type,database_role) ]

    [ DB_UNIQUE_NAME ]

    [ VERIFY ] }

    Parameter Defs


    Data guard services log apply redo apply l.jpg

    Data Guard ServicesLog Apply (Redo Apply)

    • For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database.

    • I.e. Block-for-block, byte-for-byte, bit-for-bit identical to primary.


    Data guard services log apply sql apply l.jpg

    Data Guard ServicesLog Apply (SQL Apply)

    • For logical standby databases, Data Guard uses SQL Apply technology, which transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database.

    • I.e. Use Log Mining to extract the redo, transform the redo into SQL statements, then execute the SQL statements against the logical standby.


    Sql apply engine l.jpg

    SQL Apply Engine

    Primary

    Redo

    Shared

    Pool

    Logical Change

    Records not Grouped

    into Transactions

    Redo

    Records

    LCR

    LCR

    LCR

    LCR

    LCRs

    Builder

    Preparer

    Reader

    Log

    Mining

    Transaction

    Groups

    Coordinator

    Analyzer

    Apply

    Processing

    Applier

    Transactions

    Sorted in

    Dependency Order

    Transactions

    To be Applied

    Standby

    Database


    Sql apply engine steps l.jpg

    SQL Apply Engine Steps

    • Primary ARCH or LGWR (LNS0) sends redo to standby RFS

    • READER mines redo (LogMiner), then sends redo to PREPARER

    • PREPARER transforms redo into Logical Change Records (LCRs), then stores LCRs in the LCR CACHE of the SHARED_POOL

    • BUILDER retrieves LCRs, groups them into Transaction Groups, then sends Transaction Groups to ANALYZER

    • ANALYZER sorts Transactions (by SCN), then sends sorted Transaction Groups to COORDINATOR

    • COORDINATOR assigns sorted Transaction Groups to one or more APPLIERS

    • APPLIERS execute transactions against Logical Standby


    Sql apply considerations l.jpg

    SQL ApplyConsiderations

    • SHARED_POOL_SIZE will need to increase to accommodate the Logical Change Records (LCRs)

    • PREPARER and/or APPLIER processes may be over-worked (… add more)

    • Transaction sizes (Oracle determines...)

      • Small : Applies LCRs once COMMIT is read

      • Large : Breaks transaction into transaction chunks and

        applies immediately, before COMMIT is read.

        WHY?

    • Pageouts : Not enough memory in LCR Cache of the

      SHARED POOL

    • Restarts : Can require the SQL Apply process to

      “re-mine” the redo for any transactions not

      committed and time of standby shutdown.

      WHY?


    Sql apply considerations25 l.jpg

    SQL ApplyConsiderations

    Primary DML Statements:

    • Batch updates are performed one row at a time

    • Direct path inserts are performed in a conventional manner

    • Parallel DML is not executed in parallel

      Primary DDL Statements:

    • Parallel DDL is not executed in parallel

    • CREATE TABLE AS SELECT is performed as:

      • CREATE TABLE …

      • INSERT, INSERT, INSERT, etc…

        What does this imply?


    Role transitions switchover and failover l.jpg

    Role TransitionsSwitchover and Failover

    • Not automatically invoked (but can be…)

    • Switchover

      • Planned role reversal

      • Used for OS or hardware maintenance

    • Failover

      • Unplanned role reversal

      • Use in an emergency

      • Minimal or zero data loss depending on choice of data protection mode


    Data protection modes maximum protection l.jpg

    Data Protection ModesMaximum Protection

    • Ensures that no data loss will occur if the primary database fails.

    • Redo needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits.

    • If a fault prevents the primary database from writing its redo stream to the standby redo log of at least one transactionally-consistent standby database, the primary database shuts down.


    Data protection modes maximum availability l.jpg

    Data Protection ModesMaximum Availability

    • Highest level of data protection possible without compromising availability of the primary database.

    • Transactions will not commit until the redo needed to recover them is written to the local online redo log and to the standby redo log of at least one transactionally-consistent standby database.

    • If a fault prevents the writing of the redo stream to a remote standby redo log, the protection mode downgrades to maximum performance, thus preventing the shutdown of the primary database.

    • Once fault is corrected, and all gaps in redo log files are resolved, the mode upgrades back to maximum availability.

    • No data loss is guaranteed unless a second fault occurs prior to the correction of the first fault.


    Data protection modes maximum performance l.jpg

    Data Protection ModesMaximum Performance

    • Default. Provides highest level of data protection possible without affecting the performance of the primary database.

    • Transactions may commit as soon as the redo data needed to recover them transaction is written to the primary’s online redo log.

    • Redo data stream written asynchronously to at least one standby database.

    • If sufficient bandwidth exists, provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.


    Setting the protection mode l.jpg

    Setting the Protection Mode

    • To set up redo transport services and specify a level of data protection for the Data Guard configuration, perform the following steps.

    • Step 1: Configure the LOG_ARCHIVE_DEST_n parameters on the primary database.


    Setting the protection mode31 l.jpg

    Setting the Protection Mode

    • Step 1: Bounce the primary database:

      SQL> SHUTDOWN IMMEDIATE;

      SQL> STARTUP MOUNT;

      * If RAC, shutdown all instances, restart/mount one instance.

    • Step 2: Set the mode:

      SQL> ALTER DATABASE SET STANDBY DATABASE TO PROTECTION MODE;

    • Step 3: Open the Primary Database:

      SQL> ALTER DATABASE OPEN;

    • Step 4: Confirm LOG_ARCHIVE_DEST_n parameter(s) on Standby

    • Database:

      SQL> SHOW PARAMETER log_archive_dest_

    • Step 5: Confirm configuration:

      SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;


    Oracle10g data guard briefs l.jpg

    Oracle10g Data GuardBriefs…


    Cascaded destinations l.jpg

    Cascaded Destinations

    • A primary database can directly support up to nine standby databases in any combination of physical and/or logical configurations.

    • A standby database can directly support up to nine standby databases in any combination of physical and/or logical configurations.

    • Indirectly, there is no technical limit.


    Standbys from standbys from standbys l.jpg

    Standbys from Standbys from Standbys….

    Physical

    Standby

    Logical

    Standby

    Physical

    Standby

    Primary

    Logical

    Standby

    Logical

    Standby


    Data guard broker l.jpg

    Data Guard Broker

    Primary

    Database

    Standby

    Database

    DataGuardBroker

    DataGuardBroker

    CLI Management Client


    Data guard manager l.jpg

    Data Guard Manager

    Oracle

    Management

    Server

    Repository

    Intelligent

    Agent

    Intelligent

    Agent

    Standby

    Database

    Primary

    Database

    DataGuardBroker

    DataGuardBroker

    Data Guard Manager (GUI)

    CLI Management Client


    Data guard manager gui l.jpg

    Data Guard Manager (GUI)


    Oracle data guard and real application clusters l.jpg

    Oracle Data Guard and RealApplication Clusters

    • Oracle Data Guard and Real Application Clusters are complementary and can be used together

      • Real Application Clusters provides high availability

      • Oracle Data Guard provides disaster protection and prevents data loss

      • RAC to Data Guard can be All:1 or All:M

      • All RAC instances must stream their redo to the standby database, The standby database will apply the redo in proper order based on SCN.


    Oracle10g data guard logical standby l.jpg

    Oracle10g Data GuardLogical Standby


    Logical standby benefits l.jpg

    Logical StandbyBenefits

    • Provides disaster recovery, high availability, and data protection

    • Can be altered to have a different structure from the Primary

    • Is maintained while in an OPEN state

    • Allows for offloading of reports and adhoc queries

    • Easy to implement


    Logical standby database considerations l.jpg

    Logical Standby DatabaseConsiderations

    • Data types

    • Storage types

    • PL/SQL Supplied Packages

    • Unsupported Tables, Sequences, and Views

    • DDL Support

    • SKIP Handlers

    • Tuning


    Logical standby database data type considerations l.jpg

    Logical Standby Database Data Type Considerations

    • Not all Data Types are supported:

      • BFILE

      • Collections (including VARRAYS and nested tables)

      • Encrypted columns

      • Multimedia data types (including Spatial, Image, and Context)

      • ROWID, UROWID

      • User-defined types

      • XML Type


    Logical standby database storage type considerations l.jpg

    Logical Standby DatabaseStorage Type Considerations

    • Not all Storage Types are supported:

      • Segment compression


    Logical standby database pl sql supplied packages considerations l.jpg

    Logical Standby DatabasePL/SQL Supplied Packages Considerations

    • Typically, any supplied Oracle PL/SQL that modifies system metadata is not supported

    • e.g.:

      • DBMS_JAVA, DBMS_REGISTRY, DBMS_ALERT

      • DBMS_SPACE_ADMIN, DBMS_REFRESH

      • DBMS_REDEFINITION, DBMS_SCHEDULER,DBMS_AQ.

  • DBMS_JOB is supported as follows

    • Job execution is suspended on the logical standby

    • Jobs cannot be scheduled on the logical standby database

    • Jobs submitted on the primary are replicated in the logical standby

    • In the event of a switchover or failover, jobs scheduled on the primary database will begin running on the new primary


  • Logical standby database unsupported tables sequences and views l.jpg

    Logical Standby DatabaseUnsupported Tables, Sequences, and Views

    • Identify unsupported structures:

    SQL> SELECT DISTINCT OWNER,TABLE_NAME

    2> FROM DBA_LOGSTDBY_UNSUPPORTED

    3> ORDER BY OWNER,TABLE_NAME;

    OWNER TABLE_NAME

    ----------- --------------------------

    HR COUNTRIES

    OE ORDERS

    OE CUSTOMERS

    OE WAREHOUSES


    Logical standby database unsupported tables sequences and views46 l.jpg

    Logical Standby DatabaseUnsupported Tables, Sequences, and Views

    • Identify unsupported columns:

    SQL> SELECT COLUMN_NAME,DATA_TYPE

    2> FROM DBA_LOGSTDBY_UNSUPPORTED

    2> WHERE OWNER='OE' AND TABLE_NAME = 'CUSTOMERS';

    COLUMN_NAME DATA_TYPE

    ------------------------------- -------------------

    CUST_ADDRESS CUST_ADDRESS_TYP

    PHONE_NUMBERS PHONE_LIST_TYP

    CUST_GEO_LOCATION SDO_GEOMETRY


    Logical standby database unsupported tables sequences and views47 l.jpg

    Logical Standby DatabaseUnsupported Tables, Sequences, and Views

    • So what we do when we have structures that are unsupported?

      • Redefine the structure

      • Leverage other means to move the data across

        • Export/Import

        • DataPump

        • Database links

        • SQL*Loader

        • Program

      • Don’t move the data…


    Logical standby database sql apply skipped sql statements l.jpg

    Logical Standby DatabaseSQL Apply : Skipped SQL Statements

    • Not all SQL Statements are supported by SQL Apply:

      • ALTER DATABASE

      • ALTER SESSION, ALTER SYSTEM

      • CREATE CONTROL FILE

      • CREATE DATABASE

      • CREATE/DROP DATABASE LINK

      • CREATE PFILE FROM SPFILE

      • CREATE/DROP/ALTER MATERIALIZED VIEW

      • CREATE/DROP/ALTER MATERIALIZED VIEW LOG

      • CREATE SCHEMA AUTHORIZATION

      • CREATE SPFILE FROM PFILE

      • EXPLAIN

      • LOCK TABLE

      • SET CONSTRAINTS, SET ROLE, SET TRANSACTION


    Logical standby database handling materialized views l.jpg

    Logical Standby DatabaseHandling Materialized Views

    • For primary MViews created before creation of logical standby:

      • ON-COMMITs are maintained as normal

      • ON-DEMANDs must be manually refreshed

    • For primary MViews created after creation of logical standby:

      • Recreate logical standby

      • Create MView manually

    • For standby MViews:

      • Create MView manually


    Logical standby database skip handlers l.jpg

    Logical Standby DatabaseSKIP Handlers

    • Allow programmatic control over replicated tables

    • Prevent changes to a specific schema

    • Prevent changes to a specific schema object

    • Replace SQL Apply supported DDL statements

    • Created via DBMS_LOGSTDBY.SKIP


    Logical standby database skip handlers51 l.jpg

    Logical Standby DatabaseSKIP Handlers

    In my logical environment, I renamed all filenames/folders from “wimp” to “wimr”, so my DDL syntax must be changed to reflect such:

    e.g:

    PRIMARY:

    SQL> create tablespace users datafile '/db01/ORACLE/data/wimp/wimp_users_ts01.dbf‘ size 16m;

    needs to become:

    STANDBY:

    SQL> create tablespace users datafile

    '/db01/ORACLE/data/wimr/wimr_users_ts01.dbf‘ size 16m;


    Logical standby database dbms logstdby skip l.jpg

    Logical Standby DatabaseDBMS_LOGSTDBY.SKIP

    • DBMS_LOGSTDBY.SKIP

    • (stmt IN VARCHAR2,

    • schema_name IN VARCHAR2 DEFAULT NULL,

    • object_name IN VARCHAR2 DEFAULT NULL,

    • proc_name IN VARCHAR2 DEFAULT NULL,

    • use_like IN BOOLEAN DEFAULT TRUE,

    • esc IN CHAR1 DEFAULT NULL);

    The DBMS_LOGSTDBY.SKIP is used to define rules that will be used by SQL Apply to skip the application of certain changes to the logical standby database

    *Do NOT confuse with SQL Apply Skipped SQL statements


    Logical standby database skip handlers53 l.jpg

    Logical Standby DatabaseSKIP Handlers

    1. Create your procedure to be executed by DBMS_LOGSTDBY.SKIP. Here, SYS.HANDLE_TBS_DDL will be called by DBMS_LOGSTDBY.SKIP for every DDL statement that contains the command ‘TABLESPACE’

    SQL> CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (

    OLD_STMT IN VARCHAR2,

    STMT_TYP IN VARCHAR2,

    SCHEMA IN VARCHAR2,

    NAME IN VARCHAR2,

    XIDUSN IN NUMBER,

    XIDSLT IN NUMBER,

    XIDSQN IN NUMBER,

    ACTION OUT NUMBER,

    NEW_STMT OUT VARCHAR2

    ) AS

    BEGIN

    NEW_STMT = REPLACE(OLD_STMT, 'wimp', ‘wimr');

    ACTION := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;

    EXCEPTION

    WHEN OTHERS THEN

    ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;

    NEW_STMT := NULL;

    END HANDLE_TBS_DDL;

    Intercept and modify DDL:


    Logical standby database skip handlers54 l.jpg

    Logical Standby DatabaseSKIP Handlers

    Intercept and modify DDL:

    2. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    3. Register the skip procedure with SQL Apply:

    SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', -

    proc_name => 'sys.handle_tbs_ddl');

    4. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Logical standby database skip handlers55 l.jpg

    Logical Standby DatabaseSKIP Handlers

    All DDL using keyword TABLESPACE will now replace all occurrences of 'wimp' with 'wimr‘before it executes on the logical:

    e.g.:

    PRIMARY:

    SQL> create tablespace users datafile '/db01/ORACLE/data/wimp/wimp_users_ts01.dbf‘ size 16m;

    becomes

    STANDBY:

    SQL> create tablespace users datafile

    '/db01/ORACLE/data/wimr/wimr_users_ts01.dbf‘ size 16m;

    This is necessary because the DR server supports both a physical and logical standby database, maintained from a single primary, and the physical will retains ‘wimp’ folder/file names, whereas the logical will use ‘wimr’.


    Logical standby database skip handlers56 l.jpg

    Logical Standby DatabaseSKIP Handlers

    Prevent a change to a specific schema object:

    a. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    b. Register the SKIP rules:

    SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', -

    schema_name => 'HR', object_name => 'EMPLOYEES', -

    proc_name => null);

    SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL', -

    schema_name => 'HR', object_name => 'EMPLOYEES', -

    proc_name => null);

    c. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

    If I replace 'EMPLOYEES‘ with '*‘, what does this imply?


    Logical standby database creating l.jpg

    Logical Standby DatabaseCreating

    • Identify/resolve all unsupported operations

    • Ensure primary table rows can be uniquely identified

    • Determine need for SKIP Handlers

    • Create a physical standby database

    • Stop Redo Apply on the physical standby

    • Inject primary metadata into the redo stream

    • Initiate conversion of physical standby to logical standby

    • Confirm


    Logical standby database creating58 l.jpg

    Logical Standby DatabaseCreating

    • Place the primary metadata into the redo stream

    SQL> exec DBMS_LOGSTDBY.BUILD

    • Convert physical standby to logical standby

    SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY wimr;

    • Create a new passwordfile for the logical standby

    C:\ orapwd file=%ORACLE_HOME%\database\pwdwimr.ora password=xxxxx

    • Open, verify, and use the logical standby

    SQL> SHUTDOWN IMMEDITE

    SQL> STARTUP MOUNT

    SQL> ALTER DATABASE OPEN RESETLOGS;

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Logical standby database tuning l.jpg

    Logical Standby DatabaseTuning

    • Primary Key RELY Constraint

    • Cost-Based Optimizer

    • APPLIER / PREPARER Processes

    • LCR Cache

    • Transaction Apply Order


    Tuning a logical standby database primary key rely constraint l.jpg

    Tuning a Logical Standby Database Primary Key RELY Constraint

    • For primary tables with no primary key nor unique index, SQL Apply will perform a full-table scan for every update and delete. Adding a Primary Key RELY constraint and index will eliminate the full-table scan:

      • Table must have unique rows

    SQL> ALTER TABLE HR.TEST_EMPLOYEES

    2> ADD PRIMARY KEY (EMPNO) RELY DISABLE;

    SQL> CREATE UNIQUE INDEX UI_TEST_EMP

    2> ON HR.TEST_EMPLOYEES (EMPNO);


    Tuning a logical standby database cost based optimizer l.jpg

    Tuning a Logical Standby DatabaseCost-Based Optimizer

    • Even though the Logical standby is data-equivalent to the primary, the DML / DDL operations are executed through different execution paths.

    • It is critical that optimizer statistics are collected on an as-needed basis the same as one would for a primary database.


    Tuning a logical standby database applier processes l.jpg

    Tuning a Logical Standby DatabaseAPPLIER Processes

    • 1. Determine if all APPLIER processes are busy:

    SQL> SELECT COUNT(*) AS IDLE_APPLIER

    FROM V$LOGSTDBY_PROCESS

    WHERE TYPE = 'APPLIER' and status_code = 16166;

    IDLE_APPLIER

    -------------------------

    0


    Tuning a logical standby database applier processes63 l.jpg

    Tuning a Logical Standby DatabaseAPPLIER Processes

    • 2. Ensure there is enough work available:

    SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS

    WHERE NAME LIKE 'TRANSACTIONS%';

    NAME VALUE

    --------------------- -------

    transactions ready 27896

    transactions applied 25671

    If transactions ready – transactions applied

    > 2 * APPLIER Processes

    Then

    add more APPLIER processes


    Tuning a logical standby database applier processes64 l.jpg

    Tuning a Logical Standby DatabaseAPPLIER Processes

    • 3. Add APPLIER Processes:

    a. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    b. Set the number of APPLY_SERVERS to 20:

    SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS', 20);

    c. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Tuning a logical standby database preparer processes l.jpg

    Tuning a Logical Standby DatabasePREPARER Processes

    • It is RARE to have to increase PREPARERs

    • Confirm all PREPARER processes are busy:

    SQL> SELECT COUNT(*) AS IDLE_PREPARER

    FROM V$LOGSTDBY_PROCESS

    WHERE TYPE = 'PREPARER' and status_code = 16166;

    IDLE_PREPARER

    -------------

    0


    Tuning a logical standby database preparer processes66 l.jpg

    Tuning a Logical Standby DatabasePREPARER Processes

    • 3. Ensure the slowdown is not due to a lack of

    • APPLIER processes:

    SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS

    WHERE NAME LIKE 'TRANSACTIONS%';

    NAME VALUE

    --------------------- -------

    transactions ready 27896

    transactions applied 27892

    SQL> SELECT COUNT(*) AS APPLIER_COUNT

    FROM V$LOGSTDBY_PROCESS WHERE TYPE = 'APPLIER';

    APPLIER_COUNT

    -------------

    20

    Run these commands several times to be certain the

    results are consistent.


    Tuning a logical standby database preparer processes67 l.jpg

    Tuning a Logical Standby DatabasePREPARER Processes

    • 4. Be certain there are idle APPLIER processes:

    SQL> SELECT COUNT(*) AS IDLE_APPLIER

    FROM V$LOGSTDBY_PROCESS

    WHERE TYPE = 'APPLIER' and status_code = 16166;

    IDLE_APPLIER

    -------------------------

    1


    Tuning a logical standby database applier processes68 l.jpg

    Tuning a Logical Standby DatabaseAPPLIER Processes

    • 5. Add PREPARER Processes:

    a. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    b. Set the number of PREPARE_SERVERS to 4:

    SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS',4);

    c. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Tuning a logical standby database lcr cache l.jpg

    Tuning a Logical Standby DatabaseLCR Cache

    • Pageout activity should not consume more than 5% of total uptime.


    Tuning a logical standby database lcr cache70 l.jpg

    Tuning a Logical Standby DatabaseLCR Cache

    • Query pageout activity every 5 minutes during the busiest hour of the day:

    SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS

    WHERE NAME LIKE '%PAGE%' OR

    NAME LIKE '%UPTIME%' OR NAME LIKE '%idle%';

    NAME VALUE

    -------------------------- ---------------

    coordinator uptime in secs 894856 (C)

    bytes paged out 20000

    seconds spent in pageout 2 (P)

    system idle time in secs 1000 (I)

    RUN 1

    NAME VALUE

    -------------------------- ---------------

    coordinator uptime in secs 895156 (C)

    bytes paged out 20000

    seconds spent in pageout 100 (P)

    system idle time in secs 1000 (I)

    RUN 2


    Tuning a logical standby database lcr cache71 l.jpg

    Tuning a Logical Standby DatabaseLCR Cache

    2.Subtract latest RUN from previous RUN:

    • 3. Increase LCR Cache:

    Change in coordinator uptime (C)= (895156 – 894856) = 300 secs

    Amount of additional idle time (I)= (1000 – 1000) = 0

    Change in time spent in pageout (P) = (100 – 2) = 98 secs

    Pageout time in comparison to uptime = P/(C-I) = 98/300 ~ 32.67%

    a. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    b. Increase LCR Cache (kbytes):

    SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('MAX_SGA', 1024);

    c. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Tuning a logical standby database transaction apply order l.jpg

    Tuning a Logical Standby DatabaseTransaction Apply Order

    • On occasion, there may arise a need to apply transactions in an order different than that in which they were committed, such as after a period of prolonged outage.

    • Doing so will:

      • Eliminate the need to order non-dependent transactions

      • Speed up the apply process

      • CAUTION: reporting can/will be different from primary during this time


    Tuning a logical standby database transaction apply order73 l.jpg

    Tuning a Logical Standby DatabaseTransaction Apply Order

    a. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    b. Allow transactions to be applied out-of-order:

    SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET -

    ('PRESERVE_COMMIT_ORDER', 'FALSE');

    c. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Tuning a logical standby database transaction apply order74 l.jpg

    Tuning a Logical Standby DatabaseTransaction Apply Order

    • Once the standby is caught up with the primary, enforce commit order.

    a. Stop SQL Apply:

    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

    b. Reset transaction apply order:

    SQL> EXECUTE DBMS_LOGSTDBY.APPLY_UNSET -

    ('PRESERVE_COMMIT_ORDER');

    c. Start SQL Apply:

    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


    Summary l.jpg

    Summary

    • In this lesson you should have learned:

      • Benefits of Data Guard and why we might use it

      • Basic components of Oracle Data Guard

      • Difference between Physical and Logical Standby databases

      • Detailed insight into Logical Standby databases:

        • Things-you-need-to-be-aware-of

        • Creation

        • Maintenance

        • Tuning


    Oracle10g data guard overview logical standby bill sutton saic suttonbi@saic com76 l.jpg

    Oracle10g Data GuardOverview / Logical StandbyBill [email protected]


  • Login