Upgrade DB version from 10.2.0.4 to 11.2.0.1

1 Upgrade Oracle Database from 10.2.0.4 to 11.2.0.1

1.1 Pre-installation Tasks

- Upgrade 10.2.0.1 to any upper version (we upgraded to 10.2.0.4)

- Install Oracle Database 11gR2 software (only binary, no database and must be in different ORACLE_HOME):

1. In our case we install in in same ORACLE_BASE but Different ORACLE_HOME

2. on RHEL5R4 some kernel parameter value needed to be changed and some rpm needed to

be installed. Change those and use sysctl –p (from root) to reload kernel parameters.

3. In case of installing unix-ODBC and unix-ODBC-devel rpm, first install unix-ODBC and unix-ODBC-devel to avoid dependency warnings.

4. ./runinstaller (install Software only)

5. After resolving all requirements use check again button to recheck.

6. Press Next and complete SW installation process.

7. Incase of running $ORACLE_HOME(new)/root.sh accept all default values.

- Run the Pre-Upgrade Information Tool

1. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle home directory.

2. Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

Make a note of the new location of this file.

3. Log in to the system as the owner of the Oracle home directory of the database to be upgraded.

4. Change to the directory that you copied utlu112i.sql to in Step 2.

5. Start SQL*Plus.

6. Connect to the database instance as a user with SYSDBA privileges.

7. Set the system to spool results to a log file for later analysis:

SQL> SPOOL upgrade_info.log

8. Run the Pre-Upgrade Information Tool:

SQL> @utlu112i.sql

9. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in upgrade_info.log

- After upgrading to the new Oracle Database 11g release the CONNECT role has only the CREATE SESSION privilege, so do necessary adjustment to those user by granting individual privileges to them

- Save Oracle Enterprise Manager Database Control Data

cd new_oracle_home

./emdwgrd -save -sid old_SID -path save_directory

- Complete Materialized View Refreshes

For a list of materialized view refreshes that need to be completed, issue the following statement:

SELECT DISTINCT(TRUNC(last_refresh))

FROM dba_snapshot_refresh_times;

- Ensure No Files Need Media Recovery

For a list of files that require media recovery, issue the following statement:

SELECT * FROM v$recover_file;

- Ensure No Files Are in Backup Mode

For a list of files in backup mode, issue the following statement:

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

- Resolve Outstanding Distributed Transactions

Resolve outstanding distributed transactions prior to the upgrade.

SELECT * FROM dba_2pc_pending;

If this query returns any rows, then issue the following statements:

SQL> SELECT local_tran_id FROM dba_2pc_pending;

SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');

SQL> COMMIT;

- SQL > PURGE DBA_RECYCLEBIN

- SQL > EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

- Create New LISTENER with new ORACLE 11gR2 SW:

1. stop the current listener using 10gR2 SW

2. export ORACLE_HOME = new_oracle_home(11gR2)

export ORACLE_BASE = new_oracle_base(11gR2)

[to generate listener.ora and log to new locations]

3. Change your directory to $ORACLE_HOME(new)/bin

4. ./netca

5. Use same configuration as previous LISTENER

- backup the DATABASE being upgraded (or backup using DBUA)

1.2 Upgrade Database Using DBUA

- export ORACLE_HOME = old_oracle_home

- export ORACLE_BASE = old_oracle_base

- export ORACLE_SID = SID_being_upgraded

- cd new_oracle_home

- ./dbua (if upgraging os user are different then it cannot be used)

- Select the DB being upgraded

[if 10.2.0.1 is used(not upgraded to later version of 10gR2) then during gathering information Error like “The CEP file rdbmsup.sql does not provide the version directive”. In this case patch 10.2.0.1 to later version.]

- Warnings for Optimizer Statistics ( can be ignored, if stat is gathered on previous step using DBMS_STATS.GATHER_DICTIONARY_STATS, ACL (resolve after upgrade), time zone (resolve after upgrade), invalid objects (DBUA will recompile them) and Warning about OEM (can be ignored, as we have a downgrade backup, 9.3.1)

- Resolve other warnings and proceed next

- Select backup option and location if not done previously

- Go through all the steps and finish DBUA process

- Update Environment variables (ORACLE_BASE, ORACLE_HOME, PATH and oratab)

1.3 Upgrade the Time Zone to Version 11

Upgrading a time zone file and timestamp with time zone data contains the following steps:

- timezone_11.dat & timezlrg_11.dat will be found in $ORACLE_HOME/oracore/zoneinfo

- Shut down the database

- Start up the database in UPGRADE mode

- Execute DBMS_DST.BEGIN_UPGRADE(11)

1. If the BEGIN_UPGRADE execution fails, an ORA-56927 error (Starting an upgrade window failed) will be raised.

2. After BEGIN_UPGRADE finishes executing with errors, check sys.dst$error_table to determine if the dictionary conversion was successful.

3. If successful, there will not be any rows in the table.

4. If there are errors, correct these errors manually and rerun DBMS_DST.BEGIN_UPGRADE(11). See "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data" on page 4-26 in “Oracle® Database Globalization Support Guide 11g Release 2 (11.2)”

- Restart the database in normal mode.

Note: Note that only one DBA should run the prepare window at one time. Also, make sure to correct all errors before running the upgrade.

- Truncate the error and trigger tables (by default, sys.dst$error_table and sys.dst$trigger_table).

- Upgrade the TSTZ data in all tables by invoking DBMS_DST.UPGRADE_DATABASE as bellow:

VAR numfail number

exec 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 => TRUE,

error_on_nonexisting_time => TRUE);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

/

If there are any errors, you should correct them and use UPGRADE_TABLE on the individual tables:

- query SYS.DST$ERROR_TABLE table to find tables with error

- then use UPGRADE_TABLE as below:

VAR numfail number

exec DBMS_DST.UPGRADE_TABLE(:numfail,

table_list => 'SCOTT.t, SCOTT.TABLE_TEST',

parallel => TRUE,

continue_after_errors => FALSE,

log_errors => TRUE,

log_errors_table => 'SYS.DST$ERROR_TABLE',

error_on_overlap_time => FALSE,

error_on_nonexisting_time => TRUE,

log_triggers_table => 'SYS.DST$TRIGGER_TABLE',

atomic_upgrade => TRUE);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

For details, See "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data" on page 4-26 in “Oracle® Database Globalization Support Guide 11g Release 2 (11.2)”

- Check to see that all tables have finished being upgraded by querying the DBA_TSTZ_TABLES view. Then look at dst$error_table to see if there were any errors. If there were errors, correct the errors and rerun DBMS_DST.UPGRADE_TABLE on the relevant tables.

- End the upgrade window by invoking DBMS_DST.END_UPGRADE as below:

Var numfail number;

Exec DBMS_DST.END_UPGRADE(:numfail);

- We also need to check as below:

A "select * from dba_scheduler_jobs;" (look for columns with timezone datatype) and query tables with timezone data type columns from the sqlplus found in the oracle_home on the server (!!! this is important !!!) gives "ORA-01882: timezone region %s not found" then you need to fix it as mentioned in Note 414590.1