Moving temp datafile in Oracle

[suki]

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

[T G Manikandan]

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 · Accepted solution]

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

[Yogeeraj_1]

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

[Barbara Spadaro_1]

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

[T G Manikandan]

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]

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

[T G Manikandan]

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]

Dictionary managed and locally managed

Sorry for the previous posting as temporary managed.


Thanks

[suki]

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)
)

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

[T G Manikandan]

What do you mean by moving the shared binaries?

Why is the need?


Revert

[suki]

Hi Mani,
I have copied the /opt/oracle_817 into a different directory for the newly moved instance. But earlier all the 4 instances where using the same /opt/oracle_817 Oracle_Home Directory.
Now the home directory is different for the moved database.
Thanks,
suki.

[T G Manikandan]

Why are you moving the oracle_home for the moved instance?
There is no need to move the Oracle_home.Oracle_home is the home directory for the Oracle application and it is common for all the instances.

YOu listener.ora file looks great.
If you are adding another instance then

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)
)
(SID_DESC =
(SID_NAME = xxx)
(ORACLE_HOME = /opt/oracle_817)
)

)


Revert

[suki]

Hi Mani,
I need to configure each instance as a separate package in a cluster environment.So what I want to keep different Oracle_Home directories.
Thanks,
Suki.

[Christian Gebhardt]

From oracle it is not recommended to move or copy an Server-Installation to another place.

With relinking the binaries it will work but OraInstaller will not work correctly on this "new installation" because in the OraInventory the former installation path is hard coded in binary files.

The correct way to separate installations is a new install of oracle binaries.

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x1b13237a4bc6d611abdb0090277a778c,00.html

Chris