In următoarele rânduri voi descrie cat mai detaliat pașii prin care voi upgrada Oracle RDBMS 11.2.0.4 la versiunea 12.1.0.2. Motivele pentru a upgrada nu le voi descrie acum, dar o motivație suficient de puternică este descrisă în Doc ID 161818.1 de pe support.oracle.com :
Patching for 11.2.0.1 ended on 13/Sep/2011
Patching for 11.2.0.2 ended on 31/Oct/2013
Patching for 11.2.0.3 ended on 27/Aug/2015
Patching for 11.1.0.7 ended on 31/Aug/2015 for most platforms.
iar 12.1.0.2 este ultima versiune din 12c Release 1 (cu alte cuvinte nu va exista 12.1.0.3)
Revenind la upgrade, din punctul meu de vedere poate părea destul de stufos, iar din acest motiv există riscul de a greși ori să apară o situație neprevazută, de exemplu un bug, de aceea este indicat ca prima dată să întocmiți un plan de backup iar abia după aceea să luați deciziile cu privire la upgrade.
Metodele identificate de mine prin care se poate realiza upgrade-ul:
– Database Upgrade Assistant (DBUA)
– Manual Upgrade (script based)
– Export/Import
– Transport tablespace
Fiecare metoda prezintă diferite particularități iar în funcție de necesități se alege una din cele patru metode pentru a duce la bun sfârșit un upgrade. „Database Upgrade Assistant” este un utilitar ușor de folosit iar cea mai mare parte a procesului se va face cu ajutorul mouse-ului. Manual Upgrade este o modalitate mai transparenta pentru cine dorește „sa dețină controlul”. Ultimele doua metode prezintă avantajul de a realiza o data cu upgrade-ul și migrarea pe un alt server.
In acest articolul voi descrie manual upgrade. Pentru aceasta metodă este necesar ca versiunea de bază de date care urmează să fie upgradată să se regăsească în una din cele de mai jos :
-Oracle Database 10g (10.2.0.5)
-Oracle Database 11g (11.1.0.7)
-Oracle Database 11g (11.2.0.2 sau mai nouă)
In exemplul meu am folosit RDBMS 11.2.0.4 peste cate am aplicat patch-ul 20760982, pe aceeași mașină (OEL x86 64) am instalat binarele RDBMS 12.1.0.2 peste care am aplicat patch-ul 20831110.
Următorul pas este analiza documentului „Things to Consider Before Upgrading to 12.1.0.2 to Avoid Poor Performance or Wrong Results (Doc ID 2034610.1)”
Înainte de a rula scripturile de pre-upgrade trebuie verificat dacă există o versiune mai nouă a acestor scripturi: „How to Download and Run Oracle’s Database Pre-Upgrade Utility (Doc ID 884522.1)” , eu am descarcat noile scripturi și le-am înlocuit pe cele din binarele Oracle:
[oracle@scott admin]$ pwd
/opt/oracle/product/12.1.0/dbhome/rdbms/admin
[oracle@scott admin]$ mv preupgrd.sql preupgrd.sql.old
[oracle@scott admin]$ mv utluppkg.sql utluppkg.sql.old
[oracle@scott admin]$ mv /opt/preupgrd.sql /opt/oracle/product/12.1.0/dbhome/rdbms/admin/
[oracle@scott admin]$ mv /opt/utluppkg.sql /opt/oracle/product/12.1.0/dbhome/rdbms/admin/
După urmarea tuturor pașilor de mai sus este timpul să pregătim baza de upgrade:
- creez un restore point în cazul în care upgrade-ul va eșua și va fi nevoie să revin asupra modificărilor făcute
SQL> create restore point BEFORE_UPGRADE guarantee flashback database;
- colectez statistici pentru obiectele de system
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
- compilarea obiectelor invalide
SQL> @?/rdbms/admin/utlrp.sql
- setez HOME-ul Oracle pentru binarele de 12c, în cazul meu acestea sunt în:
export ORACLE_HOME=/opt/oracle/product/12.1.0/dbhome
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
- trebuie menționat că în noua versiune RDBMS 12c componenta Oracle Enterprise Manager a fost înlocuită prin urmare vechiul repository din 11g nu mai este întreținut, de asemenea opțiunea OLAP disponibilă în 11g nu mai este suportată, de aceea dacă nu le folosiți și doriți un log al scriptului de upgrade cat mai curat este recomandat să ștergeți înainte cele două componente (scripturile se găsesc în binarele de 12c!):
SQL>@?/rdbms/admin/emremove.sql ..... The Oracle Enterprise Manager related schemas and objects are dropped. SQL> @?/olap/admin/catnoamd.sql
- în acest moment rulam scriptul de preupgrade pentru verificări
SQL> @?/rdbms/admin/preupgrd.sql Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in ORCL... *************************************************************************** ************************************************************ ====>> ERRORS FOUND for ORCL <<==== The following are *** ERROR LEVEL CONDITIONS *** that must be addressed prior to attempting your upgrade. Failure to do so will result in a failed upgrade. You MUST resolve the above errors prior to upgrade ************************************************************ ************************************************************ ====>> PRE-UPGRADE RESULTS for ORCL <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks: /opt/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade: /opt/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade: /opt/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql ************************************************************ *************************************************************************** Pre-Upgrade Checks in ORCL Completed. *************************************************************************** *************************************************************************** ***************************************************************************
Acest script va genera trei fișiere după cum urmează:
- preupgrade.log : aici găsiți rezultatul tuturor verificărilor realizate de preupgrd.sql. Log-ul va trebui analizat și de văzut dacă se poate continua procesul de migrare.
- preupgrade_fixups.sql : acest script va trebui rulat înainte de migrare
- postupgrade_fixups.sql : acest script va trebui rulat după migrare
SQL> !cat /opt/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log Oracle Database Pre-Upgrade Information Tool 10-16-2015 18:24:29 Script Version: 12.1.0.2.0 Build: 010 ********************************************************************** Database Name: ORCL Container Name: Not Applicable in Pre-12.1 database Container ID: Not Applicable in Pre-12.1 database Version: 11.2.0.4.0 Compatible: 11.2.0.4.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone file: V14 ********************************************************************** [Update parameters] --> If Target Oracle is 64-bit, refer here for Update Parameters: WARNING: --> "memory_target" needs to be increased to at least 650117120 ********************************************************************** ********************************************************************** [Renamed Parameters] [No Renamed Parameters in use] ********************************************************************** ********************************************************************** [Obsolete/Deprecated Parameters] [No Obsolete or Desupported Parameters in use] ********************************************************************** [Component List] ********************************************************************** --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] VALID --> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle Multimedia [upgrade] VALID --> Oracle Spatial [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID --> Oracle OLAP API [upgrade] VALID ********************************************************************** [Tablespaces] ********************************************************************** --> SYSTEM tablespace is adequate for the upgrade. minimum required size: 1258 MB --> SYSAUX tablespace is adequate for the upgrade. minimum required size: 1349 MB --> UNDOTBS1 tablespace is adequate for the upgrade. minimum required size: 400 MB --> TEMP tablespace is adequate for the upgrade. minimum required size: 60 MB --> EXAMPLE tablespace is adequate for the upgrade. minimum required size: 309 MB [No adjustments recommended] ********************************************************************** ********************************************************************** [Pre-Upgrade Checks] ********************************************************************** INFORMATION: --> Older Timezone in use Database is using a time zone file older than version 18. After the upgrade, it is recommended that DBMS_DST package be used to upgrade the 11.2.0.4.0 database time zone version to the latest version which comes with the new release. Please refer to My Oracle Support note number 1509653.1 for details. INFORMATION: --> There are existing Oracle components that will NOT be upgraded by the database upgrade script. Typically, such components have their own upgrade scripts, are deprecated, or obsolete. Those components are: OWB INFORMATION: --> Oracle Application Express (APEX) can be manually upgraded prior to database upgrade APEX is currently at version 3.2.1.00.12 and will need to be upgraded to APEX version 4.2.5 in the new release. Note 1: To reduce database upgrade time, APEX can be manually upgraded outside of and prior to database upgrade. Note 2: See MOS Note 1088970.1 for information on APEX installation upgrades. ********************************************************************** [Pre-Upgrade Recommendations] ********************************************************************** ***************************************** ********* Dictionary Statistics ********* ***************************************** Please gather dictionary statistics 24 hours prior to upgrading the database. To gather dictionary statistics execute the following command while connected as SYSDBA: EXECUTE dbms_stats.gather_dictionary_stats; ^^^ MANUAL ACTION SUGGESTED ^^^ ********************************************************************** [Post-Upgrade Recommendations] ********************************************************************** ***************************************** ******** Fixed Object Statistics ******** ***************************************** Please create stats on fixed objects two weeks after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; ^^^ MANUAL ACTION SUGGESTED ^^^ ********************************************************************** ************ Summary ************ 0 ERRORS exist in your database. 0 WARNINGS exist in your database. 3 INFORMATIONAL messages that should be reviewed prior to your upgrade. After your database is upgraded and open in normal mode you must run rdbms/admin/catuppst.sql which executes several required tasks and completes the upgrade process. You should follow that with the execution of rdbms/admin/utlrp.sql, and a comparison of invalid objects before and after the upgrade using rdbms/admin/utluiobj.sql If needed you may want to upgrade your timezone data using the process described in My Oracle Support note 1509653.1 ***********************************
Din log reiese că versiunea Timezone-ului va trebui actualizată, după upgrade va trebui realizat și acest lucru. Celelalte atenționări le pot ignora deoarece nu folosesc cele două componente.
SQL> @/opt/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql SQL> shutdown immediate;
Copiez fișierul de parametrii și parole în binarele de 12c:
$ cp /opt/oracle/product/11.2.0/dbhome/dbs/spfileorcl.ora /opt/oracle/product/12.1.0/dbhome/dbs/ $ cp /opt/oracle/product/11.2.0/dbhome/dbs/orapworcl /opt/oracle/product/12.1.0/dbhome/dbs/
Acum cu binarele de 12c pornesc baza de date:
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 578813952 bytes Fixed Size 2926952 bytes Variable Size 461375128 bytes Database Buffers 109051904 bytes Redo Buffers 5459968 bytes Database mounted. Database opened.
Si in cele din urmă rulez catupgrd cu toți parametrii impliciți:
$ cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql Argument list for [catctl.pl] SQL Process Count n = 0 SQL PDB Process Count N = 0 Input Directory d = 0 Phase Logging Table t = 0 Log Dir l = 0 Script s = 0 Serial Run S = 0 Upgrade Mode active M = 0 Start Phase p = 0 End Phase P = 0 Log Id i = 0 Run in c = 0 Do not run in C = 0 Echo OFF e = 1 No Post Upgrade x = 0 Reverse Order r = 0 Open Mode Normal o = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 Display Phases y = 0 Child Process I = 0 catctl.pl version: 12.1.0.2.0 Oracle Base = /opt/oracle/ Analyzing file catupgrd.sql Log files in /opt/oracle/product/12.1.0/dbhome/rdbms/admin catcon: ALL catcon-related output will be written to catupgrd_catcon_5130.lst catcon: See catupgrd*.log files for output generated by scripts catcon: See catupgrd_*.lst files for spool files, if any Number of Cpus = 1 SQL Process Count = 0 New SQL Process Count = 4 ------------------------------------------------------ Phases [0-73] Start Time:[2015_10_16 16:09:04] ------------------------------------------------------ Serial Phase #: 0 Files: 1 Time: 157s Serial Phase #: 1 Files: 5 Time: 31s Restart Phase #: 2 Files: 1 Time: 0s Parallel Phase #: 3 Files: 18 Time: 19s Restart Phase #: 4 Files: 1 Time: 0s Serial Phase #: 5 Files: 5 Time: 15s Serial Phase #: 6 Files: 1 Time: 11s ................. Serial Phase #:70 Files: 1 Time: 1184s Serial Phase #:71 Files: 1 Time: 0s Serial Phase #:72 Files: 1 Time: 0s Serial Phase #:73 Files: 1 Time: 19s ------------------------------------------------------ Phases [0-73] End Time:[2015_10_16 19:43:02] ------------------------------------------------------ Grand Total Time: 4317s LOG FILES: (catupgrd*.log) Upgrade Summary Report Located in: /opt/oracle/product/12.1.0/dbhome/cfgtoollogs/orcl/upgrade/upg_summary.log Grand Total Upgrade Time: [0d:1h:11m:57s]
In acest moment baza de date este oprită, o pornesc și rulez scriptul postupgrade_fixups
SQL> startup SQL> @/opt/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql Post Upgrade Fixup Script Generated on 2015-10-16 18:24:27 Version: 12.1.0.2 Build: 010 Beginning Post-Upgrade Fixups... ......................... PL/SQL procedure successfully completed.
Urmatoarele scripturi nu sunt necesare dar este recomandat să fie rulate după migrare:
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed. SQL> @utlrp.sql PL/SQL procedure successfully completed. SQL> @utluiobj.sql ........................ PL/SQL procedure successfully completed. SQL> @utlu121s.sql PL/SQL procedure successfully completed.
In acest moment migrarea este aproape gata:
SQL> SELECT version, prv_version,banner FROM sys.registry$ ; VERSION PRV_VERSION BANNER ---------- ------------------------------ -------------------------------------------------------------------------------- 12.1.0.2.0 11.2.0.4.0 Oracle Database Catalog Views Release 12.1.0.2.0 - 64bit Production 12.1.0.2.0 11.2.0.4.0 Oracle Database Packages and Types Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle Workspace Manager Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 JServer JAVA Virtual Machine Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle XDK Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle Database Java Packages Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle Text Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle XML Database Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle Multimedia Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 OLAP Analytic Workspace Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Oracle OLAP API Release 12.1.0.2.0 - Production 12.1.0.2.0 11.2.0.4.0 Spatial Release 12.1.0.2.0 - Production 4.2.5.00.0 3.2.1.00.12.8 Oracle Application Express Release 4.2.5.00.08 - Production 11.2.0.4.0 OWB Release 11.2.0.4.0 - Development 14 rows selected.
A mai ramas de actualizat Timezone-ul de la versiunea 14 la 18 si putem considera migrarea finalizată cu succes!
Pasii pentru actualizarea Timezone-ului sunt din urmatorul document: Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)
SQL> SELECT version FROM v$timezone_file; VERSION ---------- ########## SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 2 3 4 PROPERTY_NAME VALUE ------------------------------ ------------------------------ DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL> !ls -ltr total 68 -rw-r--r-- 1 oracle oinstall 31010 Aug 22 2014 upg_tzv_check.sql -rw-r--r-- 1 oracle oinstall 19502 Aug 22 2014 upg_tzv_apply.sql -rw-r--r-- 1 oracle oinstall 6294 Jan 8 2015 countstarTSTZ.sql -rw-r--r-- 1 oracle oinstall 6909 Jan 8 2015 countstatsTSTZ.sql SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY; COUNT(*) ---------- 1401 SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY; COUNT(*) ---------- 33191 SQL> select systimestamp - dbms_stats.get_stats_history_availability from dual; SYSTIMESTAMP-DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY --------------------------------------------------------------------------- +000000782 18:06:44.919544000 SQL> exec dbms_stats.alter_stats_history_retention(0); PL/SQL procedure successfully completed. SQL> exec DBMS_STATS.PURGE_STATS(systimestamp); PL/SQL procedure successfully completed. SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY; COUNT(*) ---------- 0 SQL> select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY; COUNT(*) ---------- 0 SQL> spool countstatsTSTZ.log SQL> @countstatsTSTZ.sql SQL> spool off SQL> exec dbms_scheduler.purge_log; PL/SQL procedure successfully completed. SQL> spool upg_tzv_check.log SQL> @upg_tzv_check.sql INFO: Starting with RDBMS DST update preparation. INFO: NO actual RDBMS DST update will be done by this script. INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: Doing checks for known issues ... INFO: Database version is 12.1.0.2 . INFO: Database RDBMS DST version is DSTv14 . INFO: No known issues detected. INFO: Now detecting new RDBMS DST version. A prepare window has been successfully started. INFO: Newest RDBMS DST version detected is DSTv18 . INFO: Next step is checking all TSTZ data. INFO: It might take a while before any further output is seen ... A prepare window has been successfully ended. INFO: A newer RDBMS DST version than the one currently used is found. INFO: Note that NO DST update was yet done. INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update. INFO: Note that the upg_tzv_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. SQL> spool off spool upg_tzv_apply.log SQL> @upg_tzv_apply.sql .... INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv18 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this sqlplus session. INFO: Do not use it for timezone related selects. SQL> spool off SQL> exec dbms_stats.alter_stats_history_retention(11); PL/SQL procedure successfully completed. SQL> SELECT version FROM v$timezone_file; VERSION ---------- 18 1 row selected.
oradbaro 