Manual Upgradation of 11.2.0.1 Database to 11.2.0.2

Steps in 11.2.0.1 Environment:

1. Install 11.2.0.2 into a separate ORACLE_HOME

2. Running the Pre-Upgrade Information Tool
After installing the 11.2.0.2 software, start the 11.2.0.1 instance with the 11.2.0.1 ORACLE_HOME and spool/run 11.2.0.2 $ORACLE_HOME/rdbms/admin/utlu112i.sql script against the running 11.2.0.1 instance.

3. Review 11.2.0.2 pre-upgrade script spool file  from Step 2 and fix any issues before upgrading to 11.2.0.2

4. Run dbupgdiag.sql script to verify that all the components in dba_registry are valid and no invalid data dictionary objects in dba_objects

5. If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

6. Shut down the database cleanly

Steps in 11.2.0.2 Env

7. Set Environment variable ORACLE_HOME to point to 11.2.0.2 installation

8. Create pfile from spfile

9. Change cluster_database=false

10. Copy init.ora/spfile and password file (orapw<sid>.ora) from 11.2.0.1 $ORACLE_HOME/dbs to 11.2.0.2 $ORACLE_HOME/dbs

11. Upgrade the database manually
a)start sqlplus and run catupgrd.sql script

sqlplus " / as sysdba "
SQL> spool /tmp/upgrade.log
SQL> startup upgrade
SQL> set echo on
SQL> @catupgrd.sql;
SQL> spool off
SQL> Shutdown immediate


b) Check catupgrd.sql spool file for errors

12. Restart the database in normal mode

13. SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql;

14. SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;

15. Run dbupgdiag.sql script again to verify that all the components in dba_registry are valid and no invalid data dictionary objects in dba_objects
POST UPGRADE STEPS

16. Upgrade time Zone to version 14 using DBMS_DST

17. Upgrade the Recovery Catalog RMAN (Add the steps)

18. Run srvctl.

From old 11.2.0.1 Oracle_Home:
% $ORACLE_HOME/bin/srvctl config database -d db_name -a
% $ORACLE_HOME/bin/srvctl config service -d db_name
% $ORACLE_HOME/bin/srvctl remove database -d db_name

From 11.2.0.2 Oracle_Home:

% $ORACLE_HOME/bin/srvctl add database -d db_name -o <location of 11g home>
% $ORACLE_HOME/bin/srvctl add instance -d db_name -i instance -n node
% $ORACLE_HOME/bin/srvctl add service -d db_name –s service_name

Steps to do for Timezone Upgrade
---Check the current version of file ---
SQL> select version from V$TIMEZONE_FILE;

   VERSION
----------
11

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME       VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION       11
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE       NONE

---Confirm if $ORACLE_HOME/oracore/zoneinfo contains timezlrg_14.dat i.e version 14 file

--Prepare for upgrade
SQL> exec DBMS_DST.BEGIN_PREPARE(14);

PL/SQL procedure successfully completed.

--Confirm status

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME       VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION       11
DST_SECONDARY_TT_VERSION       14
DST_UPGRADE_STATE       PREPARE

---Re-running prepare statement will give below error.
SQL> exec DBMS_DST.BEGIN_PREPARE(14);
BEGIN DBMS_DST.BEGIN_PREPARE(14); END;

*
ERROR at line 1:
ORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in
an active state
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1340
ORA-06512: at line 1

-- truncate logging tables if they exist.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;

-- log affected data

BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/
---Query table to check if any affected table
SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL>
SQL> SELECT * FROM sys.dst$error_table;

no rows selected

-- End the prepare phase
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

--Startup the database in upgrade mode. Ensure you have set cluster_database=false

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

-- To confirm whether it has been upgraded to version 14
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

PROPERTY_NAME       VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION       14
DST_SECONDARY_TT_VERSION       11
DST_UPGRADE_STATE       UPGRADE

--Restart the database after removing cluster_database

VVI – Make sure you can connect using the services.