Friday, October 5, 2012

Convert Oracle Database from Aix to Linux

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

2 comments:

  1. Good one mate.
    We have a requirement on similar lines.. can you please help on how to proceed?

    Oracle database; 11.2.0.2
    Source: IBM AIX 6.1
    Target: RHEL 7.x

    What is the best way forward?

    Thanks in advance

    ReplyDelete
  2. Good one mate.
    We have a requirement on similar lines.. can you please help on how to proceed?

    Oracle database; 11.2.0.2
    Source: IBM AIX 6.1
    Target: RHEL 7.x

    What is the best way forward?

    Thanks in advance

    ReplyDelete