Hello Friend's,
In this post we will discuss about the steps to upgrade Oracle RAC database (2 instance dm01db01 & dmdb1db02) from 12.2.0.1 to 19.16
So let's get started .
Below is the enviornment details we will be using in this activity.
RAC nodes : dm01db01, dm01db02
DB Name : CDBTRAIN
DB Instances : CDBTRAIN1, CDBTRAIN2
PDB : TRAINDB
Current DB version : 12.2.0.1
DB to be upgraded to version : 19.16
Cluster Storage used : ASM (AFD)
Platform : OEL 7.8
Current DB HOME : /oracle/app/orawork/product/12.2.0.1/db_1
New 12c DB HOME : /oracle/app/orawork/product/19.0.0.0/db_home1
Upgrade Method : Manual
1.Install 19c Software with Patch of 19.16 34130714
[oracle@dm01db01 db_home1]$ ./runInstaller -applyPSU /oracle/34130714 -applyOneOffs /oracle/34130714 -silent -responseFile /home/oracle/db19.rsp -ignorePrereqFailure
Preparing the home to patch...
Applying the patch /oracle/34130714...
Successfully applied the patch.
Applying the patch /oracle/34130714...
Successfully applied the patch.
The log can be found at: /grid/app/gridwork/oraInventory/logs/InstallActions2022-10-27_02-04-08AM/installerPatchActions_2022-10-27_02-04-08AM.log
Launching Oracle Database Setup Wizard...
[WARNING] [INS-13013] Target environment does not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /grid/app/gridwork/oraInventory/logs/InstallActions2022-10-27_02-04-08AM/installActions2022-10-27_02-04-08AM.log
ACTION: Identify the list of failed prerequisite checks from the log: /grid/app/gridwork/oraInventory/logs/InstallActions2022-10-27_02-04-08AM/installActions2022-10-27_02-04-08AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
/oracle/app/orawork/product/19.0.0.0/db_home1/install/response/db_2022-10-27_02-04-08AM.rsp
You can find the log of this install session at:
/grid/app/gridwork/oraInventory/logs/InstallActions2022-10-27_02-04-08AM/installActions2022-10-27_02-04-08AM.log
As a root user, execute the following script(s):
1. /oracle/app/orawork/product/19.0.0.0/db_home1/root.sh
Execute /oracle/app/orawork/product/19.0.0.0/db_home1/root.sh on the following nodes:
[dm01db01, dm01db02]
Successfully Setup Software with warning(s).
2.Run root.sh scripts :
[root@dm01db02 ~]# /oracle/app/orawork/product/19.0.0.0/db_home1/root.sh
Check /oracle/app/orawork/product/19.0.0.0/db_home1/install/root_dm01db02.database.com_2022-10-27_02-45-32-491748899.log for the output of root script
[root@dm01db01 product]# /oracle/app/orawork/product/19.0.0.0/db_home1/root.sh
Check /oracle/app/orawork/product/19.0.0.0/db_home1/install/root_dm01db01.database.com_2022-10-27_02-44-47-854192382.log for the output of root script
3.Prechecks of database 12c :
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;SQL> SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- -----------------------------
1 CDBTRAIN1 CDBTRAIN dm01db01.database.com PRIMARY ARCHIVELOG READ WRITE 12.2.0.1.0 ALLOWED 27-OCT-2022 01:32:18
2 CDBTRAIN2 CDBTRAIN dm01db02.database.com PRIMARY ARCHIVELOG READ WRITE 12.2.0.1.0 ALLOWED 27-OCT-2022 01:32:18
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TRAINDB READ WRITE NO
SQL> select comp_id,status from dba_registry;
COMP_ID STATUS
------------------------------ --------------------------------------------
CATALOG VALID
CATPROC VALID
JAVAVM VALID
XML VALID
CATJAVA VALID
APS VALID
RAC VALID
XDB VALID
OWM VALID
CONTEXT VALID
ORDIM VALID
SDO VALID
XOQ VALID
OLS VALID
DV VALID
15 rows selected.
SQL> select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM'; 2 3 4 5 6 7
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
4. Time to run the preupgrade tool :
[oracle@dm01db01 db_1]$ pwd
/oracle/app/orawork/product/12.2.0.1/db_1
[oracle@dm01db01 db_1]$ cd /oracle/app/orawork/product/12.2.0.1/db_1/jdk/bin
[oracle@dm01db01 bin]$ java -jar /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2022-10-27T02:55:39
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: CDBTRAIN
Container Name: CDB$ROOT
Container ID: 1
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
_ipddb_enable
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
2. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade using
the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
APS 12.2.0.1.0 VALID SYS
CATALOG 12.2.0.1.0 VALID SYS
CATJAVA 12.2.0.1.0 VALID SYS
CATPROC 12.2.0.1.0 VALID SYS
CONTEXT 12.2.0.1.0 VALID CTXSYS
DV 12.2.0.1.0 VALID DVSYS
JAVAVM 12.2.0.1.0 VALID SYS
OLS 12.2.0.1.0 VALID LBACSYS
ORDIM 12.2.0.1.0 VALID ORDSYS
OWM 12.2.0.1.0 VALID WMSYS
RAC 12.2.0.1.0 VALID SYS
SDO 12.2.0.1.0 VALID MDSYS
XDB 12.2.0.1.0 VALID XDB
XML 12.2.0.1.0 VALID SYS
XOQ 12.2.0.1.0 VALID OLAPSYS
Review the information before upgrading.
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 480 MB 502 MB
SYSTEM 800 MB 912 MB
TEMP 39 MB 150 MB
UNDOTBS1 70 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
5. No action needed.
Using default parallel upgrade options, this CDB with 2 PDBs will first
upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2
parallel processes per PDB.
The number of PDBs upgraded in parallel and the number of parallel
processes per PDB can be adjusted as described in Database Upgrade Guide.
6. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
7. Here is a count of invalid objects by Oracle-maintained users:
Oracle-Maintained User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
8. Here is a count of invalid objects by Application users:
Application User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDBTRAIN container CDB$ROOT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade_fixups.s
ql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
9. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
10. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
11. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDBTRAIN container CDB$ROOT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postupgrade_fixups.
sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2022-10-27T03:01:23
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: CDBTRAIN
Container Name: PDB$SEED
Container ID: 2
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: TRUE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
_ipddb_enable
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
2. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade using
the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
APS 12.2.0.1.0 VALID SYS
CATALOG 12.2.0.1.0 VALID SYS
CATJAVA 12.2.0.1.0 VALID SYS
CATPROC 12.2.0.1.0 VALID SYS
CONTEXT 12.2.0.1.0 VALID CTXSYS
DV 12.2.0.1.0 VALID DVSYS
JAVAVM 12.2.0.1.0 VALID SYS
OLS 12.2.0.1.0 VALID LBACSYS
ORDIM 12.2.0.1.0 VALID ORDSYS
OWM 12.2.0.1.0 VALID WMSYS
RAC 12.2.0.1.0 VALID SYS
SDO 12.2.0.1.0 VALID MDSYS
XDB 12.2.0.1.0 VALID XDB
XML 12.2.0.1.0 VALID SYS
XOQ 12.2.0.1.0 VALID OLAPSYS
Review the information before upgrading.
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 350 MB 500 MB
SYSTEM 250 MB 360 MB
TEMP 64 MB 150 MB
UNDOTBS1 100 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
5. Here is a count of invalid objects by Oracle-maintained users:
Oracle-Maintained User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
6. Here is a count of invalid objects by Application users:
Application User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDBTRAIN container PDB$SEED
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade_fixups.s
ql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
7. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
8. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
9. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDBTRAIN container PDB$SEED
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postupgrade_fixups.
sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 13 on 2022-10-27T03:01:26
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: CDBTRAIN
Container Name: TRAINDB
Container ID: 3
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
_ipddb_enable
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
2. (AUTOFIXUP) Gather statistics on fixed objects prior to the upgrade using
the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
APS 12.2.0.1.0 VALID SYS
CATALOG 12.2.0.1.0 VALID SYS
CATJAVA 12.2.0.1.0 VALID SYS
CATPROC 12.2.0.1.0 VALID SYS
CONTEXT 12.2.0.1.0 VALID CTXSYS
DV 12.2.0.1.0 VALID DVSYS
JAVAVM 12.2.0.1.0 VALID SYS
OLS 12.2.0.1.0 VALID LBACSYS
ORDIM 12.2.0.1.0 VALID ORDSYS
OWM 12.2.0.1.0 VALID WMSYS
RAC 12.2.0.1.0 VALID SYS
SDO 12.2.0.1.0 VALID MDSYS
XDB 12.2.0.1.0 VALID XDB
XML 12.2.0.1.0 VALID SYS
XOQ 12.2.0.1.0 VALID OLAPSYS
Review the information before upgrading.
4. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 350 MB 500 MB
SYSTEM 250 MB 361 MB
TEMP 64 MB 150 MB
UNDO_2 100 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
5. Here is a count of invalid objects by Oracle-maintained users:
Oracle-Maintained User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
6. Here is a count of invalid objects by Application users:
Application User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDBTRAIN container TRAINDB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade_fixups.s
ql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
7. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
8. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
9. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database CDBTRAIN container TRAINDB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postupgrade_fixups.
sql
==================
PREUPGRADE SUMMARY
==================
/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade.log
/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade_fixups.sql
/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postupgrade_fixups.sql
Execute fixup scripts across the entire CDB:
Before upgrade:
1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/ -b preup_CDBTRAIN /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade_fixups.sql
2. Review logs under /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/
After the upgrade:
1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/ -b postup_CDBTRAIN /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postupgrade_fixups.sql
2. Review logs under /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/
Preupgrade complete: 2022-10-27T03:01:41
5.Run preupgrade fixup.sql
[oracle@dm01db01 bin]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/ -b preup_CDBTRAIN /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade//preup_CDBTRAIN_catcon_75212.lst]
catcon: See [/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade//preup_CDBTRAIN*.log] files for output generated by scripts
catcon: See [/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade//preup_CDBTRAIN_*.lst] files for spool files, if any
catcon.pl: completed successfully
6. Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
SQL>
SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
Prechecks continues 12c database :
7.Create Restore_point for flashback the incase upgrade fails :
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL>
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECOC1
db_recovery_file_dest_size big integer 13380M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>
SQL> select * from V$restore_point;
no rows selected
SQL>
SQL> create restore point FB_UPGRADE guarantee flashback database;
Restore point created.
SQL> col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;SQL> SQL> SQL> SQL>
NAME GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
FB_UPGRADE YES 26-OCT-22 09.16.11.000000000 PM
8. Check the timezone version:
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
26
9.Disable dbms_schduler jobs:
SQL> set pagesize 2000
set lines 2000
set long 99999
select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;SQL> SQL> SQL>
OWNER JOB_NAME ENABL STATE
------------------------- ---------------------------------------- ----- ---------------
SYS PURGE_LOG TRUE SCHEDULED
SYS ORA$AUTOTASK_CLEAN TRUE SCHEDULED
SYS HM_CREATE_OFFLINE_DICTIONARY FALSE DISABLED
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SCHEDULED
SYS ORA$PREPLUGIN_BACKUP_JOB FALSE DISABLED
SYS BSLN_MAINTAIN_STATS_JOB TRUE SCHEDULED
SYS FGR$AUTOPURGE_JOB FALSE DISABLED
SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SCHEDULED
SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SCHEDULED
SYS LOAD_OPATCH_INVENTORY FALSE DISABLED
SYS LOAD_OPATCH_INVENTORY_2 FALSE DISABLED
SYS LOAD_OPATCH_INVENTORY_1 FALSE DISABLED
ORACLE_OCM MGMT_CONFIG_JOB TRUE SCHEDULED
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE SCHEDULED
SYS FILE_WATCHER FALSE DISABLED
SYS PMO_DEFERRED_GIDX_MAINT_JOB TRUE SCHEDULED
SYS CLEANUP_NON_EXIST_OBJ TRUE SCHEDULED
SYS CLEANUP_ONLINE_IND_BUILD TRUE SCHEDULED
SYS CLEANUP_TAB_IOT_PMO TRUE SCHEDULED
SYS CLEANUP_TRANSIENT_TYPE TRUE SCHEDULED
SYS CLEANUP_TRANSIENT_PKG TRUE SCHEDULED
SYS CLEANUP_ONLINE_PMO TRUE SCHEDULED
SYS FILE_SIZE_UPD TRUE SCHEDULED
SYS XMLDB_NFS_CLEANUP_JOB FALSE DISABLED
24 rows selected.
--- Disable the scheduled jobs by using below command
SQL> execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);
10.Verify system and sys default tablespace.(Both should be system tablespace)
SQL> col username for a30
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM'); 2 3
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
11.Check whether database has any externally authenticated SSL users
SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL'; 2 3
no rows selected
12.Review and Remove any unnecessary hidden/underscore parameters
SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
NAME
--------------------
VALUE
-------------------------
_ipddb_enable
TRUE
13.Stop database from 12.2 Home and remove database from 12.2 oracle restart
[oracle@dm01db01 bin]$ srvctl status database -d CDBTRAIN
Instance CDBTRAIN1 is running on node dm01db01
Instance CDBTRAIN2 is running on node dm01db02
[oracle@dm01db01 bin]$
[oracle@dm01db01 bin]$ srvctl stop database -d CDBTRAIN
[oracle@dm01db01 bin]$
[oracle@dm01db01 bin]$ srvctl remove database -d CDBTRAIN -f
14.Set 19c Env As below
export ORACLE_BASE=/oracle/app/orawork
export ORALCE_HOME=/oracle/app/orawork/product/19.0.0/dbhome_1
export PATH=/oracle/app/orawork/product/19.0.0/dbhome_1/bin:$PATH
spfile : +DATAC1/CDBTRAIN/PARAMETERFILE/spfile.270.1119144227
password file : +DATAC1/CDBTRAIN/PASSWORD/pwdcdbtrain.258.1119142913
srvctl add database -d CDBTRAIN -oh /oracle/app/orawork/product/19.0.0/dbhome_1 -spfile +DATAC1/CDBTRAIN/PARAMETERFILE/spfile.270.1119144227 -pwfile +DATAC1/CDBTRAIN/PASSWORD/pwdcdbtrain.258.1119142913
15. Create pfile and edit parameters given below :
1. instance_number
2. thread
3. 2nd instance's undo tablespace.
4. cluster_database
Do as given below :
[oracle@dm01db01 bin]$ cat /oracle/CDBTRAIN.ora | grep ^#
#*.cluster_database=true
#CDBTRAIN2.instance_number=2
#CDBTRAIN1.instance_number=1
#CDBTRAIN2.thread=2
#CDBTRAIN1.thread=1
#CDBTRAIN2.undo_tablespace='UNDOTBS2'
16. Start the database from 1 instance with the pfile created in above step :
[oracle@dm01db01 db_home1]$ . oraenv
ORACLE_SID = [CDBTRAIN1] ?
The Oracle base remains unchanged with value /oracle/app/orawork
[oracle@dm01db01 db_home1]$
[oracle@dm01db01 db_home1]$
[oracle@dm01db01 db_home1]$ echo $ORACLE_HOME
/oracle/app/orawork/product/19.0.0.0/db_home1
[oracle@dm01db01 db_home1]$
[oracle@dm01db01 db_home1]$
[oracle@dm01db01 db_home1]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 03:30:20 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade pfile='/oracle/CDBTRAIN.ora';
ORACLE instance started.
Total System Global Area 1073738488 bytes
Fixed Size 9143032 bytes
Variable Size 557842432 bytes
Database Buffers 503316480 bytes
Redo Buffers 3436544 bytes
Database mounted.
Database opened.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
SQL> SQL> col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- -----------------------------
1 CDBTRAIN1 CDBTRAIN dm01db01.database.com PRIMARY ARCHIVELOG READ WRITE 19.0.0.0.0 RESTRICTED 27-OCT-2022 03:30:38
17. Start PDB in migrate / Upgrade options :
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 TRAINDB MOUNTED
SQL>
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- ---------- --- ----------------- ------------
CDBTRAIN READ WRITE YES 19.0.0.0.0 OPEN MIGRATE
SQL>
SQL> ALTER PLUGGABLE DATABASE TRAINDB OPEN UPGRADE;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 TRAINDB MIGRATE YES
old methold with long command :)
nohup /oracle/app/orawork/product/19.0.0/dbhome_1/perl/bin/perl catctl.pl -l /oracle/app/orawork/upgradelogs -n 4 catupgrd.sql &
--Or--
New Short method
./dbupgrade
18.Actual Upgrade to 19c Start Here (Start database in upgrade mode)
[oracle@dm01db01 db_1]$ cd /oracle/app/orawork/product/19.0.0/dbhome_1/bin/
[oracle@dm01db01 bin]$
[oracle@dm01db01 bin]$ pwd
/oracle/app/orawork/product/19.0.0/dbhome_1/bin
[oracle@dm01db01 bin]$ ls -ltr dbupgrade
-rwxr-x--- 1 oracle oinstall 3136 Apr 17 2019 dbupgrade
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 00:28:00 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade pfile='/oracle/initCDBATM.ora'
ORACLE instance started.
Total System Global Area 1073738488 bytes
Fixed Size 9143032 bytes
Variable Size 520093696 bytes
Database Buffers 541065216 bytes
Redo Buffers 3436544 bytes
Database mounted.
Database opened.
SQL>
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
CDBATM dm01db01.database.com PRIMARY READ WRITE ARCHIVELOG 19.0.0.0.0 RESTRICTED 27-OCT-2022 00:28:09
In NONCDB script runs only once but IN CDB env it depends on No or pdbs including Seed pdb, In my case 1 has 1 PDB so the total executions are 3
1.CDB$ROOT
2.PDB$SEED
3.TRAINDB
All things will be done automatically no manual intervention needed after running ./dbupgrade
19. Start with the upgrade
[oracle@dm01db01 bin]$ ./dbupgrade
Argument list for [/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]
/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/orahome = [/oracle/app/orawork/product/19.0.0.0/db_home1]
/oracle/app/orawork/product/19.0.0.0/db_home1/bin/orabasehome = [/oracle/app/orawork/product/19.0.0.0/db_home1]
catctlGetOraBaseLogDir = [/oracle/app/orawork/product/19.0.0.0/db_home1]
Analyzing file /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20221027033305]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20221027033305/catupgrd_catcon_93650.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221027033305/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20221027033305/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = CDBTRAIN
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdcdbroot_catcon_93650.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdcdbroot*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdcdbroot_*.lst] files for spool files, if any
Log file directory = [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307]
PDB Parallel SQL Process Count = [2] is higher or equal to CPU Count = [1]
Concurrent PDB Upgrades defaulting to CPU Count [1]
Parallel SQL Process Count (PDB) = 2
Parallel SQL Process Count (CDB$ROOT) = 4
Concurrent PDB Upgrades = 1
Generated PDB Inclusion:[PDB$SEED TRAINDB]
Components in [CDB$ROOT]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM WK]
DataBase Version = 12.2.0.1.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_10_27 03:33:10]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [CDB$ROOT] Files:1 Time: 41s
*************** Catalog Core SQL ***************
Serial Phase #:1 [CDB$ROOT] Files:5 Time: 94s
Restart Phase #:2 [CDB$ROOT] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [CDB$ROOT] Files:19 Time: 89s
Restart Phase #:4 [CDB$ROOT] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [CDB$ROOT] Files:7 Time: 31s
***************** Catproc Start ****************
Serial Phase #:6 [CDB$ROOT] Files:1 Time: 16s
***************** Catproc Types ****************
Serial Phase #:7 [CDB$ROOT] Files:2 Time: 16s
Restart Phase #:8 [CDB$ROOT] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [CDB$ROOT] Files:70 Time: 77s
Restart Phase #:10 [CDB$ROOT] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [CDB$ROOT] Files:1 Time: 90s
Restart Phase #:12 [CDB$ROOT] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [CDB$ROOT] Files:95 Time: 28s
Restart Phase #:14 [CDB$ROOT] Files:1 Time: 1s
Parallel Phase #:15 [CDB$ROOT] Files:122 Time: 55s
Restart Phase #:16 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:17 [CDB$ROOT] Files:25 Time: 5s
Restart Phase #:18 [CDB$ROOT] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [CDB$ROOT] Files:32 Time: 43s
Restart Phase #:20 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:21 [CDB$ROOT] Files:3 Time: 15s
Restart Phase #:22 [CDB$ROOT] Files:1 Time: 0s
Parallel Phase #:23 [CDB$ROOT] Files:25 Time: 375s
Restart Phase #:24 [CDB$ROOT] Files:1 Time: 0s
Parallel Phase #:25 [CDB$ROOT] Files:12 Time: 213s
Restart Phase #:26 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:27 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:28 [CDB$ROOT] Files:4 Time: 4s
Serial Phase #:29 [CDB$ROOT] Files:1 Time: 0s
Restart Phase #:30 [CDB$ROOT] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [CDB$ROOT] Files:1 Time: 1s
Restart Phase #:32 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:34 [CDB$ROOT] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [CDB$ROOT] Files:297 Time: 29s
Serial Phase #:36 [CDB$ROOT] Files:1 Time: 0s
Restart Phase #:37 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:38 [CDB$ROOT] Files:10 Time: 3s
Restart Phase #:39 [CDB$ROOT] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [CDB$ROOT] Files:3 Time: 67s
Restart Phase #:41 [CDB$ROOT] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [CDB$ROOT] Files:13 Time: 228s
Restart Phase #:43 [CDB$ROOT] Files:1 Time: 0s
Parallel Phase #:44 [CDB$ROOT] Files:11 Time: 15s
Restart Phase #:45 [CDB$ROOT] Files:1 Time: 0s
Parallel Phase #:46 [CDB$ROOT] Files:3 Time: 2s
Restart Phase #:47 [CDB$ROOT] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [CDB$ROOT] Files:1 Time: 10s
Restart Phase #:49 [CDB$ROOT] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [CDB$ROOT] Files:1 Time: 8s
************ Upgrade Component Start ***********
Serial Phase #:51 [CDB$ROOT] Files:1 Time: 1s
Restart Phase #:52 [CDB$ROOT] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [CDB$ROOT] Files:2 Time: 397s
***************** Upgrading XDB ****************
Restart Phase #:54 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:56 [CDB$ROOT] Files:3 Time: 6s
Serial Phase #:57 [CDB$ROOT] Files:3 Time: 3s
Parallel Phase #:58 [CDB$ROOT] Files:10 Time: 3s
Parallel Phase #:59 [CDB$ROOT] Files:25 Time: 9s
Serial Phase #:60 [CDB$ROOT] Files:4 Time: 10s
Serial Phase #:61 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:62 [CDB$ROOT] Files:32 Time: 4s
Serial Phase #:63 [CDB$ROOT] Files:1 Time: 0s
Parallel Phase #:64 [CDB$ROOT] Files:6 Time: 6s
Serial Phase #:65 [CDB$ROOT] Files:2 Time: 21s
Serial Phase #:66 [CDB$ROOT] Files:3 Time: 36s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:69 [CDB$ROOT] Files:1 Time: 2s
Parallel Phase #:70 [CDB$ROOT] Files:2 Time: 63s
Restart Phase #:71 [CDB$ROOT] Files:1 Time: 1s
Parallel Phase #:72 [CDB$ROOT] Files:2 Time: 1s
Serial Phase #:73 [CDB$ROOT] Files:2 Time: 0s
***************** Upgrading SDO ****************
Restart Phase #:74 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:76 [CDB$ROOT] Files:1 Time: 74s
Serial Phase #:77 [CDB$ROOT] Files:2 Time: 2s
Restart Phase #:78 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:79 [CDB$ROOT] Files:1 Time: 50s
Restart Phase #:80 [CDB$ROOT] Files:1 Time: 1s
Parallel Phase #:81 [CDB$ROOT] Files:3 Time: 130s
Restart Phase #:82 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:83 [CDB$ROOT] Files:1 Time: 5s
Restart Phase #:84 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:85 [CDB$ROOT] Files:1 Time: 9s
Restart Phase #:86 [CDB$ROOT] Files:1 Time: 1s
Parallel Phase #:87 [CDB$ROOT] Files:4 Time: 213s
Restart Phase #:88 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:89 [CDB$ROOT] Files:1 Time: 1s
Restart Phase #:90 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:91 [CDB$ROOT] Files:2 Time: 14s
Restart Phase #:92 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:93 [CDB$ROOT] Files:1 Time: 1s
Restart Phase #:94 [CDB$ROOT] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [CDB$ROOT] Files:1 Time: 15s
Restart Phase #:96 [CDB$ROOT] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [CDB$ROOT] Files:1 Time: 3s
************* Final Upgrade scripts ************
Serial Phase #:98 [CDB$ROOT] Files:1 Oracle Interim Patch Installer version 12.2.0.1.33
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /oracle/app/orawork/product/19.0.0.0/db_home1
Central Inventory : /grid/app/gridwork/oraInventory
from : /oracle/app/orawork/product/19.0.0.0/db_home1/oraInst.loc
OPatch version : 12.2.0.1.33
OUI version : 12.2.0.7.0
Log file location : /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/opatch/opatch2022-10-27_04-17-27AM_1.log
Lsinventory Output file location : /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/opatch/lsinv/lsinventory2022-10-27_04-17-27AM.txt
--------------------------------------------------------------------------------
Generated xml file /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/target_home_inventory.xml from Oracle Home inventory shown above.
OPatch succeeded.
Time: 67s
******************* Migration ******************
Serial Phase #:99 [CDB$ROOT] Files:1 Time: 1s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [CDB$ROOT] Files:1 Time: 2s
Serial Phase #:101 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:102 [CDB$ROOT] Files:1 Time: 58s
***************** Post Upgrade *****************
Serial Phase #:103 [CDB$ROOT] Files:1 Time: 2s
**************** Summary report ****************
Serial Phase #:104 [CDB$ROOT] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:106 [CDB$ROOT] Files:1 Time: 1s
Serial Phase #:107 [CDB$ROOT] Files:1 Time: 94s
------------------------------------------------------
Phases [0-107] End Time:[2022_10_27 04:21:13]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
Start processing of PDBs (PDB$SEED)
[/oracle/app/orawork/product/19.0.0.0/db_home1/perl/bin/perl /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catctl.pl -I -i pdb_seed -n 2 -c 'PDB$SEED' -l /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307 /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catupgrd.sql]
Argument list for [/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = PDB$SEED
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = pdb_seed
Child Process I = 1
Log Dir l = /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]
/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/orahome = [/oracle/app/orawork/product/19.0.0.0/db_home1]
/oracle/app/orawork/product/19.0.0.0/db_home1/bin/orabasehome = [/oracle/app/orawork/product/19.0.0.0/db_home1]
catctlGetOraBaseLogDir = [/oracle/app/orawork/product/19.0.0.0/db_home1]
Analyzing file /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catupgrd.sql
Log file directory = [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdpdb_seed_catcon_126915.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdpdb_seed*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdpdb_seed_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = CDBTRAIN
DataBase Version = 19.0.0.0.0
PDB$SEED Open Mode = [MIGRATE]
Generated PDB Inclusion:[PDB$SEED]
CDB$ROOT Open Mode = [OPEN]
Components in [PDB$SEED]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM WK]
DataBase Version = 19.0.0.0.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_10_27 04:21:46]
Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [PDB$SEED] Files:1 Time: 58s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PDB$SEED] Files:5 Time: 70s
Restart Phase #:2 [PDB$SEED] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PDB$SEED] Files:19 Time: 64s
Restart Phase #:4 [PDB$SEED] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [PDB$SEED] Files:7 Time: 27s
***************** Catproc Start ****************
Serial Phase #:6 [PDB$SEED] Files:1 Time: 14s
***************** Catproc Types ****************
Serial Phase #:7 [PDB$SEED] Files:2 Time: 16s
Restart Phase #:8 [PDB$SEED] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [PDB$SEED] Files:70 Time: 87s
Restart Phase #:10 [PDB$SEED] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [PDB$SEED] Files:1 Time: 95s
Restart Phase #:12 [PDB$SEED] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [PDB$SEED] Files:95 Time: 20s
Restart Phase #:14 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:15 [PDB$SEED] Files:122 Time: 24s
Restart Phase #:16 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:17 [PDB$SEED] Files:25 Time: 3s
Restart Phase #:18 [PDB$SEED] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [PDB$SEED] Files:32 Time: 50s
Restart Phase #:20 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:21 [PDB$SEED] Files:3 Time: 19s
Restart Phase #:22 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:23 [PDB$SEED] Files:25 Time: 811s
Restart Phase #:24 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:25 [PDB$SEED] Files:12 Time: 220s
Restart Phase #:26 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:27 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:28 [PDB$SEED] Files:4 Time: 16s
Serial Phase #:29 [PDB$SEED] Files:1 Time: 0s
Restart Phase #:30 [PDB$SEED] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [PDB$SEED] Files:1 Time: 1s
Restart Phase #:32 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:34 [PDB$SEED] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [PDB$SEED] Files:297 Time: 111s
Serial Phase #:36 [PDB$SEED] Files:1 Time: 0s
Restart Phase #:37 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:38 [PDB$SEED] Files:10 Time: 5s
Restart Phase #:39 [PDB$SEED] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [PDB$SEED] Files:3 Time: 57s
Restart Phase #:41 [PDB$SEED] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [PDB$SEED] Files:13 Time: 244s
Restart Phase #:43 [PDB$SEED] Files:1 Time: 1s
Parallel Phase #:44 [PDB$SEED] Files:11 Time: 6s
Restart Phase #:45 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:46 [PDB$SEED] Files:3 Time: 3s
Restart Phase #:47 [PDB$SEED] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [PDB$SEED] Files:1 Time: 11s
Restart Phase #:49 [PDB$SEED] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [PDB$SEED] Files:1 Time: 8s
************ Upgrade Component Start ***********
Serial Phase #:51 [PDB$SEED] Files:1 Time: 0s
Restart Phase #:52 [PDB$SEED] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [PDB$SEED] Files:2 Time: 269s
***************** Upgrading XDB ****************
Restart Phase #:54 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:56 [PDB$SEED] Files:3 Time: 7s
Serial Phase #:57 [PDB$SEED] Files:3 Time: 2s
Parallel Phase #:58 [PDB$SEED] Files:10 Time: 3s
Parallel Phase #:59 [PDB$SEED] Files:25 Time: 5s
Serial Phase #:60 [PDB$SEED] Files:4 Time: 9s
Serial Phase #:61 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:62 [PDB$SEED] Files:32 Time: 9s
Serial Phase #:63 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:64 [PDB$SEED] Files:6 Time: 8s
Serial Phase #:65 [PDB$SEED] Files:2 Time: 24s
Serial Phase #:66 [PDB$SEED] Files:3 Time: 31s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:69 [PDB$SEED] Files:1 Time: 1s
Parallel Phase #:70 [PDB$SEED] Files:2 Time: 13s
Restart Phase #:71 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:72 [PDB$SEED] Files:2 Time: 1s
Serial Phase #:73 [PDB$SEED] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:76 [PDB$SEED] Files:1 Time: 54s
Serial Phase #:77 [PDB$SEED] Files:2 Time: 2s
Restart Phase #:78 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:79 [PDB$SEED] Files:1 Time: 6s
Restart Phase #:80 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:81 [PDB$SEED] Files:3 Time: 77s
Restart Phase #:82 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:83 [PDB$SEED] Files:1 Time: 5s
Restart Phase #:84 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:85 [PDB$SEED] Files:1 Time: 7s
Restart Phase #:86 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:87 [PDB$SEED] Files:4 Time: 142s
Restart Phase #:88 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:89 [PDB$SEED] Files:1 Time: 0s
Restart Phase #:90 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:91 [PDB$SEED] Files:2 Time: 28s
Restart Phase #:92 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:93 [PDB$SEED] Files:1 Time: 1s
Restart Phase #:94 [PDB$SEED] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [PDB$SEED] Files:1 Time: 18s
Restart Phase #:96 [PDB$SEED] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [PDB$SEED] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [PDB$SEED] Files:1 Time: 33s
******************* Migration ******************
Serial Phase #:99 [PDB$SEED] Files:1 Time: 1s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:101 [PDB$SEED] Files:1 Time: 3s
Serial Phase #:102 [PDB$SEED] Files:1 Time: 3s
***************** Post Upgrade *****************
Serial Phase #:103 [PDB$SEED] Files:1 Time: 641s
**************** Summary report ****************
Serial Phase #:104 [PDB$SEED] Files:1 Time: 0s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:106 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:107 [PDB$SEED] Files:1 Time: 0s
------------------------------------------------------
Phases [0-107] End Time:[2022_10_27 05:19:21]
Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 3459s [PDB$SEED]
LOG FILES: (/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdpdb_seed*.log)
Upgrade Summary Report Located in:
/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/upg_summary.log
Start processing of PDBs (TRAINDB)
[/oracle/app/orawork/product/19.0.0.0/db_home1/perl/bin/perl /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catctl.pl -I -i traindb -n 2 -c 'TRAINDB' -l /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307 /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catupgrd.sql]
Argument list for [/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = TRAINDB
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = traindb
Child Process I = 1
Log Dir l = /oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 2
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.16.0.0.0DBRU_LINUX.X64_220701]
/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/orahome = [/oracle/app/orawork/product/19.0.0.0/db_home1]
/oracle/app/orawork/product/19.0.0.0/db_home1/bin/orabasehome = [/oracle/app/orawork/product/19.0.0.0/db_home1]
catctlGetOraBaseLogDir = [/oracle/app/orawork/product/19.0.0.0/db_home1]
Analyzing file /oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/catupgrd.sql
Log file directory = [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdtraindb_catcon_34275.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdtraindb*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdtraindb_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = CDBTRAIN
DataBase Version = 19.0.0.0.0
TRAINDB Open Mode = [MIGRATE]
Generated PDB Inclusion:[TRAINDB]
CDB$ROOT Open Mode = [OPEN]
Components in [TRAINDB]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM WK]
DataBase Version = 19.0.0.0.0
------------------------------------------------------
Phases [0-107] Start Time:[2022_10_27 05:19:31]
Container Lists Inclusion:[TRAINDB] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [TRAINDB] Files:1 Time: 33s
*************** Catalog Core SQL ***************
Serial Phase #:1 [TRAINDB] Files:5 Time: 53s
Restart Phase #:2 [TRAINDB] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [TRAINDB] Files:19 Time: 50s
Restart Phase #:4 [TRAINDB] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [TRAINDB] Files:7 Time: 21s
***************** Catproc Start ****************
Serial Phase #:6 [TRAINDB] Files:1 Time: 13s
***************** Catproc Types ****************
Serial Phase #:7 [TRAINDB] Files:2 Time: 13s
Restart Phase #:8 [TRAINDB] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [TRAINDB] Files:70 Time: 65s
Restart Phase #:10 [TRAINDB] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [TRAINDB] Files:1 Time: 79s
Restart Phase #:12 [TRAINDB] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [TRAINDB] Files:95 Time: 12s
Restart Phase #:14 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:15 [TRAINDB] Files:122 Time: 17s
Restart Phase #:16 [TRAINDB] Files:1 Time: 0s
Serial Phase #:17 [TRAINDB] Files:25 Time: 2s
Restart Phase #:18 [TRAINDB] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [TRAINDB] Files:32 Time: 37s
Restart Phase #:20 [TRAINDB] Files:1 Time: 0s
Serial Phase #:21 [TRAINDB] Files:3 Time: 14s
Restart Phase #:22 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:23 [TRAINDB] Files:25 Time: 423s
Restart Phase #:24 [TRAINDB] Files:1 Time: 1s
Parallel Phase #:25 [TRAINDB] Files:12 Time: 237s
Restart Phase #:26 [TRAINDB] Files:1 Time: 0s
Serial Phase #:27 [TRAINDB] Files:1 Time: 0s
Serial Phase #:28 [TRAINDB] Files:4 Time: 3s
Serial Phase #:29 [TRAINDB] Files:1 Time: 0s
Restart Phase #:30 [TRAINDB] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [TRAINDB] Files:1 Time: 1s
Restart Phase #:32 [TRAINDB] Files:1 Time: 0s
Serial Phase #:34 [TRAINDB] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [TRAINDB] Files:297 Time: 23s
Serial Phase #:36 [TRAINDB] Files:1 Time: 0s
Restart Phase #:37 [TRAINDB] Files:1 Time: 0s
Serial Phase #:38 [TRAINDB] Files:10 Time: 2s
Restart Phase #:39 [TRAINDB] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [TRAINDB] Files:3 Time: 61s
Restart Phase #:41 [TRAINDB] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [TRAINDB] Files:13 Time: 290s
Restart Phase #:43 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:44 [TRAINDB] Files:11 Time: 6s
Restart Phase #:45 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:46 [TRAINDB] Files:3 Time: 3s
Restart Phase #:47 [TRAINDB] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [TRAINDB] Files:1 Time: 11s
Restart Phase #:49 [TRAINDB] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [TRAINDB] Files:1 Time: 9s
************ Upgrade Component Start ***********
Serial Phase #:51 [TRAINDB] Files:1 Time: 0s
Restart Phase #:52 [TRAINDB] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [TRAINDB] Files:2 Time: 263s
***************** Upgrading XDB ****************
Restart Phase #:54 [TRAINDB] Files:1 Time: 0s
Serial Phase #:56 [TRAINDB] Files:3 Time: 8s
Serial Phase #:57 [TRAINDB] Files:3 Time: 2s
Parallel Phase #:58 [TRAINDB] Files:10 Time: 3s
Parallel Phase #:59 [TRAINDB] Files:25 Time: 5s
Serial Phase #:60 [TRAINDB] Files:4 Time: 8s
Serial Phase #:61 [TRAINDB] Files:1 Time: 0s
Serial Phase #:62 [TRAINDB] Files:32 Time: 3s
Serial Phase #:63 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:64 [TRAINDB] Files:6 Time: 6s
Serial Phase #:65 [TRAINDB] Files:2 Time: 20s
Serial Phase #:66 [TRAINDB] Files:3 Time: 34s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [TRAINDB] Files:1 Time: 1s
Serial Phase #:69 [TRAINDB] Files:1 Time: 1s
Parallel Phase #:70 [TRAINDB] Files:2 Time: 9s
Restart Phase #:71 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:72 [TRAINDB] Files:2 Time: 1s
Serial Phase #:73 [TRAINDB] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [TRAINDB] Files:1 Time: 0s
Serial Phase #:76 [TRAINDB] Files:1 Time: 59s
Serial Phase #:77 [TRAINDB] Files:2 Time: 1s
Restart Phase #:78 [TRAINDB] Files:1 Time: 1s
Serial Phase #:79 [TRAINDB] Files:1 Time: 5s
Restart Phase #:80 [TRAINDB] Files:1 Time: 0s
Parallel Phase #:81 [TRAINDB] Files:3 Time: 74s
Restart Phase #:82 [TRAINDB] Files:1 Time: 0s
Serial Phase #:83 [TRAINDB] Files:1 Time: 5s
Restart Phase #:84 [TRAINDB] Files:1 Time: 0s
Serial Phase #:85 [TRAINDB] Files:1 Time: 8s
Restart Phase #:86 [TRAINDB] Files:1 Time: 1s
Parallel Phase #:87 [TRAINDB] Files:4 Time: 143s
Restart Phase #:88 [TRAINDB] Files:1 Time: 1s
Serial Phase #:89 [TRAINDB] Files:1 Time: 0s
Restart Phase #:90 [TRAINDB] Files:1 Time: 0s
Serial Phase #:91 [TRAINDB] Files:2 Time: 25s
Restart Phase #:92 [TRAINDB] Files:1 Time: 0s
Serial Phase #:93 [TRAINDB] Files:1 Time: 1s
Restart Phase #:94 [TRAINDB] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [TRAINDB] Files:1 Time: 14s
Restart Phase #:96 [TRAINDB] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [TRAINDB] Files:1 Time: 4s
************* Final Upgrade scripts ************
Serial Phase #:98 [TRAINDB] Files:1 Time: 26s
******************* Migration ******************
Serial Phase #:99 [TRAINDB] Files:1 Time: 1s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [TRAINDB] Files:1 Time: 1s
Serial Phase #:101 [TRAINDB] Files:1 Time: 2s
Serial Phase #:102 [TRAINDB] Files:1 Time: 3s
***************** Post Upgrade *****************
Serial Phase #:103 [TRAINDB] Files:1 Time: 1s
**************** Summary report ****************
Serial Phase #:104 [TRAINDB] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [TRAINDB] Files:1 Time: 1s
Serial Phase #:106 [TRAINDB] Files:1 Time: 1s
Serial Phase #:107 [TRAINDB] Files:1 Time: 0s
------------------------------------------------------
Phases [0-107] End Time:[2022_10_27 05:56:37]
Container Lists Inclusion:[TRAINDB] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 2227s [TRAINDB]
LOG FILES: (/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdtraindb*.log)
Upgrade Summary Report Located in:
/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/upg_summary.log
Time: 2883s For CDB$ROOT
Time: 5728s For PDB(s)
Grand Total Time: 8611s
LOG FILES: (/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/catupgrdcdbroot*.log)
Upgrade Summary Report Located in:
/oracle/app/orawork/product/19.0.0.0/db_home1/cfgtoollogs/CDBTRAIN/upgrade20221027033307/upg_summary.log
Grand Total Upgrade Time: [0d:2h:23m:31s]
[oracle@dm01db01 bin]$
20. Node1 copy all file dbs and network/admin to new home
[oracle@dm01db01 dbs]$ cp * /oracle/app/orawork/product/19.0.0.0/db_home1/network/admin/
[oracle@dm01db01 dbs]$
[oracle@dm01db01 dbs]$
[oracle@dm01db01 dbs]$ cd ..
[oracle@dm01db01 db_1]$
[oracle@dm01db01 admin]$ cp * /oracle/app/orawork/product/19.0.0.0/db_home1/dbs
[oracle@dm01db01 dbs]$ cat initCDBTRAIN1.ora
spfile='+DATAC1/CDBTRAIN/PARAMETERFILE/spfile.270.1119144227'
21. Node2 copy all file dbs and network/admin to new home
[oracle@dm01db02 ~]$ cd /oracle/app/orawork/product/12.2.0.1/db_1/network/admin
[oracle@dm01db02 admin]$
[oracle@dm01db02 admin]$ cp -R * /oracle/app/orawork/product/19.0.0.0/db_home1/network/admin/
[oracle@dm01db02 admin]$ cd ../../dbs
[oracle@dm01db02 dbs]$
[oracle@dm01db02 dbs]$ cp * /oracle/app/orawork/product/19.0.0.0/db_home1/dbs
[oracle@dm01db02 dbs]$ cat initCDBTRAIN2.ora
spfile='+DATAC1/CDBTRAIN/PARAMETERFILE/spfile.270.1119144227'
22. check database status and invalid objects after upgrade .
[oracle@dm01db01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 06:16:51 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SQL> SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- -----------------------------
1 CDBTRAIN1 CDBTRAIN dm01db01.database.com PRIMARY ARCHIVELOG READ WRITE 19.0.0.0.0 ALLOWED 27-OCT-2022 04:20:39
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TRAINDB READ WRITE NO
SQL> select count(*) from dba_objects where status = 'INVALID';
COUNT(*)
----------
47
23. Run Post upgrade fixup script
[oracle@dm01db01 dbs]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/ -b postup_CDBTRAIN /oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postupgrade_fixups.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postup_CDBTRAIN_catcon_89079.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postup_CDBTRAIN*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/cfgtoollogs/CDBTRAIN/preupgrade/postup_CDBTRAIN_*.lst] files for spool files, if any
catcon.pl: completed successfully
24. Run utlrp.sql to recompile invalid objects
[oracle@dm01db01 admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/utlrp_catcon_94744.lst]
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/utlrp*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/oracle/app/orawork/product/19.0.0.0/db_home1/rdbms/admin/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
25. Recheck database for invalid objects
SQL> select count(*) from dba_objects where status = 'INVALID';
COUNT(*)
----------
0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TRAINDB READ WRITE NO
26 .Start with the Timezone DST upgrade for 12.2 its 26 and will be upgrade to 32 for 19c
[oracle@dm01db01 oracle]$ cd DBMS_DST_scriptsV1.9
[oracle@dm01db01 DBMS_DST_scriptsV1.9]$ ls -ltr
total 68
-rw-r--r-- 1 oracle oinstall 31010 Aug 22 2014 upg_tzv_check.sql
-rw-r--r-- 1 oracle oinstall 19502 Aug 22 2014 upg_tzv_apply.sql
-rw-r--r-- 1 oracle oinstall 6294 Jan 8 2015 countstarTSTZ.sql
-rw-r--r-- 1 oracle oinstall 7213 Mar 17 2018 countstatsTSTZ.sql
[oracle@dm01db01 DBMS_DST_scriptsV1.9]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 07:08:42 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> @countstatsTSTZ.sql
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
26/01/2017 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 3
26/01/2017 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 3
26/01/2017 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 3
26/01/2017 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
26/01/2017 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
26/01/2017 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
26/01/2017 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
26/01/2017 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
26/01/2017 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
26/01/2017 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
26/01/2017 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
26/01/2017 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
27/10/2022 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
27/10/2022 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
27/10/2022 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
26/01/2017 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
26/01/2017 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
26/01/2017 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
26/01/2017 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
26/01/2017 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
26/01/2017 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
26/01/2017 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
26/01/2017 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
26/01/2017 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
27/10/2022 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
27/10/2022 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
27/10/2022 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
26/01/2017 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
26/01/2017 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
26/01/2017 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
26/01/2017 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
26/01/2017 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
26/01/2017 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
26/01/2017 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
26/01/2017 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
27/10/2022 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
27/10/2022 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
27/10/2022 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
27/10/2022 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
27/10/2022 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 1740
27/10/2022 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
26/01/2017 - SYS.RADM_FPTM$.TSWTZ_COL - 1
26/01/2017 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
26/01/2017 - SYS.REG$.REG_TIME - 2
27/10/2022 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 22
27/10/2022 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
27/10/2022 - SYS.SCHEDULER$_JOB.END_DATE - 24
27/10/2022 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 24
27/10/2022 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 24
27/10/2022 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 24
27/10/2022 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 24
27/10/2022 - SYS.SCHEDULER$_JOB.START_DATE - 24
27/10/2022 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 8
27/10/2022 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 8
27/10/2022 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 8
26/01/2017 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
26/01/2017 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
27/10/2022 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
27/10/2022 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
27/10/2022 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
27/10/2022 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
27/10/2022 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
27/10/2022 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
27/10/2022 - SYS.TAB_STATS$.SPARE6 - 1132
27/10/2022 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 48
27/10/2022 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 48
27/10/2022 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 31659
27/10/2022 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 31659
27/10/2022 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 997
27/10/2022 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 997
27/10/2022 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 108
27/10/2022 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 108
27/10/2022 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 108
27/10/2022 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 3530
27/10/2022 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 3530
27/10/2022 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 3530
27/10/2022 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 2159
27/10/2022 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 2159
27/10/2022 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 5
27/10/2022 - SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 6
27/10/2022 - SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 6
27/10/2022 - SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 5
27/10/2022 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 4
27/10/2022 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 4
26/01/2017 - SYS.XS$PRIN.END_DATE - 15
26/01/2017 - SYS.XS$PRIN.START_DATE - 15
Total numrow of SYS TSTZ columns is : 84078
There are in total 165 non-SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
26/01/2017 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
26/01/2017 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
26/01/2017 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME -
1
26/01/2017 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
26/01/2017 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
26/01/2017 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
26/01/2017 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
26/01/2017 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrow of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
SQL>
SQL>
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL>
SQL>
SQL>
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1073738488 bytes
Fixed Size 9143032 bytes
Variable Size 557842432 bytes
Database Buffers 503316480 bytes
Redo Buffers 3436544 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1073738488 bytes
Fixed Size 9143032 bytes
Variable Size 557842432 bytes
Database Buffers 503316480 bytes
Redo Buffers 3436544 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.
SQL>
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
1 row selected.
SQL>
27. Run script catuppst.sql
SQL> @?/rdbms/admin/catuppst.sql
Session altered.
Session altered.
Session altered.
PL/SQL procedure successfully completed.
Session altered.
System altered.
PL/SQL procedure successfully completed.
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2022-10-27 07:19:27
DBUA_TIMESTAMP DBRESTART FINISHED 2022-10-27 07:19:27 Container=CDB$ROOT Id
=1
DBUA_TIMESTAMP DBRESTART NONE 2022-10-27 07:19:27
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2022-10-27 07:19:27 Container=CDB$ROOT Id=
1
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2022-10-27 07:19:27
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2022-10-27 07:19:27 Container=CDB$ROOT Id
=1
DBUA_TIMESTAMP POSTUP_BGN NONE 2022-10-27 07:19:27
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2022-10-27 07:19:27
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2022-10-27 07:19:27 Container=CDB$ROOT Id
=1
DBUA_TIMESTAMP CATREQ_BGN NONE 2022-10-27 07:19:27
1 row selected.
PL/SQL procedure successfully completed.
sPrvVersion = 12.2.0.1.0 nPrevMajorVer = 12
sRetFunc = ?/rdbms/admin/catupshd.sql
catrequtlmg: utlmmig.sql (bootstrap objects migration) not called
catrequtlmg: Gathering statistics not required returning
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2022-10-27 07:19:27
DBUA_TIMESTAMP CATREQ_END FINISHED 2022-10-27 07:19:27 Container=CDB$ROOT Id
=1
DBUA_TIMESTAMP CATREQ_END NONE 2022-10-27 07:19:27
1 row selected.
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
PL/SQL procedure successfully completed.
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view _CURRENT_EDITION_STUB_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG
PL/SQL procedure successfully completed.
catuppst: Dropping table OBJ$MIG
catuppst: Dropping table USER$MIG
catuppst: Dropping table COL$MIG
catuppst: Dropping table CLU$MIG
catuppst: Dropping table CON$MIG
catuppst: Dropping table BOOTSTRAP$MIG
catuppst: Dropping table TAB$MIG
catuppst: Dropping table TS$MIG
catuppst: Dropping table IND$MIG
catuppst: Dropping table ICOL$MIG
catuppst: Dropping table LOB$MIG
catuppst: Dropping table COLTYPE$MIG
catuppst: Dropping table SUBCOLTYPE$MIG
catuppst: Dropping table NTAB$MIG
catuppst: Dropping table REFCON$MIG
catuppst: Dropping table OPQTYPE$MIG
catuppst: Dropping table ICOLDEP$MIG
catuppst: Dropping table VIEWTRCOL$MIG
catuppst: Dropping table ATTRCOL$MIG
catuppst: Dropping table TYPE_MISC$MIG
catuppst: Dropping table LIBRARY$MIG
catuppst: Dropping table ASSEMBLY$MIG
catuppst: Dropping table TSQ$MIG
catuppst: Dropping table FET$MIG
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2022-10-27 07:19:27
DBUA_TIMESTAMP POSTUP_END FINISHED 2022-10-27 07:19:27 Container=CDB$ROOT Id
=1
DBUA_TIMESTAMP POSTUP_END NONE 2022-10-27 07:19:27
1 row selected.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2022-10-27 07:19:27
DBUA_TIMESTAMP CATUPPST FINISHED 2022-10-27 07:19:27 Container=CDB$ROOT Id
=1
DBUA_TIMESTAMP CATUPPST NONE 2022-10-27 07:19:27
1 row selected.
Session altered.
28. Run utlusts.sql TEXT
SQL> @?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 10-27-2022 07:22:0
Container Database: CDBTRAIN
[CON_ID: 1 => CDB$ROOT]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.16.0.0.0 00:26:05
JServer JAVA Virtual Machine VALID 19.16.0.0.0 00:02:40
Oracle XDK VALID 19.16.0.0.0 00:01:24
Oracle Database Java Packages VALID 19.16.0.0.0 00:00:16
OLAP Analytic Workspace VALID 19.16.0.0.0 00:00:20
Oracle Label Security VALID 19.16.0.0.0 00:00:09
Oracle Database Vault VALID 19.16.0.0.0 00:00:25
Oracle Text VALID 19.16.0.0.0 00:00:43
Oracle Workspace Manager VALID 19.16.0.0.0 00:00:35
Oracle Real Application Clusters VALID 19.16.0.0.0 00:00:00
Oracle XML Database VALID 19.16.0.0.0 00:01:36
Oracle Multimedia VALID 19.16.0.0.0 00:01:06
Spatial VALID 19.16.0.0.0 00:08:21
Oracle OLAP API VALID 19.16.0.0.0 00:00:14
Datapatch 00:01:01
Final Actions 00:01:07
Post Upgrade 00:00:00
Post Compile 00:00:06
Total Upgrade Time: 00:45:28 [CON_ID: 1 => CDB$ROOT]
Database time zone version is 32. It meets current release needs.
29. Add Database using old spfile will all the cluster parameter intact .
[oracle@dm01db01 bin]$ srvctl add database -d CDBTRAIN -o /oracle/app/orawork/product/19.0.0.0/db_home1 -spfile +DATAC1/CDBTRAIN/PARAMETERFILE/spfile.270.1119144227 -pwfile +DATAC1/CDBTRAIN/PASSWORD/pwdcdbtrain.258.1119142913
[oracle@dm01db01 bin]$
30. Add instance after the upgrade :
./srvctl add instance -i CDBTRAIN1 -d CDBTRAIN -n dm01db01
./srvctl add instance -i CDBTRAIN2 -d CDBTRAIN -n dm01db02
31. Shutdown database and start database in cluster mode with srvctl command
[oracle@dm01db01 bin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 27 07:34:55 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> shu iimmediate
SP2-0717: illegal SHUTDOWN option
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@dm01db02 dbs]$ srvctl start database -d CDBTRAIN
[oracle@dm01db02 dbs]$
[oracle@dm01db02 dbs]$
32. Check database status :
[oracle@dm01db02 dbs]$ srvctl status database -d CDBTRAIN
Instance CDBTRAIN1 is running on node dm01db01
Instance CDBTRAIN2 is running on node dm01db02
srvctl status database -d CDBTRAIN -v -f
[oracle@dm01db02 dbs]$ srvctl status database -d CDBTRAIN -v -f
Instance CDBTRAIN1 is running on node dm01db01. Instance status: Open.
Instance CDBTRAIN2 is running on node dm01db02. Instance status: Open.
SQL> set pages 9999 lines 300
SQL> col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,LOG_MODE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
SQL> SQL>
INST_ID INSTANCE_NAME DB_NAME HOST_NAME DATABASE_ROLE LOG_MODE OPEN_MODE DB_VERSION LOGINS DB UP TIME
---------- ---------------- --------- ------------------------------ ---------------- ------------ ---------- ----------------- ---------- -----------------------------
1 CDBTRAIN1 CDBTRAIN dm01db01.database.com PRIMARY ARCHIVELOG READ WRITE 19.0.0.0.0 ALLOWED 27-OCT-2022 07:36:44
2 CDBTRAIN2 CDBTRAIN dm01db02.database.com PRIMARY ARCHIVELOG READ WRITE 19.0.0.0.0 ALLOWED 27-OCT-2022 07:46:19
33. check version after upgrade .
SQL> select banner_full from gv$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TRAINDB READ WRITE NO
34. check with the crsctl command db
[grid@dm01db01 ~]$ crsctl status res ora.cdbtrain.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cdbtrain.db
1 ONLINE ONLINE dm01db01 Open,HOME=/oracle/ap
p/orawork/product/19
.0.0.0/db_home1,STAB
LE
2 ONLINE ONLINE dm01db02 Open,HOME=/oracle/ap
p/orawork/product/19
.0.0.0/db_home1,STAB
LE
--------------------------------------------------------------------------------
[grid@dm01db01 ~]$
[grid@dm01db01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
ONLINE ONLINE dm01db01 STABLE
ONLINE ONLINE dm01db02 STABLE
ora.chad
ONLINE ONLINE dm01db01 STABLE
OFFLINE OFFLINE dm01db02 STABLE
ora.net1.network
ONLINE ONLINE dm01db01 STABLE
ONLINE ONLINE dm01db02 STABLE
ora.ons
ONLINE ONLINE dm01db01 STABLE
ONLINE ONLINE dm01db02 STABLE
ora.proxy_advm
OFFLINE OFFLINE dm01db01 STABLE
OFFLINE OFFLINE dm01db02 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
1 ONLINE ONLINE dm01db01 STABLE
2 ONLINE ONLINE dm01db02 STABLE
ora.DATAC1.dg(ora.asmgroup)
1 ONLINE ONLINE dm01db01 STABLE
2 ONLINE ONLINE dm01db02 STABLE
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE dm01db01 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE dm01db02 STABLE
ora.RECOC1.dg(ora.asmgroup)
1 ONLINE ONLINE dm01db01 STABLE
2 ONLINE ONLINE dm01db02 STABLE
ora.asm(ora.asmgroup)
1 ONLINE ONLINE dm01db01 Started,STABLE
2 ONLINE ONLINE dm01db02 Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
1 ONLINE ONLINE dm01db01 STABLE
2 ONLINE ONLINE dm01db02 STABLE
ora.cdbtrain.db
1 ONLINE ONLINE dm01db01 Open,HOME=/oracle/ap
p/orawork/product/19
.0.0.0/db_home1,STAB
LE
2 ONLINE ONLINE dm01db02 Open,HOME=/oracle/ap
p/orawork/product/19
.0.0.0/db_home1,STAB
LE
ora.cvu
1 ONLINE ONLINE dm01db02 STABLE
ora.dm01db01.vip
1 ONLINE ONLINE dm01db01 STABLE
ora.dm01db02.vip
1 ONLINE ONLINE dm01db02 STABLE
ora.qosmserver
1 ONLINE ONLINE dm01db02 STABLE
ora.scan1.vip
1 ONLINE ONLINE dm01db01 STABLE
ora.scan2.vip
1 ONLINE ONLINE dm01db02 STABLE
--------------------------------------------------------------------------------
Fall back plan :
1. Shutdown immediate;
2. set ORACLE_HOME to 12.2
3. Start up mount ( with the 12c spfile)
4. select * from v$restore_point;
5. flashback database to restore point FB_UPGRADE. ( this restore point was created before upgrade)
6. alter database open resetlogs;
7. Start database from both Instance.
Hope this will help , Any suggestions , put diwn in comment box.
Regards
Sultan Khan
ConversionConversion EmoticonEmoticon