Creating a New Database

Creating a new database sounds like a simple task. But as with any other complex endeavor, the foundation you lay at the start will determine your success. Many DBAs have scripts they've written over the years for creating databases that they can "hack upon" for current needs. Other DBAs prefer to use Oracle's Database Configuration Assistant (available with later versions of the database). But both these approaches suffer the same drawback; the DBA must be intimate with every single Oracle feature for the target database version and must explicitly define all the necessary information in Oracle terms. The problem is that you may well be a developer whose responsibilities include certain DBA functions. In that case, you may not be up to date on every single little Oracle tidbit. You just want to create a database as quickly as possible that meets your application's needs.

TOAD's New Database wizard was specifically designed with this scenario in mind. In fact, the guiding mantra was from Star Trek III, when Captain Kirk asks Mr. Scott if the Enterprise is fully automated yet and he replies, "A chimpanzee and two trainees could run her!" That's how easy this utility aims to be. Unlike Oracle's Database Configuration Assistant, which is more suited to DBAs, the TOAD New Database wizard was designed so that people with limited Oracle expertise could click a few buttons and create a database. The information requested by the wizard is more about the server hardware and application needs than about vague Oracle parameters and settings. The real-world test case for the New Database wizard was a group of Web consultants building Oracle applications but not having any DBAs because their project time spans were so short (that is, generally averaging under 90 days).

Note that TOAD's New Database wizard can be used to generate both UNIX and Windows scripts necessary to create the database. For UNIX, it will generate a shell script and an INIT.ORA file. For Windows , TOAD's New Database wizard will generate a batch script, a SQL file, and the INIT.ORA file. All scripts are generated such that the user can make customizations easily. In other words, the TOAD generated scripts can serve as their basic templates (similar to the way DBAs use scripts they've maintained over the years). Moreover, these scripts can be rerun at any time to recreate the database as well. TOAD offers all this functionality with little or no Oracle expertise required.

The TOAD New Database wizard takes you through five steps and is accessible from the main menu at DBANew Database. Note that all the selections you make during these five steps will affect both the generated code and possibly some of the options presented in the windows themselves . There are both Next and Back buttons in this wizard, so you can easily move back and forth to try various settings and options. Plus you can save and load your selections to a file on your PC. That way, when you get some or all the settings the way you like, you can save them for future restoration.

In step 1, you must provide some very basic database information, as shown in Figure 6.1. If you're merely creating a local database, TOAD will offer the ability to run the generated scripts at the wizard's conclusion. If instead you're creating a remote Windows or UNIX database, TOAD will just generate all the scripts. You will need to find other methods with which to deploy and execute those scripts. For example, with a UNIX database, you could use TOAD's FTP and rexec abilities (accessible from the main menu under File). Note that the Oracle base and home directory settings on this screen are critical. The database files will be created in the ORACLE_BASE/admin/SID directory. And Oracle commands will be referenced in the ORACLE_HOME/bin directory. The scripts will not work if these settings are not 100% correct.

Figure 6.1. TOAD New Database wizard ” step 1.

In step 2, you answer some basic server hardware and application requirements questions, as shown in Figure 6.2. How many users will the database have? How many CPUs on the server and at what speed? How much RAM? How many controller channels and with how much cache memory? These are all questions that should take little or no thought on your part. The answers, however, are very important because they will be utilized to calculate values for numerous Oracle parameters and settings in the generated scripts and INIT.ORA file. Thus TOAD enables you to focus and think in non-Oracle terms and translates the input into good Oracle configurations and settings. It's like having a DBA expert in the box for creating new databases.

Figure 6.2. TOAD New Database wizard ” step 2.

In step 3, you must decide on how you want to define your database's space allocation. In automatic mode, you continue working in the manner where you focus on non-Oracle responses and TOAD translates that into ideal Oracle selections. If instead you choose manual mode, you will be working in a more traditional mode where you must define tablespaces and allocate space to them yourself. Your selection here will decide which screen comes up next in the wizard. We'll examine both alternatives; however, the automatic mode is highly recommended.

In step 4 for automatic mode, you merely need to tell TOAD how big to make the database and where space exists on your server that can be used for creating that database. That's it, as shown in Figure 6.4. TOAD will then utilize expert rules to optimally place the data, redo, and control files. The only Oracle- related question on this screen is whether to use dictionary versus locally managed tablespaces via the Extent Mgmnt button (the default is locally managed). Of course, this button is sensitive to the Oracle database version setting from step 1 and adjusts accordingly . And for those who are interested, you can preview how your available space will automatically map to table-spaces to handle your requested database size.

Figure 6.3. TOAD New Database wizard ”step 3.

Figure 6.4. TOAD New Database wizard ”step 4 automatic.

When you define your volume information, keep in mind that not everyone is using just simple disks ”hence the term volume. If you have just simple disks, your volumes might be something like "C:\" and "D:\" for a Windows box. But more and more these days people are using volume managers to abstract away physical disks and instead deal with volumes . So a volume may have four disks and implement RAID as shown in Figure 6.5. The physical properties for the number of disks, RAID level, interface type, transfer rate, and rotation speed are critical to the rules that automatically lay out your database files. Note, too, that whether a volume contains operating system or swap files also helps to decide where to place database files. Once again, TOAD's New Database wizard makes the job super easy. You let it know how much space, where it is, and some other hardware properties, and TOAD will calculate the best database layout for your needs.

Figure 6.5. TOAD New Database wizard ”Edit Volumes.

In step 4 for manual mode, you must define all the tablespace information as shown in Figure 6.6. So instead of just saying give me a database of size x on the following volumes, you have to perform the more traditional work of defining each Oracle tablespace, its size and data file placement. That also means you need to know which tablespaces to create. For example, you'd need to decide between creating a rollback segment tablespace versus using an undo tablespace for an Oracle 9i database. But in automatic mode, all the best alternatives would have been automatically selected for you. This interface is provided for those DBAs who are comfortable with and accustomed to working this way. For non-experts or people just wanting the fastest way to create a database, the automatic mode is the better choice.

Figure 6.6. TOAD New Database wizard ”step 4 manual.

Finally in step 5, you get to select whether or not to run various advanced Oracle administrative scripts and where to create the generated output. And if you were creating a local database, you also would have been given the option to run the generated script as well. Plus there are buttons to view all the generated code. For UNIX, you can preview both the shell script (which also contains the SQL code as a here document) and the INIT.ORA file, which is shown in Figure 6.7. For Windows, you can preview the batch script, SQL file, and INIT.ORA file. When you click Finish, all these files are written to the location specified.