Execute privileges will be granted to PUBLIC for all Time Series types and packages.

Migration to Oracle8

In order to migrate a database, as a DBA you need to understand the procedures involved in the process. It needs careful planning, testing the migration process and the migrated test database, preparing and preserving the source database, migrating the source database, and testing and tuning the new production database. Typically, as a DBA you are responsible for ensuring the success of the migration process. The application developer is responsible for ensuring that the application designed for the Oracle7 source database works the same way with the migrated Oracle8 target database. Users should not have access to the migrated database until all applications have been tested properly.

---

Caution:  
Always back up your version 7 database before you migrate!

---

There are three methods available for migrating or upgrading a database to Oracle8:

•  The migration utility, for migrating from Oracle7, release 7.x to Oracle8.

•  Export of an Oracle7 (or version 6) source database into an Oracle8 target database. It could be a full or partial export followed by full or partial import.

•  Copying data from a source database into an Oracle8 database. It could be either the COPY command or AS clause of the CREATE TABLE command.

The migration utility converts the files and structures in the source database (Oracle7) by changing the file headers and possibly the definitions of the data in the files. It does not affect the data portions of the datafiles; also their format and content remains unchanged. It is good for quick migration of the entire source and not for selective datafiles, which is best handled by Export and Import.

•  As typical of Export/Import, these utilities physically copy the data from the source to a new database. However, it requires that the new target Oracle8 database must have already been created before the Import utility can be used to migrate the Export file. The main advantage is that the source database is available throughout the migration process. However, if a consistent snapshot of the database is required, the source database must be up in the Restricted mode during the Export process. Thus the two databases could be running in parallel before the Oracle8 database is released to users.

The Export and Import utilities provide the following benefits over and above the migrate utility:

•  Creates an entirely new database, restructured and defragmented

•  Allows specified migration of objects or users

•  Serves as a backup archive

These advantages do have the regular disadvantages associated with any export/import procedure: Export data is a logical copy of the data. It can allow recovery to the date and time the export was taken. An Export file should not be edited and can be used by Import only. Import imports only full tables and cannot be used to do a conditional load. In terms of time and disk space requirements, Export/Import is very expensive.

Copying Data

You can copy data from one Oracle database to another using database links. The data can be copied using the regular SQL*Plus COPY command or using the INSERT INTO, CREATE TABLE FROM command, CREATE TABLE ... AS. This method offers the same advantage of moving selected objects or even specified rows across the databases. Copying also requires less disk and memory buffer space for migration than export/import.

Develop a Testing Plan

The migration procedure requires planning and testing, involving the path from the source to the new migrated database, irrespective of any of the methods—migration utility, export/import, or copying data. Regardless of what method you choose, as a database administrator, it is your responsibility to establish, test, and validate a migration plan. The testing plan normally includes functional, integration, performance, and volume/load stress testing and pre- and post-migration testing.

Be sure to include timing tests, data dictionary growth, observations and resource usage observations. Collecting this information will help you compare the source and the target database. You can use EXPLAIN PLAN on both source and target to compare the execution paths followed for SQL statements.