Oracle Clustering and Replication Technologies

1. Oracle Clustering and Replication Technologies CCR Workshop - Otranto Barbara Martelli Gianluca Peco

2. Oracle Database Architecture The Oracle Server architecture can be described in three categories: • User-related processes • User Process • Server Process • Logical memory structures that are collectively called an Oracle instance • Physical file structures that are collectively called a database Database CCR Workshop, Otranto

3. CCR Workshop, Otranto

4. Instance CCR Workshop, Otranto Database

5. Oracle Real Application Cluster • The Oracle Real Application Cluster technology allows to share a database amongst several servers • All datafiles, control files, PFILEs, and redo log files in RAC environments must reside on cluster-aware shared disks so that all of the cluster database instances can access them. • RAC aims to provide highly available, fault tolerant and scalable database services Database servers Network shared disks (Cluster Filesystem) CCR Workshop, Otranto

6. RAC testbed Disk I/O traffic Fiber Channel Sw ORA-RAC-01 GigaSw1 ORA-RAC-02 Private network for interconnect traffic ORA-RAC-03 IBM FAStT900 FC RAID Controller ORA-RAC-04 1.2 TB RAID-5 disk array formatted with OCFS2 Public and VIP Network Interface 4 x Dual Xeon 2.8 GHz 4 GB RAM Red Hat Enterprise 4 on RAID-1 disks 2 x Intel PRO1000 NICs 1 QLogic 2312 FC HBA with 2 x 2Gb/s links GigaSw2 Clients Clients Clients CCR Workshop, Otranto

7. RAC Test AS3AP 1-4 nodes Select Query 1GB cache

8. RAC Test AS3AP 1-4 nodes Overview • Summarize the main plans • Explain the long-term course to follow Select Query 8GB no db cache

9. RAC Test OLTP 1-2-4 nodes 4 nodes With OLTP applications, system scalability is lower, we argue there is a disk subsystem bottleneck 2 nodes 1 node

10. RAC Test OLTP 4 nodes TransactionPerMinute workload OLTP O_DIRECT enabledASYNC_IO enabled TransactionPerMinute workload OLTP O_DIRECT DisabledASYNC_IO Disabled

11. Consumption Capture Propagate Oracle Streams CAPTURE: • Streams captures events • Implicitly: log-based capture of DML and DDL • Explicitly: Direct enqueue of user messages PROPAGATION: • Captured events are published in the staging area • Streams publishes captured events into a staging area • Implemented as a queue • Messages remain in staging area until consumed by all subscribers • Other staging areas can subscribe to events in same database or in a remote database • Events can be routed through a series of staging areas • Transformations can be performed as events enter, leave or propagate between staging areas CCR Workshop, Otranto

12. Consumption Capture Propagate Oracle Streams Comsumption: • Staged events are consumed by subscribers • Implicitly: Apply Process • Default Apply • User-Defined Apply • Explicitly: Application dequeue via API (C++, Java…) • The default apply engine will directly apply the DML or DDL represented in the LCR • apply to local Oracle table • apply via DB Link to non-Oracle table • Automatic conflict detection with optional resolution • unresolved conflicts placed in exception queue • Rule based configuration: expressed as “WHERE” clause CCR Workshop, Otranto

13. table1id |field1|.. id1 | value3 |… id2 | value2 | ... table1 Propagation Queue ----- LCRs Queue ------ LCRs ACK Capture Apply Streams Replication Example User executes an update statement at source node: update table1 set field1= ‘id3’ where table1id = ‘id1’; Update table1 set field1=‘value3’ where table1id=‘id1’; table1 Source Node Destination Node CCR Workshop, Otranto RedoLog

14. CCR Workshop, Otranto

15. Oracle Streams in 3D • The Oracle streams allows connecting single tables or complete schemas in different databases and keeping them up to date at Real Time. CCR Workshop, Otranto

16. CCR Workshop, Otranto

17. CCR Workshop, Otranto

18. CCR Workshop, Otranto

19. CCR Workshop, Otranto

20. LFC Replication testbed • 40 lfc clients, 40 lfc daemons threads, streams pool. • Client’s actions • Control if LFN exists into the database • Select from cns_file_metadata • If yes -> add a sfn for that lfn • Insert sfn into cns_file_replica • If not -> add both lfn and sfn • Insert lfn into cns_file_metadata • Insert sfn into cns_file_replica • For each lfn 3 sfn are inserted CCR Workshop, Otranto

21. LFC Master HW Configuration Gigabit Switch GigabitSwitch Dual Xeon 3,2GHz,4GB memory 2nodes-RAC on Oracle 10gR2 RHEL 4 kernel 2.6.9-34.ELsmp 14 Fibre Channel disks (73GB each) HBA Qlogic Qla2340 – Brocade FC Switch Disk storage managed with Oracle ASM (striping and mirroring) Private LHCB link Private LHCB link rac-lhcb-02 rac-lhcb-01 ASM Dell 224F 14 x 73GB disks CCR Workshop, Otranto

22. LFC Slave Configuration • LFC Read only replica • Dual Xeon 2.4, 2GB RAM • Oracle 10gR2 (oracle RAC but used as single instance) • RHEL 3 kernel 2.4.21 • 6 x 250GB disks in RAID 5 • HBA Qlogic Qla2340 – Brocade FC Switch • Disk storage formatted with OCFS2 CCR Workshop, Otranto

23. Performance • About 75 transactions per second on each cluster node. • Inserted and replicated 1700k entries in 4 hours (118 insert per second). • Almost real-time replica with Oracle Streams without significant delays (<< 1s). CCR Workshop, Otranto

24. CPU load on cluster nodes is far from being saturated. CCR Workshop, Otranto

25. Conclusions and Future Plans • RAC technology is a good solution for scalability at DB server level. Some work is needed to tune the installation and optimize performance for a particular application. Moreover a reliable and scalable storage subsystem is needed. • Streams based replication is a good solution for scalability at “grid level”, a reliable DB infrastructure has to be distributed across many sites. • First LFC replication test results demonstrate that Streams is an interesting solution for real-time master/slave replication. • VOMS replication tests in the very near future. • Many thanks to Vincenzo Vagnoni, Eva da Fonte Perez. CCR Workshop, Otranto