We are in the middle of the migration of our Oracle 10gr2 databases to 11gr2 but also leaving our old Aix platform to go to readheat linux.
This means also we go from Big Endian format(Aix - Based Systens (64-bit) to Little Endian format on Linux IA (64-bit). We did them all with the export import method but now with the larger databases we want to use convert datafile rman command fo migrating the database.
Here are the steps how we did this for our Simcorp database.
To find out witch endian your platform has you use the following query
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
20 Solaris Operating System (x86-64) Little
19 HP IA Open VMS Little
As we are on Aix Based system Big endian and Linux IA is on Litle endian.
First a ran my script that create the script we use for the convert.
set feedback off trimspool off verify off
set head off
set lines 100
set pages 0
spool convert_dt.rcv
prompt run
prompt {
select 'CONVERT DATAFILE' from dual;
select case when rownum!=e.aantal then ''''||d.file_name||''''||','
when rownum=e.aantal then ''''||d.file_name||''''||'' end
from (select count(*) aantal from dba_tablespaces f,dba_data_files g
where f.tablespace_name = g.tablespace_name
and f.tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and f.contents = 'PERMANENT'
order by f.tablespace_name)
,(select substr(upper(file_name),instr(file_name,'/',-1)+1) file_name
from dba_tablespaces a, dba_data_files b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and contents = 'PERMANENT'
order by a.tablespace_name) d,(select count(*) aantal from dba_tablespaces f,dba_data_files g
where f.tablespace_name = g.tablespace_name
and f.tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and f.contents = 'PERMANENT'
order by f.tablespace_name) e
/
prompt FROM PLATFORM 'AIX-Based Systems (64-bit)'
prompt PARALLELISM 4
prompt FORMAT '+DATA/'
select ';' from dual;
prompt }
spool off
set head off
set feedb off
set pages 0
set lines 80
set verify off
set timing off
spool source_exp.par
prompt directory=data_pump_dir
prompt dumpfile=exp_db.dmp
prompt transport_full_check=no
select 'transport_tablespaces=' from dual
/
select case when rownum!=b.aantal then tablespace_name||','
when rownum=b.aantal then tablespace_name||'' end
from dba_tablespaces a,(select count(*) aantal from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and contents = 'PERMANENT'
order by tablespace_name) b
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and contents = 'PERMANENT'
order by tablespace_name
/
prompt log=exp_db.log
spool off
spool target_import.par
prompt directory=data_pump_dir
prompt dumpfile=exp_db.dmp
prompt logfile=import_db.log
select 'transport_datafiles=' from dual
/
select case when rownum!=b.aantal then ''''||file_name||''''||','
when rownum=b.aantal then ''''||file_name||''''||'' end
from dba_data_files a,(select count(*) aantal from dba_data_files
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS','TEMP','UNDO')
order by tablespace_name) b
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS','TEMP','UNDO')
order by tablespace_name
/
spool off
spool ro_tbs.sql
select 'alter tablespace '||tablespace_name||' read only;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and contents = 'PERMANENT'
group by tablespace_name
/
spool off
spool rw_tbs.sql
select 'alter tablespace '||tablespace_name||' read write;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and contents = 'PERMANENT'
group by tablespace_name
/
spool off
set heading off feedback off trimspool on linesize 140 pages 0
spool drop_tbs.sql
select 'DROP TABLESPACE '||tablespace_name ||' INCLUDING CONTENTS AND DATAFILES;'
from dba_tablespaces
where tablespace_name not in ('SYSTEM','SYSAUX','TOOLS','USERS')
and contents = 'PERMANENT';
spool off
col USERDDL format A150
set pages 0
spool sys_privs.sql
select 'grant '||privilege||' on '||table_name||' to '||grantee||' '||
decode(grantable,'YES',' with grant option ')||
decode(hierarchy,'YES',' with hierarchy option ')||
';'
from dba_tab_privs
where owner in
(select name
from system.logstdby$skip_support
where action=0)
and grantee in
(select username
from dba_users
where username not in
(select name
from system.logstdby$skip_support
where action=0) )
/
spool off
This create's the following scripts.
@ro_tbs.sql # script to set tablespace on source database in read only mode
@rw_tbs.sql #script to set tablespace on target database in read write mode
@sys_priv.sql #script to grant sys privileges from source database on target database
@sour_exp.par # metadata parfile for tablespace from source database
@target_imp.par # metadata parfile to plugin tablespace in target database
@drop_tbs.sql # drop user tablespaces in target database
In our case were going to migrate Simcorp database SCD401P1 on Aix to Linux SC451P1.
make sure the characterset of both databases are th same
SQL> select * from database_properties where property_name like '%CHARACTERSET';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------------
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set
NLS_CHARACTERSET WE8MSWIN1252 Character set
The Target database SC451P1 is created with only the default tablespaces.sysaux,system,tools,users,undo and temp.
First we create a database link in the target database SCD451P1.
under user system.
SQL> CREATE DATABASE LINK TSS_LINK USING 'SCD401P1';
Don't forget to put the source database SCD401P1 in the tnsnames.ora on the server where the target database SCD451P1 is running.
On the target database SCS451P1 we create the tablespaces were going to convert later from the source database. We only do this so that the impdp of user,role and profiles runs without errors. Later on we drop those tablespaces again. this are the tablespaces
FBT4_D
FBT4_I
SCAUDIT
SCAUDIX
SCDAT
SCINDX
SCQUEUE
SCSYS
SCTRDARC
SCTRDDAT
SCTRDIDX
SCUSERS
Now we import the user,profiles roles roles_grant from source SCD401P1to SCD451P1
We do this on the target server
$ impdp system DIRECTORY=data_pump_dir LOGFILE=user_imp.log NETWORK_LINK=tss_link FULL=y INCLUDE=PROFILE,USER,ROLE,ROLE_GRANT
After that al users,profiles,roles and role_grant exsist in the target database.
Now we run export the metadata from the source database SCD401P1 on the source server
$ expdp system DIRECTORY=data_pump_dir \
LOGFILE=full_exp_meta.log \
DUMPFILE=full_exp_meta.dmp FULL=y \
CONTENT=METADATA_ONLY \
EXCLUDE=USER,ROLE,ROLE_GRANT,PROFILE \
PARALLEL=6
Shutdown the application if needed.
Set tablespace on source database SCD401P1 read only.
SQL>@ro_tbs.sql
export user tablespaceses source database SCD401P1
We use the parfile source_exp.par
cat source_exp.par
directory=data_pump_dir
dumpfile=exp_db.dmp
transport_full_check=no
transport_tablespaces=
FBT4_D,
FBT4_I,
SCAUDIT,
SCAUDIX,
SCDAT,
SCINDX,
SCQUEUE
SCSYS,
SCTRDARC,
SCTRDDAT,
SCTRDIDX,
SCUSERS,
log=exp_db.log
$ expdp system parfile=source_exp.par
Now drop the user tablespaces on the target database SCD451P1 we created for the import for the users,roles ...
Don't forget on the server where the target database SCD451P1 runs
SQL>drop_tbs.sql
Now we can run the convert script.
First we created an nfs mount for the filesystem were the datafiles are. In our case it was /oracle/SCD401P/DATA_01 .This is so on the target server(LINUX) we can access the
datafiles. In that way we can run rman from the target server.
The convert_dt.rcv looks like this
cat convert_dt
run
{
CONVERT DATAFILE
'FBT_D_01.DBF',
'FBT4_I_01.DBF',
'SCAUDIT_02.DBF',
'SCAUDIT_01.DBF',
'SCAUDIX_02.DBF',
'SCAUDIX_01.DBF',
'SCDAT_04.DBF',
'SCDAT_02.DBF',
'SCDAT_03.DBF',
'SCDAT_14.DBF',
'SCDAT_01.DBF',
'SCINDX_01.DBF',
'SCINDX_02.DBF',
'SCINDX_03.DBF',
'SCQUEUE_01.DBF',
'SCSYS_01.DBF',
'SCTRDARC.DBF',
'SCTRDDAT.DBF',
'SCTRDIDX.DBF',
'SCUSERS_01.DBF'
FROM PLATFORM 'AIX-Based Systems (64-bit)'
PARALLELISM 4
FORMAT '+DATA/'
;
}
As we start the rman script on linux the platform is from which is comes AIX in our case.
The datafile will be set on a ASM in the diskgroup DATA.
start the script
RMAN> connect target /
RMAN>@convert_dt.rcv
When it finished succesfully then we must import(plugin) all user tablespaces into the target database SCD451P1.
You have to manualy adjust the location of the datafiles(Sorry if i had thime enough i would write a script for this)as where going from filesystem to ASM.
$ vi target_import.par
directory=data_pump_dir
dumpfile=exp_db.dmp
logfile=import_db.log
transport_datafiles='+DATA/upg401p/datafile/ars4_arcm_d.265.791808391',
'+DATA/scd451p/datafile/ars4_arcm_i.257.791808377',
'+DATA/scd451p/datafile/ars4_arcm_l.264.791808399',
'+DATA/scd451p/datafile/ars4_bp_d.262.791808403',
'+DATA/scd451p/datafile/ars4_bp_i.261.791808405',
'+DATA/scd451p/datafile/ars4_d.258.791808311',
'+DATA/scd451p/datafile/ars4_i.259.791808227',
'+DATA/scd451p/datafile/ars4_l.260.791808409'
start the import
$ impdp gda001 parfile=target_import.par
set tablespaces on target database SCD451P1 read,write
SQL>@rw_tbs.sql
Now import the remaing metadata from the source database
into the target database
$ impdp gda001 DIRECTORY=data_pump_dir LOGFILE=full_imp.log DUMPFILE=full_exp_meta.dmp FULL=y PARALLEL=6
There you've it. Database is converted from aix to linux. and from 10.2.0.4 to 11.2.0.3
Only 1 thing to do
compile invalid objects.
SQL>@?/rdbms/admin/utlrp.sql
