A brief overview of Oracle administration

network1

ABSTRACT

The DBA is the administrator who plans, designs, implements, tests, operates, and maintains databases for an organization. The DBA wears many hats: there are numerous types of DBAs that contribute to the overall database functionality. This paper will examine the most common types of database administrators and the duties of each. Additionally, we will examine the database monitoring tools and Optimal Flexible Architecture, and how the DBA can administer security, performance tuning, and backup and recovery of the database.

Different database administrator types

The production database administrator works with other information systems administrators and specializes in the creation and management of database tables, backup and recovery, security, and performance of databases. Every day database administrative tasks are the duties of this position; and duties include monitoring database and ensuring availability, CPU, memory, utilization, and system I/O among numerous other things. Security is a large part of the DBA repertoire of which user accounts, roles, and profiles are a large part. Table space creation and control file backup are an important task that the production database administrator works with to ensure quick recovery from any corruption or data loss within the database.

Interaction with the analysts (programmers) would involve backup planning, as many backups incorrectly timed can interfere with database operation. This is because locked files can become corrupted if the backup runs at a time in which the file is written to. Files that are being written to are locked of course, but if a backup application is trying to copy that file at that time, errors within the backup logs can result: it is best to schedule backups at a time of reduced functionality.

The development DBA is usually an analyst who spends a lot of time programming and configuring applications and interfaces that connect to the database. Supporting the application development life cycle, this type of DBA often builds the database environment that is not yet operational: thus no immediate impact to business. The DBA follows project schedules and Gantt charts frequently and works closely with management and programmers to ensure support of business applications via the database structures. This includes working with the “application team to create and maintain effective database-coupled application logic [which includes] stored procedures, triggers, and user-defined functions (UDFs) (Mullins, 2003).

Patching is a constant in any information system; and patches must be test within a development or test system before being integrated into production. Therefore thorough testing of patches must be undertaken to examine the effects that this would have on the production system.

Skill in normalization is paramount in this position. Following the Database development Life Cycle (DBLC), similar to the systems development life cycle, this position works with management and top analysts in planning the database to realize business rules and goals.

OFA Standard

Every database should follow this standard. The Optimal Flexible Architecture standard is a set of guidelines that defines file naming and configuration that will ensure more structured Oracle installations. It also provides standards that can improve system performance by “spreading I/O functions across separate devices by separating data from software” (Powell & McCullough-Dieter, 2007). Bottlenecks can be avoided by utilizing OFA, as this will improve performance by sorting elements into distinctive directories that are placed within separate devices. In short, OFA is a more logical way of file and file indexing methodology that enhances system I/O.

Oracle OFA structure is installed into the directory ORACLE_BASE. On Windows systems, it follows the convention – c:\oracle\product\11.x. Within the UNIX/Linux architecture, we have /app/oracle/product/11.x. The database file architecture then includes within the base:

Admin: contains ini files and logs

db_1: the main Oracle database installation

client_1: client installation

oradata/<db name>: datafiles for each database, control and redo logs.

flash_recovery_area: backup and recovery and archive logs.

Each database function has its own area, or location of operation and “is stored separately” (Powell & McCullough-Dieter, 2007) from other types of system files.

All Oracle binaries are stored under the directory structure known as ORACLE_HOME. Each database will have its own ORACLE_HOME format; i.e.: /app/oracle/product/11.x/db1 (UNIX OS) or c:\oracle\product\11.x\db1. Within the OFA naming standard of Oracle, there are three important files located within the ORACLE_BASE/Oradata directory:

  • Control file (.ctl) – associated with only one database, this administrative file contains the database name and unique identifier (DBID), creation timestamp, data file information and redo log files, tablespace information, and RMAN backup information.
  • Redo log file (.log) – The database has two or more of these that consists of redo entries, or records, that can be used to restore recent changes to database data if needed. These redo log files are known collectively as the redo log.
  • Datafile (.dbf) – Belonging to each tablespace within a database, the datafile contains tables and indexes for the database.

As we have seen, Oracle is a very organized system that segregates its various elements to increase I/O and performance as well as recoverability.

Database security and auditing

Besides regular user permissions who can access the database with fundamental read/write permissions, there are two administrative privileges within the Oracle database: SYSDBA and SYSOPER. These permissions are high level operation levels that allow one to start up and shut down the database, and create database entities. SYSDBA is for fully empowered database administration, allowing all database permissions and access. The SYSOPER permission also has all administrative permissions, with the exception that it does not permit the ability to look at user data.

System auditing is used for supervising access, updates, and deletions. Oracle has three types of auditing: Statement auditing that requires the AUDIT SYSTEM privilege can be used to monitor changes to a table by a certain user account. This can be done within this context: AUDIT UPDATE TABLE by <user name>;. Privilege auditing presents the ability to monitor track table creation. This can be used to monitor any activity by any user. Object auditing can be used to monitor a certain object. This can be used to monitor queries to a particular table. The syntax for this is AUDIT SELECT ON <TableName>;.

Database monitoring

Monitoring of the database can be done via alerts. These alerts notify the administrator when a threshold has been reached. Alerts can be set to perform actions such as running a script when a threshold has been reached. An example of this is with a script that shrinks tablespace objects whenever a usage alert has been triggered.

Another means of database monitoring is performance self-diagnostics and automatic database diagnostics monitor (ADDM). Oracle “diagnose[s] its own performance” (Oracle, 2009) and can decide how to resolve identified anomalies. Snapshots are a part of ADDM and are used for performance comparison to other time periods, and the snapshots are stored up to 8 days before being purged to free space for newer snapshots.

Monitoring general database state and workload is done by the administrator via the database administration home page on the local system. CPU and memory utilization are presented, and all tools are available to shutdown, edit users, monitor queries, configure flashback, monitor workload, SQL response time, and much more.

Managing alerts includes viewing thresholds, clearing alerts, and “setting up direct alert notification” (Oracle, 2009).

Performance tuning

Part of proactive maintenance, performance tuning is the result of good monitoring practice. From the prediction and tracking of performance problems via the monitoring tools within the database control area, tuning the database begins. The monitors are also advisors: ADDM as previously mentioned, is an advisor as well as a monitor. Other advisors within Advisor Central within 10 g Database Control are SQL Tuning Advisor, SQL Access Advisor, Memory Advisor, MTTR Advisor, Segment Advisor, and Undo Management.

Backup and recovery

There are a few types of database backups in Oracle. Hot backup is performed when the database is in full operation, containing open files. Snapshots are used to backup the database, or smaller parts of the database one file at a time. This way, files can be restored into a running database individually or within a group. Backup tools for this include export and import data pump utilities, tablespace copies, RMAN (Recovery Manager), and Oracle Enterprise Manager and the Database Control (Powell & McCullough-Dieter, 2007). One effective means of continuity within an unstable environment is using the standby database: a backup database that is identical to the production database that can be failed over in seconds to provide continuous availability. This is also known as a physical standby database.

The most common tools of backup and recovery measures are checkpoints, redo logs, archive logs, and flashback. According to Powell and Dieter, a combination of archive logs and redo logs provides the ability of recovery from a point in time as long as the archive logs have been retained in the database structure (2007). Flashback is flash recovery that contains flashback data for a specific time of operation. The difference between physical and flashback recovery is that physical recovery can entail the restore of the entire database, where flashback includes the recovery of specific parts such as a table or a table’s entities.

Conclusion

Research skills are important for a successful database administrator; as the occupation includes examining anomalies, bugs, patch research, and normalization development. Performance monitoring and tuning are a daily task for the administrative DBA, and both performance and developmental DBAs should be well versed in the process of database normalization, standards, monitoring, and backup and recovery tools and methods.

References

McLean, C. (2006). Database Administrators: Multitasking for Advancement. Certification Magazine, 8(1), 30-40. Retrieved from Research Starters – Business database.

Mullins, C. (2003). DBAs Need Different Skills in Development and Production. The DBA Corner. Database Trends and Applications. Retrieved February 5, 2010 from http://www.craigsmullins.com/dbta_023.htm

Powell, G, & McCullough-Dieter, C. (2007). Oracle 10g Database Administrator: Implementation & Administration. Boston: Thomson Course Technology.

Oracle 11g. (2009). Monitoring and Tuning the Database. Oracle Database 2 Day DBA. 11g Release 2 (11.2). Retrieved February 13, 2010 from http://download.oracle.com/docs/cd/E11882_01/server.112/e10897/montune.htm#CACCIHAB

Advertisements

About Sedulus
Initiate.

Comments are closed.

%d bloggers like this: