Moving any File between ASM Diskgroups 2


Caveat about RMAN RUN{} Form

In previous post, on Online Datafile Move subtitle, I have showed

RMAN> switch datafile 169 to copy;

command to switch datafile from one diskgroup to other one. However RMAN is not orthogonal and the form doesn’t work with the RUN command:

RMAN> run{

2> switch datafile 169 to copy;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “copy”: expecting one of: “datafilecopy”
RMAN-01007: at line 2 column 24 file: standard input

When you need to move hundereds of datafiles from one diskgroup to the other one, you may want to use RUN form. If you do so, use the following

run{
ALLOCATE CHANNEL
disk1 DEVICE TYPE DISK;
copy datafile 111 to ‘+DGRP’ TAG ‘MOVE111’;
sql ‘alter tablespace STAGING_BTS offline’;
switch datafile 111 to DATAFILECOPY TAG MOVE111;
recover tablespace STAGING_BTS;
sql ‘alter tablespace STAGING_BTS online’;
copy datafile 112 to ‘+DGRP’ TAG ‘MOVE112’;
sql ‘alter tablespace USER_BTS offline’;
switch datafile 112 to DATAFILECOPY TAG MOVE112;
recover tablespace USER_BTS;
sql ‘alter tablespace USER_BTS online’;
release CHANNEL disk1;
}

Moving UNDO Tablespace

Basically, UNDO tablespace is also a permanent tablespace. It is only specialized for a specific purpose. Although you can use the same method explained in previous sections to move UNDO tablespace, this is not the convenient way to do the job. Because most probably any time you try to offline the tablespace there will be an active transaction on the UNDO tablespace and Oracle will not allow you to offline it. So we will use a different technique to move UNDO tablespace.

Steps

  1. Create a new UNDO tablespace
  2. Assign UNDO tablespace as the instance tablespace
  3. Wait for Oracle to switch-out tablespace before dropping the old one.

Once you have assigned the new tablespace, you don’t need to bother to offline the old one. Oracle will try it for you periodically (Look at Special Notes section).

Example

For this kind of simple staff, I always prefer to use Grid Control screens. Here is the path to do the job on Grid Control:

  • Administration>Database Administration > Storage > Tablespaces
  • Choose the radio button next to your current tablespace
  • Pick “Create Like” from Actions list.
  • Specify the size of your tablespace (since I usually use Big File tablespace for this purpose, I say tablespace size not datafile size).
  • Administration>Database Administration > Database Configuration> Undo Management
  • Undo Management>Undo Tablespace for this Instance>Change Tablespace
  • Choose your new tablespace as the instance tablespace.
  • After the completion of the switch-out, drop your old UNDO tablespace.

Special Note

After you’ve issued the tablespace assignment, you will see the following messages on alert.log

Successfully onlined Undo Tablespace 967.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Fri Jun 13 11:29:14 2008
ALTER SYSTEM SET undo_tablespace=’UNDO_BTS’ SCOPE=BOTH;
Fri Jun 13 11:30:16 2008
drop tablespace undotbs1 including contents and datafiles
Fri Jun 13 11:30:16 2008
ORA-30013 signalled during: drop tablespace undotbs1 including contents and datafiles…
Fri Jun 13 11:33:16 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:38:16 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:40:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:45:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:50:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 11:55:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jun 13 12:00:48 2008
Undo Tablespace 1 moved to Pending Switch-Out state.

That’s why Oracle is trying to move from the old tablespace to new one, and it is pending due to active transactions on old tablespace. Once the old UNDO tablespace become idle, you will see the following line in alert.log

Undo Tablespace 1 successfully switched out.

About kocakahin

Just a computer engineer

Posted on June 15, 2008, in Oracle. Bookmark the permalink. Leave a comment.

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

%d bloggers like this: