24
Upgrading to Discoverer Version 9.0.2

Upgrading to Discoverer Version 9.0.2

This chapter explains how to upgrade to Discoverer Version 9.0.2, and includes the following topics:

• "Upgrading to Discoverer Version 9.0.2"

• "About upgrading to Discoverer Version 9.0.2"

• "What are the prerequisites for upgrading to Discoverer Version 9.0.2?"

• "Upgrade step 1: Back up the Release 4.1 EUL"

• "Upgrade step 2: Upgrade the Release 4.1 EUL"

• "Upgrade step 3: Refresh summary folders"

• "Upgrade step 4: Migrate users to Discoverer Version 9.0.2"

• "Upgrade step 5: Remove the Release 4.1 EULs"

• "Upgrade step 6: Remove Discoverer Release 4.1 products from client machines"

• "Notes about upgrading EULs that contain analytic functions"

About upgrading to Discoverer Version 9.0.2

To use Discoverer Version 9.0.2, Discoverer users must have access to a Discoverer Version 5.1 End User Layer (EUL). If users have access to a Discoverer Release 4.1 EUL, that EUL must be upgraded before the users can access it using Discoverer Version 9.0.2.

The EUL upgrade process creates new Version 5.1 EUL tables, and then copies data from the Release 4.1 EUL tables into the new tables. Existing users can continue working with Discoverer Release 4.1 during the upgrade process because the upgrade process is non-destructive (i.e. the Release 4.1 EUL tables are not deleted).

For each EUL you want to upgrade, follow the upgrade steps below:

• "Upgrade step 1: Back up the Release 4.1 EUL"

• "Upgrade step 2: Upgrade the Release 4.1 EUL"

• "Upgrade step 3: Refresh summary folders"

When you are satisfied that all Release 4.1 EULs have been successfully upgraded, follow the upgrade steps below:

• "Upgrade step 4: Migrate users to Discoverer Version 9.0.2"

• "Upgrade step 5: Remove the Release 4.1 EULs"

When you have upgraded all users and removed all Release 4.1 EULs, follow the upgrade step below:

• "Upgrade step 6: Remove Discoverer Release 4.1 products from client machines"

What are the prerequisites for upgrading to Discoverer Version 9.0.2?

Before you can upgrade to Discoverer Version 9.0.2, Oracle9i Developer Suite Release 2 (including Discoverer Version 9.0.2) must already be installed.

In addition, to upgrade End User Layers, the EUL owners must have the following privileges:

• CREATE PROCEDURE

• CREATE SESSION

• CREATE TABLE

• CREATE VIEW

• CREATE SEQUENCE

If upgrading to an Oracle 8.1.6 (or above) database, summaries are implemented as materialized views and the EUL owners therefore require the following privileges:

• CREATE ANY MATERIALIZED VIEW

• DROP ANY MATERIALIZED VIEW

• ALTER ANY MATERIALIZED VIEW

• GLOBAL QUERY REWRITE

Important: To maintain the consistency of data transferred to the Version 5.1 EUL, it is very important that no Discoverer Administrator sessions are opened on the Release 4.1 EUL while the upgrade is in progress

Upgrade step 1: Back up the Release 4.1 EUL

Before you can upgrade an EUL to Discoverer Version 9.0.2, you must first back up the EUL tables by exporting the EUL owner using the standard database export utility.

How to export the EUL owner will depend on:

• the version of the Oracle client software installed on your machine

• the version of the Oracle database on which the Release 4.1 EUL resides

We recommend that the version of the Oracle database and the version of the Oracle database client software installed on your machine are the same. If the versions are not the same (e.g. if the EUL is on an Oracle8i database and Oracle9i client software is installed on your machine), you might not be able to follow the instructions below. If you are unable to export the EUL, contact your database administrator and ask them to export the EUL for you.

To back up a Release 4.1 EUL (assuming the EUL resides on an Oracle9i database and you are using a machine on which you have installed Oracle9iDS), follow the steps described in Chapter 3, "How to export an EUL using the standard database export utility" and Chapter 3, "How to import an EUL using the standard database import utility".

Having backed up the Release 4.1 EUL, you are now ready to upgrade it to Version 5.1.

Upgrade step 2: Upgrade the Release 4.1 EUL

Having backed up a Release 4.1 EUL, you can now upgrade the EUL to Version 5.1. The same database user that owned the Release 4.1 EUL will own the Version 5.1 EUL.

Note that Release 4.1 users are not affected by the upgrade and can continue using the Release 4.1 EUL.

Important: To maintain the consistency of data transferred to the Version 5.1 EUL, it is very important that no Discoverer Administrator sessions are opened on the Release 4.1 EUL while the upgrade is in progress.

To upgrade a Release 4.1 EUL to Version 5.1:

1. Choose Programs | Oracle Discoverer | Oracle9i Discoverer Administrator from the Windows Start menu to display the Connect dialog.

2. Enter the database user name and password of the owner of the Discoverer Release 4.1 EUL that you want to upgrade.

3. Click OK.

   The EUL Upgrade dialog is displayed.

   Figure 24-1 EUL Upgrade dialog

   
   
   Text description of the illustration eulup2.gif

Note: If you have not already exported the Release 4.1 EUL, click Cancel and export the EUL (for more information, see "Upgrade step 1: Back up the Release 4.1 EUL").

4. Select the Yes, I have completed a database export check box to confirm that you have exported the Discoverer Release 4.1 EUL.

5. Click Upgrade to create a new Version 5.1 EUL using data copied from the Release 4.1 EUL.

   The EUL Upgrade progress bar displays the status of the upgrade.

   If there are summary folders in the upgraded EUL, the following message dialog is displayed, indicating that the summary folders are invalid and must be refreshed.

   Figure 24-2 Invalid summaries warning message dialog

   
   
   Text description of the illustration suminv.gif
   6. If the above message dialog is displayed, click OK.

      The Load Wizard is displayed.

   7. Click Cancel to close the Load Wizard.

      The EUL upgrade is complete. You can use this EUL to connect to the database using Discoverer Administrator Version 9.0.2.

   Upgrade step 3: Refresh summary folders

   If there are summary folders in the EUL you have upgraded, you must refresh the summary folders.

   The definitions of any Release 4.1 summary folders are copied into the new Version 5.1 EUL and the status of any upgraded summary folders is changed to "Refresh Required". The database tables or materialized views on which the upgraded summary folders are based are not actually created until the summary folders are refreshed.

   When you refresh upgraded summary folders:

   • on Oracle 8.1.5 (or earlier) databases, Discoverer creates summary tables

   • on Oracle 8.1.6 (or later) databases, Discoverer creates materialized views

   When the summary tables or materialized views have been created, Discoverer can then use the summary folders to optimize queries.

   For more information about summary folders, summary tables, and materialized views, see the Chapter 14, "Managing summary folders".

   To refresh upgraded summary folders:

   1. Connect to Discoverer Administrator as the owner of the upgraded EUL.

   2. Open the business area containing the summary folders that you want to refresh.

   3. Display the Summaries tab of the Workarea.

   4. Select the summary folders that you want to refresh.

      You will have to refresh all of the summary folders before Discoverer can use them. However, depending on the amount of data, you might not want to refresh all the summary folders at the same time.

   5. Select Tools | Refresh Summaries.

      For any summary folders not based on external summary tables, the Refresh Summaries dialog is displayed.

      Figure 24-3 Refresh Summaries dialog

      
      
      Text description of the illustration sumfrefa.gif
   6. Specify refresh options as required.

      You can refresh summary folders immediately or specify a time for the refresh. Depending on the amount of data, you might want to schedule the refresh for an off-peak time.

   7. Click OK to refresh the summary folders.

   Notes

   If an upgraded EUL contains summary folders based on external summary tables, you must also refresh those summary folders. During the refresh, Discoverer updates EUL metadata for summary folders based on external summary tables. If you are using Oracle 8.1.6 (or later), Discoverer creates a materialized view that references the external summary table.

   Upgrade step 4: Migrate users to Discoverer Version 9.0.2

   When you have upgraded all Release 4.1 EULs and you are satisfied that the upgraded EULs are ready for use, migrate Discoverer users to Discoverer Version 9.0.2.

   While you are rolling out the migration across the organization, users can continue to work in either Release 4.1 or Version 9.0.2. However, note that any changes you make using one version of Discoverer Administrator will not be available to users of the other version of Discoverer.

   How to migrate Discoverer users to Discoverer Version 9.0.2 will depend on the Discoverer tools they use.

   To migrate Discoverer Plus and Discoverer Viewer users

   1. Install Oracle9iAS Discoverer Release 2 (version 9.0.2).

   2. Follow the instructions in the Oracle9iAS Migration Guide to upgrade the Discoverer components on the middle tier

   To migrate Discoverer Desktop users:

   1. Install Discoverer Desktop on client machines using the Discoverer Desktop CD supplied with Oracle9i Developer Suite.

      The Oracle Installer automatically installs Discoverer Desktop Version 9.0.2 in a separate directory from Discoverer Release 4.1

   Notes

   When a user logs into Discoverer for the first time, Discoverer searches for any Version 5.1 EULs that the user has access to, as follows:

   • If a valid EUL is found, the user can connect and can start work in Discoverer Version 9.0.2.

   • If no valid EUL is found, Discoverer alerts the user that they cannot connect to the database. At this point, the user must contact their Discoverer manager because it is likely that an EUL has not yet been upgraded. Note that the user can still use Discoverer Release 4.1 against an existing Release 4.1 EUL.

   After Discoverer Version 9.0.2 is installed and a connection to the database has been established successfully through a Version 5.1 EUL, you can remove:

   • Discoverer Release 4.1 EULs from the database

   • Discoverer Release 4.1 products (i.e. Discoverer Desktop Edition, Discoverer Administration Edition) from client machines

   Upgrade step 5: Remove the Release 4.1 EULs

   When you have upgraded all users to Discoverer Version 9.0.2 and they are successfully connecting to the database through upgraded Version 5.1 EULs, you can remove the Release 4.1 EULs.

   A SQL script called eul4del.sql is shipped with Discoverer that enables you to remove Release 4.1 EULs and associated tables (including summary tables/materialized views).

   Note the following:

   • When you run the eul4del.sql script, you are prompted for the password of the SYSTEM user. If you are not sure of the password of the SYSTEM user, contact your database administrator. Rather than give you the SYSTEM user's password, your database administrator might prefer to run the script for you.

   • The eul4del.sql script will not complete successfully if there are any summary folders owned by users other than the EUL owner. The script will list any such summary folders and their owners. The summary folder owners must delete their summary folders before you can remove the Release 4.1 EUL.

   To remove a Release 4.1 EUL:

   1. Choose ORACLE9i | Application Development | SQL*Plus from the Windows Start menu to display the SQL*Plus Log On dialog.

   2. Enter the database user name, password and database for the owner of the Discoverer Release 4.1 EUL that you want to remove.

   3. Type the following at the command prompt to start the Release 4.1 EUL deinstallation script:

      SQL>@<ORACLE_HOME>\discv902\sql\eul4del.sql
      
      

      The deinstallation script removes a specified Release 4.1 EUL and any associated database objects, including summary tables/materialized views.

      A summary of the deinstallation script is displayed:

      Removing summary refresh jobs ...
      Discoverer End User Layer Database Tables (4.x Production) 
      deinstallation
      This script will remove a version 4.x EUL and any associated database 
      objects.
      It will:
      1.  Ask you to enter the ORACLE SYSTEM password and connect string.
      2.  Ask you to enter the name and password of the 4.x EUL owner.
      3.  Confirm that you wish to drop the 4.x EUL.
      4.  Check for database jobs for users other than the 4.x EUL owner.
      5.  Confirm whether to drop 4.x tutorial tables (if any).
      6.  Log in as the 4.x EUL owner and remove any database jobs for it.
      7.  Remove all summary database objects for the 4.x EUL.
      8.  Remove all scheduled workbook database objects for the 4.x EUL.
      9.  Remove the 4.x EUL tables.
      10. Remove user and public synonyms (if any) for the 4.x EUL tables.
      
      

   4. When prompted, enter the password and database connect string for the SYSTEM user.

      If you are not sure of the password of the SYSTEM user, contact your database administrator.

   5. Enter the name and password of the owner of the Release 4.1 EUL.

      The following text is displayed:

      Preparing to remove EUL 4.x owned by <username> at <today's date>
      If you continue, the specified 4.x End User Layer will be PERMANENTLY 
      dropped. All End User Layer information and workbooks stored in the 
      database will be deleted. 
      Any 5.1 End User Layer tables will NOT be affected by this process.
      THIS PROCESS IS NON-REVERSIBLE.
      Do you wish to continue [N]: 
      
      

      Note that the EUL removal process is not reversible.

   6. Type Y to confirm that you want to drop the Release 4.1 EUL.

      If the script detects the Release 4.1 tutorial tables, you are prompted to confirm whether to delete the tables.

      During the process of dropping the Release 4.1 EUL, the script will check whether there are any summary folders owned by users other than the EUL owner.

      • If there are no summary folders owned by other users, then the Release 4.1 EUL is dropped. You will see a number of messages showing the various stages of the drop process, as follows:

        Removing summary refresh jobs ...
        Dropping internally managed summary data ...
        Removing scheduled workbook jobs ...
        Dropping scheduled workbook data ...
        Dropping 4.x End User Layer Tables  ...
        Removing public synonyms (if any) ...
        Connected.
        
        Finished removing 4.x End User Layer.
        
        

      • If there are summary folders owned by users other than the EUL owner, the script will halt and display the following text:

        The following users have managed summaries which must be dropped 
        before this EUL can be deinstalled:
        User: SCOTT, Summary: Scott's Summary
        User: FRED, Summary: Fred's Summary
        etc.
        Quitting - no changes made.
        
        

        If this message appears, the specified users must log into Discoverer Administration Edition Release 4.1 and remove the specified summary folders (for more information, see the Chapter 15, "How to delete summary folders"). Once these summary folders have been dropped, rerun the script eul4del.sql in order to drop the Release 4.1 EUL.

   Upgrade step 6: Remove Discoverer Release 4.1 products from client machines

   When you and your users have connected successfully to Discoverer Version 9.0.2 and you are confident that summary folders are working correctly, you can remove Discoverer Release 4.1 products (i.e. Discoverer Administration Edition, Discoverer Desktop Edition) from client machines.

   To remove Discoverer Release 4.1 products from client machines:

   1. Insert the CD from which you installed Discoverer Release 4.1 products into your computer.

      The Oracle Installer starts automatically. If the Installer does not start automatically, access the CD-ROM with Windows Explorer and run setup.exe from the CD root directory.

   2. Use the Oracle Installer dialogs to remove Discoverer Release 4.1 products.

      Note that any workbooks created in Discoverer Release 4.1 and saved in the <ORACLE_HOME>\discvr4 directory will not be deleted.

   3. (optional) To retain workbooks from the <ORACLE_HOME>\discvr4 directory for future use:
      1. Move any Discoverer Release 4.1 workbooks from the <ORACLE_HOME>\discvr4 directory to a new directory for use with Discoverer Version 9.0.2.

      2. Delete the \discvr4 folder.

   Notes about upgrading to Discoverer version 9.0.2

   Notes about upgrading Oracle Applications EULs

   When upgrading an Oracle Applications EUL, be aware that the MAXEXTENTS storage property of the EUL tables might have been increased to a value greater than the MAXEXTENTS storage property of the tablespace in which the EUL was created. If this situation exists, any attempt to upgrade the EUL will fail (e.g. with an ORA01631 error) because:

   • the upgrade process uses the original EUL tablespace's MAXEXTENTS value when creating the new EUL tables

   • the new EUL tables will therefore not be large enough to contain the data from the original EUL tables

   If the MAXEXTENTS value of the EUL tables is greater than the MAXEXTENTS value of the EUL's tablespace, ask your database administrator to increase the MAXEXTENTS value of the original EUL's tablespace before attempting to upgrade the EUL. The new EUL tablespace and the new EUL tables will be created with the larger MAXEXTENTS value.

   Notes about upgrading EULs that contain analytic functions

   When you upgrade an EUL to this version of Discoverer, new Oracle9i analytic functions are added to EUL tables.

   Where existing user-defined functions have the same name (or the same unique identifier) as the new functions, Discoverer does the following:

   • renames the existing user-defined function

   • displays a log message at the end of the upgrade process

   • gives you the option to save the messages to a log file

   Note: Discoverer's internal reference system ensures that Discoverer end users can still open workbooks that contain renamed user-defined functions.