Datatombraider's Blog

2013/03/31

Move tablespace to different ASM diskgroup

Filed under: Oracle — datatombraider @ 20:03
Moving a tablespace to a different AS; diskgroup can easily accomplished by using RMAN. Here are the steps for RAC. For single instance just use the step on the primary site only.
On primary
Copy all datafiles comprising the tablespace, set the tablespaces offline, switch to the copy and recover the copy to bring it up-to-date.
RMAN> backup as copy tablespace <tbs_name> format '<+NEW_DISKGROUP>';
RMAN> sql 'alter tablespace <tbs_name> offline';
RMAN> switch tablespace <tbs_name> to copy;
RMAN> recover tablespace <tbs_name>;
RMAN> sql 'alter tablespace <tbs_name> online';
On Standby
Stop log-apply because you cannot fiddle with tablespaces and datafiles if log-apply- is running.
DGMGRL> edit database '<DB_UNIQUE_NAME><db_unique_name>' set state=log-apply-off;
Now repeat the copy/switch steps from the primary except the recovery
RMAN> backup as copy tablespace <tbs_name> format '<+NEW_DISKGROUP>';
RMAN> switch tablespace <tbs_name> to copy;
and restart log-apply to start recovery:
DGMGRL> edit database '<DB_UNIQUE_NAME><db_unique_name>' set state=online;
Now delete the old datafiles. Note, datafile and datafile copy have been switched, so you have to delete the datafile copy.
RMAN> list datafilecopy all;
RMAN> delete datafilecopy '';

If you're brave or crazy, you could delete all copies in one shot:
RMAN> delete datafilecopy all;


Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.