Checkpoint Charlie: By Martin Bowes.

1. Checkpoint Charlie: By Martin Bowes. How do you back up a 1-2Tb database? How to recover it? Even if the building burns to the ground?

2. What do we have? Current backup strategy. Developed for small databases (~100G) Feature rich. Delivers 1.1G/min Faster backups by: Ingres: Parallel Checkpoints. Remove compression from checkpoint tar file. Rolling table level backups? Hardware: Triple mirrored systems?

3. What do we want? Simplicity...the magic black box... User ingres to retain complete control of the process. sudocommand Use the standard command set: ckpdb, rollforwarddb Replace tar with magic button ‘A’, ‘B’ or ‘C’ Integrate with existing processes. Keep multiple backups available on disk and on offsite storage. Backup of checkpoint files to OS dumps. Recover the database on alternate host. clean up...remove older checkpoint files when required.

4. A high level look at the SAN Groups, pools, volumes. Volumes are iSCSI targets. Volumes are created with a Snapshot reserve. COW. Volumes or snapshots can be cloned.  API

5. Samantha Carter...? Snapshot = Checkpoint. tie the Ingres checkpoint number to a SAN snapshot name. Snapshots are at volume level. volume = database location. database location must be used for one database only. Snapshots stay on the data volume. need to instantiate clones of some snapshots. clones can be included in OS backup. clones can be transferred to offsite storage. Recovery could be from snapshot or clone.

6. Checkpoint Template file: XY(D|T|E)Z: command1[;command2...] Two devices...D=Disk, T=Tape, E= Both Let: Device Tape = Snapshot Device Disk = Clone substitution parameters (see DBA guide) %C %D %F... II_CKTMPL_FILE=/full/path/cktmpl.SAN

7. Checkpoint Template file: Make database checkpoint to ‘tape’ do a SAN snapshot. BSTD: /bin/echo Starting checkpoint to SAN PSTD: /bin/true #Nothing to do WSTD: /full/path/Snapshot.pl %C %D %F ESTD: /bin/echo Ending checkpoint to SAN Quiescing the database. The dump assumption!

8. Checkpoint Template file: Database recovery from ‘tape’, does a recovery from SAN snapshot. BRTD: /bin/echo Beginning restore from SAN WDTD: /bin/true #ie. Do NOT delete. PRTD: /bin/true #nothing to do WRTD: /full/path/RestoreSnapshot.pl %C %D %F EETE: /bin/echo Ending SAN Snapshot recovery

9. Checkpoint Template file: Also you might like to:... Disallow table checkpoint to ‘tape’. Disallow table recovery from ‘tape’ Disallow table or database checkpoint to ‘disk’? Cloning a volume takes time. Disallow as volume must be offline and it requires privilege. Make creating a clone from a snapshot a separate task. Permit Table or Database recovery from ‘disk’.

10. API: We wanted simple! use EqlScript; $SAN=EqlScriptnew(); $SAN open($SAN_HOST_IP) || die “...”; $SAN login($SAN_User, $SAN_Passwd) || die “...”; @Text=$SANcmd($CMD); $SANerr && die“...”, $SANerrmsg;

11. What should be easy...: volume select newvol2 snapshot select c0012002 show __________________________ Snapshot Information _________________________ Name: c0012002 Permission: read-only DesiredStatus: offline Status: offline OriginalVolumeSize: 5GB CreationTime: 2010-01-14:17:16:39 Schedule: Replicate: iSCSI Alias: iSCSI Name: NewVol2-2010-01-14-17:16:39.45 iqn.2001-05.com.equallogic:6-8a0900- Description: Ingres backup c0012002 cb76d4a01-40405000001af44907-newvol2 Connections: 0 -2010-01-14-17:16:39.45 SnapsetName: _________________________________________________________________________

12. Checkpoint to snapshot: setenv II_CKTMPL_FILE /full/path/cktmpl.SAN ckpdb –mdevicedbname Snapshot.pl ... $CMD="volume select $volume snapshot create-now read-only”; Which responds with @Text… Snapshot creation succeeded. snapshot name is acevol2-2010-01-25-11:28:01.5 $CMD="volume select $volume snapshot rename $snapshot $ckpdb_num"; Result 1TB in 20 seconds. Wasn’t that easy!

13. Recover a snapshot: umount $mountpoint disconnect iSCSI initiator for the volume SAN commands: login and authenticate set volume offline. set snapshot offline restore the snapshot of the volume associated with the checkpoint. set volume online reconnect iSCSI initiator mount the volume.

14. Recover a snapshot: iscsiadm...google the hell out of this! The mount problem. You don’t mount a volume, you mount a partitioned device! Mapping of iSCSI targets to device names is random. mount LABEL=<label> mountpoint aaaaaaaa.rfc created in the primary data location. Preserve it and restore it after the recovery of the primary location completes.

15. Recover from snapshot: setenv II_CKTMPL_FILE /full/path/cktmpl.SAN rollforwarddb –mdevicedbname RestoreSnapshot.pl ...  Result 1TB in 30 minutes. Which was disappointing... WDTD: workaround catalogs on small primary database location. tables on large secondary locations. Result: 1TB in 40s.

16. MakeClone.pl: mkdir /$mountpoint SAN commands login and authenticate clone a volume from a snapshot (ie. Checkpoint) Set access on the clone (so iscsid can see it). Allow iscsid to discover the volume Get an initiator to connect to the new volume. mount this new volume

17. MakeClone.pl: Get an initiator to connect to the new iSCSI target. sleep 2; #Or else, you wont see it...race condition You don’t mount volumes...but what partition is it? Check in /dev/disk/by-path for a link matching the iSCSI target. ip-192.168.0.254:3260-iscsi-iqn.2001-05.com.equaqllogic:0-8a0906-1d090b703-ecd58fb52504bd18-bowtest-c0022001-lun-0-part1  ../../sdf1 mount /dev/sdf1 /bowtest-c0022001

18. Summary: OS specifics. sudocommand (/etc/fstab) mount LABEL=<label> mountpoint managing iscsid /dev/disk/by-path Ingres Specifics. A database location for a single database only. WDTD. aaaaaaaa.rfc API The less ‘scraping’ you have to code around the better. Test, test, test again.