Utility Belt

“Holy nothing works here, DBAtman!”
Take it easy, young Robin: DBAtman always has a solution…

To understand the situation:

Task: Need to migrate large database 11.1.0.6 to 12c Multi-Tenant Database with minimum downtime.
To better use the features, reorginize objects and compress data, I decided to migrate the data logically (not physically).
The first option was to migrate schema by schema through datapump with database link. There is no long columns.

Problem1: The database was veeery slow with perfect match to Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp.
workaround: None
Solution: Upgrade to 11.2. (No way).
Other things: Yes, I tried to change the cursor sharing, the estimate from blocks to statistics and all things documented. It doesn’t work.

Ok doke! Let’s use traditional exp/imp tools (with some migration area), right?
Problem2: ORA-12899 on import related to multiblocking x singleblocking charsets.
Solution: https://grepora.com/2015/11/20/charsets-single-byte-vs-multibyte-issue/

Done? Not for all. For some tables, just happened the error:

EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

An what Oracle says? “Solution: Use Datapump!”

Well, well… I realized I was going to become by myself…
Ok, so lets create table as select using database link. For most of all, ok…
But, for example, one of the missing tables has 700 million rows (350GB of compressed and no partitioned data).
Just to remmember that DBLink exclude parallel options (always serial).

The solution was to make a McGayver, this way:
1) Creating an aux table (source database):

alter session force parallel query parallel 12;
create table SCHEMA_OWNER.AUX_ROWID(ROW_ID,NUM) as select rowid, rownum from SCHEMA_OWNER.TABLE;
alter session disable parallel query;

* This table will be used to break the table in chunks.

2) Script run_chunck.sql to run each chunk of data:

DECLARE
counter number;
CURSOR cur_data is
select row_id from (
select row_id, num from SCHEMA_OWNER.AUX_ROWID@SOURCEDB order by num)
where num >= &1
and num <=&2;
BEGIN
counter :=0;
FOR x IN cur_data LOOP
BEGIN
counter := counter +1;
insert into SCHEMA_OWNER.TABLE select * from SCHEMA_OWNER.TABLE@SOURCEDB where rowid = x.row_id;
if counter = 1000 then ---commit every 1000 rows
commit;
counter := 0;
end if;
EXCEPTION
when OTHERS then
dbms_output.put_line('Error ROW_ID: '||x.row_id||sqlerrm);
END;
END LOOP;
COMMIT;
END;
/
exit;

3) Run in a BAT or SH like (my example was made for a bat, with “chunks” of 50 million rows – and commits by every 1k, defined on item 2):

@echo off
set /p db="Target Database.: "
set /p user="Username.......: "
set /p pass="Password..................: "
pause
START sqlplus %user%/%pass%@%db% @run_chunck.sql 1 2060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 2060054 52060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 52060054 102060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 102060054 152060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 152060054 202060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 202060054 252060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 252060054 302060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 302060054 352060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 352060054 402060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 402060054 452060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 452060054 502060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 502060054 552060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 552060054 602060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 602060054 652060053
START sqlplus %user%/%pass%@%db% @run_chunck.sql 652060054 702060053 -- count(*) from table

Watching the inserts running…

targetdb>@sess
User:DBATMAN_HELPS
USERNAME EVENT SQL_ID
---------- ---------- -------------------------
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink gt3mq5ct7mt6r
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from dblink 6qc1hsnkkfhnw
DBATMAN_HELPS SQL*Net message from client
DBATMAN_HELPS SQL*Net message from client
DBATMAN_HELPS SQL*Net message to client c7a5tcc3a84k6

After a few (26 hours) the copy was successfully concluded.

Na, na, na, na, na, na, na, na, na, na, na, na… DBAtman!