
Automating Database Dumps in SAP ASE
This blog post guides you through automating database and transaction log backups for your SAP system using DBACockpit in SAP Sybase ASE version 15.7.0.021 or higher. We'll focus on using the local file system for intermediate storage.
Prerequisites:
Understanding of SAP ASE utilities like dump database and dump transaction.
Steps:
Create a Storage Location:
Configure Dump Settings:
Create a dump configuration file specifying parameters like:
Schedule Database Backups:
Automate Transaction Log Backups (Optional):
Additional Considerations:
Security: Restrict access to the dump directory and configuration files.
Verification: Regularly test your backup process to ensure successful dumps and retrievals.
Rotation: Implement a backup rotation strategy to manage storage space and avoid overwriting critical backups. By following these steps, you can automate database dumps in SAP ASE, ensuring a robust and reliable backup solution.
Creating a Dedicated File System for Dumps:
To safeguard your database against data loss in the event of device failures or filesystem errors, it's crucial to store database dumps on a separate file system that is not located on the same device as the database itself. This crucial practice ensures that your backups remain intact even if the database device becomes inaccessible.
Here's a recommended approach for establishing separate file systems for dumps on UNIX/Linux systems:
Create Directories:
While mounting the file systems within /sybase/<SAPSID> is convenient, it's not a strict requirement. You have the flexibility to choose alternate locations that best suit your specific environment and disaster recovery strategies. Mount separate file systems onto these directories. This isolates the dumps from any issues affecting the database device.
Note: Ensure that your database backup and log archiving processes are configured to write dumps to these designated directories.
Create a dump configuration:
With The SAP ASE 15.7.0.021 and higher you can configure database and transaction log dumps. A dump configuration is stored in the ASE server configuration file and specifies all the necessary configuration parameters for the dump, such as a target directory where dumps are stored, the compression rate, and other parameters.The dump configuration can get created with stored procedure sp_config_dump. It has to be executed from database 'master'
The below example steps shows the necessary commands to create a dump configuration 'SMADB' for database dumps and another dump configuration 'SMALOG' for transaction log dumps.
Configuration SMADB:
use master
go
exec sp_config_dump @config_name='SMADB', @stripe_dir = '/backup/Backup/SOL' , @compression = '101' , @verify = 'header'
go
Configuration SMALOG:
use master
go
exec sp_config_dump @config_name='SMALOG', @stripe_dir = '/backup/Backup/SOL/SMALOG' , @compression = '101' , @verify = 'header'
go
The dump configurations SMADB and SMALOG are created. The target directories for storing dumps are specified. Compression = 101 is used for the dumps, and the header option is specified.
Database and transaction dumps can now be performed using the using config syntax
Example Config Syntax:
dump database SMA using config = 'SMADB'
Dumps database SMA using dump configuration SMADB.
dump transaction SMA using config = 'SMALOG'
Dumps transaction log of database SMA using dump configuration P01LOG.
If you want to ensure that database dumps are only performed using the defined dump configuration, set the ASE configuration parameter enforce dump configuration to 1.
Data backup:
Log Backup:
Note: Here we disabled the log in this system. But we configured syntax working fine.
Create and schedule ASE jobs to dump databases and transaction logs:
Use the DBA Planning Calendar in the DBACOCKPIT to automatically call the DUMP DATABASE and the DUMP TRANSACTION commands.
In order to minimize the potential for transaction loss in the case of a disk error, SAP recommend that you schedule a DUMP TRANSACTION command frequently in a production environment.
After completion of the wizard the newly scheduled job shows up in the DBA Planning calendar.
Follow the same steps to take SMALOG backup or schedule the LOG backup regular interval based on you requirement.
Set up a threshold action for the transaction log:
Also we need to setting up a scheduled DUMP TRANSACTION command, it is also strongly recommended that you set up a threshold action that triggers a DUMP TRANSACTION command whenever a threshold (fill level) in the log segment has been reached.
Please refer this snote for and setting up log threshold 1801984
Note: Test to restore your SAP system frequently. Do not just assume you have successfully backed up your data!
Thanks!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
24 | |
23 | |
9 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |