Presentation #36693 Oracle High Availability Solutions in a Nutshell

Presentation #36693 Oracle High Availability Solutions in a Nutshell Daniel T. Liu Senior Technical Consultant First American Real Estate Solutions Date: Tuesday, September 10, 2003 @ 8:30 AM - 9:30 AM Place: Moscone Room 104

Agenda Introduction High Availability Concepts High Availability Challenges High Availability Solutions OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 2

Agenda Oracle Advanced Replication Oracle Real Application Cluster (RAC) Oracle Data Guard OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 3

Agenda Oracle Streams Choose the Right High-availability Solutions Summary Q&A OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 4

Introduction Why do we need to provide system and database High Availability? Planned Downtime – Database backup/upgrade/patching – Operating system upgrade/patching – Hardware and Network maintenance Unplanned Downtime – Corruptions Logical corruptions Physical corruptions OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 5

Introduction Unplanned Downtime – Human Errors Accidentally drops, truncates a table Accidentally delete, update rows in a table Accidentally delete a data file or drop a tablespace – Disasters War, terrorism Earthquake, flood, fire or hurricane No power for a long period Server crash, malfunction of hardware OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 6

High Availability Concepts What is High Availability? – In the old days Local time Monday thru Friday 8 am to 6 pm – Current environment Global 365 x 24 x 7 Minimal downtime (planned or unplanned) OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 7

High Availability Concepts Understanding High Availability – Computing environments configured to provide nearly full-time availability are known as high availability systems. – When failures occur, the failover process moves processing performed by the failed component to the backup component. – The more transparent that failover is to users, the higher the availability of the system. OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 8

High Availability Concepts Measuring Availability – The mean time to recover (MTTR) – The mean time between failures (MTBF) – Total uptime in a year (%) Minutes of Downtime 5 60 1440 2880 Minutes of Uptime 525595 525540 524160 522720 Minutes in a Year 525600 525600 525600 525600 Total Uptime in a Year (%) 99.9990% 99.9886% 99.7260% 99.4521% OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 9

High Availability Challenges Challenges for Database High Availability – Database size is getting bigger and bigger – Database Backup Time A DLT tape writes 6 MB/Second, or 21 GB/hour A 2 TB database with one tape driver will take 97 hours to backup – Database Recovery Time – Is the backup good? – Trouble shooting time OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 10

High Availability Solutions Traditional High Availability Solutions – Backup and restore High Availability Solutions – – – – Oracle Advanced Replication Oracle Real Application Clusters (RAC) Oracle Data Guard (Standby Database) Oracle Streams OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 11

High Availability Solutions High Availability Solution means : – – – – – – No Single Points-of –failure Hardware Redundancy Software Redundancy Data Redundancy Application Redundancy More . ? – Save Money OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 12

Oracle Advanced Replication Advanced Replication Overview Replication Components Types of Replication Environments Administration Tools for Replication OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 13

Advanced Replication Overview Replication is the process of copying and maintaining database objects, such as tables, in multiple database . Changes applied at one site are captured and stored locally before being forward and applied at each of the remote locations. OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 14

Replication Components Replication Objects – Table, Indexes, View – Procedures, Packages, Functions, Triggers – User-Defined Types Replication Groups – A collection of replication objects that are logically related. – Master group – Materialized view group OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 15

Replication Components Replication Sites – Master Sites – Materialized View Sites OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 16

Types of Replication Multimaster Replication – Each master site operates as an equal peer. – Provides complete replicas of each replicated table at each of the master sites. – Replicate changes for each transaction. – Two types of multimaster replication Asynchronous Synchronous OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 17

Asynchronous Data Replication Source Database Change Destination Database Replicated Table Replicated Table Trigger Deferred Transaction Queue Remote Procedure Call Procedure Synchronous Data Replication Source Database Change Destination Database Replicated Table Trigger Replicated Table Remote Procedure Call OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES Procedure 18

Types of Replication Materialized View Replication – Replicate subset of master table data – Batch-oriented operation (refresh) – 3 types of materialized views Read-Only Updateable Writeable OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 19

Materialized View Replication Application Update Update Update Query Master Table (updatable) Master Table (updatable) Master Table (updatable) Refresh Materialized View (read-only) Refresh Materialized View (updatable) Refresh Materialized View (writeable) OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES Update Update 20

Replication Administration Tools Oracle Enterprise Manager – Replication Manager Oracle-Supplied PL/SQL packages – DBMS REPCAT Replication Catalog – On every master sites – Materialized view sites OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 21

Oracle Real Application Clusters (RAC) Real Application Clusters Overview Real Application Clusters Architecture Real Application Clusters Components Cache Fusion Transparent Application Failover (TAF) OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 22

Real Application Clusters Overview Multiple instances against the same database. Involves a cluster of nodes with access to a set of shared disks through Cluster Management Software (CMS). Oracle’s solution for system failures. Transparent Application Failover (TAF) Connection Load Balancing OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 23

Real Application Clusters Architecture Option 1 Option 2 (RAC) Instance A Instance B DB1 DB3 Instance A OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES interconnect Instance B DB1 24

Real Application Clusters Architecture SGA SGA Global Resource Directory LMON LMD Global Resource Directory Cluster Interconnect LMS LMON Instance A LMD Instance B Cluster Group Services Cluster Group Services Vendor CMS Vendor CMS Node A Node B Local Disks Oracle Software Archived Logs LMS Shared Disks Data Files Control Files Online Redo Files OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES Local Disks Oracle Software Archived Logs 25

Real Application Clusters Components Shared Disk Vendor CMS Cluster Group Services Global Resource Directory RAC Background Process – LMON (Global Cache Service Process) – LMD (Global Enqueue Service Daemon) – LMS (Global Enqueue Service Monitor) OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 26

Cache Fusion Oracle’s Global Cache Management Technology. It provides cache to cache transfers of data blocks between instances in a cluster. It eliminates forced disk writes. Dynamic resource re-mastering OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 27

Transparent Application Failover (TAF) Little or no user downtime. Applications and users are automatically and transparently reconnected to another system. DML transactions are rolled back OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 28

Oracle Data Guard Data Guard History Data Guard Components Data Guard Roles Data Guard Interfaces Data Guard Process Architecture Data Guard Protection Mode OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 29

Data Guard History History of Standby Database – Oracle7.3: First Release of Standby Database – Oracle8i: Automatic shipping and application of redo logs – Oracle9i Release 1: Protection mode – Oracle9i Release 2: Logical standby database OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 30

Data Guard Components Primary Database Standby Database – Physical Standby Database – Logical Standby Database (9iR2 only) Log Transport Services Network Configuration Log Apply Services Data Guard Broker OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 31

CLI Primary Database Online Redo Logs Local Archived Logs Oracle Net Data Guard Broker GUI Data Guard Broker Log Transport Services OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES Remote Archived Logs Standby Database Log Apply Services 32

Data Guard Roles A database can operate in one of the two mutually exclusive roles: Failover – One of the standby databases takes the primary database role Switchover – In Oracle9i, primary and standby database can continue to alternate roles OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 33

Data Guard Interface SQL*Plus and SQL Statements SQL alter database commit to switchover to physical standby; Data Guard Broker GUI – Data Guard Manager Data Guard Broker Command-Line Interface dgmgrl DGMGRL for Solaris: Version 9.2.0.1.0 Production. (c) Copyright 2002 Oracle Corporation. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 34

Process Architecture Physical/Logical Standby Processes – – – – – LGWR (Log Writer) process ARCH (Archiver) process LNS (LGWR Network Server) process RFS (Remote File Server) process MRP (Managed Recovery) process OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 35

Process Architecture Physical/Logical Standby Processes – – – – – FAL (Fetch Archive Log) Client process FAL (Fetch Archive Log) Server process LSP (Logical Standby) process PX (Parallel Execution) process DMON (Data Guard Broker Monitor) process OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 36

Physical Standby Processes Architecture Primary Database DMON DMON Physical standby Database SYNC LGWR LNS ASYNC RFS Oracle Net Online Redo Logs ARCH Standby Redo Logs ARCH Local Archived Logs FAL Client/ MRP Remote Archived Logs FAL Server OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 37

Logical Standby Processes Architecture DMON Primary Database DMON Logical Standby Database SYNC LGWR LNS RFS Oracle Net Online Redo Logs ASYNC PX PX Applying Group LSP0 ARCH PX Local Archived Logs Remote Archived Logs OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES PX Mining Group 38

Data Protection Mode Oracle9i Release 2 has three data protection modes Mode Log Writing Process Network Trans Mode Disk Write Option Redo Log Reception Option Supported on Maximum Protection LGWR SYNC AFFIRM Standby redo logs are required Physical standby databases Maximum Availability LGWR SYNC AFFIRM Standby redo logs Physical and logical standby databases Maximum Performance LGWR or ARCH SYNC or ASYNC NOAFFIRM Standby redo logs Physical and logical standby databases OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 39

Oracle Streams Oracle Streams Overview Oracle Streams Process Architecture Oracle Streams Rules Administration Tools for Oracle Streams OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 40

Oracle Streams Overview Oracle9i’s new replication product. Similar to logical standby database Changes are captured at source Database. Propagate information within a database or from one database to another. Using Message Queuing. Heterogeneous information sharing. OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 41

Oracle Streams Process Architecture Capture changes at a database. Enqueue events into a queue. Propagate events from one queue to another. Dequeue events. Apply events at a database. OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 42

Oracle Streams Processes Architecture Source Database Redo Log Capture Source Queue Target Queue LCR LCR User Message User Message LCR LCR . . . LCR LCR User Message User Message LCR LCR . . . Enqueue Propagate Dequeue OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES Target Database Apply 43

Oracle Streams Rules Rules are used to control which information to share and where to share it. Rules can be used during capture, propagate, and apply processes. Rules can define in three level: – Table – Schema – Global OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 44

Oracle Streams Administration Tools Oracle-Supplied PL/SQL packages – – – – DBMS STREAMS ADM DBMS CAPTURE ADM DBMS PROPAGATION ADM DBMS APPLY ADM Streams Data Dictionary views – DBA APPLY – V STREAMS CAPTURE Oracle Enterprise Manager OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 45

Choose High-Availability Solution Product Licensing Unsupported Datatype Feature Comparison OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 46

Product Licensing High Availability Product Enterprise Edition Advanced Replication Included Real Application Clusters Additional License Fee Data Guard Included Streams Included OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 47

Unsupported Datatype for Logical Standby and Streams Supported Datatypes Unsupported Datatypes CHAR, NCHAR VARCHAR2, NVARCHAR2 NUMBER DATE CLOB,BLOB RAW TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND NCLOB LONG LONG RAW BFILE ROWID UROWID User-defined types Object types - REFS - Varrays - Nested tables OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 48

Feature Comparison Advanced RAC Physical Logical Streams Replication Standby Standby Entire Database Replication YES N/A YES YES YES Schema Replication YES N/A NO NO YES Table Replication YES N/A NO NO YES OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 49

Feature Comparison Advanced RAC Physical Logical Streams Replication Standby Standby DML Replication YES N/A YES YES YES DDL Replication YES N/A YES YES YES Instance Redundant YES YES YES YES YES Database Redundant YES NO YES YES YES OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 50

Feature Comparison Advanced RAC Physical Logical Streams Replication Standby Standby Cluster Management Software NO Failover Mechanism Manual Failover Load Balancing YES YES NO NO NO Manual Failover Failover TAF Switchover Switchover Failover YES YES Partial OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES YES YES 51

Feature Comparison Advanced RAC Physical Logical Streams Replication Standby Standby Change Captured Local Heterogeneous Database Support YES NO NO NO YES Datatype Support ALL ALL SOME SOME SOME Local Remote Remote OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES Local 52

Feature Comparison Advanced RAC Replication OS Platform between source and Target Can be Different Must be Same Oracle Version Between Source and Target Can be Different Must be Same Physical Logical Streams Standby Standby Must be Same Must be Same Can be Different Must be Same Must be Same Can be Different OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 53

Summary High Availability Concept High Availability Options – – – – Advanced Replication Real Application Clusters Data Guard Oracle Streams High Availability Product Comparison OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 54

References Oracle9i, Data Guard Concepts and Administration. Release 1 (9.0.1); Oracle9i, Data Guard Concepts and Administration. Release 2 (9.2); Oracle9i, Data Guard Broker. Release 2 (9.2); Oracle9i, Real Application Clusters, Concepts. Release 1 (9.0.1); Oracle9i, Advanced Replication. Release 2 (9,2); Oracle9i, Streams. Release 2 (9.2); Oracle Metalink Support; Top DBA Shell Scripts for Monitoring Database, Daniel T. Liu; DBAZine; I would also like to acknowledge the assistance of Larry Barry, Ann Collins, Archana Sharma and Husam Tomeh of FARES, and Larry Carpenter, Joseph Meeks, Roger Peterson of Oracle Corporation. OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 55

Thanks For Coming !! Daniel Liu Contact Information Phone: (714) 701-3346 Email: [email protected] Email: daniel t [email protected] Company Web Site: http://www.firstam.com OracleWorld 2003, Paper #36693, Da niel T. Liu, FARES 56