Workaround for bug 19566227/20563128 doing Cross Platform Migration (MOS Note 1389592.1)

In a project I have to move an Oracle 11.2.0.4-DB (around 7TB in size) from Sparc Solaris (called Source in the remainder of this Blog) to Linux x86-64 (called Target in the remainder of the Blog). I.e. a platform migration from Big Endian to Little Endian. A good method to do that is described in My Oracle Support Note 1389592.1:

11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup

Basically I copy the DB-Files using dbms_file_transfer.get_file, which also does the conversion from Big to Little Endian. Afterwards incremental Backups are applied to the transferred files and in a final step the files are attached to the Target DB using the metadata-import of transportable tablespaces.

Following the steps mentioned in that Note 1389592.1 I ran into 2 Bugs:

1.) Bug 19574653 – ORA-7445 [sxorchk] and call stack has function krhcvt_filhdr_v10 (Doc ID 19574653.8)
2.) Bug 19566227 (base bug 20563128) described in MOS Note 2015883.1: Cross Platform Migration Using XTT Method With RMAN

The first bug 19574653 could be resolved easily, because I was allowed to install that associated patch on the target system. The second bug was more difficult, because it requires the patch 20563128 to be installed on the source and the target system. As no downtime was possible, I couldn’t install that patch on the source system.

Bug 19574653 actually shows the following behavior:

During transfer ($ORACLE_HOME/perl/bin/perl xttdriver.pl -G) the following error happens for a couple of files:


$ more getfile_sourcedir2_dwh_bub_data_p06_01.dbf_34.sql
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'SOURCEDIR2',
source_file_name => 'DWH_BUB_DATA_P06_01.dbf',
source_database => 'TTSLINK',
destination_directory_object => 'DESTDIR2',
destination_file_name => 'DWH_BUB_DATA_P06_01.dbf');
END;
/
quit
 
$ sqlplus / as sysdba
SQL> @getfile_sourcedir2_dwh_bub_data_p06_01.dbf_34.sql
BEGIN
*
ERROR at line 1:
ORA-19504: failed to create file "/DWHT/u01/oradata/DWH_BUB_DATA_P06_01.dbf"
ORA-17502: ksfdcre:1 Failed to create file
/DWHT/u01/oradata/DWH_BUB_DATA_P06_01.dbf
ORA-17501: logical block size 141558272 is invalid
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 37
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 132
ORA-06512: at line 2


The nasty thing is that the process of transferring files stops and you have to manually adjust the files xttnewdatafiles.txt and getfile.sql to be able to continue from the point where it failed. That’s difficult in a 7TB-DB with 875 Datafiles.

To get the failed file transferred, a workaround is described in MOS Note 2015883.1: E.g. assume DWH_BUB_DATA_P06_01.dbf couldn’t be transferred;

Source:


SQL> select file_id from dba_data_files where file_name like '%DWH_BUB_DATA_P06_01.dbf';
 
FILE_ID
----------
357
 
$ rman target=/
 
RMAN> backup as copy datafile 357 format '/tmp/copy_df357.bck';
RMAN> quit
 
$ scp /tmp/copy_df357.bck <target-system>:/tmp/copy_df357.bck


Target:


$ rman target=/
 
RMAN> Convert from platform 'Solaris[tm] OE (64-bit)' datafile '/tmp/copy_df357.bck' format '/DWHT/u01/oradata/DWH_BUB_DATA_P06_01.dbf';
RMAN> quit


What I wanted to achieve was that the transfer of datafiles runs through without error and I do report the failed transfers in a table and use above workaround at the end of the whole transfer phase.

Here’s what I did:

On the target system I created a table system.transfer_errors to catch the affected files of bug 19574653/20563128:


sqlplus / as sysdba
 
drop table system.transfer_errors purge;
create table system.transfer_errors (sourcedir varchar2(128), file_name VARCHAR2(513));


The workaround is to adjust the file xttdriver.pl:

Original:


my $sqlQuery =
"BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => '".$2."',
source_file_name => '".$3."',
source_database => '".$props{'srclink'}."',
destination_directory_object => '".$4."',
destination_file_name => '".$5."');
END;
/
";


Modified:


my $sqlQuery =
"BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => '".$2."',
source_file_name => '".$3."',
source_database => '".$props{'srclink'}."',
destination_directory_object => '".$4."',
destination_file_name => '".$5."');
EXCEPTION
when others then
begin
insert into system.transfer_errors values ('".$2."','".$3."');
commit;
end;
END;
/
";


I.e. I wrote an exception to ignore any error and report the failed transfers in my table system.transfer_errors.

REMARK: The workaround could be more sophisticated by writing an exception handler for ORA-19504 only and write the full error-stack plus destination directories in the table system.transfer_errors:


create table system.transfer_errors (error_code number, error_message varchar2(200), sourcedir varchar2(128), source_file_name VARCHAR2(513), targetdir varchar2(128), target_file_name VARCHAR2(513));


And adjust the code in xttdriver.pl:


my $sqlQuery =
"DECLARE
bug20563128 EXCEPTION;
PRAGMA EXCEPTION_INIT(bug20563128, -19504);
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => '".$2."',
source_file_name => '".$3."',
source_database => '".$props{'srclink'}."',
destination_directory_object => '".$4."',
destination_file_name => '".$5."');
EXCEPTION
when bug20563128 then
begin
insert into system.transfer_errors values (SQLCODE,substr(SQLERRM,1,200),'".$2."','".$3."','".$4."','".$5."');
commit;
end;
END;
/
";


With above code in xttdriver.pl the transfer runs through and I just checked the table system.transfer_errors at the end to see what files I have to transfer manually.
I do hope that the workaround helps others to ease such a Cross Platform Migration.