Reducing downtime during Oracle database migration

Welcome to the Oracle community forum and interact with 2 million technical staff. You can combine the above methods to migrate mode objects, such as using the Export Import method for small tables and modes, large tables are connected to databases using the parallel direct loading and insertion method. 3. create an index and activate constraints (system downtime required) * Method 1: Use the preceding

Welcome to the Oracle community forum and interact with 2 million technical staff> you can combine the above methods to migrate mode objects, such as using the Export/Import method for small tables and modes, for large tables, the "parallel direct loading and insertion" method is used for database connections ". 3. create an index and activate constraints (system downtime required) * Method 1: Use the preceding

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

You can combine the above methods to migrate mode objects. For example, you can use the export/import method for small tables and modes, and use the parallel direct loading and Insertion Method for large tables in database connections ".

3. Create indexes and activate constraints (system downtime required)

* Method 1: Use the exported structure file described earlier to create indexes and constraints in sequence

The command is as follows:

Imp sys/password file = structure. dmp constraints = y indexes = y full = y ignore = y

There is a problem with this method-using the import method, we can not specify the concurrent Custom Level to create the index, nor can we specify the NOLOGGING attribute. Even if we disable redo write operations on the disk using special parameters not recorded in the official file, the NOLOGGING attribute can still improve performance by setting fewer Detection Points and using fewer CPUs. fortunately, there is an import feature that can bypass this issue. It runs the script for extracting the index from the exported file.

* Method 2: Use the indexfile option of the import operation to create an index separately, and then use the import creation constraints as usual.

A long index creation time can be greatly shortened when concurrent execution and attribute nologging are used. Since the imported syntax itself does not allow us to specify the index creation method, we have to first get the index creation script and then modify them according to our actual needs. The imported indexfile option is very convenient to use, as shown below:

The command is as follows:

Imp sys/password file = structure. dmp constraints = y indexes = y full = y ignore = y \

Show = y indexfile = indexes. SQL

In the preceding example, the SQL data definition language (DDL) command is used to create separate and restricted indexes (for key and unique constraints, some indexes are also implicitly created ). Note that the show = y parameter specifies that the database cannot be changed. The exported file is only scanned from start to end, any index created during this period is stored in the indexes specified by the indexfile option. SQL script.

Next, we can use a text editor to open the SQL script, use NOLOGGING instead of LOGGING, and add the PARALLEL clause for each large index creation command. You can also use the change session command at the beginning of the script to modify some parameters, such as sort_area_size and db_file_multiblock_read_count, these two parameters greatly increase the index creation speed (for more information, see database performance tuning ). After modification, use sqlplus to run the script.

The command is as follows:

Sqlplus sys/password @ indexes. SQL

The index file contains the indexes required by the independent index and foreign key constraint or uniqueness constraint, but does not contain the restriction definition. To create and activate these constraints, we must run the import process on structure. dmp again. The command is as follows:

The command is as follows:

Imp sys/password file = structure. dmp constraints = y indexes = n full = y ignore = y

In the above command, we set indexes = n because they have been created in the previous step. At the same time, because all required indexes already exist, the index creation constraint should be completed very quickly, because you do not need to create an index on the unique key/primary key.

4. Verify that no object definition in the source database has been changed since the database structure was exported (both in step 2 and Step 3)

* Query the dba_segments described above and migrate any new objects to the new database, if appropriate (for example, use the Enterprise Manager or Toad to extract the "Data Definition Language" of a single object "). There should be no many new objects or no new objects at all, because after the database structure is exported, the configuration of the program server should be frozen and cannot be changed. This task can usually be performed at the same time as data migration or index creation.

Note: If you use "direct loading" and disable all indexes on the associated table, no major rollback and redo operations should be performed. This is one of the reasons why primary key/unique key constraints should be disabled during data loading, because they all require index support.

[1] [2]