2010 Oracle Corporation 1
<Insert Picture Here> Best Practices for Upgrading to Oracle Database 11g Release 2 Qche Yeo( 여규채, qche.yeo@oracle.com) Technical Solution Consulting Oracle Korea
Agenda Best Practices FAQ Summary 2010 Oracle Corporation 3
Agenda Best Practices FAQ Summary 2010 Oracle Corporation 4
Best Practice #1 Read the FRIENDLY Manuals! 2010 Oracle Corporation 5
Documentation Note:785351.1 Upgrade Companion 11g Release 2 Upgrade에대한많은정보와 Tip 제공 4개의 Session으로구성 : - Best Practices - Behavior Changes - Patches Recommended - Documentation 2010 Oracle Corporation 6
Documentation Note:785351.1 Upgrade Companion 11g Release 2 [ Example ] 2010 Oracle Corporation 7
Documentation Note:785351.1 Upgrade Companion 11g Release 2 [ Example ] 2010 Oracle Corporation 8
Documentation Upgrade Guides http://download.oracle.com/docs/cd/e11882_01/server. 112/e10819/toc.htm Note:837570.1 Complete Checklist for Manual Upgrades to 11g Release 2 Note: 421191.1 Complete checklist for manual upgrades from X to Y 2010 Oracle Corporation 9
OTN Upgrade Page http://www.oracle.com/technology/products/database/o racle11g/upgrade/index.html 2010 Oracle Corporation 10
OTN Upgrade Page Or simply... OTN Upgrade Page 로이동 2010 Oracle Corporation 11
OTN Upgrade Page By the way... works with other search engines as well OTN Upgrade Page 로이동 2010 Oracle Corporation 12
OTN Upgrade Page http://www.oracle.com/technology/products/database/oracle1 1g/upgrade/index.html Technical White Papers Presentations Links to Various Documentation 2010 Oracle Corporation 13
OTN Upgrade Forum http://forums.oracle.com/forums/forum.jspa?forumid=583&sta rt=0 If you have a quick question If you want to now if someone has experienced a error 2010 Oracle Corporation 14
Upgrade Blog http:// blogs.oracle.com/upgrade Latest Information: Best Practices, Workshops, Projects 2010 Oracle Corporation 15
Best Practice #2 Patch your new $ORACLE_HOME before you upgrade 2010 Oracle Corporation 16
Recommended Patches 2010 Oracle Corporation 17
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 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 2010 Oracle Corporation 18
Important Alerts? Note 161818.1 : ALERT: Oracle 11g Release 2 (11.2) Support Status and Alerts 2010 Oracle Corporation 19
Upgrade Information / Alerts Note:880782.1 Support Status and Alerts for Oracle 11g Release 2 (11.2.0.X) 2010 Oracle Corporation 20
Upgrade Information / Alerts Note 880707.1 Known Issues specific to the 11.2.0.1 Base Release 2010 Oracle Corporation 21
Upgrade Information / Alerts Note 880707.1 Known Issues specific to the 11.2.0.1 Base Release 2010 Oracle Corporation 22
Recommended OS patches Note169706.1 OS Installation and Configuration Requirements 2010 Oracle Corporation 23
Best Practice #3 Preserve performance statistics 2010 Oracle Corporation 24
Performance Statistics Preserve performance statistics 정확한성능정보 ( 통계수치 ) 수집 Upgrade 전후비교 : before after 특정쿼리와배치프로그램에대한시간측정 Database 업그레이드테스트 기능테스트 (Functional tests) 부하테스트를통한성능검증 Real Application Testing(RAT) SQL Performance Analyzer (SPA) Database Replay 2010 Oracle Corporation 25
Performance Statistics Upgrade 이전충분한성능정보데이터를수집하는것이매우중요 충분한 의의미 : upgrade 이전적어도 4 주이전에시작 정확한성능정보 ( 통계수치 ) 를수집 In Oracle 8i/9i: STATSPACK 사용 Upgrade 바로이전에 PERFSTAT 유저를 Export Note:466350.1 STATSPACK before/after upgrade In Oracle 10g/11g: Use AWR Snapshots every 30-60 minutes retention: >30 days Export the AWR with DBMS_SWRF_INTERNAL.AWR_EXTRACT Use AWR DIFF reports to compare before & after upgrade performance: DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML 2010 Oracle Corporation 26
Best Practice #4 Pre-upgrade Sanity operations [ vincent ] Now I understand What you tried to say to me And how you suffered for your sanity And how you tried to set them free They would not listen They did not know how Perhaps they'll listen now 2010 Oracle Corporation 27
Invalid Objects INVALID objects 확인 : SQL> SELECT UNIQUE object_name, object_type, owner FROM dba_objects WHERE status='invalid'; SYS 와 SYSTEM 유저에는 invalid objects 가없어야함. Utlrp.sql 을사용하여 invalid objects 를재컴파일 Upgrade 전 / 후의 invalid objects 비교 Beginning with 11.1.0.7 the comparison has been automated Find invalid objects in registry$sys_inv_objs and registry$nonsys_inv_objs Compare before-after: utluiobj.sql The view dba_invalid_objs contains a list of invalid objects after the upgrade 2010 Oracle Corporation 28
Recycle bin 만약 10g 또는 11g 로부터 11gR2 로 Upgrade 할경우, 반드시 upgrade 이전에 recycle bin 을 purge 시켜야함. SQL> purge DBA_RECYCLEBIN; 2010 Oracle Corporation 29
Best Practice #5 항상 pre-upgrade script 를수행해야한다. Upgrade to Oracle Database 11.2 : utlu112i.sql 2010 Oracle Corporation 30
Pre-Upgrade Check Upgrade 할대상서버에서 utlu112i.sql 을수행 Oracle Database 11.2 Pre-Upgrade Information Tool 09-21-2009 22:33:20 ********************************************************************** Database: ********************************************************************** --> name: ORCL --> version: 10.2.0.3.0 --> compatible: 10.2.0.3.0 --> blocksize: 8192 --> platform: Linux IA (32-bit) --> timezone file: V4 [..] ********************************************************************** Update Parameters: [Update Oracle Database 11.2 init.ora or spfile] ********************************************************************** WARNING: --> "java_pool_size" needs to be increased to at least 64 MB [..] ********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING: --> Database is using a timezone file older than version 11.... After the release migration, it is recommended that DBMS_DST package... be used to upgrade the 10.2.0.3.0 database timezone version... to the latest version which comes with the new release. 2010 Oracle Corporation 31
Pre-Upgrade Check 최신버전의 utlu1nmi.sql 을확보 Download it Note:884522.1 click to download 2010 Oracle Corporation 32
Best Practice #6 Init/spfile 로부터 obsolete parameters, underscore(hidden) parameters, event 는삭제. Examples: init.ora: <...> _always_semi_join=off _unnest_subquery=false <...> optimizer_features_enable=9.0.1 <...> event = "10061 trace name context forever, level 10" <...> 2010 Oracle 2010 Corporation Oracle Corporation 33 33
Remove Old Parameters Example: customer workload Conclusion: 이전버전의 old parameters 는사용하지않고 11g default parameters 로시작하는것을권고 2010 Oracle Corporation 34 2010 Oracle Corporation
Best Practice #7 기존운영서버의 COMPATIBLE 값이 10.1 이상일경우, 11gR2 로 Upgrade 한후일주일간원래값그대로유지한후 11.2.x.x.x 로변경 2010 Oracle Corporation 35
Parameter COMPATIBLE COMPATIBLE has to be at least 10.1.0 for an 11g database No way back once 11.1.0 has been enabled Supported release downgrade to 10.1.0.5, 10.2.0.2, 11.1.0.6 No ALTER DATABASE RESET COMPATIBILITY command anymore COMPATIBLE = 10.0/1/2.0 COMPATIBLE = 11.0.0 2010 Oracle Corporation 36
Parameter COMPATIBLE DBUA raises COMPATIBLE only for 9i databases To enable new features after the upgrade: 11.1: SQL> alter system set compatible='11.1.0' scope=spfile; 11.2 : SQL> alter system set compatible='11.2.0' scope=spfile; DB restart 이후 : 11g 신기능이활성화 Datafile headers 변경 Redo log formats 이최초 access 시에변경 2010 Oracle Corporation 37
Best Practice #8 Test your fallback strategy! 2010 Oracle Corporation 38
Fallback Strategy In any case: Take a backup!!! Then decide: 예상치못한문제점이발생되어원상복귀를해야할상황에서 Upgrade 이후 Data 변경이있었는데 Data Loss 가허용됩니까? YES or NO? If YES: restore a backup, flashback (since 10g) If NO: export/import, downgrade 2010 Oracle Corporation 39
Fallback Strategy: catdwgrd.sql catdwgrd.sgl 스크립트을수행하여 Downgrade Note:443890.1 Upgrade 이전버전으로 Downgrade 10.1.0.5 10.2.0.2/3/4 11.1.0.6/7 Only possible if COMPATIBLE hasn't been raised!!! Please note: 만약 10.2.0.4 -> 11.2.0.1 -> 11.2.0.2 의 Path 로 Upgrade 를했다면 Upgrade 를한바로이전버전인 11.2.0.1 으로만 downgrade 가가능하다. 따라서 Upgrade 를할경우 patchset 을 software 에미리적용한후 upgrade 를수행해야 10gR2, 11gR1 등으로 downgrade 할수있다. 2010 Oracle Corporation 40
Fallback Strategy: catdwgrd.sql Downgrade with catdwgrd.sql to 10g Task in 11g environment: SQL> SPOOL /tmp/downgrade.log SQL> STARTUP DOWNGRADE SQL> @catdwgrd.sql SQL> SPOOL OFF Switch to your pre-upgrade 10g environment: SQL> STARTUP UPGRADE SQL> SPOOL /tmp/reload.log SQL> @catrelod.sql -- The catrelod.sql script reloads the appropriate version of -- all of the database components in the downgraded database. SQL> SPOOL OFF Please note: additional steps are required if EM repository resides in the database - please see chapter 6 Downgrading a Database in the Oracle 11g Upgrade Guide 2010 Oracle Corporation 41
Best Practice #9 After the upgrade... 2010 Oracle Corporation 42
Post Upgrade 대표적인 workload 상황에서 system 통계정보를생성해야함. 그렇지않으면부적절한값이 CBO 에서사용될것이다. SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start');... gather statistics while running a typical workload 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... 2010 Oracle Corporation 43
Post Upgrade Example: customer OLTP workload Runtime without system statistics: 2:19h Runtime with system statistics: 2:07h => 9% faster 2010 Oracle Corporation 44
Post Upgrade Fixed table 에대한통계정보생성 catupgrd.sql 이완료된직후실행 has been completed Utlrp.sql 에의한 recompilation 처리서응향상에도움이됨. SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 일주일후대표적인 workload 상황에서 fixed tables 에대한통계정보를다시생성 이러한작업은연간 2~ 3 차례정도만수행 2010 Oracle Corporation 45
Agenda Best Practices FAQ Summary 2010 Oracle Corporation 46
FAQ: Which Version Should I Upgrade To? today R2 January 2015 January 2018 August 2012 August 2015 R2 July 2010 July 2011 July 2013 January 2009 January 2012 R2 Premier Support July 2007 Extended Support July 2008 July 2010 Sustaining Support t 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 2003 2002 2010 Oracle Corporation 47
Upgrade to Oracle Database 11g Release 2 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 2010 Oracle Corporation 48
FAQ: How long will the upgrade take? 2010 Oracle Corporation 49
Upgrade Length Upgrade 는얼마만큼의시간이걸릴것인가? 관계가없는요소 : Database 크기 사용된 Data Types Upgrade 시간에영향을미치는요소들 : 설치된 components 와 options 의개수 Valid and non-stale data dictionary statistics Synonyms 개수 9i 로부터 upgrade 될경우재컴파일됨 XDB 의 objects 개수 COMPATIBLE 의증가할경우, 다음의요소들이영향을미치나미치는영향도는작은편임 : Datafiles 개수 Redo logs 크기 2010 Oracle Corporation 50
Example: Database Upgrade Time 일반적으로 Upgrade 시간은 30 ~ 90 분소요됨. 설치된 component 와 options의개수에좌우됨. * 실제 Upgrade 시간은약간씩다를수있는데 Component HH:MM:SS Oracle Server 00:16:17 JServer JAVA Virtual Machine 00:05:19 Oracle Workspace Manager 00:01:01 Oracle Enterprise Manager 00:10:13 Oracle XDK 00:00:48 Oracle Text 00:00:58 Oracle XML Database 00:04:09 Oracle Database Java Packages 00:00:33 Oracle Multimedia 00:07:43 Oracle Expression Filter 00:00:18 Oracle Rule Manager 00:00:12 Gathering Statistics 00:04:53 Total Upgrade Time: 00:52:31 2010 Oracle Corporation 51
Upgrade Length Upgrade 시간을줄일수있는방법 : Audit 테이블인 SYS.AUD$ 를 truncate 시킨다. SQL> truncate SYS.AUD$; Note:audit 레코드를보존하기위해서는 truncate 하기전에다른 tablespace 로임시로옮겼다가 upgrade 이후다시원상복귀시킨다. 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; 만약허용이된다면 noarchivelog 모드로변경후 Upgrade 이후에다시 archivelog 모드로변경 2010 Oracle Corporation 52
FAQ: Which Method Should I Use? Export/Import UPGRADE N Stay on same OS? Y CTAS, COPY N Downtime >30min? SQL Apply Y Oracle Streams DBUA Transportable Tablespaces Transportable Database CLI SQL> @catupgrd Oracle Golden Gate 2010 Oracle Corporation 53
When to Choose the DBUA 30 ~ 90 정도의 downtime이허용될수있다. O/S 가 upgrade 전후똑같다. Manual CLI 보다 GUI 방식을선호할경우 유용한 pre-upgrade 점검이자동적으로실행됨. 실수을줄일수있고 Manual 작업의수고가적다 기존 Database가적어도 9.2.0.8 이어야함. Note: RAC database 에특히유용함. 고려사항 : Upgrade 전후의 Oracle Homes은같은시스템에존재해야함 Upgrade 도중에러를만날경우, 다시실행할수없음. 2010 Oracle Corporation 54
When to Choose Command-Line SQL> spool upgrade.log SQL> @catupgrd.sql 30 ~ 90 정도의 downtime이허용될수있다. Manual CLI 가 GUI 보다선호됨. 기존 Database가적어도 9.2.0.8 이어야함. 새로운 H/W 와동일한 O/S 로마이그레이션 고려사항 서로다른 O/S 를가진시스템으로 Upgrade 할경우, CLI 방식을사용할수없음. 많은 manual 작업이수행되어져야함. 오타와빠트린상세절차에의한에러가능성 2010 Oracle Corporation 55
When to Choose an Alternative Method 다른 upgrade 방법 Original exp/imp or Data Pump expdp/impdp) Oracle Streams or Oracle Golden Gate Data Guard (SQL Apply) Transportable Tablespaces, Tansportable Database Moving data via CREATE TABLE AS SELECT or other techniques 다른 upgrade 방법은다음의경우에사용 : O/S 가변경될경우 (32bit O/S 에서동일한 64bit O/S 로변경되는경우는동일 Platform 으로간주됨 ) Upgrade 이전 DB 버전이 9.2.0.8 보다낮을경우 다른 upgrade 방법은다음과같은경우에유용함 : Minimal downtime (<30 minutes) 이요구되어질때 Database 의 storage 과 schemas 의 Reorganization 2010 Oracle Corporation 56
FAQ: Is anybody really live on 11.2? 2010 Oracle Corporation 57
34 External 11.2 References 05-MAY-2010 2010 Oracle 2010 Corporation Oracle Corporation 58 58
Agenda Best Practices FAQ Summary 2010 Oracle Corporation 59
Summary 준비와계획이성공적인 upgrade 의가장중요한요인이다. 기술적시나리오와비지니스적요구사항을고려하여가장적절한 upgrade 방안을선택 원복방안마련과해당방안이잘수행되는지를테스트 Oracle Database 11.2 는많은훌륭한기능을가진안정적인버전이므로 upgrade 를준비하고실행하십시요. FAQ: 어디에서발표자료를 download 받을수있나요? 영문버전 : http://blogs.oracle.com/upgrade ( 맨아래부분의 Upgrade Best Practices Talk for NoCOUG ) 2010 Oracle Corporation 60
Questions Answers 2010 Oracle Corporation 61
2010 Oracle Corporation 62