Step by Step Guide on converting a database from Windows to AIX

郑全

Step by Step Guide on converting a database from Windows to AIX (Doc ID 1373780.1)  
In this Document
Goal
Solution
  1 Prerequisits
  2 Procedure overview
  2.1 Prepare and collect information on the Source database
  2.1.2 Check if the tablespaces to transport can be made
  2.1.2 Generate a script to create the related users
  2.2 Export the data
  2.2.1 Prepare for the export
  2.2.2 Export the data
  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
  6.1 Prepare for import
  6.2 Import the datafiles
  6.3 Post import steps
References
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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/technetwor ... ationtts-129269.pdf  
     "Platform Migration Using Transportable Tablespaces: Oracle Database 11g"
Solution

parfile: expdp_full_norows.par
directory=tts_exp
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
parfile: expdp_tts.par
directory=tts_exp
dumpfile=expdp_tts.dmp
logfile=expdp_tts.log
transport_tablespaces=(USERS, EXAMPLE)
- Destination server location:<path>/<tts_copy>
parfile: impdp_tts.par
directory=tts_imp
dumpfile=EXPDP_TTS.DMP
logfile=impdp_tts.log
transport_datafiles=(/<path>/tts_convert/example01.dbf, /<path>/tts_convert/users01.dbf)
parfile: impdp_full_norows.par
directory=tts_imp
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
1 Prerequisits
When a database needs to be transferred from Windows to AIX or any other platform, the key thing to check, is the endianess of the both hosts.
SQL> col platform_name format a40
     select * from v$transportable_platform order by platform_name;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------
          6 AIX-Based Systems (64-bit)               Big
         16 Apple Mac OS                             Big
         21 Apple Mac OS (x86-64)                    Little
...
          7 Microsoft Windows IA (32-bit)            Little
          8 Microsoft Windows IA (64-bit)            Little
         12 Microsoft Windows x86 64-bit             Little
         17 Solaris Operating System (x86)           Little
         20 Solaris Operating System (x86-64)        Little
          1 Solaris[tm] OE (32-bit)                  Big
          2 Solaris[tm] OE (64-bit)                  Big
SQL> select platform_id, platform_name from v$database;
PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------------
7 Microsoft Windows IA (32-bit)
PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------------
          6 AIX-Based Systems (64-bit)

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
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.

Special care needs to be take for the SYSAUX tablespace as it cannot be dropped from a database.
no data will be exported out of objects residing in SYSAUX, which might result in creation of empty objects in
new tablespace (these objects are exported as part of step full export norows, excluding of SYSAUX only is not possible).
A check should be done to get clear which schema's/applications do have segments in SYSAUX after which a plan can be
made if data needs to be extracted out of SYSAUX and if true how (for example: APEX has it's own exporting utility, RMAN could be done by a user export, ...)

This document will describe how to convert a database from Windows to AIX.
2 Procedure overview

The highlevel procedure is :
    Prepare and collect the information of the Source (=Windows) database
    Create export dumps of metadata, including Transportable Tablespaces
    Create an new database on the Destination (=AIX)
    Copy the export-dump and datafiles over to the Destination host
    Convert the datafiles
    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=<oracle_home>\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 <username>@<destination_host>:/<path>/tts_copy
        scp \<path>\*.DMP <username>@<destination_host>:/<path>/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 '/<path>/tts_copy/EXAMPLE01.DBF'
       FROM PLATFORM 'Microsoft Windows IA (32-bit)'
       FORMAT '/<path>/tts_convert/example01.dbf';
       CONVERT DATAFILE '/<path>/tts_copy/USERS01.DBF'
       FROM PLATFORM 'Microsoft Windows IA (32-bit)'
       FORMAT '/<path>/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 <usernamae> identified by <password>;
         create user <usernamae> identified by <password>;
         create user <usernamae> identified by <password>;
         create user <usernamae> identified by <password>;
         create user <usernamae> identified by <password>;
         create user <usernamae> identified by <password>;
    Create an RDBMS Directory where the export dumps are located, as in this exampe '/<path>/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=(/<path>/tts_convert/example01.dbf, /<path>/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.
    In this example, the default tablespace for the users is 'USERS' tablespace and default temporary tablespace name is 'TEMP'
    script : tts_exp_users_alter.sql
    SQL> alter user <username> default tablespace USERS temporary tablespace TEMP;
         alter user <username> default tablespace USERS temporary tablespace TEMP;
         alter user <username> default tablespace USERS temporary tablespace TEMP;
         alter user <username> default tablespace USERS temporary tablespace TEMP;
         alter user <username> default tablespace USERS temporary tablespace TEMP;
         alter user <username> 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