Oracle Upgrade 10.2.0.4 -> 11.2.0.3

Pre_upgrade

export ORACLE_SID=SID
export ORACLE_HOME=/u01/app/oradb/product/10.2.0.4/db_1


sqlplus "/ as sysdba"
SQL>shutdown immediate
SQL>STARTUP UPGRADE
SQL>SPOOL /export/home/oradb/upgrade_info.log
SQL>@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlu112i.sql
SQL>SPOOL OFF

SQL>@/u01/app/oradb/product/10.2.0.4/db_1/rdbms/admin/utlrp.sql

SQL>shutdown immediate;
SQL>spool off
SQL>exit

//////////////////////////////////////////////////////////////////////////////////////////////////


cp /u01/app/oradb/product/10.2.0.4/db_1/dbs/iniSID.ora  /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/

Удаляем устаревшие параметры из : /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/initSID.ora

background_dump_dest
user_dump_dest      

Добавляем устаревшие параметры в : /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/initSID.ora

*.diagnostic_dest='/u01/app/oradb'


////////////////////////////////////////////////////////////////////////////////////////////////////

Upgrade

export ORACLE_BASE=/u01/app/oradb
export ORACLE_HOME=${ORACLE_BASE}/product/11.2.0.3/dbhome_1
export PATH=${ORACLE_HOME}/OPatch:${ORACLE_HOME}/bin:${PATH}

. envdb11203

$env


sqlplus "/ as sysdba"
STARTUP UPGRADE
SPOOL /export/home/oradb/upgrade_db.log
SPOOL /home/oradb/upgrade_db.log

@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/catupgrd.sql

   Если завершается с ошибкой :
      SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
      *
      ERROR at line 1:
      ORA-01722: invalid number

   то возможно поможет рекомендация [ID 1255474.1]:

   update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
   commit; 
  
   и повторяем запуск
   @/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/catupgrd.sql


shutdown immediate;
startup
SPOOL OFF
exit
EOF

Post Upgrade:

sqlplus "/ as sysdba"
SPOOL /tmp/invalid_test1.log
@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlu112s.sql;
@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/catuppst.sql;
@/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlrp.sql;
spool off
exit
EOF

    Инвалидные объекты X_$KGLLK и X_$KSLEI можно удалить [ID 878623.1]

    drop view X_$KGLLK;
    drop public synonym X$KGLLK;

    drop view X_$KSLEI;
    drop public synonym X$KSLEI;

    И повторяем рекомпиляцию
    @/u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/admin/utlrp.sql;


SQL> create spfile from pfile;

SQL> shutdown immediate;
SQL> startup;

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

SQL> shutdown immediate;
SQL> startup;