Waiting in “ALTER TABLE tab_stats$” transaction during upgrade from 11.2.0.3 or 11.2.0.4 version to 12.2.0.1 version

Oracle can wait for a long time in the first phase in upgrades from 11.2.0.3 or 11.2.0.4 version to 12.2.0.1 version. When the logs are examined, it will be seen that they are waiting in the following process.

SQL> Rem Basic standard package is invalid at this point.
SQL> ALTER TABLE tab_stats$
DROP (spare1, spare2, spare3, spare4, spare5, spare6);

If you stopped the process, to fix the problem:
  1. Restore your database from backup.
  2. Apply the patch 29024054 to the database where you returned from the backup and upgrade again.

When the process is started, it will wait for a long time in the first phase as below.

[oracle@orcldb01 ~]$ cd $ORACLE_HOME/bin
[oracle@orcldb01 bin]$ ./dbupgrade 

Argument list for [/u01/app/oracle/product/12.2.0.1/dbhome_1/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: [12.2.0.1.0]
           STATUS: [production]
            BUILD: [RDBMS_12.2.0.1.0DBJUL2019RU_LINUX.X64_190624]


/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0.1/dbhome_1]
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/12.2.0.1/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/12.2.0.1/dbhome_1]

Analyzing file /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20200313190634]

catcon: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200313190634/catupgrd_catcon_346909.lst]
catcon: See [/tmp/cfgtoollogs/upgrade20200313190634/catupgrd*.log] files for output generated by scripts
catcon: See [/tmp/cfgtoollogs/upgrade20200313190634/catupgrd_*.lst] files for spool files, if any

Number of Cpus        = 24
Database Name         = ORCL
DataBase Version      = 11.2.0.3.0
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/ORCL/upgrade20200313190635/catupgrd_catcon_346909.lst]
catcon: See [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/ORCL/upgrade20200313190635/catupgrd*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/ORCL/upgrade20200313190635/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/ORCL/upgrade20200313190635]

Parallel SQL Process Count            = 4
Components in [ORCL]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM OLS ORDIM OWM RAC SDO XDB XML XOQ]
Not Installed [DV MGW ODM WK]

------------------------------------------------------
Phases [0-115]         Start Time:[2020_03_13 19:06:36]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [ORCL] Files:1    

 

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *