"Dan's Oracle7 Guide" - README.v6to_o7

From: Daniel B. Bikle <dbikle_at_alumni.cco.caltech.edu>
Date: 07 Sep 1993 07:50:47 GMT
Message-ID: <DBIKLE.93Sep7005047_at_alumni.cco.caltech.edu>


The following discussion is an excerpt from "Dan's Oracle7 Guide".

While the guide could not be described as a great literary work, it might prove useful to those DBA's and Application Developers who work with Oracle7.

The guide will be finished sometime in October.

If you want a copy, I'll put you on my mailing list.

The public domain, email, beta version is free.

If you have an extra $20, please send it to my favorite charity:

Amnesty International 322 8th ave. ny NY usa 10001.

All of the trademarks mentioned in this excerpt are owned by their respective owners.

Of course, everything I create has no warranty. If my software or ideas cause you problems, feel free to send me hate mail.

-Dan



Daniel B. Bikle
dbikle_at_alumni.caltech.edu
415/854-9542
P.O. BOX 'D'
MENLO PARK CA 94026

README.v6to_o7


Many customers will wish to convert their V6 based applications into Oracle7 applications. Three strategies are available to these customers which are outlined below:

Migrate Strategy



The migrate strategy relies on the Oracle supplied migrate utility which transforms a V6 dbs into an Oracle7 dbs. It would be wise to back up the V6 dbs in two different ways before skipping down this path: Export the V6 dbs and then shut it down and place a cold image of the software and datafiles on tape.

The migrate strategy has three obvious advantages over the stragies listed below: It is conceptually simple, it conserves disk space, and it might save time. Its main disadvantage is that it wipes out the V6 dbs.

Database Links Strategy



This strategy contains three main ideas. First, install Oracle7 (for a more thorough, yet brief, discussion about installing Oracle7, read the section below on the Exp/Imp Strategy). Next, manually create all of the users (also known as schema in Oracle7 parlance). Then, select rows into the Oracle7 tables from the V6 tables through database links.

Exp/Imp Strategy



The strategy above will only transfer tables and rows. If the DBA needs to transfer other objects like grants, views, synonyms, and indexes, the Exp/Imp strategy works. The Exp/Imp strategy has many variations, but they all follow along these lines: First, export the V6 dbs. Next, install Oracle7. Finally, import data into Oracle7 from the V6 export file(s). Most of the variations are due to how much control the DBA wants to relinquish to the Import utility.

Exp/Imp Scenario 1



For example, a DBA might want to force Import to handle the maximum number of details as possible. In this situation, the DBA would follow these steps:
  1. Export V6 (Aull dbs export); shut it down; backup the datafiles, redologs, control files, and export file; remove the V6 dbs files
  2. Install Oracle7 software, not on top of the V6 software, but owned by the same UNIX user
  3. Create Oracle7 redologs, System tablespace, and a "dummy" rollback segment if not already done by the Oracle7 installation script.
  4. Reference the "dummy" rollback segment in the "init.ora" file which is usually kept at $ORACLE_HOME/dbs/init${ORACLE_SID}.ora then "bounce" (shut it down; start it up from sqldba utility) the dbs.
  5. Import V6 dbs objects into Oracle7 from the V6 export file. This step will place the Oracle7 dbs files in the same location as the old V6 dbs files
  6. Replace the reference to the dummy rollback segment with a reference to the new, imported rollback segments in the init.ora then bounce the dbs
  7. Drop the "dummy" rollback segment.

Exp/Imp Scenario 2



Another DBA might want to keep the V6 dbs active. Also he/she might want to transfer a subset of the V6 dbs objects into Oracle7 rather than all of them. To achieve this, the DBA might follow these steps:
  1. Export subsets of the V6 dbs (several user level exports might be called for here)
  2. Export V6 (A full dbs export but with ROWS=N)
  3. Install Oracle7 software under a different user than the V6 software so the Oracle7 instance could not possibly overwrite any of the V6 files (assuming they are write protected as they should be).
  4. Manually create all redologs, tablespaces, and rollback segments which had not been created during the Oracle7 software installation (this step consists of many steps which we will not elaborate here)
  5. Import subsets of the V6 dbs (several user level imports might be called for here)

One idea falls out of the second scenario which might be interesting to a DBA of a multi-gigabyte dbs supporting several table owners (many examples of this type of dbs are found among companies using Oracle Financials/Manufacturing). The idea is simple: when several user level exports are run on the V6 dbs, they could run concurrently. Several concurrent user level exports will run much faster than a single full database export.

This idea becomes much more attractive when the time comes to import the data since importing an object is slower than exporting that same object. The idea gains power when it is implemented on a multiprocessor machine with 2 disk drives for each user (one drive for the tables, another for the indexes) utilizing multiple dbwr processes (the number of dbwr processes is controlled by an init param named db_writers).

A phrase could be coined to describe the idea above:

        "Multi Threaded Export/Import".

With a discussion about the three types of conversion strategies behind us, a list of several scripts is shown below. They are listed in an order consistent with the discussion above.

migrateDemo.sh # demo of migration utility (to be completed later)

sq6.2.7.P.sh	# simple shell script which runs V6 sqlplus against an Oracle7
		# instance using the pipe driver

sq6.2.7.S.sh	# simple shell script which runs V6 sqlplus against an Oracle7
		# this script relies on an alias in /etc/sqlnet.
		# THIS SCRIPT DOES NOT WORK
		# this script is provided as an example of what to avoid.

sq6.2.7.T.sh	# simple shell script which runs V6 sqlplus against an Oracle7
		# instance using the TCP/IP driver

cp6Tab27Tab.sh	# demo of selecting rows from a V6 table into an Oracle7 table
		# using a database link with no hard-coded password.

cp7Tab26Tab.sh	# demo of selecting rows from an Oracle7 table into a V6 table
		# using a V6 database link with a temporary hard-coded 
		# password.

fullExpImp.sh	# demo of a full V6 export then full Oracle7 import

exp6_imp7.sh	# demo of exporting a set of V6 objects and then importing 
		# a subset into an Oracle7 database

mtExp.sh	# demo of Multi Threaded Export of V6 dbs

mtImp.sh	# demo of Multi Threaded Import into an Oracle7 database

=============================================================================
Received on Tue Sep 07 1993 - 09:50:47 CEST

Original text of this message