Slide 1 - PDF Free Download

<Insert Picture Here> OTN-DBA Day : Upgrading to Oracle 11gR2 What you need to know 2009.11.18 Oracle Customer Service

Agenda 1. Why Upgrade? Oracle 의 Version 관리정책의이해 <Insert Picture Here> 11g New Features 2. 11gR2 Upgrade 사젂준비사항 H/W, S/W 홖경영향도분석 3. 성공적인 Upgrade 방안 Challenges of Upgrading DBMS Top Upgrade Secrets

Core Drivers of Upgrade BUSINESS GROWTH initial estimate is conservative COMPLIANCE - so sensitive data and services are not exposed BUSINESS THREATS and OPPORTUNITIES - a competitor introduces new products, services, effective business operations New Technology Better Performance, Maximize Availability Cost Effectiveness- TCO, Compression Software Life Cycle Stability, End of Service

Oracle Database Lifetime Today 유지보수 Full Support 지원영역 향후 Upgrade 대상 Version Version Upgrade R2 R2 August 2012 August 2015 July 2010 July 2013 July 2011 현재운영 Version R2 Premier Support Extended Support July 2007 July 2008 July 2010 Sustaining Support t 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 2003 2002 http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf

Oracle Database Lifetime 10gR2 버젂또한 2010 년 07 월이 PS 기간이만료됨 서서히 11gR2 Upgrade 을준비할시점임 참고 : My oracle Support note 번호 : 161818.1

Lifetime Support Policy * PS : Premier Support 의약어 Feature Premier Support Extended Support Sustaining Support Support 기간 제품출시후 5 년간 PS 종료후별도계약에의해 3 년간 PS 종료후제품폐기시까지 Major Product and Technology Releases Technical Support Access to Knowledge Base (MetaLink/Customer Connection/SupportWeb) Updates, Fixes, Security Alerts and Critical Patch Updates No (Pre-existing Only) Tax, Legal and Regulatory Updates No Upgrade Scripts No Certification with existing Third Party Products/Versions No Certification with new Oracle Products No Certification with New Third Party Products/Versions No No

Continuous Database Innovation Oracle 2 Oracle 11g Exadata Storage Real Application Testing Oracle 8i Advanced Compression Automatic Storage Management Oracle 8 Transparent Data Encryption Self Managing Database XML Database Oracle 7 Oracle Data Guard Real Application Clusters Flashback Query Oracle 6 Virtual Private Database Oracle 10g Built in Java VM Partitioning Support Built in Messaging Object Relational Support Multimedia Support Data Warehousing Optimizations Parallel Operations Distributed SQL & Transaction Support Cluster and MPP Support Multi-version Read Consistency Client/Server Support Platform Portability Commercial SQL Implementation Oracle 5 Oracle 9i

11g New Features Summary 가용성 / 운영 / 성능에대한강화 Real Application Clusters, Active Data Guard Adaptive Cursor Sharing, Result Cache Data Information 관리강화 (ILM) Partitioning Advanced Compression 보안,Governance, Risk & Compliance Security Options (TDE 강화 ) Total Recall 비용젃감, 변경관리최적화, Risk 감소 Real Application Testing SQL Plan management DW, 대용량 Data 관리 / 성능강화 Oracle Information Appliances OLAP, Mining, Warehouse Builder

High Availability - Active Data Guard Standby 에서 Read-only 질의가능 서버활용도최대화 활용예 ) 실시간성 reporting, Read only 성업무, Select 위주 SQL, DB 백업, 복구 Online Primary Site Active Idle Standby Site RAC Online 동기화 RAC Primary Database Active Dataguard Standby Database

Performance Bind 변수에의한성능왜곡최소화 Adaptive Cursor Sharing Bind 변수를사용하여수행되는 SQL 에대해변수값과일량 (buffer gets) 을모니터링함 Bind 에따라일량 (buffer gets) 차이가발생할경우실행계획을재생성하여성능극대화하여데이터분포에따른급격한성능변화제거 반복적인동일 SQL 수행성능의극대화 SQL Query 결과를메로리 join Results Cache 캐쉬에저장되어동일 Query 수행시 SQL에대한재수행대싞보관된 result을바로 T1 join GBY GBY cache 보내어성능극대화 join T2 T3

Security - TDE (Transparent data encryption) Application 수정없이손쉬운 DB 작업만으로 DATA 암호화가능 10g Column Level Encryption 테이블의특정칼럼만암호화 SGA 에도암호화된채로존재 Range Scan 의제약사항존재 11g Tablespace Level Encryption 테이블스페이스젂체가암호화 Server Process 입장에서는암호화와상관없이동작 Range Scan 의제약조건없음. SGA 1 2 3 4 SGA 3 4 1 2 1 2 3 4 3 4 암호화복호화 3 4 3 4 1 2 DBWR 1 2 Server Process 3 4 4 3 SQL Result 3 4 Server Process 3 4 복호화 DBWR 1 2 암호화 일반블록 1 3 2 암화화대상블록 1 2 4 3 특정칼럼만암호화된블록 4 전체암호화된블록일반테이블스페이스일반테이블스페이스암호화된테이블스페이스

Reduce risk of change - SQL Plan management 여러환경변화에따른 SQL Plan 변경을방지하여성능일관성유지 사젂에검증된 SQL Plan 만 Query 수행 SQL Plan 의관리 미검증 Plan 은자동저장관리 검증과정을통해성능을향상할경우에는수용 Parse GB HJ Execute 검증된 Plan 일경우해당 Plan 대로수행 HJ GB NL 미검증 Plan 은저장하고검증된 Plan 으로변경하여수행 NL

Reduce risk of change - Database Replay 운영 DBMS 에서캡쳐한 Workload 을그대로 Test 에재생 활용예 ) 운영 DB 에변경사항반영젂에불안정요소를인지, 분석, fix 할수있음 운영 DB 환경 테스트 DB 환경 Capture Replay Workload

Reduce risk of change - SQL Performance Analyzer Test System 에운영 DBMS 의특정 SQL 을자동수집하여재현 활용예 ) Identify and remediate performance regressions before end-users impacted 운영 DB 환경 테스트 DB 환경 Use SQL Tuning Advisor to automatically tune regression Clien t Clien t Clien t Re-execute SQL Queries Middle Tier Capture SQL Storage

Reduce Cost - Advanced Compression 약간의 DML overhead (3% 전후 ) But Space 에젃약 ( 70%) + 읽기성능향상 (2.5 배 ) Real World ERP 10 Largest Tables Space Table Scans DML Performance 2500 0.4 40 2000 0.3 30 1500 0.2 20 1000 500 0 3x Savings 0.1 0 2.5x faster 10 0 < 3% overhead

Reduce Cost - Oracle Clusterware & ASM 오라클 Clusterware 및 ASM 사용한다는것은비용절감뿐만아니라단일 Vendor 환경으로문제해결시간단축 public network Node1 VIP1 Service Listener VIP n Service Listener Node n ASM instance 1 instance N Tables ASM Oracle Clusterware Operating System cluster Interconnect Shared storage ASM Oracle Clusterware Operating System Tablespace File Names Cluster File System 불필요 Cluster Volume Manager 불필요 Automatic Storage Management Disk Group Managed by ASM R2 Redo / Archive logs all instances Database / Control files OCR and Voting Disks

H/W & OS 환경영향도 * Note : 자세한영향도평가는젂문가의 AS/IS DB 분석을통해산정됩니다. OS / Clusterware / JDK Version OS 의 Software Lifetime 고려 Upgrade 하려는 DBMS Version 과 OS Version 과의 Certification 문제 Upgrade or Migration 진행젃차에반영 CPU 현재의 CPU 사용량검검 일반적으로 Version 이한단계올라갈경우보통증설이불필요 : SQL Plan 안정화및 Version Up 에따른성능향상효과로보완 향후업무량증가 (SQL 추가, Data 증가 ) 를고려한 CPU 증설검토 Memory 현재의 Memory 사용량점검 일반적으로 Version 이한단계올라갈경우 Oracle 이사용하는 Process 의최소 50% 에서최대 100% 증설검토 향후업무량증가 (AP 서버확대, Connection 수증가 ) 를고려한 Memory 증설검토 Storage RAC 일경우 CRS 설치영역, ocr/voting disk 영역추가할당 sysaux 에대한영역, 용량산정결과에따른기타 system 영역확장고려 Network RAC 의경우현재의 Traffic 을기준으로 Interconnect Line 에대한 Switch 이중화, bandwidth 산정

RAC H/W 구성권고 Oracle Interconnect 에대한 Switch 이중화, Nic 에대한이중화구현을권고함 Crossover Cable 더이상 Support 하지않음 Oracle Interconnect Line Gigabit 이상권고 Public Line TCP Storage Line FC 채널 Cache R2 상기 Image 는이해를돕기위한 sample 입니다.

S/W 환경영향도 Client certification - Note: 207303.1 C l i e n t Database Release 11.2.0 11.1.0 10.2.0 10.1.0 9.2.0 9.0.1 8.1.7 11.2.0 x x 11.1.0 x x 10.2.0 10.1.0 9.2.0 9.0.1 8.1.7 x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x Certified x Not supported anymore x Supported but ES Never supported

S/W 환경영향도 Database links Only supported if matrix shows Supported in both directions Not supported means: Use it on your own risk Forms/Reports certification My oracle Support Certify Tab Development Tools JDBC certification My oracle Support Note:203849.1 기타 Clients 해당 tool 별제공 Vendor 문의

Challenges of Upgrading DBMS Quality Assurance SQL Performance Difficulty in Testing Real Workloads Application Compatibility Need to Upgrade Applications too Downtime Prevent Known Issues Cost down, Upgrading Skills Etc (Fast resolution, workaround )

Challenges of Upgrading DBMS Etc??? 어떤 Task들을언제, 어떻게수행해야할까? 어디부터시작해야하나? Plan 신규시스템은제대로구성되었나? DB, Cluster, RAC, interconnect, parameter Upgrade 후새로운문제점은없을까? Open 시비상사태? Post Upgrade Upgrade 정상여부확인방법?? Governance 사전에장애를예방할수없을까? Test Test 는무엇을어떤식으로진행하나? 결과검증은어떻게? 우리인력이훌륭하지만이런작업은경험이없어서리 Execute Issue 발생시신속한해결이필요한데 Plan 변경에따른성능저하? 실제와비슷한시스템부하를어떻게만들어테스트하나?

Upgrade Secret #1 Read the documentation

Documentation Upgrade Guides + http://download.oracle.com/docs/cd/b28359_01/server.111/b28300/toc.htm http://download.oracle.com/docs/cd/e11882_01/server.112/e10819/toc.htm Note:429825.1 Complete Checklist for Manual Upgrades to 11g Note:837570.1 Complete Checklist for Manual Upgrades to 11g Release 2

Documentation Note: 601807.1 Upgrade Companion 11g Note: 785351.1 Upgrade Companion 11 Release 2

Upgrade Secret #2 Patch your new $ORACLE_HOME before you upgrade

Patch Set Installation Install patch sets and patches to your 'fresh' $OH before you start the upgrade

Recommended Patches

Patch Set Update (PSU) Installation Install PSUs as well Note:854428.1: Introduction to Database Patch Set Updates Database PSUs include: Fixes for critical issues that may affect a large number of customers and that are already proven in the field Critical Patch Update (CPU) fixes Database PSUs do not include: Changes that require re-certification Fixes that require configuration changes Typically 50-100 new bug fixes - cumulative Guaranteed to be RAC rolling installable Change the fifth digit of the release number, e.g. 10.2.0.4.3 Will be released 4x per year such as CPUs on the same schedule Platforms: Solaris SPARC64, Linux x86 and x86-64, HP-UX PA-RISC, HP Itanium, IBM AIX

효율적인오라클 DB Patch 적용 분석된 Patch를정기적으로적용 상기권고사항과고객업무, Project 일정에맞추어진행관리필요 1. 현재운영 Version에대한최싞출시된 Patchset 조사 2. 최싞 patchset 이후출시된 PSU, CPU Patch조사 3. Database 제품군에따른 Merge Patch, bundle Patch 조사 4. 기존에적용된 Patch에대한상기 Patch에반영되어있는지조사 5. 기타주요한 one-off Patch들에대한조사 6. OS Patch에대한조사 Premier Support 기간 ( 출시후 5 년 ) New Version 출시 Patchset #1 Patchset #2 Patchset #3 Patchset #4 Upgrade Patch Patch Patch Patch Patch 6 개월단위 1 년 2 년 3 년 4 년 5 년

효율적인오라클 DB Patch 적용 오라클 Patch 적용및 Parameter 변경에대한 Best Practice 1. 운영계와가능한동일한 Architecture 을가진 Test 환경구성 - Storage 는가능한동일하게, cpu/memory 는작아도큰영향없음 2. 고객표준 System 변경관리젃차에따른 DB 패치적용 업무테스트환경구축 운영 DBMS Version 표준구성안을위한테스트 업무연동테스트 운영적용계획 운영시스템적용 모니터링 테스트 / 개발 Online 운영 테스트 / 개발서버는동일장비에서 2 개 DB 로분리운영 주기적으로 Data 복제

Upgrade Secret #3 Preserve performance statistics

Preserve Performance Statistics Upgrade Project 수행젂충분한성능 Data 수집은매우중요한작업임 ( OS, DB, Middleware 등가능한 Data 수집및보관 ) 수집기간 : 최소 4 주이며권장기간은 3 개월이상임주요 Peak time( 결산, Batch) 의성능 Data 포함 수집주기 : 30 분 or 60 분단위 In Oracle 8i/9i/10g(if you don t use AWR): Note:466350.1 Use STATSPACK Export the PERFSTAT user right before the upgrade In Oracle 10g/11g: Use AWR Export the AWR with DBMS_SWRF_INTERNAL.AWR_EXTRACT Use ADR DIFF reports to compare before & after upgrade performance:

Upgrade Secret #4 The upgrade won't take hours...

Upgrade Length How long will the upgrade take to complete? Independent of: Size of the database Used datatypes Dependent mainly on: The number of installed components and options Valid and non-stale data dictionary statistics Number of synonyms they'll get recompiled (upgrade from 9i) Number of objects in XDB At a very low rate, if COMPATIBLE is increased: Number of datafiles Size of redo logs

Upgrade Length Speed up your upgrade performance by: Truncating the auditing table SYS.AUD$ SQL> truncate SYS.AUD$; Creating dictionary statistics right before the upgrade Oracle 9i: SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('SYS', options => 'GATHER',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE); Oracle 10g/11g: SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;

Upgrade Secret #5 Sanity operations

Plan table, Recycle bin Drop table SYS.PLAN_TABLE$ and the public synonym PUBLIC.PLAN_TABLE For background information please see: Alert Note:782735.1, Note:605317.1 and Note:736353.1 Otherwise the component "Oracle Server" may be INVALID after the upgrade Applies to upgrades to: 10.2.0.4, 11.1.0.6 and 11.1.0.7 Issue has been introduced with DBMS_SQLPA If upgrading from 10g or 11g, purge the recycle bin before the upgrade. SQL> purge DBA_RECYCLEBIN;

Upgrade Secret #6 How to Handle Timezone Changes

Timezone Patches - 11gR1 Upgrade to Oracle Database 11gR1: New 11g-$OH has timezone V4 Source $OH (<10.2.0.4) has to be patched to timezone V4 Note:359145.1 Download and run the check script utltzuv2.sql Note:413671.1 Download and apply the patch

Timezone Patches - 11gR2 R2 Upgrade to Oracle Database 11g Release 2: New 11.2-$OH has timezone V11 No need to patch the source $OH Database only needs to be adjusted if you are using the datatype TIMESTAMP WITH TIMEZONE Conversion done after the upgrade See Note 944122.1 Package DBMS_DST DBMS_DST.FIND_AFFECTED_TABLES DBMS_DST.BEGIN_UPGRADE DBMS_DST.UPGRADE_DATABASE DBMS_DST.END_UPGRADE

Upgrade Secret #7 Always run the pre-upgrade script: Upgrade to Oracle Database 11g : utlu111i.sql Upgrade to Oracle Database 11.2 : utlu112i.sql

Pre-Upgrade Check Get the current version of utlu1nmi.sql Download it now! Note:884522.1

Upgrade Secret #8 After the upgrade...

Post Upgrade Create system statistics during a regular workload period - otherwise non-appropriate values for the CBO will be used: SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start');... SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop'); SQL> select pname NAME, pval1 VALUE, pval2 INFO from aux_stats$; NAME VALUE INFO -------------------- ---------- ------------------------------ STATUS COMPLETED DSTART 04-03-2009 12:30 DSTOP 05-03-2009 12:30 FLAGS 1 CPUSPEEDNW 1392.39 IOSEEKTIM 8.405 IOTFRSPEED 255945.605...

Post Upgrade Create fixed table statistics Directly after catupgrd.sql has been completed This will speed up the job processing for recompilation with utlrp.sql SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; Again: after a few days regular database workload

Upgrade Secret #9 최적의 Upgrade Path 및방법론선택

Upgrade Path Export/Import UPGRADE N Stay on same OS? Y CTAS, COPY N Downtime >60min? Data Guard Logical Standby Y Oracle Streams Transportable Tablespaces DBUA Manual Upgrade SQL> @catupgrd 별도장비가있을경우 (H/W, Storage 등 ) 안정적인검증 Test 홖경마련으로 Downtime 최소화, 방안에따라 Data 재편성효과등새롭게 New Feature 적용이가능한방식임 일반적인방식으로운영장비에서바로 Upgrade Oracle 이권고하는방식으로써가장성공적이고쉬운 Upgrade 방식이나일정시간 Downtime 이필요함

Upgrade Path 7.3.4 R2 9.2.0.4 9.2.0.8 8.0.6 8.1.7.4 R2 10.1.0.5 9.0.1.4 R2 10.2.0.2 "Empty" arrows mean: no specific patch release required

Command Line vs. DBUA Command line upgrade is better than DBUA But DBUA 는아래와같은장점이있음 DBUA does a lot of helpful checks & Simple Less error-prone DBUA changes the parameter and values for you DBUA uses the same scripts

Prevent execution plan changes Classical approach: Rule Based Optimizer (RBO desupport since Oracle 10g - Note:189702.1) Hints Stored Outlines Rewriting SQL statements optimizer_features_enabled=n.n.n Change specific optimizer parameters Import and fix object and systems statistics Modern, efficient and better resource consumption: SQL Plan Management

Without SQL Plan Management Challenging to "freeze" execution plans and statistics Difficulty: Statement has been parsed and a plan got created Verification happens during execution: GB Parse Execute Plan acceptable HJ HJ Now some conditions get changed (statistics, upgrade, parameters) A new plan will be created - is it better or worse??? GB Parse Execute Plan possibly NL not acceptable NL

SQL Plan Management - Upgrade 1 Upgrade scenario 1 STS Repeatable plans will be added to the Plan Baseline upon 2nd execution Plan History Plan Baseline GB GB GB GB NL NL Now: Different plans created with OFE=11 will be added to the Plan History for later verification NL HJ NL NL HJ NL

SQL Plan Management - Upgrade 2 Upgrade scenario 2 Staging STS Table exp imp expdp impdp DB-Link... STS DBMS_SQLTUNE 사용 (STS: Sql Tuning Set) 10.2 plans will be added to the SQL Plan Baseline Plan History GB Plan Baseline GB GB GB NL NL NL HJ NL NL HJ NL Every new and better plan will be stored in the Plan History

Upgrade Secret #10 Better not test... something is bound to go wrong anyway

Testing is the secret of upgrade's success Never ever change too many system components at once! Clearly document all changes into a change log! Always (!!!) use real world data for testing! Reserve enough time and resources for testing. ALWAYS collect sufficient performance data BEFORE starting the upgrade!! Create a fallback strategy! PLEASE test your fallback strategy - does it really work?? Please remember: Upgrade has never been easier - but you still have to test!!!

Summary Upgrade has never been easier... But don't forget to test carefully Oracle Database 11g has many great features Stable and fast optimizer SQL Plan Management Online application upgrade Etc Let's go to 11gR2

For More Information http://search.oracle.com or oracle.com