White Paper: Oracle Database Administration

White Paper: Oracle Database Administration

TABLE OF CONTENTS OVERVIEW
 BR*TOOLS FOR ORACLE DBA ,
 SPACE MANAGEMENT
,Analyses,
Table-space Management.,
Reorganization,
Segment Management,
Functions,
DATABASE SYSTEM CHECK
,Functions.,
UPDATE STATISTICS ,
 Functions.,
BACKING UP,
RESTORING,
AND RECOVERING THE DATA.,
Functions.
,COMPUTING CENTER MANAGEMENT SYSTEM ,
ADDITIONAL INFORMATION
,Library ,
Notes

OVERVIEW
 The database server plays a key role in the SAP server technology. The amount of work involved in managing the Oracle database largely depends on the size and the workload. SAP supplies several database administration tools, BR*Tools, in the standard SAP system package, helping you to manage your Oracle database more easily. You can manage your Oracle database using the following BR*Tools for database administration (DBA):  BRSPACE Database startup and shutdown, alter database parameters, recreate database, tablespace management, space monitoring and analyses, alter and move data files, table reorganization, index rebuild, export and import, manage database statistics, manage flashback database, manage online redo log, manage data encryption  BRCONNECT Update statistics, database system check, adapt next extents, clean up old logs and traces, and other additional functions  BRBACKUP, BRARCHIVE Backup and verification of database and offline redo log files  BRRECOVER Database recovery and restore, as well as disaster recovery

FEATURES OF BR*TOOLS FOR ORACLE DBA

BR*Tools feature several user interfaces, such as a command line and an ASCII user interface with menus. You can also execute some functions using the graphical user interface BRGUI and the Computing Center Management System (CCMS). These tools make database administration considerably easier. BR*Tools:  Guide you through all tasks, proposing default values and issuing warnings  Log all administrative operations in file system and database logs, detailed logs for individual DBA tasks, and the main log for actions affecting database structure  Follow SAP norms and recommendations (for example, for tablespace extension)  Perform security checks before starting an operation  Monitor operations and evaluate the various error messages  Execute the entire process required (for example, BRRECOVER automatically performs all steps for recovering a database after a media error)  Let database administrators without in-depth Oracle knowledge perform complex operations such as reorganization  Provide an interface to external backup tools using the standard BACKINT interface  Identify dependencies using the logs (for example, if the required backup does not exist, BRRECOVER does not perform a recovery)  Are integrated into the Computing Center Management System (CCMS)  Conform to SAP’s ABAP Dictionary  Support Oracle databases on raw devices  Support Oracle Automatic Storage Management (ASM)  Support Oracle Standby configurations  Support Oracle Recovery Manager (RMAN)  Support Oracle Real Application Cluster (RAC)  Support Oracle database appliances like Oracle Exadata Database Machine

SPACE MANAGEMENT 
The Oracle database is installed in file systems, on raw devices, or in Oracle ASM with the SAP system. Database space management with BRSPACE provides essential functions for:  Organization of the database files (number, assignment to tablespaces, distribution on disks, file system, raw device, links to raw devices, ASM disk groups)  Monitoring logical objects (tables or indexes) in individual tablespaces and checking free space in tablespaces  Monitoring storage space allocation of individual logical objects (extents or blocks), extent allocation to an object, the fill level of segments, and so on  If storage problems occur, you can use BRSPACE to solve them by extending a tablespace (that is, increasing the physical storage space allocated to the tablespace) or by performing a reorganization (that is, redistributing existing free space and allocated space). Analyses BRSPACE provides several options that help to identify and analyze storage problems in advance. Some options let you modify storage parameters automatically to prevent storage problems arising. In this way, for example, you can considerably reduce the need to reorganize the database. Tablespace Management When BRSPACE creates new data files, it follows SAP naming conventions and take free disk space into account. BRSPACE determines and displays information about free space on disk volumes and raw disks. Therefore, you can easily and quickly extend tablespaces. Reorganization During a reorganization, the database system can be used for production operation or for all other online transaction processing (OLTP) applications. However, the reorganization puts additional load on the system and temporarily doubles the space required for reorganized tables. This is why we recommend you to avoid reorganization if possible, or to speed it up, if it becomes necessary. The reasons why you might need to reorganize the database are:  Internal fragmentation: fragmentation of table or index data within a block or chaining of data over several blocks  External fragmentation: fragmentation of the extents of a logical object and of the free space within a tablespace, maximum number of extents for an object reached  You want to transform dictionary managed into locally managed tablespaces.  You want to move certain large and heavily used tables into separate tablespaces.  You want to convert BasicFile LOBs to SecureFile LOBs  You want to compress tables, indexes, SecureFile LOBs  There are fragmented tables or indexes in dictionary managed tablespaces that you are still using. You can identify these by using the database system check. The aim of reorganization is to speed up data access and to use available space more efficiently. The Oracle package DBMS_REDEFINITION is used for the online reorganization process that is executed by BRSPACE as a series of steps. The SQL command ALTER TABLE MOVE is used for offline reorganization. The tables are locked during this procedure. You can modify certain table or index attributes during the reorganization procedure.

Segment Management
 You can use BRSPACE to change certain attributes of tables and indexes. You can use BRCONNECT to adapt storage attributes of database objects such as tables and indexes. Functions  Displaying disk usage  Displaying fragmentation (free space and allocated space) and degree to which tablespaces are filled  Computing and displaying database statistics for tables and indexes (individually or for each tablespace)  Extending tablespaces  Creating and dropping tablespaces  Renaming tablespaces and their data files  Moving and renaming data files  Reorganization (single table and its index, list of tables and indexes, or tablespace), including changes to storage parameters  Moving tables and indexes to other tablespaces during reorganization or rebuild  Rebuilding online indexes  Shrinking online tables and indexes  Using Oracle export/import for data transfer

DATABASE SYSTEM CHECK
 We recommend you to regularly run the database system check in order to spot problems early. The database system check with BRCONNECT is also available from CCMS. We recommend you to perform the system check daily, using the automation in the DBA Planning Calendar in transaction DBACOCKPIT to execute brconnect -f check. You then display the check results in the CCMS alert monitor and decide whether to implement any recommendations. Functions  Checking space (that is, free space and fragmentation)  Checking the results of DBA operations with BR*Tools  Monitoring Oracle alert messages  Checking Oracle profile parameters  Checking physical consistency (that is, availability of data files, control files, and redo log files)  Checking missing optimizer statistics  Checking specific problem situations based upon SAP’s experience in managing databases  Controlling all system checks by one single customizing transaction (for example, thresholds, repeatability periods, error descriptions and corrective actions, Oracle error message checks, and Oracle profile parameters can be added)

UPDATE STATISTICS 
The Oracle Cost-Based Optimizer (CBO) is activated as standard and is supported by BRCONNECT. The CBO optimizes data access by gathering database statistics, which it obtains by analyzing table size and data distribution. You can use CCMS to automatically set up the statistics control table, DBSTATC, as well as to check and update the CBO statistics. The result is improved database performance. Functions  Displaying information about existing statistics  Detecting tables that need updated statistics  Creating missing statistics for tables and indexes  Displaying alert messages for missing statistics for tables and indexes (see “Database System Check” above)  Controlling the update procedure by editing the control table DBSTATC  Managing table statistics (version management, export/import)

BACKING UP, RESTORING, AND RECOVERING THE DATA
 As shown in the graphic below, BRBACKUP backs up the database objects (data files, control files, and online redo log files) and BRARCHIVE backs up the offline redo log files. You can perform a restore and recover with the SAP tools BRRESTORE and BRRECOVER. BRRESTORE also allows parallel processing (like BRBACKUP). You can call all these tools from the BRGUI or BRTOOLS menu. Control files Data files Online redo log files Offline redo log files Backup Media Restore and Recovery Detail and summary log Detail and summary log BRBACKUP BRRESTORE BRARCHIVE BRRECOVER Detail and summary log Data files Online redo log files Offline redo log files Control files Oracle Database BRBACKUP, BRARCHIVE, and BRRESTORE provide an open interface, BACKINT, enabling you to use non-SAP backup tools. The official name of this interface is BC-BRI, and it is certified by SAP. The BRRECOVER menu options for restore and recovery are specially designed for ease of use. BRRECOVER provides support in the following important situations:  Media error in several files, for example, because of a disk failure. The database is fully recovered up to the time of the media error.  Restoring the entire database or some tablespaces to perform a point-in-time recovery or to reset the database to a previous state  Disaster recovery BRRECOVER evaluates the backup logs and the summary log to decide whether the chosen recovery can be performed using the selected backups. For example, it determines whether any actions have occurred between the time of the backup and the end time of the selected recovery (point in time) that would prevent a successful recovery. If BRRECOVER cannot perform a recovery, it rejects the backup selected or the recovery procedure specified.

BRRECOVER can only recover data automatically if BRBACKUP and BRARCHIVE or the BACKINT interface were used for the backup. In this respect, the SAP tools BRBACKUP, BRARCHIVE, BRRESTORE, and BRRECOVER function as an integrated solution.

Functions 

 BRBACKUP: database backup (parallel, restartable, database status is monitored)  BRARCHIVE: archiving of offline redo log files  BRRESTORE: restore of database and offline redo log files (parallel, restartable)  BRRECOVER: complete database recovery, point-in-time recovery, and disaster recovery  Backup media management  Validity check of all objects in the database (online redo log files, control files, data files)  Automatic restore of all required objects (tape request)  Support in various situations such as disk failure, point-in-time recovery, resetting the database to a previous state  Support for special database configurations such as standby database.  Backup from disk backups to tape (two-step backup)  Split-mirror disk backup  Standby database backup  Completion of partial backups  Robot control interface for automatic tape mounting  BACKINT interface to commercial backup tools  Support of Oracle Recovery Manager (RMAN) for incremental backups  Support for Oracle Real Application Cluster (RAC)  Support for Oracle Automatic Storage Management (ASM)  Support for Oracle appliances such as the Oracle Exadata Database Machine