Complete Checklist for Manual Upgrades from 11.2.0.1 to 11.2.0.2

Complete Checklist for Manual Upgrades from 11.2.0.1 to 11.2.0.2

STEP 1

======

Download 11.2.0.2 rdbms software

The software can be downloaded from My Oracle support: patch 10098816

             https://updates.oracle.com/download/10098816.html

Please select your platform before downloading (Click on "Platform or Language" drop down menu) if the platform name contains (32-bit) then it's 32-bit 11.2.0.2 client version

To install 11.2.0.2, you must download both p10098816_112020_<platform>_1of7.zip and p10098816_112020_<platform>_2of7.zip

For the 11.2.0.2  Examples CD ( Formerly Companion CD), you need to download p10098816_112020_<platform>_6of7.zip

<platform> = your platform( ie for linux x86, download p10098816_112020_LINUX_1of7.zip and p10098816_112020_LINUX_2of7.zip )

STEP 2

======

Install 11.2.0.2 into a separate ORACLE_HOME

Please note that the same products must be installed into the 11.2.0.2 ORACLE_HOME for instance IF  your database is using Oracle Text themes  or IF you want to install Oracle Multimedia demos and other demos THEN you need to install 11.2.0.2 examples CD ( Formerly Companion CD) you can run "opatch lsinventory -detail" against 11.2.0.1 and 11.2.0.2 and compare installed products

STEP 3

======

After installing the 11.2.0.2 software, start the 11.2.0.1 in regular mode and spool/run 11.2.0.2 $ORACLE_HOME/rdbms/admin/utlu112i.sql script against 11.2.0.1 DB

Running the Pre-Upgrade Information Tool is recommended whether you are upgrading with DBUA or manually.

You need to review 11.2.0.2 pre-upgrade script spool file and fix any issues before upgrading to 11.2.0.2

One of the recommendations in the output is to purge recyclebin :

sqlplus / as sysdba

purge DBA_RECYCLEBIN;

STEP 4

======

Run dbupgdiag.sql script (from flti005:/oracle/app/oracle/local/dbupgdiag.sql) to verify that all the components in dba_registry are valid and no invalid data dictionary objects in dba_objects

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

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

STEP 5

======

Take a full  backup of the database (must be done prior to the upgrade)

STEP 6

Create pfile from spfile and shutdown the database

sqlplus / as sysdba

create pfile from spfile;

shutdown immediate;

STEP 7

=======

Unregister the database with the CRS agent:

. oraenv grid11

crsctl delete resource ora.SID.db

(where SID = database name)

Check that the database is no longer registered with the CRS :

crs_stat -t | grep SID

(where SID = database name)

should not return any records

STEP 8

=================

Configure the target 11.2.0.2 ORACLE_HOME

1) Make sure environment variables ORACLE_BASE,ORACLE_HOME, PATH, NLS_10 and LIBRARY_PATH are set to point to 11.2.0.2 installation. Set ORACLE_SID to the database you are about to upgrade. Unset TNS_ADMIN.

Modify the oratab file to point to your Oracle Database 11g Release 2 (11.2.0.2) Oracle home

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

3) copy network configuration files (listener.ora, sqlnet.ora, tnsnames.ora ..etc) from 11.2.0.1 $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location to 11.2.0.2 $ORACLE_HOME/admin/network (or $TNS_ADMIN) location (can be skipped if the files were copied once)

4) (Optional, might not exist)

Copy also the following two directories and their contents from 11.2.0.1 to 11.2.0.2

ORACLE_HOME/<hostname_dbname>

ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname_dbname>

Specify the actual name for <hostname_dbname>

5) Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database 11g Release 2 (11.2). The Pre-Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set (can set to 11.2.0.2.0 right away).

6) Adjust the values of the initialization parameters to at least the minimum values indicated by the Pre-Upgrade Information Tool.

STEP 9

======

Upgrade the database manually

1) 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 ---- usually the database is already down

2) Check the /tmp/upgrade.log file for errors

3) Restart the database in normal mode

sqlplus / as sysdba

startup

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

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

6) Run dbupgdiag.sql script and verify that all the components in dba_registry are valid and there are no invalid objects in dba_objects

7) Create spfile

sqlplus / as sysdba

create spfile from pfile;

shutdown immediate

startup

8) Register the database with CRS agent :

. oraenv grid11

srvctl add database -d <SID> -o /oracle/app/oracle/product/11.2.0.2 -p /oracle/app/oracle/product/11.2.0.2/dbs/spfile<SID>.ora

where <SID> is the database name

9) Check that the registration has succeeded:

crsctl check resource ora.<SID>.db

where <SID> is the database name

should not return any rows

10) Check that the service is running:

crs_stat -t | grep <SID>

where <SID> is the database name

should return something like this :

ora.<SID>.db ora....se.type ONLINE    ONLINE    fldi010

11) Verify that the entry in /etc/oratab is correct

POST UPGRADE STEPS

===================

1)    Upgrade time Zone to version 14 using DBMS_DST

Check current RDBMS DST version and "DST UPGRADE STATUS".

conn / as sysdba
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

-- check that the output gives

-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

-- DST_PRIMARY_TT_VERSION should match the value found when selecting

SELECT version FROM v$timezone_file;

exec DBMS_DST.BEGIN_PREPARE(14);

-- check for prepare status

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         4

DST_SECONDARY_TT_VERSION       14

DST_UPGRADE_STATE              PREPARE

-- truncate logging tables if they exist.

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

BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

-- check what tables have affected data that cannot be resolved automatically.
-- if this gives no rows then there is no problem at all

SELECT * FROM sys.dst$affected_tables;

If there are entries, please refer to the Metalink article mentioned above.

-- end prepare window, the rows above will stay in those tables.

EXEC DBMS_DST.END_PREPARE;

-- check if this is ended

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

- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

Do the actual RDBMS DST version update of the database

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on

-- check if previous prepare window is ended

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

-- output should be
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

-- If there are objects containing TSTZ data in recycle bin, please purge the bin now.
-- Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin".

purge dba_recyclebin;

-- clean used tables

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

-- start upgrade window

EXEC DBMS_DST.BEGIN_UPGRADE(14);

-- the message
-- "An upgrade window has been successfully started."
-- will be seen

-- check if this select

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

-- gives this output:
-- PROPERTY_NAME VALUE
-- --------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION <the old DST version number>
-- DST_UPGRADE_STATE UPGRADE

-- you can check what tables need to updated using

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

-- restart the database

shutdown immediate
startup

-- now upgrade the tables who need action

set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
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);
END;
/

-- ouput of this will be a list of tables lie:

-- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
-- Number of failures: 0
-- ....
-- Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
-- Number of failures: 0
-- Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
-- Number of failures: 0
-- Failures:0

-- if there where no failures then end the upgrade.

VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

-- output that will be seen:
-- An upgrade window has been successfully ended.
-- Failures:0

-- last checks

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

-- needed output:
-- PROPERTY_NAME VALUE
-- ---------------------------- ------------------------------
-- DST_PRIMARY_TT_VERSION <the new DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

SELECT * FROM v$timezone_file;

-- needed output:
-- FILENAME VERSION
-- ------------------ ----------
-- timezlrg_<new version>.dat <new version>

2) Collect system statistics

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

3)Upgrade the Recovery Catalog (optional)

For complete information about upgrading the recovery catalog and the UPGRADE

CATALOG command, see Oracle Database Backup and Recovery User's Guide for the

topic that describes the procedures.

4) Upgrade Statistics Tables Created by the DBMS_STATS Package (optional)

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by running the following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('scott', 'stat_table');

In the example, SCOTT is the owner of the statistics table and STAT_TABLE is the name of the statistics table. Perform this procedure for each statistics table.