The 9i setup consisted of an Oracle 9i enterprise server, with multiple database instances. One instance had 40 GB of data. This is the procedure we used, which I believe is the quickest way possible to move this amount of data:
On the 9i database server:
- Identify and Clear all tables with temporary data. This includes temporary report tables, backup tables, log tables etc. In our case, this saved us a gigabyte.
- Export all database intances using the exp utility on the 9i server. Be careful to not allow connections to the database while exporting since sequences can be out of sync. Do not export the sys user schema.
- Create scripts to create tablespaces, schema users and oracle directories
Note: Steps 1, 2 and 3 below can be scripted using bash and sqlplus.
- Create all tablespaces on the target 11g database. Tablespace reorganization complicates the migration so I would advice against it. Plus, the imp utility expects to find the same tablespaces when importing data of tables with CLOB or BLOB fields
- Create all schema users on the target 11g database.
- Create all directories on the target 11g database, both on the database and also on the operating system level and grant the appropriate rights to the appropriate users.
- Run import with ROWS=N CONSTRAINTS=Y INDEXFILE=INDEXES.SQL, to create sql statement
script for index creation. Here's an example below.
$ export ORACLE_SID=ORADB
$ imp \'sys/oracle as sysdba\' file=/home/oracle/expdat.dmp log=createIndexes.log FROMUSER=oracleuser TOUSER=oracleuser ROWS=n INDEXES=Y CONSTRAINTS=Y INDEXFILE=INDEXES.SQL - Run imp with options ROWS=n INDEXES=N CONSTRAINTS=N STATISTICS=NONE This will create empty tables in the database w/o constraints and indexes, and also import procedures, triggers, and sequences.
- Disable Constraints and Triggers in the database. (click to get the scripts)
- Imports table data by running the imp utility with ANALYZE=n BUFFER=100000000 recordlength=65535 FEEDBACK=10000 IGNORE=Y ROWS=Y INDEXES=N CONSTRAINTS=N STATISTICS=NONE
- In sqlplus run INDEXES.SQL (created in step 4 above) to create database indexes
- Analyze tables in database. In sqlplus run:
exec dbms_stats.gather_schema_stats( ownname => 'oracleuser', estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size repeat', degree => 34 );
No comments:
Post a Comment