Method

1. Data Migration Method Using DB Link

1.1 Data Migration from Source to Target

You can migrate data from Source to Target through a DB Link.

--Source
SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+ append parallel(4) */ into tbl_link_test@ttot select /*+ parallel(4) */ * from tbl_link_test;

SQL> select * from v$pe_session; --Check from another session

       SID    SERIAL#     PEP_ID      QCSID  QCSERIAL#     DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ---------- ----------
        97       1601          0         77        531          4          4
        98       1600          0         77        531          4          4
        99       1598          0         77        531          4          4
       100       1599          0         77        531          4          4

4 rows selected.


--Target
SQL> alter table TBL_LINK_TEST parallel 4;

Table 'TBL_LINK_TEST' altered.

SQL> select * from v$pe_session;

0 row selected.

Caution

When using the DB link of the Source, you cannot use the parallel option when inserting into the Target table.

Even if you change the parallel setting on the Target table in advance, parallel processing will not occur during migration.

Therefore, this data migration method is not recommended.

1.2 Data Migration from Source to Target Initiated from Target

--Target
SQL> alter session enable parallel dml;

Session altered.

SQL> insert /*+ append parallel(4) */ into tbl_link_test select /*+ parallel(4) */ * from tbl_link_test@ttot;

SQL> select * from v$pe_Session; --Check from another session

       SID    SERIAL#     PEP_ID      QCSID  QCSERIAL#     DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ---------- ----------
        97       3879          0         77       3852          4          4
        98       3881          0         77       3852          4          4
        99       3882          0         77       3852          4          4
       100       3884          0         77       3852          4          4
       101       3883          0         77       3852          4          4
       102       3885          0         77       3852          4          4
       103       3880          0         77       3852          4          4
       104       3886          0         77       3852          4          4

8 rows selected.


--Source
SQL> select * from v$pe_session;

       SID    SERIAL#     PEP_ID      QCSID  QCSERIAL#     DEGREE REQ_DEGREE
---------- ---------- ---------- ---------- ---------- ---------- ----------
        97       4054          0         79       3928          4          4
        98       4053          0         79       3928          4          4
        99       4052          0         79       3928          4          4
       100       4051          0         79       3928          4          4

4 rows selected.

Note

With this method, both the Target and Source can be processed in parallel.

2. Pre-Migration Settings

2.1 TABLE LOGGING Setting

You can improve migration speed by setting the target table to nologging mode.

alter table <username>.<tablename> nologging;

2.2 Drop Indexes

You can improve migration speed by dropping indexes created on the target table.

DROP INDEX <username>.<indexname>;

Note

If you secure the index creation statement before performing DROP INDEX, you can use it later when recreating the INDEX.

2.3 Set Table Parallel Processing

You can specify append processing and the DOP (Degree of Parallelism) for the target table through hints.

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ append parallel(4) */ <username>.<tablename> 
SELECT /*+ parallel(4) */ <username>.<tablename>@<DB Link name>;

COMMIT;

Caution

You must enable parallel dml at the session level and commit after migration.

2.4 Set NOARCHIVELOG MODE

You can improve migration speed by setting the Target's ARCHIVELOG MODE to NOARCHIVELOG.

ALTER DATABASE NOARCHIVELOG;

Caution

This operation can only be performed in mount state, and DB restart is required.

2.5 Set UNDO RETENTION

You can improve migration speed by shortening the reuse time of the most used UNDO TABLESPACE during data migration. (Default: 900 (seconds))

-- How to change while DB is running
ALTER SYSTEM SET UNDO_RETENTION=30;

-- How to change requiring DB restart (add parameter)
vi $TB_HOME/config/$TB_SID.tip

UNDO_RETENTION=30

Note

When using the ALTER statement to change during DB operation, it applies to newly connected sessions.

It is recommended to set the value to 100 or less.

2.6 Disable or Drop Constraints

Constraints are checked when inserting data into the Target. Therefore, disabling or dropping constraints on the Target can improve data migration speed.

-- How to disable constraint
ALTER TABLE <username>.<tablename> disable constraint <constraintname>;


-- How to drop constraint
ALTER TABLE <username>.<tablename> drop constraint <constraintname>;

Note

If you secure the constraint creation statement before performing DROP CONSTRAINT, you can use it later when recreating the CONSTRAINT.

3. Post-Migration Settings

3.1 Restore TABLE LOGGING Setting

After data migration is complete, you must restore the TABLE LOGGING setting.

-- Check the LOGGING mode of the Source and apply it to the Target
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || '.' || ' logging;'
FROM DBA_TABLES@<DB Link name>
WHERE LOGGING='YES' and OWNER='<username>'; 

Caution

If you continue to use NOLOGGING, there is a performance benefit, but recovery is not possible.

3.2 Recreate Indexes

After data migration is complete, recreate the indexes that were previously dropped. Create them using the secured creation statements in parallel, and once creation is complete, restore the index DOP.

CREATE INDEX <username>.<indexname> ~~~~ PARALLEL 4;

ALTER INDEX <username>.<indexname> NOPARALLEL;

3.3 Restore Table Parallel Processing Setting

After data migration is complete, restore the table DOP setting.

-- Check the DOP value of the Source and apply it to the Target
SELECT 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' parallel ' || degree || ';'
FROM DBA_TABLES@<DB Link name>
WHERE OWNER='<username>';

3.4 Set ARCHIVELOG MODE

After data migration is complete, you must change the Target's ARCHIVELOG MODE to ARCHIVELOG.

ALTER DATABASE ARCHIVELOG;

Caution

This operation can only be performed in mount state, and DB restart is required.

HOTBACKUP is only possible in ARCHIVELOG state.

3.5 Restore UNDO RETENTION

After data migration is complete, restore the UNDO RETENTION value. (Default: 900 (seconds))

-- How to change while DB is running
ALTER SYSTEM SET UNDO_RETENTION=900;

-- How to change requiring DB restart (add parameter)
vi $TB_HOME/config/$TB_SID.tip

UNDO_RETENTION=900

3.6 Enable and Recreate Constraints

After data migration is complete, you must enable constraints. For large tables, this process can take a significant amount of time, so you should consider this when estimating migration time.

-- How to add constraint
ALTER TABLE <username>.<tablename> ADD CONSTRAINT <constraintname> <constraint> <columnname> [NOVALIDATE];

-- How to enable constraint
ALTER TABLE <username>.<tablename> ENABLE <constraintname>;

Caution

Although you can shorten the time by using the NOVALIDATE option, it can cause side effects such as changes to the execution plan, so it is not recommended.

The NOVALIDATE option can only be used when adding constraints.

4. Other Notes

• Data migrated with NOLOGGING cannot be recovered, so it is strongly recommended to perform a full backup after migration is complete.
• When migrating data via DB Link, you cannot select only specific partitions. Therefore, the operator must specify the range condition separately.
• In a TAC environment, shut down one node before proceeding with data migration. If migrating data by user (schema), perform data migration using different users (schemas) on each node.

5. Conclusion

• Migrate data using the Target's DB Link
• Set the Target's target table to nologging
• Disable or drop indexes on the Target's target table
• Set append and parallel processing on the Target's target table
• Change the Target's target DB to NOARCHIVELOG MODE
• Disable or drop constraints on the Target's target