Optimising Oracle Imports - page 2
author: Martin "mpot" Pot
date: 25 October 2004
navigation:
<prev
| 1
| 2
| 3
| 4
| next>
Pre-Export Activities
Preparatory Work
Prior to exporting your current Oracle database, there are a number of activities you should perform, to streamline the database migration process.
Purge Unnecessary Data
Before exporting the data out of the current database, purge any log tables and other unnecessary data.
In particular, old data can typically be purged from log and status tables, thus reducing the volume of data to be migrated.
Be sure to preserve a copy of this data if required, and liase with the data owner to determine how much data they want to retain in these tables.
Remove Non-Critical Tables
If the current database contains large tables which are not critical to any of the major applications using the database, the size of database import can be reduced by excluding these tables, and migrating them at a later time, once the database is operational again.
To do so, the following activities need to be performed prior to the database export:
- identify large tables that are not critical to major application(s)
- shutdown any applications using identified tables
- export identified tables
- truncate identified tables
- perform database migration
- import identified tables from previously created dump file
Database Files
AutoExtend
Some Oracle database files are created with the AUTOEXTEND option turned on, which allows the database files to grow on an as-needed basis.
Resize Datafiles
Prior to importing a database dump, the datafiles should be resized to ensure they are large enough to hold the data being imported, rather than relying on Oracle to extend the datafiles.
This prevents Oracle from having to continually extend the database files during the import, which is a relatively expensive process.
If the AUTOEXTEND option is turned off, then it's critical that the database files are resized appropriately prior to performing the import, as the import will fail when the database files are filled.
Determine Size
Check the size of the database files in the Oracle 7.3.4 database for an indication of the expected size in the new database, and modify the size of each datafile in your database build script. The syntax is typically:
create tablespace DATA1_TS
datafile 'e:\ora8\ORADATA\SID\data01.dbf'
size 3500M reuse autoextend on next 5M
default storage (initial 50K next 100K
minextents 1 maxextents unlimited pctincrease 0);
Database Already Created?
If your new database has already been created, refer to the Oracle documentation for details on resizing existing datafiles and adding new datafiles to an existing tablespace.
References
Metalink Note 1029252.6 How to Resize a Datafile
Archive Log Mode
Database Robustness
Archive log mode is necessary for creating on-line backups and for certain types of database recovery. Configuring the database to operate in archive log mode allows the user to perform complete and point-in-time from media (disk) failures using off-line or on-line backups. If archive log mode is disabled, the database can be restored from a backup in case of failure, but it cannot be rolled forward to the point where the failure occurred.
Disable Archive Log Mode
Ensure you disable archive log mode prior to performing the import, as this will reduce the overhead of creating and managing archive logs.
Backup Database
Note that a cold backup must be taken immediately after doing the unrecoverable operations, to ensure roll-forward compatibility.
References
Metalink Note 69739.1 How to Turn Archiving ON and OFF
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Block Size
Definition
DB_BLOCK_SIZE specifies the size (in bytes) of Oracle database blocks. Typical values are 2048 and 4096. The value for DB_BLOCK_SIZE in effect at the time you create the database determines the size of the blocks.
Larger Block Size
Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries. A larger block size results in less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.
Existing Database
Note that modifying the block size of an existing database requires the database to be recreated.
Refer to the Oracle documentation for more information on this procedure.
Datafile Limitation
The database being migrated used a default block size of 2KB. Note that a block size of 2KB limits the maximum datafile size to 8GB in Windows 2000.
Configuration
|
Default value:
| |
2048 bytes
| |
Recommended value:
| |
8192
| |
Parameter name:
| |
db_block_size
| |
Location:
| |
init.ora
|
References
Metalink Note 1011167.7 How to Change Database Block Size
Metalink Note 46757.1 Notes on Choosing an Optimal DB BLOCK SIZE
Metalink Note 30709.1 Init.ora Parameter "DB_BLOCK_SIZE" Reference Note
Metalink Note 93763.1 Tuning Considerations When Import Is Slow
Timed Statistics
Definition
The timed_statistics parameter specifies whether time-related statistics are collected by the Oracle database.
Disable Timed Statistics
Ensure the timed_statistics parameter is set to FALSE.
Configuration
|
Default value:
| |
FALSE
| |
Recommended value:
| |
FALSE
| |
Parameter name:
| |
timed_statistics
| |
Location:
| |
init.ora
|
References
Metalink Note 30824.1 Init.ora Parameter "TIMED_STATISTICS" Reference Note
navigation:
<prev
| 1
| 2
| 3
| 4
| next>
|
|
