In its simplest hardware deployment, the database can be implemented on a single-processor server, as shown in Figure 3.1. This configuration may be very viable for some small data warehouses or data marts and can provide valuable benefits and should not be dismissed. However, this configuration is obviously not going to run a large data warehouse and support the demands of a large user population requiring a quick response time to their queries.

Figure 3.1. A Single-Processor Server

This architecture has a number of inherent problems, not least of which is the risk to the data with this deployment, because it is dependent upon a single disk with no built-in redundancy. In addition, because there is only one CPU, there is no facility for true parallelism in the warehouse, where more than one process is actually executing concurrently. There is a single CPU running a single process at a time, pulling data from one disk through one I/O channel from disk to server. Adding more disks does not necessarily enable any improvement if the queries that are running at peak times are already saturating that I/O channel. Therefore, we need to see how we can improve the delivery of the data from the disks.

Although the single-disk architecture will suffice for a very small data warehouse, it is obviously totally unsuitable for a typical data warehouse with very large volumes of data. Now we need to look at a configuration that uses multiple disks and that uses a bigger capacity I/O channel between our disks and our server in order to transfer more data. An example of this is shown in Figure 3.2, where multiple separate controllers are used to interface with our multiple disks. The advantage of this approach is that more disks can be used to provide the data to answer the users’ queries and can provide a bigger I/O channel, which enables more data to be communicated between the disks and the server at the same time. There are a number of technologies that can connect multiple disks to our server, from SCSI and Ethernet to Fibre Channel, and there are different architectures, such as storage area networks and network attached storage, for utilizing this connectivity. We will look at these in more detail later in this chapter.

Figure 3.2. A Single-Processor, Multidisk Architecture

We also need to consider carefully how we use our multiple disks. For example, where do we put the data files for each of our tablespaces? Traditionally, the DBA, who has a number of separate disks, will try to split the tablespaces onto different disks so that the I/O requirements don’t interfere with each other. A typical approach used by many DBAs is to place index tablespaces and data tablespaces on separate disks. In this way the I/O load is spread over multiple disks, improving performance.

However, even with careful placement of tablespaces on disks to avoid heavy loaded disks, we can still get hot spots, where certain disks are always heavily in demand and other disks are rarely used; what we really want is to smooth the I/O out across all of the disks. The sheer size of a typical data warehouse will necessitate a large number of disks, resulting in this type of manual placement quickly becoming an impractical and demanding chore that is prone to errors.

A solution to these problems is to use some form of RAID. This will transparently split our data files across multiple disks, improve response times, and also provide a higher level of protection against disk failure. In Oracle Database 10g, an important feature called Automatic Storage Management (ASM) is introduced and can be thought of as RAID within the database. We will look at RAID and ASM in more detail later in this chapter.

Simply adding more disks is not sufficient for our large data warehouse if we only have a single processor handling all of the user workload. We may have multiple user queries being executed at the same time, but this parallelism is actually an illusion if we have only a single processor, because the processor can only really execute one process at a time. It provides the illusion of parallelism by swapping between, and executing small increments of, the processes in very quick succession. Adding more processors to the server enables Oracle to actually run more of the operations truly in parallel. This can be executing Oracle specific processes, running multiple separate users’ queries or even just a single query that the database has transparently split among multiple processing threads operating on different parts of the warehouse data. Adding more disks and improving the I/O channels between the disks and server improves the ability of these multiple processing threads to concurrently access more data.

The server architecture, which uses multiple CPUs that are able to address a common memory structure, is called a symmetrical multiprocessor (SMP) architecture. The operating system runs across all CPUs concurrently and schedules and load balances its separate processes on all of its CPUs. A normal dual processor PC is an example of an SMP architecture server. Figure 3.3 shows a four-processor SMP system, where each processor accesses the same system memory via the system bus.

Figure 3.3. A Multiprocessor, Multidisk Architecture

Let’s look at an example of why multiple processors are required. The Oracle database can run a query by transparently splitting it into a number of cooperating parallel processes, where each operate on a different portion of the warehouse data. Executing these processes in parallel needs multiple processors if any speedup is to be achieved. Adding more disks and improving the I/O channels between the disks and server further improves the ability of these multiple processing threads to concurrently access more data. In addition, this parallelism in the database can be better utilized by physical database structures such as table partitions. However, the improved ability to use parallelism can only be realized if it is supported by the underlying hardware: multiple processors for the processing capability and handling the multiple process threads, more memory to support the multiple parallel processes, and multiple disks to maximize the available I/O and deliver the data in parallel to those parallel processes.

All of our server architectures so far have been SMP architectures that are limited in their ability to scale by the internal system bus, which must handle all of the data traffic and which also limits the number of processors because of the contention and demand that they place upon it. It is unusual to see SMP servers having greater than 16 to 32 processors without necessitating a more sophisticated, and therefore more expensive, internal system bus design.

Another architecture to consider is clustering, which involves multiple independent servers that work together to perform a common set of functions and may appear to client users and applications as a single server. The servers are physically connected by a network. We can identify two groups:

In Figure 3.4, we have shown a simple two-node cluster that is accessing a common disk bus. We have shown our cluster nodes as having only two processors, but, of course, there is nothing preventing them from having more, as previously discussed. There are a number of technologies to share and connect external disks to our servers, and we will look at some of these in more detail later in this chapter.

Figure 3.4. A Two-Node Cluster Using Shared External Disks

Clustering provides the benefits of:

Another alternative architecture is the massively parallel processor (MPP) architecture, where separate nodes or servers, each consisting of their own CPUs, memory, and which may or may not have their own disk, are connected to each other by a high-speed interconnect. Each node runs its own operating system, and the application typically coordinates its processing across the nodes in an MPP architecture utilizing the inter-node connectivity. Figure 3.5 shows just seven nodes in an MPP architecture using a single common interconnect, but large systems can have hundreds of nodes, which can be interconnected in a variety of different topologies.

Figure 3.5. A Massively Parallel Architecture

An Oracle database is the set of database files that comprise the data warehouse, including the data files, control files, and redo log files. An Oracle instance, as shown in Figure 3.6, consists of the memory area (known as the System Global Area or SGA) and background processes—for example, SMON, PMON, and ARCH. The background processes access data files and manage user sessions. Background processes are shared by all database users.

Figure 3.6. The Oracle SGA and Background Processes

The background processes each perform a specific set of tasks for the database. For example:

This is only a partial list. There are quite a number of Oracle processes, and each has its own area of responsibility and interacts with its own part of the Oracle database. For example, only the DBWn processes are responsible for writing out to the data files. A diagram of a single instance on a server and an overview of the background processes is shown in Figure 3.6.

When an Oracle instance is started, the database initialization parameter file is read and the SGA memory area is allocated from the server’s main memory and configured, the background processes are started, and the database files are opened. The SGA is where all the important information in the database is held when the instance is running. It contains the buffer and dictionary caches used internally by Oracle and the redo logs. However, it contains more than just data; it also contains the memory regions for the shared pool for SQL and PL/SQL and the Java pool for Java objects.

When one database and instance are running on a single server, even on a very powerful multiprocessor server with a lot of memory, performance is still inherently limited by the server itself and by the number of processors and memory that it can contain. In order to scale our database beyond one server, we need to be able to cluster the database using Oracle Database 10g RAC.

Clustering the Oracle database requires more than one Oracle instance to access the same database data files and therefore uses the shared disk type of server cluster we identified previously. A clustered Oracle database offers a number of benefits:

The simplest example of this is shown in Figure 3.7, with two servers, each with their own CPUs, running their own database instance but both instances accessing the same database files stored on disks shared between the servers.

Figure 3.7. Overview of a two-node Oracle RAC Architecture

RAC was introduced in Oracle 9i as a replacement technology for Oracle Parallel Server. A key component of the Oracle RAC architecture is Cache Fusion. This uses a dedicated, high-speed interconnect to coordinate nodes in a RAC cluster and to keep data in each node’s memory synchronized. Cache Fusion effectively enables the individual caches from the separate instances to act as a single cache.

Cache Fusion significantly improves the ability for clustered database instances to scale. Prior to Cache Fusion, one of the major problems involved with more than one Oracle instance accessing the same database files concerned the interinstance communication, such as for passing Oracle data blocks between nodes. For example, if the same disk block were being accessed and updated by two separate database instances on different servers at the same time, then, in order for the second instance to access the block, it had to be transferred, via disk, from the first instance. Block pinging (as this was known) could occur repeatedly, since blocks were frequently written to disk in order to transfer them between instances and disk accesses are relatively slow in a database. To solve this without Cache Fusion necessitated clever partitioning of the data, often by splitting the user community between the two instances by the data that they were accessing. Partitioning in this fashion was not often a realistic possibility, was time consuming, and required skilled planning.

Cache Fusion, with its dedicated high-speed communication link, improves the communication between the instances. A number of technologies can be used for the interconnect. For example, the simplest, but still effective, one is a high-speed gigabit, or 10G Ethernet. If Ethernet is used then the Cache Fusion network should be dedicated to just the interconnect traffic and kept separate from the Ethernet network used for the normal LAN network to connect the users to the servers. There are also other new technologies, such as Infiniband, that offer a low latency and high speed interconnectivity. This improved, faster communication of the blocks between the instances in a RAC cluster removes the need for the slower block communication via the disks and therefore almost always removes the need for the careful partitioning of the data. This enables real applications, i.e., normal third-party applications and not just highly tuned and balanced ones, to be deployed on RAC and to fully use the benefit of the scalable architecture.

To scale the RAC architecture involves the addition of a new server and Oracle RAC instance into an existing cluster. This makes available a new set of CPU and memory resources to be utilized by the cluster. As new nodes are added, then the RAC cluster exhibits very good scalability and queries are transparently run across all of the nodes in the cluster to utilize any free resource on other nodes.

An additional benefit to our warehouse architecture from Oracle RAC is in the improved reliability of the whole environment. With RAC, instead of a single instance on a single server accessing our database, we have multiple instances on multiple servers. So, if one server fails, we still have the other servers in the RAC cluster that are able to pick up and continue the failed database sessions. For a production system that cannot afford downtime due to server failures, this is a very attractive and important feature.

With Oracle Database 10g, Oracle has based its Database Grid upon the RAC architecture concept and has rearchitected many of its components as a consequence.

Declining hardware costs, leading to the increased availability of powerful, lower-cost servers, coupled with the easier availability and access within the Enterprise architecture to shared storage, make the appearance and use of interacting servers and clusters cheaper and therefore more likely. This means that isolated servers, with their isolated pools of storage and with their own application systems, can be consolidated into a lower-cost cluster offering better performance and reliability. This has evolved into the grid concept in Oracle Database 10g.

But plugging many servers and shared storage together to create a large cluster does not automatically provide you with the computing power that you need or use the computing resources to their best effect. The ability to harness and manage the power of this grid of multiple servers and storage comes from the sophistication of the management software to specifically address the issues that this type of environment presents. Oracle Database 10g Grid is a new, integrated software infrastructure that enables the improved management and use of resources provided by a grid of servers and storage.

Previously, we have been administering our database using Enterprise Manager Database Control, but the new Oracle Enterprise Manager Grid Control administers and manages multiple database components in the grid as well as Oracle Application Server 10g components. The 10g Grid management by Enterprise Manager provides new features, such as:

We will examine Enterprise Manager in more detail in Chapter 11.

One of the main objectives with our I/O subsystem is to keep the processors in our server constantly supplied with data when processing queries. With data warehouses, this is much more relevant than with OLTP systems, because in a warehouse the queries are typically spanning a much larger set of the data, whereas in an OLTP the data access is generally more specific record oriented. This means that both the disk subsystem and the I/O subsystem must have sufficient bandwidth to transport the data to the warehouse quickly enough to support the processing needs. This requirement is made more severe in a parallel environment, where multiple processes are accessing data at the same time.

The other factor that must be considered for the I/O subsystem is the latency of the technology used to connect the disks to the server. Latency is the time it takes a packet of information to travel from the disks to the server. Latency and bandwidth together define the speed and capacity of the interconnectivity. In addition to this, there is the latency of the disk itself which is the time the disk takes to position the disk head over the required data block on disk before the data can actually be read.

When considering the number of disks for a warehouse, you must take into account both the capacity and the maximum transfer rate. If we were to base our disks for our warehouse purely on the disk capacity and go for a small number of large disks, then we are inherently limiting the ability of the disks to provide the data. Small numbers of disks may have sufficient storage capacity but are unlikely to have sufficient I/O capacity to meet our needs. For example, if we chose four disks, each with a 320Mbps transfer rate, then the maximum data that can be transferred even if everything is working ideally (which is probably not the case), is (4 * 320)Mbps. Alternatively, if we select a larger number of smaller sized disks, then we have more disks—each able to run up to their own maximum data transfer limit to provide the data to the servers.

Following this simple rule, we should, therefore, be looking at using a larger number of smaller disks that can deliver the data that we want “in parallel.” However, the I/O subsystem should not be considered solely on the basis of the ability of the disks to provide data: the capability of the communication channel (the data bus and controllers) between the disks and the servers and the I/O channels at the server end are equally as important. One rule of thumb often seen is that there should be at least two disks per processor. Considering that the size of a warehouse will typically be very large, we will probably be using many times that multiplier.

The capability to use disks in this fashion in parallel comes from the ability to stripe our data across many disks and for that we need to have a brief look at RAID to understand how to use RAID and striping in our data warehouse.

Striping, or RAID, is the ability to spread the I/O requests across multiple disks in parallel. RAID is an acronym, which stands for Redundant Arrays of Inexpensive Disks, and the basic concept of RAID is to combine small, inexpensive disks to yield improved performance and reliability and have them appear to the server as a single, larger disk. We touched upon this problem earlier in the chapter when we looked at adding more disks into our warehouse architecture and where RAID provides a solution for better throughput and reliability by using multiple disks.

RAID can be described as levels, which progress from 0 to 10. As you progress through the levels, you get different performance and reliability. For example, rather than storing all of the data on one file on a single disk, the data is striped across multiple disks, which yields better performance during reading, because all of the disks potentially get read in parallel rather than just one.

Striping is the process where the disks are split into separate sections and the sections are utilized round-robin style on each disk. So, for example, if your stripe size is 1M, then the first 1M of your file is written to disk 1, the second 1M stripe is written to disk 2, and so on. This splitting of the data into stripes that are written to different disks is done either at the byte level or the block level and is performed automatically and completely transparently to the applications, whether it is a file for a word processor or a sophisticated database. Striping has the effect of reducing the contention for disk areas and improving I/O throughput to the server. Figure 3.8 shows a four disk RAID, where the stripe width is four and stripe size is the size of the individual amounts of data written to each disk.

Figure 3.8. Four-way RAID Showing Stripe Size and Width

The process of mirroring the data across multiple disks involves duplicating the information that we write to one disk by writing a complete copy to a second disk so we always have a copy of our data.

The RAID levels are:

RAID can be implemented either in hardware, via a RAID controller for the disks, or in software, via a tool called a logical volume manager(LVM). An LVM is a piece of software that could be provided either as part of the operating system, from a third-party vendor (e.g., Veritas), or from the disk storage vendor (e.g., from EMC). The LVM combines areas on separate disks and makes them appear as a single unit to the operating system, and in the process it provides software striping and mirroring. Performing RAID in hardware may be easier, but there may be the disadvantage that it can be more difficult to change at a later stage—for example, if more disks are to be added; performing RAID in software can take advantage of a number of powerful volume managers available. Regardless of the mechanism, the I/O request from the server is transparently split by the controller or LVM into separate I/Os for the individual disks.

From a performance perspective, the DBA and systems administrator must carefully consider not just the type of RAID that they wish to employ and on which disks, but they must also consider items such as the RAID stripe size (i.e., the byte size of each stripe on the disks) and the number of disks in the stripe set.

A high-level perspective of the operations needed to use a volume manager to prepare the disks for Oracle can be seen in the following text. Different hardware vendors will have their own volume managers and there are third-party ones, and in addition, different operating systems may have a slightly different approach, so the steps listed here are intended to be very generic. The DBA and the system administrator must:

After much research on the storage configurations that are optimal to the Oracle database for both OLTP and warehouse configurations, Oracle recommends using the SAME (Stripe And Mirror Everything) method. This method involves all of the disks being mirrored and striped using a 1M stripe width and with all of the database files being placed on these disks.

Equally important, SAME is a very simple storage configuration concept to grasp and is independent of any third-party’s storage product; it provides significant benefits to our warehouse database.

Our choice of architecture selected for the data warehouse will influence the type of storage that is needed. If we are using RAC, then we need to be able to access the disks from more than one server. We can use technologies such as SCSI, a bus technology that can connect up to 16 external devices (i.e., disks in the case of our warehouse) with our warehouse server. SCSI comes in many forms and can offer transfer speeds up to 160 MBps (megabytes/ second); shared SCSI enables the bus to be shared between two servers. We will now have a brief look at two alternative solutions, Storage Area Networks (SAN) and Network Attached Storage (NAS), which offer better capacity, scalability, and robustness.

A Storage Area Network, or SAN, can be viewed as a dedicated high performance network to connect servers and storage. An example of this is a disk array accessed using a technology such as Fibre Channel to link the disk array to the different servers (though other connection media, such as optical fiber, can also be used).

Fibre Channel is a technology for transmitting data at a rate of Gbps (giga-bits per second) between the devices in our warehouse architecture (i.e., between servers and between servers and disks). In addition, Fibre Channel technology allows a physical separation in terms of kilometers, so this also enables a large physical separation of the hardware components for our warehouse, if required. It may help to think of the Fibre Channel (or other technology) as a specialist data bus between the servers and the shared disk storage devices. High-availability systems will use multiple Fibre Channel routes from the servers to the disks and use hubs and switches to minimize the chance of system failure if any part of the SAN fails.

An alternative shared storage technology is Network Attached Storage, or NAS, which can be viewed as a set of disks running from its own special server platform and accessed via a normal network to which it has been assigned its own network IP address. For our warehouse architecture we may prefer to keep the normal company LAN network traffic separate from the warehouse server to NAS traffic and therefore use a separate LAN network between our database servers and our NAS.

It can be seen that the SAN concept encapsulates NAS. The Ethernet-connected disk devices in a NAS architecture are just a very specific example of storage available on a dedicated network (which SAN is). NAS performance, however, will be limited by the capacity of the network to move the data between the NAS storage devices and the servers: typically this limitation will be readily overcome by the network communication technology used in a SAN. In addition, new protocols, such as i-SCSI, are much more performant than old-style NFS.

The use of SAN and NAS in the Oracle warehouse architecture separates the disk storage system from the servers. This also means that these technologies are not just for the single-server environment. NAS and SAN storage are attached to a network that can have multiple database servers attached to it; therefore, it provides the required shared storage for Oracle RAC.

An example RAC architecture with shared NAS storage is shown in Figure 3.9. Taking our simple RAC architecture one stage further, for the example, we’ll base the interconnects on Ethernet, add in the shared storage using NAS, and include two separate connections between the servers and the NAS storage, so if one fails, then the other is still present to enable the RAC to operate. Of course, the same redundancy could be introduced for the network providing the connection for the RAC Cache Fusion interconnect as well. The reason that three separate networks are used (i.e., the normal network to connect to the users, the RAC interconnect, and the NAS storage) is to ensure that the three different types of network traffic do not interfere with each other and degrade the overall system performance. Of course, our example is very simplified to demonstrate the point, and typical large warehouse production systems can be significantly more complex.

Figure 3.9. Overview of RAC and NAS Shared Storage

In this section, we have discussed the various hardware components of our architecture and how our storage can be configured and deployed. In the next section, we will see how a new storage feature in Oracle Database 10g can build upon the strengths of the RAID striping and mirroring.

In Oracle Database 10g, ASM is an attractive alternative to using the previously described Logical Volume Manager (LVM), so let’s see how easy ASM is to use to manage our disks. The first step is to ensure that the disks that you want to use are made known to Oracle as candidate ASM disks, and this is done by using the ASM Tool to stamp an ASM header on the disks. On Windows systems you will find this in the bin directory as asmtool.exe (the command-line version) or as asmtoolg.exe (the graphical version); on UNIX based systems you will find oracleasm in the /etc/init.d directory. Our examples are based on the Windows graphical version.

The ASM Tool marks the disks with a header, which identifies the disks as ASM disks: to do this the disks need to be configured with a single disk partition and without a file system. Figure 3.10 shows the initial ASM Tool screen, where we will begin by adding the header to our ASM disks.

Figure 3.10. The ASM Tool

Clicking the Next button displays a list of the disks with their different partitions, and what ASM Tool considers as potential candidates is shown in Figure 3.11.

Figure 3.11. ASM Tool Disk Selection

In Figure 3.11, we have four disks available, shown as Harddisk0 to Harddisk3, and ASM Tool has identified disks 2 and 3 (the last two in the list) which are actually two small SCSI disks and are the ones we want to use for ASM. Note that ASM Tool identifies and displays the file system on the partition, but it has marked three of them as candidates. Actually, on our system, this is a dual operating system machine and these partitions are Linux partitions, not ones we want to use for ASM. Generally, you must provide whole disks and not partitions of part of the disks to ASM, so you should not get this situation. In particular, any production system will probably not be a dual boot system. However, this example is used to make the point that you do need to be very aware of your disk and partition layout when you are using ASM.

Perform a multiple selection of both disks, click Next and you will see the screen in Figure 3.12, which shows the ASM stamp that will be applied to the disks. Note that the prefix stamp, DATA in our case, from the field in Figure 3.11, is incorporated into the ASM link name.

Figure 3.12. ASM Tool: Stamp Disks Confirmation Screen

If we were creating the database as an ASM database using DBCA or the Universal Installer (during Oracle installation), then we could take a different choice for the storage option of the file system selection that we made in Chapter 2 (Figure 2.8). Now, we will choose ASM, as shown in Figure 3.13.

Figure 3.13. Database Creation: ASM Storage Option

With ASM selected clicking the Next button displays the Backup choice screen as before, but after that we get a different screen, as is shown in Figure 3.14, which enables us to select the ASM disks that we stamped with the ASM Tool earlier. Because we are performing an actual installation at this point, we will need to have previously invoked ASM Tool directly from our installation disk and stamped our disks so that they are recognized as ASM disks. For Windows installations, ASMTool can be found on the installation disk in the ASM Tool subdirectory.

Figure 3.14. Configure Automatic Storage Management

Here we have selected the External redundancy option, so no mirroring will be performed and we can see and check the tick boxes for both of the disks that we previously stamped and that we want to be members of our new ASM disk group. The disk group that we are creating is named DATA, which we will also refer to later when we manage the disk group via SQL and create tablespaces in it.

When we click Next and continue with the installation and database creation, these two ASM disks will now be used to hold our database files.

The ASM disks and disk groups can also be created, deleted, and managed manually and to do this we need to be logged on to the ASM instance and not the normal warehouse instance. We will work through a simple example, but if you try to run these commands on a normal warehouse instance database, you will get Oracle error ORA-15000, saying, effectively, that it is the wrong command for the instance type. This is because there is a new database initialization parameter introduced with ASM called instance_type, which takes the value RDBMS for a database instance and the value ASM for an ASM instance. In our previous example, where we created a database using an ASM disk group, the ASM instance was created automatically for us as part of the database creation.

Before any ASM management tasks can be performed, you must first logon to the ASM instance. To logon to the ASM instance from the Windows operating system command line you, will need to reset your Oracle SID environment variable to that for the ASM instance. By default, this takes the value +ASM.

A disk group is created as follows:

C:> SET ORACLE_SID=+ASM
C:> sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Thu May 27 19:51:13
2004

Copywright (c) 1982, 2004, Oracle. All rights reserved.

SQL> connect sys/password as sysdba

SQL> ALTER SYSTEM SET asm_diskstring = '\.*:' ;

SQL> CREATE DISKGROUP data
           NORMAL REDUNDANCY DISK '\.H:', '\.I:' ;

Here, the disk group is being created with the name DATA with a normal redundancy, i.e. a single copy of the data will be maintained. The disk group is formed from the two disks which are visible on our Windows system as H and I.

The new ASM_DISKSTRING parameter controls and limits the disks that Oracle discovers at the ASM instance startup; it is normally placed in the ASM instance initialization parameter file. To illustrate its use, we have specifically set this parameter using a wildcard. The format of this string, as shown, is specific to the Windows platform and will be different for UNIX and other platforms. So the \. indicates the current server and the *: indicates any disk on the server

If we want to add a new disk into our disk group, then we issue the following statement, which adds disks J and K into the precreated disk group DATA.

SQL> ALTER DISKGROUP data ADD DISK '\.J:', '\.K:' ;

Then Oracle will automatically distribute our blocks and rebalance the load from the initial set of two disks, which we specified earlier when the database was created, to include the new disks. All the time, the database is kept open to the users.

Creating tablespaces is now simplified, because we just need to refer to the ASM disk group. For example, to create a tablespace on our new DATA disk group we issue the following. The + used in +DATA for the datafile name indicates that it is a disk group data file.

SQL> CREATE TABLESPACE easydw_asmtest DATAFILE '+DATA' SIZE 5M;

Furthermore, using ASM for your database doesn’t force the database to only use ASM. Oracle Database 10g enables a database to use both the standard file system method and the new ASM method at the same time. For example, previously we created our database as ASM during installation, but if we had created our database using the file system and subsequently decided that we wanted to start using ASM, then we would need to create the ASM instance manually. On Windows this is done using the ORADIM utility, which also creates the Windows service, and there are some new ORADIM parameters in Oracle Database 10g for ASM for specifying the ASM SID name and the ASM service name.

oradim -NEW -ASMSID +ASM -SYSPWD change_on_install
-PFILE c:oracleproduct10.1.0admin+ASMpfileinit+ASM.ora

The init.ora file for the ASM instance is much simpler, and, if the defaults for the parameters are used, it can be reduced to only the following parameters:

INSTANCE_TYPE = ASM
DB_UNIQUE_NAME = +ASM
ASM_DISKSTRING = '\.*:'
COMPATIBLE = 10.1.0.2.0
ASM_POWER_LIMIT = 1
USER_DUMP_DEST = 'c:oracleproduct10.1.0admin+ASMudump'
BACKGROUND_DUMP_DEST = 'c:oracleproduct10.1.0admin+ASMdump'
CORE_DUMP_DEST = 'c:oracleproduct10.1.0admin+ASMcdump'

Once the ASM instance is created, it is accessed and used in exactly the same way described previously and an ASM-based tablespace can be created alongside our file system tablespaces without any problems.

ASM is an important new feature, which is easy to administer once you understand its deployment and operation. Two other areas indicate other aspects of the administration to investigate for understanding ASM further:

We think that you will agree that ASM is very easy to administer from the command line, but, as we shall now show, it is even easier by using Enterprise Manager.

Now that we have a good understanding of how ASM operates and how it is controlled from the command line, we will have a look at how ASM is administered and controlled from Enterprise Manager.

To administer ASM, either Enterprise Manager Database Control version or Grid Control version can be used. We are going to use Grid Control for our ASM examples in this chapter, and in Chapter 11 we will discuss more about how EM Grid Control is deployed, started, and used. To complete this section on ASM we want to show how Enterprise Manager also supports ASM and the screens that complement what we have discussed so far.

From within EM Grid Control, you are able to drill down to view information on individual servers and then drill further to the Oracle software that is executing on the server. From the Hosts Target page within EM Grid Control, clicking the link that identifies an ASM instance will drill you down to the Home page (not shown) for that ASM instance, which displays the general status and alerts for the instance. From this page we can follow the Configuration link to display the screen shown in Figure 3.15, where you can see the parameters for the instance, some of which we have talked about earlier, and which can easily be set from this screen.

Figure 3.15. EM Grid Control ASM Instance Configuration

If the Administration link is followed, then we will see the screen shown in Figure 3.16, where the disk groups handled by this ASM instance are shown. Here we can see our DATA disk group, and we have expanded the pick list on the right to show the operations that can be performed on it. Mounting and dismounting a disk group makes it available or unavailable to the database instances running on that server.

Figure 3.16. EM Grid Control ASM Instance Administration

The third option involves the distribution of the file blocks across the disks in the group. When a disk is added or removed from the group, either intentionally or due to disk failure, ASM automatically redistributes, or rebalances, the file blocks. This Rebalance option enables you to manually perform a rebalance operation, though, because it is generally done automatically, you should rarely need to do this yourself.

By drilling down on the DATA disk group we see the screen in Figure 3.17.

Figure 3.17. EM Grid Control ASM Disk Group Members

This screen shows more information about the individual disks that make up the disk group, their path on the server, status, and their capacity and free space. It is a nice and comprehensive way to show you information at a glance about your disk group members.

The final screen in our quick tour of ASM via Enterprise Manager is that shown in Figure 3.18, which shows the hierarchical contents of the disk group. Here you can expand the hierarchy to see the databases using the disk group, their folders, and the files that they have created. This screen also shows that the ASM is as much about implementing a file system on the disk groups as it is about the mirroring and striping of the underlying disks.

Figure 3.18. EM Grid Control ASM Disk Group Files

You can see that Enterprise Manager supports the easy configuration of the ASM parameters, and its clear, logical screens allow you to traverse and examine the ASM information with ease. You can drill up and down the hierarchies of ASM information from the instance, to the disk group, to the disks, and to the actual database files in the file system on the disk group in order to fully appreciate the structure of your storage.

In this section, we have only touched upon ASM to demonstrate its power and ease of use. The important question is how is it used to help us in our data warehouse, and this is what we are going to look at in the next section.