UPGRADE ORACLE 10.2.0.1 TO 10.2.0.4 WINDOWS SERVER 2003 32-BIT
—————————————————————
1. CHECK FOR TIMZEZONE INFORMATION USING FOLLOWING SQL QUERY
SELECT version FROM v$timezone_file;
* If this query reports version 4, no action is required; in this case, continue with next steps.
* If this reports a version lower or higher then 4, proceed to step 2
2. when upgrading from 10.1.0.x or updating a lower 10.2.0.X version to 10.2.0.4.
Check if you are storing *user* 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’
order by col
/
Check if there is something outside the Data Dictionary (= other then SYS or WMSYS objects).
For TIMESTAMP WITH TIME ZONE (TSTZ) data types you need to run utltzpv4.sql to see if you have affected data. Any affected data needs to be saved as plain text (Varchar2) before applying 10.2.0.4 or upgrading to 10.2.0.4 and restored afterwards.
Download and unzip utltzpv4.sql by clicking on the link, the location of utltzpv4.sql is not critical, we suggest however to save it in $ORACLE_HOME/rdbms/admin .
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/utltzpv4.sql
DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
Your current timezone version is 1!
now checking all TIMESTAMP WITH TIMEZONE data..
.
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
WITH TIMEZONE data is affected by the update to RDBMS DSTv4 update.
.
Any table with YES in the nested_tab column (last column) needs
a manual check as these are nested tables.
PL/SQL procedure successfully completed.
After running utltzpv4.sql you can then do:
SQL> select * from sys.sys_tzuv2_temptab;
no rows selected
If a “select * from sys.sys_tzuv2_temptab;” gives no rows then there is no action to take for the Oracle time zone definitions, upgrade from 9i / 10.1 to 10.2.0.4 or apply 10.2.0.4 to a 10.2.0.X version.
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. See point 5) on how to do this. For SYS.SCHEDULER$% rows you need to drop and re-submit the jobs (see previous point).
3. Stop All Services
$ emctl stop dbconsole
$ isqlplusctl stop
$ lsnrctl stop
SQL> shutdown immediate
Use the Control Panel to stop the Oracle Database service OracleServiceSID where SID is the system identifier of the database.
4. Installing the Oracle Database 10g Patch Set Interactively
#
Log on as a member of the Administrators group to the computer on which you are going to install Oracle components. If you are installing on a Primary Domain Controller or a Backup Domain Controller, log on as a member of the Domain Administrators group.
#
Start Oracle Universal Installer located in the unzipped area of the patch set. For example, Oracle_patch\setup.exe.
#
On the Welcome screen, click Next.
#
In 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
5. POST INSTALLATION TASKS : Upgrading Oracle Database 10g Release 10.2.0.x to Oracle Database 10g Release 10.2.0.4
#
Log in with administrator privileges.
#
Set the values for the environment variables ORACLE_HOME, ORACLE_SID and PATH.
#
For single-instance installations, if you are using Automatic Storage Management, start the Automatic Storage Management instance.
#
For Oracle single-instance installations, start the listener as follows:
C:\> lsnrctl start
SQL> SHUTDOWN IMMEDIATE
The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 10.2. Complete the following procedure to run the Pre-Upgrade Information Tool:
1.
Start the database in the UPGRADE mode:
SQL> STARTUP UPGRADE
#
Set the system to spool results to a log file for later analysis:
SQL> SPOOL upgrade_info.log
#
Run the Pre-Upgrade Information Tool:
SQL> @?/rdbms/admin/utlu102i.sql
#
Turn off the spooling of script results to the log file:
SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in the upgrade_info.log file. The following is an example of the output generated by the Pre-Upgrade Information Tool:
Oracle Database 10.2 Upgrade Information Utility 02-04-2008 11:48:11
.
**********************************************************************
Database:
**********************************************************************
–> name: X102040
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1
–> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 505 MB
…. AUTOEXTEND additional space required: 15 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 401 MB
…. AUTOEXTEND additional space required: 376 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 265 MB
…. AUTOEXTEND additional space required: 15 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 58 MB
…. AUTOEXTEND additional space required: 38 MB
–> EXAMPLE tablespace is adequate for the upgrade.
…. minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: –> “shared_pool_size” needs to be increased to at least 167772160
WARNING: –> “java_pool_size” needs to be increased to at least 67108864
.
**********************************************************************
Components: [The following database components will be upgraded orinstalled]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID
–> Oracle Data Mining [upgrade] VALID
–> Messaging Gateway [upgrade] VALID
–> OLAP Analytic Workspace [upgrade] VALID
–> OLAP Catalog [upgrade] VALID
–> Oracle OLAP API [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Spatial [upgrade] VALID
–> Oracle Ultra Search [upgrade] VALID
–> Oracle Label Security [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> EM Repository [upgrade] VALID
–> Rule Manager [upgrade] VALID
PL/SQL procedure successfully completed.
SQL> SHUTDOWN
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sql
SQL> SPOOL OFF
#
Review the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script.
This list provides the version and status of each SERVER component in the database.
#
If necessary, rerun the catupgrd.sql script after correcting any problems.
#
Restart the database:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
#
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql
note : When the 10.2.0.4 patch set is applied to an Oracle Database 10g Standard Edition database, there may be 54 invalid objects after the utlrp.sql script runs. These objects belong to the unsupported components and do not affect the database operation.
Ignore any messages indicating that the database contains invalid recycle bin objects similar to the following:
BIN$4lzljWIt9gfgMFeM2hVSoA==$0
#
Run the following command to check the status of all the components after the upgrade:
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
In the output of the preceding command, the status of all the components should be VALID for a successful upgrade.
To configure and secure Enterprise Manager follow these steps:
Ensure the database and Listener are operational.
#
In the case of a single instance, execute
emca -upgrade db
