How to Migrate Data from 9i to 12c via DB Link

  • by

Migrate 9i to 12c

As I said in How to Resolve ORA-03134, you cannot directly access an Oracle 9i database from an Oracle 12c or 18c client to fetch data, you have to work around it by building up an intermediary database as your middle step to the Oracle 9i database.

For migrating a database, the intermediary database is also useful for incremental or daily migration. If you cannot build up an intermediate database or you really don’t like the solution, you may consider to reverse the direction, which is to make 9i database dump data to 12c or 18c through network as long as you have already solved ORA-28040.

Solution

The main technique in this post is to compose, prepare and execute INSERT INTO SELECT statements in the source database (9i) so as to transfer data to the remote 12c database via a database link.

First, we have to know how to compose INSERT INTO SELECT statements for all tables of specific schemas

1. Compose Statements

First of all, prepare a compose statement compose_insert_remote_stmt.sql in the 9i database server.

[oracle@ora9i ~]$ vi compose_insert_remote_stmt.sql

set linesize 120 pagesize 0 echo off feedback off;
column stmt format a120;
spool insert_remote_12c_hr_sh_tables.sql;
select 'SET ECHO ON TIMING ON;' from dual;
select 'SET AUTOCOMMIT ON;' from dual;
select distinct 'INSERT INTO "' || t.owner || '"."' || t.table_name || '"@ORA12C SELECT * FROM "' || t.owner || '"."' || t.table_name || '";' || chr(10) || 'COMMIT;' stmt from dba_tables t inner join dba_segments s on t.owner = s.owner and t.table_name = s.segment_name where t.owner in ('HR', 'SH') and s.segment_type = 'TABLE' order by 1 nulls last;
select 'EXIT;' from dual;
spool off;
exit;

In the above, I insert remote tables owned by HR and SH through a database link named ORA12C and commit each INSERT INTO SELECT for each table.

Committing every change is very important, since the network condition may not be as good as we thought, we should secure our hard work after each completion of every INSERT INTO SELECT.

In the above SQL script, we use two skills to achieve this, one is SET AUTOCOMMIT ON for sqlplus, the other is to issue explicit COMMIT commands in case that SET AUTOCOMMIT ON cannot be used in your tools or environment.

A trick is worth it to mention, I add a character char(10) to represent a line break in order to separate COMMIT from INSERT INTO SELECT. Can we just put them both in one line? No, we can’t.

2. Spool Statements

[oracle@ora9i ~]$ sqlplus "/ as sysdba" @compose_insert_remote_stmt.sql

As a result, all composed insert statements will spool into insert_remote_12c_hr_sh_tables.sql.

Let’s check the result content.

[oracle@ora9i ~]$ cat -n insert_remote_12c_hr_sh_tables.sql

     1  SET ECHO ON TIMING ON;
     2  SET AUTOCOMMIT ON;
     3  INSERT INTO "HR"."DEPARTMENTS"@ORA12CDB SELECT * FROM "HR"."DEPARTMENTS";
     4  COMMIT;
     5
     6  INSERT INTO "HR"."EMPLOYEES"@ORA12CDB SELECT * FROM "HR"."EMPLOYEES";
     7  COMMIT;
     8
        ...
    57  INSERT INTO "SH"."TIMES"@ORA12CDB SELECT * FROM "SH"."TIMES";
    58  COMMIT;
    59
    60  EXIT;

They are all statements that are ready to INSERT INTO SELECT remote tables one by one. If you’d like to do it parallel, please split them equally by yourself.

3. Start to Migrate

Please make sure that you have created all necessary table definitions in the 12c database before starting to migrate data. Then execute those statements.

[oracle@ora9i ~]$ sqlplus "/ as sysdba" @insert_remote_12c_hr_sh_tables.sql

That’s it!

More Considerations

Using a database link to transfer data is very convenient but limited, for example, you can’t transfer tables containing LONG, CLOB or BLOB columns. This approach is only suitable for small-sized databases or some sort of incremental migration. For large-scaled data migration, you should go export / import solutions for initial loading first, then come back for this approach.

Please note that, the character set of target database may not be the same as the source database’s, you might see some problems on column width like ORA-12899 during data migration.

Leave a Reply

Your email address will not be published. Required fields are marked *