Part IV
Oracle8 Administration

In This Part

•  Oracle8 Administrative Requirements

•  Routine Maintenance and Scheduling Tasks

•  Administration Using Enterprise Manager

•  Oracle8 Tools

•  Backup and Recovery

•  Oracle8 Database Tuning

•  Oracle8 Application Tuning

Chapter 19
Oracle8 Administrative Requirements

by Dan Hotka

In This Chapter

•  Overview of Oracle8 Administration

•  Backup and Recovery Concepts

•  Security and Access Concepts

•  Tablespace Maintenance Concepts

•  Schema Maintenance Concepts

•  Tuning Concepts

•  Miscellaneous Tasks

Overview of Oracle8 Administration

Oracle8 comprises many complex interrelated processes, files, and memory structures (see Figure 19.1). Connecting to Oracle8 is controlled by SQL*Net and Net8, each controlled by a different set of files. The data files must be backed up periodically, based on business needs. The memory structures greatly affect how well Oracle8 performs on a given hardware platform. Tuning and troubleshooting can be ongoing chores. Data replication needs, Web server access, and partitioning add additional administrative needs to an already complex environment.

Figure 19.1.  Oracle processes and files.

It is the job of the database administrator (DBA) to adequately handle each of these issues. Tasks such as installing the Oracle database software, setting up the SQL*Net and/or Net8 environment, and establishing the default memory usage parameters are handled once or on an infrequent basis. Strategies for database backup are established based on data recovery needs, and likewise, are adjusted rather infrequently. Adding and deleting user access privileges can be a daily task as can addressing connectivity and security issues. The various types of tuning require various types and times of attention: some happen at installation time, some during the application testing phase; some are proactive (monitoring for excessive resource utilization processes) and some reactive (end users, slow response times).

Oracle8 supplies many tools to aid the DBA in these tasks. Server Manager is a single management tool that allows the database to be started and shut down, controls the backup and recovery process, maintains user connectivity and resource access permissions, creates and maintains the tablespaces, and can monitor for various situations in the database such as locking, resource contention, and memory usage. Oracle Enterprise Manager is a series of graphical tools available for the Windows NT environment that provide the same services as Server Manager, but can also set up and monitor the SQL*Net and/or Net8 environments, monitor fragmentation, reorganize individual objects, schedule maintenance or routine tasks, and do almost-real-time monitoring. Oracle Enterprise Manager also contains a graphical interface to Recovery Manager, Oracle8’s new backup and recovery tool. Important tools for the Oracle environment since the Oracle V4 days, Export and Import allow for data and objects to be moved between Oracle servers and can play an important incremental backup role. SQL*Loader loads data from flat-file structures into the Oracle environment. Oracle8 provides some significant performance enhancements to this tool, primarily in the areas of table partitioning and parallel processing of the data loading process.

Backup and Recovery Concepts

Any computing environment is subject to a variety of hardware or software failures/problems that can cause a loss of stored data. Many circumstances can affect electronic data processing. Natural disasters, power outages, and machine failures are hard to predict. Other considerations that need to be taken into account are accidental and intentional corruption or destruction of data.

Each application has specific data requirement needs. These needs must be assessed accurately for the business needs of the applications. The frequency of performing backups depends on the application and business needs as well. For example, a database that is loaded from flat files every evening may not require that any backups be performed at all, whereas OLTP applications may not have any acceptable data loss or downtime at all. This application might lend itself well for Oracle’s standby database recovery option, utilizing frequent checkpoints and small archive log modes (for quick recovery and minimal data loss).

Many hardware issues, such as power outages and disk failure, can be averted entirely with available technology. Accidental data corruption is difficult to predict but, de- pending on backup/recovery strategies in place, can be nothing more than a temporary inconvenience. The possibility of intentional data corruption is a security issue requiring provisions for restoring or recovering data. Oracle8 has a variety of options available for all of these scenarios including Oracle’s Export and Import utilities, Oracle cold and hot backups, standby database, and Recovery Manager.

Oracle Export can be used to get individual data objects, functions, packages, user definitions, and permissions, entire tablespaces, and even the entire database into operating system files. Import can then import what was exported, to either the same Oracle instance or another Oracle instance. Export and Import tend to be rather slow, sometimes limited by operating system file limits, and are not well suited for very large databases (VLDB).

Cold backups are done when the Oracle RDBMS is in a down state and all of the physical files and initialization files are backed up. This method may be supplemented with Oracle’s Export and Import utilities to do incremental backups of changed objects, or better yet, to supplement Oracle’s Log Archive (see Chapter 23, “Backup and Recovery”). If archive mode is being utilized, all prior archive log files become irrelevant after a successful backup of all physical files is complete. This method is an “all or nothing” approach to recovery. If ARCHIVELOG mode is not utilized, and no other method is used to capture changes in the database, all changes are lost from the last cold backup.