Transcription
ProgressDatabase AdministrationGuide and Reference
2002 Progress Software Corporation. All rights reserved.Progress software products are copyrighted and all rights are reserved by Progress Software Corporation. This manual is alsocopyrighted and all rights are reserved. This manual may not, in whole or in part, be copied, photocopied, translated, or reducedto any electronic medium or machine-readable form without prior consent, in writing, from Progress Software Corporation.The information in this manual is subject to change without notice, and Progress Software Corporation assumes no responsibilityfor any errors that may appear in this document.The references in this manual to specific platforms supported are subject to change.Progress, Powered by Progress, Progress Fast Track, Progress Profiles, Partners in Progress, Partners en Progress, Progress enPartners, Progress in Progress, P.I.P., Progress Results, ProVision, ProCare, ProtoSpeed, SmartBeans, SpeedScript, andWebSpeed are registered trademarks of Progress Software Corporation in the U.S. and other countries. A Data Center of YourVery Own, Allegrix, Apptivity, AppsAlive, AppServer, ASPen, ASP-in-a-Box, Empowerment Center, Fathom, Future Proof,IntelliStream, OpenEdge, PeerDirect, POSSE, POSSENET, Progress Dynamics, Progress Software Developers Network,SectorAlliance, SmartObjects and WebClient are trademarks or service marks of Progress Software Corporation in the U.S. andother countries.SonicMQ is a registered trademark of Sonic Software Corporation in the U.S. and other countries.Vermont Views is a registered trademark of Vermont Creative Software in the U.S. and other countries.Java and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the U.S. and other countries.Any other trademarks and/or service marks contained herein are the property of their respective owners.Progress Software Corporation acknowledges the use of Raster Imaging Technology copyrighted by Snowbound Software 19931997, the IBM XML Parser for Java Edition, and software developed by the Apache Software Foundation (http://www.apache.org/). IBM Corporation 1998-1999. Allrights reserved. U.S. Government Users Restricted Rights — Use, duplication or disclosurerestricted by GSA ADP Schedule Contract with IBM Corp.Progress is a registered trademark of Progress Software Corporation and is used by IBM Corporation in the mark Progress/400under license. Progress/400 AND 400 are trademarks of IBM Corporation and are used by Progress Software Corporationunder license.The implementation of the MD5 Message-Digest Algorithm used by Progress Software Corporation in certain of its products isderived from the RSA Data Security, Inc. MD5 Message-Digest Algorithm.May 2002Product Code: 4590Item Number: 89434;V9.1D
ContentsPreface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Organization of This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part I Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part II Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Part III Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .How To Use This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Typographical Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Syntax Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Example Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Progress Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Other Useful Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Development Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Reporting Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4GL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .DataServers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .SQL-89/Open Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .SQL-92 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .WebSpeed. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.The Progress Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.1Progress Database Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.1.1Other Database-related Files . . . . . . . . . . . . . . . . . . . . . . . . . .1.2Storage Design Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ixliii1–11–21–41–5
Contents1.2.1Storage Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.2.2Extents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.2.3Storage Areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Determining Configuration Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.3.1System Platform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.3.2Connection Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.3.3Database Location . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1.3.4Database Configurations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Operating System Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Multi-threaded Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Multi-tier and Cluster Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . .Self-service and Network Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Relative- and Absolute-path Databases . . . . . . . . . . . . . . . . . . . . . . . . –131–141–151–152.Administrative Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2.1Block Size Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2.1.1Guidelines For Choosing Storage Area Locations . . . . . . . . . .2.2Calculating Database Disk Requirements . . . . . . . . . . . . . . . . . . . . . . . .2.3Storing Database Extents On Raw Partitions . . . . . . . . . . . . . . . . . . . . .2–12–22–32–32–73.Progress Database Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.1Database Block Sizes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.2Number and Size Of Storage Areas . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.3Maximum Number Of Records Per Block . . . . . . . . . . . . . . . . . . . . . . . .3.4Table and Index Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.5Number Of Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.6Maximum Size Of the Primary Recovery (BI) Area . . . . . . . . . . . . . . . . .3.7Maximum Database Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.8Number Of Connections Per Database . . . . . . . . . . . . . . . . . . . . . . . . . .3.9Number Of Simultaneous Transactions Per Database . . . . . . . . . . . . . .3.10Size Of Database Buffer Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.11Progress Database Name Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.12Applicable Operating System Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . .3.13Data Types and Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73–73–83–83–104.Creating and Deleting Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4.1Ways To Create a Progress Database . . . . . . . . . . . . . . . . . . . . . . . . . .4.2Creating a Database With PROSTRCT CREATE . . . . . . . . . . . . . . . . . .4.2.1Creating a Structure Description File . . . . . . . . . . . . . . . . . . .4.2.2Create a Database Structure Extent . . . . . . . . . . . . . . . . . . . .4.2.3Adding Schema To a Void Database . . . . . . . . . . . . . . . . . . . .4.3Creating a Database With the PRODB Utility . . . . . . . . . . . . . . . . . . . . .4.4Creating a Database With the Data Administration Tool . . . . . . . . . . . . .4.5Migrating Version 8 Databases To Version 9 Databases . . . . . . . . . . . 51.31.41.51.61.71.8iv
Contents4.5.1Converting a Single-volume Version 8 Database WithPROCOPY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4.5.2Converting a Single-volume Version 8 Database WithPROREST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4.5.3Converting a Single-volume Version 8 Database WithPROSTRCT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4.5.4Converting a Version 8 Database To Version 9. . . . . . . . . . . .Using the Schema Mover After Conversion . . . . . . . . . . . . . . . . . . . . . .Copying a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .AutoConvert Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Deleting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4–184–184–194–224–244–245.Starting Up and Shutting Down . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.1The Progress Explorer Framework . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.1.1AdminServer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.1.2Progress Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.1.3Command-line Configuration Utilities . . . . . . . . . . . . . . . . . . .5.1.4Using the DBMAN Command-Line Utility . . . . . . . . . . . . . . . .5.2Starting a Server Or Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.2.1Using the PROSERVE Command . . . . . . . . . . . . . . . . . . . . . .5.2.2Specifying International Character Sets . . . . . . . . . . . . . . . . .5.2.3Network Addressing With Progress (- S and -H) . . . . . . . . . .5.2.4Starting Network Brokers and Servers. . . . . . . . . . . . . . . . . . .5.2.5Starting Multiple Brokers Using the Same Protocol . . . . . . . .5.2.6Accessing a Server Behind a Firewall . . . . . . . . . . . . . . . . . . .5.3Starting and Stopping Background Writers . . . . . . . . . . . . . . . . . . . . . .5.3.1Starting and Stopping an APW . . . . . . . . . . . . . . . . . . . . . . . .5.3.2Starting and Stopping a BIW . . . . . . . . . . . . . . . . . . . . . . . . . .5.3.3Starting and Stopping an AIW . . . . . . . . . . . . . . . . . . . . . . . . .5.4Stopping a Server Or Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.4.1PROSHUT Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5.4.2PROMON Shut Down Database Option . . . . . . . . . . . . . . . . 125–156.Backup and Recovery Strategies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.1Identifying Files For Back Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.2Determining the Type Of Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.2.1Incremental Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.2.2Online Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.2.3Offline Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.3Choosing Backup Media . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.4Creating a Backup Schedule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.4.1Database Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.4.2Database Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.4.3Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86–84.64.74.84.94–164–17v
Contents6.4.4Unscheduled Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Developing a Recovery Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.5.1Lost Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6.5.2Duration Of Offline Applications . . . . . . . . . . . . . . . . . . . . . . . .6.5.3Time Needed For Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . .6.5.4Distributed Transactions Occurring Inconsistently . . . . . . . . . .Recovery Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6–96–96–106–106–116–126–137.Backing Up a Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.1Performing a Progress Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.1.1Performing an Online Full Backup With PROBKUP . . . . . . . . .7.1.2Progress Backup Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.1.3Testing Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.1.4Archiving Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.2Performing an Offline Progress Backup . . . . . . . . . . . . . . . . . . . . . . . . .7.3Performing an Online Progress Backup . . . . . . . . . . . . . . . . . . . . . . . . .7.4Using Database Quiet Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.5Performing a Non-Progress Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.6Database Backup Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.6.1UNIX Incremental Backup Example . . . . . . . . . . . . . . . . . . . . .7.6.2Windows Full Backup Example. . . . . . . . . . . . . . . . . . . . . . . . .7.6.3Incremental Backup Example . . . . . . . . . . . . . . . . . . . . . . . . . .7.7Verifying a Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.8CRC Codes and Redundancy In Backup Recovery . . . . . . . . . . . . . . . .7.8.1CRC Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.8.2Error-correction Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.9Restoring a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7.9.1Using the PROREST Utility To Restore a Database . . . . . . . .7.9.2Important Rules For Restoring Backups . . . . . . . . . . . . . . . . . .7.9.3Obtaining Storage Area Descriptions Using PROREST . . . . .7.9.4Database Restore Examples . . . . . . . . . . . . . . . . . . . . . . . . . 257–257–267–277–287–298.Recovering a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.1Introduction To Recovery Mechanisms . . . . . . . . . . . . . . . . . . . . . . . . . .8.1.1Crash Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.1.2Roll-forward Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.1.3Two-phase Commit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.2File Locations That Ensure Safe Recovery . . . . . . . . . . . . . . . . . . . . . . .8.3Sample Recovery Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.3.1Example 1: Low Availability Requirements . . . . . . . . . . . . . . . .8.3.2Example 2: Moderate Availability Requirements . . . . . . . . . . .8.3.3Example 3: Moderate-to-High Availability Requirements . . . . .8.3.4Example 4: High Availability Requirements . . . . . . . . . . . . . . .8.3.5Sample Recovery Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . 98–108–116.56.6vi
aging and Roll-forward Recovery Commands . . . . . . . . . . . . . .Recovering From System Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.5.1System Crash While Running RFUTIL ROLL FORWARD. . . .8.5.2System Crash While Running Other Utilities . . . . . . . . . . . . . .8.5.3System Crash While Backing Up the Database. . . . . . . . . . . .8.5.4System Crash While Database Is Up. . . . . . . . . . . . . . . . . . . .Recovering From Media Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.6.1Loss Of the DB Files, BI Files, Or Both . . . . . . . . . . . . . . . . . .8.6.2Loss Of the AI File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.6.3Loss Of Database Backup . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.6.4Loss Of Transaction Log File . . . . . . . . . . . . . . . . . . . . . . . . .Recovering From a Full Disk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.7.1After-image Area Disk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8.7.2Control Or Primary Recovery Area Disk . . . . . . . . . . . . . . . . .8.7.3Transaction Log File Disk . . . . . . . . . . . . . . . . . . . . . . . . . . . .Truncating the BI File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Recovering From a Crash . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Recovering From a Lost Or Damaged Control Area . . . . . . . . . . . . . . .Unlocking Damaged Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Dumping Tables From a Damaged Database . . . . . . . . . . . . . . . . . . . .Forcing Access To a Damaged Database With the -F Parameter . . . . �248–258–268–278–289.Maintaining Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.1The Progress Structure Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.2Progress Structure Statistics Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.3Progress Structure List Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.4Progress Structure Add Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.4.1PROSTRCT ADD and Pathnames . . . . . . . . . . . . . . . . . . . . .9.4.2Adding Extents to Existing Storage Areas . . . . . . . . . . . . . .9.5Progress Structure Remove Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9.6Maintaining Indexes and Tables
Contents v 4.5.1 Converting a Single-volume Version 8 Database With PROCOPY . . . . . . . . . . . . .
