Migrating an Oracle Database From Solaris to Linux
There has been a general move over the last couple of years for a number of our clients to migrate their Oracle environments off Solaris/SPARC and onto Linux/x86, normally Red Hat Linux. The reasons typically given are cost savings and performance improvements. I had assumed that the takeover of Sun by Oracle would slow these migrations down as people waited to see the roadmap from Oracle but so far there's been no indication of that.
The migration process is reasonably well described in the Oracle documentation (Transporting Tablespaces Between Databases), but I'll give a summary of the process here so you can see what's involved, we're using the 'power of RMAN' (I think I may need to trademark that!) to do the heavy lifting.
1: Check that platforms are compatible, run this query on the database you want to migrate from and it will list the platforms you can migrate to:
select * from V$TRANSPORTABLE_PLATFORM;
check the value of ENDIAN_FORMAT, if you are migrating from one format to another you are going to need to convert your database files, so for example going from Solaris to Linux you will need to convert.
2: You can only migrate tablespaces to a database which uses the same character set and national charcter set, so check if you are migrating to an existing database or make sure to use the same values if you are building a new database
3: Assuming you are good to go you need to determine which tablespaces hold the data you want to transport and then double check that the list is self-contained, execute this PL/SQL procedure to check the tablespaces you plan to migrate:
execute DBMS_TTS.TRANSPORT_SET_CHECK('tspace1','tspace2', true);
Then query the output table:
select * from TRANSPORT_SET_VIOLATIONS;
Ideally this will come up with no rows, in which case your list of tablespaces is self-contained and you are ready to proceeed, if not then the quuery results will show problems you have, for example:
VIOLATIONS
--------------------------------------------------------------------------------
Index TESTUSER.INDEX1 in tablespace TSPACE1 points to table TESTUSER.Table2 in tablespace TSPACE3
In this case you could move that table from TSPACE3 into one of the tablespaces you are migrating, include TSPACE3 in your tablespace list, drop or move the index, whichever action is most appropriate. Re-run the PL/SQL and recheck the results - rinse and repeat until you are clean to go:
exec DBMS_TTS.TRANSPORT_SET_CHECK('tspace1','tspace2', true);
select * from TRANSPORT_SET_VIOLATIONS;
no rows selected
4: Generate the transportable tablespace set; you need to export the metadata and then copy the datafiles and if you want the views, PL/SQL etc. for the schemas that are included in the tablespaces then you will need to export those too:
alter tablespace TSPACE1 read only;
alter tablespace TSPACE2 read only;
exp file=tts.dmp transport_tablespace=Y tablespaces=TSPACE1,TSPACE2
cp /data/tspace1_01.dbf /migrate
cp /data/tspace1_02.dbf /migrate
cp /data/tspace2_01.dbf /migrate
cp /data/tspace2_02.dbf /migrate
exp file=testuser.dmp log=testuser.log rows=N owner=TESTUSER
alter tablespace TSPACE1 read write;
alter tablespace TSPACE2 read write;
5: copy the metadata export, datafiles and any schema exports to the new host
6: convert the datafiles to new platform format using RMAN, in this example we will put the converted datafiles into an ASM instance:
rman
connect target /
convert datafile
'/migrate/tspace1_01.dbf',
'/migrate/tspace1_02.dbf',
'/migrate/tspace2_01.dbf',
'/migrate/tspace2_02.dbf'
from platform 'Solaris[tm] OE (64-bit)'
format '+DATA1'
parallelism 4;
7: plug the tablespace into the target database, check the ASM instance to get the names of the converted datafiles:
imp file=tts.dmp log=tts.log transport_tablespace=Y datafiles= \
'+DATA1/db1_server1/datafile/TSPACE1.279.699544583',\
'+DATA1/db1_server1/datafile/TSPACE1.299.699544581',\
'+DATA1/db1_server1/datafile/TSPACE2.300.699544581',\
'+DATA1/db1_server1/datafile/TSPACE2.301.699544581'
8: set the imported tablespaces to read write:
alter tablespace TSPACE1 read write;
alter tablespace TSPACE2 read write;
9: import procs, triggers etc
imp file=testuser.dmp log=testuser.log rows=N ignore=Y full=Y
10: unlock the schema object statistics as they will all be 'locked' after the migration - this caught me out the first time!:
exec DBMS_STATS.UNLOCK_SCHEMA_STATS('testuser');
11: Take a backup of your newly migrated tablespaces
There has been a general move over the last couple of years for a number of our clients to migrate their Oracle environments off Solaris/SPARC and onto Linux/x86, normally Red Hat Linux. The reasons typically given are cost savings and performance improvements. I had assumed that the takeover of Sun by Oracle would slow these migrations down as people waited to see the roadmap from Oracle but so far there's been no indication of that.
The migration process is reasonably well described in the Oracle documentation (Transporting Tablespaces Between Databases), but I'll give a summary of the process here so you can see what's involved, we're using the 'power of RMAN' (I think I may need to trademark that!) to do the heavy lifting.
1: Check that platforms are compatible, run this query on the database you want to migrate from and it will list the platforms you can migrate to:
select * from V$TRANSPORTABLE_PLATFORM;
check the value of ENDIAN_FORMAT, if you are migrating from one format to another you are going to need to convert your database files, so for example going from Solaris to Linux you will need to convert.
2: You can only migrate tablespaces to a database which uses the same character set and national charcter set, so check if you are migrating to an existing database or make sure to use the same values if you are building a new database
3: Assuming you are good to go you need to determine which tablespaces hold the data you want to transport and then double check that the list is self-contained, execute this PL/SQL procedure to check the tablespaces you plan to migrate:
execute DBMS_TTS.TRANSPORT_SET_CHECK('tspace1','tspace2', true);
Then query the output table:
select * from TRANSPORT_SET_VIOLATIONS;
Ideally this will come up with no rows, in which case your list of tablespaces is self-contained and you are ready to proceeed, if not then the quuery results will show problems you have, for example:
VIOLATIONS
--------------------------------------------------------------------------------
Index TESTUSER.INDEX1 in tablespace TSPACE1 points to table TESTUSER.Table2 in tablespace TSPACE3
In this case you could move that table from TSPACE3 into one of the tablespaces you are migrating, include TSPACE3 in your tablespace list, drop or move the index, whichever action is most appropriate. Re-run the PL/SQL and recheck the results - rinse and repeat until you are clean to go:
exec DBMS_TTS.TRANSPORT_SET_CHECK('tspace1','tspace2', true);
select * from TRANSPORT_SET_VIOLATIONS;
no rows selected
4: Generate the transportable tablespace set; you need to export the metadata and then copy the datafiles and if you want the views, PL/SQL etc. for the schemas that are included in the tablespaces then you will need to export those too:
alter tablespace TSPACE1 read only;
alter tablespace TSPACE2 read only;
exp file=tts.dmp transport_tablespace=Y tablespaces=TSPACE1,TSPACE2
cp /data/tspace1_01.dbf /migrate
cp /data/tspace1_02.dbf /migrate
cp /data/tspace2_01.dbf /migrate
cp /data/tspace2_02.dbf /migrate
exp file=testuser.dmp log=testuser.log rows=N owner=TESTUSER
alter tablespace TSPACE1 read write;
alter tablespace TSPACE2 read write;
5: copy the metadata export, datafiles and any schema exports to the new host
6: convert the datafiles to new platform format using RMAN, in this example we will put the converted datafiles into an ASM instance:
rman
connect target /
convert datafile
'/migrate/tspace1_01.dbf',
'/migrate/tspace1_02.dbf',
'/migrate/tspace2_01.dbf',
'/migrate/tspace2_02.dbf'
from platform 'Solaris[tm] OE (64-bit)'
format '+DATA1'
parallelism 4;
7: plug the tablespace into the target database, check the ASM instance to get the names of the converted datafiles:
imp file=tts.dmp log=tts.log transport_tablespace=Y datafiles= \
'+DATA1/db1_server1/datafile/TSPACE1.279.699544583',\
'+DATA1/db1_server1/datafile/TSPACE1.299.699544581',\
'+DATA1/db1_server1/datafile/TSPACE2.300.699544581',\
'+DATA1/db1_server1/datafile/TSPACE2.301.699544581'
8: set the imported tablespaces to read write:
alter tablespace TSPACE1 read write;
alter tablespace TSPACE2 read write;
9: import procs, triggers etc
imp file=testuser.dmp log=testuser.log rows=N ignore=Y full=Y
10: unlock the schema object statistics as they will all be 'locked' after the migration - this caught me out the first time!:
exec DBMS_STATS.UNLOCK_SCHEMA_STATS('testuser');
11: Take a backup of your newly migrated tablespaces
