Document Type | Technical Information
Category | Migration
Applicable Product Version | 6F
Document Number | TMITI015
Overview
This guide provides instructions on the settings to be configured after migrating data via DB Link.
Note
This document is based on the conditions below.
Method
1. Table Logging Settings
After the data migration is completed, restore the table logging settings. Check the logging mode of the Source and apply it to the Target.
Continuing to use nologging has performance advantages but the disadvantage of being unrecoverable.
select' alter table ' || owner ||'.'|| table_name ||' logging;' from dba_tables where logging='YES' and owner=โmigration_user_nameโ;
2. Recreate Indexes
After data migration is completed, recreate the indexes that were previously dropped.
Use the previously prepared creation scripts to create indexes in parallel, and after completion, revert the indexes back to dropped state if needed.
create index user_name.index_name ~~~~ parallel 4; alter index user_name.index_name noparallel;
3. Table Parallel Processing Settings
After data migration is completed, restore the table DOP (Degree of Parallelism) settings.
Check the DOP value of the Source and apply it to the Target.
select' alter table ' || owner ||'.'|| table_name ||' parallel '|| degree ||';' from dba_tables@dblink_name where owner=โmigration_user_nameโ;
4. Archive Settings
After data migration is completed, change the archive mode of the Target DB to archivelog.
This operation requires restarting the DB.
HotBackup is only possible in archivelog mode.
alter database archivelog;
5. Undo Retention Restoration
After data migration is completed, restore the Undo Retention value.
Refer to the value of the Source DB; the default value is 900 seconds.
alter system set undo_retention=900;
6. Enable and Recreate Constraints
After data migration is completed, enable constraints. For large tables, this may take considerable time, so allow sufficient migration time considering this.
If constraints were dropped, execute the previously prepared creation scripts. Although the novalidate option can shorten the time, it may cause changes in execution plans, so it is recommended to avoid using it if possible.
The novalidate option cannot be used when enabling constraints, only when adding them.
alter table user_name.table_name add constraint constraint_name constraint_column_name [novalidate]; alter table user_name.table_name enable constraint_name;