Sunday, October 21, 2012

Concepts for Database Administrators part 1

Concepts for Database Administrators

This chapter contains the following sections:

Duties of Database Administrators

The principal responsibility of a database administrator (DBA) is to make enterprise data available to its users. DBAs must work closely with the developers to ensure that their applications make efficient use of the database, and with system administrators to ensure that physical resources are adequate and used efficiently.
Oracle DBAs are responsible for understanding the Oracle Database architecture and how the database works. DBAs can expect to perform the following tasks:
  • Installing, upgrading, and patching Oracle Database software
  • Designing databases, including identifying requirements, creating the logical design (conceptual model), and physical database design
  • Creating Oracle databases
  • Developing and testing a backup and recovery strategy, backing up Oracle databases regularly, and recovering them in case of failures
  • Configuring the network environment to enable clients to connect to databases
  • Starting up and shutting down the database
  • Managing storage for the database
  • Managing users and security
  • Managing database objects such as tables, indexes, and views
  • Monitoring and tuning database performance
  • Investigating, gathering diagnostic data for, and reporting to Oracle Support Services any critical database errors
  • Evaluating and testing new database features
The preceding tasks, and many others, are described in Oracle Database 2 Day DBA and Oracle Database Administrator's Guide.
The types of users and their roles and responsibilities depend on the database environment. A small database may have one DBA. A very large database may divide the DBA duties among several specialists, for example, security officers, backup operators, and application administrators.

Tools for Database Administrators

Oracle provides several tools for use in administering a database. This section describes some commonly used tools:

Oracle Enterprise Manager

Oracle Enterprise Manager (Enterprise Manager) is a system management tool that provides centralized management of a database environment. Combining a graphical console, Oracle Management Servers, Oracle Intelligent Agents, common services, and administrative tools, Enterprise Manager provides a comprehensive systems management platform for Oracle products.
The Web-based Enterprise Manager Database Control (Database Control) is the primary tool for managing an Oracle database. It is installed with Oracle Database. You can use Database Control to perform administrative tasks such as:
  • Diagnosing, modifying, and tuning the database
  • Grouping related targets together to facilitate administration tasks, sharing tasks with other administrators, and scheduling tasks at varying time intervals
  • Configuring and managing Oracle Net Services for an Oracle home (see "Overview of Oracle Networking Architecture")
  • Launching integrated Oracle and third-party tools
The following figure shows the Database Home page of Database Control. The subpage links across the top of the page enable you to access performance, availability, and other database administration pages. The subsections of the Database Home page provide information about the environment and status of the database.
Description of homepage_small.gif follows
Description of the illustration homepage_small.gif

The following figure shows the basic architecture of Enterprise Manager. The management repository is stored inside the database. Both the agent and the management service run on the database host. You can run the Database Control Console from any Web browser that can connect securely to the management service.
Description of cncpt305.gif follows
Description of the illustration cncpt305.gif

See Also:
Oracle Database 2 Day DBA to learn how to administer the database with Enterprise Manager

SQL*Plus

SQL*Plus is an interactive and batch query tool included in every Oracle Database installation. It has a command-line user interface that acts as the client when connecting to the database.
SQL*Plus has its own commands and environment. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform tasks such as:
  • Formatting, performing calculations on, storing, and printing from query results
  • Examining table and object definitions
  • Developing and running batch scripts
  • Administering a database
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility.
See Also:
Oracle Database 2 Day DBA and SQL*Plus User's Guide and Reference to learn more about SQL*Plus

Tools for Database Installation and Configuration

Oracle provides several tools to simplify the task of installing and configuring Oracle Database software. The tools include:
  • Oracle Universal Installer (OUI)
    OUI is a GUI utility that enables you to view, install, and deinstall Oracle Database software. Online Help is available to guide you through the installation. See Oracle Database Installation Guide to learn how to install Oracle Database software.
  • Database Upgrade Assistant (DBUA)
    DBUA interactively guides you through a database upgrade and configures the database for the new release. DBUA automates the upgrade by performing all tasks normally performed manually. DBUA makes recommendations for configuration options such as tablespaces and the online redo log. See Oracle Database 2 Day DBA to learn how to upgrade a database with DBUA.
  • Database Configuration Assistant (DBCA)
    DBCA provides a graphical interface and guided workflow for creating and configuring a database. This tool enables you to create a database from Oracle-supplied templates or create your own database and templates. See Oracle Database Administrator's Guide to learn how to create a database with DBCA.

Tools for Oracle Net Configuration and Administration

Oracle Net Services provides enterprise wide connectivity solutions in distributed, heterogeneous computing environments. Oracle Net, a component of Oracle Net Services, enables a network session from a client application to an database. You can use the following tools to configure and administer Oracle Net Services:
  • Oracle Net Manager
    This tool enables you to configure Oracle Net Services for an Oracle home on a local client or server host. You can use Oracle Net Manager to configure naming, naming methods, profiles, and listeners. You can start Oracle Net Manager using the Oracle Enterprise Manager Console or as an independent application.
  • Oracle Net Configuration Assistant
    This tools runs automatically during software installation. The Assistant enables you to configure basic network components during installation, including listener names and protocol addresses, naming methods, net service names in a tnsnames.ora file, and directory server usage.
  • Listener Control Utility
    The Listener Control utility enables you to configure listeners to receive client connections (see "The Oracle Net Listener"). You can access the utility through Enterprise Manager or as a standalone command-line application.
  • Oracle Connection Manager Control Utility
    This command-line utility enables you to administer an Oracle Connection Manager, which is a router through which a client connection request may be sent either to its next hop or directly to the database. You can use utility commands to perform basic management functions on one or more Oracle Connection Managers. Additionally, you can view and change parameter settings.
See Also:

Tools for Data Movement and Analysis

Oracle Database includes several utilities to assist in database movement and analysis. For example, you can use database utilities to:
Other tasks include performing physical data structure integrity checks on an offline database or data file with DBVERIFY, or changing the database identifier (DBID) or database name for an operational database using the DBNEWID utility.
Note:
Tools related to backup and recovery are covered in "Backup and Recovery".
See Also:
Oracle Database Utilities to learn about DBVERIFY and DBNEWID

SQL*Loader

SQL*Loader loads data from external files, called data files, into database tables. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. You can use SQL*Loader to perform tasks such as:
  • Loading data from multiple data files into multiple tables
    You store the data to be loaded in SQL*Loader data files. The SQL*Loader control file is a text file that contains DDL instructions that SQL*Loader uses to determine where to find the data, how to parse and interpret it, where to insert it, and more.
    Note:
    The SQL*Loader data files and control file are unrelated to the Oracle Database data files and control file.
  • Control various aspects of the load operation
    For example, you can selectively load data, specify the data character set (see "Character Sets"), manipulate the data with SQL functions, generate unique sequential key values in specified columns, and so on. You can also generate sophisticated error reports.
  • Use either conventional or direct path loading
    A conventional path load executes SQL INSERT statements to populate tables. In contrast, a direct path load eliminates much of the database overhead by formatting data blocks and writing them directly to the database files. Direct writes operate on blocks above the high water mark and write directly to disk, bypassing the database buffer cache. Direct reads read directly from disk into the PGA, again bypassing the buffer cache.
A typical SQL*Loader session takes as input a SQL*Loader control file and one or more data files. The output is an Oracle database, a log file, a bad file, and potentially, a discard file. Figure 18-1 illustrates the flow of a typical SQL*Loader session.
Figure 18-1 SQL*Loader Session
Description of Figure 18-1 follows
Description of "Figure 18-1 SQL*Loader Session"
See Also:
Oracle Database 2 Day DBA and Oracle Database Utilities to learn about SQL*Loader

Oracle Data Pump Export and Import

Oracle Data Pump enables high-speed movement of data and metadata from one database to another. This technology is the basis for the following Oracle Database data movement utilities:
  • Data Pump Export (Export)
    Export is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set is made up of one or more binary files that contain table data, database object metadata, and control information.
  • Data Pump Import (Import)
    Import is a utility for loading an export dump file set into a database. You can also use Import to load a destination database directly from a source database with no intervening files, which allows export and import operations to run concurrently, minimizing total elapsed time.
Oracle Data Pump is made up of the following distinct parts:
  • The command-line clients expdp and impdp
    These client make calls to the DBMS_DATAPUMP package to perform Oracle Data Pump operations (see "PL/SQL Packages").
  • The DBMS_DATAPUMP PL/SQL package, also known as the Data Pump API
    This API provides high-speed import and export functionality.
  • The DBMS_METADATA PL/SQL package, also known as the Metadata API
    This API, which stores object definitions in XML, is used by all processes that load and unload metadata.
Figure 18-2 shows how Oracle Data Pump integrates with SQL*Loader and external tables. As shown, SQL*Loader is integrated with the External Table API and the Data Pump API to load data into external tables (see "External Tables"). Clients such as Database Control and transportable tablespaces can use the Oracle Data Pump infrastructure.
Figure 18-2 Oracle Data Pump Architecture
Description of Figure 18-2 follows
Description of "Figure 18-2 Oracle Data Pump Architecture"
See Also:

Oracle LogMiner

Oracle LogMiner enables you to query redo log files through a SQL interface. Potential uses for data contained in redo log files include:
  • Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun
  • Detecting user error
  • Determining what actions you would have to take to perform fine-grained recovery at the transaction level
  • Using trend analysis to determine which tables get the most updates and inserts
  • Analyzing system behavior and auditing database use through the LogMiner comprehensive relational interface to redo log files
LogMiner is accessible through a command-line interface or through the Oracle LogMiner Viewer GUI, which is a part of Enterprise Manager.
See Also:
Oracle Database Utilities to learn more about LogMiner

ADR Command Interpreter (ADRCI)

ADRCI is a command-line utility that enables you to investigate problems, view health check reports, and package and upload first-failure diagnostic data to Oracle Support. You can also use the utility to view the names of the trace files in the Automatic Diagnostic Repository (ADR) (ADR) and to view the alert log. ADRCI has a rich command set that you can use interactively or in scripts.
See Also:

Topics for Database Administrators

Chapter 17 describes topics important for both developers and DBAs. This section covers topics that are most essential to DBAs and that have not been discussed elsewhere in the manual.
This section contains the following topics:

Backup and Recovery

Backup and recovery is the set of concepts, procedures, and strategies involved in protecting the database against data loss caused by media failure or users errors. In general, the purpose of a backup and recovery strategy is to protect the database against data loss and reconstruct lost data.
A backup is a copy of data. A backup can include crucial parts of the database such as data files, the server parameter file, and control file. A sample backup and recovery scenario is a failed disk drive that causes the loss of a data file. If a backup of the lost file exists, then you can restore and recover it. Media recovery refers to the operations involved in restoring data to its state before the loss occurred.
See Also:
Oracle Database 2 Day DBA and Oracle Database Backup and Recovery User's Guide for backup and recovery concepts and tasks

Backup and Recovery Techniques

You can use the following means to back up and recover an Oracle database:
  • Recovery Manager (RMAN)
    RMAN is an Oracle Database utility that integrates with an Oracle database to perform backup and recovery activities, including maintaining a repository of historical backup metadata in the control file of every database that it backs up. RMAN can also maintain a centralized backup repository called a recovery catalog in a different database. RMAN is an Oracle Database feature and does not require separate installation.
    RMAN is integrated with Oracle Secure Backup, which provides reliable, centralized tape backup management, protecting file system data and Oracle Database files. The Oracle Secure Backup SBT interface enables you to use RMAN to back up and restore database files to and from tape and internet-based Web Services such as Amazon S3. Oracle Secure Backup supports almost every tape drive and tape library in SAN and SCSI environments.
    RMAN and Oracle Secure Backup are accessible both from the command line and from Enterprise Manager.
  • User-Managed techniques
    As an alternative to RMAN, you can use operating system commands such as the Linux dd for backing up and restoring files and the SQL*Plus RECOVER command for media recovery. User-managed backup and recovery is fully supported by Oracle, although RMAN is recommended because it is integrated with Oracle Database and simplifies administration.
Figure 18-3 shows basic RMAN architecture. The RMAN client, accessible through Enterprise Manager, uses server sessions on a target database to back up data to disk or tape. RMAN can update an external recovery catalog with backup metadata.
Figure 18-3 RMAN Architecture
Description of Figure 18-3 follows
Description of "Figure 18-3 RMAN Architecture"
Whichever backup and recovery technique you use, Oracle recommends that you configure a fast recovery area. This database-managed directory, file system, or Oracle ASM disk group centralizes backup and recovery files, including active control files, online and archived redo log files, and backups. Oracle Database recovery components interact with the fast recovery area to ensure database recoverability.
See Also:

Database Backups

Database backups can be either physical or logical. Physical backups, which are the primary concern in a backup and recovery strategy, are copies of physical database files. You can make physical backups with RMAN or operating system utilities.
In contrast, logical backups contain logical data such as tables and stored procedures. You can extract logical data with an Oracle Database utility such as Data Pump Export and store it in a binary file. Logical backups can supplement physical backups.
Physical backups have large granularity and limited transportability, but are very fast. Logical backups have fine granularity and complete transportability, but are slower than physical backups.
See Also:
Oracle Database Backup and Recovery User's Guide to learn about physical and logical backups
Whole and Partial Database Backups
A whole database backup is a backup of every data file in the database, plus the control file. Whole database backups are the most common type of backup.
A partial database backup includes a subset of the database: individual tablespaces or data files. A tablespace backup is a backup of all the data files in a tablespace or in multiple tablespaces. Tablespace backups, whether consistent or inconsistent, are valid only if the database is operating in ARCHIVELOG mode because redo is required to make the restored tablespace consistent with the rest of the database.
Consistent and Inconsistent Backups
A whole database backup is either consistent or inconsistent. In a consistent backup, all read/write data files and control files have the same checkpoint SCN, guaranteeing that these files contain all changes up to this SCN. This type of backup does not require recovery after it is restored.
A consistent backup of the database is only possible after a consistent shutdown (see "Shutdown Modes") and is the only valid backup option for a database operating in NOARCHIVELOG mode. Other backup options require media recovery for consistency, which is not possible without applying archived redo log files.
Note:
If you restore a consistent whole database backup without applying redo, then you lose all transactions made after the backup.
In an inconsistent backup, read/write data files and control files are not guaranteed to have the same checkpoint SCN, so changes can be missing. All online backups are necessarily inconsistent because data files can be modified while backups occur.
Inconsistent backups offer superior availability because you do not have to shut down the database to make backups that fully protect the database. If the database runs in ARCHIVELOG mode, and if you back up the archived redo logs and data files, then inconsistent backups can be the foundation for a sound backup and recovery strategy.
See Also:
Oracle Database Backup and Recovery User's Guide to learn more about inconsistent backups
Backup Sets and Image Copies
The RMAN BACKUP command generates either image copies or backup sets. An image copy is a bit-for-bit, on-disk duplicate of a data file, control file, or archived redo log file. You can create image copies of physical files with operating system utilities or RMAN and use either tool to restore them.
Note:
Unlike operating system copies, RMAN validates the blocks in the file and records the image copy in the RMAN repository.
RMAN can also create backups in a proprietary format called a backup set. A backup set contains the data from one or more data files, archived redo log files, or control files or server parameter file. The smallest unit of a backup set is a binary file called a backup piece. Backup sets are the only form in which RMAN can write backups to sequential devices such as tape drives.
Backup sets enable tape devices to stream continuously. For example, RMAN can mingle blocks from slow, medium, and fast disks into one backup set so that the tape device has a constant input of blocks. Image copies are useful for disk because you can update them incrementally, and also recover them in place.
See Also:
Oracle Database Backup and Recovery User's Guide to learn more about backup sets and image copies
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)