The Database Administrator

While learning the job of DBA cannot be done entirely in a few short months, a great place to start learning is this chapter. It provides an overview of the main tasks of a DBA such as the daily operations and how to manage database objects, users and space. (From the book Oracle Database 10g: A Beginner’s Guide, by Ian Abramson, Michael Abbey, and Michael Corey, McGraw-Hill/Osborne, 0072230789.)

oracleSo, you’ve decided to be a Database Administrator (DBA). Great choice! On top of that, you’ve chosen Oracle as the Database Management System (DBMS) that you want to work with. Even better! All you need to do now is figure out how to learn what you need to know to do the job. Reading this book is a great start. However, the job of a DBA cannot be learned entirely in a few short months. It is a work in progress that can take several years to become really good at. Don’t get us wrong—you can learn the basics that will make you a productive DBA in a few short months, but there is a great deal to learn, and we don’t become really good at this job until we’ve actually run the utility, executed the SQL, or performed the task. In other words, don’t just read this book—try the examples and don’t be afraid to make mistakes.

Critical Skill 3.1 — Learn the Job of the DBA

The role of a DBA is more of a career than a job. Those of us who have been doing this for many years are always learning new things and just trying to keep up! That’s the exciting thing about being a DBA: the job keeps changing. Databases are growing at a phenomenal pace, the number of users is increasing, availability requirements are striving for that magical 24/7 mark, and security has become a much greater concern. As you will see in this book, databases now include more than just data. They are also about the Internet and grid computing and XML and Java. So, how long will it take you to learn how to be a DBA? For as long as you’re practicing this career.

There are some concrete steps that you can take to jump-start your learning process. Undertaking an Oracle Certification will provide you with a structured program that offers you clear steps to help learn the details of the job. Instructor-led courses as well as CD- and Internet-based classes can help you through the process. Also, read as much as you can and then get your hands on a test database and practice what you’ve learned.

Applications come and go, but data stays around. All of the information that makes your company valuable is (or should be) stored in a database. Customer, vendor, employee, and financial data, as well as every other corporate data is stored in a database, and your company would have great difficulty surviving if any of that data was lost. Learn your job well. People are depending on you.

CRITICAL SKILL 3.2 — Understand the Oracle Database 10g DBA Skill Set

There is good news for DBAs: Oracle has tools to help you do your job and manage your databases. These tools have existed for many versions of Oracle and have improved with each release to the point where the Oracle Database 10gofferings are extensive. In many cases, you will have the option of doing your job using a GraphicalUserInterface(GUI),and you will also have the option of using a command-line interface. We recommend learning both. You will need to use the command-line interface in many cases to schedule work through scripts. The GUI can be used for performing day-to-day operations and can also be used as a great learning tool the first time you perform an operation. In many cases, you will be able to generate the low-level commands from the GUI and can copy them to a file to be used later on.

As we’ve mentioned, there is a great deal that you will need to know in order to be able to provide well-rounded coverage of your Oracle environment. We can categorize the specialized areas of database management so that you will be aware of the whole picture and can break your work into well-defined groupings.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Perform Day-to-Day Operations}

CRITICAL SKILL 3.3 — Perform Day-to-Day Operations

In order to properly perform the role of Database Administrator, you will need to develop and implement solutions that cover all areas of this discipline. The amazing part of this job is that you may be asked to do many, or perhaps all, aspects of your job on any given day. Your daily tasks will vary from doing high-level architecture and design to performing low-level tasks. Let’s take a look at the things that you will be getting involved in.

Architecture and Design

DBAs should be involved with the architecture and design of new applications, databases, and even technical infrastructure changes. Decisions made here will have a large impact on database performance and scalability and database knowledge will help choose a better technical implementation. Data design tools such as Oracle Designer can assist the DBA.

Capacity Planning

Short and long range planning needs to be performed on your databases and applications. This will focus on performance and sizing characteristics of your systems that will help to determine upcoming storage, CPU, memory, and network needs. This is an area that is often neglected and can lead to big problems if it is not done properly.

Backup and Recovery

A backup and recovery plan is, of course, critical in order to protect your corporate data. You need to ensure that data can be recovered quickly to the nearest point in time as possible. There is also a performance aspect to this since backups must be performed using minimal resources while the database is up and running and recoveries need to be performed within a time limit predefined by Service Level Agreements (sla) that are developed to meet customers’ requirements. A complete backup and recovery implementation should include local recovery and remote recovery that is also referred to as disaster recovery planning (drp). You will see more on backup and recovery later in Chapter 5.

Security

This is an area that has become very sensitive due to the number of users that can access our databases and the amount of external, web-based access. Database users need to be authenticated so that we know with certainty who is accessing our database. They must then be given authorization to use the resources that they need to do their job by granting access to the objects in Oracle. This can be managed with Oracle Enterprise Manager, and we will show examples of this later in this chapter. External users require extra web-based security that is beyond the scope of this book.

Performance and Tuning

Performance and tuning is arguably the most exciting area of database management. Changes here are noticed almost immediately and every experienced DBA has stories about small changes they’ve made that resulted in large performance gains. On the other hand, every performance glitch in the environment will be blamed on the database and you will need to learn how to deal with this. Statspack, OEM Performance Management, and third-party tools will assist you in this area. There is a lot to learn here, but the proper tools will simplify this considerably.

Managing Database Objects

We need to manage all schema objects such as tables, indexes, views, synonyms, sequences, clusters, and source types such as packages, procedures, functions, and triggers to ensure they are valid and organized in a fashion that will deliver adequate performance and have adequate space. The space requirements of schema objects are directly related to tablespaces and datafiles that are growing at incredible rates. Using OEM, this can be simplified, something we will see examples of later in this chapter.

Storage Management

Databases are growing at incredible rates. We need to carefully manage space and pay particular attention to the space used by datafiles and archive logs. Online utilities are supported to help reorg objects while they remain online. Reorgs use considerable resources, however, so do not perform these operations unless it is necessary. See the section “Managing Space” for more on this.

TIP Do not reorg unless you absolutely need to.

Change Management

Being able to upgrade or change the database is a discipline that includes many areas. Upgrades to the database schema, procedural logic in the database, and database software must all be performed in a controlled manner. Change control procedures and tools such as Oracle’s Change Manager and third-party offerings will assist you.

Schedule Jobs

Oracle Database 10gcomes with a new scheduler that allows you to schedule a job for a specific date and time, and to categorize jobs into job classes that can be prioritized. So, resources can be controlled by job class. Of course, other native scheduling systems such as “at” in Windows and crontab in UNIX can be used as well as other third-party offerings.

Network Management

Oracle Networking is a fundamental component of the database that you will need to become comfortable with. Database connectivity options like Tnsnames, the Oracle Internet Directory (OID), and the Oracle Listener require planning to ensure that performance and security requirements are met in a way that is simple to manage. You will see more of this in the next chapter.

Troubleshooting

Though troubleshooting may not be what you’d consider a classic area of Database Management, it is one area that you will encounter daily. You will need tools to help you with this. Oracle MetaLink technical support, available to customers who purchase the service, is invaluable. Oracle alert logs and dump files will also help you greatly. Experience will be your biggest ally here and the sooner you dive into database support, the faster you will progress.

You’ve seen the areas of database management that need to be handled, now it’s time to look at the Oracle schema and storage infrastructure.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Understand the Oracle Database 10g Infrastructure}

CRITICAL SKILL 3.4 — Understand the Oracle Database 10g Infrastructure

Oracle’s memory and process infrastructure have already been discussed in Chapter 1. In this section, we will take a look at the Oracle schema and storage infrastructure since these are a large part of what you will be required to manage.

Schemas

An Oracle database has many schemas contained in it. The schema is a logical structure that contains objects like segments, views, procedures, functions, packages, triggers, user-defined objects, collection types, sequences, synonyms, and database links. A segment is a data structure that can be a table, index, or temporary or undo segment. The schema name is the user that controls the schema. Examples of schemas are the System, Sys, Scott, and SH schemas. Figure 3-1 shows the relationship between these schema objects.

Segments, Extents, and Blocks

As you can see in Figure 3-1, a schema can have many segments and many segment types. Each segment is a single instance of a table, partition, cluster, index, or temporary or undo segment. So, for example, a table with two indexes is implemented as three segments in the schema. A segment is broken down further into extents, which are a collection of contiguous data blocks. As data is added to Oracle, it will first fill the blocks in the allocated extents and once those extents are full, new extents can be added to the segment as long as space allows. Oracle segment types are listed here:

  • Tables are where the data is kept in rows and columns. This is the heart of your database and a table is implemented in one schema and one tablespace. The exception to this is a special type of table called a partitioned table where the table can be split into different ranges or sets of values called a partition and each partition can be implemented in a different tablespace.



    Figure 3-1: The database and user schemas

    Remember, however, that each partition is itself a segment and each segment can only reside in one tablespace. Clustered tables are another special case where two tables with a close link between them can have their data stored together in a single block to improve join operations.

  • Indexes are optionally built on tables for performance reasons and to help implement integrity constraints such as primary keys and uniqueness.

  • Temporary segments are used as a temporary storage area by Oracle to run an SQL statement. For example, they may be used for sorting data and then discarded once a query or transaction is complete.

  • Undo segments are used to manage the before image of changes to allow data to roll back if needed and to help provide data consistency for users querying data that is being changed. There will be more on this in Chapter 5.

Segments can be thought of as physical structures since they actually are used to store data that is kept in a tablespace, although some of this is temporary in nature. There are other structures stored in the schema that are more logical in nature.

Logical Schema Structures

Not everything stored in a database and schema is data. Oracle also manages source modules as well as supporting structures such as sequences that are used to populate new unique and primary key values when inserting data into the database. These objects belong to a schema and are stored in the Oracle Catalog. These can all be easily managed through OEM, as shown in Figure 3-2. Take a look at the following for a brief description of these logical structures:

  • Views give you the capability of subsetting a table and combining multiple tables through a single named object. They can be thought of as a stored query. With the exception of a special type of view called a materialized view that is used for data warehousing, data is not stored in views. They are simply a new way of defining access to the underlying tables. These can be used for security, performance, and ease-of-use.

  • Synonyms are used to create a new name or alias for another database object such as a table, view, another synonym, and sources such as a procedure, package, function, java class, and so on. They can be used to simplify access. As with views, data is not stored in a synonym.

  • Sequences are used to generate new unique numbers that can be used by applications when inserting data into tables.

  • Source programs can be stored in the catalog and written in Oracle’s proprietary PL/SQL or Java. PL/SQL source types include business logic that can be written as Packages, Procedures, and Functions. Triggers can also be used to implement business logic, but are often used to implement data integrity since they are not executed directly by a user or source program, but rather are automatically executed when an action is performed on the database. Java Sources and Java Classes are also implemented directly in Oracle. This server-side support of application logic improves performance since the logic resides with the data and it also improves performance.
  • User types can be created by you to support object-oriented development. Array types, Object types, and Table types can all be created by you. As well, the Oracle XML Schema Processor supports XML processing by adding data types to XML documents that can be used to ensure the integrity of data in XML documents.

So now that you have seen all of our schema objects, it’s time to tie these together to our storage architecture.


Figure 3-2.
Enterprise Manager table definition view

Storage Structures

As shown in Figure 3-1, the physical schema objects are stored as segments in the database. Each segment can only be stored in a single tablespace and a tablespace can be made up of one or more datafiles. If a tablespace is running out of space, you can expand the datafiles it is made up of, or you can also add a new datafile to the tablespace. A datafile can only store data for a single tablespace.

A single tablespace can store data for multiple segments and in fact for several segment types. Segments from multiple schemas can also exist in the same tablespace. So, for example, table_a from schema1 and index_b from schema2 can both be implemented in the same tablespace. Oh and by the way, a tablespace can only store data for a single database.

The logical structures such as views and source code are stored in the Oracle catalog but are part of a schema. So, this means that the Oracle-supplied SH schema can contain all of the objects that it needs to run the entire application under its own schema name. This provides strong security and management benefits.

Progress Check

  1. Name five areas that you will need to address as a DBA.

  2. What is a schema and what does it contain?

  3. Can a tablespace store more than one segment type?

  4. Under which circumstances would you bother to start up the database in nomount mode?

Progress Check Answers

  1. As a DBA, you will need to address architecture, capacity planning, backup and recovery, and security and performance, among others.

  2. A schema is a logical structure that contains objects like segments, views, procedures, functions, packages, triggers, user-defined objects, collection types, sequences, synonyms, and database links.

  3. A single tablespace can store data for multiple segments and different segment types. Segments from multiple schemas can also exist in the same tablespace. So, for example, table_a from schema1 and index_b from schema2 can be implemented as two segments in the same tablespace.

  4. When started in nomount mode, the parameter file is read and memory structures and processes are started for the instance. The database is not yet associated with the instance. You will use this in cases where you need to re-create the controlfile.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Operate Modes of an Oracle Database 10g}

CRITICAL SKILL 3.5 — Operate Modes of an Oracle Database 10g

Oracle is a software package like many others that you may have used. However, when you run most programs, they run one and only one way. So when I open my accounting software, I run it the same way all the time. However, you have options with Oracle. This section discusses the many ways that you can run Oracle. Some of these methods will be important to administrators, while others will allow for full use. This feature is important when you need to perform both critical and noncritical activities, and not interfere with your users or your data.

Modes of Operation

Oracle has several modes of operation. In most cases, when you start Oracle, you will simply issue the command:

> Startup;

This command actually takes Oracle through three distinct startup phases automatically, or you could also choose to explicitly step through these phases:

  1. In the nomount phase, the database reads the spfile or the init.ora parameter file and starts up the Oracle memory structures as well as the background processes. The instance is started, but the database is not yet associated with the newly started instance. This is usually used in cases where you need to re-create the controlfile. The command to perform this is

    > startup nomount;

  2. In order to associate a database with the instance, the instance “mounts” the database. This is done in the mount phase. The previously read parameter file is used to find those controlfiles, which contain the name of the data files and redo logs. The database is then mounted to allow some maintenance activities to be performed. Datafiles and redo logs are not opened when the database is in mount mode, so the database is not yet accessible by end users for normal tasks. Commands to mount a database are

    > startup mount;
    > alter database mount;

  3. When Oracle opens the database in the open phase,it opens the data files and redo logs, making the database available for normal operations. Your redo logs must exist in order for the database to open. If they do not, the resetlogs command must be used to create new redo logs in the location specified in the control files.

    > Startup {open} {resetlogs};
    > alter database open;
Other Ways to Open the Database

There are some other options for opening a database. For example, you may want to open it in Read-Only mode so that no database changes (inserts, updates, or deletes) can be performed. There are also the upgrade/downgrade options that allow a database to be opened to perform a downgrade or upgrade to another version of Oracle.

> alter database open read only;

A common option you will use to perform maintenance will be to open the database in restricted mode. When you issue the command startup restrict, only users with both the create session and restricted session privileges will be able to use the database. So, as a DBA this is a helpful way to open the database that only you can use.

> startup restrict;

The database can be placed in a state where only the sys and system users can query the database without stopping the database and performing a subsequent startup restrict. The activities of other users continue until they become inactive. This can be performed using the quiesce option of alter session when the Database Resource Manager option has been set up.

> alter system quiesce restrict;
> alter system unquiesce;

Forcing a Startup

Over time, you will run into situations where Oracle has not shutdown properly and you are unable to restart it. In these rare instances, you will need to use the force option of the startup command. This will first perform a “shutdown abort” that forces the database to shutdown (see the next section for more information on this) followed by a database startup.

> startup force

Database and Instance Shutdown

When shutting down an instance, perform these steps, which are reverse from those you just saw when opening a database:

  1. Close the database, including the data files and redo logs, so that it is no longer usable for normal tasks.

  2. Unmount the database from the instance so that only the instance memory structures and background tasks are left running without a database associated with them.

  3. Shut down the instance to close the control files.

In order to shut down a database, four different approaches can be used: Shutdown Normal, Immediate, Transactional, and Abort.

  • Normal is, in a sense, the “perfect” way to shut down, since this approach will wait for all users to disconnect from the database and all transactions to complete before the shutdown occurs. Once this command has been issued, new users are not allowed into the system. This can be impractical in cases where users remain on the system for long periods of time.

    > shutdown normal;
  • Immediate is a practical shutdown approach that also leaves the database in a consistent state. When the database is put through a “shutdown immediate,” all current transactions are rolled back and users are disconnected. No new transactions are allowed into the system. This will be relatively quick if the rollback operations are small, and is an excellent way to shut down the database before performing a database backup.

    > shutdown immediate;
  • A transactional shutdown is similar to the immediate variety except that running transactions are allowed to complete. So, once transactions have been committed, the user running it is disconnected. This is useful in cases where you do not want to shutdown until currently running transactions have finished or in cases where it will be quicker to complete existing transactions than it will be to roll them back.

    > shutdown transactional
  • Abort is the least graceful shutdown option of the four. When this is used, all transactions are ended immediately without waiting for a rollback or commit and all users are instantly disconnected while the database is brought down. Use this only if you are experiencing problems shutting down the database using one of the three options described previously or in cases where you need to shutdown the database immediately. The database needs to go through recovery procedures the next time it is restarted. After a shutdown abort has been performed, you should try to immediately start up the database so that you can then perform a shutdown (normal, immediate, or transactional) to bring the database down in the proper manner.

    > shutdown abort;

OEM can help with instance and database startup and shutdown, as shown in Figure 3-3. Open OEM. In the left panel, choose the instance you want to work on and select “Instance” under the instance name (in this case, it’s ora10g), then on the right side of the panel, choose View And Edit The Values Of Instance Parameters.


Figure 3-3. Enterprise Manager instance configuration view

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Get Started with Oracle Enterprise Manager}

CRITICAL SKILL 3.6 —  Get Started with Oracle Enterprise Manager

Oracle Enterprise Manager (OEM) is a great tool to assist the beginner DBA through to the experienced one. You should, however, also learn the low-level commands that will allow you to do your job through an interface like SQL*Plus. OEM can help you with this by showing you the SQL that it has generated when you select the Show Sql button that exists on many windows. Given how many options OEM has to help you do your job as a DBA, we will take a quick look at them here.

First off, OEM can be used to manage all of the databases in your network. As you can see in Figure 3-3, once you expand the network, you can select Databases and manage your databases from there. The right side of this OEM panel shows all of the objects that can be managed through OEM such as instances, schemas, security, distributed, warehouse, and workspace management features.

Instance Configuration

Once you select a database, which in our example is named ora10g, you can drill down to the instance and then the configuration of that instance. Figure 3-3 shows that we can see the state of an instance as well as all initialization parameters and whether the database is running in archive log mode and if it is in a restricted state. As you can see from the tabs on the right panel of this screen, we can see and manage memory settings, recovery options, resource monitors in effect, and undo information.

User Sessions

Now that we have a good handle on managing our instances and databases, we can drill down to our user sessions to see exactly what is going on inside the database. By choosing a session, we can see some general information such as the user session ID, when they logged in, and what the OS username and terminal name are for this user. As you can see from Figure 3-4, we can also see the SQL that is currently running, along with the explain plan being used. You can follow the order that each explain step is being performed in by the Step # column and can step through the plan or see it in a graphical layout using the far right column. You can also manage sessions and disconnect users by right-clicking the username and issuing the kill session command. I admit that the command name may be a bit harsh, but it does get the idea across.

Resource Consumer Groups

Next, we can select the Resource Consumer Groups item to see all of the groups that exist. A resource consumer group provides a way to group together users so that they can share similar processing requirements. The DATABASE_RESOURCE_ MANAGER package is used to allocate the maximum amount of CPU that a session can use or to set a limit for parallel execution for a session or to set the number of sessions that can be active for a consumer group as a few examples of this capability. OEM can assist in managing these groups by giving us an easy way to add new groups and edit those that exist. This panel allows us to enter a description of the group and attach users and database roles to a group. If you look at Figure 3-5, you will see all of the resource consumer groups listed. If you select one of these consumer groups, you will be presented with the capabilities to manage users, roles, and general information it.


Figure 3-4. SQL explain plan

A resource plan builds on the resource consumer groups by providing a way to define how system resources will be allocated to the resource consumer groups that we just discussed. Figure 3-5 shows a list of the groups and subplans that can be set up here. We see tabs that allow us to define maximum parallelism, concurrently active sessions, undo pool space, and execution time limits for the group. Group switching allows for a session to change groups after a predefined amount of execution time has been reached. Presumably, you would move the user to a lower priority group to free resources to other sessions. The resource plan schedule can be used to set daily schedules to enable and disable resource plans.

Schema, Security, and Storage Management

The next items on the OEM console are schema, security, and storage management. We will visit these in sections 3.7, 3.8, and 3.9/3.10, respectively. It is worth mentioning now, however, that all three of these can be completely managed through OEM.


Figure 3-5. Enterprise Manager resource group view

Distributed Management

Some Oracle distributed capabilities are handled through the Distributed option. These include the ability to:

  • Manage in-doubt transactions that can result from two-phase commit.

  • Create, edit, and drop database links.

  • Use streams to implement messaging.

  • Use advanced queues and replication to pass messages and data to applications.

This can be a difficult area to manage and having a tool such as the OEM Console to help us out with this is a very welcome feature.

Warehouse Features

Warehouse options such as summary management, materialized views, and dimensions can all be dealt with through OEM, as shown in Figure 3-5.

Other Tools

You will notice that the toolbar has an option called Tools. This includes more advanced tools for managing our environment. Let’s very quickly review the Tools that are included here. These tools can all be selected, as shown in Figure 3-6.

Database Tools to analyze data, perform backup management, and provide data management for utilities such as export, import, and load are included in the first option. Choose Tools and then Database Tools to get to these. Once Database Tools has been chosen, you will be presented with options to back up, recover, maintain, and configure your backup and recovery jobs. Backup management can be selected under Tools, as shown in Figure 3-6.


Figure 3-6. Enterprise Manage tool options

Database change management can be performed through the Change Management Pack. Under Tools, choose Change Management Pack or Standard Management Features and these will lead you to the Change Manager utility.

Database applications will provide support for the spatial index advisor, SQL*Plus Worksheet, and the Oracle Text Manager.

Tuning facilities such as performance manager, outline management, and tablespace maps are provided through Standard Management Features and Tuning Features, as shown in Figure 3-6.

As you can see from this overview of OEM console capabilities, many of the tools that we need to perform our day-to-day tasks can be found in this one console. Now that we have confidence that there’s a toolset to support us, let’s take a quick look at what you need to think about when managing database objects.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Manage Database Objects}

CRITICAL SKILL 3.7 — Manage Database Objects

A large part of your job as a DBA will be to manage the objects that exist in a database. Let’s look at the objects that you need to concern yourself with and discuss the main management issues that you will have in each of these areas.

Controlfiles

It is critical to the database that you have at least one valid control file for your database. These are small files and can be multiplexed by the Oracle instance. Ensuring that you have at least three copies of the controlfiles (remember, they are small), as well as text and binary backups whenever a data file, log file, or tablespace is changed and on a regularly scheduled basis (at least daily) will go a long way towards ensuring that your control files are in good shape. Controlfiles will be discussed in more detail in Chapter 5.

Redo Logs

Redo logs are necessary to ensure database integrity and should be duplexed in Oracle. Oracle mirroring helps even if your redo logs are mirrored by your storage subsystem since Oracle will use the alternate redo log if one should become corrupt. You will need to ensure that you have enough redo logs and that they are sized properly to support database performance. How large should your redo logs be? They should be large enough that a log switch does not usually occur more than once every 15 minutes due to the checkpointing that occurs during a log switch and the overhead that is incurred during this operation. How many redo logs should you have? You should have enough redo logs that the system will not wrap around to a log that has not yet completed a checkpoint or completed archiving (for systems in archivelog mode). Redo logs can be added, deleted, and switched through OEM.

Undo Management

The Undo segment is where the before images of changed rows are stored. Oracle will manage your undo segments for you, but you need to determine how large to make the tablespace that the Undo segment is stored in. The size that you make this depends on the length of time that you want the undo information to be available to you. If you look at Figure 3-7, you will see how OEM helps you determine the length of time that Undo can be retained based on the system activity and Undo tablespace. This is in the Configuration section for an instance. If the tablespace is not the correct size, it can be changed using the Storage feature of OEM, which you will see later in this chapter. Undo segments are covered further in Chapter 5.

If you choose to implement user-managed rollback segments, then these can be managed in the Storage section of OEM by choosing Rollback Segments and then selecting the segment name that you want to manage. If you look at Figure 3-7, you will see a Rollback Segment named System. By the way, the System rollback segment will always exist but should never be used as a rollback segment for user processes.


Figure 3-7. Enterprise Manager Automatic Undo Management view 

Schema Objects

Schema objects were discussed earlier in this chapter, and you saw that we can manage schema objects through OEM. There are also some things that you may want to do with your own SQL scripts that run as scheduled jobs. When managing schemas, you need to ensure that those physical objects that take up a great deal of space, do in fact have enough space to grow. This includes tables, indexes, clusters, and partitioned tables. Manage this space through the tablespace that they are implemented in and ensure that there is enough room to grow in the tablespace. Ensuring that the extent sizes are large enough that you do not need to allocate too many extents is something that you need to monitor. But, do not become reorg-happy. You do not need to reorg a table if it is in hundreds of extents. You only need to reorg if there are a large number of chained or migrated rows. Indexes, on the other hand, will need to be reorged more frequently. We will find out more about managing space in the next section.

Figure 3-2 shows an example of how the SH.Customers table can be managed through OEM. Note the Storage tab that allows you to change the table’s storage parameters. You should also try to maintain statistics on your tables and indexes so they are up-to-date. This will assist the optimizer make better decisions when choosing access paths for your queries and can be used to validate the structures. In Oracle Database 10g,a scheduler job called gather_stats_jobwill run during a maintenance window between 10:00 P.M. and 6:00 A.M., by default, and will run statistics for those objects in cases where they have not been collected yet or are stale. Setting the Oracle Database 10ginitialization parameter statistics_level to typical (the default) will allow Oracle to automatically update statistics as a background task on a regular basis and is the recommended approach for gathering statistics. In pre–Oracle Database 10greleases, the DBMS_STATS package should be run manually or can use the Monitoringkeyword in a CREATE or ALTER table. Monitoring is a deprecated feature in Oracle Database 10gand the keyword (along with “nomonitoring”) will be ignored.

Logical schema objects that do not take up a lot of space need to be watched to ensure they are not invalid. Triggers, views, synonyms, procedures, functions, and packages are examples of the objects that should be valid. You can check this with the SQL statement that follows.

select owner, object_name, object_type from dba_objects where status ^= ‘VALID’;

We’ve looked at many of the database objects that will require your attention. (The next section will) explore one area that requires special attention due to the size of today’s databases.


Ask the Expert Q: Why is it important for DBAs to get involved with the architecture and design of a new system?

A: Decisions made on the technical infrastructure as well as data and application designs here will have a large impact on database performance and scalability. Database knowledge will help choose a better technical implementation. Once chosen, these can be difficult to change.

Q: Which method do you normally use to shut down a database?

A: Although the shutdown normal operation is a recommended approach, it is often impractical since you need users to disconnect themselves. The approach that I prefer is to perform a checkpoint using the command alter system checkpoint which will write data out to data files and speed up the restart. I then perform a shutdown abort, immediately followed by a startup restrict, and shutdown immediate. This is a fast, guaranteed shutdown that leaves the database in a consistent state once all of the steps have been completed.

Q: What is the best way to become a good Oracle DBA quickly and then to keep improving?

A: There are many things that you will need to do and many skills that you’ll need to develop to do this job. First, learning the basic DBA skills, which you can get from books such as this as well as from courses, will give you a head start. Practicing what you see is probably the quickest and most practical way to learn. Getting involved in supporting some databases in development and production will force you to learn very quickly. Then working on development systems for different types of applications will help to round out your skills. Keep reading and learning and never assume that you know it all and you will do very well.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Manage Space} 

CRITICAL SKILL 3.8 —  Manage Space

The challenge of managing data in your Oracle Database 10g is one that provides you with options. In this section, we will look at the methods that have been used in the many versions of the database to manage your information. Today’s version of the database provides us with options. The first that we will discuss is managing your data and the files in which they reside in a manual way. Another option, automatic storage management, is discussed in Chapter 9.

Archive Logs

When you put the database in archive logging mode, the redo logs are written out to a directory that is named in your parameter or SPFILE. If that directory becomes full and the database attempts to write another archive log, the database activity will be suspended until sufficient space is made available for the new file. Create a large directory and schedule jobs to move the archive log files from online storage to tape before you encounter a space issue. RMAN does a nice job of helping you manage this. Please see Chapter 5 for more information on this.

Tablespaces and Datafiles

Space should be managed at the datafile and tablespace level rather than at a lower level such as a table or index. Using locally managed tablespaces with uniform extent sizes will simplify your management. Do not worry that you have some extents in a tablespace or for an object. This does not create a performance issue since the extents contain a number of blocks that must be contiguous. You can see the amount of space available in your datafiles by selecting Datafiles in OEM, as in Figure 3-8. This shows the amount of free space available in the currently allocated space. If you have used the autoextend feature to allow a datafile to extend in size when more space is needed, the extra space is not shown in this graph. Do not allow temporary tablespaces or undo tablespaces to autoextend since they will grow to use all of the space.

TIP Do not autoextend temporary and undo tablespaces, since they will quickly grow to use all of the space to which they can autoextend.

What do you do if you run out of space in a datafile? Just enter OEM, click the datafile, and choose the Storage tab. Once there, you can change the autoextend feature and enter the size of the extensions that you would like. Do not forget to limit the size of the datafile so that it does not grow to use all of your space. After you’ve completed this, click Apply and you’re done. If you select the Show SQL button, you can see the alter database syntax, which is also shown next.

alter database datafile ‘/u01/oradata/ora10g/example01.dbf’
autoextend on next 50M maxsize 5000M;


Figure 3-8. Tablespace view in Enterprise Manager

You can write your own scripts to compare the amount of allocated space for a datafile in view dba_data_files to the amount of free space, as shown in view dba_free_space.

OEM also provides you with a more detailed map of how space is used. In OEM, select a tablespace and then navigate from Tools to Tuning Features, finally choosing Tablespace Map. This opens a graphical layout showing each segment in the tablespace. From the tablespace map, you can choose the Tablespace Analysis Report tab for a written report on the space being used.

Managing the database objects discussed earlier will be a large part of your role as a DBA. In the next section, let’s take a look at setting up and managing users. After all, without database users, there is no point in doing any of this! 

Progress Check: 

  1. What’s better: “shutdown transactional” or “shutdown immediate”?

  2. Do you only need to worry about logical schema objects that do not take up a large amount of space?

  3. What happens if your archive log directory becomes full?

  4. Why would you want to use a command-line interface rather than a GUI to perform your tasks as a DBA?

Progress Check Answers: 

  1. Both leave your database in a consistent state. It depends on how long your transactions will take to complete or roll back. If all things are equal and you think that it will take as long to commit the transactions that are already running, then you should use “shutdown transactional” since commits will be allowed to complete and no data will be lost.

  2. Logical schema objects need to be watched to ensure they are in a valid state.

  3. If database attempts to write an archive log after the directory has become full, the database activity will be suspended until sufficient space is made available for the new file.

  4. You may want to place the command in a script that is scheduled or run as a repetitive task.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Manager Users}

CRITICAL SKILL 3.9 —  Manage Users

Before you can do anything in Oracle, you need to have a user ID created to enable you to log in to Oracle. As a DBA, you will begin with the SYS or SYSTEM accounts since these accounts both have the DBA role and exist in all Oracle databases. They are often used to perform database administration tasks. The SYS account is also granted the sysdba privilege and is the schema that the Oracle catalog is stored in. You should only use the SYS account when you need to perform a task as SYS or need the sysdba privilege. If your database was created using the Database Configuration Assistant (dbca), then you will also automatically get the SYSMAN and DBSNMP accounts. SYSMAN is used to administer Oracle Enterprise Manager (OEM) and DBSNMP is used by the agent that OEM employs to monitor Oracle databases. Several other accounts will also be set up for the “example” schemas, such as the Sales History (‘SH’) user that we will utilize throughout this book. The OUTLN schema will be created to allow you to use plan stability through the stored outline feature. Depending on the options you choose when creating your database, other accounts may be set up for you. For example, if you install the OLAP option, the OLAPSYS account will be created.

Create a User

When you create a user, you can either use the create user syntax or the OEM, which is an easier approach. In order to create a user, you will need to decide the following things:

  • The default tablespace where segments created by this user will be placed unless a tablespace name is used in the DDL to override this.
  • Whether to expire the password so that the user needs to change it the first time they log in to Oracle.
  • A temporary tablespace to store internal data used by Oracle while queries are running. Sort operations make use of the temporary tablespace if there is not enough room in the SGA to perform the sort operation.
  • Whether to employ user quotas on tablespaces, which put a limit on the amount of space that a user’s objects can take up in that tablespace.
  • The authentication type, which allows you to specify whether you want Oracle to force the user to specify a password when they log in, or you can trust the operating system to do this for you.
  • The initial password that the user will log in with. The user must change this during the first log in if you chose to expire the password in advance of the user’s first logon.
  • Privileges to grant to the user. These can be direct privileges or roles. We will discuss them in the next section.
  • A profile for the user, which can be employed to manage the user’s session by limiting resources that sessions can use, and that help implement corporate password policies. We will also see this in the next section.
  • Whether to lock or unlock the user.

The OEM console in Figure 3-9 shows the options available to you to create and edit a user. For each user, there are eight separate tabs that allow you to easily enter a user’s information. You can see the SQL that will be generated by selecting the Show Sql button at the bottom of the panel. Another great option allows you to model a user and create another user like one that already exists. To do this, click the user that you want to model, select Object from the top of the panel, and then select the Create Like option.

Here is a sample CREATE USER statement:

CREATE USER “NEWUSER” PROFILE “DEFAULT” IDENTIFIED BY “newpassword”
PASSWORD EXPIRE DEFAULT TABLESPACE “USERS” TEMPORARY TABLESPACE “TEMP”
QUOTA UNLIMITED ON TEMP QUOTA UNLIMITED ON USERS
ACCOUNT UNLOCK;
GRANT “CONNECT” TO “NEWUSER”;


Figure 3-9. User Management view

Edit Users

Once a user has been created, you will be asked at different times to edit users to change quotas or reset passwords or unlock an account. This can be easily performed through OEM by selecting the User, choosing the option you want to change through the Gui, and then applying the change.

Editing users can also be performed using the ALTER USER statement, as shown next, where a user account is unlocked, the password is changed, and a tablespace quota is increased.

ALTER USER “username” IDENTIFIED BY “newpwd ” QUOTA UNLIMITED ON TOOLS ACCOUNT UNLOCK;

We’ve now created a user and it’s time to grant them some privileges. Let’s see how we do this in the next section.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Manage Privileges for Database Users}

CRITICAL SKILL 3.10 — Manage Privileges for Database Users

Creating a user in Oracle has accomplished the first part of user setup and that is authentication. We have a user ID and password and have authorized this user to use an Oracle database. Once the user logs in, however, they will not be able to do very much because they will not have privileges that allow them to access any objects. This leads us to the second step of setting up a user: authorization. In order to authorize a user to perform their tasks, we need to grant access.

Grant Authority

You now need to give permission to the user to do things in Oracle. Actions like accessing a table or executing a procedure or running a utility require you to “grant” the authority to that user. When you perform a grant, you can specify four things:

  • The user that is being granted the authority.
  • The object that is being granted. Examples of these are a table, procedure, or role.
  • The type of access being granted, such as select, insert, update, or delete on a table, or execute on a procedure, function, or package.
  • Whether this user has authority to then grant the same authority to other users. By default, they do not, but this can be added by using the With Grant option.

Here are two examples that grant a user “NEWUSER” access to a table and then to a package.

GRANT SELECT ON “TABLE_NAME” TO “NEWUSER” WITH GRANT OPTION;
GRANT INSERT ON “TABLE_NAME” TO “NEWUSER” WITH GRANT OPTION;
GRANT EXECUTE ON “PROCEDURE_NAME” TO “NEWUSER”

Types of Grants

There are two types of grants that can be given to a user: system privileges and object privileges.

  • System privileges are predefined Oracle privileges granting authority to overall system objects rather than individual ones. The ability to perform a create tablespace, alter system, and back up any table are just a few examples of some system-level privileges that can be granted to a user.

  • Object privileges are a lower-level authority where a named object is granted to a user. So, the ability to perform an operation on a particular table, or execute an individual function, package, or procedure are object privileges as opposed to the ability to execute any procedure or select any table, which are system-level privileges.
Take Away Authority

What is given can be taken away. In order to take privileges away from a user, we use the REVOKE command and the syntax is very similar to the syntax we use when issuing a grant. Here are two examples of a REVOKE operation:

REVOKE INSERT ON “TABLE_NAME” FROM “NEWUSER”;
REVOKE EXECUTE ON “TABLE_NAME” FROM “NEWUSER”;

Roles

When you think of the number of privileges that need to be managed in situations where you have thousands of database objects as well as thousands of users, you quickly realize that it would be nice to organize the privileges into groups that can be easily managed. This is where roles come into play.

A “role” is used to group privileges together into a predefined group that can be granted to users. So, rather than granting object and system privileges individually to every user in your system, you can grant them to a role, which in turn is granted to the user.

Oracle-Defined Roles

Some special roles are created by Oracle through the install process or by running Oracle-supplied scripts. The DBA, Connect, Resource, Imp_Full_Database, and Select_Catalog_Role are some examples of roles that are supplied by Oracle and should not be changed.

Create and Grant a Role Roles are created using the create statement in the same manner as creating users. We can also revoke privileges from roles and drop roles when they are no longer needed. Roles can also be granted to other roles. You can see an example of this next where the Oracle role CONNECT is granted to the newly created role TESTROLE, along with a system and object privilege.

CREATE ROLE “TESTROLE”;
GRANT CONNECT TO “TESTROLE”
GRANT EXECUTE ANY PROCEDURE TO “TESTROLE”
GRANT SELECT ON “table_name” TO “TESTROLE”

The new role can then be granted to a user as shown next, where “testrole” is granted to user “Testuser.”

Grant “testrole” to “Testuser”;

The “TESTROLE” is then dropped since it is no longer required.

DROP ROLE “TESTROLE”;

Now that we’ve created users and roles, we can fine-tune our management of these by implementing some user policies through profiles,which we will explore next.

Profiles

A profile can be used to implement a password management policy, as well as limit resources for a user. When you created the user NEWUSER earlier, a password was supplied along with the DEFAULT profile. Using this DEFAULT profile, the user never needs to change their password and there are no limits placed on any system resources. You can create new profiles to implement your corporate password policies in Oracle. For example, you can specify the number of days after which a user must change their password. You can also establish a policy where a password cannot be reused within a certain period of time and must contain a certain number of changes. A function can be used to ensure that a complex password is created by the user. For example, you may require that a password be more than eight characters long, use alpha, numeric, and special characters, and that it does not repeat a character more than twice. This can all be implemented in a function. An account can be locked after a specified number of login attempts and can remain locked for the number of days defined in the profile.

System limits for a user can also be implemented by a profile. These include limiting system resources such as those for CPU, connect, and idle time as well as the number of sessions employed by the user, limits on reads, and the SGA used. You should note, however, that the Database Resource Manager is the preferred way to limit resources and that you should use profiles to manage passwords.

The following is an example of the creation of a new policy that will lock an account after three failed login attempts and will keep the account locked indefinitely. The password needs to be changed every 60 days and the new password will be verified by your custom function COMPLEX_PASSWORD. The old password cannot be reused for 120 days.

CREATE PROFILE “NEWPOLICY”
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 120
PASSWORD_VERIFY_FUNCTION COMPLEX_PASSWORD

Now, let’s grant this policy to user NEWUSER:

ALTER USER NEWUSER PROFILE NEWPOLICY;

In Conclusion

As you have seen in this chapter, there is a great deal that a DBA needs to be aware of to properly manage a database. The good news is that you will have tools such as OEM to help you. Do your best to keep your environment as simple as you possibly can! You will be glad that you did as your overall database environment continues to grow.

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

{mospagebreak title=Project 3-1: Creating Essential Objects}

Project 3-1 Creating Essential Objects

This project will walk you through the creation of the essential storage and schema objects after a database has been created, which in this project will be called ora10g. You will create a new tablespace called NEW_TS and will than add a user NEW_USER who will be given the authority to this tablespace. You will then create a role called NEW_ROLE and grant privileges to it. Afterward, you’ll grant this role to the new user. A table and index will be created on this tablespace by the new user. Lastly, you will resize the undo tablespace to make it larger. You will see how to do this in OEM and the generated SQL will also be shown to you so you can do this in SQL*Plus.

Step by Step
  1. You have been asked to create a new used named NEW_USER who will need to create objects in a new tablespace called NEW_TS that should be sized at 5MB. Your first step will be to create the tablespace. In OEM, log in as user SYSTEM, go to database ora10g, choose storage, then choose tablespace and select an existing tablespace to model. Under Objects in the toolbar, select the Create Like option to model your new tablespace after the existing one. Enter the new tablespace name, datafile name, and all properties including the size. Make this a locally managed tablespace 5MB in size with uniform extents 96KB in size. If you choose the Show Sql button, you will see the generated SQL. It should look something like the following SQL. You can either apply the change in OEM or you can copy and paste the generated SQL and run it in SQL*Plus.

    CREATE TABLESPACE “NEW_TS” LOGGING
    DATAFILE ‘C:ORACLEORA10ORA10GNEW_TS1.ora’ SIZE 2M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 96K SEGMENT SPACE MANAGEMENT  AUTO;

  2. Now you will create NEW_USER. As with the preceding tablespace creation, you can model an existing user. In OEM, go to Security and then to User, choose an existing user to model, and select Object from the toolbar. Once again, use the Create Like feature. The user should now have a password of new_password, which will be unlocked. Set the default tablespace to NEW_TS.

    CREATE USER “NEW_USER” PROFILE “DEFAULT” IDENTIFIED BY “new_password” PASSWORD
    EXPIRE DEFAULT TABLESPACE “NEW_TS”
    TEMPORARY TABLESPACE “TEMP” QUOTA UNLIMITED ON “TEMP”;

  3. Create a role called NEW_ROLE. In OEM, go to security, and then choose Role. Under Object in the toolbar, select Create and enter the role name.

    CREATE ROLE “NEW_ROLE” NOT IDENTIFIED;

  4. Grant the CREATE TABLE system privilege, the OLAP_USER role, and the object privilege SELECT on table SQLPLUS_PRODUCT_PROFILE to NEW_ROLE. In OEM, go to role and choose NEW_ROLE. Use the tabs System, Object, and Role to choose the objects listed here. Click the Apply button to make the changes. The generated SQL will look like the three grants listed next.

    GRANT CREATE TABLE TO “NEW_ROLE”;
    GRANT SELECT ON “SYSTEM”.”SQLPLUS_PRODUCT_PROFILE” TO “NEW_ROLE”;
    GRANT “OLAP_USER” TO “NEW_ROLE”;

  5. Grant NEW_ROLE and connect to NEW_USER. Also, give NEW_USER an unlimited quota on NEW_TS to allow for objects to be created in the tablespace. In OEM, navigate to Users and choose NEW_USER. Once there, choose the Role tab and select NEW_ROLE, and then select the down arrow. Click the Apply button to make the change.

    GRANT “NEW_ROLE” TO “NEW_USER”;
    ALTER USER “NEW_USER” DEFAULT ROLE ALL;
    ALTER USER “NEW_USER” QUOTA UNLIMITED ON “NEW_TS”;

  6. You will now log into the database as NEW_USER and can use OEM with the NEW_USER account. Once in OEM, you will create a table called NEW_TABLE with columns col01 as number(15) and col02 as varchar2(30). In OEM, in the toolbar, select Object and under that choose Create, and then choose Table. Make sure the table is created in NEW_TS. Follow the screens to add col01 and col02. You will then create a primary key called NEW_TABLE_PK using col01. Follow the screens and choose the options you would like. We recommend you name any keys and constraints rather than relying on system defaults. Choose Finish and you have created a new table with a primary key!

    CREATE TABLE “NEW_USER”.”NEW_TABLE”
    (“COL01″ NUMBER(15) NOT NULL,
     “COL02″ VARCHAR2(30) NOT NULL,
    CONSTRAINT “NEW_TABLE_PK” PRIMARY KEY(“COL01″),
    CONSTRAINT “NEW_TABLE_U1″ UNIQUE(“COL01″))
    TABLESPACE “NEW_TS”;

  7. You now have one last task: resizing the undo tablespace to add 100MB to it. Log in to OEM as user System and choose the datafile under the undo tablespace. Enter the new size and click Apply. It’s as easy as that. The SQL to increase this from 50MB to 150MB is shown here:

    ALTER DATABASE DATAFILE’/u01/oradata/ORA10G/
    UNDOTBS01.DBF’ RESIZE 150M;

Project Summary

This project has taken you through the basic steps of creating an environment for a new user, including using roles and granting privileges. You’ve seen how to manage users as well as space and have even created objects. Armed with these capabilities, you are now on your way to being a productive DBA. Congratulations!

Chapter 3 Mastery Check
  1. What is the benefit of a role?

  2. Should a table that is in tens or hundreds of extents be reorged?

  3. What is the preferred method for collecting object statistics?

  4. What is a segment?

  5. What is an extent?

  6. Name two reasons for implementing an index.

  7. How can you place a database in maintenance mode without first shutting it down?

  8. How can we limit the resources that a particular user can consume and how does this work?

  9. When managing undo segments, what are the things that you need to think about?

  10. What is likely to happen if you turn on the autoextend property for undo and temporary tablespaces with a maxsize set to unlimited?

  11. What is special about the SYS user account and how does it differ from SYSTEM?

  12. What are temporary tablespaces used for?

  13. What are the two aspects of security that are covered in Oracle’s implementation?

  14. Name and describe the types of privileges that can be granted to a user.

  15. How would you implement your corporate password policy in Oracle?

This chapter is from Oracle Database 10g: A Beginner’s Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan