Admin task -3: Advance backup and restore topics in sybase

Backup and Recovery:

To back up the master to a tape device, start isql


enter the command:
                                    dump database master to "tape_device"

Note:
Master database contains the information regarding the entire structure of the database.

Master database act similarly as system database in HANA database.


Automatic backup scheduling process:


command - dbcc to check the consistency and any errors prediction.

* If dbcc detects errors, correct them before dumping the database..

1. create one folder for Backup.
2. run the below commands

su - sybecd
% isql -Usapsa -PWsysmax@756 -SECD -X

1> use master
2> exec sp_config_dump @config_name='ECD', @stripe_dir = '/backup/12-10-2018.dmp' , @compression = '101' , @verify = 'header'
3> go
The change is completed. The option is dynamic and ASE need not be rebooted for
the change to take effect.
(return status = 0)

3. schedule a job in DB13.

4. check the dump file grow from previous backup to present one.

Allocating Physical Resources

System Administration Guide: Volume 2 > Overview of DiskResourcesto understand the kinds of disk resources required by SAP ASE. See also System Administration Guide: Volume 2 > Configuring Memory and System Administration Guide: Volume 2 > Managing Mulitprocessor Servers for information about memory and CPU resources. The following sections provide helpful pointers in determining physical resource requirements.

my doudts in sybase DB

1. what this interface files does in DB level and network level?

Unit -3:System Administration

Admin Task -2: performing backup and restore of sybase database

After getting the system access through Putty:

Backup:

• login with root user.
• The isql command for Sybase database Backup:

                         su - syb<sid>
                       

                        isql  -Usapsa -Ppassword -Ssid -X

                        dump database sid to ' /backup_folder/11-10-2018.dmp' with compression 101

                        go

Here: 

          sapsa  -  user with database admin privileges.

          password- password for the sapsa user.

          sid       - system id in SAP.
          101      - compression rate.

                        The advantage of compressing data backups:

                          

                               * use less disk space.

                               * complete in shorter amounts of time. 

Restore:

• login with root user.
• The isql command for Sybase database Restore:

                                         su - syb<sid>

                                         isql  -Usapsa -Ppassword -Ssid -X

                                         load database sid from 'file location of backup '  with compression 101

                                         go

                                    

System or stored procedures, Creating Temporary Tables,

System or stored procedures:

• SAP system procedures are stored in the database sybsystemprocs.
• Any time you make changes to sybsystemprocs, back up the database.

Creating Temporary Tables:

• Create temporary tables either by preceding the table name in a create table statement with a pound sign (#), or by specifying the name prefix “tempdb..”.
• Temporary tables created with a pound sign are accessible only by the current SAP ASE session: users on other sessions cannot access them. These nonsharable, temporary tables have destroyed at the end of each session.
• Warning! Do not create temporary tables with the “tempdb..” prefix from inside a stored procedure unless you intend to share those tables among other users and sessions. 
• Each time you restart SAP ASE, it copies model to tempdb, which clears the database. You cannot recover temporary tables.

                for more info:                                                                                                                                                                  

• sybsystemdb Database

1. The sybsystemdb database stores information about distributed transactions.                              
2.  Information about remote servers participating in distributed transactions is stored in the syscoordinations table. 
3. The sybsystemdb database also stores information about SYB2PC transactions that use the SAP two phase commit protocol.
4. The spt_committab table, which storesinformation about and tracks the completion status of each two-phase commit transaction, is stored in the sybsystemdb database. 

Note:
See the Configuration Guide for your platform for information about two-phase commit transactions and how to create the sybsystemdb database.


sybmgmtdb Database:

The sybmgmtdb database stores jobs, schedules, scheduled jobs information, and data needed by the internal Job Scheduler task for processing.

pubs2 and pubs3 Sample Databases:


The pubs2 and pubs3 sample databases are provided as a learning tool for SAP ASE.

Note:

• The sample databases are intended for training purposes only, and installing them is optional.
•  Do not install them in an SAP ASE production environment.
• If space is a problem, instruct users to issue the begin transaction command before updating a sample database. After the user has finished updating one of the sample databases, he or she can issue the rollback transaction command to undo the changes.

sybdiag Database:

SAP Product Support may create the sybdiag database on your system for debugging purposes. This database holds diagnostic configuration data, and should not be used by customers.

Determining the Version of the Installation Scripts:

• sp_version lets you determine the current version of the scripts.

The syntax for sp_version is: sp_version [script_file [, “all”]]

where: 

• script_file is the name of the installation script (the default value is NULL).

 • all reports details about each script, such as the date executed, and the length of time for execution.

If you run sp_version without any parameters, it reports on all scripts

                                               sp_version null, 'all' 

This example describes what installation scripts were run, what time they were run, and what time they finished.

Unit-2 : System and optional Databses

                                                      System and Optional Databases



A default SAP ASE installation includes a number of system databases.
• The master database
• The model database
• The system procedure database, sybsystemprocs
• The two-phase commit transaction database, sybsystemdb
• The temporary database, tempdb

Optionally, you can install:
• The auditing database, sybsecurity
• The sample databases, pubs2 and pubs3
• The dbcc database, dbccdb

• The Job Scheduler database, sybmgmtdb 
Use the installjsdb script (located in $SYBASE/ASE-15_0/scripts) to installthe sybmgmtdb database.


Note:
The master, model, sybsystemdb, and temporary databases reside on the master device which is named during installation. The master database must be contained entirely on the master device and cannot be expanded onto any other device. Create all other databases and user objects on other devices.

Warning! Do not store user databases on the master device; doing so makes it difficult to recover both the system databases and any user databases stored on the master device.

info type: connecting to SAP ASE

Admin Task -1. Memory management for logging error messages

•  SAP ASE writes start-up information to a local error log file. Each time it starts, the installation program automatically sets the error log location when you configure a new SAP ASE. 

what the error logging messages consist of :

Many error messages from SAP ASE are written only to the user’s terminal. However, fatal error messages (severity levels 19 and above), kernel error messages, and informational messages from SAP ASE are recorded in the error log file.

Note:

• SAP ASE keeps the error log file open until you stop the server process.

Admin Task-1:

• Before deleting old messages to reduce the size of the error log, stop the SAP ASE process.
• Use sp_errorlog to dynamically change its path. Once the older error log is not being used by SAP ASE, you can move it, and make space available.

location of logging error messages at OS level:

 Note: On some platforms, such as Windows, SAP ASE also records error messages in the operating system event log. See the installation guide and configuration guide for your platform.


Additionals references:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc35823.1600/doc/html/san1334282798251.html


https://itsiti.com/where-to-locate-sybase-ase-error-log-file-location


https://help.sap.com/viewer/99a215e70bf64c4b8cc8794dc700e767/16.0.3.4/en-US/a72b5487bc2b101482e99b08b54a23e4.html

performing system administration tasks

• System administrative tasks can be performed by using isql or sap control center (graphics tool).
• You can save the Transact-SQL statements you use to create or modify user databases and database objects. 
• The easiest way to do this is to create or copy the statements to an ASCII-formatted file.
• The syntax for using isql with an ASCII-formatted file is the following, where filename is the full path and file name of the file that contains Transact-SQL statements:

                                       isql -Uusername -ifilename

Note: On UNIX and other platforms, use the “less than” symbol (<) to redirect the file.


System Tables:

• The master database contains system tables that keep track of information about  SAP ASE.In addition, each database (including the master database) contains system tables that keep track of information that is specific to that database. 
• All the SAP ASE-supplied tables in the master database (the SAP ASE controlling database) are considered system tables. Each user database is created with a subset of these system tables. The system tables may also be called the data dictionary or the system catalogs. 
• A master database and its tables are automatically created when SAP ASE is installed. The system tables in a user database are created when the create database command is issued. The names of all system tables start with “sys”. You cannot create tables in user databases that have the same names as system tables. See Reference Manual: Tables for detailed descriptions of system tables and their columns. 

Stored procedures or System procedures:

SAP ASE supplies stored procedures (called system procedures), many of which provide shortcuts for querying the system tables.

These system procedures provide information from the system tables:

  

Updating the system tables:Under ordinary circumstances, you need not perform direct data modifications to system tables.

Update system tables only when you are instructed to do so bySAP TechnicalSupport, by an instruction in the Error Messaging and Troubleshooting Guide, or asrequired by some system administration tasks.

Before you update system tables, you must issue a sp_configure command that enables system table updates

• Modify system tables only inside a transaction. Issue a begin transaction command before you issue the data modification command. 
• Verify that only the rows you wanted to be changed have been affected by the command and that the data has been changed correctly.
•  If the command was incorrect, issue a rollback transaction command. If the command was correct, issue a commit transaction command. 

•  the parameter value for a system procedure must enclose it in single or double quotes.

• System procedures can be invoked during a session using either chained or unchained transaction mode. Chained mode implicitly begins a transaction before any data retrieval or modification statement. Unchained mode requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction.
• You cannot execute the system procedures that modify data in system tables in the master database from within a transaction.
• If no transaction is active when you execute a system procedure, SAP ASE turns off chained mode and sets transaction isolation level 1 for the duration of the procedure.

Note:

All system procedures report a return status. For example, the following means that the procedure executed successfully: 

                                                   return status = 0

 If the system procedures do not execute successfully, the return status is a number other than 0. 

•  xp_cmdshell, which executes an operating system command on the system that is running SAP ASE.

introduction to sybase DBA

• Even though the process of SYBASE DBA has common work tasks for all types of applications running on top of Sybase. but SYBASE DBA need to have an overview about the applications to boost up the performance and other application specific activities.

• Roles Required for System Administration Tasks: To perform system administration for SAP ASE the follow 4 roles are required. which are the integration of security, administration and operational tasks are grouped into the following roles:

1.  sa_role
2. sso_role
3. sybase_ts_role
4. oper_role   

Note: oper_role  is responsible for all types of backup(dump database, dump transaction, load database, load transaction) activities.

• In addition, two users are classified based on their objects in Sybase DB:

1. database owner.
2. database object owner.

1.Database owner: The database owner is the creator of a database.

 A system administrator can use the grant command to grant users the authority to create databases.

A database owner can run:   

•  sp_adduser to allow other SAP ASE users access to the database.
• Use the grant command to give other users permission to create objects and execute commands within the database.

Note:

The database owner does not automatically receive permissions on objects owned by other users. However, a database owner can temporarily assume the permissions of other users in the database at any time by using the setuser command. Using a combination of the setuser and grant commands, the database owner can acquire permissions on any object in the database.

2.Database object owner: A database object owner is a user who creates a database object.

Database objects include tables, indexes, views, defaults, triggers, rules, constraints, and procedures. Before a user can create a database object, the database owner must grant the user permission to create objects of a particular type.

There is no special login name or password for a database object owner.
The database object owner creates an object using the appropriate create statement, and then grants permission to other users.

s use the setuser command to impersonate any other user in the database, including the object owner.

Note: need to create a database user by using security guide of sybase.