Oracle Rdb7(tm) Guide to Database Maintenance
Release 7.0

A41748-1

Library

Product

Contents

Index

Prev Next

1
Oracle Rdb Database Maintenance

The need for efficient database management has become more urgent as systems grow in physical size, capacity, and complexity. Most businesses store critical data in database management systems. These databases often are shared among multiple office locations and used by large numbers of people performing varied tasks. Consequently, the businesses require that the database is functioning properly and is available for use at all times.

This chapter summarizes database management techniques and tools that help the database administrator (DBA) properly manage all the requirements of an Oracle Rdb database.

1.1 Database Administrator Responsibilities

Database administration involves planning, designing, implementing, maintaining, and tuning one or more databases.

Table 1-1 lists broad categories of DBA responsibilities and indicates the Oracle Rdb manual that provides information about the topic.

Table 1-1 Database Administrator Responsibilities
DBA Task   Involves...   Reference  

Design or reorganize the database

 

Initiating a complete analysis of the business needs and data requirements in order to achieve an appropriate database design and definition. Reorganizing or changing mature databases to reflect changing business needs.

 

Oracle Rdb Guide to Database Design and Definition

 

Implement the database

 

Fine tuning the definition of the database and its entities, setting up security mechanisms, setting up a data repository, and loading data.

 

Oracle Rdb Guide to Database Design and Definition

 

Maintain the database

 

Opening and closing a database, data backup and restoration, security auditing, and journaling: tasks that ensure the database availability, integrity, security, and scalability required to meet your business needs.

 

Oracle Rdb Guide to Database Maintenance

 

Tune the database

 

Collecting data usage statistics, testing performance, improving database access, and improving applications to attain optimal performance.

 

Oracle Rdb Guide to Database Performance and Tuning

 

Distribute or move the database

 

Distributing applications across a network distributed environment, or moving a database from a development environment to a production environment.

 

Oracle Rdb Guide to Distributed Transactions and Oracle Rdb Guide to Database Performance and Tuning

 

Provide a fault-tolerant database

 

Physically replicating a database, applications, and environment at a geographically remote standby site.

 

Oracle Rdb and Oracle CODASYL DBMS: Guide to Hot Standby Databases

 

This manual concentrates on the database administrator's job regarding the ongoing management of the database.

1.2 Database Management Requirements

Your overall goal as a DBA is to maintain and protect the integrity of the data and the availability of the database. Database management requirements are shown in Table 1-2.

Table 1-2 Management Requirements
Requirement   Description  

Analysis

 

Collect and display information about database users, data, and characteristics.

 

Recovery

 

Return the database to a correct, consistent state after a hardware or software failure, or human error.

 

Integrity

 

Ensure that data is correct. The database must not store data that is inconsistent with the rules of the business. Once stored in the database, data must persist. Partially entered transactions are rolled back.

 

Security

 

Protect data from inadvertent or deliberate destruction or viewing by an unauthorized person.

 

Concurrency

 

Ensure that multiple users and transactions can access the database concurrently and see a consistent view of the data.

 

1.3 Database Maintenance Tools

Once a database is installed and in use, it must be monitored and maintained. You maintain an Oracle Rdb database using a combination of data manipulation languages (for example, SQL) and Oracle RMU, the Oracle Rdb database management utility.

Table 1-3 lists general categories of database maintenance tasks, and the Oracle Rdb utilities and tools that help you perform the maintenance operations.

Table 1-3 Database Management Tools
Operation   Utility   Purpose  

Analyze

 

RMU Analyze

 

Collects and displays information about how data is being stored and about the index structure

 

 

RMU Show

 

Displays information about the Oracle Rdb database running on the node from which you issue the Show command

 

 

RMU Verify

 

Checks internal database structures for consistency and integrity

 

 

RMU Dump

 

Displays information about the contents, structure, and users of the database

 

Control

 

RMU Open

 

Manually opens the database for normal user access

 

 

RMU Close

 

Manually closes the database for maintenance and restricted access

 

 

RMU Monitor

 

Starts or stops the Oracle Rdb monitor process

 

 

RMU Set

 

Allows you to control AIJ login, audit login, corrupt pages, and privileges

 

Integrity

 

RMU Backup

 

Creates full or partial (incremental) backup copy of the database or after-image journal file

 

 

RMU Restore

 

Restores the database to its state at the beginning of the execution of RMU Backup

 

 

RMU Optimize

 

Recovers the database to the last committed transaction

 

 

RMU Resolve

 

Eliminates unneeded and duplicate journal records and orders the journal records

 

 

RMU Server After_Journal

 

Commits or rolls back any unresolved distributed transactions in the database, and manually starts or stops the AIJ log server (ALS) process for the specified database

 

Load

 

RMU Load

 

Loads data from a file into an Oracle Rdb table

 

Unload

 

RMU Unload

 

Unloads data from a specific table or view into a file

 

Restructure or Update

 

SQL Import and Export

 

Assists with major restructuring or migration of a database

 

 

RMU Convert

 

Upgrades an Oracle Rdb database from a previous version to the current version

 

 

RMU Extract

 

Reads and decodes Oracle Rdb metadata and reconstructs equivalent statements in RDO or SQL

 

Replication

 

RMU Copy_Database

 

Manually replicates a database

 

Replication

 

RMU Replicate

 

Automatically replicates a database

 

1.4 Database Maintenance Tasks

Table 1-4 lists database maintenance tasks according to when they should be performed (daily, weekly, monthly, and so forth). Table 1-4 also shows the Oracle Rdb utilities and tools that help you perform the maintenance operations.

Table 1-4 Database Maintenance Activities
Daily Maintenance   Method  

Perform incremental backup operation of database

 

RMU Backup/Incremental

 

Attach to database, map root file, map OpenVMS global sections or Digital UNIX shared memory partitions

 

RMU Open

 

Detach from database, unmap OpenVMS global sections or Digital UNIX shared memory partitions, abort user processes

 

RMU Close

 

Report on database activity

 

RMU Show
or on OpenVMS systems
you can type SYS$SYSTEM:RDMMON411.LOG

 

Perform incremental verification of database integrity

 

RMU Verify Incremental

 
Weekly Maintenance   Method  

Perform full backup operation of database

 

RMU Backup

 

Report on database space usage

 

RMU Analyze

 

Display performance indicators interactively

 

RMU Show Statistics

 

Perform full verification of database integrity prior to an RMU Backup operation

 

RMU Verify All

 
Database Startup   Method  

Create monitor process, start monitor log

 

RMU Monitor Start
(Use at system startup)

 

Attach to database, map root file, map OpenVMS global sections or Digital UNIX shared memory partitions

 

RMU Open
(Use after restart)

 
Database Shutdown   Method  

Detach from database, unmap OpenVMS global sections or Digital UNIX shared memory partitions, abort user processes

 

RMU Close
(Use before maintenance operations)

 

Terminate the monitor process, let users currently attached finish, and prevent new users from attaching to the database

 

RMU Monitor Stop
(Use at system shutdown)

 
Troubleshooting   Method  

Restore database

 

RMU Restore
(Use after unrecoverable loss of database)

 

Restore root file

 

RMU Restore Only_Root
(Use after unrecoverable loss of root file)

 

Roll database forward

 

RMU Recover
(Use after RMU Restore operation)

 

Report on database integrity after a system failure
or on database integrity of a restored database after the RMU Restore operation

 

RMU Verify
(Use after system failure or after using the RMU Restore operation)

 

Patch the database if the corruption is minor in scope

 

RMU Alter
(Use after verification reports a corruption problem)

 

Report on database space usage

 

RMU Analyze
(Use after noticing performance problems)

 

Display performance indicators interactively

 

RMU Show Statistics
(Use after noticing performance problems)

 

Display contents of database, storage area, and .SNP files, including root information

 

RMU Dump
(Use after noticing performance problems)

 

Rebuild SPAM and ABM pages, fix page tail problems, create and initialize new snapshot (.snp) files for those that are missing

 

RMU Repair
(Use after noticing SPAM page corruption, area bit map (ABM) page or page tail errors, or missing .snp files)

 

Set pages corrupt; set pages consistent

 

RMU Set Corrupt_Pages
(Use after verification reports a corruption problem)

 

Display corrupt pages by disk, area, and page

 

RMU Show Corrupt_Pages
(Use after verification reports a corruption problem, corrupt pages have been previously set, or a display of the header file indicates corrupt pages exist)

 
General RMU Maintenance   Method  

Create a new version of the monitor log file

 

RMU Monitor Reopen_Log

 

Display contents of database files

 

RMU Dump

 

Report on current database users

 

RMU Show Users

 

Report on users of all databases

 

RMU Show System

 

Copy a table or view into a BRP format or RMS file

 

RMU Unload

 

Copy data unloaded using the RMU Unload command into a table

 

RMU Load

 

Convert an existing database file to a format compatible with a new version of Oracle Rdb

 

RMU Convert

 

Create a backup copy of the database prior to conversion and a backup copy of the database immediately following a successful conversion and verification

 

RMU Backup

 

Create a backup file of the database .aij file

 

RMU Backup After_Journal

 

Improve the performance of rolling forward .aij files

 

RMU Optimize After_Journal

 

Update the cardinality in the metadata when the storage area has been set to read-only and when cardinality values stored in the system tables no longer accurately reflect the characteristic of the data stored in the database

 

RMU Collect Optimizer Statistics

 

Copy a database on line

 

RMU Copy_Database Online

 

Move storage areas of a database when the database is off line

 

RMU Move_Area

 

Enable Oracle Rdb security auditing

 

RMU Set Audit

 

Display the set of Oracle Rdb security auditing characteristics established with the RMU Set Audit command

 

RMU Show Audit

 

Enable Oracle Rdb to load security audit records from the security audit journal into a table in your database

 

RMU Load Audit

 

Enable modification of the root file access control list (ACL) for a database

 

RMU Set Privilege

 

Decode system table information and reconstruct equivalent commands in the selected interface (SQL or RDO) for the definition of that database

 

RMU Extract

 

Force all active database processes on all nodes to immediately perform a checkpoint operation

 

RMU Checkpoint

 

Enable setting of AIJ attributes

 

RMU Set After_Journal

 

Display AIJ configuration information and optionally initialize AIJ symbols

 

RMU Show After_Journal

 
General SQL Maintenance   Method  

Modify schema and database characteristics

 

SQL ALTER DATABASE

 

Copy a database into an intermediate, compressed interchange (.RBR) file format for migration or restructuring

 

SQL EXPORT DATABASE

 

Copy the contents of the .RBR files created with the EXPORT statement into a database to complete database migration and restructuring

 

SQL IMPORT DATABASE

 

Copy a database into an intermediate, compressed .RBR file format for migration or restructuring, but contain only metadata and no data

 

SQL EXPORT DATABASE
NO DATA

 

Copy the contents of the .RBR files (with data) created with the EXPORT statement into a database to complete database migration and restructuring, but contain only metadata and no data

 

SQL IMPORT DATABASE
NO DATA

 

Update database access control by adding new database users or modifying user privileges

 

SQL GRANT

 

Update database access control by deleting database users or modifying user privileges

 

SQL REVOKE

 

Reorganize the relation records or table rows within one or more storage areas according to partitions specified

 

SQL ALTER STORAGE MAP
REORGANIZE

 

1.5 Database Availability

Availability is the amount of time that a computing system provides application service to its users. Making the database continuously available to users and applications is a primary goal of database administration.

The availability of a database management system can be compromised not only by unscheduled downtime such as hardware or software failures, but also by routine maintenance tasks such as backup operations, definition changes, and file or data restructuring.

Some database management systems are restricted by their degree of fault tolerance. Oracle Rdb has few restrictions and provides a high degree of availability. The following sections describe the availability features of Oracle Rdb and how they minimize the impact of scheduled and unscheduled downtime on database access.

1.5.1 Fault-Tolerant Oracle Rdb Databases

Oracle Rdb provides fault tolerance by allowing you to physically duplicate a database, applications, and environment at a geographically remote standby site to provide fault tolerance. In the event of a failure, Rdb continues to provide the required services by transparently failing over users and applications to the replicated database.

You use Oracle RMU to replicate a database at a remote site and automate after-image journal backup and rollforward operations to provide a nonintrusive, high-performance solution to data availability. See the Oracle Rdb and Oracle CODASYL DBMS: Guide to Hot Standby Databases manual for complete information.

1.5.2 Online Backup Operations

Oracle Rdb supports online backup operations that allow users access to the database during that operation. The online backup operation uses snapshot (.snp) files to achieve a high degree of database availability. Oracle Rdb also supports fast online or offline incremental backup operations, which reduce backup time and ease maintenance by reducing the amount of data that needs to be backed up. Fast incremental backup operations use memory bitmaps for each storage area's space area management (SPAM) pages in the global section or shared memory partition of each node.

Oracle Rdb can provide the fastest and most reliable backup capabilities of any database system on Alpha and VAX computers. Oracle Rdb can back up multiple storage areas at the same time, concurrently running several tape drives through its support of the multithreaded backup operation.


Note:

The number of tape drives that can be used concurrently is limited only by the system's I/O capacity.

 

Refer to Chapter 7 for more information about backing up your database.

1.5.3 Online By-Area and By-Page Restore and Recovery Operations, and By-Area Move Operations

Oracle Rdb supports online by-area and by-page restore and recovery operations as well as online move storage area operations. If a storage area or pages within a storage area need to be restored and recovered, or if a storage area needs to be moved for some reason, the database need not be shut down and made inaccessible to users during these maintenance operations. In fact, only pages within an area or areas that are being restored and recovered become inaccessible until the restore and recover operation completes. Similarly, only areas that are moved are inaccessible until the move operation completes. Once the area or pages within an area are successfully restored and recovered, or the area is moved, it is accessible to users again. See Chapter 8, Chapter 9, and the Oracle Rdb Guide to Database Design and Definition for more information.

1.5.4 Disabling Journaling for Write-Once Storage Areas

Multimedia applications can store very large amounts of list data (images, documents, video, voice, and so forth). If after-image journaling is enabled, sufficient storage space must be available to handle the large .aij files that can result. Also, additional tape media may be required to back up these large .aij files. For some applications, this may be impractical. An alternative is to disable journaling of list data stored in write-once storage areas on write-once, read-many (WORM) devices while continuing to journal all other database activity.

1.5.5 Cluster and Networkwide Automatic Recovery

Oracle Rdb has supported fault tolerance in a cluster environment since V2.0 (1985) of Oracle Rdb, so if a CPU goes off line or an HSC node fails, Oracle Rdb automatically handles recovery and rollback. Refer to the Oracle Rdb Guide to Database Performance and Tuning for more information on this process.

1.5.6 Automatic Cleanup

The following online activities are automatically performed by the database while users are attached to it:

See Chapter 9, Chapter 10, the Oracle Rdb Guide to Database Design and Definition, and the Oracle Rdb Guide to Database Performance and Tuning for more information.

1.5.7 Online DBA Activities

As a DBA, you can perform the following activities on line while users are attached to the database:

See Chapter 3, Chapter 5, Chapter 7, Chapter 8, Chapter 9, the Oracle Rdb Guide to Database Design and Definition, and the Oracle Rdb Guide to Database Performance and Tuning for more information.

1.5.8 Offline DBA Activities

As DBA, you must perform the following activities off line because they require the database to be shut down:

Refer to the Oracle Rdb Guide to Database Design and Definition for more information on changing database characteristics, adding new storage areas, changing and deleting storage areas, adding and deleting indexes, and adding and deleting storage maps.

1.5.9 DBA Activities Requiring a Database Reload Operation

The following activities require the database to be shut down, unloaded with the SQL EXPORT statement, and reloaded with the SQL IMPORT statement:

Refer to the Oracle Rdb Guide to Database Design and Definition for more information on using the SQL EXPORT and IMPORT statements.

1.5.10 Quick and Automatic Database Recovery

Oracle Rdb automatically detects abnormal termination of users' transactions and initiates rollback and recovery without causing data inconsistency. Refer to Chapter 10 for more information on journaling and recovery for update transactions, and the recovery-unit journal (.ruj) file.

In a cluster environment, Oracle Rdb will coordinate recovery even if the node that started the original transaction is no longer present, thereby ensuring recovery capability and database integrity in a cluster environment. In addition, database recovery processes (DBRs) start in parallel to recover from node failures. Refer to the Oracle Rdb Guide to Database Performance and Tuning for more information on the automatic recovery procedure.

1.5.11 Database Integrity

Database integrity has been one of the primary features of Oracle Rdb since its inception. Automatic detection, rollback, and recovery of incomplete transactions through user-based recovery-unit journaling, after-image journaling, and use of the OpenVMS Distributed Lock Manager means that Oracle Rdb maintains optimum integrity in all OpenVMS environments. These environments include single or multiple CPU nodes, CI clusters, local area clusters, mixed clusters, and DECnet distributed environments. Each database page contains a checksum, which is checked by Oracle Rdb and recalculated after each update.

Refer to Chapter 7 for a description and examples of database backup and verification, Chapter 9 for information on journaling and recovery, and the Oracle Rdb Guide to Database Performance and Tuning for information on using Oracle Rdb in a cluster environment.

1.5.12 Checking Database Integrity and Evaluating Performance

The Oracle RMU management utility includes the RMU Verify, RMU Dump, and RMU Analyze commands for checking database integrity and performing database analysis.

A database should be verified regularly. If problems are detected, the cause must be determined before the database is made accessible to users again. Verifying a database is discussed in Chapter 5. One method of isolating database integrity problems is to display the contents of the page that is corrupt for a more detailed inspection. Displaying database pages and interpreting the contents are described in Chapter 11 and Chapter 12. Information on database integrity is described in Chapters 7, 8, 9, and in the Oracle Rdb Guide to Database Performance and Tuning.

The performance of a database application should be monitored regularly to provide a benchmark for the application and to detect early signs of problems as the database grows or changes in size. The RMU Show Statistics command is used to analyze database performance. For more information about DBA tools for evaluating performance and database behavior, refer to the Oracle Rdb Guide to Database Performance and Tuning.

Occasionally, transactions may compete for resources and cause deadlocks to occur. Deadlocks are detected and broken by Oracle Rdb (through its use of the OpenVMS Distributed Lock Manager). Information on deadlocks and other lock statistics can be viewed and recorded using the RMU Show Statistics command. Refer to the Oracle Rdb Guide to Database Performance and Tuning for more information on using this command and interpreting its results.

1.6 Creating Sample Single-File and Multifile Databases

This manual describes maintenance topics and provides examples from the sample personnel databases, single-file personnel and multifile mf_personnel, which are used throughout Oracle Rdb documentation. You can follow along and type in most of the examples if you have access to copies of these databases.

The sample personnel database created by the Installation Verification Procedure (IVP) is small and is a single-file database. It has only 9 tables, each containing from a few to 100 rows, and 3 views. For more information about the location and creation of the sample personnel databases, refer to the Migrating Oracle Rdb Databases and Applications to Digital UNIX or the Migrating Oracle Rdb Databases and Applications to OpenVMS Alpha.




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index