Home » RDBMS Server » Performance Tuning » Reorganizing index datafiles with tables in partition (merged) (Oracle 10g 10.2.0.5, RHEL 6.2)
Reorganizing index datafiles with tables in partition (merged) [message #668960] Mon, 26 March 2018 01:15 Go to next message
reym21
Messages: 238
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 Go to previous messageGo to next message
John Watson
Messages: 8268
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.
Re: Reorganizing index datafiles with tables in partition [message #668962 is a reply to message #668961] Mon, 26 March 2018 01:58 Go to previous messageGo to next message
reym21
Messages: 238
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.
Relocating/moving data files without a shutdown (merged) [message #668973 is a reply to message #668960] Mon, 26 March 2018 20:57 Go to previous messageGo to next message
reym21
Messages: 238
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 Go to previous messageGo to next message
BlackSwan
Messages: 26704
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
Re: Relocating/moving data files without a shutdown (merged) [message #668979 is a reply to message #668973] Tue, 27 March 2018 01:01 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
Quote:
7. execute the ALTER DATABASE OFFLINE DROP;
What is this? I dread to think.
Re: Relocating/moving data files without a shutdown (merged) [message #668981 is a reply to message #668973] Tue, 27 March 2018 03:13 Go to previous messageGo to next message
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.
Re: Reorganizing index datafiles with tables in partition (merged) [message #668985 is a reply to message #668960] Tue, 27 March 2018 07:52 Go to previous messageGo to next message
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
Re: Reorganizing index datafiles with tables in partition (merged) [message #668992 is a reply to message #668985] Tue, 27 March 2018 21:01 Go to previous messageGo to next message
reym21
Messages: 238
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.


Re: Relocating/moving data files without a shutdown (merged) [message #668993 is a reply to message #668979] Tue, 27 March 2018 21:08 Go to previous messageGo to next message
reym21
Messages: 238
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.
Re: Relocating/moving data files without a shutdown (merged) [message #668994 is a reply to message #668993] Wed, 28 March 2018 00:58 Go to previous messageGo to next message
John Watson
Messages: 8268
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 Go to previous message
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
Previous Topic: SQL Query performance Tuning
Next Topic: Oracle Golden gate- Compression tables
Goto Forum:
  


Current Time: Fri May 29 16:11:46 CDT 2020