Home » RDBMS Server » Backup & Recovery » ORA-01156 on deleting tablespace
ORA-01156 on deleting tablespace [message #202557] Fri, 10 November 2006 06:19 Go to next message
burkhart
Messages: 4
Registered: November 2006
Junior Member
Hi

I'm trying to delete a tablespace and get the following error:

ORA-01156: recovery in progress may need access to files

When I look at the data files of the tablespace they have status "RECOVER" although there is no recovery running.

Is there a way to reset the status of these files so they can be deleted?

Thanks in advance
Martin

Report message to a moderator

Re: ORA-01156 on deleting tablespace [message #202616 is a reply to message #202557] Fri, 10 November 2006 09:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Did you manually remove the files from the filesystem already?

What does the ERROR column indicate in V$RECOVER_FILE ?

Have you tried to run:

ALTER TABLESPACE <tablespace_name> OFFLINE IMMEDIATE;

Report message to a moderator

Re: ORA-01156 on deleting tablespace [message #202846 is a reply to message #202616] Mon, 13 November 2006 01:08 Go to previous messageGo to next message
burkhart
Messages: 4
Registered: November 2006
Junior Member
I did remove the files manually but only after not being able to delete them by the enterprise manager.

The error column in v$recover_file is empty although the view has a row for each file.

The "alter tablespace" command was successful but the situation is unchanged.

Report message to a moderator

Re: ORA-01156 on deleting tablespace [message #203136 is a reply to message #202846] Tue, 14 November 2006 00:58 Go to previous messageGo to next message
burkhart
Messages: 4
Registered: November 2006
Junior Member
We managed to drop the tablespaces by setting the database in NOARCHIVELOG mode. But as soon as we're setting it back to ARCHIVELOG we can't delete any tablespaces anymore having the same error 01156. But no more files in RECOVER state are present! In fact we can create a new tablespace without any data in it and can't delete it. Setting it offline and online again works fine.

We found out, that there is always one ARCH process more then the configured maximum. If we set the max to 2 we have 3 processes running. Killing them does not help as they are restarted immediately. Any ideas?

Report message to a moderator

Re: ORA-01156 on deleting tablespace [message #203138 is a reply to message #203136] Tue, 14 November 2006 01:12 Go to previous message
burkhart
Messages: 4
Registered: November 2006
Junior Member
We solved the problem. The cause was a log process belonging to an already removed standby instance. The process was still running and supposedly still trying to synchronize instances. When removing the standby instance it was not uninstalled correctly.

Report message to a moderator

Previous Topic: corrupted system01.dbf file
Next Topic: RMAN Backup failed - RMAN-06059 error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 02 09:20:02 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.