How to Move a Oracle Database to a Different Platform

Print this post

Export/Import is the only supported method of moving an existing Oracle database from

one hardware platform to another. This is due to the incompatible file structures between

Operating Systems. The only option is to take a logical backup of the database, hence export/import.

The general procedure is as follows:

1.  Gather the necessary information from your source database.  You will need to know the exact name of all TABLESPACES, 

     so perform the following query as a DBA user:

        SQL>  SELECT tablespace_name FROM dba_tablespaces; 

    Or you can use the script given below to generate create tablespace script

---------------------------------------------------------------------------------------------------

set verify off; 
set termout off; 
set feedback off; 
set pagesize 0; 
  
set termout on; 
select 'Creating tablespace build script...' from dual; 
set termout off; 
  
create table ts_temp (lineno number, ts_name varchar2(30), 
                    text varchar2(800)); 
  
DECLARE 
   CURSOR ts_cursor IS select   tablespace_name, 
                             initial_extent, 
                                next_extent, 
                                min_extents, 
       max_extents, 
                                pct_increase, 
                  status 
                        from    sys.dba_tablespaces 
                     where tablespace_name != 'SYSTEM' 
                        and status != 'INVALID' 
                        order by tablespace_name; 
   CURSOR df_cursor (c_ts VARCHAR2) IS select   file_name, 
                    bytes 
                                       from     sys.dba_data_files 
                                       where    tablespace_name = c_ts 
                                         and    tablespace_name != 'SYSTEM' 
                                       order by file_name; 
   lv_tablespace_name   sys.dba_tablespaces.tablespace_name%TYPE; 
   lv_initial_extent    sys.dba_tablespaces.initial_extent%TYPE; 
   lv_next_extent       sys.dba_tablespaces.next_extent%TYPE; 
   lv_min_extents       sys.dba_tablespaces.min_extents%TYPE; 
   lv_max_extents       sys.dba_tablespaces.max_extents%TYPE; 
   lv_pct_increase      sys.dba_tablespaces.pct_increase%TYPE; 
   lv_status            sys.dba_tablespaces.status%TYPE; 
   lv_file_name         sys.dba_data_files.file_name%TYPE; 
   lv_bytes             sys.dba_data_files.bytes%TYPE; 
   lv_first_rec         BOOLEAN; 
   lv_string            VARCHAR2(800); 
   lv_lineno            number := 0; 
  
   procedure write_out(p_line INTEGER, p_name VARCHAR2,  
             p_string VARCHAR2) is 
   begin 
     insert into ts_temp (lineno, ts_name, text) values  
            (p_line, p_name, p_string); 
   end; 
  
BEGIN 
   OPEN ts_cursor; 
   LOOP 
      FETCH ts_cursor INTO lv_tablespace_name, 
                           lv_initial_extent, 
                           lv_next_extent, 
     lv_min_extents, 
                           lv_max_extents, 
           lv_pct_increase, 
                           lv_status; 
      EXIT WHEN ts_cursor%NOTFOUND; 
      lv_lineno := 1; 
      lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name)); 
      lv_first_rec := TRUE; 
      write_out(lv_lineno, lv_tablespace_name, lv_string); 
      OPEN df_cursor(lv_tablespace_name); 
      LOOP 
         FETCH df_cursor INTO lv_file_name, 
        lv_bytes; 
         EXIT WHEN df_cursor%NOTFOUND; 
         if (lv_first_rec) then 
            lv_first_rec := FALSE; 
            lv_string := 'DATAFILE '; 
         else 
            lv_string := lv_string || ','; 
         end if; 
     lv_string:=lv_string||''''||lv_file_name||''''|| 
                    ' SIZE '||to_char(lv_bytes) || ' REUSE'; 
      END LOOP; 
      CLOSE df_cursor; 
   lv_lineno := lv_lineno + 1; 
         write_out(lv_lineno, lv_tablespace_name, lv_string); 
         lv_lineno := lv_lineno + 1; 
         lv_string := (' DEFAULT STORAGE (INITIAL ' || 
                      to_char(lv_initial_extent) || 
                   ' NEXT ' || lv_next_extent); 
         write_out(lv_lineno, lv_tablespace_name, lv_string); 
         lv_lineno := lv_lineno + 1; 
         lv_string := (' MINEXTENTS ' || 
                      lv_min_extents || 
          ' MAXEXTENTS ' || lv_max_extents); 
         write_out(lv_lineno, lv_tablespace_name, lv_string); 
         lv_lineno := lv_lineno + 1; 
         lv_string := (' PCTINCREASE ' || 
                      lv_pct_increase || ')'); 
  write_out(lv_lineno, lv_tablespace_name, lv_string); 
         lv_string := ('   '||lv_status); 
         write_out(lv_lineno, lv_tablespace_name, lv_string); 
         lv_lineno := lv_lineno + 1; 
         lv_string:='/'; 
         write_out(lv_lineno, lv_tablespace_name, lv_string); 
         lv_lineno := lv_lineno + 1; 
         lv_string:='                                                  '; 
   write_out(lv_lineno, lv_tablespace_name, lv_string); 
   END LOOP; 
   CLOSE ts_cursor; 
END; 
/ 
  
spool create_tablespaces.sql 
set heading off 
set recsep off 
col text format a80 word_wrap 
  
  
select   text 
from     ts_temp 
order by ts_name, lineno; 
  
spool off; 
  
drop table ts_temp;

-------------------------------------------------------------------------------------------------

2.  Perform a full export from the source database as a DBA user.  
    For example:

        exp system/manager full=y file=expdat.dmp log=expdat.log

3.  Move the dump file to the target database server via ftp.   (Note:  remember to change to BINARY mode to avoid 

     corrupting the file.  To configure ftp for binary mode, enter 'binary' at the ftp prompt)

4.  Create a database on the target server. 

5.  Before performing the import, you will need to precreate your tablespaces. This is necessary since the import will 

    want to create the corresponding datafiles in the same file structure as was at the source database.  Since your file 

    structure will be different on the target database, precreating the tablespaces will allow you to specify a file structure 

    that will work.
    
6.  Perform a full import with the parameter IGNORE=Y into the target database as a DBA user.  

        imp system/manager full=y ignore=y file=expdat.dmp log=expdat.log

    Using IGNORE=Y will tell Oracle to ignore any creation errors during the import, allowing the import to complete.