OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Move UNDOTBS

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

Move UNDOTBS [message #173672]

Tue, 23 May 2006 22:21

rmalhi
Messages: 42
Registered: May 2006
Location: Australia

Member

Hi Gurus,

I want to move UNDOTBS to another mount point on SUN server.
at present UNDOTBS is on /u01/app/oracle/oradata/ovpi/ and I want to move this to /u03/app/oracle/oradata/ovpi/.

As I am running out of space on u01 as UNDOTBS is autoextent ON and have took nearly 5G on /u01.

My database is using spfile and versio is 9.2.0.4 64bit.

Your help will be appreciated.

This is my first post to orafaq.

Cheers

Report message to a moderator

Re: Move UNDOTBS [message #173718 is a reply to message #173672]

Wed, 24 May 2006 03:16

Frank Naude
Messages: 4580
Registered: April 1998

Senior Member

Follow this procedure:

1. Shutdown the database
2. Move the datafile to its new location.
3. Do a "STARTUP MOUNT"
4. ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
5. ALTER DATABASE OPEN;

Report message to a moderator

Re: Move UNDOTBS [message #173737 is a reply to message #173672]

Wed, 24 May 2006 05:04

ebrian
Messages: 2794
Registered: April 2006

Senior Member

If you need to avoid shutting down the instance, you can do the following:

--Create new undo tablespace:
SQL> create undo tablespace UNDOTBS02 datafile '/u03/app/oracle/oradata/ovpi/undo02.dbf' size 1000m;

--Set new tablespace as undo_tablespace:
SQL> alter system set undo_tablespace=undotbs02;

--Drop the old tablespace:
SQL> drop tablespace undotbs including contents;

When dropping the old tablespace, you may encounter an ORA-30013 error. This essentially indicates that you must wait for any existing transactions using this tablespace to either commit or rollback before the tablespace can be dropped.

Report message to a moderator

Re: Move UNDOTBS [message #173856 is a reply to message #173737]

Wed, 24 May 2006 21:14

rmalhi
Messages: 42
Registered: May 2006
Location: Australia

Member

Thanks Brain and Frank,

Really appreciated your help.

Brain, if i follow your procedure do i need to modify init file when i will start my database later stage. But i am using spfile.

Thanks for your help

Report message to a moderator

Re: Move UNDOTBS [message #175186 is a reply to message #173856]

Thu, 01 June 2006 03:59

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

I hope by now, you might have resolved your issue, but YEs, the modification in init file & subsequently in spfile is required. You need to change the parameter for undo tablespace to this new tablespace name.

--Girish

Report message to a moderator

Re: Move UNDOTBS [message #175367 is a reply to message #175186]

Fri, 02 June 2006 00:17

rmalhi
Messages: 42
Registered: May 2006
Location: Australia

Member

Thanks to everyone.

I have fixed the issue and i have followed the following cmds. feel free to use it will work,

1. Login as oracle account
su - oracle
2. Change directory
cd $ORACLE_HOME/dbs
3. Make a backup copy of the original init<ORACLE_SID>.ora file.
cp -p init<ORACLE_SID>.ora init<ORACLE_SID>.ora.bYYYYMMDD
4. Login in the oracle service.
sqlplus /nolog
5. Use the sys account.
conn / as sysdba
6. Copy the spfile into in pfile.
CREATE pfile FROM spfile;
7. Create a new UNDO tablespace.
CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE '<YOUR-NEW-LOCATION-HERE>/undotbs02.dbf' SIZE 5000M;

8. Shutdown the service.
shutdown immediate;
9. Exit from sqlplus.
quit

10. Backup the spfile.
cp -p spfile<ORACLE_SID>.ora spfile<ORACLE_SID>.ora.bYYYYMMDD
11. Open and the modify the init<ORACLE_SID>.ora
vi initovpi.ora
12. Replace the line *.undo_tablespace='UNDOTBS1' with
*.undo_tablespace='UNDOTBS2'
13. Save and quit
Mad

14. Login again into the oracle service.
sqlplus /nolog
15. Use the sys account.
conn / as sysdba
16. Copy the pfile into the spfile.
CREATE spfile FROM pfile;
17. Start the oracle service.
startup
18. Drop the old UNDO tablespace.
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
19. Exit the sqlplus.
quit

20. Finish, congratulations!

Rupi

Report message to a moderator

Previous Topic:	Operating system authentication
Next Topic:	Bdump Trace file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:52:16 CDT 2024