Microsoft PowerPoint - 3일차_Advanced_10gRAC( ).ppt

Transcription

1 Learn Oracle From Oracle Advanced 10g Real Application Cluster August 28, 2008 권웅원 Agenda I. Architecture and Concept II. Deployment and Network/Storage Configuration III. RAC Internals IV. Load Balancing and Fail Over V. ASM (Automatic Storage Management) VI. Useful Tips (RAC Tuning, etc.) and QnA 2

2 Architecture and Concept 3 What Is a Cluster? A cluster is a group of independent computers working together as a single system. Interconnected nodes acts as a single server. Cluster software hides the structure. Disks are available for read and write by all nodes. Operating System is the same on each machine. 4

3 What Is Oracle Real Application Clusters? Multiple instances accessing the same database One Instance per node Physical or logical access to each database file Software-controlled data access 5 Why Use RAC? High availability : Survive node and instance failures. Scalability : Add more nodes as you need them in the future. Eliminate need for 3 rd party components that add significantly to complexity and cost Automatic fast failure notification (FAN) Key grid computing features : - Growth and shrinkage on demand - Single-button addition of servers - Automatic workload management for services 6

4 Scaleup and Speedup Original system Hardware Time 100% of task Cluster system scaleup Cluster system speedup Hardware Hardware Time Time up to 200% of task up to 300% of task Hardware Hardware Time/2 100% of task Hardware Time Scaleup = (volume parallel) / (volume original) Speedup = (time original) / (time parallel) 7 What does Clusterware provide? VIP Event Management Clusterware High Availability Framework Process Monitor Group Membership Operating System 8

5 Oracle Clusterware 10g Architecture VIP EVM Oracle Clusterware CRS/RACG OPROC CSS Operating System 9 Complete Integrated Clusterware 9i RAC Applications 10g RAC Applications/RAC Event Services Cluster control Volume Manager file system Messaging and Locking Membership System Management Event Services Services framework Cluster control/recovery APIs Automatic Storage Management Messaging and Locking Membership Management APIs Connectivity Hardware/OS kernel Connectivity Hardware/OS kernel CRS is REQUIRED to be installed and running prior to installing 10g RAC. CRS(R1), Clusterware(R2) 10

6 Typical Cluster Stack with RAC Servers Interconnect High-speed Interconnect: Gigabit Ethernet UDP Oracle CRS Proprietary Proprietary OS C/W RAC Linux, UNIX, Windows RAC Linux Windows RAC Linux Windows RAC AIX, HP-UX, Solaris ASM OCFS RAW ASM RAW OS CVM CFS Database shared storage The vendor clusterware was required in 9i but is optional in 10g. 11 RAC Software Principles Node1 Instance1 Cluster Noden Instancen Cache LMON LMD0 LMSx LCK0 DIAG GES GES GCS Global resources GES GES GCS Cache LMON LMD0 LMSx LCK0 DIAG Cluster Ready Services CRSD & RACGIMON EVMD OCSSD & OPROCD Applications ASM, DB, Services, OCR VIP, ONS, EMD, Listener Cluster interface Global management: SRVCTL, DBCA, EM Cluster Ready Services CRSD & RACGIMON EVMD OCSSD & OPROCD Applications ASM, DB, Services, OCR VIP, ONS, EMD, Listener CRSD, EVMD and OCSSD are CRS daemon processes. 12

7 RAC Software Storage Principles Node1 Instance1 Noden Instancen Node1 Instance1 Noden Instancen CRS home Oracle home Local storage CRS home Oracle home Local storage Local storage Local storage Voting file OCR file Shared storage Voting file OCR file CRS home Oracle home Shared storage ORA_CRS_HOME and ORACLE_HOME must be installed in DIFFERENT locations. 13 RAC Database Storage Principles Node1 Instance1 Archived log files Local storage Undo tablespace files for instance1 Online redo log files for instance1 Data files Temp files Control files Flash recovery area files Change tracking file SPFILE Shared storage Noden Instancen Archived log files Local storage Undo tablespace files for instancen Online redo log files for instancen All data files in RAC must reside on shared devices in order to be shared by all the instances, and all the redo log groups must also be stored devices for instance or crash recovery purposes. 14

8 Automatic Storage Management Provides the first portable and highperformance database file system Manages Oracle database files Contains data spread across disks to balance load Provides integrated mirroring across disks Solves many storage management challenges Application File system Volume manager Database ASM Operating system 15 OCR Architecture Node1 Node2 Node3 OCR cache OCR cache OCR cache OCR process OCR process OCR process * /etc/oracle/ocr.loc ocrconfig_loc ocrmirrorconfig_loc Client process Client process Shared storage OCR file The OCR client applications are OUI, SRVCTL, EM, DBCA, DBUA, NETCA and VIPCA. 16

9 RAC 10g Architecture 17 The Necessity of Global Resources SGA1 SGA2 SGA1 SGA SGA1 SGA2 SGA1 SGA Lost updates! The slide shows you what happen in the absence of cache coordination. RAC prohibits this problem. 3 18

10 The Necessity of Global Resources : Example EMPNO ENAME Hal Jim 19 The Necessity of Global Resources : Example 10 Hal >Tom 10 Tom 20 Jim (C,D) Hal (U) UPDATE emp SET ename= Tom WHERE empno=10; EMPNO ENAME Hal Jim 20

11 The Necessity of Global Resources : Example 10 Hal >Tom 10 Tom 20 Jim Hal (U) 10 Hal 20 Jam Jim (U) 20 Jim >Jam (C,D) (C,D) UPDATE emp UPDATE emp SET ename= Tom WHERE empno=10; EMPNO ENAME SET ename= Jam WHERE empno=20; 10 Hal 20 Jim 21 The Necessity of Global Resources : Example 10 Hal >Tom 10 Tom 20 Jim Hal (U) 10 Hal 20 Jam Jim (U) 20 Jim >Jam (C,D) (C,D) 1 2 UPDATE emp UPDATE emp SET ename= Tom WHERE empno=10; EMPNO ENAME SET ename= Jam WHERE empno=20; 10 Hal 20 Jam 22

12 The Necessity of Global Resources : Example 10 Hal >Tom 10 Tom 20 Jim (C,D) Hal (U) 10 Hal 20 Jam (C,D) Jim (U) 20 Jim >Jam 2 1 UPDATE emp SET ename= Tom WHERE empno=10; EMPNO 10 ENAME Tom UPDATE emp SET ename= Jam WHERE empno=20; 20 Jim 23 Global Resource Coordination Node1 Instance1 GRD Master Cache LMON GES LMD0 GES LMSx GCS LCK0 DIAG Cluster Global resources Interconnect Noden Instancen GRD Master Cache GES GES GCS LMON LMD0 LMSx LCK0 DIAG Global Resource Directory (GRD) Global Cache Service (GCS) Global Enqueue Service (GES) 24

13 Global Cache Coordination : Example 2 Node1 Instance1 Cache 1009 LMON LMD0 LMSx LCK0 DIAG Block mastered by instance one Cluster Instance two has the current version of the block 4 GCS 3 Node2 Instance Cache LMON LMD0 LMSx LCK0 DIAG Which instance masters the block? No disk I/O 25 Write to Disk Cordination : Example 1 Node1 Instance1 Cache 1009 LMON LMD0 LMSx LCK0 DIAG Need to make room in my cache. Who has the current version of that block? 5 Cluster GCS 4 3 Block flushed, make room Node2 Instance Cache LMON LMD0 LMSx LCK0 DIAG Instance two owns it. Instance two, flush the block to disk Only one disk I/O 26

14 The Necessity of Global Resources :Example 10 Hal----- >Tom Hal (U) 10 Tom 20 Jim (P,D) (C,D) 10 TOM 10 TOM 20 Jam 20 Jim (C,D) Jim (U) 20 Jim----- >Jam UPDATE emp SET ename= Tom WHERE empno=10; EMPNO 10 ENAME Hal UPDATE emp SET ename= Jam WHERE empno=20; 20 Jim 27 Dynamic Reconfiguraton (Reconfiguration Remastering) Node 1 Instance A Global Resources ID Node 1 Granted A,B,C A,B C B,C Node 2 Instance B Global Resources ID Node 2 Granted B A,C A,B,C B,C Node 3 Instance C Global Resources ID Node 3 Granted A,C C A,B,C B,C Instance A Global Resources ID Granted A,C A C A,C C Instance B Global Resources ID Granted B A,C A,B,C B,C Instance C Global Resources ID Granted A,C A,C C A,C C C 28

15 RAC and Services Stop/Start service connections ERP Connection load balancing Application server Run-time load balancing Service location transparency Service connections Listeners RAC Instances Up and down events notification engine Restart failed components CRM ERP ERP ERP ERP Backup Priority CRM CRM Alerts CRM CRM Tuning CRS Service availability aware Modify service to instance mapping 29 Node Startup Sequence 3 VIP Service Listener Instance 1 ASM Oracle Clusterware Operating System 30

16 Deployment & Storage/Network Configuration 31 Hardware Organization 32

17 Openfiler Storage Organizaton 33 Cluster Storage Organizaton After storage configuration, you are going to install the Oracle software in various directories under /u01 34

18 Scenario Workflow Storage Setup Step I : Install Clusterware and ASM Step II : Set up single-instance protection Step III : Single-instance to RAC conversion Step IV : Cluster extension to three nodes 35 Step I : Install Clusterware and ASM 1. Install Oracle Clusterware locally on the first and second nodes only. 2. Install database software locally on the first and second nodes. 3. Configure ASM with DATA and FRA disk groups. 36

19 Installing Oracle Clusterware In 10gR2, although you can use block devices to store OCR and voting disks, OUI does not accept it. - Bug : , Linux x86 Run OUI from /stage/10gr2/rdbms/clusterware: -Inventory=/u01/app/oracle/oraInventory -Home=/u01/crs OCR and voting disks:/dev/raw/raw1,2,3,4,5 - VIPCA needs to be manually executed ( ITS-Demo01 ) 37 Installing Oracle RAC Software and ASM Run OUI from /stage/10gr2/rdbms/database: -Home=/u01/app/oracle/product/10.2.0/rac - Software installation only - First two nodes Run dbca from /u01/app/oracle/product/10.2.0/rac/bin: -Export ORACLE_HOME - Use the first two nodes - Create two disk groups used later:data and FRA - dbca automatically creates the listeners and ASM instances ( ITS-Demo02, ITS-Demo03 ) 38

20 Step II : Set Up Single-Instance Protection 1. Install single-instance database software on the first and second nodes only 2. Create single-instance database on the first node 3. Protect it against both instance and node failure using Oracle Clusterware 39 Installing Single-Instance Database Software Run OUI from /stage/10gr2/rdbms/database: -Home=/u01/app/oracle/product/10.2.0/sg1 - Software install only - To be done on the first and second nodes Do the same on your second node: - You could parallelize the work. ( ITS-Demo04 ) 40

21 Creating Single-Instance Database Run dbca from /u01/app/oracle/product/10.2.0/sg1: - Store your database and Flash Recovery Area on ASM: DATA and FRA disk groups. - Use sample schemas. You use shared storage to protect against node failures. ( ITS-Demo05 ) 41 Step III : Single-Instance to RAC Conversion 1. Use dbca from single-instance home to create a database template including files. 2. Propagate template files from single-instance home to RAC home. 3. Use dbca from single-instance home to remove the existing database. 4. Use dbca from RAC home to create a new database with the same name by using the new template. ( ITS-Demo07 ) 42

22 Step IV : Cluster Extension to Three Nodes 1. Use addnode.sh to add Oracle Clusterware to the third node. 2. Add ONS configuration of your third node to the OCR. 3. Use addnode.sh to add RAC to the third node. 4. Use dbca to extend your database to the third node. 43 Required Steps to Add a Node to a RAC cluster Install and configure OS and hardware for the new node. Add Oracle Clusterware to the new node. Configure ONS for the new node. Add ASM home to the new node. (optional) Add RAC home to the new node. Add a listener to the new node. (optional) Add a database instance to the new node. ( ITS-Demo08, ITS-Demo09, ITS-Demo10 ) 44

23 Oracle Storage, Clusterware Components No Storage Option OCR/Voting Oracle S/W DataBase Recovery Area 비고 1 ASM NO NO YES YES 2 OCFS YES NO YES YES 3 OCFS2 YES YES YES YES 4 CFS (GPFS, GFS, CFS 등 ) YES YES YES YES 5 Local NO YES NO NO 6 NFS YES YES YES YES 7 Shared raw YES NO YES NO No Oracle Clusterware Components Unix / Linux Process 1 Process Monitor Daemon oprocd 2 RACG racgmain, racgimon Oracle Notification Service Event Manager Cluster Ready Cluster Synchronization Service ons evmd, evmd.bin, evmlogger crsd.bin init.cssd, ocssd, ocssd.bin 45 Storage 구성사례 : 구성도 eth0 eth0 eth2 eth3 eth2 eth3 bond0 bond0 bond0 bond0 eth2 eth3 eth2 eth3 eth0 DL585(2CPU Quad core) Memory 16G 2Nodes RAC * 2Set eth0 NAME OS Kernel Oracle redhat as 4( 이상 x86_64) Value EMC CX GB*14*8box*2 46

24 Storage 구성사례 : Storage 표준안구성도 ASM > Meta LUN DAE 間 Mirror > DAE 內 Mirror Stripe Size = 64K (vs 32K, 128K) Read : Write Cache Ratio = 2:8 (vs 6:4, 7:3, 8:2) 8 LUN (ASM VOL) DGDATA1 영역 LUN (ASM VOL) DGBACKUP1 영역 LUN (ASM VOL) 여유영역 LUN (ASM VOL) 미사용영역 HS 7 OCR1 (200M) LUN0 (ASM VOL01) LUN1 (ASM VOL02) LUN2 (ASM VOL03) LUN3 (ASM VOL04) LUN4 (ASM VOL05) LUN5 (ASM VOL06) LUN6 (ASM VOL07) LUN7 (ASM VOL08) VOTE1 (200M) LUN8 (ASM VOL09) LUN9 (ASM VOL10) LUN10 (ASM VOL11) LUN11 (ASM VOL12) LUN12 (ASM VOL13) LUN13 (ASM VOL14) LUN14 (ASM VOL15) LUN15 (ASM VOL16) HOT SPARE HS MIRROR OCR2 (200M) LUN16 (ASM VOL17) LUN17 (ASM VOL18) LUN18 (ASM VOL19) LUN19 (ASM VOL20) LUN20 (ASM VOL21) LUN21 (ASM VOL22) LUN22 (ASM VOL23) LUN23 (ASM VOL24) VOTE2 (200M) LUN24 (ASM VOL25) LUN25 (ASM VOL26) LUN26 (ASM VOL27) LUN27 (ASM VOL28) LUN28 (ASM VOL29) LUN29 (ASM VOL30) LUN30 (ASM VOL31) LUN31 (ASM VOL32) HOT SPARE 6 5 LUN0~LUN7 (MIRROR) LUN16~LUN23 (MIRROR) HS LUN8~LUN15 (MIRROR) MIRROR HS LUN24~LUN32 (MIRROR) 47 Storage 구성사례 : Storage 표준안구성도 (Cont.) 4 LUN (ASM VOL) DGDATA1 영역 LUN (ASM VOL) DGBACKUP1 영역 LUN (ASM VOL) 여유영역 LUN (ASM VOL) 미사용영역 HS OCR1 (200M) LUN0 (ASM VOL01) LUN1 (ASM VOL02) LUN2 (ASM VOL03) LUN3 (ASM VOL04) LUN4 (ASM VOL05) LUN5 (ASM VOL06) LUN6 (ASM VOL07) LUN7 (ASM VOL08) VOTE1 (200M) LUN8 (ASM VOL09) LUN9 (ASM VOL10) LUN10 (ASM VOL11) LUN11 (ASM VOL12) LUN12 (ASM VOL13) LUN13 (ASM VOL14) LUN14 (ASM VOL15) LUN15 (ASM VOL16) HOT SPARE 3 HS MIRROR OCR2 (200M) LUN16 (ASM VOL17) LUN17 (ASM VOL18) LUN18 (ASM VOL19) LUN19 (ASM VOL20) LUN20 (ASM VOL21) LUN21 (ASM VOL22) LUN22 (ASM VOL23) LUN23 (ASM VOL24) VOTE2 (200M) LUN24 (ASM VOL25) LUN25 (ASM VOL26) LUN26 (ASM VOL27) LUN27 (ASM VOL28) LUN28 (ASM VOL29) LUN29 (ASM VOL30) LUN30 (ASM VOL31) LUN31 (ASM VOL32) HOT SPARE 2 1 LUN0~LUN7 (MIRROR) LUN16~LUN23 (MIRROR) LUN8~LUN15 (MIRROR) LUN24~LUN32 (MIRROR) HS HS MIRROR 0 Storage OS CAFE5DB RMAN Backup (RAID 5) CAFE6DB RMAN Backup 2008 Innovative Technology (RAID 5) Seminar 48

25 Storage 구성사례 : LUN 구성 & DB Sizing 구분내용 Size LUN 수 용도및 Size (Single 4 대 Consolidation) LUN MAPPING OCR, Vote OCR, Vote 각 200MB OCR, Vote Device ASM Disk Group DGDATA1 1.2 TB 12EA Real Data (100G/ 년증가 ) DGBACKUP1 1.2 TB 12 EA RMAN (280G=Full 140G/ 일 *2 일 ) or (400G=Full+Inc. 200G/ 주 *2 주 ) Archive File(22G=5.5G/ 일 *2 일 *2Nodes) Data Pump(300G=75G/ 일 *2 일 *2Nodes) LUN1~3,9~11, 17~19,25~27 LUN5~7,13~15, 21~23,29~31 여유분 400GB 4EA 추가여유분확보 LUN 4,12,20,28 미사용 400GB 4EA OCR, Vote 사용영역, 향후사용고려 LUN 0,8,16,24 계 3.2 TB 32EA 구분내용 Size Disk 수 용도 (Single 4대 Consolidation) 비고 OS DAE File System RMAN 2중화 400 GB RMAN 2중화 (FTP) Node1 Arch 50 GB 5EA Node1 Archive File (11G=5.5G/ 일 *2일*1Node) Raid5 Node2 Arch 50 GB Archive File (11G=5.5G/ 일 *2일*1Node) Node2 49 ASM Striping with RAID 0+1 Online Redo Undo Temp DB Files Highest Fastest 20% 40% Slow Slowest 20% 20% Archived logs Dump destinations 50

26 Switch 가없는경우 Direct (Cross) 연결 [Node-1] /oracle> oifcfg getif ce global public ce global cluster_interconnect ce global cluster_interconnect [Node-1] /oracle> more $ORACLE_HOME/dbs/init<SID1>.ora Node-1.cluster_interconnects = : Node-2.cluster_interconnects = : ** ce8 NIC Cable 절체시 ㄱ. All Instance Hang-Up & No New Connections ㄴ. CRSD Daemon 이 Misscount 동안 Waiting ㄷ. Node-2 를 Cluster Member 에서 Evict ㄹ. Oprocd 의한 Node-2 의 I/O Fencing ㅁ. Misscount 값을초과한경우 System Rebooting 51 Switch 가있는경우 Active / Standby Solution ** OS 의 NIC 이중화 Solution 을통하여 NIC A-S 구성시.. SUN-IPMP, IBM-EtherChannel, HP-APA, Linux Bonding, Win2K Teaming [Node-1] /oracle> oifcfg delif global ce8 ; oifcfg delif global ce9 [Node-1] /oracle> oifcfg getif ce global public [Node-1] /oracle> more $ORACLE_HOME/dbs/init<SID1>.ora Node-1.cluster_interconnects = Node-2.cluster_interconnects = ** ce8 NIC Cable 절체시 ㄱ. OS 에서 Take-Over IP 를 ce9:1 에서기동시킴ㄴ. All Instance 정상처리및신규 Session 정상처리ㄷ. ce8 Cabling 시에 IP 는 Auto Fail-Back 됨. 52

27 Switch 가있는경우 Active / Active Solution ** OS 의 NIC 이중화 Solution 을통하여 NIC A-A 구성시.. SUN-Trunking, IBM-EtherChannel, HP-APA, Linux Bonding, Win2K Teaming [Node-1] /oracle> oifcfg getif ce global public ce global cluster_interconnect [Node-1] /oracle> more $ORACLE_HOME/dbs/init<SID1>.ora #Node-1.cluster_interconnects = #Node-2.cluster_interconnects = ** ce8 NIC Cable 절체시 ㄱ. ce8 + ce9 ce7 로이중화되어있으므로 ce9 를사용함ㄴ. All Instance 정상처리및신규 Session 정상처리 53 VIP Failover (no bonding), Cluster-Interconnect Bonding eth0 Switch A Public eth0 eth1 Switch B Public eth1 eth2 eth3 eth5 Switch C Private eth5 eth2 eth3 Switch D Private srvctl modify nodeapps -n racnode1 -A racnode1_vip/ /eth0 eth1 oifcfg getif To check which interfaces are configured as public or private eth global public eth global public eth global cluster_interconnect 54

28 Cluster-Interconnect 확인하기 1. x$ksxpia SQL> select * from x$ksxpia; ADDR INDX INST_ID PUB_KSXPIA PICKED_KSXPIA NAME_KSXPIA IP_KSXPIA FFFFFFFF7B N CI ce FFFFFFFF7B N CI ce OCR :: Oracle Clusterware -- OSD :: Operating System Dependant -- CI :: Indicate the cluster_interconnects in init.ora 2. gv$cluster_interconnects SQL> select * from gv$cluster_interconnects; INST_ID NAME IP_ADDRESS IS_ SOURCE ce NO cluster_interconnects parameter 2 ce NO cluster_interconnects parameter 1 ce NO cluster_interconnects parameter 1 ce NO cluster_interconnects parameter 3. alert.log Cluster communication is configured to use the following interface(s) for this instance Thu Aug 9 17:59: Oracle instance running with ODM: VERITAS ODM Library, Version Cluster-Interconnect 확인하기 4. ipc dump SQL> oradebug setmypid SQL> oradebug ipc SQL> oradebug tracefile_name; $ more /oracle/product/admin/rac/udump/ora_rac_18392.trc admno 0x7cfa4d28 admport: SSKGXPT 0x676ad00 flags SSKGXPT_READPENDING socket no 8 IP UDP context timestamp 0 5. oifcfg [Node-1] /oracle> oifcfg getif ce global public ce global cluster_interconnect 6. ocrdump [Node-1] /oracle> ocrdump [Node-1] /oracle> view OCRDUMPFILE... [SYSTEM.css.node_numbers.node1.privatename] ORATEXT : rac_node1-priv SECURITY : {USER_PERMISSION : PROCR_ALL_ACCESS, GROUP_PERMISSION : PROCR_READ, OTHER_PERMISSION : PROCR_READ, USER_NAME : root, GROUP_NAME : root} 56

29 Cluster-Interconnects 성능향상 InfiniBand Cluster Performance Benefits Network Level Cluster Performance for Oracle RAC Block Transfer/sec (16KB) InfiniBand GigE 0 2-node cluster 4-node cluster Source: Oracle Corporation and Topspin on dual Xeon processor nodes InfiniBand delivers 2-3x higher block transfers/sec as compared to GigE 57 RAC Rolling Upgrade Preferred solution for System and hardware upgrades Operating system upgrades Qualified one-off patches CRS upgrades 58

30 Rolling Patch Upgrade using RAC 59 RAC 에서의 Opatch 사용 기본적으로임시 patch 적용시는 instance 를 down 한후적용해야하므로, 패치적용시는서비스를할수없게된다. RAC 에서는몇가지 argument 를사용하여 down-time 을최소화할수있다. 대략적으로 RAC 에서패치가적용되는방법을적어보면다음과같다. If ( 사용자가패치적용방법을 minimize_downtime 으로설정 ) patching mechanism = Minimize Downtime else if ( 패치가롤링패치를지원 ) patching mechanism = Rolling else patching mechanism = All-Node 주의 ) CFS 등의공유파일시스템에오라클제품을설치하여여러노드에서공유하는경우에는 minimize_downtime 과롤링패치가적용되지않는다. 60

31 RAC 에서의 Opatch 사용 1. All-Node 일반적인 database 에 patch 를적용하는것처럼, 모든 instance 를 down 한후, 한 node 씩순차적으로진행한다. ORACLE_HOME 을여러 node 에서공유하는경우. 2. Minimize_downtime Minimize_downtime 은 RAC 운영시 downtime 을최소화하기위해사용된다. 2node RAC 의경우를예로 patch 절차를알아보자. 1. 먼저 local node 의 instance 를 shutdown 한다. opatch apply -minimize_downtime 명령으로 patch 를시작한다. 2. Is this node ready for updating? 질문에 Yes 로답하면, local node 에 patch 가적용된다. 3. pach 적용후다음에적용할 node 명을물어본다. 4. 해당 node 명을입력하면, 이 node 의 instance 를 shutdown 하도록요청한다. 5. 요청받은 node 의 instance 는 Shutdown immediate 로 shutdown 한후, shutdown 이완료되면이미 patch 가적용된 node 의 instance 가 start 한다. 6. 두번째 node 에 patch 를적용한다. 7. inventory 정보가 update 된다. 8. patch 가완료되면, 두번째 instance 도 start 한다. Minimize_downtime argument 를사용해도모든 instance 가 down 되는단계는있지만, 5 번단계에서만해당되므로, downtime 을최소화할수있다. 61 RAC 에서의 Opatch 사용 3. Rolling Patch Rolling Patch 가 Minimize_downtime 과틀린점은 downtime 이전혀없다는것이다. 1. 먼저 instance1 을 shutdown 한다. 이단계에서 instance2 는서비스중이다. 2. node1 에서 opatch apply 명령으로 patch 를적용한다. 3. node1 에 patch 가적용되었으면, instance1 을시동하라는메시지를받는다. 그리고다음에적용할 node 를입력하도록메시지를받는다. 4. instance2 를 shutdown 한다 번세션에서계속이어서 node2 에 patch 를적용한다. 6. patch 적용후 instance2 를시동한다. Rolling Patch 는모든 patch 가다가능한것은아니고, Rolling Patch 를지원하도록설계된 patch 만가능하다. Rolling Patch 가가능한지여부는 opatch query -is_rolling_patch 명령으로확인할수있다. 예 1) [cafe4db1(oracle):/oracle/product/10.2.0/patch> opatch query -is_rolling_patch Patch is a rolling patch: true 예 2) [cafe4db1(oracle):/oracle/product/10.2.0/patch> opatch query -all grep rolling Patch is a rolling patch: true 62

32 RAC 에서의 Opatch 사용 Rolling Patch 시고려사항 Server 의 Service 모든 Node 에구성된경우 Node 별로 Perferred, Available 로구성된경우 (3Node 이상도동일 ) 한 Node 에만구성된경우 Client tnsnames.ora 의 ADDRESS = 모든 Node 가명시된경우 (Failover=on 과무관 ) 특정 Node 만명시된경우 Service 가구성된모든 Node 가명시된경우 (Failover=on 과무관 ) Perferred Node 만명시된경우 한 Node 만명시 Service & VIP 를 Down 한다. 특정 Node 가 opatch target 일경우, 1. tnsnames.ora 에가용한 Node 를추가하거나, 2. VIP 를 takeover 하고, takeover 된 Node 의 listener.ora 에새로운 VIP 를추가하여 listener 를 restart 하거나, 3. VIP 를 takeover 하고, takeover 된 Node 에서추가된 VIP 로 Listener 를추가로생성한다. Servcie & VIP 를 Down 한다. Service & VIP 1. tnsnames.ora 에 available Node 를추가하거나, 2. VIP 와 Service 를 takeover 하고, takeover 된 Node 의 listener.ora 에새로운 VIP 를추가하여 listener 를 restart 하거나, 3. VIP 와 Service 를 takeover 하고, takeover 된 Node 에서추가된 VIP 로 Listener 를추가로생성한다. Service를 modify 하여새로운 Node에 Service를추가후, 1. tnsnames.ora에새로운 Node를추가하거나, 2. VIP를 takeover 하고, takeover 된 Node의 listener.ora에새로운 VIP를추가하여 listener를 restart 하거나, 3. VIP를 takeover 하고, takeover 된 Node에서추가된 VIP로 Listener를추가로생성한다. 63 SQL Apply Rolling Database Upgrades 64

33 RAC Internals 65 Cache Fusion (GCS) by juliandyke Read with No Transfer N S 1 Request shared resource Resource Master Instance 2 2 Request granted Instance 3 Instance 1 4 Block returned 3 5 Resource status Read request Instance 4 Instance 2 requests current read on block 1318 STOP 66

34 Cache Fusion (GCS) by juliandyke Read to Write Transfer 3 N Block and resource status X N S N 1318 Instance Transfer block to Instance 1 for exclusive access Request exclusive resource Resource Master Instance Instance 1 4 Resource status Instance 4 Instance 1 requests exclusive read on block 1318 STOP 67 Cache Fusion (GCS) by juliandyke Write to Write Transfer N S N 1318 Instance 2 2 Transfer block to Instance 4 in exclusive mode Resource Master Instance 3 1 Request block in exclusive mode N X N Instance 1 3 Resource status Block and resource status 4 N X Instance 4 Instance 4 requests exclusive read on block 1318 Note that Instance 1 will create a past image (PI) of the dirty block STOP 68

35 Cache Fusion (GCS) by juliandyke Past Image Buffer Cache UPDATE t SET c1 = 1324; 1325; 1326; 1327; 1328; COMMIT; Buffer Cache UPDATE t1 SET c1 = 1329; COMMIT; Instance 1 Instance Redo Log 1 Block DBWR Assume Undo/redo Block GCS Block Instance Undo/Redo 42 has 42 transfers is table t1 contains a single is subsequently not 1 updates row is 1 needs updated makes is 1324 read must 2 written applied Crashes block in from recovery perform column block changes to from buffer 42 disk written 42 to Contents to Instance recovery back block a Past of to Redo 42 1 buffer disk cache uses 1 Image for back to Log Instance by cache Past to block 12 DBWR disk Image are 2yet lost Redo Log 2 STOP 69 Cache Fusion (GCS) by juliandyke Write to Read Transfer N S N S 1318 Instance Request block in shared mode Resource status Resource Master Instance 3 2 Transfer block to Instance 1 in shared mode N X N N X S Block and resource status 1323 Instance 1 Instance 2 requests current read on block 1318 Instance 4 Note that in recent versions _fairness_threshold is used to avoid unnecessary lock conversions STOP 70

36 GC Read Mechanism by juliandyke GC Read Committted Block Committed Block - Data Block on disk Session15 LMS0 Session27 22:10 22:19 UPDATE score SET runs = 200 WHERE team = 'ENG'; ENG 205 AUS 99 ENG AUS 99 ENG 199 ENG 200 ENG 204 UPDATE score SET runs = 204 WHERE team = 'ENG'; UPDATE score SET runs = 205 WHERE team = 'ENG'; SELECT runs FROM score WHERE team = 'ENG'; ENG Block 42 Undo Block COMMIT; AUS 99 STOP Instance 1 Instance 2 71 GC Read Mechanism by juliandyke GC Read Committted Block Committed Block - Data Block in buffer cache Session15 LMS0 Session27 22:10 22:19 UPDATE score SET runs = 200 WHERE team = 'ENG'; ENG 205 AUS 99 ENG AUS 99 ENG 199 ENG 200 ENG 204 UPDATE score SET runs = 204 WHERE team = 'ENG'; UPDATE score SET runs = 205 WHERE team = 'ENG'; SELECT runs FROM score WHERE team = 'ENG'; ENG 199 Block 42 Undo Block COMMIT; AUS 99 STOP Instance 1 Instance 2 72

37 GC Read Mechanism by juliandyke GC Read Uncommittted Block Uncommitted changes MUST be flushed to the redo log before the LMS process can ship a consistent block to another instance Reading process must wait until redo log changes have been written to redo log by LMS process Bad for standard RAC databases Reads must wait for redo log writes Worse for extended / stretch RAC clusters Increased latency of cross site disk communications 73 GC Read Mechanism by juliandyke GC Read Uncommittted Block For each block on which a consistent read is performed, a redo log flush must first be performed Number of redo log flushes is recorded in the FLUSHES column of V$CR_BLOCK_SERVER Redo log flush time is recorded in the gc cr block flush time statistic for the LMS process will increase time taken to serve consistent block will increase time taken to perform consistent read If LMS processes become very busy, consistent reads will experience high wait times e.g. for a full table scan gc cr multi block request 74

38 GC Read Mechanism by juliandyke GC Read Uncommittted Block Uncommitted Block - Data Block in buffer cache Session15 LMS0 Session27 22:10 UPDATE score SET runs = 200 WHERE team = 'ENG'; ENG 199 AUS 99 ENG AUS 99 ENG AUS 99 ENG 199 ENG 200 ENG 204 UPDATE score SET runs = 204 WHERE team = 'ENG'; UPDATE score SET runs = 205 WHERE team = 'ENG'; SELECT runs FROM score WHERE team = 'ENG'; ENG 199 Block 42 Copy Block 42 Undo Block AUS 99 STOP Instance 1 Instance 2 75 GC Read Mechanism by juliandyke GC Read Uncommittted Block Uncommitted Block - Data Block on disk Session15 LMS0 Session27 22:10 UPDATE score SET runs = 200 WHERE team = 'ENG'; ENG AUS 99 ENG 199 ENG 200 ENG 204 ENG AUS 99 ENG 199 ENG 200 ENG 204 UPDATE score SET runs = 204 WHERE team = 'ENG'; UPDATE score SET runs = 205 WHERE team = 'ENG'; SELECT runs FROM score WHERE team = 'ENG'; ENG Block 42 Undo Block STOP AUS 99 Instance 1 Instance 2 SEE SLIDE NOTES FOR ADDITIONAL INFORMATION 76

39 Load Balancing & Fail Over 77 Possible Service Configuration with RAC 동일한클러스터내의인스턴스들을사용자가정의한 Service로묶어관리 사용자가직접인스턴스의조합및특성 ( 장애대책 ) 을생성, 변경가능 Active/Spare RAC01 RAC02 RAC03 AP AP GL GL Active/Symmetric RAC01 RAC02 RAC03 Active/Asymmetric RAC01 RAC02 RAC03 AP AP AP AP AP AP GL GL GL GL GL GL 78

40 Creating Services with DBCA DBCA configures both the CRS resources and the Net Service entries for each service. 79 Creating Services with SRVCTL $ srvctl add service d PROD s GL -r RAC02 -a RAC01 $ srvctl add service d PROD s AP r RAC01 -a RAC02 RAC02 AP GL AP GL RAC01 80

41 Preferred and Available Instances $ srvctl add service d PROD s ERP \ r RAC01,RAC02 -a RAC03,RAC RAC01 RAC02 RAC03 RAC04 ERP ERP ERP ERP RAC01 RAC02 RAC03 RAC04 ERP ERP ERP ERP 4 RAC01 RAC02 RAC03 RAC04 ERP ERP ERP ERP 3 RAC01 RAC02 RAC03 RAC04 ERP ERP ERP ERP 81 Managing Services Use EM or SRVCTL to manage services: Start: Allow connections Stop: Prevent connections Enable: Allow automatic restart and redistribution Disable: Prevent starting and automatic restart Relocate: Temporarily change instances on which services run Modify: Modify preferred and available instances Get status information Use the DBCA or SRVCTL to: Add or remove Modify services 82

42 Managing Services with EM 83 Managing Services: Example Start a named service on all preferred instances. $ srvctl start service d PROD s AP Stop a service on selected instances. $ srvctl stop service d PROD s AP i RAC03,RAC04 Disable a service at a named instance. $ srvctl disable service d PROD s AP i RAC04 Set an available instance as a preferred instance. $ srvctl modify service d PROD s AP -i RAC05 r 84

43 Load Balancing 85 Client Side Load Balancing Client 1 Client 2 Client 3 Client 4 Network Clients connect to instance using random method Uses address list in tnsnames lsnr1 Node 1 rac1 instance rac Database Client n lsnr2 Node 2 rac2 instance RAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(port = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(port = 1521)) (LOAD_BALANCE = ON) (FAILOVER = ON) (CONNECT_DATA = (SERVICE_NAME = rac) (failover_mode = (type=select)(method=basic)) ) ) 86

44 Listener Load Balancing Client 1 Client 2 Client 3 Client 4 Network Listeners balance load using CPU / user load lsnr1 rac1 instance Node 1 rac Database Client n lsnr2 rac2 instance Node 2 87 Listener Load Balancing Load balancing facilities to allow client connections to be distributed among multiple listeners, dispatchers, instances and nodes Balancing the number of active connections No single component becomes overloaded. PMON sends updated service_register loading information to the listener, i.e. load, maxload, instance_load, instance_maxload. init.ora Parameters - Service_names Oracle Service is a logical way to represent an application: Example: Sales Oracle Service and HR Oracle Service - Instance_name - Dispatchers - Local_listener - Remote_listener 88

45 Server-side Connect-time time Load Balancing ERP = (DESCRIPTION=(LOAD_BALANCE=ON)(FAILOVER=ON) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=node1vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=node2vip)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=ERP))) 6 4 Listener 5 PMON 1 Node1 ERP started on both instances *.REMOTE_LISTENERS=RACDB_LISTENERS Listener PMON RACDB_LISTENERS= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=node1vip)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=node2vip)(PORT=1521))) Node2 89 Listener Load Balancing 에서 Listener 확인 [cafe4db1(oracle):/oracle/product/10.2.0> lsnrctl serv [cafe4db2(oracle):/oracle/product/10.2.0> lsnrctl serv LSNRCTL for Linux: Version Production on 09-MAY :16:49 LSNRCTL for Linux: Version Production on 09-MAY :20:02 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Services Summary... Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) Service "CAFE4DB" has 2 instance(s). Services Summary... Instance "CAFE4DB1", status READY, has 2 handler(s) for this service... Service "CAFE4DB" has 2 instance(s). Handler(s): Instance "CAFE4DB1", status READY, has 1 handler(s) for this service... "DEDICATED" established:0 refused:0 state:ready Handler(s): REMOTE SERVER "DEDICATED" established:0 refused:0 state:ready (ADDRESS=(PROTOCOL=TCP)(HOST=cafe4db1-vip)(PORT=1521)) REMOTE SERVER "DEDICATED" established:0 refused:0 state:ready (ADDRESS=(PROTOCOL=TCP)(HOST=cafe4db1-vip)(PORT=1521)) LOCAL SERVER Instance "CAFE4DB2", status READY, has 2 handler(s) for this service... Instance "CAFE4DB2", status READY, has 1 handler(s) for this service... Handler(s): Handler(s): "DEDICATED" established:0 refused:0 state:ready "DEDICATED" established:0 refused:0 state:ready REMOTE SERVER REMOTE SERVER (ADDRESS=(PROTOCOL=TCP)(HOST=cafe4db2-vip)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=cafe4db2-vip)(PORT=1521)) "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER 90

46 Transparent Application Failover : Overview TAF Basic TAF Preconnect 2 Application 2 Application OCI Library 6 OCI Library 6 4 Net Services 4 Net Services AP 3 8 ERP AP ERP ERP_PRECONNECT 1 91 TAF Configuration:Example N1VIP (I1) AP ERP INTERCONNECT N2VIP (I2) AP ERP ERP_PRECONNECT hr/hr@ap oe/oe@erp 92

47 TAF Configuration:Example N1VIP (I1) AP ERP INTERCONNECT N2VIP (I2) AP ERP ERP_PRECONNECT 93 TAF Basic Configuration:Example $ srvctl add service -d RACDB -s AP -r I1,I2 -P BASIC $ srvctl start service -d RACDB -s AP AP = (DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = AP) (FAILOVER_MODE = (TYPE=SESSION) (METHOD=BASIC) (RETRIES=180) (DELAY=5)))) 94

48 TAF Preconnect Configuration:Example $ srvctl add service -d RACDB -s ERP -r I1 a I2 P PRECONNECT $ srvctl start service -d RACDB -s ERP ERP = (DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = ERP) (FAILOVER_MODE = (BACKUP=ERP_PRECONNECT) (TYPE=SESSION)(METHOD=PRECONNECT)))) ERP_PRECONNECT = (DESCRIPTION =(FAILOVER=ON)(LOAD_BALANCE=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=N1VIP)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=N2VIP)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = ERP_PRECONNECT))) 95 TAF Verification SELECT machine, failover_method, failover_type, failed_over, service_name, COUNT(*) FROM v$session GROUP BY machine, failover_method, failover_type, failed_over, service_name; 1st node 2nd node MACHINE FAILOVER_M FAILOVER_T FAI SERVICE_N COUNT(*) node1 BASIC SESSION NO AP 1 node1 PRECONNECT SESSION NO ERP 1 MACHINE FAILOVER_M FAILOVER_T FAI SERVICE_N COUNT(*) node2 PRECONNECT SESSION NO ERP_PRECO 1 2nd node after MACHINE FAILOVER_M FAILOVER_T FAI SERVICE_N COUNT(*) node2 BASIC SESSION YES AP 1 node2 PRECONNECT SESSION YES ERP_PRECO 1 96

49 Component Effect Table Example of the effect certain components have. Result OK OK OK Down OK Down OK OK OK Down Down Down Down Component CPU Panic / Crash Memory Crash Inter-Connect Inter-Connect Switch OS Failure / Freeze Cluster Manager S/W DB Instance Crash Control File (Corrupt / Loss) Redo log file Loss Data File Human Error Dropped Object DB Software Bug Effect of Failure Node Failed, other node still active Node Failed, other node still active With dual Inter-connects, OK Nodes can not communicate Node Failed, other node still active Cluster freezes, all nodes go down Instance running on other node provides database service Multiplexed controlfile will be used Multiplexed redo file Requires Media Recovery Depends on type of Mistakes DB is available but application stall DB may stall on all instances 97 Case 별 Fail-Over Test eth0 eth0 eth2 eth3 eth2 eth3 bond0 bond0 bond0 bond0 eth2 eth3 eth2 eth3 eth0 DL585(2CPU Quad core) Memory 16G 2Nodes RAC * 2Set eth0 NAME OS Kernel Oracle redhat as 4( 이상 x86_64) Value EMC CX GB*14*8box*2 98

50 Case 별 Fail-Over Test TAF Fail Instance Kill (kill -9 ~) Server Process Kill (os level) Session Kill (alter system kill session) 항목 background process kill 시간 select가멈추는시간 alert*.log에 abort 메시지가남는시간 session이 Failover된시간 (gv$session) select가다시시작되는시간 instance re-start 시간 server process kill 시간 select가멈추는시간 alert*.log에 abort 메시지가남는시간 session이 Failover된시간 (gv$session) select가다시시작되는시간 instance re-start 시간 serssion kill 시간 select가멈추는시간 alert*.log에 abort 메시지가남는시간 session이 Failover된시간 (gv$session) select가다시시작되는시간 instance re-start 시간 Type SELECT 12:08:09 12:08:09 12:08:12 13:52:29 해당사항없음 13:52:30 1 초소요 13:53:26 53 초소요 해당사항없음 16:52:10 Session kill 과동시에 해당사항없음 Session kill 과동시에 Select 안됨. 해당사항없음 Method BASIC instance kill 과동시에 12:08:49 40 초소요 12:09:02 53 초소요 Process kill 과동시에 Type SELECT 17:43:24 17:43:25 17:44:00 36 초소요 17:44:20 56 초소요 17:43:27 19:00:39 Process kill 과동시에 해당사항없음 19:00:39 0 초소요 19:01:01 21 초소요 해당사항없음 20:55:55 Session kill 과동시에 해당사항없음 Failover 안됨. Select 안됨. 해당사항없음 Method PRE CONNECT instance kill 과동시에 TAF Type SESSION 21:14:23 instance kill 과동시에 21:14:23 21:14:59 36 초소요 Select 안됨. 21:14:26 21:59:08 Process kill 과동시에 해당사항없음 21:59:20 12 초소요 Select 안되나, session 은바로 reconnect 해당사항없음 22:19:56 Session kill 과동시에 해당사항없음 Failover 안됨. Select 안되나, 곧바로새로운접속을함. 해당사항없음 Method BASIC Type SESSION 22:43:53 instance kill 과동시에 22:43:54 22:44:21 28 초소요 Select 안됨. 22:43:57 23:25:00 Process kill 과동시에 해당사항없음 23:25:05 5 초소요 Select 안되나, session 은바로 reconnect 해당사항없음 23:53:41 Session kill 과동시에 해당사항없음 Failover 안됨. Select 안되며, session 은곧바로 Failover 해당사항없음 Method PRE CONNECT 99 Case 별 Fail-Over Test Action 시간 alert*.log상에감지시간 VIP Failover 시간 Session Failover 시간 (gv$session) SYSTEM HALT (POWER-OFF) 15:44:00 15:45:04 (1분 4초 ) 상대 Node 임 15:45:04 (1분 4초 ) 15:45:43 (1분 43초 ) Public Line 절체 2:10:17 Null 2:11:09 2:12:00 POWER-OFF 시 Public Line 절체시 100

51 db01 halt시 instance recovery (FAST_START_MTTR_TARGET) 101 db01 halt시 instance recovery (FAST_START_MTTR_TARGET) COUNT 7000 db01 halt COUNT

52 db01 halt시 instance recovery (FAST_START_MTTR_TARGET) FAST_START_MTTR_TARGET=10 FAST_START_MTTR_TARGET= Case별 Fail-Over Test Interconnect 절체 시 구분 1차 2차 항목 시간 Interconnect 1 Line 절체 시간 (Node1) 19:03:00 Interconnect 2 Line 절체 시간 19:04:04 Reboot 시간 (Node2 Reboot) 19:05:04 Interconnect 1 Line 절체 시간 (Node1) 19:23:00 Interconnect 1 Line 연결 시간 19:25:00 Interconnect 1 Line 절체 시간 (다른 Line) 19:27:00 Interconnect 2 Line 절체 시간 19:29:00 Reboot 시간 (Node1 Reboot) 19:29:23 104

53 db01의 db01의 interconnect etherchannel(en8) 중 한 line(en3)을 line(en3)을 절체 후 원복하면 원복하면 DB Servers ether-channel 구성 ent0 : Admin Network ent1/ent2 : ent7 (Public Network) ent3/ent4 : ent8 (RAC Interconnect) ent5/ent6 : ent9 (HACMT Heartbeat) 105 db01의 db01의 interconnect etherchannel(en8) 중 한 line(en3)을 line(en3)을 절체 후 원복하면 원복하면 COUNT 7000 db01 interconnect 복구(3차) 6000 db01 interconnect 복구(2차) COUNT

54 Spanning Tree Port States Spanning tree transitions each port through several different states. 107 Describing PortFast 108

55 Configuring PortFast Configuring spanning-tree portfast (interface command) or spanning-tree portfast default (global command) enables PortFast on all nontrunking ports Verifying show running-config interface fastethernet 1/1 109 Configuring PortFast 후 110

56 Oracle Client 에서 Server 장애를빨리감지하려면 (enable=broken) Oracle RAC TAF(Transparent Application Failover) 환경등에서인스턴스장애를 ( 정확히서버장애 ) 빨리감지하려는경우... 서버다운등으로 TCP 세션에장애가발생하면재수가없는 (?) 경우, OS TCP 스택에서 TCP 세션타임아웃이발생할때까지 hang 비슷하게걸릴수있다. The 2 main options available to cover this scenario are: - Code the client to wait only a set period of time for a response before assuming there is a problem. - Ensure the client is using tcp_keepidle and tcp_keepintvl with reasonably short duration so that the network level notices that the remote node is missing and reports an error to the NET layer. Keep-alive is a TCP/IP mechanism that allows a connection to detect if the partner has unexpectedly died. It is NOT an Oracle mechanism, although Oracle can request for keepalive to be enabled or disabled for a given connection. By default SQLNET connections do not enable keep-alive for TCP connections. However, it is possible to enable this by adding ENABLE=BROKEN in the tnsnames.ora file. Adding this parameter turns on a TCP level facility which can detect the loss of a server. If the server dies then keep-alive will notice this and signal an error to Oracle Net code. TAF notices this error and performs fail-over as if the remote instance had been aborted. Be aware that changing TCP/IP parameters may affect more than Oracle connections. Seek advices from your network administrator before changing any AIX TCP/IP parameters. 예제 ) tnsnames.ora ETG_GENERAL = (DESCRIPTION_LIST = (enable=broken) (LOAD_BALANCE = OFF) (FAILOVER = ON) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = ON) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(port = 1523)) (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(port = 1523)) ) (CONNECT_DATA = (SERVICE_NAME=ETG) (FAILOVER_MODE=(TYPE=SELECT)(backup=ETG_BACKUP)(METHOD=BASIC)(RETRIES=10)))) 111 Oracle Client 에서 Server 장애를빨리감지하려면 (enable=broken) 사례 A 생명 DBMS Server 의전원 Turn Off 시에 DBMS 의 Fail over 의기능이느리게작동하는현상에대한원인분석과그대처방안에대해아래와같이정리합니다. 1. 현상 시스템구성하나의웹로직인스턴스가두개의오라클데이타베이스에연결되어있고, 이연결은각각의 Connection Pool 을이용하고있으며, 이두개의 Connection Pool 의하나의 Multipool 로묶어서사용하고있다. Connection Pool1 ORACLE 1 MultiPool Connection Pool2 ORACLE 2 장애현상이상황에서하나의 Oracle 이기동되고있는 HW Box 의전원이 OFF 되거나 LAN 선이끊어졌을경우에, MultiPool 에서정상적인 Connection Pool 로 Failover 하는데 9 분정도의시간이소요된다. 전원이 OFF 되지않고, Oracle 이기동되는 HW Box 에서 Oracle Instance 만 Shutdown 이되었을때는바로 Failover 가진행된다. 2. 현상분석 Connection Pooling 의경우, 미리 Network 의 Connection 을연결해놓고, 사용하고있다가, 네트워크케이블을뽑거나전원이꺼진경우에는 Connection Pool 쪽의 socket 은 Oracle 로부터 close signal (FIN) 등을받지못했으므로, ESTABLISH 상태를유지하게된다. 이상황에서 WebLogic 에서어떤 Query 를실행했을때, MultiPool 에서는해당 Connection Pool 을통해서해당 JDBC Connection 에 query 를 send 하고그결과를기다리게된다. < 그림 2. socket wait 상태 Thread dump > Socket 의상태는 ESTABLISH 로모니터링된다. 112

57 Oracle Client 에서 Server 장애를빨리감지하려면 (enable=broken) 사례 3XMTHREADINFO "ExecuteThread: '24' for queue: 'weblogic.kernel.default'" (TID:0x302E8860,sys_thread_t:0x5521FD28, state:r, native ID:0x1D1E) prio=5 4XESTACKTRACE at java.net.socketinputstream.socketread0(native Method) 4XESTACKTRACE at java.net.socketinputstream.read(socketinputstream.java(compiled Code)) 4XESTACKTRACE at oracle.net.ns.packet.receive(unknown Source) 4XESTACKTRACE at oracle.net.ns.datapacket.receive(unknown Source) 4XESTACKTRACE at oracle.net.ns.netinputstream.getnextpacket(unknown Source) 4XESTACKTRACE at oracle.net.ns.netinputstream.read(unknown Source) 4XESTACKTRACE at oracle.net.ns.netinputstream.read(unknown Source) 4XESTACKTRACE at oracle.net.ns.netinputstream.read(unknown Source) 4XESTACKTRACE at oracle.jdbc.ttc7.marengine.unmarshalub1(marengine.java(compiled Code)) 4XESTACKTRACE at oracle.jdbc.ttc7.marengine.unmarshalsb1(marengine.java(compiled Code)) 4XESTACKTRACE at oracle.jdbc.ttc7.oopen.receive(oopen.java:105) 4XESTACKTRACE at oracle.jdbc.ttc7.ttc7protocol.open(ttc7protocol.java:611) 4XESTACKTRACE at oracle.jdbc.driver.oraclestatement.open(oraclestatement.java:575) 4XESTACKTRACE at oracle.jdbc.driver.oraclestatement.doexecutewithtimeout(oraclestatement.java:2803) 4XESTACKTRACE at oracle.jdbc.driver.oraclestatement.execute(oraclestatement.java:942) 4XESTACKTRACE at weblogic.jdbc.common.internal.connectionenv.test(connectionenv.java:665) 4XESTACKTRACE at weblogic.jdbc.common.internal.connectionenv.test(connectionenv.java:386) <socket wait상태의 thread dump > Socket 이 read 하는상황에서상대편서버에대한접속끊어진후에 Network 오류를내어주어야 WebLogic 에서는이내용을파악하고 Multipool failover 를수행하게되는데, JDBC Driver 에서이부분에서 network 에러를내주는게느리기때문에발생하는문제이다. 3. 대처안이문제는 WebLogic 의기능이아니라 JDBC Driver 에서얼마나빨리에러를내주느냐에따라해결할수있는데 Oracle 10G 의 JDBC Driver 를사용하면이문제를해결할수있다. Oracle 10G JDBC Driver 에는 server 쪽으로의 TCP/IP Connection 이살아있는지를체크하기위해서 keep alive 체크를하는 (ENABLE=BROKEN) 이라는옵션이있다. 이옵션을설정하면 AIX 의경우 tcp_keepidle * 0.5 초동안에동작이없는소켓에대해서체크패킷을전송하고 tcp_keepintvl * 0.5 만큼응답이없을경우해당 socket 을 close 하고 fail over 를하게한다. 113 Oracle Client 에서 Server 장애를빨리감지하려면 (enable=broken) 사례 이설정은 WebLogic 의 JDBC URL 을다음과같이설정하면된다. jdbc:oracle:thin:@(description= (ENABLE=BROKEN)(ADDRESS=(PROTOCOL=TCP)(HOST= 서버 ip)(port=1521)) (CONNECT_DATA=(SID= 오라클 SI))) <Oracle JDBC Keep Alive 설정을위한 JDBC Connection URL > 4. 결론 1) Oracle JDBC 10G Driver(ojdbc14.jar) 를다운받아서 CLASSPATH 에추가한다. 2) Connection Pool 설정에서 URL 설정을위의그림 3 같이설정한다. <JDBCConnectionPool DriverName="oracle.jdbc.driver.OracleDriver" InitialCapacity="5" MaxCapacity="5" Name="OracleNotebookPool" Password="{3DES}QY2j7/8TMSk=" Properties="user=system" Targets="myserver" TestConnectionsOnReserve="true" TestStatementTimeout="10" TestTableName="SQL SELECT 1 FROM DUAL" URL="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=TCP)(HOST= )(PORT=1521)) (CONNECT_DATA=(SID=ORA817)))"/> <JDBC Connection Pool 을설정한 config.xml 예제 > 3) Timeout 에필요한적절한 tcp_keepidle 과 tcp_keepintvl time 을설정한다. Multi pool 성능향상을위한설정위와같이하면 Fail over 는잘되지만좀더 Multi Pool 의 fail over 기능을향상시키기위해서다음과같은조치를취할수있다. WebLogic 8.1 Service Pack 3 에는 Multi Pool 에서 Connection 을체크한후에, 그 Connection 이문제가있을경우에는해당 Connection Pool 을 unavailible 로 mark 하고 HealthCheckFrequencySeconds 만큼 unavailable 로 mark 된 connection 에대한테스트를 skip 한다음그후에다시 healthcheck 를해서문제가없으면다시사용한다. HealthChecFrequencySeconds 의 default 값은 300sec 이고 config.xml 의 Connection Pool element 에 HealthChecFrequencySeconds= 90 식으로지정할수있다. 114

58 RAC 환경에서 TCP/IP Tuning 기법 1. 증상네트워크인터페이스카드 (NIC) 또는서버의전원이오프되거나, 네트워크케이블이서버로부터분리될경우, 원래수초이내이루어지는 Transparent Application Failover (TAF) 또는 Connect-time Failover 가수분씩걸리며, Time out 으로인한에러도발생할수있다. 2. 변경사항 NIC 가더이상 TCP/IP 네트워크에존재하지않아, ping 이되지않는다. 3. 원인이현상은, OS 또는 Network layer 의 TCP 프로토콜에서발생하는 time out 에서발생하는사항이다. 이상황은, 오라클에서 failover 가늦게이루어지는것뿐만아니라, 다른툴에서도마찬가지현상이발생하는문제이다. 예를들어, FTP 또는 telnet 에서도발생하는문제이다. 만약동일한현상이발생한다면, Oracle 이아닌다른툴에서도 Fail 이발생한 IP 주소에대한연결을시도해보기바란다. 결과는늦은응답시간이나 Fail 이발생하는상황이나타날것이다. 한편, Sofware Failover 나 Load Balancing 은, 다른툴과마찬가지로하위 layer 의 TCP 가작동할때만기능을수행할수있다. 만약하드웨어에장애가발생한다면소프트웨어역시장애가발생하는것과같은원리이다. 일반적으로, 장애가발생하였지만, NIC 가살아있다면, 다음과같이설명할수있다 : Three-way Connection Model 과같이, 클라이언트는, 호스트의특정포트넘버를통해통신을한다. 만약서비스가가용한상태가아니라서, 해당포트에대해 Listen 을하지않고있다면, 클라이언트는, 정상적인 TCP/IP three-way handshake 를시도하지만, 호스트로부터 RESET 패킷을돌려받아, connect 를더이상시도하지않게된다. ( 수초이내 ). 이경우, 호스트는, 이와같은내용을통신할수있는상태이다. 하지만, NIC 가죽어있거나, TCP/IP 네트워크상에서가용하지않을경우, 클라이언트에서는다음과같은작업들이일어난다 : 클라이언트는, 존재하지않는호스트의서비스와통신을하려할때, 예를들어, 클라이언트가접속하려는 IP 주소에해당하는시스템이존재하지않을때, 해당 IP 주소에서는응답을할주체가존재하지않게된다. connection model 에따라클라이언트는, TCP/IP threeway handshake 를시도하지만응답이돌아오지않게된다. 이경우클라이언트는지정된시간동안대기하게되는데 (OS 에서일반적으로설정가능 ) - 예를들어 200ms, 이경우 SYN 패킷을다시한번보내게된다. 물론이경우에도응답이없을것이다. 이경우, 400ms를대기한후재시도하게된다. 만약그대로응답이없을경우, 800ms 동안대기후재시도, 1600ms 동안재시도하다마침내 3200ms 동안대기해도응답이없을경우클라이언트는연결을일단포기하게된다. 따라서연결을포기하는데까지걸리는시간에총 6.2초가걸리게된다. 하지만이경우에도클라이언트는매 3200마다재시도를하게되는데, 재시도는 magic interval step 까지진행된다. SUN 장비의경우, interval 값이 tcp_ip_abort_cinterval 값으로지정할수있게되어있으며, 기본값은 3분이다. (18000ms). 다른시스템의경우, 기본값을공급업체에문의해확인할수있다. 115 RAC 환경에서 TCP/IP Tuning 기법 4. 가능한해법 Oracle 10g 에서는, TCP timeout 이발생할때까지대기하는것이아니라, 주어진기간을초과할경우 time out 을시킬수있는기능이추가되었다. 10g 부터는, 다음과같은내용을클라이언트또는서버의 sqlnet.ora 파일에지정할수있다. sqlnet.inbound_connect_timeout (server) sqlnet.send_timeout (client and/or server) sqlnet.recv_timeout (client and/or server) 하지만, Oracle 9.2.x.x 이하버전에서는, 하드웨어 failover 방식또는 HA(High Availability) 를사용하는것이권고되며, 예를들어, Virtual IP address(vip) 를사용하여, 하나이상의물리적 IP 주소를참조하는방식을사용할수있다. 하드웨어 load balancing 시스템또는클러스터에서는이와같은설정이가능하다. 필요할경우 OS 공급업체의도움을받을수도있을것이다. 이노트는, 단일데이터베이스와리스너환경에대해주로기술하고있다. RAC 또는 OPS 시스템의경우, 하나이상의리스너를사용하므로, HA 설정이훨씬복잡해지며, RAC 또는 OPS 전문가의도움을필요로할것이다. 이와같은설정은단일 VIP 설정과같이단순하지않다. 간단히말해 virtual IP address 는, 하나또는그이상의실제 IP address 를나타낸다. 만약하나의주소가죽을경우, OS 별 HA 메카니즘은장애를인식하고, 시스템에설정된다음번 IP address 로전환한다. 오라클은이와같은전환이발생하는지여부를다른소프트웨어와마찬가지로알수없으며오직 virtual IP address 만을알고있으므로, 프로그램은계속해서실행되며, 연결역시끊기지않게된다. ( 이기능은 TAF 와는좀다릅니다. connecton time failover 는가능하나, HA s/w 에서는 process 자체에대한 failover 까진지원하지않습니다.) 만약이와같은기능을사용할수없다면, 다른옵션을사용할수도있으나, OS 레벨에서의 TCP 설정을변경하는작업을수반하여, 동일한 TCP/IP 레이어를사용하는다른콤포넌트에부작용을일으킬수도있다. 이옵션은, 시스템관리자로하여금, 노드의하드웨어나 NIC 장애가발생할경우를대비해, OS 레벨의 TCP 파라미터를조정하여 CLINT 가좀더신속하게 fail over 될수있도록요청하는것이다. 작업에도움이되게하고자, Sun 클라이언트, Linux Client 및 Microsoft client 의조정예를기술하였다. 만약다른플랫폼을사용하고잇다면, 플랫폼에적합한설정방법을공급업자로부터권고받아진행할수있을것이다. 116

59 RAC 환경에서 TCP/IP Tuning 기법 5. TCP 설정 1) LINUX 오라클클라이언트가리눅스에설치되어있고, 기본연결되는서버노드가 (9i RAC 환경의임의의플랫폼 ) 전원을끈상태. 이경우, 클라이언트에서에러가발생할때까지걸리는시간이매우느릴수있다. 경우에따라서는클라이언트에서노드 1 이죽어있는사실을가미하고, 살아있는다른노드에연결되는시간이 4 분까지도걸릴수있다. 이와같은환경에서는 telent 세션또한에러가발생할때까지 4 분가량걸릴수있다. Linux 파라미터를다음과같이변경할경우 : net.ipv4.tcp_keepalive_time 3000 net.ipv4.tcp_retries2 5 net.ipv4.tcp_syn_retries 1 Timeout 에걸리는시간이 20 초로단축된다. 2) SUN SOLARIS 다음은 Sun 클라이언트에 ( 테스트목적으로 ) 파라미터값을설정하는예로, 서버의 NIC 장애를클라이언트에서신속하게인식할수있도록한다. 마찬가지로 OS 설정은, 해당시스템에서실행되는모든애플리케이션및툴에영향을미친다는점에유의해야한다. tcp_ip_abort_cinterval = (default is ) tcp_keepalive_interval = (default is ) tcp_ip_abort_interval = (default is ) 3) MICROSOFT 다음은 Microsoft 플랫폼에권고하는클라이언트설정이다. 마찬가지로시스템전체에영향을미치게된다는점에유의할필요가있다 : Microsoft TCP parameter 는다음과같은레지스트리에서변경한다 : HKEY_LOCAL_MACHINE System CurrectControlSet services Tcpip Parameters (TcpTimedWaitDelay, and KeepAlive) 작업을수행하기위해서는 : * 레지스트리를백업받는다. * 레지스트리의 HKEY_LOCAL_MACHINE:SYSTEM (CurrentControlSet:Services:TCPIP:Parameters) 로간다. * 레지스트리에서 KeepAliveTime : REG_DWORD 항목을추가한다. (Note: KeepAlive 지정시대소문자구분필요 ) * timeout 값으로적정한값을지정한다. 예 :( = 2 분 ) * 저장 * 시스템리부팅 117 RAC 환경에서 TCP/IP Tuning 기법 6. 주의 (IMPORTANT NOTE!!!) OS 레벨의변경은, 하나의접근방안으로, 시스템담당자와상의후적용할필요가있다. 이문서에서제시하는 OS TCP/IP parameter 값은하나의예시로, 각시스템환경에적합하지않을수있다. 네트워크또는시스템담당자오상의하여, 영향을판단하고, 3rd party 애플리케이션에미칠수있는영향을면밀하게분석한후값을적용할필요가있다. 테스트는, 순차적으로진행하여, 전체시스템의모든콤포넌트가이상없이적용되는지여부를판단한후, 그설정값을실제환경에적용할필요가있다. 언급한바와같이하드웨어장애처리에대한오라클의공식적인권고사항은, 하드웨어에서제공하는 HA 시스템을사용하는것이다. 상세한사항은하드웨어공급업체와상의할필요가있다. ** Reduce Global Cache load via Maximize TCP receive / send buffers IF db_block_size (8K), db_file_multiblock_read_count (16) then the system using full table scan 128K [ Solaris ] Set the maximum (send or receive) TCP buffer size an application can request: /usr/sbin/ndd -set /dev/tcp tcp_max_buf Set the maximum congestion window: /usr/sbin/ndd -set /dev/tcp tcp_cwnd_max Set the default send and receive buffer sizes: /usr/sbin/ndd -set /dev/tcp tcp_xmit_hiwat /usr/sbin/ndd -set /dev/tcp tcp_recv_hiwat ## See more detailed notes

60 Recommend CSS Parameter misscount = 120 sec (default :: 60 sec) Patch bug for misscount = 120 sec (default :: 60 sec) disktimeout = 200 sec (default) reboottime = 3 sec (default) misscount = 120 sec (default :: 60 sec) disktimeout = 200 sec (default) reboottime = 3 sec (default) misscount = 120 sec (default :: 60 sec) disktimeout = 200 sec (default) reboottime = 3 sec (default) All the above recommendations are for a Linux OS. ## Command crsctl get css misscount disktimeout reboottime crsctl set css misscount 120 crsctl set css disktimeout 200 crsctl set css reboottime 3 ## crsctl get css disktimeout / reboottime will not show, check $ORA_CRS_HOME/log/`hostname`/cssd directory. ## Change mesg_logging_level = 3 in the $ORACLE_HOME/crs/srvm/admin/ocrlog.ini for detailed logging regarding OCR. 119 Parameter 별 Failover misscount (Default 60) tcp_keepalive_time (Default 7200) tcp_keepalive_intvl (Default 75) tcp_syn_retries (Default 5) fast_start_mttr_target (Default 0) enable=broken (Default Null) 접속된세션 Failover 신규세션접속 비고 YES 1:32 1:34 전체 Default YES 약 1: NULL 0: YES 0: NULL 0: NULL 1:33 1: YES 1: YES 약 1: YES 0: NULL 0: YES 1:20 0: YES 0: YES 1:31 0: YES 0:39 0: YES NULL 0:08 0:08 0:21 0:21 instance recovery 가빨라접속이빨라짐 YES 0:08 0: YES 0:07 0: YES 0:08 0: YES 0:08 0:09 120

61 ASM (Automatic Storage Management) 121 Automatic Storage Management Database Tablespace ASM solves management problems of Oracle databases. Segment Extent ASM manages only Oracle files. ASM does not replace existing concepts. 122

62 ASM Benefits Reduces the cost of managing storage Reduces administration complexity Supports RAC Improves performance, scalability, and reliability 123 New Concepts Database ASM disk group Tablespace Data file ASM file Segment ASM disk Extent Oracle block File system file or raw device Allocation unit Physical block 124

63 The Operational Stack TODAY ASM Tables Tables Tablespace Files Tablespace Files File System Logical Vols Disks File System Logical Vols Disk Group ASM The best way to lower mgmt costs is to remove complexity 125 General Architecture Node1 DB Instance SID=sales Group Services tom=ant dick=ant harry=ant Group Services tom=bee dick=bee harry=bee Node2 DB Instance SID=sales DBW0 RBAL ASMB ASMB DB Instance SID=test FG FG DBW0 RBAL ASM Instance SID=ant RBAL ARB0 ARBA ASM Instance SID=bee RBAL ARB0 ARBA FG FG DBW0 RBAL ASMB DBW0 RBAL ASMB DB Instance SID=test ASM disks ASM disks ASM disks ASM disks ASM disks ASM disks ASM Disk group Tom ASM Disk group Dick ASM Disk group Harry 126

64 ASM Disk Groups Is a pool of disks managed as a logical unit Partitions total disk space into uniformsized megabyte units ASM spreads each file evenly across all disks in a disk group Provides coarse(1m) or fine-grain(128k) striping based on file type Disk groups integrated with Oracle Managed Files Administers disk groups, not files Number of disk groups remains constant while number of files and disks continually increases ASM instance Disk group 127 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Disk Group 128

65 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Only move data proportional to storage added Disk Group 129 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Only move data proportional to storage added No need for manual I/O tuning Disk Group 130

66 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Online migration to new storage Disk Group 131 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Online migration to new storage Disk Group 132

67 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Online migration to new storage Disk Group 133 Disk Group Dynamic Rebalancing Automatic online rebalancing whenever storage configuration changes Online migration to new storage Disk Group 134

68 ASM Mirroring Flexible redundancy choices Efficient extent-based mirroring and striping Reduces human errors Up to 3-way mirroring External - Hardware Normal 2way High 3way Can leverage external ASM Disk Group RAID protection 135 ASM Mirroring Normal Redundancy Mirror at extent level Mix primary & mirror extents on each disk 136

69 ASM Mirroring Disk Failure Disk H Fails Mirror at extent level Mix primary & mirror extents on each disk 137 ASM Mirroring Disk Failure Reconstruct Redundancy No hot spare disk required Just spare capacity Failed disk load spread among survivors Maintains balanced I/O load 138

70 ASM Mirroring Disk Failure Drop Disk 139 Migration to ASM File system to ASM RMAN DBMS_FILE_TRANSFER XML FTP ASM to file system RMAN DBMS_FILE_TRANSFER XML FTP ASM to ASM RMAN DBMS_FILE_TRANSFER 140

71 Migration to ASM using RMAN File system to ASM RMAN 1. Shut down your database cleanly 2. Modify your server parameter file to use OMF 3. Edit and execute the following RMAN script: RMAN> STARTUP NOMOUNT; RMAN> RESTORE CONTROLFILE FROM '/u1/c1.ctl'; RMAN> ALTER DATABASE MOUNT; RMAN> BACKUP AS COPY DATABASE FORMAT '+dgroup1'; RMAN> SWITCH DATABASE TO COPY; # Repeat command for all online redo log members... RMAN> SQL "ALTER DATABASE RENAME '/u1/log1' TO '+dgroup1' "; RMAN> ALTER DATABASE OPEN RESETLOGS; # Repeat command for all temporary tablespaces RMAN> SQL "ALTER TABLESPACE temp ADD TEMPFILE"; RMAN> SQL "ALTER DATABASE TEMPFILE '/u1/temp1' DROP"; 141 Migration to ASM using DBMS_FILE_TRANSFER HP-UX (Big) ASM Oracle bit +DG1/oradata/air01.dbf +DG1/oradata/air02.dbf DBMS_FILE_TRANSFER Solaris 5.9 (Big) ASM Oracle bit +DISK1/trans/datafile/air01.dbf +DISK1/trans/datafile/air02.dbf 1. Prepare 1) User Creation : Target 인 Sun 에 SUN> create user air identified by air; SUN> grant connect,resourceto air; 2) Directory Creation(HP, Sun both) HP> create or replace directory HP_DG as +DG1/oradata; HP> grant write on directory HP to air; SUN> create or replace directory SUN_DG as +DISK2/trans/datafile ; SUN> grant write on directory SUN to air; 3) DataLink Creation(DBMS_FILE_TRANSFER 용 ) HP> create public database link DB_LINK connect to air identified by air using SUN ; 2. Transportable Check HP> exec dbms_tts.transport_set_check( AIR,TRUE) HP> select * from sys.transport_set_violations; HP> alter tablespaceair read only; 142

72 Migration to ASM using DBMS_FILE_TRANSFER 3. Export only Meta % exp userid=\'sys/welcom as sysdba\' file=air.dmp log=air.log \ transport_tablespace=y tablespaces=air 4. Data Copy HP> exec DBMS_FILE_TRANSFER.PUT_FILE( HP_DG', air01.dbf',\ SUN_DG', air01.dbf', DB_LINK') ; HP> exec DBMS_FILE_TRANSFER.PUT_FILE( HP_DG', air02.dbf',\ SUN_DG', air02.dbf', DB_LINK') ; HP> ftp sun put air.dmp HP> alter tablespace air read write; 5. Plug-In % imp userid=\'sys/welcome as sysdba\' file=air.dmp log=air_i.log \ transport_tablespace=y datafiles= +DISK1/trans/datafile/air01.dbf, \ +DISK1/trans/datafile/air02.dbf SUN> alter tablespaceair read write; SUN> alter user air default tablespace air; 6. 기타확인내용 1) 통계정보 2) Constraints,Trigger를제외한기타Procedure, Function 등, Invalid Object 확인 3) 기타실행계획 143 Copy from ASM using XML FTP ASM to File system XML FTP In Oracle 10.2 and above files in ASM file systems can be accessed using FTP Requires XML DB installation Created by default during DBCA installation Port numbers must be assigned manually. For example: FTP 7001 HTTP/WebDAV 8001 Port numbers can be assigned in Enterprise Manager XDB Configuration Page SQL*Plus For example, run the following SQL*Plus script as SYSDBA

73 Copy from ASM using XML FTP % lsnrctl status Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= )(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= )(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac_node1)(PORT=8888))(Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac_node1)(PORT=7777))(Presentation=FTP)(Session=RAW)) 145 Copy from ASM using XML FTP 1. XML DB port 설정 SQL> exec dbms_xdb.setftpport(7777); SQL> alter system register; SQL>!netstat -na grep LISTEN grep 7777 tcp : :* LISTEN 2. FTP 접속 % ftp rac_node Name (rac_node1:oracle): system 331 pass required for SYSTEM Password: 230 SYSTEM logged in 3. ASM repository로이동및file Get ftp> cd /sys/asm ftp> ls drw-r--r-- 2 SYS oracle 0 JUN 02 20:03 DATA_DG drw-r--r-- 2 SYS oracle 0 JUN 02 20:03 BACK_DG ftp> cd BACK_DG ftp> ls -rw-r--r-- 1 SYS oracle JUN 02 20:03 bk_1390_1_ << asm backup 파일 ftp> get bk_1390_1_ % ls -lrt -rw-r--r-- 1 oracle dba Jun 2 16:04 bk_1390_1_

74 Useful Tips (RAC Tuning, etc.) & QnA 147 log file sync _wait_for_sync=false COUNT _wait_for_sync=true COUNT COUNT TPS 약 10% 정도향상됨. parameter 변경전에는 Orange Session Monitor 에서실시간으로 log file sync 가나타났으나, SQL*Net message to client, SQL*Net message from client 만나타남 COUNT _wait_for_sync=false 148

75 dml_locks=0, alter table table_name disable table lock; 만일 DML_LOCKS 파라미터값을 0으로주면테이블에대해 TM Lock이획득되지않는다. 이경우오라클은테이블정의가보호되는것을보장하기위해이미존재하는테이블에대한 DDL을원천적으로허용하지않는다. 따라서, TM Lock을획득하지않고도아무런걱정없이테이블의특정 row를변경하는것이허용된다. RAC와같은환경에서 TM Lock을글로벌하게획득하는데서오는오버헤드를줄이기위해 DML_LOCKS 값을 0으로변경하기도한다. For RAC, table locks are coordinated through global inter-instance communication. Due to the fact that properly designed applications do not need to lock entire tables, table locks can be disabled to improve locking efficiency with minimal adverse side effects. Essentially, there are two methods for disabling table locks: * Disabling table locks for individual tables. * Setting dml_locks to zero. The following sections will examine these methods. 149 dml_locks=0, alter table table_name disable table lock; Disabling Table Locks for Individual Tables To prevent users from acquiring individual table locks, the following statement can be used: ALTER TABLE table_name DISABLE TABLE LOCK When users attempt to lock tables with disabled locks, they will receive an error. To re-enable table locking after a transaction, the following statement can be used: ALTER TABLE table_name ENABLE TABLE LOCK Using this syntax forces all currently executing transactions to commit before enabling the table lock. The statement does not wait for new transactions to start after issuing the ENABLE statement. The disadvantage to this statement is that it must be executed for all tables that may experience improper locking. To determine whether a table in the schema has its table lock enabled or disabled, the table_lock column in the user_tables data dictionary table should be queried. If SELECT privilege is on dba_tables, the table lock state of other user 뭩 tables can be queried as well. The all_tables views can be used to see the locking state of tables for which a user has been granted SELECT privileges. 150

76 dml_locks=0, alter table table_name disable table lock; Setting dml_locks to Zero Using the dml_locks initialization parameter, table locks can be set for an entire instance. This will disable the DROP TABLE, CREATE INDEX and LOCK TABLE commands. If these commands are not needed, dml_locks should be set to zero to minimize lock conversions and achieve maximum performance. DDL statements cannot be executed against tables with disabled locks. SQL*Loader checks the flag to ensure that there is not a non-parallel direct load running against the same table. The use of direct load forces Oracle to create new extents for each session. If dml_locks are set to zero on one instance, it must be set it to zero on all instances. If non-zero values are used with the dml_locks parameter, the values need not be identical on all instances. 151 dml_locks=0, alter table table_name disable table lock; COUNT Alter table AAA enable table lock; COUNT COUNT TPS 약 3% 정도향상됨 COUNT Alter table AAA disable table lock; 152

77 _db_fast_obj_truncate=false Symptoms Truncate is very slow or appears to hang Waits on Object Reuse enqueue may be seen statspack or AWR, e.g: Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) RO-Multiple Object Reuse (fast object reuse) 2,836 2, ,577 17, KO-Multiple Object Checkpoint (fast object checkpoint) ,716 49, or Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) DB Time Wait Class enq: RO - fast object reuse 21,075 10, Application or, in v$session-wait: 'enq: RO - fast object reuse' Cause Changes to the way truncate operates, under certain circumstances, may cause performance to be poorer than anticipated. This area is still under investigation by development. 153 Virtual Ethernet (on AIX) Virtual LAN 은하나의물리적인 Adapter 를여러 LPAR(OS) 가공유하여, 외부네트워크와의연결은물론각 LPAR 간통신을가능하게하는것. inter-partition 간굉장히속도가좋은 Gigabit Switch 의기능을해줄뿐만아니라, VLAN 을구성할수있어서, 특정파티션은내부만을, 특정파티션은외부네트워크까지도달할수있게해준다. POWER5 프로세서기반시스템들은직접적으로연결된 Network Interface 를통해서나또는 Virtual Ethernet Interface 를통해서 TCP/IP 연결을만든다. Virtual Ethernet 은하나의 Gigabit Ethernet Adaptor 와같은작동을한다. POWER5 프로세서기반서버의파티션들은 Virtual Ethernet 통신포트를통해 TCP/IP 를사용하여다른것들과통신한다. 최대 4,094 개의 Virtual Ethernet LAN(VLAN) 을정의할수있다. 각파티션은최대 65,534 개의 Virtual Ethernet Adaptor 를가상스위치에연결할수있다. 각어댑터는 21 개의 VLAN 들에연결될수있다. Virtual Ethernet 을실행하고설정할때에는특별한하드웨어나소프트웨어가필요하지않다. 파티션을위해특정 Virtual Ethernet 을실행한후에 ethxx 라는이름이붙은네트워크장치가그파티션에만들어진다. 그렇게되면 TCP/IP 를설정하여다른파티션들과통신한다. 154

78 Virtual Ethernet (on AIX) COUNT COUNT COUNT COUNT Pysical Ethernet TPS 약 29% 정도향상됨. Virtual Ethernet 155 CURSOR_SPACE_FOR_TIME=TRUE CURSOR_SPACE_FOR_TIME 파라미터값을 True 로변경하면, Oracle 은 Open 상태의 Cursor 를 Shared Pool 에 Pin 한다. Pin 된 Cursor 는 Close 가될때까지 Flush 되지않는다. 따라서 Open 후여러차례사용되는 Cursor 들이많은경우에는성능에유리하다. Oracle Manual 에따르면, Oracle Forms 와같이하나의화면에서많은수의 Cursor 를 Open 하는 Application 에서가장유리하다. CURSOR_SPACE_FOR_TIME 파라미터값이 True 이면, Open 상태의모든 Cursor 는 Pin 상태이며 Cursor 가 Close 될때까지는 Pin 이해제되지않는다. 따라서 Shared Pool 은 Open 상태의모든 Cursor 를저장할수있을만큼충분히커야한다. 그렇지않으면 ORA-4031 에러가날수있다. Hard Parse 가왕성한시스템은 Cursor 의재활용성이높지않기때문에이파라미터를 True 로지정하지않는것이좋다. 156

79 CURSOR_SPACE_FOR_TIME=TRUE COUNT cursor_space_for_time=false COUNT COUNT TPS 약 20% 정도향상됨 COUNT cursor_space_for_time=true 157 RAC Tracing Cluster Wide Tracing 예제 1. Cluster wide tracing 9i R2 에서부터 oradebug 에서 cluster wide 범위로 dump 를수행할수있다. ( 단, diag process 를기동시킨경우에만가능 ) 즉, 한노드에서 RAC cluster 참여한모든혹은일부노드들의 Hanganalyze / Systemstate dump 를받을수있다 2. Cluster wide tracing 의옵션들 -g : 각각의노드에 dump 를생성 (-g 를권장 ) -r : 옵션은한노드에생성 def : Default 인스턴스 all : 모든인스턴스들 3. Sample 예제 SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug -g all hanganalyze 3 Hang Analysis in /oracle/product/admin/rac/bdump/rac1_diag_38257.trc 반대편노드에도 ORACLE_SID _diag_ diag 의 process ID.trc 형식으로생성 SQL> oradebug -g all dump systemstate 10 Statement processed. 이어서 systemdump 를수행하면앞의 diag trace file 에추가된다 DIAG trace file 을 rename 하거나지우면 DB 를재기동하기전에는 dump 가생성되지않는다. 만약지우거나파일이름을변경한경우에는각각에노드에접속하여 Single 처럼작업한다. ( 이때는 bdump 가아니라 udump 에생성된다 ) SQL> oradebug hanganalyze 3 Hang Analysis in /oracle/product/admin/rac/udump/rac1_ora_47561.trc 158

80 Vote Disk Mirror ** Vote Disk Mirror 하기 0) OS 및 raw-device 준비 (200M 권고 ) 1) stop database $ srvctl stop database -d RACDB ** CRS & DB Patchset 실패시원복및 OS ReInstall 시에 CRS 관련 S/W Backup 과함께반드시아래 2 가지를 Backup 해야원복가능!! 1. Voting, OCR device Backup with dd 2. init.cssd, init.crs, init.crsd, init.evmd 2) stop nodeapps $ srvctl stop nodeapps -n rac7 $ srvctl stop nodeapps -n rac8 3) stop crs # crsctl stop crs root user!! 4) add VoteDisk # crsctl add css votedisk /dev/vg07/rvoting -force # crsctl add css votedisk /dev/vg08/rvoting -force 5) start crs # crsctl start crs # crsctl query css votedisk 6) start nodeapps $ srvctl start nodeapps -n rac7 $ srvctl start nodeapps -n rac8 7) start database $ srvctl start database -d RACDB 159 OCR Disk Mirror ** OCR Disk Mirror 하기 -- Online 중에 1) raw-device 준비및 Owner-ship 양쪽 Node 에서 [root@rac1 oracle10]# chmod 640 /dev/raw/raw3 [root@rac1 oracle10]# chown root:dba /dev/raw/raw3 2) Ocr Disk Mirror [root@rac1 oracle10]# ocrconfig -replace ocrmirror /dev/raw/raw3 [root@rac1 oracle10]# ocrcheck Status of Oracle Cluster Registry is as follows : Version : 2 Total space (kbytes) : Used space (kbytes) : 3760 Available space (kbytes) : ID : Device/File Name : /dev/raw/raw2 Device/File integrity check succeeded Device/File Name : /dev/raw/raw3 Device/File integrity check succeeded Cluster registry integrity check succeeded [root@rac1 oracle10]# more /etc/oracle/ocr.loc 다른 Node 는자동으로바뀜!! #Device/file getting replaced by device /dev/raw/raw3 ocrconfig_loc=/dev/raw/raw2 ocrmirrorconfig_loc=/dev/raw/raw3 local_only=false ** OCR Disk Mirror 해제하기 -- Online 중에 [root@rac1 oracle10]# ocrconfig -replace ocrmirror 160

81 각종 IP 변경 : VIP & VIP Device 변경 CRS 만떠있는상태에서 (nodeapps 까지는내리고 ), srvctl modify 명령으로수정함. IP 변경없고, NIC Device 명이바뀌더라도, VIP 변경작업내용과동일하게작업해야함. [trdb01:/oracle]$srvctl stop nodeapps -n trdb01 [trdb01:/oracle]$srvctl stop nodeapps -n trdb02 trdb01:/#>srvctl modify nodeapps -n trdb01 -A / /en7 trdb01:/#>srvctl modify nodeapps -n trdb02 -A / /en7 trdb01:/#>crs_stat -p ora.trdb01.vip trdb01:/#>crs_stat -p ora.trdb02.vip [trdb01:/oracle]$srvctl start nodeapps -n trdb01 [trdb01:/oracle]$srvctl start nodeapps -n trdb02 VIP가정상으로기동하니까, Listener도정상적으로기동함. Name Type Target State Host ora...t1.inst application ONLINE OFFLINE ora...t2.inst application ONLINE OFFLINE ora.krxbmt.db application OFFLINE OFFLINE ora...01.lsnr application ONLINE ONLINE trdb01 ora.trdb01.gsd application ONLINE ONLINE trdb01 ora.trdb01.ons application ONLINE ONLINE trdb01 ora.trdb01.vip application ONLINE ONLINE trdb01 ora...02.lsnr application ONLINE ONLINE trdb02 ora.trdb02.gsd application ONLINE ONLINE trdb02 ora.trdb02.ons application ONLINE ONLINE trdb02 ora.trdb02.vip application ONLINE ONLINE trdb 각종 IP 변경 : Interconnect IP & Interconnect Device 변경 CRS 만떠있는상태에서 (nodeapps 까지는내리고 ), oifcfg delif setif 명령으로수정함. IP 변경없고, Interconnect/Public NIC Device 명이바뀌더라도, IP 변경작업내용과동일하게작업해야함. [trdb01:/oracle]$oifcfg getif en global public en global cluster_interconnect [trdb02:/oracle]$oifcfg getif en global public en global cluster_interconnect [trdb01:/oracle]$srvctl stop nodeapps -n trdb01 [trdb01:/oracle]$srvctl stop nodeapps -n trdb02 [trdb01:/oracle]$oifcfg delif -global en5 [trdb01:/oracle]$oifcfg delif -global en4 [trdb01:/oracle]$oifcfg getif [trdb01:/oracle]$oifcfg setif -global en8/ :cluster_interconnect [trdb01:/oracle]$oifcfg setif -global en7/ :public [trdb01:/oracle]$oifcfg getif en global cluster_interconnect en global public [trdb02:/oracle]$oifcfg getif en global cluster_interconnect en global public [trdb01:/oracle]$srvctl start nodeapps -n trdb01 [trdb01:/oracle]$srvctl start nodeapps -n trdb02 162

82 각종 IP 변경 : Interconnect IP & Interconnect Device 변경 Node1 SQL> select * from x$ksxpia; ADDR INDX INST_ID PUB_KSXPIA PICKED_KSXPIA NAME_KSXPIA IP_KSXPIA B6CF8 0 1 N OCR en B6CF8 1 1 Y OCR en Node2 SQL> select * from x$ksxpia; ADDR INDX INST_ID PUB_KSXPIA PICKED_KSXPIA NAME_KSXPIA IP_KSXPIA B2B N OCR en B2B Y OCR en SQL> select * from gv$cluster_interconnects; INST_ID NAME IP_ADDRESS IS_ SOURCE en NO Oracle Cluster Repository 2 en NO Oracle Cluster Repository Node1 alert.log Interface type 1 en configured from OCR for use as a cluster interconnect Interface type 1 en configured from OCR for use as a public interface Node1 oradebug SQL> oradebug setmypid SQL> oradebug ipc [trdb01:/oracle]$tail /oracle/app/admin/krxbmt/udump/krxbmt1_ora_ trc... SSKGXPT 0x1044f9d0 flags SSKGXPT_READPENDING socket no 7 IP UDP RAC 환경의 Character Set 변경 ** (KO16KSC5601 KO16MSWIN949) cluster_database=false 로설정후, character set 변경변경 $ export NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949 SQL> alter system set cluster_database = false scope=spfile; [oracle@halla ~]$ srvctl stop database -d halla SQL> startup mount exclusive SQL> alter system enable restricted session; SQL> alter system set job_queue_processes=0 scope=memory; SQL> alter system set aq_tm_processes=0 scope=memory; SQL> alter database open; SQL> alter database character set ko16mswin949; SQL> select * from props$; SQL> alter system set cluster_database=true scope=spfile; SQL> shutdown immediate [oracle@halla ~]$ srvctl start database -d halla [oracle@halla ~]$ crsstat 164

83 RAC Added Parameters 1. Instance_Number, Thread 2. Cluster_Database, Cluster_Database_Instances 3. Remote_Listener, Local_Listener 4. Parallel_Instance_Group, Instance_Groups 5. Cluster_Interconnects 165 Some [Hidden] Parameters 1. _interconnect_checksum = FALSE 2. _cr_server_log_flush = FALSE 3. _immediate_commit_propagation = FALSE 4. commit_write = BATCH, NOWAIT 5. _optim_peek_user_binds = FALSE 6. trace_enabled = FALSE 7. _gby_hash_aggregation_enabled = FALSE 8. lock_sga = TRUE 166

84 OPS/RAC 변천사 167 OPS/RAC 변천사 168