Oracle database reorganization (moving datafiles to new filesystems)

[Former Member]

Hi All,

We are planning to perform a database reorganization (moving datafiles to new filesystems) in our Production environment using SAPDBA.

We had noticed in our recent performance reports that few datafiles in some of the SAPDATAxx filesystems are having higher I/O contention and are putting heavy load on those filesystems resulting in poor performance. So, we had identified the datafiles and planning to move them to new SAPDATA filesystems (uniformly distribute the datafiles to stabilize the I/O). Below are the version information.

SAP: 3.0F

Oracle: 9.2.0.8

OS: HP-UX 11.23

SAPDBA: 6.20

I'm going through SAP notes and Oracle documentation on this.Can you please share your experiences if similar activity was carried out by any of you and the documents relating to this.

Your inputs on this is very much appreciated.

Thanks,

Vasu

[Former Member · Accepted solution]

A reorganization with sapdba can take some time.. In this case, I would perform an offline backup and restore to your new filesystem.

You can also just put the database in offline mode, change your mountpoins and copy the datafiles to the new directories.

Kind regards,

Mark

Edited by: M. Dijsselbloem on May 23, 2010 3:05 PM

[Former Member]

Hello All,

Thank you for the information on this.

At this time we are only considering of moving the datafiles (uniformly distributing the top 10 high I/O datafiles to new filesystems) to reduce the disk I/O contention that is currently experienced by few specific sapdata filesystems.

Tables and Tablespaces reorg is not in the current scope of work.

Thanks,

Vasu

[Former Member]

Just another idea if u cannot bring down the entire instance .

Just take the datafiles offline that u intend to move to a different filesystem (Note that any object residing on datafiles if accessed during this opeartion would throw an error).

alter database datafile <name> offline;

copy the datafiles to new FS.

alter database rename file 'datafile with old path'' to 'datafile with new path';

alter database datafile <name> online;

The above command will require a recovery.

alter database datafile <name> recover;

This has to b done for each datafiles being moved

If the number of datafiles being moved is large u can issue

recover database;

alter database datafile <name> online;

For each datafile issue the above command.

Regards

Kausik

[Former Member]

If your goal is just to make use of new storage, it is fairly straightforward and quick to move the datafiles as others mentioned. However, moving the datafile will NOT reorganize tables/tablespaces.

You can optionally reorg tables/tablespaces in addition to the move of datafiles. This generally saves space and helps performance. If you decide to reorg, I would suggest you move the datafiles to faster storage then reorg (since reorg itself is highly IO intensive).

Some points to consider:

- perform the reorg when business use is least

- perform online reorg where possible

- online reorg creates temporary snaps, typically in the users default tablespace, make sure it has sufficient space

- start with small tables/tablespaces first to get throughput estimate and also estimate of reduction in size

- estimate the size of target tablespace (how small it is going to be compared to source) and build the target tablespace smaller to avoid wasting space

- watch the space usage of the target tablespace when the reorg is running

- if you are reorging individual tables, make sure the indexes are rebuilt

Hope this helps.

Regards,

Shan

[Former Member]

Hi,

We are planning to perform a database reorganization (moving datafiles to new filesystems) in our Production environment using SAPDBA.

Moving datafile is not a complex task... see the link for how to perform this task.

Oracle Procedure:

[http://www.mydigitallife.info/2007/08/04/how-to-rename-or-move-oracle-tablespace-datafile-to-another-location/|http://www.mydigitallife.info/2007/08/04/how-to-rename-or-move-oracle-tablespace-datafile-to-another-location/]

and SAPDBA procedure:

http://help.sap.com/saphelp_470/helpdata/pt/0d/d3035c4a0c11d182b80000e829fbfe/frameset.htm

Regards.

Rajesh Narkhede

[volker_borowski2]

Hi,

moving the datafiles should be fairly simple.

Since no exp/imp is involved, it will just take as much time,

as it takes to copy the files.

Watch out to be on latest sapdba patchlevel.

May be better to switch to a more recent version of brtools?

Backup before reorg is always a good idea.

Best regards

Volker