Problem dropping dbspace
A user on IDS 11.50.FC6 (HP-UX) couldn't drop an offline dbspace: onspaces -d failed with "DBspace is not empty", even though sysdatabases/systabnames queries on partnum showed no objects in it. The corruption arose because the same raw devices were accidentally assigned to a second instance's dbspaces while still in use by the first, so both instances marked the chunks offline. Suggestions were to run oncheck -pe (which also showed nothing) and to rebuild chunks and restore from a level 0 archive, but no suitable backup existed. No fix was reached in the thread; the advice was to let IBM support dial in and remove the broken dbspace.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Running IDS 11.50.fc6 on HP-UX 11.31 (PA-RISC)
I have a dbspace that is offline due to some disk corruption. We were going to
drop this dbspace even before the corruption was detected, and all databases
and tables that we knew of had been removed from it. The dbspace does not
appear to have any databases or tables in it, as queries of
sysmaster:sysdatabases and sysmaster:systabnames show nothing with a partnum
in this dbspace. Not sure if it matters, but there are no blobs in this
instance.
(In case I'm mis-remembering how to check, I am querying "WHERE TRUNC(partnum
/ 1048576, 0) = " the number of the dbspace in question. I believe this is the
way to see what dbspace a database or table (or index) is in.)
Anyway, when I try to run 'onspace -d dbspacename', I get:
WARNING: Dropping a DBspace.
Do you really want to continue? (y/n)y
WARNING! The dbspace you wish to drop has been disabled. There
may be database catalog entries for tables or indices which contain
data in this dbspace. If you complete the drop they will be unusable.
Do you really want to continue? (y/n)y
Cannot drop the Space.
ISAM error: DBspace is not empty
How do I find what is in this dbspace, if the sysdatabases and systabnames
queries do not find anything?
Run oncheck -pe
Regards,
David.
> On 14 May 2014 at 21:12 MARK COLLINS <markc@myfastmail.com> wrote:
>
>
> Running IDS 11.50.fc6 on HP-UX 11.31 (PA-RISC)
>
> I have a dbspace that is offline due to some disk corruption. We were going
to
> drop this dbspace even before the corruption was detected, and all databases
> and tables that we knew of had been removed from it. The dbspace does not
> appear to have any databases or tables in it, as queries of
> sysmaster:sysdatabases and sysmaster:systabnames show nothing with a partnum
> in this dbspace. Not sure if it matters, but there are no blobs in this
> instance.
>
> (In case I'm mis-remembering how to check, I am querying "WHERE TRUNC(partnum
> / 1048576, 0) = " the number of the dbspace in question. I believe this is
the
> way to see what dbspace a database or table (or index) is in.)
>
> Anyway, when I try to run 'onspace -d dbspacename', I get:
>
> WARNING: Dropping a DBspace.
> Do you really want to continue? (y/n)y
> WARNING! The dbspace you wish to drop has been disabled. There
> may be database catalog entries for tables or indices which contain
> data in this dbspace. If you complete the drop they will be unusable.
> Do you really want to continue? (y/n)y
> Cannot drop the Space.
> ISAM error: DBspace is not empty>
> How do I find what is in this dbspace, if the sysdatabases and systabnames
> queries do not find anything?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
David,
Thanks. I ran oncheck -pe and looked for anything with the pathname for the
raw devices associated with the two chunks of this dbspace. There was no ouput
listing these raw devices. I also searched for any reference to the dbspace
name, again no reference.
I can post the output if anyone wants to see it, but it is over 63k lines.
Mark
===============================
Run oncheck -pe
Regards,
David.
> On 14 May 2014 at 21:12 MARK COLLINS <markc@myfastmail.com> wrote:
>
>
> Running IDS 11.50.fc6 on HP-UX 11.31 (PA-RISC)
>
> I have a dbspace that is offline due to some disk corruption. We were going
to
> drop this dbspace even before the corruption was detected, and all databases
> and tables that we knew of had been removed from it. The dbspace does not
> appear to have any databases or tables in it, as queries of
> sysmaster:sysdatabases and sysmaster:systabnames show nothing with a partnum
> in this dbspace. Not sure if it matters, but there are no blobs in this
> instance.
>
> (In case I'm mis-remembering how to check, I am querying "WHERE TRUNC(partnum
> / 1048576, 0) = " the number of the dbspace in question. I believe this is
the
> way to see what dbspace a database or table (or index) is in.)
>
> Anyway, when I try to run 'onspace -d dbspacename', I get:
>
> WARNING: Dropping a DBspace.
> Do you really want to continue? (y/n)y
> WARNING! The dbspace you wish to drop has been disabled. There
> may be database catalog entries for tables or indices which contain
> data in this dbspace. If you complete the drop they will be unusable.
> Do you really want to continue? (y/n)y
> Cannot drop the Space.
> ISAM error: DBspace is not empty>
> How do I find what is in this dbspace, if the sysdatabases and systabnames
> queries do not find anything?
>
>
A little more information, in case it is helpful. The cause of the corruption is that the raw devices were accidentally allocated to a second instance while still allocated to this instance. Both instances have flagged these devices as offline. The original plan was to drop these dbspaces in this instance, perform a level 0, and then reallocate them for other dbspaces. Unfortunately, the INFORMIXSERVER variable was not set correctly, and the same dbspace existed in the other instance (and it was empty there). So the dbspace was dropped, the level 0 performed, and the devices were allocated to new dbspaces in the second instance, while the devices were still allocated to the first instance with the original dbspace name. Shortly afterward, both instances identified errors (sanity checks) with these dbspaces and marked them offline. So I have two instances with this problem (other than the fact that the second instance has a different names for the dbspaces).
from what i can see, you have 2 offline database instances because the chunk was mistakenly allocated into the other instance. The only thing you can do is to to build the correct chunks for each db instances and restore from backup (level 0).
Jack, Actually the instances are online, it is just the dbspaces assocaiated with the chunks that are offline. Unfortunately, there are no level 0 archives that included the spaces in question available for either of the instances. I suspect that this will require the intervention of IBM support, but I was trying to see if there was any way short of that to address this situation. Mark ========================== from what i can see, you have 2 offline database instances because the chunk was mistakenly allocated into the other instance. The only thing you can do is to to build the correct chunks for each db instances and restore from backup (level 0).
IBM suipport can dial in and remove the broken dbspace, that would be the best option. David. > On 15 May 2014 at 10:24 JACK PAPA <informix2009@gmail.com> wrote: > > > from what i can see, you have 2 offline database instances because the chunk > was mistakenly allocated into the other instance. > > The only thing you can do is to to build the correct chunks for each db > instances and restore from backup (level 0). > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >