Home » RDBMS Server » Backup & Recovery » ORA-01113: file 5 needs media recovery (oracle 10.2.0.4)
icon4.gif  ORA-01113: file 5 needs media recovery [message #554230] Fri, 11 May 2012 09:48 Go to next message
Seant
Messages: 6
Registered: May 2012
Location: USA
Junior Member
Hello Experts,
I am trying to move my data files from one location to other as i ran out of space.

SQL> alter database rename file 'C:\oracle\product\10.2.0\oradata\SPF433\SPF433ADW_DATA.DBF' to 'D:\Oracle\Datafiles\SPF433\SPF433ADW_DATA.DBF';
Database Altered

SQL> alter database rename file 'C:\oracle\product\10.2.0\oradata\SPF433\SPF433ADW_DATATEMP.DBF' to 'D:\Oracle\Datafiles\SPF433\SPF433ADW_DATATEMP.DBF'
Database Altered

Now when i am trying to run Alter Database open I get ORA-01113: file 5 needs media recovery error

Please guide

Thanks in Advance
Re: ORA-01113: file 5 needs media recovery [message #554232 is a reply to message #554230] Fri, 11 May 2012 10:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

You need to provide a lot more information, such as the complete sequence of events. For example, how did you move the files? 'was the database open at the time?

But one thing you can certainly try is
alter database datafile 5 offline;
alter database open;
recover datafile 5;
alter database datafile 5 online;
You have nothing to lose, and it might work.

Re: ORA-01113: file 5 needs media recovery [message #554234 is a reply to message #554232] Fri, 11 May 2012 10:15 Go to previous messageGo to next message
Seant
Messages: 6
Registered: May 2012
Location: USA
Junior Member
I appreciate your quick response. I am a novice when it comes to Oracle real admin stuff and i did following.
I am sure database was open.
I ran your command
alter database datafile 5 offline; but got following error
ORA-01145: offline immediate disallowed unless media recovery enabled
Re: ORA-01113: file 5 needs media recovery [message #554237 is a reply to message #554234] Fri, 11 May 2012 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you are in NOARCHIVELOG mode then directly try "recover" command.

Post
select d.status, v.status, v.enabled
from v$datafile v, dba_data_files d
where d.file_id = v.file#
  and v.file# = 5
/

Regards
Michel
Re: ORA-01113: file 5 needs media recovery [message #554239 is a reply to message #554237] Fri, 11 May 2012 10:45 Go to previous messageGo to next message
Seant
Messages: 6
Registered: May 2012
Location: USA
Junior Member
Michel,
I ran recover database command and got this

SQL> recover database
ORA-00279: change 3660589 generated at 05/10/2012 11:10:26 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SPF433\ARCHIVELOG\2012_05_11\O1_MF_

1_104_%U_.ARC
ORA-00280: change 3660589 for thread 1 is in sequence #104


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SPF433\ARCHIVELOG\2012_05_11\O1_MF

_1_104_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log
'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SPF433\ARCHIVELOG\2012_05_11\O1_MF

_1_104_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


I have tried many things to get alter database open to work but all in vain.

Thanks you so much for your reply.

Sean
Re: ORA-01113: file 5 needs media recovery [message #554241 is a reply to message #554239] Fri, 11 May 2012 11:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Are you in ARCHIVELOG or NOARCHIVELOG mode?
2/ Post the result of the command I requested
3/ What is the last archive you have?
4/ What is the last one generated (see v$loghist)?

Regards
Michel
Re: ORA-01113: file 5 needs media recovery [message #554249 is a reply to message #554241] Fri, 11 May 2012 11:24 Go to previous messageGo to next message
Seant
Messages: 6
Registered: May 2012
Location: USA
Junior Member
Michel,
1. I am in no AtchiveLog mode
2. result of your command


SQL> elect d.status, v.status, v.enabled from v$datafile v, dba_data_files d whe
re d.file_id = v.file# and v.file# = 5
SP2-0734: unknown command beginning "elect d.st..." - rest of line ignored.
SQL> select d.status, v.status, v.enabled from v$datafile v, dba_data_files d wh
ere d.file_id = v.file# and v.file# = 5
2
SQL> select d.status, v.status, v.enabled from v$datafile v, dba_data_files d wh
ere d.file_id = v.file# and v.file# = 5;
select d.status, v.status, v.enabled from v$datafile v, dba_data_files d where d
.file_id = v.file# and v.file# = 5
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views o
nly




3. I have a snapshot of working environment that i made before starting yesterday, but even if i restore it back i have to move from old location to new due to disk space issue.

4. i have around 116 rows but copying only




THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM SWITCH_CHANGE#
---------- ---------- ------------- --------- --------------
1 106 3703009 10-MAY-12 3756201


Thanks,
Sean
Re: ORA-01113: file 5 needs media recovery [message #554255 is a reply to message #554249] Fri, 11 May 2012 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have you the requested archived log?
Post the result of "select status from v$datafile where file#=5;"

Regards
Michel
Re: ORA-01113: file 5 needs media recovery [message #554274 is a reply to message #554255] Fri, 11 May 2012 13:11 Go to previous messageGo to next message
Seant
Messages: 6
Registered: May 2012
Location: USA
Junior Member
Sorry, i didnt get your 1st question and status says its active.
Re: ORA-01113: file 5 needs media recovery [message #554275 is a reply to message #554274] Fri, 11 May 2012 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00279: change 3660589 generated at 05/10/2012 11:10:26 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SPF433\ARCHIVELOG\2012_05_11\O1_MF_

1_104_%U_.ARC
ORA-00280: change 3660589 for thread 1 is in sequence #104


Have you archived log sequence 104?
You are now at sequence 107 (last archived is 106).
Have you these archived logs 104 to 106?

By the way, file status can't be active, it OFFLINE, ONLINE or RECOVER.
Also add "ENABLED" column to the query and copy and paste its execution like:
SQL> select status, enabled from v$datafile where file#=5;

STATUS  ENABLED
------- ----------
ONLINE  READ WRITE

Regards
Michel

[Updated on: Fri, 11 May 2012 13:29]

Report message to a moderator

Re: ORA-01113: file 5 needs media recovery [message #554276 is a reply to message #554275] Fri, 11 May 2012 13:44 Go to previous messageGo to next message
Seant
Messages: 6
Registered: May 2012
Location: USA
Junior Member
I dont see anything in archived log folder (C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\SPF433\ARCHIVELOG\2012_05_11\O1_MF_

1_104_%U_.ARC)

Here's what i got. I should have said its online not active. Getting used to these terms.

SQL> select status, enabled from v$datafile where file#=5;

STATUS ENABLED
------- ----------
ONLINE READ WRITE
Re: ORA-01113: file 5 needs media recovery [message #554281 is a reply to message #554276] Fri, 11 May 2012 15:40 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You are in noarchivelog mode: in this mode, there is no possibility of recovery, and backups (or file copies) can be made only when the databased is closed. Therefore you have no possibility of recovery. Your only option is a complete restore of the database from your last backup.
If you can't do this, there is one other possibility, that will lose all the data in the damaged files:
alter database datafile 5 offline drop;
alter database open;

This is irreversible, so think carefully first.

Previous Topic: multiple channels in RMAN
Next Topic: backup full database throws an error
Goto Forum:
  


Current Time: Thu Mar 28 10:21:47 CDT 2024