Saturday, January 3, 2015

If you compare Oracle database and MS SQL Server databases, there are some wins for Oracle and some for MS SQL Server. In this blog post, I will talk about a backup feature with Oracle as the winner.

Oracle block checking during RMAN backups

By default, an Oracle RMAN database backup computes the checksum for each block and stores it in the backup, even if the DB_BLOCK_CHECKSUM command is set to TYPICAL. DB_BLOCK_CHECKSUM initialization parameter applies to data files in the database and not applicable to backups.

Therefore one way of recognizing block media corruptions in an Oracle database is when Oracle RMAN database backup terminates with errors after it fails to perform a checksum for those corrupted blocks. So an Oracle DBA need not monitor for block media corruptions separately because the database backup (if the RMAN method is used) command itself acts as such a validation tool inherently.

MS SQL Server page (block) checking during backups

The corresponding term in MS SQL Server for datafile blocks is a page. In SQL Server, most database administrators create the backup database task through maintenance plans. But one cannot rely on these maintenance plans for page (block) checksum and torn-page detection during backups. Even if the "Verify backup integrity" option is enabled as part of the backup database task in a maintenance plan, it does nothing more than checking whether the backup media is readable without having to perform an actual restore. So it is superficial in nature.

SQL Server will check every page in the backup file by reading and recomputing the page checksum and then compares it to the recorded page checksum in the backup file to ensure integrity.

Conclusion

It is recommended that MS SQL Server DBAs create a separate T-SQL script or a stored procedure to backup all databases using the with checksum option. This blog post is validated against Oracle 11g R2 and 10g R2 databases and MS SQL Server 2008 and 2008 R2 databases. Please note the extra time taken for a restore verifyonly with checksum command. Another observation that may be noted is that a backup without a checksum and a backup with checksum takes about the same time.

Introduction Have you seen your VNC Viewer showing a black screen intermittently ? We have a fix for it. This issue has been occurrin...

Copyright and Disclaimer Notice

Copyright 2007 - 2017 DBA University, Inc. All Rights Reserved. No content of this website may be reprinted or otherwise reproduced without DBA University's permission. The posts and comments in this blog are on an "AS IS" basis without warranties. Always test your changes before pushing them to a real-time system !

Oracle is a registered trademark of Oracle Corporation and/or its affiliates .Other names may be trademarks of their respective owners.