Archive Databases in Sybase ASE

Archive Databases in Sybase ASE (15.0.2 +) - Quick Overview

NOW ACCESS DATA DIRECTLY FROM BACKUP FILE WITHOUT RESTORING IT !!!!

Archive Database is a type of database in ASE which is built primarily on database backup files and partly on server devices.  A system table, sysaltusages, maps these database backup files to archive database. This system table is stored on a user defined database which should be designated as "scratch database"

Server devices  (like the one used by normal databases, initialized by disk init) on Archive databases are used to store pages which are result of recovery operation performed while loading archive database with database dump.  When an archive DB is loaded, a logical mapping is created between pages in database dump file and archive database. In light of this, the size of your archive database will be very less as compared to size of source database.

Figure below shows a high level view of Archive Database.

Add caption

Possible applications of Archive Databases -

    Before loading a dump file to a database, in order to ensure that there is no corruption, an archive database can first be created on it and consistency checks could be run. Creating an archive database is very fast as only logical mappings are to be created.

    Archive Databases can be used for Object level Recovery

Limitations

Read Only Databases (Although dbcc with fix can be applied on it)

Database dumps from files/disks can be only used, tapes are not supported

Compressed dumps should be created using "with compression" and not "compression::"

Implementation Steps -

    1.Either create a new database or designate an existing databases as scratch db.
--create DB
Create database scratchdb on data_dev = 100 log on log_dev = 50
--mark it as acratch db
sp_dboption scratchdb, 'scratch database', true
2. Create an archive database
create archive database archive_db on data_dev1 = 20 with scratch_database = scratchdb
3. If you are going to load a compressed dump, set a compression memory pool first using below command -
sp_configure 'compression memory size' , 2560
4. Load archive database with dump of desired database -
load database archive_db from '/dev/ddumpd/prod_db_01'
stripe on from '/dev/ddumpd/prod_db_02'

Bring it online
Online database archive_db
5.Use load database with norecovery, This give you immediate access to the dump files as recovery will not happen. You don't need to execute online database either.

Ardent DBA

Sybase ASE Upgradation from 12.5.X to 15.X - Dump Load

• Install ASE 15 instance on the same box at the different partition/directory exactly Same as ASE 12.5.X instance (this includes devices, databases, logins, loginroles etc). (NOTE: System db's Size will differ)
•  Install backup server for ASE 15.0
•  ASE 15.0 RUN_server/Backupserver file and ASE 12.5.X RUN_server/Backupserver file should have same share memory, Trace Flags, other backup server parameters like -P, -N, -m etc. Please confirm. 
• Copy 12.5.X cfg file to ASE 15.0 cfg file
• Recycle server 15
• Run sp_countmetadata "number of open partitions" and set whatever value it provides with sp_configure.
• BCP out master..syslogins, master..sysloginroles from ASE 12.5.X and BCP in ASE 15.0 master.
• Block all the users on Current Production server
• In 12.5 server, For "default data cache" set large 16k pool with APF=90%  and Set 2k pool also bigger around 150% of current size with APF=50% for running dbcc checkstorage. (can be done from sp_poolconfig)
• BCP out all important master..system tables (sysdevices,sysdatabase,syslogins,sysloginroles,sysusages,sysconfigures
• Run DBCC Checkstorage on all User databases on 12.5.x server
• Unpartition all the partition tables (if any).
• Put Database in "dbo use" mode (sp_dboption DBNAME, "dbu use", true).
• Run sp_flushstats (stats are flushed to disk from cache)
• - Wait for 10 to 30 seconds, depending on the database size and activity.
• run checkpoint against the database to flush updated pages.
• Dump 12.5.X all databases (user databases)
• Load user Database (on traget i.e. ASE 15 db) 
• Online database.
• Run sp_checkreswords (in each user database without any parameters) for all user databases. If there are any reservewords fix it.
• Run sp_checksource (in each user database without any parameters) to verify we have all source text for compiled objects. Then you can run dbcc upgrade_object (in each user database without any parameters) for to find potential problems and upgrade objects manually.
• Partition all those unpartition tables from ASE 12.5.X
• Increase default datacache 3 folds and procedure cache 4-6 folds.
• Increase default value of “number of lava Operators”
• Increase default value of “stack size”
• Increase number of histogram steps and histogram tuning factor.
• Run update stats on new databases.

Ardent DBA

Sybase ASE Installation From Resource File

Sybase ASE Installation From Resource Files

1. Go To Software Product Download Center and Download appropriate Product
2. Generate License (no of engines,hostID,served(req license server) or unserved etc)
3. Talk to SA and ensure that all OS patches are up-to-date and NW drivers, SAN drivers etc are up to date.
4. Create an account Sybase and give it permissions on $SYBASE DIR as well as all raw devices and filesystem devices
5. Specify the license type
6. Have SA adjust OS Shared memory param for Sybase (UNIX /etc/projects; LINUX sysctl kernal.shmmax)
7. Work with SA and have RAW devices created for all databases except tempdb. For tempdb have filesystem device created. Ensure that Sybase is set to be owner of these devices. Its quite handy to create Links to Raw devices (ln -s /dev/hda4  /var/sybase/ASE1/master.dat)
8. Ask SA to install Sybase ASE Software
9. Copy resourcefile from $SYBASE/$SYBASE_ASE/init (srvbuilders -r <resourcefilename>). Following imp params

sybinit.release_directory: USE_DEFAULT
sybinit.product: sqlsrv
sqlsrv.server_name: ASE1
sqlsrv.network_hostname_list: <your hostname>
sqlsrv.network_port_list: 4000
sqlsrv.server_page_size: USE_DEFAULT
sqlsrv.master_device_physical_name: /var/sybase/ASE1/master.dev
sqlsrv.master_device_size: 100
sqlsrv.master_database_size: 60
sqlsrv.errorlog: /opt/sybase/admin/ASE1/errorlog
sqlsrv.sybsystemprocs_device_physical_name: /var/sybase/ASE1/sybsystemprocs.dev
sqlsrv.sybsystemprocs_device_size: 200
sqlsrv.sybsystemprocs_database_size: 200
sqlsrv.sybsystemdb_device_physical_name: /var/sybase/ASE1/sybsystemdb.dev
sqlsrv.sybsystemdb_device_size: 20
sqlsrv.sybsystemdb_database_size: 20
sqlsrv.tempdb_device_physical_name: /var/sybase/ASE1/tempdb.dev
sqlsrv.tempdb_device_size: 100
sqlsrv.tempdb_database_size: 100M
sqlsrv.default_backup_server: SYB_BACKUP

10 .The instmsgs.ebf file contains the latest update for Sybase error messages.isql -i $SYBASE/$SYBASE_ASE/scripts/instmsgs.ebf

11. Create backup serverver svrbuilders -r<backup Server RS>

12. Modify RUN_SYB_BACKUP as follows

          Change location of logfile with -e flag

13. Go ahead and create user databases

Ardent DBA