Resizing an SAP ASE VDB

When resizing ASE VDBs, we recommend:

• Ensure that the VDB's database devices reside on the Delphix storage, in the same directory as the database's other devices. Use the ASE "sp_helpdb" and "sp_helpdevice" commands to get details on the VDB's devices.

• If you need to add a new device, use the "skip_alloc=true" clause because the storage on Delphix is already zeroed (initialized). This should allow the "DISK INIT" command to complete almost instantaneously.

• If you add a new device to the database by issuing the ASE "DISK INIT" followed by the "ALTER DATABASE" commands, click the camera icon to take a new snapshot of the VDB.

  Note: It is important to take a snapshot after altering the VDB's device layout so that Delphix rewrites the database's "manifest" file to include the new database device.

• Delphix 6.x uses the new "ALLOW_DBID_MISMATCH,FIXDBID" clause to fix in dbid mismatches on versions of ASE that support these clauses. If you are on an older version of Delphix or ASE that does not support this clause, you may be required to run the DBCC CHECKALLOC() command on the VDB.

If you use the ASE ALTER DATABASE command to increase the amount of space allocated to a VDB, you may need to run the DBCC CHECKALLOC() command on the VDB. If the VDB is unmounted and remounted without this command being run on it, you may run into the following ASE CR which leaves the VDB in an unusable state:

Determining the need to run DBCC CHECKALLOC

DBID mismatches are most likely to occur under the following circumstances:

• When provisioning VDBs to the same ASE instance hosting the staging database of the dSource the VDB was provisioned from.

• When provisioning multiple copies of the same VDB to the same ASE instance.

Delphix has found the following technique as a reliable way to determine whether or not you need to run DBCC CHECKALLOC() after increasing the amount of space allocated to the VDB.

In the following example, a VDB has been provisioned back to the same ASE instance where the staging database was being hosted. The staging database had a dbid=11865, so ASE automatically assigns the VDB the next lowest available dbid (dbid=22 in this case).

1. Resize the database:

   CODE

   
   1> SELECT name, dbid FROM sysdatabases WHERE name = 'Vdb5'
   2> go
    name                           dbid   
    ------------------------------ ------ 
    Vdb5                               22 
   
   1> DISK RESIZE name="AAA4$ff367xUvd67P7II_db1_dev26", size="4M"
   2> go
   
   1> ALTER DATABASE Vdb5 on AAA4$ff367xUvd67P7II_db1_dev26="4M"
   2> go
   Extending database by 1024 pages (4.0 megabytes) on disk AAA4$ff367xUvd67P7II_db1_dev26
   Database Vdb5 which is currently offline has been altered from size 2816 logical pages (2816 physical pages) to 3840 logical pages

2. Run the following queries to determine of there is a dbid mismatch on the pages of the VDB. DBCC PAGE() requires the sybase_ts_role role.

   CODE

   
   1> -- Run a query to generate DBCC commands:
   1> SELECT 'dbcc page (Vdb5,' + convert(varchar(5), lstart) + ', 0, 0)' FROM master..sysusages WHERE dbid=db_id('Vdb5') AND segmap != 0
   2> go
                                 
    ---------------------------- 
    dbcc page (Vdb5,0, 0, 0)     
    dbcc page (Vdb5,1536, 0, 0)  
    dbcc page (Vdb5,1792, 0, 0)  
    dbcc page (Vdb5,2816, 0, 0)  
    
   1> -- Turn on DBCC traceflag 3604 to direct output to stdout
   2> dbcc traceon(3604)
   3> GO
    
   1> dbcc page (Vdb5,0, 0, 0)     
   2> dbcc page (Vdb5,1536, 0, 0)  
   3> dbcc page (Vdb5,1792, 0, 0)  
   4> dbcc page (Vdb5,2816, 0, 0)
   5> go | grep dbid
       bmass_next=0x0 bmass_prev=0x0 bdbid=22
   pageno=0 dealloc_count=102 allocnode=0 ptnid=99 allocation_page dbid=11865 timestamp=0000 00002730, segmap=0x00000003 (0x00000002
       bmass_next=0x0 bmass_prev=0x0 bdbid=22
   pageno=1536 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=11865 timestamp=0000 00000001, segmap=0x00000004 (0x00000004
       bmass_next=0x0 bmass_prev=0x0 bdbid=22
   pageno=1792 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=11865 timestamp=0000 00000001, segmap=0x00000003 (0x00000002
       bmass_next=0x0 bmass_prev=0x0 bdbid=22
   pageno=2816 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=22 timestamp=0000 00000001, segmap=0x00000003 (0x00000002

Notice that three pages (0, 1536 and 1792) have the staging database's dbid (11865) and one page (2816) has the correct dbid (22) that matches the dbid of 22 stored in sysdatabases.

If all the dbid's match the dbid in sysdatabases, there is no need to run DBCC CHECKALLOC().

This means that DBCC CHECKALLOC() must be run on the VDB to correct the dbid mismatch:

1> dbcc checkalloc(Vdb5)
2> go
...etc...
11 allocation pages have been corrected to match database ID 22.

Note: In the code block above 11 pages were fixed.

Now if you run the DBCC PAGE() query again you can see that all of the dbid's match:

1> dbcc page (Vdb5,0, 0, 0)     
2> dbcc page (Vdb5,1536, 0, 0)  
3> dbcc page (Vdb5,1792, 0, 0)  
4> dbcc page (Vdb5,2816, 0, 0)
5> go | grep dbid
    bmass_next=0x0 bmass_prev=0x0 bdbid=22
pageno=0 dealloc_count=102 allocnode=0 ptnid=99 allocation_page dbid=22 timestamp=0000 00002730, segmap=0x00000003 (0x00000002
    bmass_next=0x0 bmass_prev=0x0 bdbid=22
pageno=1536 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=22 timestamp=0000 00000001, segmap=0x00000004 (0x00000004
    bmass_next=0x0 bmass_prev=0x0 bdbid=22
pageno=1792 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=22 timestamp=0000 00000001, segmap=0x00000003 (0x00000002
    bmass_next=0x0 bmass_prev=0x0 bdbid=22
pageno=2816 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=22 timestamp=0000 00000001, segmap=0x00000003 (0x00000002

Recovering from DBID mismatch errors

If a VDB containing mismatched dbids gets unmounted, it will likely get ASE errors 14545, 14547 and 14519 during the next attempt to MOUNT it and it will now be in an unusable state. Options for recovery include:

• For ASE versions that do not have the fix for ASE CR 798271, you may rewind the VDB to a snapshot taken prior to resizing the VDB. Any data changes made since this point in time will be lost.

On newer versions of ASE where ASE CR 798271 has been fixed, you can manually mount the VDB using the new MOUNT command options:

1> -- Get the device listing for the VDB from the manifest
2>  MOUNT DATABASE Vdb5 FROM '/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/manifest' WITH LISTONLY
3> go
[database]
Vdb5
[device]
'/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/dxnff367xUvd67P7II_db1_dev26' =
'AAA4$ff367xUvd67P7II_db1_dev26'
'/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/dx9279VtfPAvfOm5xY_db1_dev27' =
'AAA5$279VtfPAvfOm5xY_db1_dev27'
 
1> -- Mount the VDB using the WITH ALLOW_DBID_MISMATCH clause
2> MOUNT DATABASE [Vdb5] AS [Vdb5] FROM '/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/manifest'
3> WITH ALLOW_DBID_MISMATCH USING 
4> '/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/dxnff367xUvd67P7II_db1_dev26' = 'AAA4$ff367xUvd67P7II_db1_dev26',
5> '/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/dx9279VtfPAvfOm5xY_db1_dev27' = 'AAA5$279VtfPAvfOm5xY_db1_dev27'
6> go
The physical device '/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/dxnff367xUvd67P7II_db1_dev26'
has been automatically assigned the logical device name 'AAA6$ff367xUvd67P7II_db1_dev26'.
The physical device '/export/home/sybase/toolkit/564dfe60-40b6-aec9-26fb-3baeb4e7b23b-vdb-19/datafile/dx9279VtfPAvfOm5xY_db1_dev27'
has been automatically assigned the logical device name 'AAA7$279VtfPAvfOm5xY_db1_dev27'.
Started estimating recovery log boundaries for database 'Vdb5'.
Database 'Vdb5', checkpoint=(1543, 13), first=(1543, 13), last=(1543, 13).
Completed estimating recovery log boundaries for database 'Vdb5'.
Started ANALYSIS pass for database 'Vdb5'.
Completed ANALYSIS pass for database 'Vdb5'.
Started REDO pass for database 'Vdb5'. The total number of log records to process is 1.
Completed REDO pass for database 'Vdb5'.
MOUNT DATABASE: Completed recovery of mounted database 'Vdb5'.
MOUNT DATABASE: A new database id was required for database 'Vdb5' in order to mount it. Execute DBCC CHECKALLOC(Vdb5, fixdbid) to
correct it.


1> DBCC CHECKALLOC(Vdb5, fixdbid)
2> go
Checking Vdb5: Logical pagesize is 4096 bytes
Total (# alloc pages = 15, # of alloc pages modified = 15).
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
 
1> -- Confirm pages all have the correct dbid
2> SELECT 'dbcc page (Vdb5,' + convert(varchar(5), lstart) + ', 0, 0)' FROM master..sysusages WHERE dbid=db_id('Vdb5') AND segmap != 0
3> go
                              
 ---------------------------- 
 dbcc page (Vdb5,0, 0, 0)     
 dbcc page (Vdb5,1536, 0, 0)  
 dbcc page (Vdb5,1792, 0, 0)  
 dbcc page (Vdb5,2816, 0, 0)
 
1> dbcc traceon(3604)
2> dbcc page (Vdb5,0, 0, 0)     
3> dbcc page (Vdb5,1536, 0, 0)  
4> dbcc page (Vdb5,1792, 0, 0)  
5> dbcc page (Vdb5,2816, 0, 0) 
6> go | grep dbid
    bmass_next=0x0 bmass_prev=0x0 bdbid=23
pageno=0 dealloc_count=102 allocnode=0 ptnid=99 allocation_page dbid=23 timestamp=0000 00002730, segmap=0x00000003 (0x00000002
    bmass_next=0x0 bmass_prev=0x0 bdbid=23
pageno=1536 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=23 timestamp=0000 00000001, segmap=0x00000004 (0x00000004
    bmass_next=0x0 bmass_prev=0x0 bdbid=23
pageno=1792 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=23 timestamp=0000 00000001, segmap=0x00000003 (0x00000002
    bmass_next=0x0 bmass_prev=0x0 bdbid=23
pageno=2816 dealloc_count=0 allocnode=0 ptnid=99 allocation_page dbid=23 timestamp=0000 00000001, segmap=0x00000003 (0x00000002

More information

VDBs are unmounted and remounted during operations such as:

• Disable/Enable of the VDB.

• Stop/Start of the VDB.

• Rebooting of the server hosting the VDB's ASE instance.

DBAs are unlikely to have found the need to run DBCC CHECKALLOC() on physical databases in the normal workflow of resizing a database unless the ASE MOUNT/UNMOUNT command is used. When you MOUNT a database on a server that already has the mounted database's dbid in use, ASE assigns the mounted database a new dbid and this gets reflected in the sysdatabases and sysusages tables in the master database and in the dbtable memory structure for the mounted database. However, the dbid in the dbinfo structure and allocation pages in the mounted database are not updated by mount. Doing so can take a long time on a big database. DBCC CHECKALLOC() will correct the values in all the allocation pages.

In Delphix version 5.1.3.0 and higher, Delphix will create databases using a high dbid(using the "CREATE DATABASE with dbid=" clause). By using a high dbid (ASE supports a maximum of 32,767 dbids), it is unlikely that the dbid will be reused and thus Delphix will be able to avoid the errors (14545, 14547 and 14519). It attempts to assign unique dbid values for staging databases in the range 10000-30000.

The second part of this fix involves keeping the staging database mounted so that the dbid is pinned and no other database gets created and uses the staging database's dbid. The staging database will remain mounted except for a brief window where we unmount it to create a manifest file.

The final part of this fix utilizes the ASE DBCC PAGE() command to confirm the dbid on select pages from the sysusages table all match the database's actual dbid. If there is a mismatch found, Delphix will call DBCC CHECKALLOC() to fix the database. Delphix calls DBCC PAGE() in order to avoid calling DBCC CHECKALLOC() as the CHECKALLOC() routine can add significant time to database recovery (performance considerations for CHECKALLOC).