Oracle Point, Oracle Life.

To upgrade oracle database manually from 10g to 11g, we need to use Oracle-supplied pre- and post-upgrade scripts. There is a different set of upgrade
scripts you must use, depending on the release number of the database you’re
upgrading from. In this example, I’m upgrading from an Oracle Database 10g release
database to Oracle Database 11g and would need to use the following scripts in turn to perform the manual upgrade.

1. utlu111i.sql
2. catupgrd.sql
3. utilu111s.sql
4. catuppst.sql and
5. utlrp.sql

Following is a summary of the functions performed by each of the upgrade scripts:

■ utlu111i.sql This script, also known as the Pre-Upgrade Information Tool,
gathers information from the database and analyzes it to make sure that it
meets all the upgrade requirements, such as whether the database already
contains the SYSAUX tablespace or not. The Pre-Upgrade Information Tool
will issue warnings about potential upgrade issues such as database version
and compatibility, redo log size, initialization parameters, and tablespace
size estimates, and generates warnings if your database doesn’t satisfy the
requirements for upgrading to Oracle Database 11g.
■ catupgrd.sql This is the script that performs the actual upgrading of the
database to the Oracle Database 11g release and it now supports parallel
upgrades of the database.
■ utlu111s.sql This is the Upgrade Status Utility script which lets you
check the status of the upgrade—that is, whether the upgraded database’s
components have a valid status.
■ catuppst.sql This is the script you run to perform post-upgrade actions. This
is new in Oracle Database 11g Release 1.
■ utlrp.sql This script recompiles and revalidates any remaining application
objects.

By following Oracle Metalink document “Complete Checklist for Manual Upgrades to 11gR1 [ID 429825.1]”, the upgrade process would be smooth. Here, I would like to highlight couple points I experienced during my 11g upgrade.

Point 1: Either take a Cold or Hot backup of source 10g database (advisable to have cold backup).

Since 11g upgrade requires downtime of oracle database, I strongly suggest to take cold backup beforehand. The reason to do that is to minimize downtime as much as possible. If anything wrong happens during upgrade, we get the most straightforward way to restore source database.

Point 2: Running utlu111i.sql would be helpful to solve possible pre-requisite problem of upgrade.

Script utlu111i.sql does more than gathering information from database and analyzing database. Actually, it solves problems which could break the process of upgrade. For instance, the time zone files required in Oracle 11g has been updated to version 4. Failing doing that will cause break out of upgrade while we do upgrade with running catupgrd.sql. By running script utlu111i.sql, the new time zone file (version 4) will be recorded in view registry$database.

Point 3: With running utlu111i.sql, making necessary change on parameter file is required.

Running utlu111i.sql will present the report about warnings in potential upgrade issues such as dataabse version and compatibility, and so on. Before doing real upgrade, I followed the generated report to change parameter file as per as below.

**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
–> “background_dump_dest” replaced by  “diagnostic_dest”
–> “user_dump_dest” replaced by  “diagnostic_dest”
–> “core_dump_dest” replaced by  “diagnostic_dest”

Also, it’s critical that the parameter “compatible” need to be changed from 10.*.*.*.* to 11.1.0.0.0. Otherwise, upgrade process will raise problem and it’s very likely that control file of source database would be corrupt and need to be recovered if upgrade failed. That absolutely make DBA in timely trouble to recover source database.

Popularity: 5% [?]

Days ago, I experienced technical issue while I set up the oracle ODBC connection for one of our third-party application which is required to get oracle database connection. This application is windows-based ASP.NET application sitting on Windows Server 2003.

To prepare the database connection, I installed Oracle Client software and created new TNS name pointing to target database with no problem. After setting the database connection within application, I was surprised getting the following error message while I tested via internet explorer.

System.Exception: OCIEnvCreate failed with return code -1 but error message text was not available.

I know it is because of no permissions to access oracle client while the connection was trying to create environment.  My quick search shows that there is one possible way to solve it by changing configuration file of Microsoft .Net Framework. But, that’s not my preferred way because any direct changing on very low level of system file could raise unexpected problem. Another method is to adjust permissions manually and it works to my situation.

Step 1: Log on Windows as a user with Administrator privileges

Step 2: Launch Windows Explorer from and navigate to the ORACLE_HOME folder. In my case, it’s installation path of Oracle Client Software.

Step 3: Right-click on the ORACLE_HOME folder and choose the “Properties” option

Step 4: Click on the “Security” tab of the “Properties” window

Step 5: Click on “Authenticated Users” item in the “Name” list

Step 6: Uncheck the “Read and Execute” box in the “Permissions for Administrators” list under the “Allow” column and then re-check the “Read and Execute” box under the “Allow” column (this is
the box you just unchecked).

After doing that, the section “Permissions for Administrators” looks like below. In my case, above actions un-checked “Full Control” and “Modify” under the Allow” column.

Step 7: Click the “Advanced” button and in the “Permission Entries” list make
sure you see the “Authenticated Users” listed there with:
Permission = Read & Execute
Apply To = This folder, subfolders and files
If this is NOT the case, edit that line and make sure the “Apply onto”
drop-down box is set to “This folder, subfolders and files”.  This
should already be set properly but it is important that you verify this.

Step 8: Click “OK” button to apply the changes you made and then reboot your windows server.

Done!!!

Popularity: 2% [?]