Before
you upgrade make sure you have backup taken backup of the database/
1.
Run Pre-Upgrade Information Tool on 10.2.0.4 database
It
is under 11ghome/rdbms/admin/ utlu112i.sql
sqlplus
'/ as sysdba'
spool
pre_upg_11201_info.log
@/oracle/upgrade/utlu112i.sql
spool
off
Make
the changes as suggested by pre-upgrade toll.
Sample:
SQL>
@/oracle/upgrade/utlu112i.sql
Oracle
Database 11.2 Pre-Upgrade Information Tool
05-22-2013 19:23:20
**********************************************************************
Database:
**********************************************************************
-->
name: ORCLDB
-->
version: 10.2.0.4.0
-->
compatible: 10.2.0.4.0
-->
blocksize: 8192
-->
platform: Linux x86 64-bit
-->
timezone file: V4
**********************************************************************
Tablespaces:
[make adjustments in the current environment]
**********************************************************************
-->
SYSTEM tablespace is adequate for the upgrade.
....
minimum required size: 624 MB
....
AUTOEXTEND additional space required: 144 MB
-->
UNDOTBS1 tablespace is adequate for the upgrade.
....
minimum required size: 16 MB
-->
SYSAUX tablespace is adequate for the upgrade.
....
minimum required size: 547 MB
....
AUTOEXTEND additional space required: 97 MB
-->
TEMP tablespace is adequate for the upgrade.
....
minimum required size: 61 MB
**********************************************************************
Flashback:
OFF
**********************************************************************
**********************************************************************
Update
Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING:
--> "sga_target" needs to be increased to at least 672 MB
WARNING:
--> "java_pool_size" needs to be increased to at least 128 MB
**********************************************************************
Renamed
Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--
No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated
Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-->
background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
-->
user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
-->
core_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
**********************************************************************
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 Workspace Manager
[upgrade] VALID
-->
Oracle Text
[upgrade] VALID
-->
Oracle XML Database [upgrade] VALID
-->
Oracle Java Packages
[upgrade] VALID
-->
Oracle interMedia
[upgrade] VALID
-->
Expression Filter
[upgrade] VALID
-->
Rule Manager
[upgrade] VALID
**********************************************************************
Miscellaneous
Warnings
**********************************************************************
WARNING:
--> Database is using a timezone file older than version 11.
....
After the release migration, it is recommended that DBMS_DST package
....
be used to upgrade the 10.2.0.4.0 database timezone version
....
to the latest version which comes with the new release.
WARNING:
--> Database contains schemas with stale optimizer statistics.
....
Refer to the Upgrade Guide for instructions to update
....
schema statistics prior to upgrading the database.
....
Component Schemas with stale statistics:
.... SYS
.... CTXSYS
WARNING:
--> Database contains INVALID objects prior to upgrade.
....
The list of invalid SYS/SYSTEM objects was written to
....
registry$sys_inv_objs.
....
The list of non-SYS/SYSTEM objects was written to
....
registry$nonsys_inv_objs.
....
Use utluiobj.sql after the upgrade to identify any new invalid
....
objects due to the upgrade.
....
USER PUBLIC has 4 INVALID objects.
....
USER JEFFC has 3 INVALID objects.
....
USER SYS has 2 INVALID objects.
WARNING:
--> Database contains schemas with objects dependent on network
packages.
....
Refer to the Upgrade Guide for instructions to configure Network ACLs.
WARNING:-->
recycle bin in use.
....
Your recycle bin turned on.
....
It is REQUIRED
....
that the recycle bin is empty prior to upgrading
....
your database.
....
The command: PURGE DBA_RECYCLEBIN
....
must be executed immediately prior to executing your upgrade.
PL/SQL
procedure successfully completed.
SQL>
spool off;
2:
Make Init parameter changes and copy the init.ora file to 11g oracle home
Put
compatible = 10.2.0.4.0 to avoid the unnecessary errors.
3:
run utlrp.sql Twice to compile invalid objects
cd
$ORACLE_HOME/rdbms/admin/
SQL>
@utlrp.sql
4:
check timezone, it needs to be upgraded
SQL>
SELECT version FROM v$timezone_file;
VERSION
4
5:
Gather Dictionary stats
SQL>
EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL
procedure successfully completed.
6
Purge Recycle bin
SQL>
PURGE DBA_RECYCLEBIN;
DBA
Recyclebin purged.
7:
Put database in no-archive log mode
shut
immediate
startup
mount
alter
database noarchivelog;
8:
shut down database and listener
Shut
immediate
lsnrctl
stop LISTENER_ORCLDB
9:
prepare listener file or add this database entry in 11gr2 home
10:
set the env, we are using CSH
setenv
ORACLE_BASE /oracle
setenv
ORACLE_HOME /oracle/product/11.2.0/dbhome_1
setenv
PATH $ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH
setenv
LD_LIBRARY_PATH $ORACLE_HOME/lib
setenv
ORACLE_SID ORCLDB
setenv
ORACLE_BASE /oracle
11:
runupgrade
cd
$ORACLE_HOME/rdbms/admin
spool
/oracle/upgrade/upgrade_ORCLDB_11201.log
@catupgrd.sql
12:
post upgrade
Run
below scripts:
SQL>
@utlu112s.sql
SQL>
@catuppst.sql
SQL>
@utlrp.sql
Upgrade
is done
13.
Make sure to check the all components are valid and upgrade to 11.2.0.4 and
send screen shot
14:
Upgrading timezone
Make
sure the exiting timezone using below commands
SQL>
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
SQL>
SELECT version FROM v$timezone_file;
conn
/ as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
shutdown immediate;
startup upgrade;
set serveroutput on
SQL>
purge dba_recyclebin;
DBA
Recyclebin purged.
TRUNCATE
TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
SQL>
EXEC DBMS_DST.BEGIN_UPGRADE(11);
An
upgrade window has been successfully started.
PL/SQL
procedure successfully completed.
--
check if this select
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------
--------------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 1
DST_UPGRADE_STATE UPGRADE
--
you can check what tables need to updated using
SQL>
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where
UPGRADE_IN_PROGRESS='YES';
no
rows selected
--
restart the database
shutdown immediate
startup
shutdown immediate
startup
--Timezone
should be upgraded to V11 now, run the following command to verify
SQL>
select * from v$timezone_file;
FILENAME VERSION
--------------------
----------
timezlrg_11.dat 11
--
now upgrade the tables who need action
set
serveroutput on
VAR
numfail number
BEGIN
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 => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
BEGIN
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 => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
Failures:0
PL/SQL
procedure successfully completed.
--
If there are no failures then end the upgrade
VAR
fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
An
upgrade window has been successfully ended.
Failures:0
PL/SQL
procedure successfully completed.
15.
After update take Database backup
Refer
: Complete Checklist for Manual Upgrades to 11gR2
(Doc ID 837570.1)
