Skip to Main Content
  • Questions

  • Migrating the datafiles to a new SAN

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Vijayakumar.

Asked: March 15, 2011 - 11:33 am UTC

Last updated: March 15, 2011 - 2:14 pm UTC

Version: 10.2.0.x

Viewed 1000+ times

You Asked

Hi Tom,

Thanks for your time in answering my question.

Hardware Changes that is being planned:

We are planning to migrate our SAN environment from IBM SAN to HP SAN and the steps to be followed are as follows:

1. Shutdown the database gracefully
2. Perform a backup of the database files (whole database is stored in SAN) at the operating system level (I also have a database backup taken through SQLBacktrack just in case :) )
3. Migrate to new SAN
4. Restore the database files from the backup taken in step 2
5. Start the database

Question:

After starting the database in new SAN environment, do I have to recreate/rebuild the indexes?

I have performed similar SAN migrations in the past and we have encountered some performance problems but not because of the database but because of:

Change in LUN size between old SAN and new SAN
Changes to some SAN disk attributes
Striping

But in one of the discussion, I'm hearing that as a "best database practce", it is advisable to recreate indexes after such SAN migrations.

My point is,

1. At the lowest level of granularity, Oracle stores the data in terms of data blocks/index blocks.
2. ROWID of a table record does not change as part of the SAN migration
3. The status of the indexes does not get changed after the SAN migration is completed.
4. No invalid objects after the SAN migration.
5. I don't recreate the indexes if I move my datafile from file system to another file system and logically I'm changing the datafiles from one SAN disk to another SAN disk.
6. If I have to restore a production database into another server due to a hardware failure, still I don't recreate the index.

All these above points are leading me towards "an index recreation/rebuild is not required after the SAN migration is completed".

I even asked this question in one of the SR and the analyst also specified that "he doesn't think an index recreation is required". But there is no article available in Metalink to refer.

Please let me know your inputs.

and Tom said...

just copy the datafiles over, you don't need to 'backup and restore' if you don't want to. It would be reassuring to know that you can (restore) that is for sure, but if you just want to migrate the database - just move the datafiles - no need to read datafiles from old, write to backup, read from backup, write to new. Just read old, write new.

And no, you would not have to recreate any indexes or anything like that - the datafiles are intact.


But in one of the discussion, I'm hearing that as a "best database practce", it is advisable to recreate indexes after such SAN migrations

that is ranking way up there in a list of "not smart things" I've heard said. If what you say is true - that someone is listing this as a database best practice - find new people to listen to please.


All these above points are leading me towards "an index recreation/rebuild is not required after the SAN migration is completed".

Excellent!!!! You just saw right through the "not smart stuff".

There is no article on metalink telling you not to delete all of your data and rollback either after a SAN migration. Does that mean you shouldn't?

Documenting that "no index rebuild is necessary after a SAN migration" would be like documenting that you should not use your lawn mower to cut hair, or you shouldn't use a hammer and nail to pierce ears. Some things just go without saying.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vijayakumar Sekar, March 15, 2011 - 2:32 pm UTC

As always, your inputs are very crisp and clear..Thank you very much.

Think of it this way....

A reader, March 17, 2011 - 10:24 pm UTC



Do you rebuild the indexes after bringing up the database? Guess not. Moving to a diff SAN should be transparent:
1. Shutdown DB
2. Sync up the disks
3. Bring up the db
4. Check out and release

We are also doing a data center migration (across cities) but the steps we are following are the same.

The disks are being sycned at the backend while the servers are being (physically) moved to the new location.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.