OraFAQ Forum: Performance Tuning » Reorganizing index datafiles with tables in partition (merged)

Home » RDBMS Server » Performance Tuning » Reorganizing index datafiles with tables in partition (merged) (Oracle 10g 10.2.0.5, RHEL 6.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reorganizing index datafiles with tables in partition (merged) [message #668960]

Mon, 26 March 2018 01:15

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Gurus,

As we're expanding/adding disks on the database server, we will be relocating most of our index datafiles to another location/mountpoint. We will implement this without shutting down the database.
The tables associated with said indexes are partitioned.
Do this implementation have any critical impact on the database?

Thank you very much.

[Updated on: Tue, 27 March 2018 00:59] by Moderator

Report message to a moderator

Re: Reorganizing index datafiles with tables in partition [message #668961 is a reply to message #668960]

Mon, 26 March 2018 01:19

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

As you are on release 10, you will not be able to relocate datafiles without taking them offline. Yes, you can do this without shutting down the database but large parts (probably all) of your application will not be available while doing this.

Partitioning has minimal impact.

Report message to a moderator

Re: Reorganizing index datafiles with tables in partition [message #668962 is a reply to message #668961]

Mon, 26 March 2018 01:58

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Well-noted Sir.

Yes Sir, we'll be scheduling said implementation during the holidays of the 'Holy Week' (mostly no end-users).

Btw, since the database is up, we will be issuing at the OS level the copy (cp) instead of moving (mv) for the datafiles.
After we finished copying, executing the "ALTER DATABASE RENAME FILE", make in ONLINE, then make a backup of the controlfile,
do we need to remove/delete right away the old datafiles and bounce the database in order to effect the changes?
This is a problematic database where we find it hard to restart it normally, as we resorted usually to shutdown abort.

Thanks you for your advice.

Report message to a moderator

Relocating/moving data files without a shutdown (merged) [message #668973 is a reply to message #668960]

Mon, 26 March 2018 20:57

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Sirs,

Good day!

As we're preparing to reorganize index datafiles (due to diskspace issue),
below are our planned steps without shutting down the Prod database:
1. check datafiles current paths and the new path (esp. permissions);
2. make the tablespace OFFLINE;
3. copy (via OS) the datafiles from source to destination;
4. execute the ALTER DATABASE RENAME FILE;
5. bring the tablespace ONLINE;
6. backup the controlfile;
7. execute the ALTER DATABASE OFFLINE DROP;
8. delete (via OS) the old datafiles;
9. implement index rebuild.

Just to validate if there are steps in here that would cause something
troublesome for the database.

Thank you in advance.

[Updated on: Tue, 27 March 2018 01:00] by Moderator

Report message to a moderator

Re: Relocating/moving data files without a shutdown [message #668974 is a reply to message #668973]

Mon, 26 March 2018 21:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

reym21 wrote on Mon, 26 March 2018 18:57

Sirs,

Good day!

As we're preparing to reorganize index datafiles (due to diskspace issue),
below are our planned steps without shutting down the Prod database:
1. check datafiles current paths and the new path (esp. permissions);
2. make the tablespace OFFLINE;

Immediately after doing #2 above, expect the online application to start spewing ERRORS because many objects will now be unavailable

post SQL & results that show disk space will be "reclaimed" if you succeed completing steps #1 - #9

Report message to a moderator

Re: Relocating/moving data files without a shutdown (merged) [message #668979 is a reply to message #668973]

Tue, 27 March 2018 01:01

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

Quote:

7. execute the ALTER DATABASE OFFLINE DROP;

What is this? I dread to think.

Report message to a moderator

Re: Relocating/moving data files without a shutdown (merged) [message #668981 is a reply to message #668973]

Tue, 27 March 2018 03:13

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

reym21 wrote on Tue, 27 March 2018 02:57

Just to validate if there are steps in here that would cause something
troublesome for the database.

Database won't care.

The users, on the other hand...well...yeah they're going to have an bad time.

Report message to a moderator

Re: Reorganizing index datafiles with tables in partition (merged) [message #668985 is a reply to message #668960]

Tue, 27 March 2018 07:52

JPBoileau
Messages: 88
Registered: September 2017

Member

Are you moving ONLY the index data (and not the table data) to a new location? Your original post is a bit ambiguous.

If so, you may want to consider simply rebuilding the index as you're already doing that in step 9.

ALTER INDEX <SCHEMA>.<INDEX_NAME> REBUILD TABLESPACE <XYZ> (ONLINE);

I'm putting the "ONLINE" part as optional as if you're taking downtime to do this, you would want to NOT do it online for performance considerations.

Another point to note, if your tables are located in the same tablespaces as your indexes, the above will not free physical space but will release free space to the tablespace.

jp

Report message to a moderator

Re: Reorganizing index datafiles with tables in partition (merged) [message #668992 is a reply to message #668985]

Tue, 27 March 2018 21:01

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@JPBoileau

Yes Sir, we're moving only the index datafiles only.
Although table data (including temp and undo) are both in the same mountpoint/directory.
We need this badly because disk utilization currently is nearing 98%.
This is a system turned-over to us by a contractor after warranty.

Thanks.

Report message to a moderator

Re: Relocating/moving data files without a shutdown (merged) [message #668993 is a reply to message #668979]

Tue, 27 March 2018 21:08

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

@John Watson

Sir, reiterating my query-- do we need to remove/delete right away the old datafiles (as per steps #7-8) without bouncing the database?

Thank you.

Report message to a moderator

Re: Relocating/moving data files without a shutdown (merged) [message #668994 is a reply to message #668993]

Wed, 28 March 2018 00:58

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

I don't know what your step 7 is.
Step 8, no problem.
Your step 9 is redundant.

[Updated on: Wed, 28 March 2018 01:00]

Report message to a moderator

Re: Reorganizing index datafiles with tables in partition (merged) [message #669002 is a reply to message #668992]

Wed, 28 March 2018 08:34

JPBoileau
Messages: 88
Registered: September 2017

Member

The mountpoint is not as relevant here as the tablespaces you are using (and of course where it is located).

e.g.:

1) If you have a 10GB table in tablespace A on mountpoint 1, along with 5GB of indexes in tablespace A on mountpoint 1, if you rebuild the indexes in tablespace B on mountpoint 2, you will have freed 5GB in tablespace A, but not on the mountpoint 1.

2) If you have a 10GB table in tablespace A on mountpoint 1, along with 5GB of indexes in tablespace Z on mountpoint 1, if you rebuild the indexes in tablespace B on mountpoint 2, you can then drop tablespace Z (which should be empty after rebuilding the indexes) including the tablespace datafiles, and will have freed 5 GB on mountpoint 1.

If your situation is #1 above, you would need to resize the tablespace if possible, or create a new tablespace with a smaller size, and move the table into the new tablespace.

JP

Report message to a moderator

Previous Topic:	SQL Query performance Tuning
Next Topic:	Oracle Golden gate- Compression tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 20:43:40 CDT 2025