advertisement

LVOUG meetup #4 - Case Study 10g to 11g

100 %
0 %
advertisement
Information about LVOUG meetup #4 - Case Study 10g to 11g
Technology

Published on March 11, 2014

Author: mariselsins

Source: slideshare.net

Description

My presentation on a case study of 10g to 11g upgrade at LVOUG meetup #4 in 2012. Includes preserving execution plans by exporting them from 10g and importing as SQL Plan Baselines in 11gR2
advertisement

Case Study 10gR2 to 11gR2 Maris Elsins Oracle Applications DBA 28.06.2012

© 2012 Pythian Few words about me • 11y  Oracle • 3y – PL/SQL Developer • 8y – Oracle [Apps] DBA • Certificates: • 10g OCM, 9i/10g/11g OCP, • 11i Apps DBA OCP, 11i System Administrator OCE • Employed by Pythian since July 2011 • Speaker at conferences: 5* , 4* , 3* • How to find me? • Blog – http://www.pythian.com/news/author/elsins/ • Earlier blog posts – http://appsdbalife.wordpress.com/ • LinkedIn – http://lv.linkedin.com/in/mariselsins • Twitter – @MarisElsins • Email – elsins@pythian.com 2

© 2012 Pythian AGENDA • Starting Point • The Goal • Closer look at key things which ensurred the successful upgrade • Minimizing the downtime • Performance management • Repeatability of the upgrade process 3

© 2012 Pythian Starting Point • The business • Gas Detection as a Service • 24x7 web based application displaying alerts, trends, measurements taken by gas detection devices • 70 000 gas detection devices • 3 500 worksites • Data uploaded to the DB using docking stations • 10.2.0.3 EE + Tuning Pack + Diagnostics Pack • RHEL AS 4 (Update 7) • ~800Gb • HW doesn’t matter 4

© 2012 Pythian The Goal • Migration to new HW + Upgrade • 11.2.0.3 EE + Tuning Pack + Diagnostics Pack • OEL 5.8 + UEK • VMWare • 32G RAM • (8 cores / 16 threads with HT) Xeon X7560@2.27GHz • QUIZ! How many cores does a Xeon X7560 have? • The Challenge • We have 4 hours of downtime window • Old HW -> New HW = ~27MB/s (800Gb = ~8h) • upgrade itself takes ~1h • Collecting fresh statistics requires ~3h • Poor testing, how to guarantee at least the same performance? • Fallback strategy and required time (why is this important?) 5

© 2012 Pythian BRAINSTORM How do we fit into the downtime window? 6

© 2012 Pythian Chosen solution - Time • «Manual» Standby • Before the downtime • Clone 10.2.0.3 software • Preinstall 11.2.0.3 software • R/O NFS mount source backup locations • Use RMAN to duplicate the database (make sure it’s not open after duplication) • Establish custom archived log apply process • During the downtime • Complete the recovery and open the database (~20minutes) • Perform the upgrade (~1 hour) • Perform other tasks... (~30 minutes) 7

© 2012 Pythian RMAN duplicate the database • Clone 10.2.0.3 software • Prepare Auxiliary instance parameter file and tnsnames.ora • SQLNet connectivity to source DB • Define [db|log]_file_name_convert parameters • Use RMAN to duplicate the DB • Make sure the archived logs are NOT available while duplicate is running 8 connect target sys/***@PRD connect auxiliary / run { allocate auxiliary channel a1 type disk; allocate auxiliary channel a2 type disk; allocate auxiliary channel a3 type disk; allocate auxiliary channel a4 type disk; allocate auxiliary channel a5 type disk; allocate auxiliary channel a6 type disk; set until sequence 45458; duplicate target database to "PRD"; }

© 2012 Pythian Establish custom archived log apply process 9 export ORACLE_HOME=/u01/PRD/oracle/10.2.0 export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_SID=PRD sqlplus -S "/ as sysdba" << EOF spoo recstart.lst alter database recover until cancel using backup controlfile; spoo off EOF # ORA-00279: change 5975222350297 generated at 05/08/2012 19:30:06 needed for thread 1 # ORA-00289: suggestion : /u02/PRD/archive/PRD_45035_1_694087161.arc # ORA-00280: change 5975222350297 for thread 1 is in sequence #45035 RECFILE=`grep suggestion recstart.lst | awk -F " : " '{print $2}' | xargs -n 1 basename` FULL_RECFILE=`find /u01/oradata/PRD/archive -type f -name $RECFILE -mmin +5` if [ "A${FULL_RECFILE}A" != "AA" ] then CMD="ALTER DATABASE RECOVER LOGFILE '${FULL_RECFILE}';" find /u01/oradata/PRD/archive -name "PRD_*_1_694087161.arc" -newer $FULL_RECFILE -mmin +5 | sort | sed "s,^,ALTER DATABASE RECOVER LOGFILE '," | sed "s,$,';,g" > dorecover_archlogs.sql sqlplus -S "/ as sysdba" << EOF set echo on alter database recover until cancel using backup controlfile; $CMD @dorecover_archlogs.sql EOF fi

© 2012 Pythian Complete the recovery and open the database 10 • Apply the remaining archived logs manually • Transfer and apply the required redo logs manually • v$log.archived='NO' order by SEQUENCE# • open resetlogs • alter tablespace temp add tempfile ...

© 2012 Pythian BRAINSTORM 11 How do we ensure the performance does not degrade after upgrade?

© 2012 Pythian Chosen solution – Performance management • «SQL Plan Management» to avoid degradation of Execution plans • Capture all execution plans into SQL Tuning Set (STS) in production before QA testing (Tuning Pack needed) • Duplicate PROD to QA DB • Upgrade QA to 11.2.0.3 • Import execution plans from STS into SPM (create SQL baselines) in QA • 2 iterations of testing – Evolve the baselines before the 2nd iteration of testing. • Upgrade PROD to 11.2.0.3 • Import all plans from QA SPM into PROD • Statistics • Collect statistics in QA • Import statistics from QA into PROD 12

© 2012 Pythian Capture all execution plans into SQL Tuning Set 13 SQL> BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'ALL_PLANS_FOR_11G', description => 'Plans to be imported in SQL Management Base after 11g Upgrade'); END; / PL/SQL procedure successfully completed. SQL> BEGIN DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET ( sqlset_name => 'ALL_PLANS_FOR_11G', time_limit => 86400, -- 24 hours repeat_interval => 3600, -- collect plans every hour capture_option => 'MERGE', capture_mode => DBMS_SQLTUNE.MODE_REPLACE_OLD_STATS, basic_filter => 'parsing_schema_name = ''PRD'' and executions>5', sqlset_owner => 'SYS' ); END; / PL/SQL procedure successfully completed. Useless if bind variables are not used.

© 2012 Pythian Import execution plans from STS into SPM 14 SQL> create table MEL_SQLSET_sqlid_bckup as select sql_id from WRH$_SQLTEXT where sql_id in (select sql_id from WRI$_SQLSET_STATEMENTS) and dbid=475466233; SQL> update WRH$_SQLTEXT set dbid=1675823245 where sql_id in (select sql_id from MEL_SQLSET_sqlid_bckup) and dbid=475466233; 39013 rows updated. SQL> commit; Commit complete. SQL> declare 2 n number:=0; 3 begin 4 n:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET ( 5 sqlset_name=>'ALL_PLANS_FOR_11G', 6 sqlset_owner=>'SYS', basic_filter=>NULL, 7 fixed=>'NO', enabled=>'YES', commit_rows=>1000); 8 dbms_output.put_line('PLANS LOADED: '||n); 9 end; 10 / PLANS LOADED: 37859 PL/SQL procedure successfully completed. SQL> update WRH$_SQLTEXT set dbid=475466233 where sql_id in (select sql_id from MEL_SQLSET_sqlid_bckup) and dbid=1675823245; 39013 rows updated. SQL> drop table MEL_SQLSET_sqlid_bckup; Table dropped. Statements in STS are bound to DBID I changed the DBID as part of cloning Some hacking was needed to make this work

© 2012 Pythian Evolve the baselines before the 2nd testing 15 SQL> select count(*) from dba_sql_plan_baselines where accepted='NO' and last_verified is null; COUNT(*) ---------- 42 SQL> var rep clob SQL> set timing on SQL> set pages 50000 lines 240 SQL> exec :rep := DBMS_SPM.evolve_sql_plan_baseline(); PL/SQL procedure successfully completed. Elapsed: 00:00:12.15 SQL> set long 999999999 SQL> print :rep ... ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of plans verified: 81 Number of plans accepted: 13

© 2012 Pythian Import all plans from QA SPM into PROD 16 # in QA SQL> exec DBMS_SPM.CREATE_STGTAB_BASELINE(table_name=>'SMB_UPG_11GR2',table_owner=>'SYSTEM'); SQL> var n number SQL> exec :n :=DBMS_SPM.PACK_STGTAB_BASELINE(table_name=>'SMB_UPG_11GR2',table_owner=>'SYSTEM'); PL/SQL procedure successfully completed. SQL> print n 638 SQL> ! exp file=/tmp/SMB_UPG_11GR2.dmp tables=SYSTEM.SMB_UPG_11GR2 # in PROD SQL> ! imp file=/tmp/SMB_UPG_11GR2.dmp fromuser=SYSTEM touser=SYSTEM tables=SMB_UPG_11GR2 SQL> var n number SQL> exec :n :=DBMS_SPM.UNPACK_STGTAB_BASELINE(table_name=>'SMB_UPG_11GR2',table_owner=>'SYSTEM'); PL/SQL procedure successfully completed. SQL> print n 638 SQL> select count(*) from DBA_SQL_PLAN_BASELINES; 638 SQL> drop table system.SMB_UPG_11GR2;

© 2012 Pythian Import statistics into PROD after upgrade 17 # in QA SQL> exec DBMS_STATS.CREATE_STAT_TABLE ('SYSTEM','UPG_STATS_11GR2','USERS'); SQL> exec DBMS_STATS.EXPORT_SYSTEM_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); SQL> exec DBMS_STATS.EXPORT_DICTIONARY_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); SQL> exec DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); SQL> exec DBMS_STATS.EXPORT_DATABASE_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); SQL> ! exp file=/tmp/11GR2_UPG_STATS.dmp tables=SYSTEM.UPG_STATS_11GR2 SQL> exec DBMS_STATS.DROP_STAT_TABLE(ownname=>'SYSTEM', stattab=>'UPG_STATS_11GR2'); # in PROD $ imp file=/tmp/11GR2_UPG_STATS.dmp fromuser=SYSTEM touser=SYSTEM tables=UPG_STATS_11GR2 $ rm /tmp/11GR2_UPG_STATS.dmp exec DBMS_STATS.IMPORT_SYSTEM_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); exec DBMS_STATS.IMPORT_DICTIONARY_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); exec DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); exec DBMS_STATS.IMPORT_DATABASE_STATS(stattab=>'UPG_STATS_11GR2',statown=>'SYSTEM'); exec DBMS_STATS.DROP_STAT_TABLE(ownname=>'SYSTEM', stattab=>'UPG_STATS_11GR2');

© 2012 Pythian 18 Repeatability of the upgrade process

© 2012 Pythian Repeatability of the upgrade process • Why repeatability is important • Stress • Limited time • No space for errors • Doing something different may impact the result. • Take notes while you’re testing • Commands executed • Outputs seen • Describe navigation through GUIs / take screenshots • Problems and solutions • Produce an «upgrade guide» to fallow during the PROD upgrade 19

© 2012 Pythian ? Tweet about the event: @MarisElsins and @lvoug Ask more questions: elsins@pythian.com, @MarisElsins, LVOUG mailing list Follow Pythian on twitter @pythian and LinkedIn http://linkd.in/pythian 20

Add a comment

Comments

Anne | 22/12/14
I came to your LVOUG meetup #4 - Case Study 10g to 11g, SlideSearchEngine.com page and noticed you could have a lot more hits. I have found that the key to running a website is making sure the visitors you are getting are interested in your niche. There is a company that you can get visitors from and they let you try their service for free. I managed to get over 300 targeted visitors to day to my site. Check it out here: http://qa.juststicky.com/yourls/275f

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

10g 11g | LinkedIn

View 37339 10g 11g posts, presentations, experts, and more. Get the professional knowledge you need on LinkedIn. LinkedIn Home What is LinkedIn? Join Today
Read more

LVOUG meetup #5 Tickets, Fri, Aug 3, 2012 at 4:00 PM ...

Yury is an OCP 7,8,9,10g,11g and 9i,10g,11g OCM ... Uzstāsies: Andrejs Vorobjovs, First Data Tēma: Case study: ... LVOUG meetup #5
Read more

LVOUG Meetup #4 | DBACC - DBACC | Oracle Gold certified ...

LVOUG Meetup #4 2012. gada 28. jūnijs. 28.06.2012 LVOUG rīko kārtējo minisemināru, ... Tēma: Case study: 10g upgrade to 11gR2 Valoda: Latviešu .
Read more

Upgrading Oracle Forms to the Web - A Roadmap

... Learn about Oracle's partners who can help you upgrade Read a case study of a ... Reference material for Forms 4.0 to ... 10g or 11g ...
Read more

twp upgrading 10g 11g what to expect from optimizer nov 2010

Upgrading from Oracle Database 10g to 11g: What to expect from the ... SQL Test Case Builder ... 11g plan queue for verification Figure 4 Upgrading by ...
Read more

A Case Study - Setting Up Oracle Forms 11g with OAM ...

A Case Study - Setting Up Oracle Forms 11g with OAM (Oracle Access Manager) By Bulent Seckin-Oracle on Sep 27, 2013
Read more

Sydney Oracle Meetup #5 Report: Oracle 11g New Features

Sydney Oracle Meetup #5 Report: Oracle 11g ... If you haven’t seen the case study from Oracle ... I was uncertain whether to go from 9i to 10g or 11g.
Read more

Oracle CASE SQL statement

Oracle CASE SQL statement for 9i ... when n_numb < 4 then v ... This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy ...
Read more