Upgrading to Oracle Database 11g
Compatibility Matrix
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 1
The following database version will require an indirect upgrade path.
This post is about upgrading the Oracle database from 10g to 11g.
This post is based on metalink note ID 429825.1
Below steps are the one which are tried and successfully implemented on linux RHEL 4.
For Oracle database 10g, we need REHL 3, but for 11g version the minimum OS level for linux is RHEL 4.
I have given the list of patches for RHEL 3.0 and RHEL 4.0
PACKAGES
Red Hat Enterprise Linux 3.0 and Asianux 1.0:
make-3.79.1
gcc-3.2.3-34
glibc-2.3.2-95.20
compat-db-4.0.14-5
compat-gcc-7.3-2.96.128
compat-gcc-c++-7.3-2.96.128
compat-libstdc++-7.3-2.96.128
compat-libstdc++-devel-7.3-2.96.128
openmotif21-2.1.30-8
setarch-1.3-1
Red Hat Enterprise Linux 4.0 and Asianux 2.0:
binutils-2.15.92.0.2-13.EL4
compat-db-4.1.25-9
compat-libstdc++-296-2.96-132.7.2
control-center-2.8.0-12
gcc-3.4.3-22.1.EL4
gcc-c++-3.4.3-22.1.EL44
glibc-2.3.4-2.9
glibc-common-2.3.4-2.9
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-22.1
libstdc++-devel-3.4.3-22.1
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2
setarch-1.6-1
Packages is install.
linux@oracle]# rpm -q package_name
Once the OS is upto RHEL 4.0 level, you can start the upgrade process. Please follow the below steps for performing the upgrade.
Pre-Requisite:
You should have the Oracle database 10g, which you want to migerate.
OS level should be RHEL 4.0
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)
Step 1) Installing Oracle 11g Home
We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.
Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0
then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0
Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.
Start the installation using the below command
./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst.Loc
Screen 1 – Select Product Install
select “Oracle Database 11g”
Screen 2 – Select Installation Method
Choose “Advanced Installation”
Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.
Screen 4 – Select Installation Type
Choose “Enterprise Edition”
Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME
Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further
Checking for gcc-3.4.5; found gcc-3.4.6-3.1-i386. Passed
Checking for libaio-0.3.105; found libaio-0.3.105-2-i386. Passed
Checking for libaio-devel-0.3.105; found libaio-devel-0.3.105-2-i386. Passed
Checking for libstdc++-3.4.5; found libstdc++-3.4.6-3.1-i386. Passed
Checking for elfutils-libelf-devel-0.97; found elfutils-libelf-devel-0.97.1-3-i386. Passed
Checking for sysstat-5.0.5; found sysstat-5.0.5-11.rhel4-i386. Passed
Checking for libgcc-3.4.5; found libgcc-3.4.6-3.1-i386. Passed
Checking for libstdc++-devel-3.4.5; found libstdc++-devel-3.4.6-3.1-i386. Passed
Checking for unixODBC-2.2.11; Not found. Failed <<<<
Checking for unixODBC-devel-2.2.11; Not found. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<
Checking for rmem_default=4194304; rmem_default=262144. Failed <<<<
Checking for rmem_max=4194304; rmem_max=262144. Failed <<<<
Checking for wmem_default=262144; found wmem_default=262144. Passed
Checking for wmem_max=262144; found wmem_max=262144. Passed
Check complete. The overall result of this check is: Failed <<<<
Screen 7 – Upgrade an Existsing Database
Choose “No”
Screen 8 – Select Configuration Option
Choose “Install Software Only”
Screen 9 – Privileged system groups
Based on the group of oracle user, this value has to be set.
Screen 10 – Summary
Click on “Install”
At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.
Step 2) Pre-Upgrade Utility
In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.
[oracle@ocvmrh2178 admin]$ cd $ORACLE_HOME
[oracle@ocvmrh2178 db_1]$ cd rdbms/admin/
[oracle@ocvmrh2178 admin]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle@ocvmrh2178 admin]$ cp utlu111i.sql /tmp
Then login to the 10g oracle database and run the above sql you copied.
Oracle Database 11.1 Pre-Upgrade Information Tool 09-07-2007 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
.
.
.
.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.
The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.
Step 3) Executing the recommended steps
Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home
WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
Finding the Version of existing timezone files:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 2
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
———-
2
If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.
Check the database version
SQL> select banner 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
For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.
Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.
If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.
For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.
Please follow the metalink note ID 396387.1
Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle@ocvmrh2178 patch]$ unzip p5632264_10202_LINUX.zip
[oracle@ocvmrh2178 5632264]$ ls
etc files README.txt
[oracle@ocvmrh2178 5632264]$ cd files/oracore/zoneinfo
[oracle@ocvmrh2178 zoneinfo]$ ls
readme.txt timezlrg.dat timezone.dat
Backup $ORACLE_HOME/oracore/zoneinfo directory
[oracle@ocvmrh2178 zoneinfo]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup
Copy the .dat files
[oracle@ocvmrh2178 zoneinfo]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version again
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
———-
4
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. SYSMAN
Gather Dictionary stats:
Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);
PL/SQL procedure successfully completed.
Step 4) Run Pre-Upgrade Utility again
After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.
Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.
If every thing looks fine, Shut down the database from 10g Oracle Home
This time make sure you dont have the critical warnings like the one with TIMEZONE version.
Step 5) Starting Upgrade
Source the following variables for 11g Oracle Home
[oracle@ocvmrh2178 db_1]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle@ocvmrh2178 db_1]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ocvmrh2178 db_1]$ export ORACLE_SID=orcl
[oracle@ocvmrh2178 db_1]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance
SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
Check the dba_registry for the components and its status
Step 6) Post-Upgrade Steps
Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
Run utlu111s.sql to display the results of the upgrade:
SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 09-07-2007 05:22:40
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57
PL/SQL procedure successfully completed.
Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:
SQL> @?/rdbms/admin/catuppst.sql
Run utlrp.sql to recompile
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
1576
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
0
This completes the upgrade.
References:
Metalink Note ID: 429825.1
Metalink Note ID: 396671.1
Metalink Note ID: 396387.1
Compatibility Matrix
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 1
The following database version will require an indirect upgrade path.
This post is about upgrading the Oracle database from 10g to 11g.
This post is based on metalink note ID 429825.1
Below steps are the one which are tried and successfully implemented on linux RHEL 4.
For Oracle database 10g, we need REHL 3, but for 11g version the minimum OS level for linux is RHEL 4.
I have given the list of patches for RHEL 3.0 and RHEL 4.0
PACKAGES
Red Hat Enterprise Linux 3.0 and Asianux 1.0:
make-3.79.1
gcc-3.2.3-34
glibc-2.3.2-95.20
compat-db-4.0.14-5
compat-gcc-7.3-2.96.128
compat-gcc-c++-7.3-2.96.128
compat-libstdc++-7.3-2.96.128
compat-libstdc++-devel-7.3-2.96.128
openmotif21-2.1.30-8
setarch-1.3-1
Red Hat Enterprise Linux 4.0 and Asianux 2.0:
binutils-2.15.92.0.2-13.EL4
compat-db-4.1.25-9
compat-libstdc++-296-2.96-132.7.2
control-center-2.8.0-12
gcc-3.4.3-22.1.EL4
gcc-c++-3.4.3-22.1.EL44
glibc-2.3.4-2.9
glibc-common-2.3.4-2.9
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-22.1
libstdc++-devel-3.4.3-22.1
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2
setarch-1.6-1
Packages is install.
linux@oracle]# rpm -q package_name
Once the OS is upto RHEL 4.0 level, you can start the upgrade process. Please follow the below steps for performing the upgrade.
Pre-Requisite:
You should have the Oracle database 10g, which you want to migerate.
OS level should be RHEL 4.0
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)
Step 1) Installing Oracle 11g Home
We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.
Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0
then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0
Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.
Start the installation using the below command
./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst.Loc
Screen 1 – Select Product Install
select “Oracle Database 11g”
Screen 2 – Select Installation Method
Choose “Advanced Installation”
Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.
Screen 4 – Select Installation Type
Choose “Enterprise Edition”
Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME
Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further
Checking for gcc-3.4.5; found gcc-3.4.6-3.1-i386. Passed
Checking for libaio-0.3.105; found libaio-0.3.105-2-i386. Passed
Checking for libaio-devel-0.3.105; found libaio-devel-0.3.105-2-i386. Passed
Checking for libstdc++-3.4.5; found libstdc++-3.4.6-3.1-i386. Passed
Checking for elfutils-libelf-devel-0.97; found elfutils-libelf-devel-0.97.1-3-i386. Passed
Checking for sysstat-5.0.5; found sysstat-5.0.5-11.rhel4-i386. Passed
Checking for libgcc-3.4.5; found libgcc-3.4.6-3.1-i386. Passed
Checking for libstdc++-devel-3.4.5; found libstdc++-devel-3.4.6-3.1-i386. Passed
Checking for unixODBC-2.2.11; Not found. Failed <<<<
Checking for unixODBC-devel-2.2.11; Not found. Failed <<<<
Check complete. The overall result of this check is: Failed <<<<
Checking for rmem_default=4194304; rmem_default=262144. Failed <<<<
Checking for rmem_max=4194304; rmem_max=262144. Failed <<<<
Checking for wmem_default=262144; found wmem_default=262144. Passed
Checking for wmem_max=262144; found wmem_max=262144. Passed
Check complete. The overall result of this check is: Failed <<<<
Screen 7 – Upgrade an Existsing Database
Choose “No”
Screen 8 – Select Configuration Option
Choose “Install Software Only”
Screen 9 – Privileged system groups
Based on the group of oracle user, this value has to be set.
Screen 10 – Summary
Click on “Install”
At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.
Step 2) Pre-Upgrade Utility
In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.
[oracle@ocvmrh2178 admin]$ cd $ORACLE_HOME
[oracle@ocvmrh2178 db_1]$ cd rdbms/admin/
[oracle@ocvmrh2178 admin]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle@ocvmrh2178 admin]$ cp utlu111i.sql /tmp
Then login to the 10g oracle database and run the above sql you copied.
Oracle Database 11.1 Pre-Upgrade Information Tool 09-07-2007 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
.
.
.
.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.
The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.
Step 3) Executing the recommended steps
Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home
WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
Finding the Version of existing timezone files:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 2
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
———-
2
If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.
Check the database version
SQL> select banner 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
For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.
Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.
If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.
For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.
Please follow the metalink note ID 396387.1
Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle@ocvmrh2178 patch]$ unzip p5632264_10202_LINUX.zip
[oracle@ocvmrh2178 5632264]$ ls
etc files README.txt
[oracle@ocvmrh2178 5632264]$ cd files/oracore/zoneinfo
[oracle@ocvmrh2178 zoneinfo]$ ls
readme.txt timezlrg.dat timezone.dat
Backup $ORACLE_HOME/oracore/zoneinfo directory
[oracle@ocvmrh2178 zoneinfo]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup
Copy the .dat files
[oracle@ocvmrh2178 zoneinfo]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version again
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
———-
4
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. SYSMAN
Gather Dictionary stats:
Connect as sys user and gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);
PL/SQL procedure successfully completed.
Step 4) Run Pre-Upgrade Utility again
After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.
Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.
If every thing looks fine, Shut down the database from 10g Oracle Home
This time make sure you dont have the critical warnings like the one with TIMEZONE version.
Step 5) Starting Upgrade
Source the following variables for 11g Oracle Home
[oracle@ocvmrh2178 db_1]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle@ocvmrh2178 db_1]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@ocvmrh2178 db_1]$ export ORACLE_SID=orcl
[oracle@ocvmrh2178 db_1]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance
SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
Check the dba_registry for the components and its status
Step 6) Post-Upgrade Steps
Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
Run utlu111s.sql to display the results of the upgrade:
SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 09-07-2007 05:22:40
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57
PL/SQL procedure successfully completed.
Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:
SQL> @?/rdbms/admin/catuppst.sql
Run utlrp.sql to recompile
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
1576
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
0
This completes the upgrade.
References:
Metalink Note ID: 429825.1
Metalink Note ID: 396671.1
Metalink Note ID: 396387.1
No comments:
Post a Comment