Problem with adding new volume to database

[Former Member]

Hello,

We have problem with our MaxDB database. Database reached full data volumes so db admin execute 'Add volume' command via MaxDB Database Manager App.

Something strange had happen because in MaxDB Database Manager new volume is look like active and succesfully added but on disk ../sapdata/DISKD0032 file doesn't create....

Now database runs only into admin state. If we try to do db_online in startlog I see that database is looking for this missing file... how can I do?

Unfortunately backup restore is highly unwanted..

I'll be grateful if someone can help

Best regards,

Michal Sarna

[Former Member]

Hello,

Thank you very much for your support Lars.

I added missing files with your procedure.

db_admin starts

db_online fails

Trying to start I have error:

dbmcli on BWP>db_online

ERR

-24988,ERR_SQL: SQL error

-9407,System error: unexpected error

3,Database state: OFFLINE

20025,ACTIVATE is missing

in klndiag i have:

2011-01-18 13:04:42 18955 8 FileDir File directory restart completed

2011-01-18 13:04:43 18955 20061 Log History: 100 (100) files existing

2011-01-18 13:04:43 18955 20064 Log History: all history files registered, GC is ready

2011-01-18 13:04:43 18955 ERR 51080 SYSERROR -9407 unexpected error

2011-01-18 13:04:43 18955 ERR 3 Admin Database state: OFFLINE

2011-01-18 13:04:43 18955 ERR 20025 Admin + ACTIVATE is missing

2011-01-18 13:04:43 18955 11560 COMMUNIC Releasing T142

2011-01-18 13:04:43 18955 12696 DBSTATE Change DbState to 'SHUTDOWN'(25)

2011-01-18 13:04:43 18933 12696 DBSTATE Change DbState to 'KILL'(29)

2011-01-18 13:04:43 18953 12825 TASKING state 29 before shutkill(1)

2011-01-18 13:04:43 18933 12697 DBSTATE Resuming tracewriter

2011-01-18 13:04:43 18953 WNG 11824 COMMUNIC Releasing T52 database shutdown

2011-01-18 13:04:43 18933 12696 DBSTATE Change DbState to 'TRACE_WRITER_WAIT'(29)

2011-01-18 13:04:43 18949 12825 TASKING state 29 before shutkill(1)

2011-01-18 13:04:43 18953 12768 UKT5 stopped

2011-01-18 13:04:43 18949 20000 Trace Start flush kernel trace

2011-01-18 13:04:43 18939 11566 stop REQUESTOR stopped

2011-01-18 13:04:43 18949 20001 Trace Stop flush kernel trace

2011-01-18 13:04:43 18949 20002 Trace Start flush kernel dump

2011-01-18 13:04:44 18949 20003 Trace Stop flush kernel dump

2011-01-18 13:04:44 18949 12619 TASKING Releasing tracewriter

+++++++++++++++++++++++++++++++++++++++ Kernel Exit ++++++++++++++++++++++++++++

2011-01-18 13:04:45 0 ERR 12009 DBCRASH Kernel exited due to signal 0(Killed after timeout with state SERVER_KILL)

2011-01-18 13:04:45 0 12890 DIAGHIST Backup of diagnostic files will be forced at next restart

2011-01-18 13:04:45 0 12808 DBSTATE Flushing knltrace pages

2011-01-18 13:04:45 0 12696 DBSTATE Change DbState to 'OFFLINE '(29)

-

---

current write position -

---

Any idea? What msxdb mean with ACTIVATE is MISSING?

BTW. I'm not a customer. I'm a SAP Basis Consultant (but I'm not maxdb expert) and I'm trying to support our customer

Also I see:

dbmcli on BWP>db_online -t

ERR

-24994,ERR_RTE: Runtime environment error

1,kernel program missing '/sapdb/BWP/db/pgm/omststknl' (OS error code 11000)

Regards,

Michal

Edited by: Michal Sarna on Jan 18, 2011 1:27 PM

[lbreddemann]

Now this is really strange.

Are you sure that the volumes have been correctly entered into the configuration again?

And before the failed volume addition there weren't any issue?

At this point a more detailed analysis needs to be done - or you just restore the database from the last good backup.

The later option is now quite likely the quickest and safest option.

For the analysis of the issue, you need to have a support message for this!

regards,

Lars

[Former Member]

Lars,

I see at the end of file knldiag

2011-01-18 13:22:25 19152 3 RunTime State changed from STARTING to ADMIN

2011-01-18 13:22:25 19168 31 SrvTasks Permanently reserved 37 servertasks for 'Backup / Restore'.

======================================= end of startup part ====================

2011-01-18 13:22:25 19152 11570 startup complete

2011-01-18 13:22:25 19168 31 SrvTasks Permanently reserved 2 servertasks for 'Backup / Restore'.

2011-01-18 13:22:25 19168 31 SrvTasks Permanently reserved 1 servertasks for 'Savepoint'.

2011-01-18 13:22:25 19153 11561 COMMUNIC Connecting T52 local 19134

2011-01-18 13:22:25 19167 11561 COMMUNIC Connected T52 local 19134

-

---

current write position -

---

Hmmm, is it look like the customer has failed recover?

Maybe we should clear db log?

[lbreddemann]

> Hmmm, is it look like the customer has failed recover?

> Maybe we should clear db log?

No, it does not look at all as if a recovery had been tried.

The task/thread-allocation happens always - this isn't an indication of any actual recovery.

And NO! Don't clear the log!

You're going to loose data by this.

Please do open a support message!

regards,

Lars

[Former Member]

Thanks very much Lars!,

What if I have in knldiag.old only data about files

DATA_VOLUME_MODE_0001 to DATA_VOLUME_MODE_0022

and I want to reactivate files from DATA_VOLUME_MODE_0023 to DATA_VOLUME_MODE_0030

I still possible via steps above?

What is going happen If I try to use these command to file 23 (and for other missing) (without having this info in knldiag.old):

param_directput DATA_VOLUME_NAME_0023 /sapdb/BWP/sapdata/DISKD0023

param_directput DATA_VOLUME_SIZE_0023 563200

param_directput DATA_VOLUME_TYPE_0023 F

Is it worth trying?

Best regards,

Michal

Edited by: Michal Sarna on Jan 18, 2011 11:55 AM

[lbreddemann]

>

> I still possible via steps above?

Yes it is.

Technically all you've to do right now is to tell the database that there are additional data volumes and the size, type and location of these data volumes.

Having the information in the old KNLDIAG file would have been only a way to make it easier for you to know the exact values to put in, e.g. the filepaths and the size of the files in DB-pages (8K).

If you're using the same size for all data volumes (as recommended) then it's still pretty easy.

If different filesizes were used you need to find them out before (simply checking the file size in bytes and dividing it by 8192 would do).

> What is going happen If I try to use these command to file 23 (and for other missing) (without having this info in knldiag.old):
> 
> param_directput DATA_VOLUME_NAME_0023 /sapdb/BWP/sapdata/DISKD0023
> param_directput DATA_VOLUME_SIZE_0023 563200
> param_directput DATA_VOLUME_TYPE_0023 F
> 
> Is it worth trying?

More then that - it's currently the only way (except restoring from backup) to get the database online again.

BTW: as it seems you're a SAP customer.

If this is true, you probably should open a support message for this as I can only answer your forum thread in my 'spare' time.

Serious issues like this really should be handled in a support message.

regards,

Lars

[Former Member]

Hi Michal,

Is it resolved?

Have you checked for the file permission?

Thanks.

Regards,

Siva

[Former Member]

It look like the previounsly created paramconfig was very old

We have now:

dbmcli on BWP>db_online

ERR

-24988,ERR_SQL: SQL error

-902,I/O error

3,Database state: OFFLINE

6,Internal errorcode, Error code 9050 "disk_not_accessible"

20048,data volume configuration corrupted: Successor of volume 22 should be 23

20017,RestartFilesystem failed with 'I/O error'

Is it possible to attach all other datafiles (from 23 to 30) back to database?

[lbreddemann]

Hmmm... could it be that you never ever restarted the database after adding the volumes?

This would explain why the old parameter file didn't contain the volume definition.

To add those missing volumes now will be a bit of manual work, as no "last good parameter file" is available anymore.

If you still have a KNLDIAG.OLD file in which all of the volumes are listed at the beginning of the file (where all parameters are dumped during instance startup), it's not going to be difficult.

DATA_VOLUME_MODE_0001=NORMAL
DATA_VOLUME_MODE_0002=NORMAL
DATA_VOLUME_MODE_0003=NORMAL
DATA_VOLUME_NAME_0001=C:\sapdb\data\DB760\data\DISKD0001
DATA_VOLUME_NAME_0002=C:\sapdb\data\DB760\data\DISKD0002
DATA_VOLUME_NAME_0003=C:\sapdb\data\DB760\data\DISKD0003
DATA_VOLUME_SIZE_0001=6400
DATA_VOLUME_SIZE_0002=6400
DATA_VOLUME_SIZE_0003=6400
DATA_VOLUME_TYPE_0001=F
DATA_VOLUME_TYPE_0002=F
DATA_VOLUME_TYPE_0003=F

Let's assume volume 3 would be missing now.

In that case you'd need to run these three commands to add them again to the parameter configuration:

param_directput DATA_VOLUME_NAME_0003 C:\sapdb\data\DB760\data\DISKD0003
param_directput DATA_VOLUME_SIZE_0003 6400
param_directput DATA_VOLUME_TYPE_0003 F

This needs to be done for all the missing data volumes. Just make sure to get the numbering in the parameter names correct and not to mix up the volumes.

After you've added all the parameters you should be able to review the volume setup via

param_getvolsall

!!!!ATTENTION!!! BE VERY CLEAR ABOUT THAT THIS IS AN EMERGENCY PROCEDURE AND DOES IN NO WAY APPLY TO STANDARD OPERATIONS!

IF YOU USE THESE COMMANDS AND F***K UP YOUR DB WITH THAT, THEN NO SUPPORT WILL BE GRANTED.

(just a disclaimer for the people reading this instead of the documentation to learn about MaxDB administration!)

regards,

Lars

[lbreddemann]

Looks like you're using a pretty old version since this faulty behavior (changing the volume parameter configuration before actually adding the volume and not setting it back if this failed) was fixed a long time ago,

To get back to configuration from before the faulty data volume addition, you can use the last stored parameter config file which has been automatically stored:

check current config:

dbmcli -d db76 -u control,<pw>


dbmcli on db76>param_getvolsall
OK
LOG_MIRRORED                     NO
MAXLOGVOLUMES                    2
MAXDATAVOLUMES                   5
LOG_VOLUME_NAME_001              6400       F  C:\sapdb\data\DB76\log\DISKL0001
DATA_VOLUME_NAME_0001            3200       F  C:\sapdb\data\DB76\data\DAT_0001
DATA_VOLUME_NAME_0002            3200       F  C:\sapdb\data\DB76\data\DISKD0002

Check parameter history files:

dbmcli on db76>param_versions
OK
C:\sapdb\data\config\DB76
C:\sapdb\data\config\DB76.01
C:\sapdb\data\config\DB76.02
C:\sapdb\data\config\DB76.03
C:\sapdb\data\config\DB76.04
C:\sapdb\data\config\DB76.05
C:\sapdb\data\config\DB76.06
C:\sapdb\data\config\DB76.07
C:\sapdb\data\config\DB76.08
C:\sapdb\data\config\DB76.09
C:\sapdb\data\config\DB76.10

restore old parameter file:

param_restore 1
OK

check volume config again:

dbmcli on db76>param_getvolsall
OK
LOG_MIRRORED                     NO
MAXLOGVOLUMES                    2
MAXDATAVOLUMES                   5
LOG_VOLUME_NAME_001              6400       F  C:\sapdb\data\DB76\log\DISKL0001
DATA_VOLUME_NAME_0001            3200       F  C:\sapdb\data\DB76\data\DAT_0001

This pretty much should do the trick.

For all details about the commands used, feel free to check the documentation

regards,

Lars