random technical thoughts from the Nominet technical team

Migrating an Oracle database Solaris to Linux

1 Star2 Stars3 Stars4 Stars5 Stars (5 votes, average: 4.8 out of 5)
Loading ... Loading ...
Posted by jason on Jan 18th, 2006

We have been looking at ways of migrating our database from it’s current home on Solaris to Linux (64-bit AMD). This migration involves two interesting changes, first off we have to change endianness due to the move from sparc archictecture to AMD, but as we are also using RAC we are moving from a clustered filesystem (Vxfs) to Oracle’s very own ASM. In no way is this a one step migration. Here is the method we are thinking of using:

Create your target database on the new system.

In your original database first find out what destination platforms you can use transportable tablespaces to:

sql> select * from V$transportable_platform;

Find out if the tablespace(replace TBNAME with the tablespace your are interested in) to move is self-contained:

sql> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBNAME', TRUE);
select * from TRANSPORT_SET_VIOLATIONS;

If there are any rows in the the output from the query, you must include the tablespaces mentioned in the transportable set.

Make all tablespaces you want to move read only:

sql> alter tablespace TBNAME read only;

Run rman to convert the datafile(s) of the tablespace you are interested in:

rman> convert tablespace TBNAME to platform 'Linux 64-bit for AMD'
format='/home/oracle/%U' parallelism 4;

This rman command goes and changes the endianness of the data. Now create the transportable tablespace metadata you can use data pump or good old fashioned imp/exp:

(oracle $) exp 'as sys user' transport_tablespace=y
file=tbname.dmp tablespaces=tbname

Now copy the output from rman and the export over to your new target database server and we can use imp to plug the tablespace back in. You must create the owner of the tablespace on the target database. You CAN’T plug straight into ASM you must go to a filesystem first! So if you are using RAC, you have to make it non-clustered and import on 1 side only:

(oracle $) imp 'as sys user' transport_tablespace=y
file='path to export dump' datafiles='path to converted datafiles'

Now you have to use rman again to convert your datafile into ASM:

rman> convert datafile 'path_to_datafile'
format 'ASM path' parallelism 4;

Finally offline the tablespace and rename the datafile in the database:

sql> alter tablespace TBNAME rename datafile 'path to os datafile'
to 'path to ASM file'

So do this with all your datafiles and you have migrated your database.

13 Responses

  1. Sharon Says:

    Excellent article Jason. This really helped me. Cheers

  2. Doug Says:

    The second “convert” mentioned in your steps does nothing more than copy the file into ASM sine there is no PLATFORM specified. This is used inplace of BACKUP AS COPY to avoid the file being registered as an available backup in the RMAN catalog.

    The endian conversion can be done on the target system, which would eliminate moving the datafiles once. The steps currently move the datafiles 3 times: vxfs /home/oracle /stage ASM.

    If instead you do this: vxfs /stage ASM, it will make the process faster. You have to use convert datafile and list all datafiles in the tablespaces being transported. The RMAN convert command would be like this:
    convert datafile ‘/stage/dbf1′,’/stage/dbf2′ from platform ‘Solaris[tm] OE (64-bit)’ db_file_name_convert ‘/stage/’,'+DATA’

  3. Michael Says:

    Does the RMAN convert command change the data in place on the source? Or does it make a copy with changed endian-ness? Also, does the database need to be in archivelog mode for this method to work?

  4. jason arneil Says:

    hello,

    it makes a copy to the location specified by format=’/home/oracle/%U’

    so in that example creates a backup with changed endianness in the /home/oracle location

    i don’t see why archivelog mode is required as you are forced to make the tablespace you want to change read only so there will be no updates on it

    jason.

  5. Michael Says:

    Well, I’ve been playing with this. I have a database with literally thousands of datafiles. The copies made by rman end up with crazy names like data_D-MDB_I-1108627998_TS-PARTITIONS_FNO-14_0aiqhpq1

    Renaming them in the target is more difficult than it sounds.

    What a tedious undertaking, this xplatform tts…

  6. jason arneil Says:

    hello,

    You don’t have to stick with %U on the end of the format command. If you use %N instead it should put the tablespace name.

    The options on the format are described here http://download.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta033.htm#sthref531

  7. Michael Says:

    Thanks for the tip. I found the formatSpec chart and in addition to your suggestion about using %N, I would like to suggest /directory-path/%N%f.dbf which will take into account the possibility of numerous datafiles and also give one the ability to convert at the tablespace level. The %f represents file_id in dba_data_files and therefore makes it easier to generate a script on the target identifying the file names.

    But either way will work. Thanks!

  8. Michael Says:

    I’ve been working on this for about a month. We have some larger databases, and there are alot of “gotchas” which are not well documented. One is that you must create users in the source on the target manually. Another is that views and pl/sql don’t seem to make their way across with the metadata. Since they are usually based upon that same data, you must do an additional export/import to migrate them, adding yet another step to the process. Still, like any engineering exersize, anything is doable with practice and as much automation as feasible to eliminate manual errors. One other issue – your entire database and application is basically off-line while these tasks are accomplished – a very hard sell for mission-critical, always-up demands of important enterprise databases!

  9. jason arneil Says:

    Hi Michael,

    Glad to be help btw! You probably already know, but the reason that views, pl/sql stuff and of course the users don’t make their way across is that this stuff is stored in the system tablespace. The other thing to watch out for is the grants particularly look at dba_sys_privs. We basically got all of these things in place first, you can do this a good while before the migration. You are right, to do tts you need to have tablespaces as read only, however when we performed this, we managed to keep our whois service and our DAC service online pretty much throughout as these are read-only applications. If you have any read-only stuff yourself, then you can keep up your SLA on these!

    thanks for reading,

    jason.

  10. Vishal Gupta Says:

    You could also use DB_FILE_NAME_CONVERT parameter to specify different directory location, but keep same filename as source

    CONVERT tablespace perfstat
    FROM PLATFORM=”Solaris[tm] OE (64-bit)”
    TO PLATFORM=”Linux 64-bit for AMD”
    DB_FILE_NAME_CONVERT=”/Oradata/SCUAT/”, “/Orabackups2/SCUAT/database/”
    PARALLELISM=8;

  11. vm Says:

    could you please explain following your info

    So if you are using RAC, you have to make it non-clustered and import on 1 side only:

    Please provide the steps
    how to make RAC database into
    non clustered(example clustered_database=false) or any additional steps involed
    Please help
    Thanks VM

  12. Abe Says:

    Hello all, thanks for wonderful feedback.

    We have destination database Solaris[tm] OE (64-bit and
    Linux 64-bit for AMD Source database. Linux file system is raw file system and Solaris system uses normal file system

    How do we migrate, we converted dbf using rman and exported using expdp…how to move converted dbf and what are the next steps…import and copy dbf to their targets destination? thanks for your help.

  13. kb Says:

    Is there a view which get update when file is converted to ASM using RMAN, which is display by RMAN client
    input filename=/opt/stage/testing.dbf
    converted datafile=+DATA01/labdata/datafile/testing.285.702330543

    Thanks a lot

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

Recent Posts

Highest Rated

Categories

Archives

Meta: