MANAUL UPGRADE 10.2.X to 11.2.0.

2 Process DOC

Compatibility Matrix

Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2

Source Database Target Database

9.2.0.8 or higher 11.2.x
10.1.0.5 or higher 11.2.x
10.2.0.2 or higher 11.2.x
11.1.0.6 or higher 11.2.x

The following database version will require an indirect upgrade path.

Source Database Upgrade Path for

Target Database Target Database
7.3.3 (or lower) ----> 7.3.4 -> 9.2.0.8 ----> 11.2.x
8.0.5 (or lower) ----> 8.0.6 -> 9.2.0.8 ----> 11.2.x
8.1.7 (or lower) ----> 8.1.7.4 -> 10.2.0.4 ----> 11.2.x
9.0.1.3 (or lower) ----> 9.0.1.4 -> 10.2.0.4 ----> 11.2.x
9.2.0.7(or lower) ----> 9.2.0.8 ----> 11.2.x

For example:-
If source database is 8.1.7.0.0, the upgrade path to be followed is as below
8.1.7.0.0 --> 8.1.7.4 --> 10.2.0.4--> 11.2.x.

Prerequisites Pre Upgrade

1) Take the full database backup. (rman cold /cold backup).

2) Make sure default tablesapce for SYS and system user is SYSTEM.

Select default_tablespace,username from dba_users where username in (‘SYS’,’SYSTEM).

If not
 SQL> ALTER user SYS default tablespace SYSTEM;

SQL> ALTER user SYSTEM default tablespace SYSTEM;

3) Truncate the AUD$ table.

SQL> Truncate table AUD$;

4) Make sure AUD$ and its index are in system tablesapce.

SQL>Select tablespace_name from dba_tables where table_name=’AUD$’;

If Not –login as sys user and issue below command and run the spooled o/p.

SQL> Select ‘alter table ‘||table_name||move tablespace SYSTEM||’;’Chr(10)||

‘alter index ‘||index_name||’ rebuil dtablespace SYSTEM’||’;’ from dba_indexes where
table_name=’AUD$’;

5) Purge recyclebin;
SQL> Purge dba_recyclebin;

6) This optional check is introduced to spot any logical corruption in underlying object and its
dependent.
Login as sys user

Set verify off

Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'

FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
 AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

Now execute the following steps.

$ sqlplus "/ as sysdba"

SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

7) Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.

SQL>SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times;

8) Ensure that no files need media recovery

SQL>Select count(*) from v$recover_file;

o/p should be zero rows.

9) Ensure that no files are in backup mode

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

10) Resolve outstanding distributed transactions prior to the upgrade.

SQL> select * from dba_2pc_pending;

if this returns rows you should do the following.

SQL> SELECT local_tran_id

FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');--enter the local tran id
SQL> COMMIT;

11) Create Script for DBLINK (In case the database has to be downgraded again)
 SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

12) Gather dictionary stats.

SQL>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

13) Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

Copy the below file in temporary location from the installed 11g home location.

$ORACLE_HOME/rdbms/admin/utlu112i.sql

14) Login to Current Version database and run the above script. It will spool the recommendations
for upgrade.

$ sqlplus '/ as sysdba'

SQL> spool /tmp/upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>

NOTE: need to follow all the suggestions as per the spooled log
If it recommends for timezone upgrade the same can be done post upgrade using DST
package.

15) Check for the integrity of the source database.

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

It will give the information for all invalid objects.

16) Run the utlrp script to validate the invalid objects.

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

Re-run the script two to three times.

17) Note down the location of datafiles, redo logs, control files. Also take a backup of all
configuration files like listener.ora,tnsnames.ora,etc., from $ORACLE_HOME

SQL>SELECT name FROM v$controlfile

Union all
SELECT file_name FROM dba_data_files
Union all
SELECT group#, member FROM v$logfile;

18) Check for Job process and set them to zero.

SQL> Sho parameter job

Note down the value for job_queue_process
SQL> Alter system set jo_queue_process-0;

Start for Upgrade

19) Shut down the database;

SQL >Shut immediate;

20) Stop listener

Lsnrctl stop

21) Make changes in .bash_profile with new 11.2.0.2 home.

22) Copy the init.ora from old Oracle_home to New Oracle_home and make necessary chages as per
the utlu112i.sql spool.

23) Make changes in /etc/oratab to disable automatic startup

orcl:/opt/oracle/product/11.2/db_1:N

24) Startup upgrade process

 $ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE

25) Spool the upgrade process

SQL> set echo on

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

Once completed check for any errors occurred.

26) Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a

summary of the upgrade at the end of the spool log. It displays the status of the database
components in the upgraded database and the time required to complete each component
upgrade. Any errors that occur during the upgrade are listed with each component and must be
addressed

$ sqlplus "/as sysdba"

SQL> shut immediate

SQL> spool /tmp/post_upgrade.log

SQL> STARTUP
SQL> @utlu112s.sql

SQL> spool off

If the Post-Upgrade Status Tool returns errors or shows components that are not VALID or not
the most recent release, then see: Metalink

Expected o/p will be like

.
Oracle Database 11.2 Post-Upgrade Status Tool 07-11-2010 15:24:48
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.2.0.2.0 00:16:24
JServer JAVA Virtual Machine
. VALID 11.2.0.2.0 00:10:08
Oracle Workspace Manager
. VALID 11.2.0.2.0 00:00:37
OLAP Analytic Workspace
. VALID 11.2.0.2.0 00:00:30
OLAP Catalog
. VALID 11.2.0.2.0 00:01:02
Oracle OLAP API
. VALID 11.2.0.2.0 00:00:35
Oracle Enterprise Manager
. VALID 11.2.0.2.0 00:08:57
Oracle XDK
. VALID 11.2.0.2.0 00:02:35
Oracle Text
. VALID 11.2.0.2.0 00:00:54
Oracle XML Database
. VALID 11.2.0.2.0 00:04:16
Oracle Database Java Packages
. VALID 11.2.0.2.0 00:00:24
Oracle Multimedia
. VALID 11.2.0.2.0 00:05:16
Spatial
. VALID 11.2.0.2.0 00:05:12
Oracle Expression Filter
. VALID 11.2.0.2.0 00:00:12
Oracle Rule Manager
. VALID 11.2.0.2.0 00:00:09
Gathering Statistics
. 00:06:11
Total Upgrade Time: 01:03:33

PL/SQL procedure successfully completed.

27) There are some further actions that need to be done, but they do not require the database to be in
upgrade mode. Now is the time to perform those action via catuppst.sql script

SQL> @catuppst.sql

28) Now run the utlrp.sql script to compile the objects which were invalidated during the upgrade.

SQL> @utlrp.sql

29) Check for the integrity of the source database.

Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

 It will give the information for all invalid objects.

30) Again run the Utlrp.sql

UPGRADE COMPLETES HERE

Post Upgrade Steps

31) For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new
ORACLE_HOME. Start the listener :

lsnrctl start

32) Reset the job Queue processes.

33) Check for invalid objects.

34) Check for password case Sensitivity if required disable it.

SQL> Alter system set sec_case_sensitive_logon=FALSE;

Time Zone Upgrade

To fix the Miscellaneous Warnings that were suggested by the Pre-Upgrade tool as to be
done after the upgrade.
Like

WARNING: --> Database is using a timezone file older than version 14

We can upgrade the time Zone if the application data is based on time zone.This can be done
using the DBMS_DST package.

Current release 11.2.0.2 comes with version 14. Hence below steps are referred to upgrade
from version below 14.

Process Is detailed As below.

A) check current RDBMS DST version and "DST UPGRADE STATUS".

conn / as sysdba
SQL > 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>--imp
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE

-- DST_PRIMARY_TT_VERSION should match the value found when selecting

SQL> SELECT version FROM v$timezone_file;

PREPARE WINDOW

conn / as sysdba

-- start prepare window

-- these steps will NOT update any data yet.

-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691

SQL >alter session set "_with_subquery"=materialize;

SQL> exec DBMS_DST.BEGIN_PREPARE(<the new DST version number>)

note error : -

SQL> exec DBMS_DST.BEGIN_PREPARE(13)

BEGIN DBMS_DST.BEGIN_PREPARE(13); END;

*
ERROR at line 1:
ORA-30094: failed to find the time zone data file for version 13 in
$ORACLE_HOME/oracore/zoneinfo
ORA-06512: at "SYS.DBMS_DST", line 57
ORA-06512: at "SYS.DBMS_DST", line 1258
ORA-06512: at line 1

It says Sample error if the requested new DST version is the current or a lower than the current timezone
version: if you do not get any error then proceed.

-- check for prepare status

SQL> 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 <the new DST version number>
-- DST_UPGRADE_STATE PREPARE

-- truncate logging tables if they exist.

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

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

-- log affected data

SQL > set serveroutput on
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

SQL> SELECT * FROM sys.dst$affected_tables;

-- IF previous select gives rows then you can see

-- what kind of problem there are in those rows

SQL > SELECT * FROM sys.dst$error_table;

-- error_on_overlap_time is error number ORA-1883

-- error_on_nonexisting_time is error number ORA-1878

-- for a explanation of the reported data please see

-- "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data"
-- For the "error_on_overlap_time" and "error_on_nonexisting_time" you do not HAVE to
-- take action on this data to upgrade the DST version, but it is advised
-- to at least to check the results AFTER the update.

-- all "error_on_overlap_time" rows

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

-- all "error_on_nonexisting_time" rows

SQL > SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

-- check for all other possible problems

SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

When updating from DSTv1 or DSTv2 (mainly after 9.2 upgrades) to a higher DST version it is possible to
have also '1882' errors.
The cause is explained in Note 414590.1. These can be ignored, they will be corrected during the actual
update of the dst version.
-- end prepare window, the rows above will stay in those tables.

SQL> EXEC DBMS_DST.END_PREPARE;

-- check if this is ended

SQL> 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

B) Do the actual RDBMS DST version update of the database using DBMS_DST:

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

-- check if previous prepare window is ended

SQL> 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 DST_UPGRADE_STATE is "PREPARE" then you did not ended

-- the prepare window in step 3)
-- 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".

SQL > purge dba_recyclebin;

-- clean used tables

SQL >TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

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

-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691

SQL> alter session set "_with_subquery"=materialize;

START UPGRADE WINDOW

SQL > EXEC DBMS_DST.BEGIN_UPGRADE(<the new DST version number>);

-- 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

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES

where UPGRADE_IN_PROGRESS='YES';

-- restart the database

SQL> shutdown immediate
SQL> startup

-- this alter session might speed up DBMS_DST on some db's

-- see Bug 10209691

alter session set "_with_subquery"=materialize;

-- now upgrade the tables who need action

SQL> 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 like:

-- 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.

SQL>
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

TIME ZONE UPGRADE COMPLETES HERE…