Saturday, October 20, 2007

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.

2 comments:

yaseen said...

hi famy,

can you explain about tablespace ...
and what are the steps i want to do to take hot back up in the apps database ...

thanks
yaseen.a.r

M A D A N M O H A N said...

Hi Yaseen,

For core Database or Apps Database the Hotbackup process is one and the same.

1. Put the Tablespace in begin backup mode;
2. At O/s Level copy the Datafiles.
3. Put the tablespace in end backup mode.



Example:-

If u want to backup EXAMPLE Tablespace which has one datafile '/data/example01.dbf' , then

1. SQL> alter tablespace EXAMPLE begin backup;

2. $ cp /data/example01.dbf.dbf /prod/hot_backup/example01.dbf

3. SQL> alter tablespace EXAMPLE end backup;

Continue the same for all the tablepsaces.

Cheers,
Madan
http://appsdba4u.blogspot.com