0d38c19

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.

Lasă un răspuns

Completează mai jos detaliile despre tine sau dă clic pe un icon pentru autentificare:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s