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-6EJOM8P9Check 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 - ProductionCheck 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(*)
----------
0Check 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 selectedCheck if there are any files which needs to be recovered
SQL> select * from v$recover_file;
no rows selectedCheck the FRA space and dest
show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string F:\app\kisha
db_recovery_file_dest_size big integer 3912MCheck for log archive dest parameter
SQL> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 stringCheck 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 selectedEnable 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
B4UPGRADEIn 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
CLOSEDCheck if any hot backups are active
SQL> select * from v$backup where status !='NOT ACTIVE';
no rows selectedCheck for database block corruption
SQL> select * from v$database_block_corruption;
no rows selectedCheck for any dba 2pc commits pending locks on the database
SQL> select * from dba_2pc_pending;
no rows selectedIf 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 selectedCheck 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 SCHEDULEDCheck 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 selectedCheck 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 KCheck 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 errorStop 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.cfCheck 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 successfullyCheck 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 saeuv07Validate 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=ORADIMStart 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(*)
----------
0Run 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 currentlyCreate 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 0Check 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 - ProductionCheck 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 11Check 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 NONEBefore 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-BF4AE6148D6DStart 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 UPGRADETruncate 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.