This covers Upgrade of Oracle Database from version 10g (10.2.0.5.0) to 11g (11.2.0.1.0).
10g ORACLE_HOME: /u01/app/oracle/product/10.2.0/db_1
11G ORACLE_HOEM: /u01/app/oracle/product/11.2.0/dbhome_2
Preparing To Upgrade:
- Install Oracle Database 11g into a new (different) ORACLE_HOME (in this case, it's /u01/app/oracle/product/11.2.0/dbhome_2).
> When prompted for Upgrade an Existing Database, Select NO.
> On Select Configuration Option, select Install Software Only
> Refer to http://www.oracle-forums.com/installing ... -t632.html for the Oracle Database 11g Installation.
- Analyze your 10g database using Oracle Database 11.2 Pre-Upgrade Information Tool. This can be found in /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql. This must be run in the environment of the database being upgraded.
> [oracle@oracle10g]$ . oraenv
> [[oracle@oracle10g]$ sqlplus '/as sysdba'[oracle@oracle10g]$ . oraenv
ORACLE_SID = [ORCL10G2] ? ORCL10G2
SQL> spool upgrade_info.log[oracle@oracle10g]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.5.0 - Production on Sat Apr 21 00:43:58 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
> SQL> spool off;SQL> /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
SQL> @/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 04-21-2012 00:54:42
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL10G2
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 692 MB
.... AUTOEXTEND additional space required: 252 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 459 MB
.... AUTOEXTEND additional space required: 434 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 456 MB
.... AUTOEXTEND additional space required: 216 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
WARNING: --> "java_pool_size" needs to be increased to at least 64 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
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [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.5.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
.... SYSMAN
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 1 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.
.... USER ORACLE_OCM has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
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.
- Check the output of Pre-Upgrade Information Tool and fix any issues before proceeding.
Below are needed to be fixed:
1.)
> SQL> show parameter spfile;**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
WARNING: --> "java_pool_size" needs to be increased to at least 64 MB
> SQL> create pfile from spfile;
> [oracle@oracle10g]$ cd $ORACLE_HOME/dbs
> [oracle@oracle10g dbs]$ vi initORCL10G2.ora
New value is: *.sga_target=336M
New value is: ORCL10G2.__java_pool_size=67108864
2.)
Add the new parameter:**********************************************************************
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"
.
**********************************************************************
*.diagnostic_dest='/u02/'
Comment out or delete the below parameters:
#*.background_dump_dest='/u01/app/oracle/admin/ORCL10G2/bdump'
#*.core_dump_dest='/u01/app/oracle/admin/ORCL10G2/cdump'
#*.user_dump_dest='/u01/app/oracle/admin/ORCL10G2/udump'
- Copy the parameter file from 10g to 11g ORACLE_HOME: cp initORCL10G2.ora /u01/app/oracle/product/11.2.0/dbhome_2/dbs/
3.)
- Execute below statement: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
.... SYSMAN
> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN');
4.)
- Recompile invalid objects: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 1 INVALID objects.
.... USER SYS has 2 INVALID objects.
> SQL> @?/rdbms/admin/utlrp.sql
5.)
- To check if there are any objects depending upon network packages like UTL_TCP , UTL_SMTP etc.WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
.... USER ORACLE_OCM has dependent objects.
SELECT owner , name , type , referenced_name FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
- If there are any found then use this routine to create ACL's for these users.
6.)DECLARE
ACL_PATH VARCHAR2(4000);
CURSOR C1 IS
SELECT distinct owner FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
BEGIN
FOR R1 IN C1 LOOP
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,
r1.owner,'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,
r1.owner, TRUE, 'connect');
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('myACL.xml',
'ACL for network packages',
r1.owner,
TRUE,
'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('myACL.xml','host_name');
END;
COMMIT;
END LOOP;
END;
/
- Purge.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.
.
> SQL> PURGE DBA_RECYCLEBIN;
- Verify.
> SQL> select * from DBA_RECYCLEBIN;
- Check version and status of all database components
SQL>select comp_name,version, status from dba_registry;
- Change
*.compatible='10.2.0.5.0'
to
*.compatible='11.2.0.0.0'
Upgrade Database
- Shut down source database (10g)
[oracle@oracle10g ~]$ sqlplus '/as sysdba'
SQL> shutdown immediate;
Set your environment variables to Oracle Database 11g Release 2 (11.2)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=ORCL10G2
export PATH=$ORACLE_HOME/bin:$PATH
Start Upgrade
[oracle@oracle10g ~]$ sqlplus '/as sysdba'
SQL> startup upgrade
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql (This upgrade process shuts down database after executing catupgrd.sql)
- Monitor the alert log: /u02/diag/rdbms/orcl10g2/ORCL10G2/trace/alert_ORCL10G2.log