Upgrade Oracle 11gR2 from 11.2.0.1 to 11.2.0.4

Questo articolo si occupa di effettuare l'upgrade dalla versione 11.2.0.1 a 11.2.0.4 su sistema operativo Linux-64bit.
La Patch set di Oracle Database, versione 11.2.0.4 è un'installazione completa del software Oracle Database.
Come si legge la versione del db?
11.2.0.1
11- DB version
2 - Release
0 - Application release
1 - Component release/patch release

11.2.0.4
11- DB version
2 - Release
0 - Application release
4 - Component release/patch release

Il seguente link contiene le informazioni dell'insieme di Patch da installare:
https://community.oracle.com/community/support/support-blogs/database-support-blog/blog/2014/10/16/oracle-database-11g-release-2-patchset-11204-availability

Prima di procedere con l'installazione abilitare la modalità Archivelog e la Flashback Database.
Creare un "guarantee restore point" del database così da potere effettuare un restore del db ad uno specifico valore SCN in un qualsiasi instante di tempo anche al di là del periodo definito dal paramatro di retention DB_FLASHBACK_RETENTION_TARGET.

CREATE RESTORE POINT RP_201905231258 GUARANTEE FLASHBACK DATABASE;

select * from v$restore_point;

L'installazione verrà effettuata in una nuova oracle home (out-of-place upgrade). Una volta terminata l'installazione verranno trasferiti i dati dalla vecchia oracle home alla nuova. Il vantaggio è che non scrivendo da subito sulla vecchia oracle home l'installazione può essere bloccata in qualsiasi momento, ed è più veloce. Lo svantaggio è che richiede più spazio per via dell'istallazione di una seconda home directory.

Fare un backup della oracle home.

[oracle@coslab03 11.2.0.4]$ pwd

/u01/app/oracle/product/11.2.0.4

[oracle@coslab03 11.2.0.4]$ tar -cvf /u01/app/bcK_oraclehome_20200117 ./dbhome_1

Copiare i file in una directory.
[oracle@coslab03 SW]$ ls -lrt
-rw-r--r-- 1 root root 1395582860 May 23 13:34 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 root root 1151304589 May 23 13:37 p13390677_112040_Linux-x86-64_2of7.zip

Dopo aver effettuato l'unzip dei file viene creato la direcorty database
# unzip p13390677_112040_Linux-x86-64_1of7.zip
# unzip p13390677_112040_Linux-x86-64_2of7.zip

[oracle@coslab03 SW]$ ls -ltr
drwxr-xr-x 7 oracle oinstall 4096 Aug 27 2013 database
-rw-r--r-- 1 root root 1395582860 May 23 13:34 p13390677_112040_Linux-x86-64_1of7.zip
-rw-r--r-- 1 root root 1151304589 May 23 13:37 p13390677_112040_Linux-x86-64_2of7.zip

[oracle@coslab03 SW]$ cd database/
[oracle@coslab03 database]$ ll
drwxr-xr-x 4 oracle oinstall 4096 Aug 27 2013 install
-rw-r--r-- 1 oracle oinstall 30016 Aug 27 2013 readme.html
drwxr-xr-x 2 oracle oinstall 4096 Aug 27 2013 response
drwxr-xr-x 2 oracle oinstall 4096 Aug 27 2013 rpm
-rwxr-xr-x 1 oracle oinstall 3267 Aug 27 2013 runInstaller
drwxr-xr-x 2 oracle oinstall 4096 Aug 27 2013 sshsetup
drwxr-xr-x 14 oracle oinstall 4096 Aug 27 2013 stage
-rw-r--r-- 1 oracle oinstall 500 Aug 27 2013 welcome.html

[oracle@coslab03 database]$./runIstaller

Installare solo il nuovo software

Inserire il path della nuova Oracle home.

Non cliccare su OK ma accedere al server e lanciare lo script come root.

Al termine dello script cliccare su OK della schermata della installazione.

Accedere alla nuova oracle home e lanciare l'interfaccia grafica.

cd /u01/app/oracle/product/11.2.0.4/dbhome_1/bin
[oracle@coslab03 bin]$ ./dbua

Il warning contiene queste raccomandazioni:

The Upgrade Assistant has identified some INVALID objects in the database.
Make a note of these objects in order to compare them with the list of invalid objects at the end of the upgrade process.

SQL> SELECT count(*) FROM dba_invalid_objects;
COUNT(*)
----------------
12

SQL> SELECT distinct object_name FROM dba_invalid_objects;
OBJECT_NAME
--------------------------------------------------------------------------------
INSERISCI_CONTRATTI
GETDEPUTATO
PCK_RECAPITI
area575_T
PCK_COLLABORATORE
PCK_ANAG_TIPOLOGICHE
PCK_WCF
T1406D2
PCK_ANAG_TERRITORIALE
PCK_IMPORT_ACCESS
PCK_CONTRATTI

OBJECT_NAME
--------------------------------------------------------------------------------
T1406DP

12 rows selected.

Per identificare nuovi invalid objects dovuti all'upgrade eseguire dopo upgrade

SQL> ORACLE_HOME/rdbms/admin/utluiobj.sql

Your recycle bin contains objects. It is required that the recycle bin is empty prior to upgrading your database. Run "PURGE DBA_RECYCLEBIN" command using SQLPLUS before continuing with your upgrade.

Aprire una nuova sessione oracle e accedere con sqlplus come sysdba.

[oracle@coslab03 ~]$ export ORACLE_SID=S11TEST1
[oracle@coslab03 bin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/bin
[oracle@coslab03 bin]$ sqlplus / as sysdba
SQL> select count(*) from DBA_RECYCLEBIN;
COUNT(*)
-----------------
5
SQL> purge DBA_RECYCLEBIN;
DBA Recyclebin purged.

Oracle recommends gathering dictionary statistics prior to upgrading the database. Refer to the Upgrade Guide for instructions to gather statistics prior to upgrading the database.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.

Al termine cliccare su OK

Il nuovo spfile è stato creato sotto
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileS11TEST1.ora.

Il file di log dell'upgrade è sotto
"/u01/app/oracle/cfgtoollogs/dbua/S11TEST1/upgrade2".

V e r i f i c h e

[oracle@coslab03 dbs]$ cat /etc/oratab
....................
#
S11TEST1:/u01/app/oracle/product/11.2.0.4/dbhome_1:Y

[oracle@coslab03 ContentsXML]$ more /u01/app/oraInventory/ContentsXML/inventory.xml
<?xml version="1.0" standalone="yes" ?>


<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.4.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME="OraDb11g_home1" LOC="/u01/app/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="OraDB12Home1" LOC="/u01/app/oracle/product/12.2.0/dbhome_1" TYPE="O" IDX="2"/>
<HOME NAME="OraDb11g_home2" LOC="/u01/app/oracle/product/11.2.0.4/dbhome_1" TYPE="O" IDX="3"/>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY>

Copiare il file di configurazione del listener.

[oracle@coslab03 admin]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@coslab03 admin]$ ls -lrt
total 20
-rw-r--r-- 1 oracle oinstall 187 May 7 2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Nov 19 2018 samples
-rw-r----- 1 oracle oinstall 352 Feb 14 11:40 listener.ora
-rw-r----- 1 oracle oinstall 274 Feb 14 11:40 sqlnet.ora
-rw-r----- 1 oracle oinstall 1490 Apr 8 12:39 tnsnames.ora
[oracle@coslab03 admin]$ cp listener.ora sqlnet.ora tnsnames.ora /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin

Occorre cambiare la directory del Listener.

[oracle@coslab03 admin]$ ps -ef | grep tns

root 21 2 0 Mar15 ? 00:00:00 [netns]

oracle 7666 24422 0 17:04 pts/0 00:00:00 grep tns

oracle 25900 1 0 May02 ? 00:03:45 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit

[oracle@coslab03 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAY-2019 17:56:43

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=coslab03.intra.camera.it)(PORT=1521)))

STATUS of the LISTENER

---------------------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 02-MAY-2019 11:09:31

Uptime 26 days 6 hr. 47 min. 12 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/coslab03/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=coslab03.intra.camera.it)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "S11TEST1.intra.camera.it" has 1 instance(s).

Instance "S11TEST1", status READY, has 1 handler(s) for this service...

Service "S11TEST1XDB.intra.camera.it" has 1 instance(s).

Instance "S11TEST1", status READY, has 1 handler(s) for this service...

The command completed successfully

Settare la variabile d'ambiente

[oracle@coslab03 ~]$ . oraenv

ORACLE_SID = [S11TEST1] ?

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@coslab03 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0.4/dbhome_1

[oracle@coslab03 ~]$

Stop e start del Listener

[[oracle@coslab03 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-MAY-2019 18:00:05
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=coslab03.intra.camera.it)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 28-MAY-2019 17:59:59
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/coslab03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=coslab03.intra.camera.it)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Altre verifiche sulla versione del database attualmente installata.

SQL> select instance_name, host_name, version, status from v$instance;

INSTANCE_NAME HOST_NAME VERSION STATUS
----------------- ----------------------------
S11TEST1 coslab03 11.2.0.4.0 OPEN

SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> column COMP_NAME format a34

SQL> select comp_name, version, status from dba_registry;

COMP_NAME VERSION STATUS

---------------------------------- ------------------------------

OWB 11.2.0.1.0 VALID

Oracle Application Express 3.2.1.00.10 VALID

Oracle Enterprise Manager 11.2.0.4.0 VALID

OLAP Catalog 11.2.0.4.0 VALID

Spatial 11.2.0.4.0 VALID

Oracle Multimedia 11.2.0.4.0 VALID

Oracle XML Database 11.2.0.4.0 VALID

Oracle Text 11.2.0.4.0 VALID

Oracle Expression Filter 11.2.0.4.0 VALID

Oracle Rules Manager 11.2.0.4.0 VALID

Oracle Workspace Manager 11.2.0.4.0 VALID

COMP_NAME VERSION STATUS

---------------------------------- ------------------------------

Oracle Database Catalog Views 11.2.0.4.0 VALID

Oracle Database Packages and Types 11.2.0.4.0 VALID

JServer JAVA Virtual Machine 11.2.0.4.0 VALID

Oracle XDK 11.2.0.4.0 VALID

Oracle Database Java Packages 11.2.0.4.0 VALID

OLAP Analytic Workspace 11.2.0.4.0 VALID

Oracle OLAP API 11.2.0.4.0 VALID

18 rows selected.

SQL> column SOURCE_VERSION format a25
SQL> select to_char(ACTION_TIME,'dd-mm-yyyy hh24:mi:ss') upgrade_date, COMMENTS source_version, VERSION as target_version from dba_registry_history where ACTION ='UPGRADE';

UPGRADE_DATE SOURCE_VERSION TARGET_VERSION
------------------- ------------------------- ----------------------
28-05-2019 16:37:23 Upgraded from 11.2.0.1.0 11.2.0.4.0

Modificare la variabile Oracle_home

[oracle@coslab03 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@coslab03 ~]$ cd /home/oracle/

[oracle@coslab03 ~]$ vi .bash_profile

###############

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

# User specific environment and startup programs

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

# oracle 11

ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1; export ORACLE_HOME

PATH=$PATH:$ORACLE_HOME/bin:/usr/sbin; export PATH

Per rendere consistente le modifiche

[oracle@coslab03 ~]$ . ./.bash_profile

[oracle@coslab03 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/11.2.0.4/dbhome_1

Dopo l'upgrade per identificare gli oggetti invalidi eseguire lo script seguente:

cd $ORACLE_HOME/rdbms/admin/
SQL> @utluiobj.sql

Cerca nel blog

Upgrade Oracle 11gR2 from 11.2.0.1 to 11.2.0.4

Post popolari in questo blog

ORA-12154: TNS: il listener non è attualmente a conoscenza del servizio richiesto nel descrittore di connessione

Create e Drop Pluggable Database

Estrarre un file BLOB da un campo Oracle - Sql Developer