Upgrade Database from Oracle 10.2.0.1 To 10.2.0.4
Requirements:
Oracle database 10g patch set 10.2.0.4 download from
www.metalink.oracle.com
Step 1: Prerequisites check to before applying the patch set (6810189).
Check your dba_registry view oracle database components status is
valid or not, version is 10.2.0.1. Have a look at the below output
To Check database components status is valid or not and version is 10.2.0.1
sql>Column comp_name format a40
sql>Column version format a12
sql>Column status format a6
sql>Select comp_name, version, status from sys.dba_registry;
COMP_NAME VERSION STATUS
———————————————– ———— ——
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 VALID
Oracle Workspace Manager 10.2.0.1.0 VALID
JServer JAVA Virtual Machine 10.2.0.1.0 VALID
Oracle XDK 10.2.0.1.0 VALID
Oracle Database Java Packages 10.2.0.1.0 VALID
Oracle Expression Filter 10.2.0.1.0 VALID
Oracle Data Mining 10.2.0.1.0 VALID
Oracle Text 10.2.0.1.0 VALID
Oracle XML Database 10.2.0.1.0 VALID
Oracle Rules Manager 10.2.0.1.0 VALID
Oracle interMedia 10.2.0.1.0 VALID
OLAP Analytic Workspace 10.2.0.1.0 VALID
Oracle OLAP API 10.2.0.1.0 VALID
OLAP Catalog 10.2.0.1.0 VALID
Spatial 10.2.0.1.0 VALID
Oracle Enterprise Manager 10.2.0.1.0 VALID
17 rows selected.
Check your v$version view
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
Check your database objects are valid or not
SQL> select object_name,status from dba_objects where status=’INVALID’;
No rows selected
In my environment there are no invalid objects.
If you have any invalid objects then you have to really Validate your database objects through the below scripts
SQL> exec utl_recomp.recomp_serial (); -> this is for all user’s objects across the database
Then stop all the process like sql, emctl, tnslistener and shutdown your database.
$ sqlplus / as sysdba
SQL> shutdown immediate
Database closed
Database dismounted.
ORACLE instance shut down.
$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Stopping iSQL*Plus …
iSQL*Plus stopped.
$ emctl stop dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved
http://ora10.home.com:1158/em/console/aboutApplication
Stopping Oracle Enterprise manager 10g database Control
….Stoped
$ lsnrctl stop
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
Step 2:
Cold Backup of your oracle database 10g and $ORACLE_HOME using Operating system
Command
Step 3:
Manage your data with Time Zone before upgrade
In my environment there are no data and scheduler jobs with Time Zone
information that’s why I skip the step. If you have huge volume of data and
scheduler jobs means please follow the below steps
SQL> select version from v$timezone_file;
Version
———-
2
If this gives 4 then you may simply proceed with the upgrade even if you have TZ data.
If this gives higher then 4 look at the Meta link note: Note 553812.1
If this gives lower then 4 perform the following steps:
If this gives higher then 4 look at the Meta link note: Note 553812.1
If this gives lower then 4 perform the following steps:
Download utltzpv4.sql from www.metalink.oracle.com and run it.
SQL> @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 2!
Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE
data is affected by version 4 transition rules.
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.
Commit complete.
Once the script finishes successfully execute the following query
sql>column table_owner format a4
sql>column column_name format a18
sql>select * from sys_tzuv2_temptab;
TABL TABLE_NAME COLUMN_NAME ROWCOUNT NES
---- ------------------------------ ------------------ ----------
SYS SCHEDULER$_JOB LAST_ENABLED_TIME 3
SYS SCHEDULER$_JOB LAST_END_DATE 1
SYS SCHEDULER$_JOB LAST_START_DATE 1
SYS SCHEDULER$_JOB NEXT_RUN_DATE 1
SYS SCHEDULER$_JOB START_DATE 1
SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE 1
SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE 1
SYS SCHEDULER$_WINDOW LAST_START_DATE 2
SYS SCHEDULER$_WINDOW NEXT_START_DATE 2
9 rows selected.
Step 3: Applying Oracle database 10g patch set (Patch number 6810189)
After downloaded the patch set, you have login as a root user and execute the following
# xhost +SI:localuser:oracle
Install the patch set 10.2.0.4
$ cd /home/oracle
$ unzip p6810189_10204_Linux-x86.zip
$ cd Disk1/
$ ./runInstaller
Click Next to proceed the next step
Here you should provide your $ORACLE_HOME(10.2.0.1) detail
Click Next
Click Next
Click Install
Click OK and run the above script as a root user
come and you can see the error in the alert log file
$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1268896 bytes
Variable Size 171967328 bytes
Database Buffers 427819008 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
$ tail -f /opt/oracle/admin/test/bdump/alert_ora10g.log
SMON: enabling cache recovery
Fri Jul 2 15:30:15 2010
Errors in file /u01/apps/oracle/admin/ora10g/udump/ora10g_ora_12856.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Fri Jul 2 15:30:15 2010
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 12856
ORA-1092 signalled during: ALTER DATABASE OPEN…
Now startup the database with upgrade option and run the pre-upgrade information tool to see if there are any recommendation. If there is any recommendation then you have to really follow the recommendations.
Let’s start the upgrade process.
$ cd $ORACLE_HOME/rdbms/admin
SQL> spool upgrade.log
SQL> @catupgrd.sql
[output trimmed]
Oracle Database 10.2 Upgrade Status Utility 07-02-2010 18:13:40
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.4.0 00:09:32
JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:34
Oracle XDK VALID 10.2.0.4.0 00:00:29
Oracle Database Java Packages VALID 10.2.0.4.0 00:00:39
Oracle Text VALID 10.2.0.4.0 00:00:20
Oracle XML Database VALID 10.2.0.4.0 00:01:29
Oracle Workspace Manager VALID 10.2.0.4.3 00:00:39
Oracle Data Mining VALID 10.2.0.4.0 00:00:18
OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:19
OLAP Catalog VALID 10.2.0.4.0 00:00:50
Oracle OLAP API VALID 10.2.0.4.0 00:00:45
Oracle interMedia VALID 10.2.0.4.0 00:03:48
Spatial VALID 10.2.0.4.0 00:01:29
Oracle Expression Filter VALID 10.2.0.4.0 00:00:09
Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:16
Oracle Rule Manager VALID 10.2.0.4.0 00:00:07
Total Upgrade Time: 00:25:52
DOC>#####################################
DOC>######################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>##########################################
DOC>######################################
DOC>#
SQL> spool off
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
When the upgrade script catupgrd.sql finishes shutdown the database and open the spool of the upgrade process and try to find out if any thing failed. If you see something failed try to fix it and re-run the upgrade process.
The upgrade process may leave many objects invalid in the database. Perform a normal startup and run the utlrp.sql script to recompile any invalid objects.
$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup
SQL> spool recompile.log
SQL> @utlrp.sql
TIMESTAMP
==============
COMP_TIMESTAMP UTLRP_BGN 2010-07-02 18:23:116
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count)
multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
=============
COMP_TIMESTAMP UTLRP_END 2010-07-02 18:23:50
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERROR
===============
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
SQL> spool off
Step 5: Post installation steps
sql>set lines 10000
sql>set pages 1000
sql>column comp_name format a40
sql>column version format a12
sql>column status format a6
sql>select comp_name, version, status from sys.dba_registry;
COMP_NAME VERSION STATUS
============ =========== =====
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
17 rows selected.
SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production
Check your database objects are valid or not
SQL> select object_name,status from dba_objects where status=’INVALID’;
No rows selected.
***********************************************************************************
***********************************************************************************
I was looking for the Oracle Online Training courses and your website really help me in finding my needs. This site contains all the stuff which i was looking . Thanks for this great work and i hope this will help a lots of users to achieve their goals
ReplyDeleteThis information which you provided is very much useful for us.It was very interesting and useful for software Courses online training.We also providing oracle online training institute in USA.
ReplyDelete