4
Oracle WebDB Migration

This chapter contains the following sections:

• Section 4.1, "Migrating from Oracle WebDB 2.1 to 2.2"

• Section 4.2, "Migrating WebDB 2.1 Sites to Version 2.2"

• Section 4.3, "Dropping a WebDB Site"

• Section 4.4, "Dropping Users Who Create Sites"

4.1 Migrating from Oracle WebDB 2.1 to 2.2

Oracle WebDB provides upgrade scripts that allow you to migrate from Oracle WebDB version 2.1 to Oracle WebDB 2.2.

IMPORTANT: Before performing a WebDB migration, ensure that you have your ORACLE_HOME environment variable configured to point to your Oracle database.

4.1.1 Upgrading Oracle Web Application (OWA) from Version 4.0.7 to 4.0.8

Before performing a WebDB or site migration, you must upgrade your Oracle Web Application (OWA) from version 4.0.7 to 4.0.8.

Complete the following steps to do this:

1. Insert your product Oracle WebDB 2.2 CD and navigate to the 2.2.0.0.5\owa40 directory.

2. Start SQL*Plus and log on as the SYS user with the appropriate password.

3. Execute owaload.sql using the following syntax:

   <userid> <tablespace> <temp_tablespace> <log_file> <userid_password> 
   
   

   where

   Parameter	Description
   user id	The user logging onto OWA. Type oas_public.
   tablespace	The name of the tablespace containing OWA.
   temp_tablespace	The name of the temporary tablespace.
   log_file	The full path for the location of the OWA log file.
   userid_password	The password to log on as the oas_public user.

Once owaload.sql is run as directed, OWA is upgraded to version 4.0.8.

4.1.2 Running the WebDB Upgrade Script from Windows NT or Solaris

These scripts apply to the WebDB 2.1 product installed on your Windows NT or Solaris machine.

To migrate Oracle WebDB version 2.1 to version 2.2:

1. Ensure that you back up the contents of the schema in which you installed WebDB 2.1. You can do this by exporting the contents to a file using the export utility on Solaris or Windows NT.

2. Insert your product Oracle WebDB 2.2 CD and navigate to the /upgrade/webdb/21-22 directory.

3. While in the /upgrade/webdb/21-22 directory, execute the upgrade script with the appropriate command:

   Note: With the exception of the connect string (TNS names alias), all are required parameters when running the command.

   Windows NT

   upgrad22.cmd <sys_password> <user> <password> <db_o8> <connect_string> 
   <logfile> 
   
   

   Solaris

   upgrad22.csh <sys_password> <user> <password> <db_o8> <connect_string> 
   <logfile> 
   
   

   where:

   Parameter	Description
   sys_password	The SYS user password for logging on to the Oracle database where you want to migrate WebDB 2.1 to WebDB 2.2.
   user	The name of the Oracle installation schema that owns the WebDB objects you want to migrate.
   password	The password for the WebDB installation schema you want to migrate.
   db_o8	Specify TRUE if you are migrating WebDB from an Oracle8 database and FALSE for migrating to an Oracle 7 database. Default: TRUE
   connect_string	(to connect to a remote database only) A connect string or TNS names alias for the remote database you are connecting to. If you are migrating WebDB to a local database, do not specify a connect string.
   log_file	Name of the log file where you want to log information during the migration process. Log files are generated in the following locations: Windows NT    D:\<ORACLE_HOME>\ORAINST\log_file.log Solaris    /<user1>/log_file.log where user1 is the user's UNIX Home directory

4. Press the Enter key to run the upgrade script.

   Note: Upgrading WebDB from one version to another will take several minutes.

4.2 Migrating WebDB 2.1 Sites to Version 2.2

WebDB 2.2 provides scripts that you can use to migrate the Site Building packages included in Oracle WebDB 2.1 to version 2.2.

You can run the scripts from the SQL*Plus command prompt.

Important: Before performing a site migration, you must complete the steps to upgrade Oracle Web Application (OWA) from version 4.0.7 to 4.0.8. See Section 4.1.1, "Upgrading Oracle Web Application (OWA) from Version 4.0.7 to 4.0.8". Running the scripts from the Windows NT or Solaris prompt is not available.

4.2.1 Running the Site Migration Script from SQL*Plus

To run the migration scripts from the SQL*Plus prompt:

1. Insert your product Oracle WebDB 2.2 CD and navigate to the /upgrade/sites/21-22 directory.

2. Log on to SQL*Plus as the SYS user and with the appropriate password.

3. Execute upgrd220.sql using the following syntax:

   SQL> @upgrd220.sql [site schema] [site admin] [default tablespace] 
   [temporary tablespace] [site language] [site schema password@DB 
   connectstring] 
   
   [log file name]
   
   

where:

Parameter	Description
site schema	The WebDB Site Builder schema to upgrade.
site admin	The WebDB Site Builder administrator schema. Default: [site schema]_admin
default tablespace	Install webdb objects in this tablespace. Default: USERS
temporary tablespace	Use this tablespace for temporary objects. Default: TEMP
site language	The language installed for the site. Default: us
site schema password	The WebDB Site Builder schema password. Default: [site schema]
DB connectstring	The connect string (TNS names alias) for the remote database. If your database is local, the connect string is not required. Default: none
log file name	The filename for the migration script's log file.

4. Run the following commands from the SQL*Plus prompt:

   sqlldr userid=<site_schema>/<password> control=wwvsbus.ctl log=<dir>wwvsbus.log 
   sqlldr userid=<site_schema>/<password> control=help.ctl log=<dir>help.log 
   sqlldr userid=<site_schema>/<password> control=helpctxs.ctl log=<dir>helpctxs.log 
   sqlldr userid=<site_schema>/<password> control=helprela.ctl log=<dir>helprela.log 
   sqlldr userid=<site_schema>/<password> control=helpindx.ctl log=<dir>helpindx.log 
   
   

where <dir> is the directory where you want the log files to be created and <password> is the password for the site_schema.

Notes: Do not use your computer while the site migration process is in effect. Upgrading a WebDB site from one version to another can range from several minutes to about an hour depending on how much content is contained on your WebDB site.

Additional Information: If you want to import or export a WebDB site from one server to another, see the "Moving a WebDB site to a new server" topic in the "Creating and Managing Sites - Task Help." This topic is below the "Setting Up and Maintaining a WebDB Site" book node.

4.3 Dropping a WebDB Site

Upon installing a WebDB site, three database schemas are created. For example, if the schema name chosen for the new WebDB site is MYSITE, then the relevant schemas would be:

• MYSITE

• MYSITE_PUBLIC

• MYSITE_ADMIN

If you want to drop or delete a WebDB site from your database, you must delete these schemas and the data associated with them.

To drop WebDB sites, do the following in SQL*Plus:

1. Log on to SQL*Plus as the SYS user and with the appropriate password.

2. Enter the following command to display a list of users:

   select username from all_users
   
   

3. In the list of users, search for the names of the WebDB sites you want to drop. For each <sitename>, you should have the following users:

   <sitename>
   <sitename>_public
   <sitename>_admin
   
   

4. Enter the following commands from the SQL*Plus prompt to drop all schemas for the site:

   drop user <sitename> cascade;
   drop user <sitename>_public cascade;
   drop user <sitename>_admin cascade;
   

   Note: Be patient, as this operation may take awhile, depending on the size of the WebDB site(s) that is being dropped.

5. Repeat Steps 3 and 4 for all the sites that you want to drop.

   Note: Make sure that you really want to drop a site or site users since you cannot recover once they have been dropped.

6. Your next step is to drop the URL link from the WebDB interface by performing these steps:

7. Log on to SQL*Plus as the "webdb" user.

8. Delete the schema by executing the following command:

   Delete from wwv_modules$ where schema = upper('<sitename>');
   
   

   Note: Do this for each WebDB site you have dropped above.

9. Execute the command by typing:

   COMMIT;
   

   The URL to the WebDB site is dropped.

4.4 Dropping Users Who Create Sites

If you drop a user from the Oracle database, any WebDB site that was created by that user, will stop working. To resolve this:

1. Log on to SQL*Plus as the SYS user with the following command:

   sqlplus sys/<sys_password>
   
   

2. Execute the following command:

   grant execute on sys.dbms_sys_sql to <site_schema>
   
   

   where

   • sys_password is the SYS user password for the database.

   • site_schema is the schema that owns the site.

   You should now be able to access the WebDB site with an authenticated (valid) database username/password.