Upgrade to fails at 109 phase with “Unexpected error encountered”

We were trying to upgrade our database one week ago and it failed with the following error:

Serial Phase #:108 [ORCL] Files:1 Time: 1s 
******************* Migration ****************** 
Serial Phase #:109 [ORCL] Files:1 wait_for_completion: unexpected error in next_proc() 
catconExec: unexpected error in wait_for_completions 

Unexpected error encountered in catconExec; exiting 

Unexpected error encountered in catctlMain; Error Stack Below; exiting 
Died at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 7822. 
at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 7822. 
main::catctlDie("\x{a}Unexpected error encountered in catconExec; exiting\x{a} 2") called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 4556 
main::catctlExecutePhaseFiles(109, 1, undef, undef, undef) called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1862 
main::catctlRunPhase(109, 1, undef, undef, undef) called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 2006 
main::catctlRunPhases(0, 116, 116, undef, undef, undef) called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 2171 
main::catctlRunMainPhases() called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1341 
main::catctlMain() called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1256 
eval {...} called at /u01/app/oracle/product/12.2.0/db/rdbms/admin/catctl.pl line 1254

We asked Oracle Support. It seemed they did not have such situation before.
12.2 is the newest version and any existing bugs are not known at this time.

We postponed upgrade for the next week after upgrade failure. And I started to read all the documentation steps carefully. I almost learned by heart 😀

Documentation id where the upgrade steps are written is  2173141.1

I will write down upgrade steps and the error that happened at the phase 109.
Then let’s correct an error and finish upgrade successfully.

================ Preupgrade steps

0. Copy orapwORCL, sqlnet.ora, tnsnames.ora, listener.ora files from old home to new home.

  1. Execute Preupgrade script from source home
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db
    export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.1.0/db/bin
    $ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/db/rdbms/admin/preupgrade.jar TERMINAL TEXT

    It will generate two scripts pre/post fixups.

  2. Run preupgrade fixup , it will not correct all problems. Some steps must be done manually.
    sqlplus /  as sysdba
  3. In documentation there is written to backup whole database, but our database is 11TB in size, so backing up before upgrade is not an option.
    But if you read doc carefully , there is written that you may make schema tablespaces offline(read only) which means that it is not necessary to backup schema tablesapces, because they are not changed during upgrade.
    So we can simply shutdown cleanly (normal, immediate, transactional)  our database and cold copy system, sysaux datafiles to another safe location. You must also copy one of the controlfile.
    Copying undo tablespace datafiles is not necessary, you are still able to recover database when it is cleanly shutdown. So if you backup undo , you may be saved with extra steps that is necessary to recover database without undo.Take user tablespaces read only. So output of this select:

    SELECT distinct 'alter tablespace '|| tablespace_name||' read only;' 
    from dba_data_files
    where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02');

Please indicate your UNDO tablesapce name instead of mine.

4. Disable any custom DDL trigger , if exists. I have had created DDL trigger, so disabling it.

           alter trigger sys.audit_ddl_trg disable;

5. Purge recyclebin and gather dictionary statistics

           purge dba_recyclebin;
           exec dbms_stats.gather_dictionary_stats;

6. Shutdown database with clean option! or you will not be able to restore after upgrade fails.

           sqlplus / as sysdba
           alter database checkpoint;
           shutdown immediate;
           lsnrctl stop

7.  cold copy SYSTEM, SYSAUX, control files

            cp /ud01/oradata/ORCL/sysaux01.dbf /ud02/backup_mk/
            cp /ud01/oradata/ORCL/system01.dbf /ud02/backup_mk/
            cp /ud01/oradata/ORCL/control01.ctl /ud02/backup_mk/
            cp /ud01/oradata/ORCL/control02.ctl /ud02/backup_mk/


8.  Start DB in upgrade mode from target ORACLE_HOME

           export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db
           export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.2.0/db/bin:/u01/app/oracle/product/12.2.0/db

           sqlplus / as sysdba
           startup upgrade;

           cd /u01/app/oracle/product/12.2.0/db/bin
           ./dbupgrade -n 33 -T

-T option tells upgrade that schema tablespaces need to be offline(we already done it, just indicate -T)

So, here at phase 109 it fails with unexpected error , mentioned previously.

You must see all the logs that the upgrade creates.. the main error was written in catupgrd0.log

              catrequtlmg: b_StatEvt = TRUE 
              catrequtlmg: b_SelProps = FALSE 
              catrequtlmg: b_UpgradeMode = TRUE 
              catrequtlmg: b_InUtlMig = TRUE 
              catrequtlmg: Deleting table stats 
              catrequtlmg: Gathering Table Stats OBJ$MIG 
              catrequtlmg: Gathering Table Stats USER$MIG 
              ERROR at line 1: 
              ORA-01422: exact fetch returns more than requested number of rows 
              ORA-06512: at "SYS.DBMS_STATS", line 36873 
              ORA-06512: at "SYS.DBMS_STATS", line 36507 
              ORA-06512: at "SYS.DBMS_STATS", line 35428 
              ORA-06512: at "SYS.DBMS_STATS", line 34760 
              ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22496 
              ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22483 
              ORA-06512: at "SYS.DBMS_STATS", line 34416 
              ORA-06512: at "SYS.DBMS_STATS", line 35168 
              ORA-06512: at "SYS.DBMS_STATS", line 36230 
              ORA-06512: at "SYS.DBMS_STATS", line 36716 
              ORA-06512: at line 149

As you see it was gathering statistics on USER$MIG table and it got ORA-01422 🙂
If you try to gather statistics manually you will also get the same error:

         EXEC sys.dbms_stats.gather_table_stats('SYS', 'USER$MIG',method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');

If you check entries in obj$ table, you will see that there are duplicate entries:

          SQL> select to_char(OBJ#), name,  ctime, STATUS 
               from obj$ 
               where name='USER$MIG'; 

          ----------------- ------------ -------------------- 
          956329 USER$MIG 08-10-2015 12:59:55 
          956328 USER$MIG 08-10-2015 12:59:55 
          956325 USER$MIG 08-10-2015 12:59:53 
          956327 USER$MIG 08-10-2015 12:59:55 
          956326 USER$MIG 08-10-2015 12:59:55 
          1621545 USER$MIG 19-05-2017 22:55:26

There are six entries. Five of them is created in 2015 (It maybe left from previous upgrade in 2015 that also failed for the first time)

Oracle support did not advice to delete old entries from this table, BUT support sometimes is                       wrong. We had no time , we were upgrading database 4 hours , for the first time it was failed, we                 had backup so we risked and deleted 2015 entries from this table.

          delete from obj$ 
          where OBJ# in (956329, 

Rerun upgrade from phase 109

          ./dbupgrade -p 109 -T -n 40

And it completed successfully (happy) , so our risk was right !!!!

################Post Upgrade

           SQL> @?/rdbms/admin/postupgrade_fixups.sql

It will fix up some steps that can be done automatically but  it may also write steps that must be                   done manually.  One of them is upgrading timestamp, which I have described in my previous                       post. “Upgrading timezone manually in 12c”

Change compatible variable in your spfile


Change home to new oracle home in /etc/oratab and in listener.ora.

Recompile invalid objects

           SQL> @?/rdbms/admin/utlrp.sql
           select count(*) from dba_objects where status!='VALID';

Start listener from new home:

           export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db/bin
           export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/u01/app/oracle/product/12.2.0/db/bin
           lsnrctl start

Enable trigger if you have disabled before

           alter trigger sys.audit_ddl_trg enable;

Make schema tablespaces read write:

           SELECT distinct 'alter tablespace '|| tablespace_name||' read write;' 
           from dba_data_files
           where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS01','UNDOTBS02');

Upgrading timezone manually in 12c

After manual upgrade from to oracle pre/post upgrade scripts required to upgrade timezone manually.

Our database was using timezone datafile version 18 and the target database ships with timezone datafile version 26.

Updating timezone is somehow complicated process.

I will provide you with the steps that we have used:

The whole steps are described on metalink note : Doc ID 1509653.1

  1. Check the current version

SQL> select TZ_VERSION from registry$database;


  1. Check aslo
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

PROPERTY_NAME                VALUE
 --------------------------- ----------


If DST_PRIMARY_TT_VERSION is <the old DST version number>, DST_SECONDARY_TT_VERSION  is 0 and  DST_UPGRADE_STATE is NONE then continue, otherwise you need to see Note 1509653.1

  1.  Purge recyclebin
sqlplus / as sysdba
purge dba_recyclebin;

–this alter session might speed up DBMS_DST on some db’s
— see Bug 10209691 / Bug 12658443

alter session set "_with_subquery"=materialize;

— to avoid the issue in note 1407273.1

alter session set "_simple_view_merging"=TRUE;

— start prepare window , these steps will NOT update any data yet.


— truncate logging tables if they exist.

TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

SQL> select * from sys.dst$affected_tables;
no rows selected

SQL> select * from sys.dst$error_table;
no rows selected

–If there is no error then end the prepare


5. If error_table is empty we can run the actual timezone upgrade

sqlplus / as sysdba
shutdown immediate;
startup upgrade;

purge dba_recyclebin;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;


SQL> SELECT * FROM sys.dst$error_table;
no rows selected

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

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

— some oracle provided users may be listed here, that is normal


shutdown immediate

alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;

— now upgrade the tables who need action

VAR numfail number
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

 — this select should return now rows

SELECT * FROM sys.dst$error_table;

 — if there where no failures then end the upgrade.

VAR fail number
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

— Check

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_26.dat              26          0

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

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

–Check the following:

SELECT VERSION FROM v$timezone_file;

select TZ_VERSION from registry$database;


–if they differ after an upgrade then updating registry$database can be done by

conn / as sysdba
update registry$database 
set TZ_VERSION = (select version 
                  FROM   v$timezone_file);



Convert Oracle SE to EE

Upgrading Oracle database from Standard Edition to Enterprise Edition is very simple.

For example, we have running Oracle SE in ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

Let’s start converting it…

  1. Install new home in /u01/app/oracle/product/12.1.0/dbhome_2 just indicate EE during installation(not SE).
    Response file entries(db_install.rsp):

  2. Shutdown database and listener from old home.
    . oraenv
    lsnrctl stop
    sqlplus / as sysdba
    shutdown immediate;
  3. Change ORACLE_HOME in oratab and .bash_profile
    cat /etc/oratab
    cat ~/.bash_profile
    export ORACLE_BASE=/u01/app/oracle
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2
    export PATH=$PATH:$ORACLE_HOME/bin
    export ORACLE_SID=EYC
    export TNS_ADMIN=$ORACLE_HOME/network/admin
    export EDITOR=vi
  4. Copy listener.ora, tnsnames.ora, sqlnet.ora, spfileORCL.oraorapwORCL to new home
    cp /u01/app/oracle/product/12.1.0/dbhome_1/network/*.ora /u01/app/oracle/product/12.1.0/dbhome_2/network/
    cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileORCL.ora /u01/app/oracle/product/12.1.0/dbhome_2/dbs/
    cp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL /u01/app/oracle/product/12.1.0/dbhome_2/dbs/

    Please verify that you don’t have old ORACLE_HOME indicated anywhere in these files.

  5. Renew environment variables
    . oraenv
    ORACLE_SID = [ORCL] ? 
    which sqlplus
    sqlplus / as sysdba
  6. Check that you have EE
    SQL> select banner from v$version;
    Oracle Database 12c Enterprise Edition Release - 64bit Production
    PL/SQL Release - Production
    CORE      Production
    TNS for Linux: Version - Production
    NLSRTL Version - Production

How to fix /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

During upgrade from to I got  /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

To solve this problem you should install glibc.i686 packages.

yum install glibc.i686


Thanks to Giorgi Peikrishvili for this case 🙂

Upgrade Oracle Database from 11g to 12c

  1. Download Oracle 12c software fromhttp://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-linux-download-1959253.html

    unzip files:

    unzip linuxamd64_12102_database_1of2.zip
    unzip linuxamd64_12102_database_2of2.zip
  2. Make another home for 12c.
    mkdir -p /u01/app/oracle/product/12.1.0/dblb
  3. Change permissions for /u01 directory.  If 11g home is also located in /u01 you have already done the steps bellow. Just check that permissions are the following:
    chown -R oracle:oinstall /u01
    chmod -R 775 /u01
  4. Change the following parameters in response file, other parameters just leave blank.
    Response file is located in installation directory… extract_drectory/database/response/db_install.rsp

  5. Go to the 12c installation folder  and run runInstaller, to install 12c home:
     ./runInstaller -silent -responseFile /install/database/response/db_install.rsp  -waitforcompletion -showProgress

    When it asks , connect to the server via root user and run

  6. At this time your database should be turned on from 11g home. Connect to the database via SYS user and run the following scripts: emremove.sql will remove EM repository. olspreupgrade.sql will run preupgrade scripts

    You should also purge the recyclebin. For reducing upgrade time.

    Note: these scripts should be run to the open database , that is turned on by 11g

    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dblb
     sqlplus / as sysdba
     purge recyclebin;
  7. Run DBUA from 12c home to upgrade existing database
    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dblb
    /u01/app/oracle/product/12.1.0/dblb/bin/dbua -silent \
    -sid ORCL \
    -oracleHome /u01/app/oracle/product/11.2.0/dblb \
    -diagnosticDest /u01/app/oracle \
    -recompile_invalid_objects true \
    -degree_of_parallelism 40 \
    -upgradeTimezone \
    -emConfiguration NONE \
    -keepHiddenParams \
    -gatheringStatistics \
    -upgrade_parallelism 40
  8. To check that everything was upgraded successfully, after successful message from the previous command, check the following:
    cat /etc/oratab
    cat /u01/app/oraInventory/ContentsXML/inventory.xml

    Update the following parameters in your  .bash_profile:

     export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dblb
     export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dblb/lib
  9. Network file configuration. Stop listener that was previously started from 11g. Move listener.ora and tnsnames.ora files to 12c home and rename them in old location. Start the listener from 12c home.
    /u01/app/oracle/product/11.2.0/dblb/bin/lsnrctl stop
    cp /u01/app/oracle/product/11.2.0/dblb/network/admin/listener.ora /u01/app/oracle/product/12.1.0/dblb/network/admin
    cp /u01/app/oracle/product/11.2.0/dblb/network/admin/tnsnames.ora /u01/app/oracle/product/12.1.0/dblb/network/admin
    mv /u01/app/oracle/product/11.2.0/dblb/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0/dblb/network/admin/tnsnames.ora.old
    mv /u01/app/oracle/product/11.2.0/dblb/network/admin/listener.ora /u01/app/oracle/product/11.2.0/dblb/network/admin/listener.ora.old
    /u01/app/oracle/product/12.1.0/dblb/bin/lsnrctl start
  10. Connect to the database using 12c home and check again the version in v$instance view:
    export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dblb
    sqlplus / as sysdba
    select instance_name,version,status
    from v$instance;
  11. If you want to deinstall 11g home run the following :

Upgrade 10g XE to 11g XE

I’ve had a lot of trouble with 10g XE. Especially with its upgrading process to 11g XE. So I want to share my experience with you.

There are at least 2 options to do the upgrade.

First one is backup/restore and second one is export/import.

Note: I ‘ve installed 10g XE and 11g XE on separate servers(because couldn’t handle to install them together)

Note2: The 1st one doesn’t worked for me and is described shortly. The 2nd  one worked for me and is fully descriptive.

1. Take utlu112i.sql script from 11g XE and place it on 10g XE server.

2. Run this script on 10g XE.

Go to the gen_inst.sql file location or indicate a full path to the file:

sqlplus sys as sysdba @gen_inst.sql

3. Backup 10g XE database.

4. Restore to 11g XE

5. Startup upgrade.

6. Run the following scripts on 11g XE.


sqlplus sys as sysdba @catupgrd.sql


sqlplus sys as sysdba @utlrp.sql

BUT these steps that are described on most of the blogs and sites did not work for me.

Another option that worked for me is export/import.

1. Take gen_inst.sql script from 11g XE.

2. Place and run this script on 10g XE.

sqlplus sys as sysdba @gen_inst.sql

It will generate install.sql, gen_apps.sql and other .sql files

3. On 10g XE

CREATE DIRECTORY DUMP_DIR AS ‘C:\oraclexe\dump_dir’;

GRANT read, write ON DIRECTORY DUMP_DIR TO public;

expdp ‘sys/passwd as sysdba’ full=Y EXCLUDE=SCHEMA:\"LIKE \’APEX_%\’\",SCHEMA:\"LIKE \’FLOWS_%\’\" directory=DUMP_DIR dumpfile=DB10G.DMPlogfile=expdpDB10G.log

expdp ‘sys/passwd as sysdba’ TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ directory=DUMP_DIR dumpfile=DB10G2.dmp logfile=expdpDB10G2.log

Note: if ‘sys/passwd as sysdba’  doesn’t work try without it and then enter the credentials.

expdp  full=Y EXCLUDE=SCHEMA….
Username: sys@XE as sysdba
Password: *****

4. Import to 11g XE

CREATE DIRECTORY DUMP_DIR AS ‘C:\oraclexe\dump_dir’;
GRANT read, write ON DIRECTORY DUMP_DIR TO public;

impdp  ‘sys/passwd as sysdba’ full=Y directory=DUMP_DIR dumpfile=DB10G.DMP logfile=expdpDB10G1.log

impdp  ‘sys/passwd as sysdba’ directory=DUMP_DIR TABLE_EXISTS_ACTION=APPEND  TABLES=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ dumpfile=DB10G2.DMP logfile=expdpDB10G1b.log

5. Run install.sql. It will run other scripts itself.

sqlplus sys as sysdba @install.sql