Read an Excerpt
Chapter 5 : GUI-Based Approach To Oracle Administration
The Oracle8 Server on Windows NT delivers the complete functionality of Oracle8 on a user-friendly operating system. in the first four chapters, we introduced Windows NT and Oracle8 concepts. We also looked at the installation and configuration of Oracle8 on Windows NT 4.0. In theremainder of this book, we will provide Oracle-related information that will help DBAs perform their job efficiently on Windows NT. We will begin by looking at the responsibilities and common tasks for Oracle DBAs. After this we will provide a comprehensive look at the GUI tools and utilities provided by Oracle8 on Windows NT for DBAs. Most tasks on Windows NT can be completed using these GUI tools. In the next chapter, we will look at character-mode tools available with Oracle8. We will revisit the tasks performed in this chapter using a command line interface (CLI). Whether you prefer the GUI or the CLI, these two chapters should arm you with all the information that you need to be a DBA on Windows NT. Many DBA tasks are routine and can be scripted using batch (.bat) files. You can automate many tasks by scheduling batch files for execution. DBAs in real life use a combination of GUI and CLI to perform their tasks.
The information in the rest of this book supplements the information provided by Oracle8 documentation and applies to Oracle8 Servers on other platforms. However, we will keep our focus on Windows NT.
Who Are Oracle DBAs?
Oracle DBAs are a group of one or more privileged users who own and manage Oracle databases. DBAs are similar to system administrators for an operating system. (Windows NT 4.0 has a special groupof privileged users called administrators while UNIX has a privileged group named root.) Oracle DBAs are trusted users who are expected to perform tasks ranging from installation to maintenance. Routine maintenance tasks include managing users, database space, backup and recovery, and performance tuning. While the system administrators on some sites also have DBA responsibilities, most large sites have specialized DBAs. It is critical that DBAs have a thorough understanding of all aspects of administration to keep downtimes to a minimum. Difficult situations can turn into a crisis because of errors made by DBAs, and can prove to be very expensive.The Oracle database has a special role named DBA that can be granted to a user. This role includes system and object privileges that are required by typical DBAs. if this role does not satisfy the requirements of your site, you can create your own role or customize the existing DBA role by adding or removing privileges. This can be done using Security Manager or the GRANT and REVOKE commands in SQL.
Tasks And Responsibilities Of DBAs
Oracle DBAs are expected to perform well-defined tasks on a regular basis. However, the number of DBAs that are required to do the job is site dependent. We have seen sites that maintain primary and secondary DBAs with specific responsibilities. We have also seen other sites that have a dedicated DBA for every database on-site. For other mission critical sites, 24 x 7 coverage is important and one or more DBAs are always kept on call. We have listed typical DBA tasks in this section. This list is by no means comprehensive. If you have a new Oracle site and are planning to create a team of DBAs, you must ensure that most of the skills listed here are covered by the team. Your site may also have some special needs in addition to those listed here. You should also build some redundancy into your team of DBAs by having overlapping skills and responsibilities.Installation
Perhaps the most obvious task for DBAs is installation. DBAs need to perform installations of the Oracle Server, tools, and applications. Successful DBAs should be able perform installations with minimal guidance from documentation. DBAs should also be able to install in a variety of environments, including client/server and three-tier systems. If your site has a heterogeneous environment of network protocols and operating systems, you should be able to install Oracle software in multiple operating environments. Along with this, Oracle DBAs need to be familiar with the Oracle installer technology.DBAs at large sites, who are responsible for many users, must be comfortable performing an installation across a network. On Windows NT, you can create logical network drives and use these as staging areas for installation. These staging areas can be used for installations across a network.
Upgrades
Most software is revised on a regular basis. Oracle software is no different. Newer versions of Oracle software provide enhanced functionality and fix software issues or bugs. Oracle DBAs need to keep their site current to take advantage of the latest versions of software. Oracle software packages have version numbers in the format A.B.C.D where the degree of change reduces from left to right. A major revision change is reflected in the first digit (A) and minor bug fixes are reflected in the last two digits (C and D). The second digit (B) usually reflects an enhancement or a change required for porting the software to a new platform. Upgrades that involve a change in the last two digits (C and D) are normally trivial, while upgrades that involve a change in the first two digits (A and B) require planning. A revision change reflected by a change in the first digit (A) requires scheduling, and may involve some downtime. You must plan such upgrades in a time slot that has the least impact on users. Many sites schedule such upgrades on weekends. A major revision change such as an upgrade from Oracle7 to Oracle8 requires a database migration. Upgrades in versions that change the last three digits (B, C, and D) usually do not require a database migration.
NOTE: Almost all Oracle software is built for open systems. Products are first developed on a "base" operating system (platform) and then ported to other operating systems. The same product could have a slightly different version number on two operating systems. The base platform is typically one or two minor releases ahead of other ports. At this time, either Windows NT 4.0 or Sun SPARC Solaris 2.5.x are used as the base port for the majority of Oracle products.
We will look at migration on Windows NT in detail in Chapter 8. In any case, an upgrade should always be performed with care, and if it fails, you must be in a position to revert to the previous version. For example, during an upgrade from Oracle7 to Oracle8, you should create a full backup of the database before you attempt the upgrade. It is imperative that you have a usable backup because you cannot revert an Oracle8 database to Oracle7! if possible, you should keep a backup of the entire Oracle directory structure before you attempt a major upgrade.
Capacity Planning
DBAs need to be actively involved with capacity planning on-site. They must be aware of the growth patterns of the business and the impact of this growth on database applications, including the size of the database(s). it is important to plan for proper hardware based on future projections. It is likely that buying extra hardware along with the computer will be less expensive than upgrading components at a later point in time. When you buy hardware for your database needs, plan your hard disk and memory requirements keeping growth in mind. It is not uncommon for Oracle databases to grow on the order of megabytes per day!If you have a site that has very high database activity and a lot of input/output (1/0) to the database, you should consider disk striping. The number of disk controllers can also impact performance. We will discuss 1/0 further in Chapter 7 under performance tuning.
If you are deploying database applications on the Web, capacity planning can be a daunting task. It is hard to predict growth and transaction rates in large networks such as the Internet. Distributed solutions can help balance loads in these situations. We have introduced distributed databases and replication in Chapter 4.
Space Management
Oracle DBAs need to manage the logical structure of the database based on the needs of the application(s). Many applications require specific structures for data storage. Tablespaces need to be sized appropriately. Storage parameters like INITIAL EXTENT, NEXT EXTENT, and MAXEXTENTS need to be selected with care. You must consider all types of segments for proper space management. Space management for index segments, rollback segments, and temporary segments can be relatively simple with proper planning.User Management DBAs must be able to create and manage users on the databases. Every user must be granted roles and privileges as per application demands. in addition to the roles and privileges granted on the database, DBAs must ensure that users do not compromise security at the file system level. We recommend that you use the NTFS if you want to ensure security at the file system level. Otherwise, users who are part of the Administrators group in Windows NT can use their operating system privileges to acquire unauthorized privileges on an Oracle database, which is not a desirable situation.
Manage Database Objects
Oracle DBAs are required to create and manage database objects. These objects include tables, indexes, synonyms, and so forth that are required by applications, as well as system objects required by Oracle such as the data dictionary. Database schemas also need to be managed. it might be necessary to move database objects to a different tablespace. If you are using the partitions feature of Oracle8, additional management might be necessary.Auditing If your site requires additional monitoring of database access, you will be required to set up appropriate auditing features to track users. Additional objects need to be created in the database for auditing.
Performance Tuning
Performance is always a big concern at many sites. Performance tuning is an iterative process and requires close monitoring on a daily basis. DBAs on Windows NT can use Performance Monitor to measure the performance of the operating system, CPU usage, virtual memory, and database performance. Chapter 1 provides information on Performance Monitor. We will also discuss tuning in more detail in Chapter 7.Backup And Recovery
The Oracle8 Server provides a variety of backup mechanisms that allow DBAs to create robust backups. It is important to design a backup procedure that meets the demands of your site. DBAs also need to tune the frequency of backups based on the site needs. The goal is to minimize downtime if recovery is ever required. The backup strategy you choose to use will directly impact downtime. Archived redo log files need to be managed properly in order to perform full recovery in case of a database crash. We will cover backup and recovery for Windows NT later in this chapter. You can also refer to Oracle8 Backup & Recovery Handbook (Osborne/McGraw-Hill, 1998) in the Oracle Press series for comprehensive coverage of this subject.Training
DBAs should also be involved in user training. Many issues can be avoided if appropriate training is provided to application developers and users. We have seen a site that had 1,000 plus users who had the habit of turning off their PCs at the end of the day without a graceful exit! DBAs on the site had to get involved with training the users to get out of this habit. Some application developers do not understand the impact on the database when they are designing an application. With training, some issues can be avoided at the application design stage itself.Troubleshooting
Oracle DBAs need to troubleshoot issues from time to time. They need to tackle issues ranging from connection issues to tuning. Some large sites maintain a 24 x 7 help desk that helps users with issues. Experienced DBAs can ensure that minimal checks have been performed before reporting a problem to Oracle technical support staff. DBAs need to be comfortable with diagnostic tools like SQL traces and alert logs.
TIP: Oracle Enterprise Manager provides facilities for "lights-out-management." You can set up alerts including automatic paging and e-mail based on certain events on your database. Refer to the section on Oracle Enterprise Manager later in this chapter for details.
Becoming A DBA
Before we discuss the tools and utilities available with Oracle8, we will see how you can connect to Oracle as an authorized DBA. This is because you need to connect as a DBA to use most of these utilities effectively. In order to be an authorized DBA, you need to connect to Oracle as a user with DBA privileges. You can use the built-in DBA role to get these privileges or define a separate role for privileges that makes sense for your site. The authentication of such a login can be performed by Oracle itself or through external means such as the operating system. We will look at the different means of authentication in the following sections.The DBA Role As mentioned previously, there is a special role named DBA in Oracle. Any Oracle user that is granted this role can become a DBA and is automatically assigned certain system privileges. Oracle maintains user and role information within the database. Any user with the DBA role can grant DBA privileges to other users. We will cover the system privileges associated with the DBA role later in this section.
NOTE: Oracle provides two built-in users with DBA privilege. One user is called SYS and has the password CHANG E_ON_INSTALL and the second user is called SYSTEM and has the password MANAGER. Be sure to change the passwords for these accounts on your production system.
You can grant the DBA role to an existing Oracle user or create a new user and grant the DBA role to that user. You can use the GRANT command in SQL or use Security Manager to grant the DBA role to a user. Security Manager is available as part of Oracle Enterprise Manager (OEM). Tools like SQL Plus 8.0, SQL Worksheet, or Server Manager can be used to issue ad hoc SQL commands on Oracle8 for Windows NT. Table 5-1 provides information on how you can access these tools. We will look at an example of how Security Manager can be used to grant the DBA role to a user. . . .