SAP 16 Checking and Fixing SAP ASE Database Consistency

Checking and Fixing SAP ASE Database Consistency
Checking Database Consistency
The database consistency checker (dbcc) provides commands for checking the logical and physical consistency of a database. dbcc checks:
• Page linkage and data pointers at both the page level and the row level using checkstorage or checktable and checkdb
• Page allocation using checkstorage, checkalloc, checkverify, tablealloc, textalloc, and indexalloc
• For consistency within and between the system tables in a database with checkcatalog
dbcc checkstorage stores the results of checks in the dbccdb database. You can print reports from dbccdb using the dbcc stored procedures.
Uses of dbcc commands
• If you suspect that a database is damaged. For example, if using a particular table generates the message “Table corrupt,” you can use dbcc to determine if other tables in the database are also damaged.
Page and Object Allocation
When you initialize a database device, the disk init command divides the new space into allocation units.
• When you create a table of an index partition, SAP ASE allocates an extent for the object.
The Object Allocation Map (OAM)
Each table and index on a table has an object allocation map (OAM), which holds information on pages allocated to the table or index and is checked when a new page is needed for the index or table.
2K Logical Page Size 4K Logical Page Size 8K Logical Page Size 16K Logical Page Size
250 506 1018 2042
The OAM pages point to the allocation page for each allocation unit where the object uses space. The allocation pages, in turn, track the information about extent and page usage within the allocation unit. In other words, if the titles table is stored on extents 24 and 272, the OAM page for the titles table points to pages 0 and 256.
This OAM points to two allocation pages: page 0 and page 256.
These allocation pages track the pages used in each extent used by all objects with storage space in the allocation unit. For the object in this example, it tracks the allocation and deallocation of pages on extents 0, 24, 272, and 504.
OAM Page and Allocation Page Pointers

dbcc checkalloc and dbcc tablealloc examine this OAM page information, in addition to checking page linkage.
Page Linkage
After a page has been allocated to a table of an indexed partition, it is linked with other pages that are used for the same object.
How a newly allocated page is linked with other pages

dbcc Checks
The dbcc commands that you can run depend on your role.
For example:
• Only the table owner can execute dbcc with the checktable, fix_text, or reindex keywords.
• Only the database owner can use the checkstorage, checkdb, checkcatalog, checkalloc, indexalloc, textalloc, and tablealloc keywords.
• Only a system administrator can use the dbrepair keyword.
Syntax:
dbcc checkcatalog [(dbname[,fix | all | fixall])]

Option Description
dbname Database name. If database name is omitted, then the current database is used.
all Applies to ASE 12.5.2 and above. Includes system tables in the new consistency checks (check consistency only, no fix).
fixall Applies to ASE 12.5.2 and above. Includes the System tables in the fix command.
Dbcc checkcatalog validates:
• Each entry in syscolumns must have an entry in sysobjects and systypes
• Each sysobjects row must have an entry in either syscolumns or sysprocedures based on the type of object
• Each sysprocedures row must have an entry in sysobjects
• Each sysindexes entry has data in sysobjects and syssegments
• The segmap values for each fragment in sysusages has a matching entry in syssegments
• The last checkpoint entry in syslogs is correct.
• Checks that each compiled object has entries in syscomments.
• Runs consistency checks for Sysindexes (12.5.2 and above, CR 334620).
dbcc checkstorage
In order to run dbcc checkstorage we need to install dbcc database
It is special dbcc command that combines the functionality of tall the commands like
dbcc checktable
dbcc checkdb
dbcc checkalloc
dbcc indexalloc
dbcc tablealloc

Syntax : dbcc checkstorage [(dbname)]
Example : dbcc checkstorage pubs2

• In checktable it only checks for datapages
• checkdb → checks for dataapge
• dbcc checkstorage just generates a report and doesn't fix the error
• when dbcc checkstorage command is run on a db it puts that db in dbccdb and performs all other dbcc commands
• The faults that encountered in dbcc checkstorage will be stored in dbcc_faults
• whenever dbcc commands are run on user database first dbcc needs to installed and this database can be on production server/development

Видео SAP 16 Checking and Fixing SAP ASE Database Consistency канала SkillPediaAI