Running db_migratecatalog crashes with SYSERROR -9400 AK Cachedirectory ful

[simon_matter]

I'd like to upgrade our main instance from 7.6.05.09 to 7.7.06.09. I tried to test the process on a test system where I installed 7.6.05.09 and restored our main instance from a full backup. So far it looked fine and a db check showed no errors.

Now I wanted to migrate the catalog with db_migratecatalog which results in the following:

2009-05-23 15:44:27                          --- Starting GMT 2009-05-23 13:44:27           7.6.05   Build 009-123-191-997 
2009-05-23 15:44:55  5141 ERR 51105 AK CACHE AK00001.dmp     written: 0
2009-05-23 15:44:55  5141 ERR 51382 MIGRAT   MIGRATION FAILED WITH ERROR -9400
2009-05-23 15:44:55  5141 ERR 51080 SYSERROR -9400 AK Cachedirectory full
2009-05-23 15:44:56                          ___ Stopping GMT 2009-05-23 13:44:56           7.6.05   Build 009-123-191-997

I'm stuck, I just can't make it work whatever I try

Any help to find out how to go on is greatly appreciated.

Regards,

Simon

[lbreddemann]

> I'd like to upgrade our main instance from 7.6.05.09 to 7.7.06.09. I tried to test the process on a test system where I installed 7.6.05.09 and restored our main instance from a full backup. So far it looked fine and a db check showed no errors.

>

> Now I wanted to migrate the catalog with db_migratecatalog which results in the following:

Why the heck do you want to do that?

the [db_migrate|http://maxdb.sap.com/doc/7_7/45/818434546140bfe10000000a1553f7/content.htm] command is used to change the database catalog from ASCII to UNICODE and not to perform an upgrade of your database.

If you've installed the source database initially with 7.6 you won't need to do this, as the catalog is already stored in UNICODE.

> I'm stuck, I just can't make it work whatever I try

Why don't you just use the standard procedure for performing an upgrade?

Use SDBSETUP or SDBUPD to upgrade your old instance - it will do all the necessary steps for you.

regards,

Lars

[simon_matter]

> I'd like to upgrade our main instance from 7.6.05.09 to 7.7.06.09. I tried to test the process on a test system where I installed 7.6.05.09 and restored our main instance from a full backup. So far it looked fine and a db check showed no errors.
> 
> Now I wanted to migrate the catalog with db_migratecatalog which results in the following:
Why the heck do you want to do that?
the [db_migrate|http://maxdb.sap.com/doc/7_7/45/818434546140bfe10000000a1553f7/content.htm] command is used to change the database catalog from ASCII to UNICODE and not to perform an upgrade of your database.
If you've installed the source database initially with 7.6 you won't need to do this, as the catalog is already stored in UNICODE.

Hi Lars,

Thanks for your suggestions. From how I understand it our instance is using ASCII catalog. We started using the db in the old days of Adabas D. The instance has since been upgraded many many times. However once in time we migrated the db using our inhouse Java based synchronization tool. That may have been in the time of SAPDB 7.3 or so. Since then we always upgraded by doing migration backup/restore with older SAPDB releases and now with newer MaxDB releases using SDBINST/SDBUPD.

> I'm stuck, I just can't make it work whatever I try 
Why don't you just use the standard procedure for performing an upgrade?
Use SDBSETUP or SDBUPD to upgrade your old instance - it will do all the necessary steps for you.

That's exactly what I tried. I installed the new server using SDBINST and then wanted to migrate every instance using SDBUPD. Here comes the problem, SDBUPD refuses to do it's job because it tells me I have to migrate the catalog to UNICODE first. That went well with our TST instance but didn't work with our main productive instance.

I'd love to learn that I simply missed something but I'm afraid that's not the case.

Regards,

Simon

[lbreddemann]

Hi Simon,

ok, in this case we need to dig a little deeper.

Can you provide a download link to the KNLDIAG file? The KNLDIAG.ERR is not enough here.

regards,

Lars

[simon_matter]

Hello Lars,

I have put the gzipped knldiag file here:

[http://www.invoca.ch/pub/maxdb/knldiag.gz|http://www.invoca.ch/pub/maxdb/knldiag.gz]

I hope it's the correct file because it appears not to contain much information regarding the error to me.

Regards,

Simon

Edited by: Simon Matter on May 25, 2009 5:00 PM

[former_member229109]

Hello Simon,

1."That's exactly what I tried. I installed the new server using SDBINST and then wanted to migrate every instance using SDBUPD. Here comes the problem, SDBUPD refuses to do it's job because it tells me I have to migrate the catalog to UNICODE first. That went well with our TST instance but didn't work with our main productive instance."

Using the SDBINST tool you could install the database software. I assumed that you created instance later using DBMGUI, for example.

And you set the database parameter _UNICODE to NO at the step if the initialization of the database parameters

During the creation of the database instance of the version < 7.7 you could set

the database parameter _UNICODE to the value YES or NO:

"param_getexplain _UNICODE

OK

_UNICODE = 'YES' or 'NO'

'YES': Identifiers of database objects are stored in UNICODE

'NO' : Identifiers of database objects are stored in ASCII

After the installation of the SERVERDB this parameter can only be modified

from "NO" to "YES" by using a special tool."

As I understood you had created the databackup of the nonunicode database

=> you created the nonunicode database instance for the restore.

It's correct procedure.

As of database version 7.7 the database parameter _UNICODE could be

YES only:

"param_getexplain _UNICODE

OK

'YES': Identifiers of database objects are stored in UNICODE "

When you run the database upgrade to 7.7.06.09 using SDBUPD tool,

the _UNICODE parameter will be switched to YES and catalog migration step will be run in source upgrade database release < version 7.6.05.09 >.

2. The database parameter to increase the catalog cache (AK cachedirectory) is CAT_CACHE_SUPPLY.

I recommend you to increase the value of the database parameter CAT_CACHE_SUPPLY before the upgrade starts.

Bring the database to offline mode, increase the value of the database parameter CAT_CACHE_SUPPLY from 3264 to 20000. Restart the database upgrade using SDBUPD tool to the version 7.7.06.09.

Please give the feedback.

3. Are you SAP customer?

Thank you and best regards, Natalia Khlopina

[simon_matter]

Hello Natalia,

1) The instance I want to migrate has been configured with _UNICODE=NO because at the time it was created it was the default.

I learned that migrating an instance with ASCII catalog from 7.6 to 7.7 requires to do a db_migratecatalog while still running on 7.6. So far that all seems consistent with what you describe but that's where it fails.

2) Now, I have already tried to increase CAT_CACHE_SUPPLY with no success. I have now tried it again and set it from 3264 to 50000 but db_migratecatalog still crashes the same way.

3) We are not SAP customer because we are using MaxDB in a non SAP environment.

I've tried to narrow things down yesterday and it seems our instance has some issues which are not so easy to fix. Please note that this instance has lived since the SAPDB 7.3 or 7.4 days or so and has been upgraded many times.

For now I found the following:

- There are a number of tables which seem broken. They can not be read completely without error. However, all of them are not used anymore and we'll just try to remove them (if that still works).

- A number of tables have columns which are named with names which are not allowed anymore with 7.7. Like we have a column ACTION or PUBLIC and so on. We'll rename those so that they are valid again.

- There seems to be some air in the DB which can not be freed. Note that the instance has been around 50Gb of size some time ago but we reduced it to around 2.5Gb by removing a large number of binary data from it. But still if I copy all tables to a new instance it only grows to 2.1Gb. So there seems to be some 400Mb which are allocated somehow but not really used.

Apart from that the instance seems to work quite well. Also, checking the DB does not show anything wrong.

Maybe it's really better for us to simply copy all tables to a completely new instance to get rid of all the brokenness we have in the instance right now.

Do you have any other suggestions?

Regards,

Simon

[former_member229109]

Hello Simon,

1. What is the current status of the database upgrade?

Please update with output of the commands:

dbmcli -s inst_enum

dbmcli -s db_enum

What is the value of the database parameter _UNICODE?

2.

According your knldiag file the _UNICODE parameter is set to YES,

DEFAULT_CODE set to ASCII, but the catalog migration step was NOT finished. Correct?

I assumed that you created the complete databackup before you started the database upgrade to the version 7.7.06.09 and before run "db_migratecatalog". And you are running the database version 7.6.05.09.

Please run the following steps:

A) Stop the database. Change database parameter _UNICODE to NO

For example, using dbm command:

param_directput _UNICODE NO

Restart the database in the admin mode & check the values of the database parameters DEFAULT_CODE & _UNICODE in the knldiag file.

B) Initialize the database for the restore, for example, using DBMGUI and continue with restore of the databackup created before you run "db_migratecatalog".

C) Restart the database & load system tables.

Run the database "check data"< Verify >. Check the BAD INDIZES if the new has to be recreated.

2. If SDBUPD during the upgrade from 7.6.05.09 to 7.7 asked to migrate the catalog to UNICODE first

==>

To perform the migration of the database version 7.6.05.09 please run:

• Create the complete databackup;

• Bring the database to the offline status;

• Increase the database parameters CAT_CACHE_SUPPLY to 50000

CACHE_SIZE to 125000

• Run the database catalog migration using the dbm command db_migratecatalog.

Please check, that the migration is not terminated.

• The database will be in online status after the catalog migration finished successfully.

• please send the knldiag file if the catalog migration failed.

3. You wrote:

"Please note that this instance has lived since the SAPDB 7.3 or 7.4"

If you used SDBUPD when migrating from Release 7.3.00 to at least Release 7.4.03, the database catalog is automatically converted to Unicode and the _UNICODE parameter is then set to "YES" at the end of the process.

Do you know what version of the database was installed initially? How the database was upgraded to the version 7.6.05.09?

Thank you and best regards, Natalia Khlopina

Edited by: Natalia Khlopina on May 27, 2009 9:20 PM

[simon_matter]

Hello Natalia,

I tried everything you asked me to do but it doesn't work.

dbmcli -s inst_enum

OK

7.6.05.09 /opt/sdb/7605

7.7.06.09 /opt/sdb/7706

dbmcli -s db_enum

OK

OLD /opt/sdb/7605 7.6.05.09 fast running

OLD /opt/sdb/7605 7.6.05.09 quick offline

OLD /opt/sdb/7605 7.6.05.09 slow offline

OLD /opt/sdb/7605 7.6.05.09 test offline

About UNICODE:

Maybe UNICODE was set to yes when "dbmigratecatalog" was run. I have now

recreated the instance from backup of our pruduction server and it has:

_UNICODE NO

DEFAULT_CODE ASCII

OK, I have put the instance to admin mode and knldiag shows this:

2009-05-29 10:31:30 6259 20235 RTE _UNICODE=NO

2009-05-29 10:31:30 6259 20235 RTE DEFAULT_CODE=ASCII

Now trying to use SDBUPD:

...

MaxDB instance "OLD" not ready to update

finding migration strategy...

migration not allowed

from 7.6.5 Build 009-123-191-997 ASCII catalog

to 7.7.06

Upgrade to releases >= 7.7 with ASCII catalog not allowed. Please perform the unicode migration of the database catalog first.

OK, that's expected, so:

root@delta64 maxdb-all-linux-64bit-x86_64-7_7_06_09]# /opt/sdb/programs/bin/dbmcli -d OLD -u DBM,DBM db_offline

OK

[root@delta64 maxdb-all-linux-64bit-x86_64-7_7_06_09]# /opt/sdb/programs/bin/dbmcli -d OLD -u DBM,DBM param_checkall

OK

[root@delta64 maxdb-all-linux-64bit-x86_64-7_7_06_09]# /opt/sdb/programs/bin/dbmcli -d OLD -u DBM,DBM db_migratecatalog

ERR

-24988,ERR_SQL: SQL error

-9400,System error: AK Cachedirectory full

3,Database state: OFFLINE

No change

Well, we were using the RPM packaged version until 7.5.something. That means we were

unable to perform upgrades using SDBUPD but were forced to do backup/restore.

Maybe that's why our instance still has ASCII catalog?

I think when we migrated to 7.3, we have created a new instance and migrated

all data with our Java tool. So, our DB is much older but the current instance

has been created with 7.3 IIRC.

As stated above we were using the RPM version of the database for many years

and maybe that's the reason some steps were not performed which would have been

performed when using SDBUPD.

Simon

[former_member229109]

Hello Simon,

Please post the new knldiag, knldiag.err, dbm.prt, dbm.utl files after you run "db_migratecatalog".

Thank you and best regards, Natalia Khlopina

[simon_matter]

Hi Natalia,

I have posted a tarball with the files here:

[MaxDB files|http://www.invoca.ch/pub/maxdb/maxdb.tar.gz]

Regards,

Simon

[simon_matter]

Hello Natalia,

we have decided to create a new instance and copy all data via out dpcopy tool. However, the new instance is crashing when starting our appserver. I posted a new message about it.

Thanks,

Simon

[former_member229109]

Hello Simon,

I think, that you hit the known problem, see PTS in more details:

http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID=1175196

The problem will be solved with one of the next MaxDB patches.

Could you please run the following steps::

< In source database version 7.6.05.09.

1. A) Stop the database. Change database parameter _UNICODE to NO

For example, using dbm command: param_directput _UNICODE NO

Restart the database in the admin mode & check the values of the database parameters

DEFAULT_CODE & _UNICODE in the knldiag file.

B) Initialize the database for the restore, for example, using DBMGUI and continue with restore of the databackup created before

you run "db_migratecatalog".

C) Restart the database & load system tables.

2. Use Workaround:

Disable QueryRewriterules by updating QUERYREWRITERULES table

Using SQLStudio or DB studio, for examle, connect to the database as Sysdba (SUPERDBA by default) user and execute the following SQL statement:

UPDATE QUERYREWRITERULES

SET ACTIVE = 'NO'

WHERE RULENAME IN ('AddLocalPredicates','NormalizePredicates')

3. Run the database catalog migration using the dbm command db_migratecatalog.

Please check, that the migration is not terminated.

Please update the thread with the results.

Thank you and best regards, Natalia Khlopina

[simon_matter]

Hello Natalia,

I didn't expect the problem we have with our 7.7 instance is somehow related to this one. But, it makes sense now and I'll redo all my migration tests after enabling those workarounds.

I'm still not sure whether we should migrate or copy the instance. Our instance on 7.6 shows usage of 56%. After copying everything to 7.7 with exactly the same sized volumes, it shows usage of 48%. That seems to show that we have allocated data which is not really used but can not be freed. If that's the case I'd prefer to start with a new instance. What do you think?

(I'll do the migration tests anyway just to report back to this thread)

Regards,

Simon

[simon_matter]

Hello Natalia,

Unfortunately the db_migratecatalog still fails with error 9400 like before. While the workarounds discussed here seem to help the 7.7 instance not to crash, it doesn't seem to help here with the 7.6 instance.

For now I don't try the instance migration again but concentrate on the dbcopy to get our 7.7 instance running.

Regards,

Simon

[former_member229109]

Hello Simon,

You wrote, that the database size is around 2.5Gb. Correct?

Do you have the file database backup of the nonunicode database version 7.6.05.09 still available?

Thank you and best regards, Natalia Khlopina

[simon_matter]

Hello Natalia,

The size ~2.5Gb is true. The backup file of our database is still available in our backup archive. Is there anything you want me to test, just let me know.

Regards,

Simon

[former_member229109]

Hello Simon,

Could you give us link to databackup file, so we could try to reproduce the case on internal server?

Thank you & Best regards, Natalia Khlopina

[simon_matter]

Hello Natalia,

I have to check this but I'm afraid I won't be allowed to post the db backups for security reasons. Any chance I can try something here without handing over our db backup?

Regards,

Simon

[former_member229109]

Could you please create the kernel trace using following steps:

1. Create the nonunicode database instance, database version 7.6.05.09.

Restore the databackup created before you run "db_migratecatalog".

2. Restart the database, load system tables.

Update with output of the SQL statements:

select * from users

select count(*) from tables where owner='<user>'

select count(*) from views

select TRIGGERNAME from triggers

Bring the database to offline.

3. enable the database kernel trace:

/opt/sdb/programs/bin/dbmcli -d OLD -u DBM,DBM trace_on default

4. reproduce the problem by running "db_migratecatalog".

5. deactivate trace

/opt/sdb/programs/bin/dbmcli -d OLD -u DBM,DBM trace_off

6. create the ascii trace protocol

/opt/sdb/programs/bin/dbmcli -d OLD -u DBM,DBM trace_prot abkm

7. now the trace protocol is a available as file

<RUNDIRECTORY>/OLD.prt

please zip this file and give the link in thread to this file.

Please also give link to the current knldiag file.

Thank you and best regards, N. Khlopina

[former_member229109]

Hello Simon,

This case was discussed with database developer.

Please see more details in PTS 1189997 at http://www.sapdb.org/webpts.

You could also check in which version of the database the problem will be fixed later.

In database version 7.6.05.09. you could use given in PTS workaround to run the catalog migration to Unicode.

As workaround, you could use one of the following options:

A) increase CATCACHE_MINSIZE

or

B) perform the migration using slowknl

Please let us know, if you will have further database questions/problems to finish with catalog migration to Unicode.

Thank you and best regards, Natalia Khlopina

[simon_matter]

Hello Natalia,

It's quite some time since we have moved to 7.7 by doing a db copy on the app level instead of upgrading our 7.6 instance.

However I just did a test with an old backup on a test 7.6 box and increased the CATCACHE_MINSIZE. Now I don't get the 9400 anymore but it ends with:

-24988,ERR_SQL: SQL error

-9206,System error: AK Duplicate catalog information:0000000000000416000A0001

3,Database state: OFFLINE

That may be because we had some errors in our DB which are not detected by db check. While migrating to 7.7 we found some errors in out db which we had to cleanup before we were able to do the db copy. I'm quite sure the backup I have used now still contains those errors which is why we get this error.

At least I can confirm that the 9400 did not show up which means increasing CATCACHE_MINSIZE has helped.

Unfortunately I don't have time to debug this for now as we are already running 7.7 and we are fighting some other issues now with the 7.7 version.

BTW, I can't find slowknl in our installations. Is it not part of the community version anymore?

Thanks for your help!

Simon

[Former Member]

Hello Simon

I do not any experience with maxdb/cache but just saw if you can look for this error 'Cachedirectory full', means check if above said dir is full, but not sure where it is

[lbreddemann]

> I do not any experience with maxdb/cache but just saw if you can look for this error 'Cachedirectory full', means check if above said dir is full, but not sure where it is

Beware that the message refers to a database internal directory - there's no "Cachedirectory" in the filesystem of the server that has run out of freespace ...

regards,

Lars