Operating System - HP-UX
1748010 Members
4571 Online
108757 Solutions
New Discussion юеВ

Re: Moving temp datafile in Oracle

 
SOLVED
Go to solution
suki
Frequent Advisor

Moving temp datafile in Oracle

Hi,
I wanted to move my total oracle 8i database from one mount point to other. But I am able to rename all my datafiles,controlfiles,and log files after doing the below
1. Shutdown the database & Instance.
2. Started the instance and only mounted the database.
3.Moved all the files to new location.
4.Proceessed the "Alter database rename file 'oldname' to 'newname'" to all files.

But the problem starts when I try to rename temp datafile. The oracle manual also says we cannot rename a temp datafile.
Can someone help me how to rename the temp data file.
Thanks,
suki
14 REPLIES 14
T G Manikandan
Honored Contributor

Re: Moving temp datafile in Oracle

1.shutdown the database

2.copy all the datafiles from teh old mount point to the new mount point.

3.startup mount (mount the database)

4.alter database rename file 'old file path' to 'new file path'

5.alter database open

Note:Copy of the database files should be after the full instance shutdown and before the mounting of the instance.

Revert.


Thanks
Yogeeraj_1
Honored Contributor
Solution

Re: Moving temp datafile in Oracle

hi,

I would recommend the following steps:
==============================
SQL> alter database tempfile '/V90164/temp02.dbf' drop;

Database altered.

SQL> alter tablespace USER_TEMP_1 add tempfile '/V90164/temp03.dbf' size 2M;

Tablespace altered.

$ rm /V90164/temp02.dbf
==============================

i.e. Drop the tempfile and add a new one to the tablespace. Then remove the OS file.

Hope this helps!

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Moving temp datafile in Oracle

Hi,

you may also consider the following alternative:

alter database backup controlfile to trace

shutdown

move the files

edit the generated CREATE CONTROLFILE statement (found in the user dump
destination) and change the filenames

use the generated script you just edited to startup the database.

Hope this helps too!

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)

Re: Moving temp datafile in Oracle

Hi,

That's true, a temp datafile cannot be renamed. And Yogeeraj's suggestion of dropping the tablespace and recreating it in a new location is a good one.

But, if this temp datafile is also the temporary tablespace for your users, Oracle will not allow you to drop it until you have re-assigned a temporary tablespace for your users. To find out which is your users' temporary tablespace:
select username, temporary_tablespace from dba_tables;
If the tablespace you wish to drop is also the temporary tablespace for your users, then:
1. create a new temp tablespace where you want it to reside.
2. allocate your users to this tablespace,
alter user temporary tablespace ;
3. drop the old temp tablespace in Oracle and then physically remove the file.

It's all good fun...enjoy!
Barbara
If all else fails, read the instructions.
T G Manikandan
Honored Contributor

Re: Moving temp datafile in Oracle

If that is temp datafile

Yes you can drop and re-create the temp datafile

I think this workaround should work

bring the temp tablespace offline

alter tablespace temp offline;

alter tablespace temp rename datafile 'old path'
to 'new path'

this should work .


Thanks
Yogeeraj_1
Honored Contributor

Re: Moving temp datafile in Oracle

yes,

The script to modify users temporary tablespaces can be generated by running the following SQL command:
=============================
select 'alter user '||username||' temporary tablespace other_temp;'
from dba_users
where temporary_tablespace='TEMP';
=============================
hope this helps!

Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
T G Manikandan
Honored Contributor

Re: Moving temp datafile in Oracle

if it is a dictionary managed then
//*
If that is temp datafile

Yes you can drop and re-create the temp datafile

I think this workaround should work

bring the temp tablespace offline

alter tablespace temp offline;

alter tablespace temp rename datafile 'old path'
to 'new path'

*//
If it is a temporary managed tablespace then you should go with Yogeeraj's first response.

However creating a new temp tablespace and moving users from the existing one should be a safer one.


Thanks
T G Manikandan
Honored Contributor

Re: Moving temp datafile in Oracle

Dictionary managed and locally managed

Sorry for the previous posting as temporary managed.


Thanks
suki
Frequent Advisor

Re: Moving temp datafile in Oracle

Thanks to everyone who helped me.
Yogeeraj gave the correct procedure. As he said I dropped the temp datafile and recreated it.
But I am facing another problem with the listener.
In my system there were four instances of oracle running with the shared executables.Now I have moved one of the instance datafiles and copied the oracle binaries also separately rather than the shared one. I was stuck up with the listener.I have attached my listener.ora file. Can someone help me how to configure for my separate instance without disturbing the other running instances.
Thanks,
suki.
_______________________________________________
My existing listener file:-

LISTENERDSDI =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=abc.info.com)(PORT=1521))
)
)
)

SID_LIST_LISTENERDSDI =
(SID_LIST =
(SID_DESC =
(SID_NAME = ABC1)
(ORACLE_HOME = /opt/oracle_817)
)
(SID_DESC =
(SID_NAME = ABC2)
(ORACLE_HOME = /opt/oracle_817)
)
(SID_DESC =
(SID_NAME = ABC3)
(ORACLE_HOME = /opt/oracle_817)
)
(SID_DESC =
(SID_NAME = ABC4)
(ORACLE_HOME = /opt/oracle_817)
)

)
-----------------------------------------------