R/3 Database Guide: Informix (BC-DB-INF-DBA)
Purpose
This component lets you administer your Informix database with the R/3 System. Read this
documentation to make sure that you administer your database as efficiently as possible, which
helps your company get the most from its R/3 System.
Contents
R/3 Database Guide: Informix (BC-DB-INF-DBA) ...........................................10
Getting Started with Informix and R/3....................................................................................... 12
Checklist: Configuration and Setup .......................................................................................... 13
Checklist: Regular Activities...................................................................................................... 15
Checklist: Occasional Activities................................................................................................ 17
SAPDBA for Informix .................................................................................................................. 18
Informix Tools............................................................................................................................. 20
Computing Center Management System (CCMS).................................................................... 22
Informix Dynamic Server ............................................................................................................ 23
Operating System for the Informix Database........................................................................... 24
Management of Informix Database Growth .............................................................................. 26
Database Setup for Production.................................................................................................. 28
Database Growth Monitoring ..................................................................................................... 30
Recognition of Space Problems and Errors............................................................................. 33
Extension of a Dbspace.............................................................................................................. 36
Extending a Dbspace Without SAPDBA.................................................................................. 39
Creation of a New Dbspace........................................................................................................ 41
Data Recovery for Informix ........................................................................................................ 42
Getting Started with Data Recovery for Informix..................................................................... 44
Informix Data Recovery............................................................................................................ 45
Informix Archive........................................................................................................................ 48
Informix Whole-System and Storage-Space Backups............................................................. 51
Informix Logical-Log Backup.................................................................................................... 54
Informix Restore....................................................................................................................... 56
Comparison of ON-Bar, ON-Archive, and ontape for Data Recovery...................................... 59
ON-Bar for Data Recovery .......................................................................................................... 62
Configuring ON-Bar.................................................................................................................. 65
ON-Archive for Data Recovery................................................................................................... 69
Getting Started with ON-Archive.............................................................................................. 71
Entering ON-Archive Commands Manually........................................................................ 73
Configuration of ON-Archive .................................................................................................... 75
SAP Scripts for ON-Archive................................................................................................ 76
Preparing SAP Scripts for ON-Archive .......................................................................... 78
Configuration Files for ON-Archive ..................................................................................... 80
Editing the ONCONFIG File for ON-Archive.................................................................. 81
Editing the oper_deflt.arc File for ON-Archive............................................................... 83
Editing the config.arc File for ON-Archive ..................................................................... 85
Defining Devices for ON-Archive with SAP Scripts.................................................. 87
Defining Devices for ON-Archive Without SAP Scripts ............................................ 89
Volume Sets and Volumes for ON-Archive......................................................................... 92
Setting Up Volume Sets and Volumes for ON-Archive with SAP Scripts...................... 94
Setting Up Volume Sets and Volumes for ON-Archive Without SAP Scripts................ 97
ontape for Data Recovery......................................................................................................... 100
Configuring ontape................................................................................................................. 101
Database Backup (ON-Bar) ...................................................................................................... 104
Approach to Database Backup (ON-Bar)............................................................................... 105
Developing Good Practice for Database Backup (ON-Bar).............................................. 106
Choosing Levels and Frequency of Database Backup (ON-Bar)..................................... 107
Scheduling Backups and Archives with CCMS (ON-Bar and ON-Archive)...................... 108
Requirements for a Database Backup (ON-Bar) ................................................................... 111
Setting Up Storage Media for Database Backup (ON-Bar) .............................................. 112
Creation of a Database Backup (ON-Bar) ............................................................................. 114
Performing a Manual Database Backup (ON-Bar) ........................................................... 116
Checking Database Backup Status and Result with Activity Log (ON-Bar) ..................... 119
Checking Result of Database Backup or Archive with Message Log............................... 121
Completing a Database Backup (ON-Bar)........................................................................ 122
Archive (ON-Archive and ontape)............................................................................................ 123
Approach to Archive (ON-Archive)......................................................................................... 125
Developing Good Practice for Archive (ON-Archive and ontape)..................................... 126
Choosing Archive Levels and Frequency (ON-Archive and ontape)................................ 127
Scheduling Backups and Archives with CCMS (ON-Bar and ON-Archive)...................... 128
Using the SAPDBA Recovery Report with Archives and Logical-Log Backups (ON-Archive) ..... 130
Using Parallel Archive (ON-Archive)................................................................................. 131
Requirements for an Archive (ON-Archive) ........................................................................... 132
Setting Up Storage Devices for Archive (ON-Archive) ..................................................... 133
Setting Up Volume Sets and Volumes for Archive (ON-Archive) ..................................... 134
Setting Up Tape Volumes for Archive (ON-Archive and ontape) ..................................... 135
Setting Up Parallel Archive (ON-Archive) ......................................................................... 137
Setting Up Unattended Archive (ON-Archive) .................................................................. 139
Creation of an Archive (ON-Archive) ..................................................................................... 141
Determining the Volume Required (ON-Archive).............................................................. 143
Performing an Archive (ON-Archive) ................................................................................ 144
Checking Archive Status (ON-Archive)............................................................................. 146
Checking Archive Result with Activity Log (ON-Archive).................................................. 147
Checking Result of Database Backup or Archive with Message Log............................... 150
Checking Tapes After an Archive (ON-Archive and ontape) ............................................ 151
Completing an Archive (ON-Archive)................................................................................ 152
Approach to Archive (ontape) ................................................................................................ 153
Developing Good Practice for Archive (ON-Archive and ontape)..................................... 154
Choosing Archive Levels and Frequency (ON-Archive and ontape)................................ 155
Requirements for an Archive (ontape) ................................................................................... 156
Setting Up Tape Volumes for Archive (ON-Archive and ontape) ..................................... 157
Creation of an Archive (ontape) ............................................................................................. 159
Performing an Archive (ontape)........................................................................................ 160
Checking Result of Database Backup or Archive with Message Log............................... 161
Checking Tapes After an Archive (ON-Archive and ontape) ............................................ 162
Logical-Log Backup.................................................................................................................. 163
Approach to Logical-Log Backup (ON-Bar) ........................................................................... 165
Developing Good Practice for Logical-Log Backup .......................................................... 166
Choosing Continuous or Manual Logical-Log Backup (ON-Bar)...................................... 168
Scheduling Backups and Archives with CCMS (ON-Bar and ON-Archive)...................... 169
Requirements for a Logical-Log Backup (ON-Bar) ................................................................ 171
Setting Up Storage Devices for Logical-Log Backup (ON-Bar) ........................................ 172
Setting Parameters for Logical-Log Backup (ON-Bar) ..................................................... 174
Creation of a Logical-Log Backup (ON-Bar) .......................................................................... 176
Performing a Continuous Logical-Log Backup (ON-Bar).................................................. 177
Performing a Manual Logical-Log Backup (ON-Bar) ........................................................ 178
Checking Logical-Log Backup Result with Activity Log (ON-Bar) .................................... 180
Checking Logical-Log Backup Result with Message Log................................................. 181
Completing Logical-Log Backup (ON-Bar) ....................................................................... 182
Approach to Logical-Log Backup (ON-Archive) ..................................................................... 183
Developing Good Practice for Logical-Log Backup .......................................................... 184
Choosing Automatic Logical-Log Backup (ON-Archive)................................................... 186
Choosing Continuous Logical-Log Backup (ON-Archive)................................................. 189
Scheduling Backups and Archives with CCMS (ON-Bar and ON-Archive)...................... 191
Using the SAPDBA Recovery Report with Archives and Logical-Log Backups (ON-Archive)......... 193
Requirements for a Logical-Log Backup (ON-Archive).......................................................... 194
Setting Up Storage Devices for Logical-Log Backup (ON-Archive).................................. 195
Setting Up Volume Sets and Volumes for Logical-Log Backup (ON-Archive) ................. 196
Meeting Additional Logical-Log Backup Requirements (ON-Archive).............................. 198
Creation of a Logical-Log Backup (ON-Archive).................................................................... 199
Performing Automatic Logical-Log Backup (ON-Archive) ................................................ 202
Performing Continuous Logical-Log Backup to Tape (ON-Archive)................................. 204
Performing Continuous Logical-Log Backup to Disk (ON-Archive) .................................. 206
Checking Logical-Log Backup Status (ON-Archive)......................................................... 208
Checking Logical-Log Backup Result with Activity Log (ON-Archive).............................. 209
Checking Logical-Log Backup Result with Message Log................................................. 210
Checking Tapes After a Logical-Log Backup (ON-Archive and ontape) .......................... 211
Completing Logical-Log Backup (ON-Archive) ................................................................. 212
Approach to Logical-Log Backup (ontape)............................................................................. 214
Developing Good Practice for Logical-Log Backup .......................................................... 215
Choosing Automatic or Continuous Logical-Log Backup (ontape)................................... 217
Requirements for a Logical-Log Backup (ontape) ................................................................. 218
Setting Up Tape Devices for Logical-Log Backup (ontape).............................................. 219
Creation of a Logical-Log Backup (ontape) ........................................................................... 220
Performing an Automatic Logical-Log Backup (ontape) ................................................... 221
Performing a Continuous Logical-Log Backup (ontape)................................................... 222
Checking Logical-Log Backup Result with Message Log................................................. 224
Checking Tapes After a Logical-Log Backup (ON-Archive and ontape) .......................... 225
Completing Logical-Log Backup (ontape)......................................................................... 226
Emergency Logical-Log Backup.............................................................................................. 227
Performing Emergency Logical-Log Backup (ON-Bar).......................................................... 229
Emergency Logical-Log Backup Using SAP Scripts (ON-Archive)........................................ 230
Preparing for Emergency Logical-Log Backup Using SAP Scripts (ON-Archive) ............ 231
Performing Emergency Logical-Log Backup Using SAP Scripts (ON-Archive)................ 232
Checking Result of Emergency Logical-Log Backup with Activity Log (ON-Archive)....... 236
Emergency Logical-Log Backup Without SAP Scripts (ON-Archive)..................................... 238
Backing up Logical-Log Files Using ondatartr (ON-Archive) ............................................ 240
Checking Result of Emergency Logical-Log Backup with Activity Log (ON-Archive)....... 242
Cataloging the Emergency Save Set (ON-Archive).......................................................... 244
Copying Backed-Up Logs to Regular Volume Set (ON-Archive)...................................... 245
Copying Emergency Save Set to Regular Volume Set (ON-Archive) .............................. 246
Deleting the Emergency Save Set (ON-Archive).............................................................. 247
Performing Emergency Logical-Log Backup (ontape) ........................................................... 248
Preventing Emergency Logical-Log Backup.......................................................................... 250
Restore ...................................................................................................................................... 252
Getting Started with Restore.................................................................................................. 254
Identifying the Problem Requiring a Restore.................................................................... 255
Choosing the Correct Kind of Restore .............................................................................. 258
Preparing for a Restore with the SAPDBA Recovery Report (ON-Archive) ..................... 260
Full-System Cold Restore (ON-Bar) ...................................................................................... 262
Preparing for a Full-System Cold Restore (ON-Bar) ........................................................ 263
Salvaging Logical Logs for a Full-System Cold Restore (ON-Bar)................................... 265
Performing Physical Restore for a Full-System Cold Restore (ON-Bar) .......................... 267
Performing Logical Restore for a Full-System Cold Restore (ON-Bar) ............................ 270
Partial-System Warm Restore (ON-Bar)................................................................................ 273
Preparing for a Partial-System Warm Restore (ON-Bar).................................................. 274
Performing Physical Restore for a Partial-System Warm Restore (ON-Bar) ................... 275
Performing Logical Restore for a Partial-System Warm Restore (ON-Bar) ..................... 277
Full-System Cold Restore (ON-Archive) ................................................................................ 279
Preparing for a Full-System Cold Restore (ON-Archive).................................................. 281
Salvaging Logical Logs for a Full-System Cold Restore (ON-Archive) ............................ 284
Performing Physical Restore for a Full-System Cold Restore (ON-Archive).................... 287
Performing Logical Restore for a Full-System Cold Restore (ON-Archive)...................... 291
Partial-System Warm Restore (ON-Archive) ......................................................................... 295
Preparing for a Partial-System Warm Restore (ON-Archive) ........................................... 296
Performing Physical Restore for a Partial-System Warm Restore (ON-Archive) ............. 297
Performing Logical Restore for a Partial-System Warm Restore (ON-Archive) ............... 299
Full-System Cold Restore (ontape)........................................................................................ 302
Preparing for a Full-System Cold Restore (ontape) ......................................................... 303
Performing a Full-System Cold Restore (ontape)............................................................. 304
Partial-System Warm Restore (ontape) ................................................................................. 306
Preparing for a Partial-System Warm Restore (ontape)................................................... 307
Performing a Partial-System Warm Restore (ontape) ...................................................... 308
Recovery of a Lost Mirrored Chunk ....................................................................................... 309
Identifying a Lost Mirrored Chunk..................................................................................... 310
Creating a New Link for a Lost Mirrored Chunk ............................................................... 311
Recovering a Lost Mirrored Chunk Online........................................................................ 313
SAPDBA for Informix ................................................................................................................ 314
Getting Started with SAPDBA.................................................................................................. 316
SAPDBA Setup ...................................................................................................................... 317
Setting Up SAPDBA Users ............................................................................................... 318
Setting Up SAPDBA Environment Variables .................................................................... 319
Setting Up SAPDBA Screen Handling.............................................................................. 321
Setting Up SAPDBA Working Directories ......................................................................... 322
Starting SAPDBA ................................................................................................................... 324
SAPDBA Heading Lines ................................................................................................... 326
Use of SAPDBA with UNIX and NT .................................................................................. 327
Printing a SAPDBA Report..................................................................................................... 329
SAPDBA Housekeeping......................................................................................................... 330
SAPDBA Files, Directories, and Reports.......................................................................... 331
Server Mode with SAPDBA....................................................................................................... 335
Changing Server Mode with SAPDBA................................................................................... 336
Dbspaces with SAPDBA........................................................................................................... 337
Listing Chunks with SAPDBA................................................................................................. 339
Listing Dbspaces with SAPDBA............................................................................................. 343
Listing Devices with SAPDBA................................................................................................ 346
Listing Chunk Paths with SAPDBA........................................................................................ 349
Adding a Chunk with SAPDBA............................................................................................... 350
Dropping a Chunk with SAPDBA........................................................................................... 352
Adding a Dbspace with SAPDBA........................................................................................... 353
Dropping a Dbspace with SAPDBA ....................................................................................... 355
Renaming Chunk Paths with SAPDBA.................................................................................. 356
Reorganization with SAPDBA.................................................................................................. 358
Storage Analysis of Tables and Indexes with SAPDBA ........................................................ 360
Analyzing Tables by Fill Level, Size, and Extents with SAPDBA ..................................... 362
Analyzing Table Information with SAPDBA ...................................................................... 365
Analyzing Tables for Critical Next Extent Size with SAPDBA .......................................... 368
Analyzing Indexes by Fill Level, Leaves, and Levels with SAPDBA ................................ 370
Analyzing Indexes of a Table with SAPDBA..................................................................... 373
Analyzing Index Information with SAPDBA ...................................................................... 375
Analyzing a Tablespace with SAPDBA............................................................................. 378
Reorganization of Tables, Indexes, and Dbspaces with SAPDBA ........................................ 380
Reorganizing a Single Index with SAPDBA...................................................................... 381
Reorganizing All Indexes of One Table with SADPBA..................................................... 383
Preparing for Table or Dbspace Reorganization with SAPDBA ....................................... 385
Reorganizing a Single Table with SAPDBA...................................................................... 387
Reorganization of a Group of Tables or Dbspace with SAPDBA ..................................... 392
Reorganizing a Group of Tables with SAPDBA........................................................... 394
Input File for Reorganizing Groups of Tables with SAPDBA ................................. 396
Reorganizing a Dbspace and Its Tables with SAPDBA .............................................. 398
Reorganization Troubleshooting with SAPDBA........................................................... 402
Reorganization Log with SAPDBA ......................................................................... 403
Fixing Difficult Reorganization Problems with SAPDBA ........................................ 409
Restarting Uncompleted Reorganization Requests with SAPDBA........................ 413
Deleting Uncompleted Reorganization Requests with SAPDBA ........................... 415
Finishing a Table or Dbspace Reorganization with SAPDBA........................................... 416
Data Consistency with SAPDBA.............................................................................................. 418
How to Run Data Consistency Checks with SAPDBA........................................................... 420
Checking a Single Table for Data Consistency with SAPDBA .............................................. 422
Checking Tables of a Dbspace for Data Consistency with SAPDBA .................................... 424
Checking Tables with Blob Fields for Data Consistency with SAPDBA ................................ 426
Checking All Tables of R/3 Database for Data Consistency with SAPDBA .......................... 428
Checking Indexes of Tables with 'Blob' Fields Using ONCHECK -cI with SAPDBA ............. 430
Checking Tables with 'Blob' Fields Using ONCHECK -cD with SAPDBA ............................. 432
Deleting Uncompleted Consistency Requests with SAPDBA................................................ 434
Restarting Uncompleted Consistency Requests with SAPDBA ............................................ 435
Log for Data Consistency with SAPDBA................................................................................ 437
Update Statistics with SAPDBA............................................................................................... 440
How SAPDBA Performs Update Statistics............................................................................. 442
Listing Tables with Deviation with SAPDBA .......................................................................... 444
Listing Tables with Non-Updated Indexes with SAPDBA ...................................................... 446
Listing Update Statistics Log File........................................................................................... 447
Updating Statistics for One Table with SAPDBA................................................................... 449
Updating Statistics for All Tables with SAPDBA .................................................................... 451
Updating Statistics for All System Tables with SAPDBA....................................................... 453
Updating Statistics from the Command Line with SAPDBA .................................................. 455
Customizing Update Statistics with SAPDBA ........................................................................ 459
Log Table for Update Statistics with SAPDBA....................................................................... 460
Logging Mode with SAPDBA ................................................................................................... 462
Switching to ............................................................................................................................ 463
Switching to ............................................................................................................................ 464
Recovery Report with SAPDBA............................................................................................... 465
Preparing for Recovery Reports with SAPDBA ..................................................................... 466
Listing Recovery Report with SAPDBA.................................................................................. 468
Creation of Recovery Report with SAPDBA (Informix 6.0) ................................................... 474
Preparing for Recovery Reports with SAPDBA (Informix 6.0) ............................................... 475
Creating the Recovery Report with SAPDBA (Informix 6.0) .................................................. 476
DB System Checks with SAPDBA........................................................................................... 477
Checking the DB System with SAPDBA................................................................................ 478
System Information with SAPDBA .......................................................................................... 481
Listing System Information with SAPDBA.............................................................................. 482
Emergency Logical-Log Backup with SAPDBA (Informix 6.0) ............................................. 483
Preparing for Emergency Logical-Log Backup with SAPDBA (Informix 6.0)......................... 484
Performing Emergency Logical-Log Backup with SAPDBA (Informix 6.0) ............................ 486
Solutions for Top Informix Problems...................................................................................... 488
Solving Database Startup Problems ....................................................................................... 489
Solving Problems with 32-Bit SAPDBA Access to 64-Bit Software..................................... 490
Solving Problems with Memory Allocation on HP-UX........................................................... 491
Solving Problems with Corruption .......................................................................................... 493
Solving Assert Problems.......................................................................................................... 495
Solving Error Message INF-271 Without ISAM Error Code................................................... 496
Solving Performance Problems with a SELECT Statement.................................................. 498
Solving Problems with Out-of-Date Optimizer Access Paths............................................... 502
Solving Problems with In-Place Alter Table ........................................................................... 504
Examples for In-Place Alter Table.......................................................................................... 505
Download attached file: You must be Loged in to download file