In this Document
Goal
Solution
1 Prerequisits
2 Procedure overview
3 Create the new database on the Destination host
4 Copy the datafile, export dumps and SQL-script. to the
5 Convert the datafiles on Destination host
6 Import the dumps on the Destination host
References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 and later [Release: 10.1 and later ]
Information in this document applies to any platform.

Goal

Step by Step example of converting a database on a Windows host to an AIX host

See also :
http://www.oracle.com/technetwork/database/features/availability/maa-wp-11g-platformmigrationtts-129269.pdf "Platform. Migration Using Transportable Tablespaces: Oracle Database 11g"

Solution

1 Prerequisits

When a database needs to be transfered from Windows to AIX or any other platform, the key thing to check, is the endianess of the both hosts.


If the endianess is the same, the CONVERT DATABASE can be used, but as they are different in this case,
you have to use Transporttable Tablespaces and RMAN CONVERT DATAFILE

Documents relate to CONVERT DATABASE are :
Note 1401921.1 Cross-Platform. Database Migration (across same endian) using RMAN Transportable Database
Note 413586.1 How To Use RMAN CONVERT DATABASE on Source Host for Cross Platform. Migration
Again this can ONLY be used if the Endianess of the related systems are the same. eq. Windows Linux, or AIX Solaris

Due to the difference in endianess, the SYSTEM and UNDO-tablespaces can NOT be converted to the new platform.
So only the none-system tablespaces are involved and need to be transfered and converted.




This document will decribe how to convert a database from Windows to AIX.

2 Procedure overview

The highlevel procedure is :

1. Prepare and collect the information of the Source (=Windows) database
2. Create export dumps of metadata, including Transportable Tablespaces
   
3. Create an new database on the Destination (=AIX)
4. Copy the export-dump and datafiles over to the Destination host
5. Convert the datafiles
   
6. Import the dumps into the new database on the Destination host.

2.1 Prepare and collect information on the Source database

The related tablespaces which need to be converted are :

SQL> select tablespace_name
from dba_tablespaces
where tablespace_name not in ('SYSTEM', 'SYSAUX')
and contents not in ('UNDO', 'TEMPORARY');

2.1.2 Check if the tablespaces to transport can be made

A transportable Tablespace can only succeed if the 'recovery set' is selfcontained. Meaning that there can not be objects inside those tablespaces, which are referencing objects outside this set.
eq. An index has been created in the SYSTEM-tablespace instead of the normal index-tablespace. As the SYSTEM-tablespace is NOT involved in this procedure, the Transportable Tablespace will fail.
The index needs to be moved to the correct tablespace.

Precreate the TRANSPORT_SET_VIOLATIONS using the following command. It will report an error, but WILL create the TRANSPORT_SET_VIOLATIONS.

SQL> exec dbms_tts.transport_set_check('non_existing_tablespace',TRUE);

Create and execute a PL/SQL procedure which will check if the tablespaces in the recovery-set is selfcontained.

C:> sqlplus "/ as sysdba"
SQL> set serveroutput on size 1000000
declare
cursor c_TRANSPORT_SET_VIOLATIONS is select violations from TRANSPORT_SET_VIOLATIONS;
tablespace_names varchar2(4096);
type tslist is table of dba_tablespaces.tablespace_name%type;
ts tslist;
type cursor_ref is ref cursor;
ts_cur cursor_ref;
begin
dbms_output.put_line('Starting to check tablespaces as specified');

open ts_cur for 'select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY'')';
fetch ts_cur bulk collect into ts;
close ts_cur;

tablespace_names :='';
for i in ts.first .. ts.last
loop
if ( i = ts.first)
then
tablespace_names := ts(i);
else
tablespace_names := tablespace_names || ', ' || ts(i);
end if;
end loop;

dbms_output.put_line(tablespace_names);
DBMS_TTS.TRANSPORT_SET_CHECK(tablespace_names, TRUE, TRUE);

for c_cur in c_TRANSPORT_SET_VIOLATIONS loop
dbms_output.put_line(c_cur.violations);
end loop;

dbms_output.put_line('In case there are no line(s) after '||chr(39)||'Starting to check ...'||chr(39));
dbms_output.put_line('It does imply that the check went fine and there are no issues to resolve.');
end;
/

2.1.2 Generate a script. to create the related users

Generate a script. to create the users who own objects in the exported tablespaces

C:> sqlplus "/ as sysdba"
SQL> set serveroutput on size 1000000
declare
string varchar2(4096);
type userlist is table of dba_users.username%type;
users userlist;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in (select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY''))';
fetch c_cur bulk collect into users;
close c_cur;

for i in users.first .. users.last
loop
dbms_output.put_line('create user '||users(i)||' identified by '||users(i)||';');
end loop;
end;
/

set feedback off
spool tts_exp_users_create.sql
/
spool off;

Generate a script. with the user-default tablespaces

C:> sqlplus "/ as sysdba"
SQL>REM tts_ts_users
set serveroutput on size 1000000
declare
string varchar2(4096);
type userlist is table of dba_users.username%type;
users userlist;

type cursor_ref is ref cursor;
c_cur cursor_ref;

def_ts dba_users.default_tablespace%type;
temp_ts dba_users.temporary_tablespace%type;

begin
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in (select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY''))';
fetch c_cur bulk collect into users;
close c_cur;

for i in users.first .. users.last
loop
select default_tablespace,temporary_tablespace into def_ts,temp_ts
from dba_users where username=users(i);
dbms_output.put_line('alter user '||users(i)||' default tablespace '||def_ts||' temporary tablespace '||temp_ts||';');
end loop;
end;
/

set feedback off
spool tts_exp_users_alter.sql
/
spool off

2.2 Export the data

2.2.1 Prepare for the export

Create an RDBMS Directory pointing to an OS-directory, where the export-dumps will be stored, eq C:\TEMP
and get the database characterset.

SQL> conn / as sysdba
create directory tts_exp as '&directory_name';

SQL> column parameter format a30;
column value format a30;
select * from nls_database_parameters where parameter like '%CHARACTERSET%';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

Set the correct NLS_LANG depending on the previous values :

C:\>set nls
Environment variable nls not defined

C:\>set ORA_NLS10=\nls\data
set NLS_LANG=".WE8MSWIN1252"

2.2.2 Export the data

Make an export with ROWS=NO, in order to recreate all objects/schemes/grants/.. which are not covered by the TTS export

expdp_full_norows.par:

userid="/ as sysdba"
directory=tts_exp
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
full=y
content=metadata_only

C:> expdp parfile=expdp_full_norows.par

Put the related tablespaces in READ ONLY mode per requirement for the Transportable Tablespace export

C:> sqlplus "/ as sysdba"
SQL> REM tts_readonly
set serveroutput on size 1000000
declare
string varchar2(4096);
ts_fail integer:=0;

type tablespacetyp is table of dba_tablespaces%rowtype;
tslist tablespacetyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
/*
1st check if one of the tablespaces is already in read only mode, if true procedure will fail
due to fact that we cannot discriminate if the read only was a failure of an earlier run of this script
OR that it is really a read only tablespace.
*/

open c_cur for 'select * from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY'')';
fetch c_cur bulk collect into tslist;
close c_cur;

for i in tslist.first .. tslist.last
loop
if tslist(i).status!='ONLINE'
then
dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||
' can NOT be put in read only mode, current status '||
tslist(i).status);
ts_fail:=ts_fail+1;
end if;
end loop;

if ts_fail!=0
then
dbms_output.put_line('Errors have been found while check if tablespace(s) can be put in read only mode');
return;
end if;

for i in tslist.first .. tslist.last
loop
execute immediate 'alter tablespace '||tslist(i).tablespace_name ||' read only';
dbms_output.put_line('Tablespace ' || tslist(i).tablespace_name ||' read only');
end loop;

end;
/

Get the related datafiles for the tablespaces.

SQL> REM tts_show_datafiles
set serveroutput on size 1000000
declare
type datafiletyp is table of dba_data_files%rowtype;
filelist datafiletyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
open c_cur for 'select * from dba_data_files
where tablespace_name in (select tablespace_name from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY''))
order by tablespace_name,file_id';
fetch c_cur bulk collect into filelist;
close c_cur;

for i in filelist.first .. filelist.last
loop
dbms_output.put_line('Tablespace: '||filelist(i).tablespace_name||' File: '||filelist(i).file_name);
end loop;
end;
/

Export the tablespaces using Transportable Tablespace feature

expdp_tts.par :

userid="/ as sysdba"
directory=tts_exp
dumpfile=expdp_tts.dmp
logfile=expdp_tts.log
transport_full_check=y
transport_tablespaces=(USERS, EXAMPLE)

C:> expdp parfile=expdp_tts.par

3 Create the new database on the Destination host

The new database can be created using SQL*Plus of DBCA. The actual creation of this database is outside of the scope of this document.
The characterset of the database needs to be the same as the Source database or a superset of the 'source' characterset. (step 2.1.2)
The new database only needs a SYSTEM, SYSAUX, UNDO and TEMP-tablespace.
Anyother tablespace might block the import as the imported tablespace cannot exist in the new database

4 Copy the datafile, export dumps and SQL-script. to the

Copy datafiles and export dumps to the Destination-server, using an OS-utility like 'ftp', 'sftp', 'scp'
Besure to copy each datafile which is shown in the output of the procedure 'tts_show_datafiles()' from step 2.2.2

Shutdown the database
C:> sqlplus "/ as sysdba"
SQL> shutdown immediate

C:> scp *.DBF oracle@:/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_copy

scp C:\TEMP\ *.DMP oracle@:/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_copy

5 Convert the datafiles on Destination host

Convert the datafiles from the Windows format to the AIX format. Execute this on the Destination host.
Execute this for each datafile which is shown in the output of the procedure 'tts_show_datafiles()' from step 2.2.2

% rman target /
RMAN> RUN {

CONVERT DATAFILE '/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_copy/EXAMPLE01.DBF'
FROM PLATFORM. 'Microsoft Windows IA (32-bit)'
FORMAT '/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_convert/example01.dbf';

CONVERT DATAFILE '/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_copy/USERS01.DBF'
FROM PLATFORM. 'Microsoft Windows IA (32-bit)'
FORMAT '/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_convert/users01.dbf';
}

6 Import the dumps on the Destination host

6.1 Prepare for import

Precreate the users, using the generated script. tts_exp_users_create.sql (step 2.1.2)

script. tts_exp_users_create.sql

SQL> create user HR identified by HR;
create user SCOTT identified by SCOTT;
create user OE identified by OE;
create user SH identified by SH;
create user PM identified by PM;
create user IX identified by IX;

Create an RDBMS Directory where the export dumps are located, as in this exampe '/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_copy'

SQL> connect / as sysdba
create directory tts_imp as '&directory_name';

6.2 Import the datafiles

Import the datafile and metadata using the Transportable Tablespace feature.
The datafiles are the converted datafiles

impdp_tts.par :

userid="/ as sysdba"
directory=tts_imp
dumpfile=EXPDP_TTS.DMP
logfile=impdp_tts.log
transport_datafiles=(/grdbms/64bit/app/oracle/oradata/sb22Sb/tts_convert/example01.dbf, /grdbms/64bit/app/oracle/oradata/sb22Sb/tts_convert/users01.dbf)

% impdp parfile=impdp_tts.par

6.3 Post import steps

Alter the related users, to have the correct default and temporary tablespace again, using the generated script. from step 2.1.2

script. tts_exp_users_alter.sql

SQL> alter user HR default tablespace USERS temporary tablespace TEMP;
alter user SCOTT default tablespace USERS temporary tablespace TEMP;
alter user OE default tablespace USERS temporary tablespace TEMP;
alter user SH default tablespace USERS temporary tablespace TEMP;
alter user PM default tablespace USERS temporary tablespace TEMP;
alter user IX default tablespace USERS temporary tablespace TEMP;

Put the tablespaces in READ WRITE again :

SQL> set serveroutput on size 1000000
set serveroutput on size 1000000
declare
ts_fail integer:=0;

type tablespacetyp is table of dba_tablespaces%rowtype;
tslist tablespacetyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
open c_cur for 'select * from dba_tablespaces
where tablespace_name not in (''SYSTEM'', ''SYSAUX'')
and contents not in (''UNDO'', ''TEMPORARY'')';

fetch c_cur bulk collect into tslist;
close c_cur;

for i in tslist.first .. tslist.last
loop
if tslist(i).status!='READ ONLY'
then
dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' can NOT be put in read write mode, current status '||tslist(i).status);
ts_fail:=ts_fail+1;
end if;
end loop;

if ts_fail!=0
then
dbms_output.put_line('Errors have been found while check if tablespace(s) can be put in read write mode');
return;
end if;

for i in tslist.first .. tslist.last
loop
execute immediate 'alter tablespace '||tslist(i).tablespace_name||' read write';
dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' put in read write mode');
end loop;

end;
/

Import all the related objects which were not imported by the TTS, like grants etc.

impdp_full_norows.par:

userid="/ as sysdba"
directory=tts_imp
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
full=y
content=metadata_only
table_exists_action=skip

% impdp parfile=impdp_full_norows.par

Recompile all invalid objects

SQL> connect / as sysdba
@?/rdbms/admin/utlrp.sql

References

NOTE:733824.1 - How To Recreate a database using TTS (Transportable TableSpace)