Recovering SAP ASE for custom SAP Applications

[Former Member]

I am involved currently in a project involving setting up standardized recovery procedure for several SAP applications (Windows & Unix).  I have come across a couple of inconsistencies in SAP documentation which I think should be clarified in order to avoid missing up with recovery procedure.

[a]  On the one hand Database Administration Guide - version 1.4 2014-07-16 for SAP Business Suite on SAP Adaptive Server Enterprise (6.1 Ensuring Recoverability for the SAP ASE Server, p. 90) states:

"Create a file system backup for the file system /sybase/<DBSID> (Unix) and <Drive>:\sybase\<DBSID> (Windows) at regular intervals. The database does not have to be offline for this. Exclude the following database devices from the backup:

/sybase/<DBSID>/sybsystem

/sybase/<DBSID>/sapdiag

/sybase/<DBSID>/sapdata_<n>

/sybase/<DBSID>/saplog_<n>" 

On  the other hand, SAP note 1618817 - SYB How to restore a Sybase ASE database server (UNIX) or 1611715 - SYB How to restore a Sybase ASE database server (Windows) say:

"Recreate the file systems as required for the following directories:

/sybase/<SID>/sapdata_[1-n]

/sybase/<SID>/saplog_[1-n] (*)

/sybase/<SID>/sybsystem

/sybase/<SID>/sapdiag

/sybase/<SID>/sybtemp

/sybase/<SID>/saptemp

Remark: If the file system(s) /sybase/<SID>/saplog_[1-n] have not been corrupted, the devices files will probably hold transactional data that has not yet been dumped to a transaction log dump. Perform an emergency dump of the transaction log in this case (see below). Make sure you retain a good offline copy of the device files before you recreate the file systems /sybase/<SID>/saplog_[1-n] "

If one follows the BACKUP recommendation guide documented by SAP in Database Administration Guide, /sybase/<SID>/saplog_[1-n] will not be available to recover anything from saplog_xxx since that file system was not backed up in the first place so upon recovering FILESYSTEM the files will not be there at all.

[b]  On the one hand 1618817/1611715 note require to [1] restore File System (which contains everything except system and user databases).  [2] recreate ASE using resource files.  On the other hand sap installation using SWPM does not leave resource files present after it cleans up.  If you follow the installation log you may see the following:

sapinst.log:Creating file /tmp/sapinst_instdir/NW740SR1/SYB/INSTALL/STANDALONE/STD/PICP/sqlsrv.res.

...

sapinst.log:Removed file /tmp/sapinst_instdir/NW740SR1/SYB/INSTALL/STANDALONE/STD/PICP/sqlsrv.res.

sapinst.log:Creating file /tmp/sapinst_instdir/NW740SR1/SYB/INSTALL/STANDALONE/STD/PICP/sqlsrv_lang.res.

...

sapinst.log:Removed file /tmp/sapinst_instdir/NW740SR1/SYB/INSTALL/STANDALONE/STD/PICP/sqlsrv_lang.res.

Both files are created and removed by the installation - they never leave /tmp.

Excluding /sybase/<DBSID>/sybsystem is also pretty murky thing to ask.  /sybase/<DBSID>/sybsystem contains system devices for master, sybprocs, sybmgmtdb & sybsystemdb.  They are pretty small.  Why excluding them from the file system backup at all?  I think it might have been wiser to back them up as well in the general FS backup procedure, restore them on the file system level and then start up ASE normally - rather than recreating ASE from scratch using the missing resource files.  If the master database is corrupt ASE will not start.  In this case you will have to recreate ASE from scratch using resource files. If ASE does start up normally - if the hard copy of master database is more recent than the file system image - follow the load master procedure (always an uneasy one).  If the hard copy is out of date - skip loading master database altogether and load only user databases.   Recreating ASE from resource file, in addition, will require setting back all the customized ASE setting which were not included in the backup procedure and are not specified in the SAP note.   I think it would be a good practice to save a hard copy of the last ASE configuration file as well - or at least to copy the one restored by file system recovery BEFORE ASE server has been recreated by srvbuild (which will overwrite the configuration file)..

[c]  Recovering sapdiag databases too may be simplified.  Instead of dropping DB + DEVICES straight away, check if the hard copy of the DB is in sync with the DB structure (done easily using combination of sp_helpdb and load database with headeronly) and just load the database.  Recovering user databases may be done in the same manner, unless sapdata_xxx and saplog_xxx were not backed up (after the confusing documentation in [a] has been cleaned up).

Can anyone from SAP ASE team clarify on this and make documentation more straightforward?

Thanks,

Andrew

[tilman_model-bosch]

Dear Mr Melkonyan,

The DBA Guide for SAP on ASE is undergoing review at the moment and the inconsistencies you found will get fixed. Thanks for reporting these.

We will also adapt the above mentioned notes, as it has been found that a complete restore requires additional steps when using DB autoexpansion. (you have to either restore sybsystemprocs or re-run installdbextend and reset the defaults for auto-expansion)

Wrt to backup of sybsystem directory. It is true that the contents of that directory is rather small and in most cases a file system copy of the database devices files conatined therein will be consistent - i.e. will be useful for recovering a corrupted ASE server .
Nevertheless - this is not guaranteed . To get a consistent files system backup of the system databases devices one would have to quiesce these databases (see SAP note 1887068 - SYB: Using external backup and restore with SAP Sybase ASE on how to backup and restore databases using external copy methods) .

Of course it is a valid backup and restore concept to quiesce system databases and use FS backup to backup and restore these and rely on traditional online DUMP DATABASE method for the other databases).

With kind regards
Tilman Model-Bosch

[Former Member]

Dear Mr Model-Bosch,

Could it be possible to have a look at the draft of that document?  I will have to finish up the backup and restore procedure for the customer by the end of this week.  I would like to be sure I do not deviate from the recommended procedure too far.

Thank you,

Andrew

[tilman_model-bosch]

Dear Mr Melkonyan

I apologise for late reply - have been a bit of a busy week .

Basically we have been alerted that - if you use automatic database expansion then for a complete server level restore you also need to dump and load sysbsystemprocs - or to re-configure automatic database expansion after the restore. 
Also - with newer SAPINST versions the installer adds user 'SAPSR3' to the model database - so that this user is always known in the temp databases. This change to the model databases is also lost after a fiull server restore.
So we will add that in notes accordingly  - I expect these notes to be released back with refreshed content today or early next week.

With kind regards

Tilman Model-Bosch

[Former Member]

There are more issues with that.  If you use DBA cockpit, e.g., after you restore the database and the automatic expansion, DBA cockpit displays weird information on t he setting you have in place.  The installation notes for integrating ASE into DBA cockpit too is incomplete - it misses some aspects that make it inoperable if not rectified &c &c...

[Former Member]

Actually, the resource files are there in the default $SYBASE/$SYBASE_ASE/init/logs location.   Please ignore the missing resource files note.