Steps to upgrade Oracle Database 10.2.0.1 to 10.2.0.5 :-
1. Download patchset p8202632_10205_Linux-x86-64.zip
2. Unzip patchset to respective directory .
3. Backup your database before Upgrade.
4. Prerequisites for applying patchset 10.2.0.5
A. Check Version of all registry components :-
SQL> Column comp_name format a40
Column version format a12
Column status format a6
Select comp_name, version, status from sys.dba_registry;SQL> SQL> SQL>
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
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
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.
B. Check version of database :-
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
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
C. Check INVALID objects in database :-
SQL> select object_name,status from dba_objects where status='INVALID';
no rows selected
if invalid objects exists then run below command :-
SQL> exec utl_recomp.recomp_serial ();
PL/SQL procedure successfully completed.
D. Check Version of TimeZone, Manage your data with timezone before upgrade :-
SQL> set lin 400
select version from v$timezone_file;
VERSION
----------
2
if current timezone version equals 2 then move forward with upgrade process
if current timezone version greater than 4 then check on metalink Note 553812.1.
if current timezone is less than 4 then PFB steps:-
Download utltzpv4.zip from metalink and run it on your database :-
oracle@localhost DBSOFT]$ unzip utltzpv4.zip
Archive: utltzpv4.zip
inflating: utltzpv4.sql
[oracle@localhost DBSOFT]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 10:53:14 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
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!
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 in the patchset.
.
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.
SQL> column table_owner format a4
column column_name format a18
select * from sys_tzuv2_temptab;SQL> SQL>
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 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
7 rows selected.
If the output of above query returns "NO ROWS" then move forward with the upgrade.
If output contains column names containing TZ data, which will be affected by the upgrade then see metalink Note 553812.1.
If output contains scheduler objects owned by SYS then we can ignore those, but if it contains user owned objects then take a backup and restore them after the upgrade.
4. Start the Upgrade :-
A. Stop database , listener, EM console
[oracle@OWB ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 11 14:47:49 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@OWB ~]$ lsnrctl stop LISTENER
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-OCT-2016 14:48:48
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.135)(PORT=1521)))
The command completed successfully
[oracle@OWB ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
iSQL*Plus instance on port 5560 is not running ...
B. unzip the p8202632_10205_Linux-x86-64.zip file , and run start applying patch
[oracle@OWB Disk1]$ ./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Passed
All installer requirements met.
Kernal parameters for 10.2.0.1 and 10.2.0.5 are different , Kindly change that.
Now Oracle Home is patched with 10.2.0.5 patch , Now upgrade your database :-
C. Start the database in UPGRADE mode :-
[oracle@OWB Disk1]$ cd $ORACLE_HOME
[oracle@OWB dbhome_1]$ cd rdbms/admin
[oracle@OWB admin]$ pwd
/u01/app/oracle/product/10.2.0/dbhome_1/rdbms/admin
[oracle@OWB admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 11 15:04:44 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2097624 bytes
Variable Size 159387176 bytes
Database Buffers 369098752 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL>
d . Run the preupgrade information tool :-
SQL> spool ugrade.log
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 10-11-2016 15:05:38
.
**********************************************************************
Database:
**********************************************************************
--> name: ITDQF
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 486 MB
.... AUTOEXTEND additional space required: 6 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: 252 MB
.... AUTOEXTEND additional space required: 12 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> 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
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
PL/SQL procedure successfully completed.
SQL> spool off
Reflect the changes mentioned by above utility and start the Upgrade process
SQL> spool final_upgrade.log
SQL> @catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC> size 70M reuse
DOC> extent management local
DOC> segment space management auto
DOC> online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Session altered.
Table created.
.
.
.
.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2016-10-11 15:24:16
.
Oracle Database 10.2 Upgrade Status Utility 10-11-2016 15:24:16
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.5.0 00:05:07
JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:00:56
Oracle XDK VALID 10.2.0.5.0 00:00:15
Oracle Database Java Packages VALID 10.2.0.5.0 00:00:07
Oracle Text VALID 10.2.0.5.0 00:00:16
Oracle XML Database VALID 10.2.0.5.0 00:01:11
Oracle Workspace Manager VALID 10.2.0.5.0 00:00:23
Oracle Data Mining VALID 10.2.0.5.0 00:00:09
OLAP Analytic Workspace VALID 10.2.0.5.0 00:00:12
OLAP Catalog VALID 10.2.0.5.0 00:00:28
Oracle OLAP API VALID 10.2.0.5.0 00:00:21
Oracle interMedia VALID 10.2.0.5.0 00:01:43
Spatial VALID 10.2.0.5.0 00:01:15
Oracle Expression Filter VALID 10.2.0.5.0 00:00:04
Oracle Enterprise Manager VALID 10.2.0.5.0 00:00:38
Oracle Rule Manager VALID 10.2.0.5.0 00:00:08
.
Total Upgrade Time: 00:14:26
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>
Upgrade completed.
5. Post Upgrade Steps :-
a. Shut Down your database and restart it and run utlrp.sql
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2097624 bytes
Variable Size 218107432 bytes
Database Buffers 310378496 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL>
SQL> @/u01/app/oracle/product/10.2.0/dbhome_1/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-10-11 15:26:17
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>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-10-11 15:26:39
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 ERRORS
-------------------
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
SQL>
B. Check version of database and components :-
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL>
SQL> Column comp_name format a40
Column version format a12
Column status format a6
Select comp_name, version, status from sys.dba_registry;SQL> SQL> SQL>
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Packages and Types 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
JServer JAVA Virtual Machine 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle Data Mining 10.2.0.5.0 VALID
Oracle Text 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle interMedia 10.2.0.5.0 VALID
OLAP Analytic Workspace 10.2.0.5.0 VALID
Oracle OLAP API 10.2.0.5.0 VALID
OLAP Catalog 10.2.0.5.0 VALID
Spatial 10.2.0.5.0 VALID
Oracle Enterprise Manager 10.2.0.5.0 VALID
SQL>
Database is Successfully Upgraded from 10.2.0.1 to 10.2.0.5.
SQL> Column comp_name format a40
Column version format a12
Column status format a6
Select comp_name, version, status from sys.dba_registry;SQL> SQL> SQL>
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
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
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.
|
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
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
|
SQL> select object_name,status from dba_objects where status='INVALID';
no rows selected
if invalid objects exists then run below command :-
SQL> exec utl_recomp.recomp_serial ();
PL/SQL procedure successfully completed.
|
SQL> set lin 400
select version from v$timezone_file;
VERSION
----------
2
|
oracle@localhost DBSOFT]$ unzip utltzpv4.zip
Archive: utltzpv4.zip
inflating: utltzpv4.sql
[oracle@localhost DBSOFT]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 27 10:53:14 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
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!
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 in the patchset.
.
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.
SQL> column table_owner format a4
column column_name format a18
select * from sys_tzuv2_temptab;SQL> SQL>
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 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
7 rows selected.
|
[oracle@OWB ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 11 14:47:49 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@OWB ~]$ lsnrctl stop LISTENER
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-OCT-2016 14:48:48
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.135)(PORT=1521)))
The command completed successfully
[oracle@OWB ~]$ isqlplusctl stop
iSQL*Plus 10.2.0.1.0
Copyright (c) 2003, 2005, Oracle. All rights reserved.
iSQL*Plus instance on port 5560 is not running ...
|
[oracle@OWB Disk1]$ cd $ORACLE_HOME
[oracle@OWB dbhome_1]$ cd rdbms/admin
[oracle@OWB admin]$ pwd
/u01/app/oracle/product/10.2.0/dbhome_1/rdbms/admin
[oracle@OWB admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 11 15:04:44 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2097624 bytes
Variable Size 159387176 bytes
Database Buffers 369098752 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL>
|
SQL> spool ugrade.log
SQL> @utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 10-11-2016 15:05:38
.
**********************************************************************
Database:
**********************************************************************
--> name: ITDQF
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 486 MB
.... AUTOEXTEND additional space required: 6 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: 252 MB
.... AUTOEXTEND additional space required: 12 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 58 MB
.... AUTOEXTEND additional space required: 38 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> 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
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Rule Manager [upgrade] VALID
.
PL/SQL procedure successfully completed.
SQL> spool off
|
SQL> spool final_upgrade.log
SQL> @catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC> size 70M reuse
DOC> extent management local
DOC> segment space management auto
DOC> online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Session altered.
Table created.
.
.
.
.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UPGRD_END 2016-10-11 15:24:16
.
Oracle Database 10.2 Upgrade Status Utility 10-11-2016 15:24:16
.
Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.5.0 00:05:07
JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:00:56
Oracle XDK VALID 10.2.0.5.0 00:00:15
Oracle Database Java Packages VALID 10.2.0.5.0 00:00:07
Oracle Text VALID 10.2.0.5.0 00:00:16
Oracle XML Database VALID 10.2.0.5.0 00:01:11
Oracle Workspace Manager VALID 10.2.0.5.0 00:00:23
Oracle Data Mining VALID 10.2.0.5.0 00:00:09
OLAP Analytic Workspace VALID 10.2.0.5.0 00:00:12
OLAP Catalog VALID 10.2.0.5.0 00:00:28
Oracle OLAP API VALID 10.2.0.5.0 00:00:21
Oracle interMedia VALID 10.2.0.5.0 00:01:43
Spatial VALID 10.2.0.5.0 00:01:15
Oracle Expression Filter VALID 10.2.0.5.0 00:00:04
Oracle Enterprise Manager VALID 10.2.0.5.0 00:00:38
Oracle Rule Manager VALID 10.2.0.5.0 00:00:08
.
Total Upgrade Time: 00:14:26
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>
|
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2097624 bytes
Variable Size 218107432 bytes
Database Buffers 310378496 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL>
SQL> @/u01/app/oracle/product/10.2.0/dbhome_1/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2016-10-11 15:26:17
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>#
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2016-10-11 15:26:39
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 ERRORS
-------------------
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
SQL>
|
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL>
SQL> Column comp_name format a40
Column version format a12
Column status format a6
Select comp_name, version, status from sys.dba_registry;SQL> SQL> SQL>
COMP_NAME VERSION STATUS
---------------------------------------- ------------ ------
Oracle Database Catalog Views 10.2.0.5.0 VALID
Oracle Database Packages and Types 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
JServer JAVA Virtual Machine 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle Data Mining 10.2.0.5.0 VALID
Oracle Text 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle interMedia 10.2.0.5.0 VALID
OLAP Analytic Workspace 10.2.0.5.0 VALID
Oracle OLAP API 10.2.0.5.0 VALID
OLAP Catalog 10.2.0.5.0 VALID
Spatial 10.2.0.5.0 VALID
Oracle Enterprise Manager 10.2.0.5.0 VALID
SQL>
|
No comments:
Post a Comment