UPGRADE 11.2.0.1 TO 11.2.0.4 ORACLE IN WINDOWS IN STANDARD EDITION

Database upgrade in windows is quite different with linux in terms of manual upgrade. If you are get used to linux commandline, then you will get stuck at tough times in cmd where you search for commands a lot So get used to cmd commands before upgrade of database with downtime.

  • OS – Windows
  • Database edition – Standard
  • Current Database version – 11.2.0.1
  • Upgrade Database version – 11.2.0.4 + Latest PSU

Take full backup and drive backup of database location or copy the files to different drive

Verify the application compatibility with new database version

Verify the edition of the database (Enterprise or standard)

Install 11.2.0.4 software in different oracle home

Check for drive space on the oracle home location and c drive

E:\app\product\11.2.0\dbhome_2\OPatch>wmic diskdrive get size
Size
128034708480
1000202273280



E:\app\product\11.2.0\dbhome_2\OPatch>fsutil volume diskfree c:
Total # of free bytes        : 14641127424
Total # of bytes             : 126905290752
Total # of avail free bytes  : 14641127424




E:\app\product\11.2.0\dbhome_2\OPatch>fsutil volume diskfree e:
Total # of free bytes        : 65659297792
Total # of bytes             : 314572795904
Total # of avail free bytes  : 65659297792

Check the details of your database and take a snap of it

SQL> set lines 200 pages 1000
SQL> set lines 200 pages 1000
SQL> col DB_UNIQUE_NAME for a15
SQL> col OPEN_MODE for a15
SQL> col host_name for a15
SQL> select name,instance_name,db_unique_name,open_mode,database_role,controlfile_type,log_mode,flashback_on,to_char(startup_time,’dd-mm-yyyy hh24:mi:ss’) startup_time,host_name from v$database,v$instance;

NAME      INSTANCE_NAME    DB_UNIQUE_NA OPEN_MODE    DATABASE_ROLE    CONTROL LOG_MODE     FLASHBACK_ON       STARTUP_TIME        HOST_NAME
--------- ---------------- ------------ ------------ ---------------- ------- ------------ ------------------ ------------------- -----------------
SAEUV07   saeuv07          saeuv07      READ WRITE   PRIMARY          CURRENT ARCHIVELOG   NO              02-07-2021 23:37:33 LAPTOP-6EJOM8P9

Check the current version of the database and other releases

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Check the components of the database which needs to be upgraded. Note that, the upgrade estimated elapsed time depends on the total number of components

SQL> set lines 200 pages 1000
SQL> col COMP_ID for a20
SQL> col COMP_NAME for a20
SQL> col STATUS for a20
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS
---------- ---------------------------------------- ------------------------------ -----------------
OWB        OWB                                      11.2.0.1.0                     VALID
APEX       Oracle Application Express               3.2.1.00.10                    VALID
EM         Oracle Enterprise Manager                11.2.0.1.0                     VALID
AMD        OLAP Catalog                             11.2.0.1.0                     VALID
SDO        Spatial                                  11.2.0.1.0                     VALID
ORDIM      Oracle Multimedia                        11.2.0.1.0                     VALID
XDB        Oracle XML Database                      11.2.0.1.0                     VALID
CONTEXT    Oracle Text                              11.2.0.1.0                     VALID
EXF        Oracle Expression Filter                 11.2.0.1.0                     VALID
RUL        Oracle Rules Manager                     11.2.0.1.0                     VALID
OWM        Oracle Workspace Manager                 11.2.0.1.0                     VALID
CATALOG    Oracle Database Catalog Views            11.2.0.1.0                     VALID
CATPROC    Oracle Database Packages and Types       11.2.0.1.0                     VALID
JAVAVM     JServer JAVA Virtual Machine             11.2.0.1.0                     VALID
XML        Oracle XDK                               11.2.0.1.0                     VALID
CATJAVA    Oracle Database Java Packages            11.2.0.1.0                     VALID
APS        OLAP Analytic Workspace                  11.2.0.1.0                     VALID
XOQ        Oracle OLAP API                          11.2.0.1.0                     VALID

18 rows selected.

Check all the NLS properties of the database and take a note of it

SQL> select property_name,property_value from database_properties where property_name like '%NLS_%' order by 1;

PROPERTY_NAME                     PROPERTY_VALUE
--------------------------------- ---------------------------------
NLS_CALENDAR                      GREGORIAN
NLS_CHARACTERSET                  AL32UTF8
NLS_COMP                          BINARY
NLS_CURRENCY                      $
NLS_DATE_FORMAT                   DD-MON-RR
NLS_DATE_LANGUAGE                 AMERICAN
NLS_DUAL_CURRENCY                 $
NLS_ISO_CURRENCY                  AMERICA
NLS_LANGUAGE                      AMERICAN
NLS_LENGTH_SEMANTICS              BYTE
NLS_NCHAR_CHARACTERSET            AL16UTF16
NLS_NCHAR_CONV_EXCP               FALSE
NLS_NUMERIC_CHARACTERS            .,
NLS_RDBMS_VERSION                 11.2.0.1.0
NLS_SORT                          BINARY
NLS_TERRITORY                     AMERICA
NLS_TIMESTAMP_FORMAT              DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT           DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT                   HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                HH.MI.SSXFF AM TZR

20 rows selected.

Check the timezone file version

SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

Check for invalid objects

SQL> select count(*) from dba_objects where status='INVALID' and owner not in ('SYS','SYSTEM');

  COUNT(*)
----------
         0

Check for dependencies with respect to referential constraints

SQL> select * from dba_dependencies where referenced_name in ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

OWNER                          NAME                           TYPE               REFERENCED_OWNER       REFERENCED_NAME                                                  REFERENCED_TYPE
------------------------------ ------------------------------ ------------------ ------------------------------ ---------------------------------------------------------------- ------------------
REFERENCED_LINK_NAME                                                                                                     DEPE
-------------------------------------------------------------------------------------------------------------------------------- ----
MDSYS                          SDO_OLS                        PACKAGE BODY       PUBLIC                 UTL_HTTP                                                         SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_PRINT_UTIL            PACKAGE BODY       APEX_030200            UTL_HTTP                                                         SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_HELP                  PACKAGE BODY       APEX_030200            UTL_HTTP                                                         SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_LDAP                  PACKAGE            PUBLIC                 DBMS_LDAP                                                        SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_LDAP                  PACKAGE BODY       PUBLIC                 DBMS_LDAP                                                        SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_MAIL                  PACKAGE BODY       APEX_030200            UTL_SMTP                                                         SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_WEB_SERVICES          PACKAGE BODY       APEX_030200            UTL_HTTP                                                         SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_CUSTOM_AUTH_LDAP      PACKAGE BODY       PUBLIC                 DBMS_LDAP                                                        SYNONYM
                                                                                                                         HARD

APEX_030200                    WWV_FLOW_DISP_PAGE_PLUGS       PACKAGE BODY       APEX_030200            UTL_HTTP                                                         SYNONYM
                                                                                                                         HARD

APEX_030200                    UTL_HTTP                       SYNONYM            SYS                    UTL_HTTP                                                         PACKAGE
                                                                                                                         HARD

APEX_030200                    UTL_SMTP                       SYNONYM            SYS                    UTL_SMTP                                                         PACKAGE
                                                                                                                         HARD
11 rows selected.

Check for any MV refresh

SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

no rows selected

Check if there are any files which needs to be recovered

SQL> select * from v$recover_file;

no rows selected

Check the FRA space and dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      F:\app\kisha
db_recovery_file_dest_size           big integer 3912M

Check for log archive dest parameter

SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string

Check if guaranteed restore point is enabled

SQL> select flashback_on,current_scn,dbid from v$database;

FLASHBACK_ON       CURRENT_SCN       DBID
------------------ ----------- ----------
NO                     1856826 2761984782

SQL> select * from v$restore_point;

no rows selected

Enable guaranteed restore point for backout plan

SQL> create restore point B4UPGRADE guarantee flashback database;

Restore point created.

SQL> select * from v$restore_point;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME
---------- --------------------- --- ------------ ---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------------------------------------------------------
   1860906                     2 YES     52428800 08-JUL-21 05.46.00.000000000 PM
                                                                            YES
B4UPGRADE

In case of failure during upgrade, follow the below steps

SQL> select current_scn from v$database;
SQL> shutdown immediate;
SQL> startup mount;
SQL> select * from v$restore_point;
SQL> flashback database to restore point PREUPG_SCN;
SQL> alter database open resetlogs;
SQL> select current_scn from v$database;

Check for open wallet

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS
------------------
file
E:\APP\ADMIN\SAEUV07\WALLET
CLOSED

Check if any hot backups are active

SQL> select * from v$backup where status !='NOT ACTIVE';

no rows selected

Check for database block corruption

SQL> select * from v$database_block_corruption;

no rows selected

Check for any dba 2pc commits pending locks on the database

SQL> select * from dba_2pc_pending;

no rows selected

If there is standby database , check for sync between primary and standby

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

no rows selected

Check for any scheduled jobs enabled on the system

SQL> set pagesize 1000
SQL> set lines 200
SQL> set long 10000000
SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs where owner not in('SYS','SYSTEM');

OWNER                          JOB_NAME                       ENABL STATE
------------------------------ ------------------------------ ----- ---------------
EXFSYS                         RLM$EVTCLEANUP                 TRUE  SCHEDULED
EXFSYS                         RLM$SCHDNEGACTION              TRUE  SCHEDULED
ORACLE_OCM                     MGMT_CONFIG_JOB                TRUE  SCHEDULED
ORACLE_OCM                     MGMT_STATS_CONFIG_JOB          TRUE  SCHEDULED

Check default tablespace for sys and system users

SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM                         SYSTEM
SYS                            SYSTEM

Check for external authenticated ssl users

SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';

no rows selected

Check for oracle shadow processes running

C:\Windows\system32>tasklist |findstr "ora"
oracle.exe                    5620 Services                   0   4,37,824 K
oracle.exe                    9500 Services                   0   6,32,272 K
oravssw.exe                   8588 Services                   0     15,660 K

Check if oracle vsswriter services run

E:\app\product\11.2.0\dbhome_2\OPatch>vssadmin list writers
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001-2013 Microsoft Corp.

Writer name: 'Oracle VSS Writer - saeuv07'
   Writer Id: {26d02976-b909-43ad-af7e-62a4f625e372}
   Writer Instance Id: {357f494a-6fb7-4745-93de-c9614b6b75ea}
   State: [1] Stable
   Last error: No error

Stop the vss service related to oracle

E:\app\product\11.2.0\dbhome_2\OPatch>net stop OracleVssWritersaeuv07
The Oracle saeuv07 VSS Writer Service service is stopping.
The Oracle saeuv07 VSS Writer Service service was stopped successfully.

Run the preupgrade script from oracle home

SQL> @F:\app\kisha\product\11.2.0\dbhome_1\rdbms\admin\utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool    07-03-2021 23:05:02
.
**********************************************************************
Database:
**********************************************************************
--> name:          SAEUV07
--> version:       11.2.0.1.0
--> compatible:    11.2.0.0.0
--> blocksize:     8192
--> platform:      Microsoft Windows x86 64-bit
--> timezone file: V11
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 692 MB
.... AUTOEXTEND additional space required: 12 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 464 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 410 MB
.... AUTOEXTEND additional space required: 340 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 32 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "java_pool_size" needs to be increased to at least 128 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> 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
--> OLAP Catalog                 [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
....   SYS
WARNING: --> log_archive_format must be updated.
.... As of 10.1, log_archive_format requires a %r format qualifier
.... be present in its format string.  Your current setting is:
.... log_archive_format='ARC%S_%R.%T'.
.... Archive Logging is currently ON, and failure to add the %r to the
.... format string will prevent the upgraded database from starting up.
WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command:  PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.

PL/SQL procedure successfully completed.

Do not proceed further if you face any errors in the log until you fix it. Below are the recommended fixups from the script

SQL> exec dbms_stats.gather_schema_stats(ownname=>'SYS');

PL/SQL procedure successfully completed.

SQL> purge recyclebin;

Recyclebin purged.

SQL> alter system set log_archive_format='ARC%S_%R_%r.%T' scope=spfile;

System altered.cf

Check for non default parameters and their values

SQL> select num,name,value FROM V$PARAMETER where isdefault='FALSE';

       NUM NAME                 VALUE
---------- -------------------- --------------------
        31 processes            150
       578 memory_target        9428795392
       605 control_files        F:\APP\KISHA\ORADATA
                                \SAEUV07\CONTROL01.C
                                TL, F:\APP\KISHA\SAE
                                UV07\CONTROL02.CTL

       624 db_block_size        8192
       758 compatible           11.2.0.0.0
       856 log_archive_format   ARC%S_%R.%T
       915 db_recovery_file_des F:\app\kisha
           t

       916 db_recovery_file_des 4102029312
           t_size

      1168 undo_tablespace      UNDOTBS1
      1412 remote_login_passwor EXCLUSIVE
           dfile

      1423 db_domain
      1440 dispatchers          (PROTOCOL=TCP) (SERV
                                ICE=saeuv07XDB)

      1555 audit_file_dest      F:\APP\KISHA\ADMIN\S
                                AEUV07\ADUMP

      1587 audit_trail          DB
      1607 db_name              saeuv07
      1609 open_cursors         300
      2359 diagnostic_dest      F:\APP\KISHA

17 rows selected.

Create pfile from spfile and copy the listener and pfile to the other home

SQL> create pfile='F:\app\kisha\product\11.2.0\dbhome_1\dbs\initsaeuv07.ora' from spfile;

File created.


F:\app\kisha\product\11.2.0\dbhome_1\dbs>copy initsaeuv07.ora E:\app\product\11.2.0\dbhome_1\dbs\
        1 file(s) copied.


F:\app\kisha\product\11.2.0\dbhome_1\NETWORK\ADMIN>copy * E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\
listener.ora
Overwrite E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora? (Yes/No/All): Yes
sqlnet.ora
Overwrite E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora? (Yes/No/All): All
sqlnet2107041PM4104.bak
sqlnet2107041PM4402.bak
tnsnames.ora
        5 file(s) copied.

set ORACLE SID and ORACLE HOME variables for the database for new home 11.2.0.4

F:\app\kisha\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=saeuv07

F:\app\kisha\product\11.2.0\dbhome_1\BIN>set ORACLE_HOME=E:\app\product\11.2.0\dbhome_1\

Shutdown the database instance and listener of 11.2.0.1

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

F:\app\kisha\product\11.2.0\dbhome_1\BIN>lsnrctl stop listener1

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 04-JUL-2021 14:01:18

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=LAPTOP-6EJOM8P9)(PORT=1521)))
The command completed successfully

Check if oracle services are running

E:\app\product\11.2.0\dbhome_1\BIN>sc query |findstr "ora"
DISPLAY_NAME: Storage Service
DISPLAY_NAME: User Data Storage_8d521cb

E:\app\product\11.2.0\dbhome_1\BIN>sc query |findstr "saeu"

If previous old service run, then delete the old service and start new one

F:\app\kisha\product\11.2.0\dbhome_1\BIN>sc delete saeuv07

Validate the invalid objects using utlrp

SQL> @F:\app\kisha\product\11.2.0\dbhome_1\rdbms\admin\utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2021-07-04 14:51:34

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2021-07-04 14:51:37

DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Delete old sid and create new one with auto startmode

C:\Windows\system32>oradim -delete -sid saeuv07

C:\Windows\system32>oradim -new -sid saeuv07 -STARTMODE auto
Message 51 not found; No message file for product=RDBMS, facility=ORADIM

Start the database in upgrade mode

C:\Windows\system32>set ORACLE_SID=saeuv07

C:\Windows\system32>
C:\Windows\system32>
C:\Windows\system32>set ORACLE_HOME=E:\app\product\11.2.0\dbhome_2

C:\Windows\system32>sqlplus / as sysdba

SQL> startup upgrade;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'E:\APP\PRODUCT\11.2.0\DBHOME_2\DATABASE\INITSAEUV07.ORA'
SQL> create spfile from pfile='E:\APP\PRODUCT\11.2.0\DBHOME_2\dbs\initsaeuv07.ora';

File created.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 9386692608 bytes
Fixed Size                  2289080 bytes
Variable Size            4865393224 bytes
Database Buffers         4496293888 bytes
Redo Buffers               22716416 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 5 16:01:07 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Check if there are active sessions connected

SQL> select count(*) from v$session where username not in ('SYS','SYSTEM') and status='ACTIVE';

  COUNT(*)
----------
         0

Run the catupgrd script to start the upgrade

SQL> spool catupgrd.log
SQL> @E:\app\product\11.2.0\dbhome_2\rdbms\admin\catupgrd
SQL> spool off;

Check for any errors on the log post upgrade

C:\Windows\system32>type catupgrd.log |findstr "ora-"

Validate all the components in dba registry

SQL> set lines 200
SQL> set pages 1000
SQL> select comp_id,status,version from dba_registry;

COMP_ID                        STATUS                                       VERSION
------------------------------ -------------------------------------------- ------------------------------
OWB                            VALID                                        11.2.0.1.0
APEX                           VALID                                        3.2.1.00.10
EM                             VALID                                        11.2.0.4.0
AMD                            VALID                                        11.2.0.4.0
SDO                            VALID                                        11.2.0.4.0
ORDIM                          VALID                                        11.2.0.4.0
XDB                            VALID                                        11.2.0.4.0
CONTEXT                        VALID                                        11.2.0.1.0
EXF                            VALID                                        11.2.0.4.0
RUL                            VALID                                        11.2.0.4.0
OWM                            VALID                                        11.2.0.4.0
CATALOG                        VALID                                        11.2.0.4.0
CATPROC                        VALID                                        11.2.0.4.0
JAVAVM                         VALID                                        11.2.0.4.0
XML                            VALID                                        11.2.0.4.0
CATJAVA                        VALID                                        11.2.0.4.0
APS                            VALID                                        11.2.0.4.0
XOQ                            VALID                                        11.2.0.4.0

18 rows selected.

Perform post checks by validating all components and for errors

SQL> spool postcheck.log
SQL> @E:\app\product\11.2.0\dbhome_2\rdbms\admin\utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool           07-05-2021 19:47:06
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:47:44
JServer JAVA Virtual Machine
.                                         VALID      11.2.0.4.0  00:08:13
Oracle Workspace Manager
.                                         VALID      11.2.0.4.0  00:00:30
OLAP Analytic Workspace
.                                         VALID      11.2.0.4.0  00:00:30
OLAP Catalog
.                                         VALID      11.2.0.4.0  00:00:56
Oracle OLAP API
.                                         VALID      11.2.0.4.0  00:00:25
Oracle Enterprise Manager
.                                         VALID      11.2.0.4.0  00:04:02
Oracle XDK
.                                         VALID      11.2.0.4.0  00:00:33
Oracle Text
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/ctx/admin/ctxpatch.sql"
.                                         VALID      11.2.0.1.0  00:00:00
Oracle XML Database
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/dbmsxmlu.sql"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/dbmsxmls.sql"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/prvtxmlstreams.plb"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/dbmsxtr.sql"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/prvtxtr.plb"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/dbmsxmlu.sql"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/dbmsxmls.sql"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/prvtxmlstreams.plb"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/dbmsxtr.sql"
.   SP2-0310: unable to open file
"E:\app\product\11.2.0\dbhome_1/rdbms/admin/prvtxtr.plb"
.                                         VALID      11.2.0.4.0  00:02:06
Oracle Database Java Packages
.                                         VALID      11.2.0.4.0  00:00:13
Oracle Multimedia
.                                         VALID      11.2.0.4.0  00:02:48
Spatial
.                                         VALID      11.2.0.4.0  00:03:40
Oracle Expression Filter
.                                         VALID      11.2.0.4.0  00:00:15
Oracle Rules Manager
.                                         VALID      11.2.0.4.0  00:00:07
Oracle Application Express
.                                         VALID     3.2.1.00.10
Final Actions
.                                                                00:00:02
Total Upgrade Time: 01:12:12

PL/SQL procedure successfully completed.

SQL> @E:\app\product\11.2.0\dbhome_2\rdbms\admin\catuppst

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2021-07-05 19:49:53


PL/SQL procedure successfully completed.


This script will migrate the Baseline data on a pre-11g database
to the 11g database.

...                                       ...
... Completed Moving the Baseline Data    ...
...                                       ...
... If there are no Move BL Data messages ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...
...                                       ...
... Completed the Dropping of the         ...
... Renamed Baseline Tables               ...
...                                       ...
... If there are no Drop Table messages   ...
... above, then there are no renamed      ...
... baseline tables in the system.        ...
...                                       ...

PL/SQL procedure successfully completed.


0 rows created.


Commit complete.


Table created.


2 rows created.


1 row updated.


2 rows updated.


0 rows updated.


Table dropped.


Commit complete.


0 rows updated.


Commit complete.


0 rows updated.


Commit complete.


0 rows updated.


Commit complete.


0 rows created.


Commit complete.


0 rows created.


Commit complete.


PL/SQL procedure successfully completed.






TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2021-07-05 19:49:55


PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
E:\app\cfgtoollogs\catbundle\catbundle_PSU_SAEUV07_GENERATE_2021Jul05_19_50_18.log
Apply script: E:\app\product\11.2.0\dbhome_2\rdbms\admin\catbundle_PSU_SAEUV07_APPLY.sql
Rollback script: E:\app\product\11.2.0\dbhome_2\rdbms\admin\catbundle_PSU_SAEUV07_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT 'E:\app\cfgtoollogs\catbundle\' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;


SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '11.2.0.4',
  9     0,
 10     'PSU',
 11     'Patchset 11.2.0.2.0');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
E:\app\cfgtoollogs\catbundle\catbundle_PSU_SAEUV07_APPLY_2021Jul05_19_50_21.log
SQL> @E:\app\product\11.2.0\dbhome_2\rdbms\admin\utlrp

TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2021-07-05 19:50:57

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2021-07-05 19:51:41

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL>
SQL> spool off
not spooling currently

Create listener in new database home

C:\Windows\system32>set ORACLE_HOME=E:\app\product\11.2.0\dbhome_2

C:\Windows\system32>netca

Oracle Net Services Configuration:
Configuring Listener:LISTENER2
Listener configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      E:\app\product\11.2.0\dbhome_2\bin\lsnrctl start LISTENER2
    Listener Control complete.
    Setting Listener service to start automatically.
    Listener started successfully.
Oracle Net Services configuration successful. The exit code is 0

Check compatibility parameter and update to latest version

SQL>    show parameter compatible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.0.0

SQL> alter system set compatible='11.2.0.4.0' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 9386692608 bytes
Fixed Size                  2289080 bytes
Variable Size            4865393224 bytes
Database Buffers         4496293888 bytes
Redo Buffers               22716416 bytes
Database mounted.
Database opened.

Check the tools and database version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
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 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Check the timezone file for current version

E:\app\product\11.2.0\dbhome_1\oracore\zoneinfo>type readme.txt |more
Current Structure version: 3
Current Content Version  :14

Content Version 14
------------------
Timezones updated:
please refer to
$ORACLE_HOME/oracore/zoneinfo/timezdif.csv.

Timezones added:
America/Bahia_Banderas
America/Matamoros
America/Ojinaga
America/Santa_Isabel
Antarctica/Macquarie

------ End of updates in Content version 14 ---

Current Structure version: 3
Current Content Version  :11

Content Version 11
------------------
Timezones updated:
please refer to
$ORACLE_HOME/oracore/zoneinfo/timezdif.csv.

Timezones added:

------ End of updates in Content version 11 ---


Current Structure version: 3
Current Content Version  :10

Content Version 10
------------------
Timezones updated:
please refer to
$ORACLE_HOME/oracore/zoneinfo/timezdif.csv.

Timezones added:
America/Argentina/Salta

------ End of updates in Content version 10 ----

Check timezone file version

SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_11.dat              11

Check version for primary and secondary dst timezones

SQL> set lines 200
SQL> set pages 1000
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE 'DST_%'
  4  ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ ---------------------------------------
DST_PRIMARY_TT_VERSION         11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

Before timezone upgrade certain hidden parameters should be modified and recyclebin should be purged

SQL> alter session set "_with_subquery"=materialize;

Session altered.

SQL> alter session set "_simple_view_merging"=TRUE;

Session altered.

SQL> purge recyclebin;

Recyclebin purged.

Set dbms application info to monitor the timezone upgrade. Start the timezone upgrade prepare window.

SQL> exec dbms_application_info.set_client_info('up_tz');

PL/SQL procedure successfully completed.

-- start the prepare window
SQL> exec dbms_dst.begin_prepare(14);

PL/SQL procedure successfully completed.

SQL> exec DBMS_DST.FIND_AFFECTED_TABLES;

PL/SQL procedure successfully completed.

--check for errors

SQL> select * from sys.dst$affected_tables;

no rows selected

SQL> select * from sys.dst$error_table;

no rows selected

SQL> exec dbms_dst.end_prepare;

PL/SQL procedure successfully completed.

documentation from oracle to fix timezone errors
--https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-6377A1C4-F76A-4C53-82CD-BF4AE6148D6D

Start the dst in upgrade mode

--begin dst timezone upgrade
SQL> exec dbms_dst.begin_upgrade(14);

PL/SQL procedure successfully completed.

After upgrade check the dst primary and secondary version and dst upgrade state

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME                  VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE              UPGRADE

Truncate the error and trigger tables

SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

Upgrade the TSTZ data in user table

SQL> set timing on
--begin TSTZ upgrade
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
--end upgrade
SQL> set timing off
SQL> set timing on
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SQL> set timing off

Upgrade the timezone in registry

Timezone file was already upgraded and the registry$database will not be upgraded due to preupgrade information stored in it

SQL> SELECT VERSION FROM v$timezone_file;

   VERSION
----------
        14

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        11

SQL> set timing on
SQL> update registry$database
  2  set TZ_VERSION = (select version
  3  FROM v$timezone_file);

1 row updated.

SQL> commit;

Commit complete.

SQL> set timing off

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        14

help : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-6377A1C4-F76A-4C53-82CD-BF4AE6148D6D

Drop restore point

SQL> drop restore point B4UPGRADE;

Restore point dropped.

Start vss writer

E:\app\product\11.2.0\dbhome_2\OPatch>net start OracleVssWritersaeuv07
The Oracle saeuv07 VSS Writer Service service is starting.
The Oracle saeuv07 VSS Writer Service service was started successfully.

Check for latest version and inventory of opatch

E:\app\product\11.2.0\dbhome_2\OPatch>opatch version
OPatch Version: 11.2.0.3.4

OPatch succeeded.
E:\app\product\11.2.0\dbhome_2\OPatch>opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : E:\app\product\11.2.0\dbhome_2
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : E:\app\product\11.2.0\dbhome_2\cfgtoollogs\opatch\opatch2021-07-08_14-37-34PM_1.log

Lsinventory Output file location : E:\app\product\11.2.0\dbhome_2\cfgtoollogs\opatch\lsinv\lsinventory2021-07-08_14-37-34PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s