关于 Database Administrators

1.Duties of Database Administrator

• 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 data for, and reporting to Oracle Support Services any critical database errors

• Evaluating and testing new database features

2.Tools for Database Administrators

• OEM(Oracle Enterprise Manager)

Oracle Enterprise Manager

Oracle Enterprise Manager Cloud Control

- Oracle Management Service (OMS)

- Oracle Management Agents

- Oracle Management Manager Database Express 12c

      Database Home page

      Rea-Time SQL Monitoring

      ASH Analytics

• SQL*Plus

- Formatting , performing calculations on, and printing from query results.

- Examining table running batch scripts.

- Administering a database.

• Tools for Database Installation and Configuration

- Oracle Universal Installer (OUI): view , install , and uninstall Oracle Database software.

- Database Upgrade Assistant (DBUA): interactively guide you through a database upgrade and configures the database for the new release.DBUA  automates the upgrade by performing all tasks normally performed manually.

Oracle Database 2 Day DBA

- Database Configuration Assistant(DBCA): provides a graphical interface and guided workflow for creating and configuring a database.

Oracle Database Administrator’s Guide

• Tool for Oracle Net Configuration and Adminstration

Oracle Net Manager

Oracle Net Configuration Assistant

Listener Control Utility

Oracle Connection Manager Control Utility

• Tools for Data Movement and Analysis

- SQL*Loader 

Load data into Oracle Database tables from operating system files.

Move data and medata from one databases to another database

Query redo log files through a SQL interface

Manage Oracle Database data

- Oracle Data Pump Export and Import

• Data Pump Export (Export)
• Data Pump Import(Import)

Oracle Data Pump is made up of the following distinct parts:

• The command-line clients expdp and impdp

  These clients make calls to the DBMS_DATAPUMP package to perform Oracle Data Pump operations.

• 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

  All processes that load and unload metadata use this API, which stores object definitions in XML.

- Oracle LogMiner

 Oracle LogMiner enables you to query redo log files through a SQL interface.

• 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

- 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 data to Oracle Support.

3.Topics for Database Administrators

• Backup and Recovery

A backup is a copy of data. A backup can include data files, the server para, and control file.

     Backup and Recovery Techniques

     Recovery Manager Architecture

• Database Backups

1. Whole and Partial Database Backups
2. Consistent and Inconsistent Backups
3. Backup Sets and Image Copies

• Data Repairs

1. Oracle Flashback Technology
2. Data Recovery Advisor
3. Block Media Recovery
4. Data File Recovery

• Zero Data Loss Recovery Appliance

Benefits of Recovery Appliance

The primary benefits are as follows:

• Elimination of data loss
• Minimal backup overhead
• Improved end-to-end data protection visibility
• Cloud-Scale protection

Recovery Appliance Environment

 Memory Management

Automatic Memory Management

 

Shared Memory Management of the SGA

• Automatic shared memory management
• Manual shared memory management

Memory Management of the Instance PGA

Automatic PGA memory management

Manual PGA memory management

Summary of Memory Management Methods

Resource Management and Task Scheduling

Database Resource Manager

The DBMS_RESOURCE_MANAGER PL/SQL package solves many resource allocation problems that an operating system does not manage well, including:

• Excessive overhead

• Inefficient scheduling

• Inappropriate allocation of resources

• Inability to manage database-specific resources

 

Oracle Scheduler

Oracle Scheduler (the Scheduler) enables database administrators and application developers to control when and where various tasks take place in the database environment.

The Scheduler provides complex enterprise scheduling functionality, which you can use to:

• Schedule job execution based on time or events

• Schedule job processing in a way that models your business requirements

• Manage and monitor jobs

• Execute and manage jobs in a clustered environment

 

Performance and Tuning

As a DBA, you are responsible for the performance of your Oracle database. Typically, performance problems result from unacceptable response time, which is the time to complete a specified workload, or throughput, which is the amount of work that can be completed in a specified time.

Typical problems include:

• CPU bottlenecks

• Undersized memory structures

• I/O capacity issues

• Inefficient or high-load SQL statements

• Unexpected performance regression after tuning SQL statements

• Concurrency and contention issues

• Database configuration issues

 A specific and measurable goal might be "Reduce the response time of the specified SELECT statement to under 5 seconds." Whether this goal is achievable depends on factors that may or may not be under the control of the DBA. In general, tuning is the effort to achieve specific, measurable, and achievable tuning goals by using database resources in the most efficient way possible.

The Oracle performance method is based on identifying and eliminating bottlenecks in the database, and developing efficient SQL statements. Applying the Oracle performance method involves the following tasks:

• Performing pre-tuning preparations

• Tuning the database proactively on a regular basis

• Tuning the database reactively when users report performance problems

• Identifying, tuning, and optimizing high-load SQL statements

Database Self-Monitoring

Self-monitoring take place as the database performs its regular operation, ensuring that the database is aware of problems as they arise. Oracle Database can send a server-generated alert to notify you of an impending problem.

Automatic Workload Repository (AWR)

Automatic Workload Repository (AWR) is a repository of historical performance data that includes cumulative statistics for the system, sessions, individual SQL statements, segments, and services.

 Automatic Database Monitor (ADDM)

Using statistics captured in AWR (Automatic Workload Repository), ADDM automatically and proactively diagnoses database performance and determines how identified problems can be resolved. You can also run ADDM manually.

Automatic Database Diagnostic Monitor (ADDM)
 is a self- advisor built into Oracle Database.

Active Session History (ASH)

Active Session History (ASH) samples active database sessions each second, writing the data to memory and persistent storage. ASH is an integral part of the database self-management framework and is useful for diagnosing performance problems.

You can use Enterprise Manager or SQL scripts to generate ASH reports that gather session statistics gathered over a specified duration. You can use ASH reports for:

• Analysis of short-lived performance problems not identified by Automatic Database Diagnostic Monitor (ADDM)

• Scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL ID

Application and SQL Tuning

EXPLAIN PLAN Statement

Tools such as the EXPLAIN PLAN statement enable you to view execution plans chosen by the optimizer.

EXPLAIN PLAN shows the query plan for the specified SQL query if it were executed now in the current session. Other tools are Oracle Enterprise Manager and the SQL*Plus AUTOTRACE command.

Optimizer Statistics Advisor

Optimizer Statistics Advisor is diagnostic software that analyzes how you are currently gathering statistics, the effectiveness of existing statistics gathering jobs, and the quality of the gathered statistics. Optimizer Statistics Advisor uses the same advisor framework as Automatic Database Diagnostic Monitor (ADDM), SQL Performance Analyzer, and other advisors.

Optimizer Statistics Advisor provides the following advantages over the traditional approach, which relies on best practices:

• Provides easy-to-understand reports

• Supplies scripts to implement necessary fixes without requiring changes to application code

• Runs a predefined task named AUTO_STATS_ADVISOR_TASK once per day in the maintenance window

• Provides an API in the DBMS_STATS package that enables you to create and run tasks manually, store findings and recommendations in data dictionary views, generate reports for the tasks, and implement corrections when necessary

• Integrates with existing tools such as Oracle Enterprise Manager (Enterprise Manager)

SQL Tuning Advisor

The interface for automatic SQL tuning is SQL Tuning Advisor, which runs automatically during system maintenance windows as a maintenance task.

During each automatic run, the advisor selects high-load SQL queries in the database and generates recommendations for tuning these queries.

SQL Tuning Advisor recommendations fall into the following categories:

• Statistics analysis

• SQL profiling

• Access path analysis

• SQL structure analysis

SQL Access Advisor

Use SQL Access Advisor to assist you with analyzing SQL queries, and determining ways of optimizing schema objects or tuning queries.

SQL Access Advisor offers advice on how to optimize data access paths. Specifically, it recommends how database performance can be improved through partitioning, materialized views, indexes, and materialized view logs.

Schema objects such as partitions and indexes are essential for optimizing complex, data-intensive queries. However, creation and maintenance of these objects can be time-consuming, and space requirements can be significant. SQL Access Advisor helps meet performance goals by recommending data structures for a specified workload.

You can run SQL Access Advisor from Enterprise Manager using a wizard or by using the DBMS_ADVISOR package. DBMS_ADVISOR consists of a collection of analysis and advisory functions and procedures callable from any PL/SQL program.

 

SQL Plan Management

Manage SQL execution plans by using SQL plan management to carry out only tested and verified plans.

SQL plan management
 is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only verified plans. This mechanism can build a SQL plan baseline
, which is a set of one or more accepted plans for a repeatable SQL statement. The effect of a baseline is that the optimizer limits its choice to a verified plan in the baselin