Upgarading an Oracle 9i database (running for a Oracle Developer 2000 based application) to 10g or 11g

Posted on 2013-05-25
Last Modified: 2014-01-12
Hi experts,
I have an application running on Developer 2000 (Oracle Developer Server release 6.0) running on a Oracle 9i database. Following are more detailed version specifications about the forms and reports components:
Form Builder [32 bit]   :  Forms [32 bit]
Report Builder [32 bit] : Report Builder

We are planning to upgrade the 9i database to 10g or 11g

Please advise. Has anyone undertaken a similar task. What were the steps taken to have the application running succesfully with the upgraded database?

Following is a reference of a question on the same issue:

  • 2
  • 2
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39197553

Author Comment

by:Mohammad Mazharuddin Ehsan
ID: 39197700
Hi Alex140181,
All the links that you have provided are about just upgrading a database instance. However, my question is about upgrading an Oracle 9i database instance and also ensure that the Oracle Developer 6.0 application running on it is not affected (without upgrading the application)
LVL 10

Accepted Solution

peterside7 earned 500 total points
ID: 39716687

I upgraded our Oracle Designer repository from 9i to 10g.
The first step to the upgrade is downloading and extracting the patch. The download is on Oracle Tech Net (OTN, under the 10g Developer download section. Apply the patch to your client side install of designer 9i. If you don't have designer 9i already installed, you should download and install that first.

For the upgrade, use the same home where your previous installation of designer resides. I tried a couple of tests and with will let you install the patch without having Developer 9i installed (which is useless) and it will let you install to a different home which results in a non-functioning install. 9i will still work but you get errors when running the 10g tools.

The client side upgrade is pretty standard using Universal Installer. If you installed Developer previously, it will be nothing new. Except for making sure you select the correct home (I have 5 or 6 on my workstation), you should probably just accept the defaults and continue to completion.

The server side upgrade is performed from inside the Repository Admin utility (RAU). Unlike a Repository install, you don't need the sys password to upgrade. Everything is done using the repository manager account. Using RAU 10g, connect to the repository to be upgraded as the repository manager and select the upgrade button. You'll get the opportunity to optionally change some tablespaces (at least I did). I'm running with the Oracle recommended tablespaces so I chose to accept the defaults and continued on. The upgrade ran for about 30 minutes on my R&D server (that's a P4 with 1gig ram and ata hard drive running Linux Fedora 3).

If you're doing this in production, please take a backup first. I had no issues but you can never tell when the hardware might burp or when you will get a conflict on your system.

This is how upgrading should be. Simple, flawless, I give it two thumbs up. This is obviously a case where the upgrade process was engineered into the application. All applications should consider these kinds of things in the design phase and not as an after thought. Designer is a well thought-out, mature application. It also helps that the installer is such a mature product also. Does anyone remember the crappy installer back in the CDE days? Blech.

To verify your install, just select the about box. The version should be

Upgrading a Release 6i or higher Repository
To find out whether your repository can be upgraded with this release:

   1. Start the Repository Administration Utility and connect to the repository.
   2. Check that the Upgrade button is enabled. For repositories earlier than 6i, refer to "Migrating Data from an Existing pre-6i Repository to a New One".

If upgrading from the original Production release (Oracle Designer 6i or Oracle Repository 6i Release 1) only, the order in which you perform the upgrade tasks must be amended. This is necessary because the original Production release is not certified for use with Oracle9i or Oracle 8i version 8.1.7, so the required database upgrade must be performed after the repository upgrade.

To upgrade from the Production release, proceed as follows:

    * Perform steps 1 through 5 of the process shown below
    * Perform steps 7 (optional) and 8 through 18
    * Perform step 6

If upgrading from any other Oracle Designer or Oracle Repository release, perform the steps in sequence from 1 through 18.

To upgrade an Oracle Designer repository installed on an Oracle 9i database, the Oracle 9i import/export utilities are required. For details, refer to Step 5b - (Oracle 9i only) Install Oracle 9i Import and Export Utilities.

Step 1 - Log On to the Machine That Hosts the Repository
Log on to the machine that hosts the Oracle database where the repository resides.
Step 2 - Check the Settings in the Oracle Initialization Parameter File
See "Ensure Correct Settings for Database Initialization Parameters".
Step 3 - Start or Restart the Oracle Database If Necessary
See "Start or Restart the Oracle Database If Necessary".
Step 4 - Ensure That TNS Listener Is Started
See "Ensure That TNS Listener Is Started".
Step 5 - Back Up the Repository Owner's Account
Back up (export) the whole repository. In the Repository Administration Utility, click the Export button (Export) button and save the backup as a dump file. See the online help for Repository Management for instructions on how to do this.
Step 6 - Upgrade the Server Database If Necessary
The server database should be Oracle8i release 8.1.7 or higher; see the appropriate Oracle server documentation for upgrade instructions.
Step 7 - Back Up the Server Database (Optional)
See your Oracle database documentation for instructions on backing up the database. This step is necessary only if your database contains important data other than that in the repository, as you can restore the repository from the backup you created in step 5.
Step 8 - Establish Undo Tablespace or Create Rollback Segment for the Upgrade
Oracle9i Databases Using Automatic Undo
If a database is operating in automatic undo mode (UNDO_MANAGEMENT initialization parameter is set to AUTO), undo operations use a dedicated undo tablespace instead of a rollback segment. In many cases, an undo tablespace is created by default (e.g. with the name UNDOTBS) when the database is created. If so, use that tablespace.

If no undo tablespace exists, proceed as follows:

   1. In SQL*Plus, enter:

      create undo tablespace tbs_name datafile ?filespec? size 10m;

   2. Set the UNDO_TABLESPACE parameter in the database initialization file (SPFILE or INIT.ORA as appropriate) to point to the new tablespace.

Oracle8i Databases, and Oracle9i Databases Using Manual Undo
You do this by creating a rollback segment in the same way as for a first installation. See Establish an Undo Tablespace or a Rollback Segment for the Installation".
Step 9 - Ensure That Temporary Tablespace Is Allocated for the Repository Owner
You should ensure that a suitable tablespace is allocated as the temporary tablespace for the repository owner. This tablespace is used for performing sort operations.

On some servers you can use a predefined tablespace called TEMPORARY_TABLES or TEMPORARY_DATA as the repository owner?s temporary tablespace; on others, you might need to create a tablespace for this purpose.

To see the temporary tablespace for each user, open a SQL*Plus window (if one is not already open), connect as SYSTEM (or as a user with DBA privileges) and enter the following statement:

    select username, temporary_tablespace from dba_users;

If you want to change the temporary tablespace for the repository owner, enter a statement of the following form:

    alter user repos_owner temporary tablespace temp_tbs_name;

where repos_owner is the username of the repository owner and temp_tbs_name is the name of the temporary tablespace.

Make a note of the tablespace name you will be using. When you have finished, be sure to close the SQL*Plus window.
Step 10 - Log On to Windows at the Client
See "Log On to Windows".
Step 11 - Update the client software
If you have not done so already, update the client software for Oracle Designer or Oracle Designer Repository as appropriate. Make sure that it uses a different Oracle home directory from that used by other Oracle products on the client. However, if other Oracle iDS products are installed, use that Oracle home. For full instructions see the Oracle Developer Suite Installation Guide Release 2.
Step 12 - Drop and recreate the repository owner role and grant system privileges
At the client workstation, start SQL*Plus from Programs > Oracle - iDS_client_home > Application Development > SQL Plus and connect as SYS (using the AS SYSDBA clause for an Oracle database). Drop the role CK_ORACLE_REPOS_OWNER, then recreate the role and grant it to the repository owner. Exit from SQL*Plus.

For more information see "Create and Grant the Role CK_ORACLE_REPOS_OWNER for System Privileges".
Step 13 - Start the Repository Administration Utility
See "Start the Repository Administration Utility". Be sure to connect as the repository owner.
Step 14 - Open the Upgrade Utility
Click the Upgrade button (Upgrade) button on the Repository Administration Utility.
Step 15 - Assign a Rollback Segment
From the Nominate a Rollback Segment dropdown list on the display, select a rollback segment. If you created one in step 8 use that one, otherwise choose one other than SYSTEM.
Step 16 - Start the Upgrade
The upgrade consists of a number of stages. For each stage, a log file is written to the log directory (see Appendix B, "Log Files"). These log files are named stage.LIS, where stage identifies a particular stage. You can examine or monitor the log files while the upgrade is in progress, but do not delete them.

To start the upgrade:

   1. On the Upgrade a Repository Instance dialog box, click the Start button.

   2.   Reply Yes to the message "Do you wish to proceed?".

If you see the message:

    Insufficient database grant privileges to perform operation.

Click the Check Requirements button, proceed as described in "Check Privileges, Tablespaces and Parameters", and then try the upgrade again.

From this point, the upgrade process continues automatically, and normally needs no intervention.

If you need to interrupt the process at any point after the Control Status dialog box is displayed, click the Pause button (Pause) button.

When you see the message "Operation Complete", click OK followed by Cancel to return to the Repository Administration Utility.

If the upgrade appears to complete successfully, examine the log files referred to above to ensure that Oracle errors and warnings were not issued.

If the upgrade fails, examine the log files referred to above, then go to the "Troubleshooting" section.

Tip: You might find it useful to view the log directory files in date order so that you can see which ones were created most recently.
Step 17 - Check for Invalid, Missing or Disabled Repository Objects
It is a good idea, once the upgrade has completed, to check that there are no invalid, missing or disabled repository objects.

To perform this check, do the following in the Repository Administration Utility:

   1. Click View Objects button(View Objects) to open the Object Status Utility.

   2.   Check for objects with a status of Invalid, Disabled or Missing.

For more information, see the Repository Management help topic about checking the status of repository objects.
Step 18 - Test the Repository
We recommend that you review and test the repository using the repository tools before you allow other users to access the repository.
Using NLS Features
If you have a National Language Support (NLS) version of the repository and you want to change the display language for certain types of text, continue from "Setting Up the Repository for NLS Operation".
Troubleshooting an Installation, Migration or Upgrade
If an installation, migration or upgrade fails for any reason, a message is displayed with an indication of the cause.

Keep the Repository Administration Utility active on the screen if possible, as you might be able to correct the problem and continue. Proceed as follows:

   1. If a "ckrau61" error dialog is displayed:

         1. Leave the dialog displayed if possible.
         2. Take the corrective action displayed in the dialog. If you use SQL*Plus to make the correction, be sure to exit from it before proceeding.
         3. Click OK to dismiss the dialog.
         4. Click the Retry button (Retry) button in the Control Status dialog. The operation should now proceed.

              If the displayed message refers to a log file, examine the log file for error messages. Log files are written to the log directory (see Appendix B, "Log Files").

   3.   Take any corrective action appropriate to the error message(s) in the log file (see also Appendix C, "Error Messages").



      If you can take the corrective action while keeping the Repository Administration Utility still active, do so and continue from step 3.

      If the corrective action is such that you need to close down the Repository Administration Utility, click the Abort button on the message box. Take the corrective action, restart the Repository Administration Utility and restart the installation or upgrade.
   4. If the message box offers the Retry option, click this button after you have completed the corrective action. Clicking Retry causes the system to reattempt the stage of the installation or upgrade that failed, and then continue.


Do not use the Continue option, as this attempts to continue the installation or migration without allowing you to take corrective action.
The remainder of this section gives details of specific cases:

    * Use Unlimited Extents for Repository Tablespaces
    * Installation Fails at Stage CKGLBWRK
    * 'Role CKR_username does not exist' Error on Reinstall After Dropping Repository Owner

Use Unlimited Extents for Repository Tablespaces
We recommend that the tablespaces created for all repository tables have unlimited extents specified when they are created. This is especially essential for the tablespace nominated as the one to hold temporary repository indexes and tables (at install time), since these tables inherit their sizing from the tablespace definition.

If you have specified a maxextent value (e.g. 121) for this tablespace you may occasionally get errors of the form:

    ORA-03235 max # extents (string) reached in table string.string subpartition string
    ORA-03236 max # extents (string) reached in index string.string subpartition string

If you see these errors, inform your repository administrator or DBA who will need to alter the tablespace, table and index sizing characteristics as follows.

For tablespaces, use the following command:

    alter tablespace tablespace_name default storage ( maxextents unlimited );

Refer to the Administrator's Guide for your database for further information about altering storage characteristics on tablespaces.

For tables and indexes, start the Repository Administration Utility and click the View Objects button. In the Object Status Utility window, select Tables or Indexes, select the name of the table or index, click the right mouse button, choose Quick Edit, choose the Unlimited option button and click OK.
Installation Fails at Stage CKGLBWRK
If a repository installation fails at stage CKGLBWRK with the following message:

    RME-02124: Failed to execute SQL statement:... JR_WORKAREA

one possible reason is that the SQL statement to grant the select privilege to the repository owner on SYS.V$PARAMETER has been omitted. This will result in an invalid JR_WORKAREA package and a failure to create the global shared workarea and the system folder.

To recover from this situation:

   1. Start SQL*Plus, connect to the database as SYS (using the AS SYSDBA clause for an Oracle9i database) and enter:

          grant select on sys.v_$parameter to repos_owner;

   2. Connect as the repository owner and enter:

          truncate table RM$REPOSITORIES;

   3. Enter:

          update CK_INSTALLED_OBJECTS set CI_NAME = 'CKVERS' where
          CI_STAGE = 'ABORT';

   4. Exit from SQL*Plus.

   5.   Restart the installation from the point of failure.

'Role CKR_username does not exist' Error on Reinstall After Dropping Repository Owner
If the repository owner database user account is dropped manually for any reason, the repository owner role CKR_username is transferred to the SYS user account. As a result, subsequent reinstallation of the repository fails with the message "Role CKR_username does not exist" on reconciling the subordinate users.

To prevent this problem, do the following before reinstalling the repository:

   1. Start SQL*Plus, connect to the database as SYS (using the AS SYSDBA clause for an Oracle9i database) and enter:

          drop role ckr_username;

      where username is the username of the repository owner.
   2.   Exit from SQL*Plus.

   3.   Reinstall the repository.

   4.   Reconcile the subordinate users (in the Repository Administration Utility, click the Recreate button, choose Full Reconcile and click Start).

If the problem has already occurred, do the following:

   1. Close the Repository Administration Utility and any other Oracle Designer Repository tools that are open.

   2.   Start SQL*Plus, connect to the database as SYS (using the AS SYSDBA clause for an Oracle9i database) and enter:

          drop role ckr_username;

      where username is the username of the repository owner.
   3.   Exit from SQL*Plus.

   4.   Open the Repository Administration Utility and connect as the repository owner.

   5.   Reconcile the subordinate users (click the Recreate button, choose Full Reconcile and click Start).

Deinstalling a Repository
The procedure for deinstalling a repository depends on whether the repository was created by means of the SQL scripts or by the manual method (see "Installing a New Repository").
Repository Created by SQL Script Method
To deinstall a repository that was created by the SQL scripts:

   1. At the server, ensure that the parameters are set correctly in the CKPARAMS.TXT file.

   2.   Make sure that no other users are connected to the repository, and that the repository owner is not connected to the database.

   3.   Open a Command Prompt window and set the current directory to the one where the scripts are stored:

      cd d:\Oracle_home\repadm61\admin

      where Oracle_home is the Oracle home directory where the client tools were installed.
   4.   From the Command Prompt window, start SQL*Plus:


   5. At the "Enter username:" prompt, enter the full connect string in the form:

      sys/psw@host [as sysdba]

      The AS SYSDBA clause is mandatory for an Oracle9i database.
   6.   At the SQL> prompt, enter:

      @ ckclean

   7. When prompted, enter the password for SYS to start execution of the script.

   8.   Note the names of the datafiles that need to be manually removed as indicated in the messages.

   9.   At the message "Cleanup operation complete", exit from SQL*Plus.

  10.   Delete the datafiles indicated in the messages.

Repository Created by Manual Method
To deinstall a repository that was created manually:

   1. In the Repository Administration Utility, click the Remove Repository button (Remove Repository) button.

   2.   Answer Yes to the confirmation message.

   3.   When you see the message "Operation Complete", click OK.

   4.   Start SQL*Plus.

   5.   Connect to the database as SYS (for an Oracle9i database, use the AS SYSDBA clause, for example SYS/psw@alias AS SYSDBA).

   6.   Enter the following command:

      drop user repos_owner cascade;

   7. For each repository tablespace, enter the following:

      drop tablespace tbs [including contents and datafiles];

      where the portion in square brackets [] is for an Oracle9i database only.
   8.   For an Oracle8i database, manually delete the datafiles for the repository tablespaces.
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39716716
@peterside7: Whoa :-) Nice work dude! You should really post an article out of this one! Thumbs up!
LVL 10

Expert Comment

ID: 39717111
Thanks ... very appreciated!

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now