Upgrade from 10.2.0.1 to 10.2.0.4

1. Patching Oracle Database 10.2.0.1 to 10.2.0.4

1.1 Pre-installation Tasks

- Identify Oracle home directories, view the/etc/oratab file

- Download the p6810189_10204_Linux-x86.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory (patch set id: 6810189)

- Check the current version of the Oracle time zone definitions with the following command:

SELECT version FROM v$timezone_file;

If this query reports version 4, no action is required;

If this reports a version lower or higher then 4:

Check if you are storing TZ (TSTZ and TSLTZ) data use this sql query:

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col
from dba_tab_cols c, dba_objects o
where c.data_type like '%TIME ZONE'
and c.owner=o.owner
and c.table_name = o.object_name
and o.object_type = 'TABLE'

and o.owner not in ('SYS','WMSYS')
order by col
/

For the sys objects, check if you are using DBMS_SCHEDULER for scheduler$_job and so on

SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_window);

OBJECT_NAME
-----------------------------------------------------------------

WEEKNIGHT_WINDOW
WEEKEND_WINDOW

SQL> SELECT object_name FROM dba_objects WHERE object_id IN (SELECT obj# FROM scheduler$_job);

OBJECT_NAME
-----------------------------------------------------------------

PURGE_LOG
FGR$AUTOPURGE_JOB
GATHER_STATS_JOB
AUTO_SPACE_ADVISOR_JOB
RLM$EVTCLEANUP
RLM$SCHDNEGACTION

6 rows selected.

If you have no user TZ (TSTZ and/or TSLTZ) data and only the Oracle provided jobs in DBMS_SCHEDULER jobs defined then there is no action to take for the Oracle time zone definitions,.

If you have user TZ (TSTZ and/or TSLTZ) data and / or your own DBMS_SCHEDULER jobs then:

After upgrading to 10.2.0.4 a "select * from dba_scheduler_jobs;" (look for columns with timezone datatype) and qury tables with timezone datarypes 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 run Fix1882.sql mentioned in Note 414590.1. This will not harm the database.

- download and unzip utltzpv4.sql from the link in OracleMetalink document 553812.1

SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/utltzpv4.sql

Then,

SQL> select * from sys.sys_tzuv2_temptab;

If a "select * from sys.sys_tzuv2_temptab;" gives no rows then there is no action to take.You can skip any DST related sections in the upgrade documents or Patchset instructions.

If a "select * from sys.sys_tzuv2_temptab;" give(s) affected columns then you need to back up the reported columns before upgrading to 10.2.0.4[Metalink Document: 414590.1].

For SYS.SCHEDULER$% rows you need to drop and re-submit the jobs.

1.2 Install Patch Set Software (binary)

- Set the ORACLE_HOME and ORACLE_SID Environment Variables

$ ORACLE_HOME=Oracle_home

$ ORACLE_SID=SID

$ export ORACLE_HOME ORACLE_SID

- Shut down all database instances running in the Oracle home directory, where you need to install the patch set.

$ emctl stop dbconsole

$ isqlplusctl stop

$ lsnrctl stop

$ sqlplus / as sysdba

SQL> shutdown immediate

- Back Up the System, it may be manually or later by using DBUA

- Log in as the oracle user.

- If you are not installing the software on the local computer, then run the following command on remote machine:

$ export DISPLAY=localhost:0.0

- In this example, local_host is the host name or IP address of the computer that you want to use to display Oracle Universal Installer.

- Now to enable X applications, run the following command on the machine that you want to use to display Oracle Universal Installer:

$ xhost + [fully_qualified_remote_host_name]

Enter the following commands to start Oracle Universal Installer, where patchset_directory is the directory where you unpacked the patch set software:

$ cd patchset_directory/Disk1
$ ./runInstaller

- On the Welcome screen, click Next.

- On the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.

- On the Product-specific Prerequisite Checks screen, correct any reported errors, and click Next.

- On the Summary screen, click Install.

- This screen lists all of the patches available for installation.

- When prompted, run the $ORACLE_HOME/root.sh script as the root user

- On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

-

1.3 Upgrading a Release 10.2 Database using Oracle Database Upgrade Assistant

After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:

- Log in as the Oracle software owner user.

- Set the values for the environment variables $ORACLE_HOME, $ORACLE_SID and $PATH.

- For Oracle single-instance installations, start the listener as follows:

$ lsnrctl start

- Enter the following command from the command prompt:

$ dbua

- Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

1. On the Welcome screen, click Next.

2. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.

3. On the Recompile Invalid Objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.

4. If you have not taken the back up of the database earlier, on the Backup - screen, select the I would like to take this tool to backup the database option, stipulate the Path, then click Next.

5. On the Summary screen, check the summary, then click Finish.

6. On the End of Database Upgrade Assistant's Upgrade Results screen, click Close to exit from Oracle Database Upgrade Assistant.

After upgrading to 10.2.0.4 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 run Fix1882.sql mentioned in Note 414590.1. This will not harm the database.