Oracle® Database 11g Administration Guide

Preface:
This document describes how to create, configure, and administer an Oracle database.

What's New in Oracle Database Administrator's Guide?

Deferred segment creation for partitioned objects

• Deferred segment creation now applies to partitioned objects as well as nonpartitioned objects.
• You can drop segments from empty tables that were migrated from previous releases, using the DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS procedure.
• The DROP ALL STORAGE clause of the TRUNCATE statement enables you to deallocate segments for a table.
• You can materialize segments for tables, partitions, and dependent objects for which segment creation was deferred, using the DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS procedure.

New default first extent size for partitioned tables improves performance
The default size of the first extent of any new segment for a partitioned table is now 8 MB instead of 64 KB. This helps improve performance of inserts and queries on partitioned tables. Although partitioned tables will start with a larger initial size, once sufficient data is inserted, the space consumption will be the same as in previous releases. You can override this default by setting the INITIAL size in the storage clause for the table. This new default only applies to table partitions and LOB partitions.

New initialization parameter to improve the performance of NOLOGGING direct-path inserts
You can now significantly improve the performance of unrecoverable direct path inserts by disabling the periodic update of the control files. You do so by setting the new initialization parameter DB_UNRECOVERABLE_SCN_TRACKING to FALSE. However, if you perform an unrecoverable direct path insert with these control file updates disabled, you will no longer be able to accurately query the database to determine if any datafiles are currently unrecoverable.

Improved e-mail notification capabilities in Oracle Scheduler
Oracle Scheduler e-mail notification preferences now include options to specify authentication and SSL or TLS protocols for the designated SMTP Server.

Edition attributes of database services
You can set the edition attribute of a database service when you create the service, or you can modify an existing database service to set its edition attribute.

Enhancements to Oracle Database Resource Manager

• You can optimize the performance of parallel statements by limiting the parallel statement activity per consumer group, prioritizing parallel statements in the parallel statement queue, and limiting the wait time for parallel statement in the parallel statement queue.
  Parallel statement queuing enables efficient management of parallel statements when the database does not have sufficient resources to run a statement at the specified degree of parallelism. A submitted parallel statement is added to the parallel statement queue if the resources required to run the statement exceed the parallel statement activity limit specified for the database or the consumer group to which the parallel statement is assigned. Parallel statement queuing can also manage multiple parallel statement workloads according to the priorities and resource allocations specified by a resource plan.
  
• You can track CPU utilization using per-minute metrics for each resource plan. You can also monitor resource utilization for consumer groups, even if Resource Manager is not enabled, thus allowing you to view the potential effect of Resource Manager.

Oracle Database 11g Release 2 (11.2.0.1) New Features in the Administrator's Guide

• Oracle Restart improves database availability by automatically restarting the database after a failure.
  If you configure Oracle Restart, then the database, the listener, the Oracle Automatic Storage Management instance, and other Oracle components can be automatically restarted after a hardware or software failure or after a restart of the database host computer.
• Edition-based redefinition enables application developers and DBAs to upgrade an application with little or no application down time.
  A new database construct called an edition provides a privacy mechanism for installing new code and for making data changes so that the running production application does not see the changes. When all the required changes have been made in private, they can be made available to users. In support of edition-based redefinition, a new kind of view called an editioning view and a new kind of trigger called a crossedition trigger are introduced.
• Database Smart Flash Cache
  Database Smart Flash Cache is an optional memory component that you can add if your database is running on Solaris or Oracle Linux. It is an extension of the SGA-resident buffer cache, providing a level 2 cache for database blocks. It can improve response time and overall throughput.
• The Automatic Segment Advisor can now return a recommendation to use OLTP compression for a table.
• Deferred segment creation
  When creating a nonpartitioned heap-organized table in a locally managed tablespace, table segment creation is deferred until the first row is inserted.
• Oracle Database File System
  The Oracle Database File System creates a standard file system interface on top of files and directories that are stored in database tables.
• Oracle Scheduler enhancements
  
  • Remote database jobs—You can now create a job that runs stored procedures and anonymous PL/SQL blocks on another database instance on the same host or on a remote host. The target database can be any release of Oracle Database.
  • Multiple-destination jobs—You can now run a job on multiple locations, and control and monitor all instances of the job from one central database. You do so by specifying multiple destinations when you create the job. A destination can be the local host or local database; a remote host (for remote external jobs); or a remote database (for remote database jobs).
  • File watchers—A new Scheduler object called a file watcher simplifies the task of configuring the Scheduler to start a job upon the arrival of a file on the local or a remote system.
  • E-mail notifications—You can configure the Scheduler to automatically send an e-mail notification to one or more recipients when a specified job state event occurs. You can now receive an e-mail when a job completes, if it fails or is disabled, if it exceeds its allotted run time, and so on.
• Database Resource Manager Enhancements
  
  • Instance caging
    Oracle Database now provides a method for managing CPU allocations on a multi-CPU server running multiple database instances. Instance caging limits the maximum number of CPUs that any one database instance can use. If an instance then becomes CPU-bound, the Resource Manager begins allocating CPU based on the current resource plan. Thus, instance caging and the Resource Manager work together to support desired levels of service across multiple instances.
  • New MAX_UTILIZATION_LIMIT attribute of resource plan directives enables you to impose an absolute upper limit on CPU utilization for a resource consumer group. This absolute limit overrides any automatic redistribution of CPU within a plan.
  • New ORACLE_FUNCTION consumer group mapping rule type, and new predefined mapping rules for Data Pump and RMAN.
    Sessions performing a data load with Data Pump or performing backup or copy operations with RMAN are now automatically mapped to predefined consumer groups.
  • New sample resource plans and resource consumer groups to support data warehousing operations with Oracle Exadata
• New SQL command syntax for specifying table compression for direct load operations only or for all (OLTP) operations.
• The Flash Recovery Area is renamed to Fast Recovery Area.
• External tables can be preprocessed by user-supplied preprocessor programs.
  By using a preprocessing program, users can use data from a file that is not in a format supported by the access driver. For example, a user may want to access data stored in a compressed format. Specifying a decompression program for the ORACLE_LOADER access driver allows the data to be decompressed as the access driver processes the data.
• Archive logging now supports up to 30 standby databases.
• IP version 6 is now supported.
  Oracle Database components and utilities now support Internet Protocol version 6 (IPv6) addresses, which are 128 bits in length. You can now specify an IPv6 address with the easy connect method in SQL*Plus.
• Redo logs can now be stored on disk drives with a sector size of 4K bytes without performance degradation.
  A new redo log file block size of 4K bytes enables online redo logs to be stored on newer high-capacity disks with a 4K byte sectors size without incurring performance degradation. The new block size ensures that log file writes are sector-aligned.
• The Enterprise Manager Support Workbench, a component of the fault diagnosability infrastructure, now supports investigating, reporting, and resolving critical errors in Oracle Automatic Storage Management instances.

Basic Database Administration:
1 Getting Started with Database Administration:

Types of Oracle Database Users

The types of users and their roles and responsibilities depend on the database site. A small site can have one database administrator who administers the database for application developers and users. A very large site can find it necessary to divide the duties of a database administrator among several people and among several areas of specialization.

Database Administrators

Each database requires at least one database administrator (DBA). An Oracle Database system can be large and can have many users. Therefore, database administration is sometimes not a one-person job, but a job for a group of DBAs who share responsibility.
A database administrator's responsibilities can include the following tasks:

• Installing and upgrading the Oracle Database server and application tools
• Allocating system storage and planning future storage requirements for the database system
• Creating primary database storage structures (tablespaces) after application developers have designed an application
• Creating primary objects (tables, views, indexes) once application developers have designed an application
• Modifying the database structure, as necessary, from information given by application developers
• Enrolling users and maintaining system security
• Ensuring compliance with Oracle license agreements
• Controlling and monitoring user access to the database
• Monitoring and optimizing the performance of the database
• Planning for backup and recovery of database information
• Maintaining archived data on tape
• Backing up and restoring the database
• Contacting Oracle for technical support

Security Officers

In some cases, a site assigns one or more security officers to a database. A security officer enrolls users, controls and monitors user access to the database, and maintains system security. As a DBA, you might not be responsible for these duties if your site has a separate security officer.

Network Administrators

Some sites have one or more network administrators. A network administrator, for example, administers Oracle networking products, such as Oracle Net Services.

Application Developers

Application developers design and implement database applications. Their responsibilities include the following tasks:

• Designing and developing the database application
• Designing the database structure for an application
• Estimating storage requirements for an application
• Specifying modifications of the database structure for an application
• Relaying this information to a database administrator
• Tuning the application during development
• Establishing security measures for an application during development

Application developers can perform some of these tasks in collaboration with DBAs.

Application Administrators

An Oracle Database site can assign one or more application administrators to administer a particular application. Each application can have its own administrator.

Database Users

Database users interact with the database through applications or utilities. A typical user's responsibilities include the following tasks:

• Entering, modifying, and deleting data, where permitted
• Generating reports from the data

Tasks of a Database Administrator

The following tasks present a prioritized approach for designing, implementing, and maintaining an Oracle Database:

Task 1: Evaluate the Database Server Hardware

Evaluate how Oracle Database and its applications can best use the available computer resources. This evaluation should reveal the following information:

• How many disk drives are available to the Oracle products
• How many, if any, dedicated tape drives are available to Oracle products
• How much memory is available to the instances of Oracle Database you will run (see your system configuration documentation)

Task 2: Install the Oracle Database Software

As the database administrator, you install the Oracle Database server software and any front-end tools and database applications that access the database. In some distributed processing installations, the database is controlled by a central computer (database server) and the database tools and applications are executed on remote computers (clients). In this case, you must also install the Oracle Net components necessary to connect the remote systems to the computer that executes Oracle Database.
For more information on what software to install.

For specific requirements and instructions for installation, see the following documentation:

• The Oracle documentation specific to your operating system
• The installation guides for your front-end tools and Oracle Net drivers

Task 3: Plan the Database

As the database administrator, you must plan:

• The logical storage structure of the database
• The overall database design
• A backup strategy for the database

It is important to plan how the logical storage structure of the database will affect system performance and various database management operations. For example, before creating any tablespaces for your database, you should know how many datafiles will comprise the tablespace, what type of information will be stored in each tablespace, and on which disk drives the datafiles will be physically stored. When planning the overall logical storage of the database structure, take into account the effects that this structure will have when the database is actually created and running. Consider how the logical storage structure of the database will affect:

• The performance of the computer running Oracle Database
• The performance of the database during data access operations
• The efficiency of backup and recovery procedures for the database

Plan the relational design of the database objects and the storage characteristics for each of these objects. By planning the relationship between each object and its physical storage before creating it, you can directly affect the performance of the database as a unit. Be sure to plan for the growth of the database.
In distributed database environments, this planning stage is extremely important. The physical location of frequently accessed data dramatically affects application performance.
During the planning stage, develop a backup strategy for the database. You can alter the logical storage structure or design of the database to improve backup efficiency.
It is beyond the scope of this book to discuss relational and distributed database design. If you are not familiar with such design issues, see accepted industry-standard documentation.
Part II, "Oracle Database Structure and Storage", and Part III, "Schema Objects", provide specific information on creating logical storage structures, objects, and integrity constraints for your database.

Task 4: Create and Open the Database

After you complete the database design, you can create the database and open it for normal use. You can create a database at installation time, using the Database Configuration Assistant, or you can supply your own scripts for creating a database.

Task 5: Back Up the Database

After you create the database structure, perform the backup strategy you planned for the database. Create any additional redo log files, take the first full database backup (online or offline), and schedule future database backups at regular intervals.

Task 6: Enroll System Users

After you back up the database structure, you can enroll the users of the database in accordance with your Oracle license agreement, and grant appropriate privileges and roles to these users.

Task 7: Implement the Database Design

After you create and start the database, and enroll the system users, you can implement the planned logical structure database by creating all necessary tablespaces. When you have finished creating tablespaces, you can create the database objects.

Task 8: Back Up the Fully Functional Database

When the database is fully implemented, again back up the database. In addition to regularly scheduled backups, you should always back up your database immediately after implementing changes to the database structure.

Task 9: Tune Database Performance

Optimizing the performance of the database is one of your ongoing responsibilities as a DBA. Oracle Database provides a database resource management feature that helps you to control the allocation of resources among various user groups.

Task 10: Download and Install Patches

After installation and on a regular basis, download and install patches. Patches are available as single interim patches and as patchsets (or patch releases). Interim patches address individual software bugs and may or may not be needed at your installation. Patch releases are collections of bug fixes that are applicable for all customers. Patch releases have release numbers. For example, if you installed Oracle Database 11.2.0.1, the first patch release will have a release number of 11.2.0.2.

Task 11: Roll Out to Additional Hosts

After you have an Oracle Database installation properly configured, tuned, patched, and tested, you may want to roll that exact installation out to other hosts. Reasons to do this include the following:

• You have multiple production database systems.
• You want to create development and test systems that are identical to your production system.

Instead of installing, tuning, and patching on each additional host, you can clone your tested Oracle Database installation to other hosts, saving time and avoiding inconsistencies. There are two types of cloning available to you:

• Cloning an Oracle home—Just the configured and patched binaries from the Oracle home directory and subdirectories are copied to the destination host and "fixed" to match the new environment. You can then start an instance with this cloned home and create a database.
  You can use the Enterprise Manager Clone Oracle Home tool to clone an Oracle home to one or more destination hosts. You can also manually clone an Oracle home using a set of provided scripts and Oracle Universal Installer.
• Cloning a database—The tuned database, including database files, initialization parameters, and so on, are cloned to an existing Oracle home (possibly a cloned home).
  You can use the Enterprise Manager Clone Database tool to clone an Oracle database instance to an existing Oracle home.