| Renaming Datafile [message #569915] |
Sat, 03 November 2012 01:41  |
muktha_22
Messages: 527
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
muktha_22
Messages: 527
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 #569922 is a reply to message #569919] |
Sat, 03 November 2012 04:24 |
muktha_22
Messages: 527
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 #569927 is a reply to message #569924] |
Sat, 03 November 2012 05:01 |
muktha_22
Messages: 527
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 #570136 is a reply to message #569915] |
Mon, 05 November 2012 20:34 |
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 #570201 is a reply to message #569915] |
Tue, 06 November 2012 14:03 |
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 #570204 is a reply to message #569915] |
Tue, 06 November 2012 14:23 |
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 #570208 is a reply to message #570205] |
Tue, 06 November 2012 14:45 |
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'
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 |
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
