Home » RDBMS Server » Server Administration » Renaming Datafile (oracle, 10g, windows xp)
Renaming Datafile [message #569915] Sat, 03 November 2012 01:41 Go to next message
muktha_22
Messages: 408
Registered: December 2009
Senior Member
Hi All,

I am in a trouble, Need help..


Incident:
While renaming the datafile.

1) ALTER TABLESPACE .... OFFLINE;

2) CHANGING THE DATAFILE NAME IN OS LEVEL;

3) ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';

4) ALTER TABLESPACE .... ONLINE;

In the above steps, I HAD FORGOTTON TO FOLLOW THE 2ND STEP.

At the step of 3rd, i got error message, now i am not able to change the name in OS level Also.


Kindly help me out.

Regards
Muktha


Re: Renaming Datafile [message #569918 is a reply to message #569915] Sat, 03 November 2012 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot do this when the database is up on Windows because Windows locks the file and fornid any rename (or drop).
So the solution is to put the tablespace offline, shutdown the database, rename the file and restart the database...

Regards
Michel

[Updated on: Sat, 03 November 2012 01:55]

Report message to a moderator

Re: Renaming Datafile [message #569919 is a reply to message #569918] Sat, 03 November 2012 02:13 Go to previous messageGo to next message
muktha_22
Messages: 408
Registered: December 2009
Senior Member
Hi Michel,

Thanks for your prompt reply.

But i have a general doubt.

What will happened, if we are not putting the tablespace offline before shutdown/restart the DB?
How it will affect?

Regards
Muktha
Re: Renaming Datafile [message #569920 is a reply to message #569919] Sat, 03 November 2012 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try it.
I give the safest way, if it is useful or not, I don't know, I prefer to guarantee the result.

Regards
Michel
Re: Renaming Datafile [message #569922 is a reply to message #569919] Sat, 03 November 2012 04:24 Go to previous messageGo to next message
muktha_22
Messages: 408
Registered: December 2009
Senior Member
Hi Michel,

One more doubt.

What will happened if i am not changing the tablespace mode and simply restarting the Database?

Then after the database open, i will rename the datafile properly.

Any idea? please

Regards
Muktha
Re: Renaming Datafile [message #569924 is a reply to message #569922] Sat, 03 November 2012 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But why don't you just try it and post what you see?
In your opinion, why the message should be different from the one you already got (and didn't share with us)?
Hint: have a look at which state in your tablespace and file.

Regards
Michel
Re: Renaming Datafile [message #569927 is a reply to message #569924] Sat, 03 November 2012 05:01 Go to previous messageGo to next message
muktha_22
Messages: 408
Registered: December 2009
Senior Member
Hi Michel,

Actually I tried in my local PC Database with the second state.

Before i tried, i had the thought of the first state.

Hence i needed a clarification.

Regards
Muktha
Re: Renaming Datafile [message #569936 is a reply to message #569927] Sat, 03 November 2012 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the first and second states?
My "state" refers to dba_tablespaces.status, dba_data_files.status and dba_data_files.online_status columns.

Regards
Michel
Re: Renaming Datafile [message #570136 is a reply to message #569915] Mon, 05 November 2012 20:34 Go to previous messageGo to next message
crispinux
Messages: 4
Registered: July 2010
Location: Cd. Juarez
Junior Member
If you want to move/rename a datafile you must do

from oracle:
shutdown immediate;

once the database is down

mv /path/to/file.dbf /new/path/to/file/dbf

sqlplus / as sysdba
startup mount;
alter database rename file '/path/to/file.dbf' to '/new/path/to/file/dbf';
alter database open;

Regards!!!
Re: Renaming Datafile [message #570150 is a reply to message #570136] Mon, 05 November 2012 23:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT the question.
The question IS: I misdone it how to solve it. The details are in OP's posts, read them.

In addition, hopefully, you don't need to shutdown the database to rename a file, the correct steps are in OP's first post.

Regards
Michel
Re: Renaming Datafile [message #570201 is a reply to message #569915] Tue, 06 November 2012 14:03 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
I am having a similar problem. I tried to move data files and for all the user created and sysaux tablespace, I brought the tablespace offline and moved the datafiles to a different directory and executed
ALTER TABLESPACE .... RENAME DATAFILE 'FILE_OLD' TO 'FILE_NEW';


The user & sysaux table-space and sysaux are still offline

For temp tablespace I did the following
 
CREATE TEMPORARY TABLESPACE dp_ts_temp1 on new location;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE dp_ts_temp1;
drop tablespace dp_ts_temp including contents and datafiles;


CREATE TEMPORARY TABLESPACE dp_ts_temp on new location;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE dp_ts_temp;
drop tablespace dp_ts_temp1 including contents and datafiles;



For undo table space I did the following
CREATE UNDO TABLESPACE undotbs1 on new location;

alter system set undo_tablespace=undotbs1;

drop tablespace undotbs including contents;

CREATE UNDO TABLESPACE undotbs on new location;

alter system set undo_tablespace=undotbs;

drop tablespace undotbs1 including contents;



The user & sysaux table-space are still offline

I shutdown the database to move the system tablespace, and while bring up the database i get the following error.
shutdown immediate;
startup mount;
alter database rename file system_datafile_old_location to system_datafile_new_location;
alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 2980
Session ID: 115 Serial number: 9


I looked in the alert logs and trace file and i see the following error.

In alert log
Starting background process SMCO
Tue Nov 06 14:58:54 2012
SMCO started with pid=18, OS id=2168 
Errors in file c:\app\oracle\diag\rdbms\lib5\lib5\trace\lib5_ora_2980.trc:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_DP_TS_TA_4G68Y3NF_.DBF'
Error 376 happened during db open, shutting down database
USER (ospid: 2980): terminating the instance due to error 376
Instance terminated by USER, pid = 2980
ORA-1092 signalled during: alter database open...
ORA-1092 : opiodr aborting process unknown ospid (3028_2980)
Tue Nov 06 14:58:58 2012
ORA-1092 : opitsk aborting process


in trace file

*** 2012-11-06 14:58:54.995
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
Warning: kzxsl_clean_lwts: 5, ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
Warning: kzxsl_clean_lwts: 7, ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
Warning: kzxsl_clean_lwts: 9, ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
Warning: kzxsl_clean_lwts: 11, ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSAUX_4G68X24Q_.DBF'
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 4: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_DP_TS_TA_4G68Y3NF_.DBF'
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_DP_TS_TA_4G68Y3NF_.DBF'

*** 2012-11-06 14:58:55.112
USER (ospid: 2980): terminating the instance due to error 376


any help is highly appreciated.
Re: Renaming Datafile [message #570203 is a reply to message #570201] Tue, 06 November 2012 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 23068
Registered: January 2009
Senior Member
In the future consider actually testing on a small & disposable DB to validate all steps & commands result in an operational DB
BEFORE abusing some DB that you actually care about.

Do you have a recent backup from which you can restore this DB?
Re: Renaming Datafile [message #570204 is a reply to message #569915] Tue, 06 November 2012 14:23 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
This is already on a test database, but i want to understand what i did wrong so that I can do right on test and try the same on production. Can you please explain what is wrong on the steps I performed?
Re: Renaming Datafile [message #570205 is a reply to message #570204] Tue, 06 November 2012 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 23068
Registered: January 2009
Senior Member
1) SHUTDOWN IMMEDIATE
2) copy files at OS level
3) STARTUP NOMOUNT
4) rename the files within the DB
5) SHUTDOWN IMMEDIATE
6) STARTUP
above is how I have done similar on the past
Re: Renaming Datafile [message #570207 is a reply to message #570205] Tue, 06 November 2012 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
3) STARTUP MOUNT (in nomount mode you can't rename the files as the control file is not read).
Then
5) ALTER DATABASE OPEN

Regards
Michel
Re: Renaming Datafile [message #570208 is a reply to message #570205] Tue, 06 November 2012 14:45 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
I dont think Oracle allows to execute
alter database rename file 'I:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSTEM_4G68WR3Z_.DBF' 
to 'C:\U02\ORADATA\LIB5\DATAFILE\O1_MF_SYSTEM_4G68WR3Z_.DBF'
in no mount state we would only get
ORA-01507: database not mounted

[Updated on: Tue, 06 November 2012 14:54] by Moderator

Report message to a moderator

Re: Renaming Datafile [message #570209 is a reply to message #569915] Tue, 06 November 2012 14:46 Go to previous messageGo to next message
crispinux
Messages: 4
Registered: July 2010
Location: Cd. Juarez
Junior Member
for the error, looks like oracle can't read the datafile, so you must verify if the new name on oracle it's the same as the phisical name, that happen to me once.
Re: Renaming Datafile [message #570210 is a reply to message #570208] Tue, 06 November 2012 14:47 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
I believe i did the same as mention in my previous post.
Re: Renaming Datafile [message #570211 is a reply to message #570209] Tue, 06 November 2012 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 23068
Registered: January 2009
Senior Member
>for the error, looks like oracle can't read the datafile
which can also occur when OS file permissions are not correct
Re: Renaming Datafile [message #570213 is a reply to message #570211] Tue, 06 November 2012 15:02 Go to previous message
prax_14
Messages: 64
Registered: July 2008
Member
I check the file permission everything looks fine.
Previous Topic: graphical (chart/graph) analysis of dynamic performance views [V$ Views]
Next Topic: renaming the listener log_file on 11gr2
Goto Forum:
  


Current Time: Fri Nov 28 15:59:34 CST 2014

Total time taken to generate the page: 0.19207 seconds