Presentation 36693 Oracle High Availability Solutions in a

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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES

Agenda • Introduction • High Availability Concepts • High Availability Challenges • High Availability Solutions Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 2

Agenda • Oracle Advanced Replication • Oracle Real Application Cluster (RAC) • Oracle Data Guard Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 3

Agenda • Oracle Streams • Choose the Right High -availability Solutions • Summary • Q&A Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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) Oracle. World 2003, Paper #36693, Daniel 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. Oracle. World 2003, Paper #36693, Daniel 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 Total Uptime in a Year (%) 99. 9990% 99. 9886% 99. 7260% 99. 4521% Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 12

Oracle Advanced Replication • • Advanced Replication Overview Replication Components Types of Replication Environments Administration Tools for Replication Oracle. World 2003, Paper #36693, Daniel 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. Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 15

Replication Components • Replication Sites – Master Sites – Materialized View Sites Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 17

Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 19

Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 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 Oracle. World 2003, Paper #36693, Daniel 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) Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 23

Real Application Clusters Architecture Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 24

Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 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) Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 29

Data Guard History • History of Standby Database – Oracle 7. 3: First Release of Standby Database – Oracle 8 i: Automatic shipping and application of redo logs – Oracle 9 i Release 1: Protection mode – Oracle 9 i Release 2: Logical standby database Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 30

Data Guard Components • Primary Database • Standby Database – Physical Standby Database – Logical Standby Database (9 i. R 2 only) • • Log Transport Services Network Configuration Log Apply Services Data Guard Broker Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 31

Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 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 Oracle 9 i, primary and standby database can continue to alternate roles Oracle. World 2003, Paper #36693, Daniel 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> Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 36

Physical Standby Processes Architecture Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 37

Logical Standby Processes Architecture Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 38

Data Protection Mode • Oracle 9 i Release 2 has three data protection modes Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 39

Oracle Streams • • Oracle Streams Overview Oracle Streams Process Architecture Oracle Streams Rules Administration Tools for Oracle Streams Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 40

Oracle Streams Overview • • Oracle 9 i’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. Oracle. World 2003, Paper #36693, Daniel 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. Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 42

Oracle Streams Processes Architecture Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 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 Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 45

Choose High-Availability Solution • Product Licensing • Unsupported Datatype • Feature Comparison Oracle. World 2003, Paper #36693, Daniel 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 Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 47

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

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

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

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

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

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

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

References Oracle 9 i, Data Guard Concepts and Administration. Release 1 (9. 0. 1); Oracle 9 i, Data Guard Concepts and Administration. Release 2 (9. 2); Oracle 9 i, Data Guard Broker. Release 2 (9. 2); Oracle 9 i, Real Application Clusters, Concepts. Release 1 (9. 0. 1); Oracle 9 i, Advanced Replication. Release 2 (9, 2); Oracle 9 i, 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. Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 55

Thanks For Coming !! Daniel Liu Contact Information Phone: (714) 701 -3346 Email: dliu@firstam. com Email: daniel_t_liu@yahoo. com Company Web Site: http: //www. firstam. com Oracle. World 2003, Paper #36693, Daniel T. Liu, FARES 56