Reasons for Error 452: Invalid Backup_ID?

[former_member314792]

I'm investigating how to clean up (clean out) the backup/log/ directory on my HANA system after a log-full occurrence.  I worked thru the instructions in V.Srinivasan's post on moving the folders, but the ALTER SYSTEM RECLAIM LOG didn't do anything for the backups.

In looking at the m_backup_catalog view, there are many entries that I'd like to purge but if I enter the backup_id numbers shown (minus the commas of course) when issuing a BACKUP CATALOG DELETE command, I always get an "Invalid Backup_ID" error back.  Is there a trace I can look at for the underlying checks that lead to this error?  The backup catalog itself has over 300,000 entries in it (with a slightly smaller but similar count in backup_catalog_files) and this seems excessive.

I'm also investigating the backup script that's been created and contributed (message thread 15594201) but am worried that if that leads to issuing the same commands the same error will occur.

The database in question is still in pre-production mode, so I'm running log_mode=overwrite and have no value in the log backups that have been taken, so I'd be satisfied if there was a way to just flush the old backup logs and go forward.  I've seen all the dire warnings regarding using operating system commands to remove files, so I have not gone down that path.

I do have data backups and a storage snapshot from the current state of the system.

Thanks in advance for any insights or pointers,

Donn

[lbreddemann · Accepted solution]

Hi Donn,

looking at that you might want to get rid of 300K entries in the backup catalog which you are not going to need, I'd say you would be better off by simply starting over with the backup history.

Just be careful about not mixing up log files (which is what ALTER SYSTEM RECLAIM LOG works on) and log backups.

In order to start over, you would:

• shutdown the database cleanly
• move (rather than delete) the log backup files on file system level
• move (rather than delete) the backup catalog files (log_backup_0_0_0_0.xxxxxxxx in your log backup folder)
• move (rather than delete) the backup.log file from the traces folder
• startup the database again
• take a full data backup

Of course you could use the CATALOG DELETE command, but given the amount of backups you need to remove, starting over seems to be more practical.

Once the system is up and running and writing off log backups as usual, you can go on and delete the files you moved before.

And as always: make sure your backup works before you need it!

- Lars

[former_member314792 · Accepted solution]

Lars,

   I appreciate your approach to cleaning up the backup logs, and will probably do just that.

First, however, I'd like to understand a little of how HANA is managing its backup catalog.  If there is a reference that documents these internal-y things, I'd very much like to review it.

It sounds like upon startup, the database statisticsserver is actually reading from the newest valid log_backup_0_0_0_0.xxxxxxxxx (this latter being the timestamp or backup_id) in order to reconstitute the views?  As you can probably tell, I'm new to the HANA world and am trying to figure out all the moving pieces!

Much thanks,

Donn

[lbreddemann · Accepted solution]

Well, the exact mechanics for this are not documented, so you'll have to go by the documentation.

To ensure that what I wrote before is actually working I tried that out and found that there is another in-memory version of the backup catalog that is kept.

This version is apparently used for the general management of backups while the system is up and running, while the log_backup... files are used when the DB is offline (makes sense).

In order to get rid of the entries in the in-memory catalog the only option really is to use the

BACKUP CATALOG DELETE command.

(Which means, that you could also spare all of the commands before; though, maybe it's still faster to do it like that)

A caveat with the BACKUP CATALOG DELETE command is that you cannot just throw any backup id at it, but it has to be a data backup.

For example

select max(backup_id)  from m_backup_catalog where entry_type_name ='complete data backup';

gets me the last data backup (be careful, I'm not checking for whether it was successful or not here!)

MAX(BACKUP_ID)

1436417422448

Now, I can take this backup_id and use it in the command:

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID 1436417422448 COMPLETE;

That worked fine.

However, when I tried to use a log backup_id, I also received

Could not execute 'BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID 1436419998256 COMPLETE' in 308 ms 70 µs .

SAP DBTech JDBC: [2]: general error: Backup error: Invalid backup id '1436419998256'

That should do the trick.

Generally, if you want to learn more about SAP HANA administration, the official documentation is getting better and better by the SPS.

Beyond that, I like to point to and - in order to recommend my and Richard's book.

- Lars

[former_member314792 · Accepted solution]

Lars,

Your method for obtaining then using the correct backup-id worked superbly.  My catalog is now pruned!

I think that requiring the complete-data-backup as the valid checkpoint was what was happening before and explains the invalid id message from what looks like a good id, because I was trying to use a log-backup entry.

I'll check out the books too.

Much thanks, again,

  Donn

[hillmw]

This looks like a great method of resetting the log backups, but what is the recommendation for "on-going" activity?

Is this seen as a regular event? or is the regular event:

1) do a complete backup

2) reclaim the space using - ALTER SYSTEM RECLAIM LOG;

Thanks for your time.

Mike