Configuration of Sybase Database #
All the following command will be used for configuring or consulting a SAP Database D04, please replate D04 with your database SID.
Connect to database via ISQL command line tool #
d04adm$ isql -Usapsa -SD04 -X
Check Database Page Size #
This value will be used when creating dump configuration settings.
1> select @@maxpagesize
2> go
-----------
16384
Activate database logging #
In order to be able to recover a database with Point In Time options, yu must activate database logging.
1> exec master..sp_dboption D04, 'trunc log on chkpt', false
2> go
Database option 'trunc log on chkpt' turned OFF for database 'D04'.
Running CHECKPOINT on database 'D04' for option 'trunc log on chkpt' to take
effect.
(return status = 0)
Dumphistory File #
Every single time Sybase finishes a DUMP action, Dump History catalog is updated. This file holds the actual backup catalog for current database.
This file will be located at $SYBASE/$SYBASE_ASE/dumphist file. This file should not be edited. ex: /sybase/D04/ASE-16_0/dumphist
Enable Dumphistory File #
1> use master
2> go
1> sp_configure 'enable dump history', 1
2> go
1> sp_configure 'dump history filename', 'dumphist'
2> go
Show Dumphistory configuration #
1> sp_configure 'dump history filename'
2> go
Parameter Name Default Memory Used Config Value Run Value Unit Type
--------------------- ----------- ----------- ------------ ------------ -------------------- --------------------
dump history filename dumphist 0 dumphist dumphist name dynamic
1> sp_configure 'enable dump history'
2> go
Parameter Name Default Memory Used Config Value Run Value Unit Type
--------------------- ----------- ----------- ------------ ------------ -------------------- --------------------
enable dump history 0 0 1 1 switch dynamic
Show Dumphistory #
1> sp_dump_history
2> go
DUMP
----
Dump_Type Dbid Database_name Stripes Dump_instant File Server_name Compression_lvl Password Status Label Dump_date
----------- ------ -------------- ------- --------------------------- ------------------------------------------------- ----------- --------------- -------- -------- ----- --------------------------
DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.000 * 0 no Success * May 22 2020 9:41:33:096AM
DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.001 * 0 no Success * May 22 2020 9:41:33:096AM
DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.002 * 0 no Success * May 22 2020 9:41:33:096AM
DATABASE 4 D04 4 May 22 2020 9:41:29:070AM emory::compression=-2::D04.DB.20200522.093515.003 * 0 no Success * May 22 2020 9:41:33:096AM
DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.000 * 0 no Success * May 22 2020 11:29:29:353AM
DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.001 * 0 no Success * May 22 2020 11:29:29:353AM
DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.002 * 0 no Success * May 22 2020 11:29:29:353AM
DATABASE 4 D04 4 May 22 2020 11:28:44:076AM emory::compression=9::D04.DB.20200522.105442.003 * 0 no Success * May 22 2020 11:29:29:353AM
DATABASE 4 D04 0 May 22 2020 11:30:06:163AM dump.error * 0 no Error May 22 2020 11:30:06:163AM
DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.000 * 0 no Success * May 22 2020 11:34:53:513AM
DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.001 * 0 no Success * May 22 2020 11:34:53:513AM
DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.002 * 0 no Success * May 22 2020 11:34:53:513AM
DATABASE 4 D04 4 May 22 2020 11:34:45:076AM emory::::D04.DB.20200522.113046.003 * 0 no Success * May 22 2020 11:34:53:513AM
CONFIG
------
ALTER DATABASE
--------------
(return status = 0)
Purge Dumphistory file #
With this command we will be able to purge Sybase backup catalog (there is no reaseon for doing this)
1> use master
2> go
1> sp_dump_history @operation = 'purge'
2> go
Dump Profiles #
In order to make it easier for backup adminsitrator to launch backups with specific settings, it is a best practice to create dump profiles.
We can create as many Dump Profiles as we want, each one with different settings. This can be useful when we want to use, for example, one profile for Full Backups, other for Incremental Backup, another one for logs backups, and so on.
To perform these operations connect to the database using iSQL as sybsid user.
isql -Usapsa -S<SID> -X
Options to be configured:
- config_name : The name of the current configuration. This name will be used on dump actions
- ext_api : streamidentifier + :: + vendor_specific_information string
- emory::[compression=#compression_value]
- #compression_value - Compresion level to be used on dump actions
- 0 : No Compression
- 1 : Best Speed, worst compression level
- 2
- 3
- 4
- 5
- 6 : [Default compression]
- 7
- 8
- 9 : Best Compression, slowest
- -1 : Default compression
- -2 : HuffmanOnly
HuffmanOnly disables Lempel-Ziv match searching and only performs Huffman entropy encoding. This mode is useful in compressing data that has already been compressed with an LZ style algorithm (e.g. Snappy or LZ4) that lacks an entropy encoder. Compression gains are achieved when certain bytes in the input stream occur more frequently than others. Note that HuffmanOnly produces a compressed output that is RFC 1951 compliant. That is, any valid DEFLATE decompressor will continue to be able to decompress this output.
When using compression settings on dump configurations, as compression is CPU intensive process, we should not use more stripes than the number of vCPU that our server has.
If we do not use compression settings, the recomendation is to use 2 or 3 stripes per CPU
- [num_stripes] : Number os stripes to be used in parallel (max 32)
- [blocksize]: Specify the block size to override the default value for all dump devices. The block size must be at least one database page and must be an exact multiple of the database page size.
Examples:
Configure dump profile to be used for Backups on system with 4 vCPUs with no compression
1> use master
2> go
1> sp_config_dump
@config_name = 'emory_S8',
@ext_api = 'emory::',
@num_stripes = '8',
@blocksize = '16384'
2> go
Configure dump profile to be used for Backups on system with 8 vCPUs with compression
1> use master
2> go
1> sp_config_dump
@config_name = 'emory_S8_C5',
@ext_api = 'emory::compression=5',
@num_stripes = '8',
@blocksize = '16384'
2> go
Configure dump profile to be used for Log backups on system with 4 vCPUs with compression
1> use master
2> go
1> sp_config_dump
@config_name = 'emory_S4_C1',
@ext_api = 'emory::compression=1',
@num_stripes = '4',
@blocksize = '16384'
2> go
List all configurations
1> use master
2> go
1> sp_config_dump
2> go
dumpconfig
------------------------------------------------
emory_S8
emory_S8_C5
emory_S4_C1
View specific settings
1> use master
2> go
1> sp_config_dump emory_S8_C5
2> go
optionname optionvalue
----------------- ---------------------
ext_api emory::compression=5
num_stripes 8
blocksize 65536
You can also configure this settings using SAP Control Center Application