We’ll be using the latest version of the database as of this writing—Oracle Database 11g Release 2 — and the corresponding version of OWB that (as of this release) is included with the database install. If you have that version of the database installed already, you can skip this section and move right on to the next. If not, then keep reading as we discuss the installation of the database software.
Downloading the Oracle software
We can download the Oracle database software from Oracle’s website, provided we adhere to their license agreement. This agreement basically says we agree to use the database and the accompanying software either for development of a prototype of our application or for our own learning purposes. If we proceed to use this application internally or make it commercially available, then we will need to purchase a license from Oracle. For the purpose of working through the contents of this topic to learn OWB, we need to install the database, which is covered under the license agreement for the free download.
We can find the database on the Oracle Technology Network website (http://www. oracle.com/technology). The main database download is usually the first download listed under TOP DOWNLOADS on the main page. We need to register on the site, in order to create an account, before it lets us download any files, but there is no charge for that. The download files are classified by the platform on which they can be executed, so we’ll choose the one for the system we’ll be hosting the database on. We’ll have to accept the license agreement first before the web page will let us download the files. The download files are anywhere from 1.7 GB to 2.3 GB in size, depending on the platform we’ll be hosting it on. So we do not want to attempt this download unless we have a Broadband Internet connection (that is, cable, DSL, and so on). We’ll download the install files and unzip them to a folder on a drive with enough available space. The installation files are temporary and are not needed after the installation is done, so we’ll be able to delete them to free up space if needed.
A word about hardware and operating systems
When installing software of this magnitude, we have to decide whether we’ll have to buy additional hardware and a different operating system to run the database and OWB. OWB will run in Oracle Database 10gR2 or later Standard or Enterprise Editions.
We’ll be using the most recent version of OWB throughout this topic. We can download older versions of OWB that will run on older versions of the database, but we will not have the benefit of the improvements as in the latest version of the software. Much of what we’ll be doing with the software throughout the course of the topic can also be done on previous versions of the software. However, due to the changes made to things such as the interface, it would be easiest to follow along using the most recent version.
For this topic, the platform is Windows 7 with Oracle Database 11g Release 2 (11.2.0.1) Enterprise Edition (which is the most recent version as of this writing), which is available from the download site. The Enterprise Edition of the database was chosen because it allows us to make full use of the features of the Warehouse Builder, especially in the area of dimensional modeling. There are some errors that will be generated by the client software when running in the Standard Edition installation due to code dependencies. These code dependencies are in libraries that are installed with the Enterprise Edition, but not the Standard Edition. We could use OWB with the Standard Edition, but then we would be limited in the type of objects we could deploy. For instance, dimensions and cubes would be problematic, and without using them we’d be missing out on a major functionality provided by the tool. If we want to develop any reasonably-sized data warehouse, the Enterprise Edition is the way to go.
Everything that we’ll work through in this topic was done in an Oracle VM VirtualBox virtual machine on a laptop personal computer with an Intel Core 2 processor running at 1.67 GHz and 4 GB of RAM. Oracle says 1 GB of RAM will suffice so the virtual machine was configured with 1209MB of memory. Minimum specifications usually result in underpowered systems for all but the very basic processing but for the purpose of working through the tasks described in this topic it will be sufficient. In terms of hard disk space, Oracle specifies that 4.5 GB is required for the basic database installation. We’ll need about 2 Gb just to save the installation files, so to make sure we have plenty of space, we should plan for something between 10 GB and 15 GB of available disk space just to be safe. We don’t want to install the database software and then find that we don’t have any space on our hard drive. The VirtualBox machine was configured with 30GB of disk space.
Oracle supports its database installed on Windows and Unix. For Windows, it supports Windows XP Professional or Windows Vista or Windows 7 (Business Edition, Enterprise Edition, or Ultimate Edition) as well as Windows Server 2003 and 2008. The system mentioned above that was used for writing this topic and working through all the examples, is running Windows Vista Home Premium Edition with Service Pack 2 and the database installed runs on the VirtualBox VM in Windows 7 Home Premium. We certainly would not want to use this configuration for large production databases, but it works fine for simple databases and learning purposes. The installation program will first do a prerequisite check of the computer and will flag any problems that it sees, such as not enough memory or an incorrect version of the operating system. For working through this topic on our own to learn about the Warehouse Builder, we should be OK as long as we are running XP ,Vista, or Windows 7. However, for business users who would be installing the Oracle Database and OWB for use at work using Windows, it would be a good idea to stick with the recommended configurations of Windows XP Professional, Windows Vista or 7 (Business Edition, Enterprise Edition, and Ultimate Edition), or Windows Server.
Server versus workstation
We don’t have to use a computer that is configured as a server to host the Oracle database. It will get installed on a regular workstation as long as the minimum system requirements are met. However, we might encounter a minor issue. A workstation is usually configured to use Dynamic Host Configuration Protocol (DHCP) to obtain its IP address. This means the address is not specified as a fixed address and can change the next time the system boots up. The Oracle database requires a fixed address to be assigned, and it can install on a system with DHCP. But it will also require the Microsoft Loopback Adapter to be installed as the primary network interface to provide that fixed address. If this situation is encountered, the installer prerequisite checks will alert us to that and give us instructions on how to proceed. It will not harm our existing network configuration to install that option. That is the way the laptop mentioned above was configured for this topic project.
Installing Oracle database software
So far we’ve decided what system we’re going to host the database on, downloaded the appropriate install file for that system, and unzipped the install files into a folder to begin the installation. We’ll navigate to that folder and run the setup.exe file located there. This will launch the Oracle Universal Installer program to begin the installation. Those of us with experience installing the Oracle Database from prior versions will immediately notice the installer for 11gR2 has a slightly different look. It is more like a setup program for the database than the Universal Installer we’re used to from previous versions, including the first edition of this topic.
We are installing the full database, which now automatically includes the Warehouse Builder client and database components. If we had an older version of the database (10g R2 for example) that did not include the Warehouse Builder software, or if we wanted to run the client on a different workstation than where the database software is installed, then there is the option to install the Warehouse Builder by itself.
A separately downloadable install for the standalone option is available at http://www.oracle.com/technology/software/products/ warehouse/index.html. Skip ahead to the section titled Installing the OWB standalone software if just the Warehouse Builder software is needed.
1. The first thing the installer is going to ask us is our email address for use in being notified of critical system updates that are available. This is something new Oracle has started doing with their installs to get people thinking about critical vulnerabilities and keeping their databases properly patched. It’s similar to Microsoft’s Windows Update feature that keeps users notified of available patches for the Windows operating system. That’s a good feature but we would need a support agreement with Oracle and a My Oracle Support login to really make use of it so we’re going to skip this and move on to step 2. Be aware that it will pop up a warning dialog asking us if we really want to remain uninformed about security related issues. We’ll answer yes and move along.
2. The second step asks us what installation option we’d like. We can choose from one of three, create and configure a database, install the software only, or upgrade an existing database. We’ll choose the second option. The Create and Install a Database option will make some assumptions about the database that we don’t want depending on what options are selected so its easier if we just install the database separately after the software is installed.
3. For the third step, we’ll choose Single Instance for the Installation Type. The other option is for installing a database as part of a RAC installation (Real Application Cluster) of clustered databases.
4. In step 4 we’ll choose the database language, or languages, we want to install.
5. Step 5 is where we choose the edition of the database to install, Enterprise, Standard, Standard Edition One, or Personal Edition. We’ll choose the Enterprise Edition so we have access to all of the advanced features we need for the Warehouse Builder.
6. Step 6 will ask us for path names for ORACLE_BASE and for the Software Location (or Oracle home location). They will have suggested paths filled in for us. It is a good idea to leave the path names as they are and only change the drive designation if we’d like to install to a different hard drive. The install program will suggest a drive for the installation, but we might have a different preference. Oracle recommends a convention for naming folders and files that they call the Optimal Flexible Architecture (OFA). This is described in topic B of the Oracle Database Installation Guide for Microsoft Windows, which can be found at the following URL: http:// download.oracle.com/docs/cd/E118 8 2_01/install.112/e10843/of a.htm#CBBEDHEB. It is a good idea to follow their recommendations for standardization so that others who have to work with the database files will know where to find them, and to save us from problems with possible conflicts with other Oracle products we may have installed. If we keep the default folder locations intact and only change the drive letter, we will adhere to the standard.
7. Step 7 of the install is where it will conduct the prerequisite checks to ensure our system is capable of running the database. If everything succeeds we’ll move right to step 8, bypassing step 7 results. We could hit the back button on Step to move back to see the results if we wanted. If anything failed, it would have displayed the results for us.
8. Step 8 is the summary screen. It will display the Global Settings as in the previous version but includes a new Inventory section which replaces the old Product Languages, Space Requirements, and New Installations sections.
9. The actual installation happens in step 9. A progress bar proceeds to the right as the installation happens and steps for Prepare, Copy Files, and Setup Files are checked off as they are done.
10. Step 10 is the conclusion and finishes up with a success message:
Your database configuration files have been installed in C:\app\ while other components selected for installation have been installed in C:\app\product\11.2.0\dbhome_1. Be cautious not to accidentally delete these configuration files.
1. We will click Close to end the installation.
Basic versus advance install
The installation method we’re following here is the quickest and easiest, but makes many decisions for us that more advanced options will ask us about like creating a database and Desktop vs Server installs. For the purpose of working through the examples in this topic, we will be OK with the basic installation. But if we were installing for a production environment, we would want to read through the Oracle Database Installation Guide (http://www.oracle. com/technology/documentation/database.html; click on View Library to view the documentation online or click on Download to download the documentation) to familiarize ourselves with the various situations that would require us to use the more advanced installation options. This would ensure that we don’t end up with a database installation that will not support our needs.
Location of install results
A good idea is to pay particular attention to the inventory location on the Step 8 summary screen, which tells us where we can find a log of the installation. The logs that the installer keeps are stored in the Oracle folder on the system drive in the following subfolder: C:\ Program Files\Oracle\Inventory\logs. The files are named with the following convention: install ActionsYYYY-MM-DD_HH-MI-SSPM where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, SS the seconds of the time the installation was performed, and PM is either AM or PM. The files will have a .log extension. This information may come in useful later to see just what products were installed. The folder also will contain any errors encountered during the installation in files with a file extension of .err and any output generated by the installer in files with a file extension of .out.
Now that the software is installed, it’s time to proceed with creating a database. But there is one step we have to do first—we need to configure the listener.
Configuring the listener
The listener is the utility that runs constantly in the background on the database server, listening for client connection requests to the database and handling them. It can be installed either before or after the creation of a database, but there is one option during the database creation that requires the listener to be configured—so we’ll configure it now, before we create the database.
Run Net Configuration Assistant to configure the listener. It is available under the Oracle menu on the Windows Start menu as shown in the following image:
The welcome screen will offer us four tasks that we can perform with this assistant. We’ll select the first one to configure the listener, as shown here:
The next screen will ask you what we want to do with the listener. The four options are as follows:
Only the Add option will be available since we are installing Oracle for the first time. The remainder of the options will be grayed out and will be unavailable for selection. If they are not, then there is a listener already configured and we can proceed to the next section—Creating the database.
For those of us installing for the first time on our machines, we need to proceed with the configuration. The next screen will ask us what we want to name the listener. It will have LISTENER entered by default and that’s a fine name, which states exactly what it is, so let’s leave it at that and proceed.
The next screen is the protocol selection screen. It will have TCP already selected for us, which is what most installations will require. This is the standard communications protocol in use on the Internet and in most local networks. Leave that selected and proceed to the next screen to select the port number to use. The default port number is 1521, which is standard for communicating with Oracle databases and is the one most familiar to anyone who has ever worked with an Oracle database. So, change it only if you want to annoy the Oracle people in your organization who have all memorized the default Oracle port of 1521.
To change or not change the default listener port
Putting aside the annoyance, the Oracle people might have to suffer as there are valid security reasons why we might want to change that port number. Since it is so common, the people accustomed to working with the Oracle database aren’t the only people who know that port number. Hackers looking to break into an Oracle database are going to go straight for that port number, so if we change it to something obscure, the database will be harder to find on the network for the people with malicious intent. If it does get changed, be sure to make a note of the assigned number.
There also may be firewall issues that allow only certain port numbers to be open through the firewall, which means communication on any of the other port numbers would be blocked. 1521 might be allowed by default since it is common for the Oracle database. It would be a good idea to check with the network support personnel to get their recommendation.
That is the last step. It will ask us if we want to configure another listener. Since we only need one, we’ll answer "no" and finish out the screens by clicking on the Finish button back on the main screen.
Creating the database
So far we have the Oracle software installed and a listener configured, but we have not created a database.
We will install a new database using Database Configuration Assistant, which Oracle provides to walk us step-by-step through the process of creating a database. It is launched from the Windows Start menu as shown in the following image:
Running this application may require patience as we have to wait for the application to load after it’s selected. Depending on the system it is running on, it can take over a minute to display, during which time there is no indication that anything is happening. It may be tempting to just select it again from the Start menu because it appears it didn’t work the first time, but don’t as that will just end up running two instances of the program. It will appear soon. The following are steps in the creation process:
1. The first step is to specify what action to take. Since we do not have a database created, we’ll select the Create a Database option in step 1. If there was a database already created, the options for configuring a database or deleting a database would be selectable. Templates can be managed with the Database Configuration Assistant application, which are files containing preset options for various database configurations. Pre-supplied templates are provided with the application, and the application has the ability to custom-build templates.
In previous versions of the database, Automatic Storage Management could be configured as well however as of 11gR2, ASM has its own configuration assistant now, ASMCA. It is Oracle’s feature for databases for automatically managing the layout and storage of database files on the system. These are both topics for a more advance topic on the Oracle Database. We will be creating a database using an existing template.
2. This step will offer the following three options for a database template to select:
° General Purpose or Transaction Processing ° Custom Database ° Data Warehouse
3. We are going to choose the Data Warehouse option for our purposes. If we already had a database installed that we wanted to use for learning OWB, but that’s not configured as a data warehouse, it’s not a problem. We can still run OWB hosted on it and create the data warehouse schema (database user and tables), which we’ll be creating as we proceed through the topic. This would be fine for learning purposes, but for production-ready data warehouses a database configured specifically as a data warehouse should be used.
4. This step of the database creation will ask for a database name. The name of the database must be one to eight characters in length. Any more than that will generate an error when trying to proceed to the next screen. This is an Oracle database limitation. The database name can also include the network domain name of the domain of the host it is running on, to further uniquely identify it. Follow the name with a period and then the domain, which itself can include additional periods.
If this database is being created for business use, a good naming scheme would reflect the purpose of the database. Since we’re creating this database for the data warehouse of ACME Toys and Gizmos Company, we’ll choose a name that reflects this—ACME for the company name and DW for data warehouse, resulting in a database name of ACMEDW. It is important to remember this name as it will be a part of any future connections to the database.
As the database name is typed in, the SID (or Oracle System Identifier) is automatically filled in to match it. If the domain is added to the database name, the SID will stop pre-populating after the first period is entered. The end result is that the SID becomes the same as the first part of the database name.
5. This step of the database creation process asks whether we want to configure Enterprise Manager. The box is checked by default and left as it is. This is a web-based utility Oracle provides for controlling a database, and as it is very useful to have, we will want to enable it. There are two options for controlling a database: registering with Grid Control or local management. Grid Control is Oracle’s centralized feature for controlling a grid, a network of loosely coupled modular hardware and software components that can be joined and rejoined together on demand to meet business needs. That is what the "g" in Oracle Database 11g stands for. If your network is not configured in a grid architecture, or you are installing on a standalone machine, then choose the local management option. It will automatically detect a Grid Control agent that is running locally, and if it doesn’t find one, the Grid Control option will be grayed out anyway. In that case, you will only be able to select local management.
6. New in the 11gR2 version of the DBCA is the additional tab on this screen for the Automatic Maintenance option. This step used to be all by itself as step 12 of the install process. We’ll deselect that option and move on, since we don’t need that additional functionality. Automatic Maintenance Tasks are tasks that run in predefined maintenance windows of time to perform various preconfigured maintenance operations on the database. Since the database for this topic is only for learning purposes, it is not critical that these maintenance tasks be done automatically.
7. Automatic maintenance is designed to run during preset maintenance windows, which are usually in the middle of the night. So if the database system is shut down every day, there wouldn’t be a good window to run the tasks on regularly anyway. If installing in a production environment with servers that will be running 24 hours a day every day, then consider setting up the automatic maintenance to occur. Oracle provides three pre-configured maintenance tasks to choose from—collecting statistics for the query optimizer (for improving performance of SQL queries), Automatic Segment Advisor for analyzing storage space for areas that can possibly be reclaimed for use, and the Automatic SQL Tuning Advisor for automatically analyzing SQL statements for performance improvements.
If an error is encountered at some point during the database creation that indicates a listener is not configured, it simply means we started the DBCA before configuring a listener. To solve that, there is no need to exit out of the database install window, just go back and perform the listener install steps and come back here where the screen will allow us to proceed.
8. On this screen (step 5) we can set the database passwords on the system accounts using a different one for each account, or by choosing one password for all four. We’re going to set a single password on all four, but for added security in a production environment, it is a good idea to make a different password for each. Click on the option to Use the Same Administrative Password for All Accounts and enter a password. This is very important to remember as these are key system accounts used for database administrative control. Oracle has decided as of this release to attempt to make us choose better, more secure passwords by popping up a warning dialog if the password we’ve chosen does not meet with their standards of complexity. It is only a warning however and we can answer Yes and continue.
9. This step is a combination now of the old steps 6 and 7 of the installation. It is about storage and specifying the locations where database files are to be created. We’ll leave it at the default of File System for storage management. The other two options are for more advanced installations that have greater storage needs. The locations where database files are to be created can be left at the default for simplicity (which uses the locations specified in the template and follows the OFA standard for naming folders described above). A storage screen will come up where we’ll be able to change the actual file locations if we want, for all but the Oracle-Managed Files option.
Te Oracle-Managed Files option is provided by the database so that we can let Oracle automatically name and locate our data files. A folder location is specified on the step 6 screen, which will become the default location for any files created using this option. This is why we won’t be able to change any file locations later on during the installation if this option is chosen. However, files can still be created with explicit names and locations after the database is running.
10. The next screen is for configuring recovery options. We’re up to step 8 now. If we were installing a production database, we would want to make sure to use the Flash Recovery option and to Enable Archiving. Flash Recovery is a feature Oracle has implemented in its database to provide a location that is managed by the database. It stores backups and files needed to recover a database in the event of disk failure. With Flash Recovery Area specified, we can recover data that would otherwise be lost in a system failure.
Enabling archiving turns on the archive log mode of the database, which causes it to archive the redo logs (files containing information that is used by the database to recover transactions in the event of a failure.) Having redo logs archived means you can recover your database up to the time of the failure, and not just up to the time of the last backup.
These recovery options will consume more disk space, but will provide a recovery option in the event of a failure. Each individual will have to make the call for their particular situation whether that is needed or not.
We’ll specify Flash Recovery and for simplicity, we will just leave the default for size and location. We will not enable archiving at this point. These options can always be modified after the database is running, so this is not the last chance to set them.
11. This step is where we can have the installation program create some sample schemas in the database for our reference, and specify any custom scripts to run. The text on the screen can be read to decide whether they are needed or not. We don’t need either of these for this topic, so it doesn’t matter which option we choose.
12. The next screen is for Initialization Parameters. These are the settings that are put in place to define various options for the database such as Memory options. There are over 200 different parameters and to go through all of them would take much more time and space than we have here. There is no need for that at this point as there are about 28 parameters that Oracle says are basic parameters that every database installation should set. We’re just going to leave the defaults set on this screen, which will set the basic parameters for us based on the amount of memory and disk space detected on our machine. We’ll just move on from here. Once again, these can all be adjusted later after the database is created and running if we need to make changes.
13. The next step (step 10 of 11) is the Database Storage screen referred to earlier. Here the locations of the pre-built data and control files can be changed if needed. They should be left set to the default for simplicity since this won’t be a production database. For a production environment, we would want to consider storing datafiles on separate partitions for performance reasons, and to minimize the impact of disk failures on the running database if something goes wrong. If all the datafiles are on one drive and it goes bad, then the whole database is down.
14. The final step has the following three options, and any or all can be selected for creating the database:
° Create the database directly
° Save the creation options as a template for later use
° Save database creation scripts that can be used later to create the database
We’ll leave the first checkbox checked to go ahead and create the database.
The Next button is grayed out since this is the last screen. So click on the Finish button to begin creating the database using the selections we’ve just chosen. It will display a summary screen showing what options it will be using to install with. We can save this as an HTML file if we’d like to keep a record of it for future reference.
All that information will be available in the database by querying system tables later, but it’s nice to have it all summarized in one file. We can scroll down that window and verify the various options that will be installed, including Oracle Warehouse Builder, which will have a true in the Selected column as shown here:
We will be presented with the progress screen next that will show us the progress as it creates the database.
When the install progress screen gets to 100% and all the items are checked off, we will be presented with a screen summarizing the database configuration details. Take a screen capture of this screen or write down the details because it’s good to know information on how the database is configured. Especially, we’ll need the database name in later installation steps. We may see the progress screen at 100% doing nothing with apparently no other display visible. Just look around the desktop underneath other windows for the Database Configuration Screen. It’s important for the next step.
On the final Database Configuration Screen, there is a button in the lower right corner labeled Password Management. We need to click on this button to unlock the schema created for OWB use. Oracle configures its databases with most of the pre-installed schemas locked, and so users cannot access them. It is necessary to unlock them specifically, and assign our own passwords to them if we need to use them. Two of them are the OWBSYS schema and the OWBSYS_AUDIT schema. These are the schemas that the installation program automatically installs to support the Warehouse Builder. They are required for running the Warehouse Builder. Click on the Password Management button and on the resulting Password Management screen, we’ll scroll down until we see the OWBSYS and OWBSYS_AUDIT schemas. We’ll click on the check box to uncheck it on each one (indicating we want them unlocked) and then type in a password and confirm it as shown in the following image:
Click on the OK button to apply these changes and close the window. On the Database Configuration Screen, click on the Exit button to exit out of the Database Configuration Assistant.
That’s it. We’re done installing our first database and it’s ready to use. Next, we’ll discuss installing the OWB client if we want to run the client on another computer, or if we already have a 10gR2 database installed that we want to use with the Warehouse Builder.
