Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Database Creation Assistant on Linux

Oracle Tips by Burleson

At the Linux command line in the Oracle user at the users’ home directory we type "dbca." The DBCA command will be effective only if the values for PATH and ORACLE_HOME are set correctly. Generally, if you can run the other Oracle utilities, such as SQL*Plus (the command would be sqlplus), you won’t have a problem running DBCA. Figure 2.1 shows the welcome screen for the Database Configuration Assistant tool.

Figure 2.1 Welcome screen for the Database Configuration Assistant.

The next screen, shown in Figure 2.2, (reached by selecting the Next button on the welcome screen) allows you to choose between several options:

* Create a database.

* Configure database options in a database.

* Delete a database.

* Manage templates.

For this example, we want to create a database. The other options are fairly self-explanatory. In the Oracle version of the DBCA, the Manage templates option has been added to allow the DBA to manage the precreated or newly created templates used to create databases. The Configure database options allows the DBA to choose from the available databases and perform reconfiguration of the server mode (dedicate or shared), or the options installed. The Delete database option lists the current instances and allows you to delete from them. We have selected the Create a database option for this example. Its radio button is shown as set in Figure 2.2, the Operations screen.

Figure 2.2 Database Operations screen.

Figure 2.3 Database Template Selection Screen

Figure 2.3 shows the selection of the database template to use. We will select New Database, as this will display the most custom options. The other templates use baseline assumptions to provide a minimized set of screens to generate a data warehouse, transaction processing, or general-purpose database. These other templates use already configured database files, which are uncompressed and placed in your filesystems, identically to creating a database from the same options offered in the OUI interface. Note that you have more options in the Oracle version of the Assistant than the Typical and Custom database options that were provided in the Oracle8i version. Now select the Next button to go on to the next step of database creation using the Database Configuration Assistant. The next screen is the Database Identification screen, which is shown in Figure 2.4.

Figure 2.4 Database Identification screen.

On the Database Identification screen you fill in one value, the SID domain name for your database. If you are unsure of the domain to use, type "" or simply the SID name. In my system, the domain is my server name, so I used the sid.tuscgalinux or specifically galinux2.tuscgalinux where the database name (SID) is "galinux1". The screen automatically strips off the domain name (anything after the first period) to create the SID name, and places the SID in the appropriate box for you. Once you have defined the SID and domain and selected the Next button, the Database Options screen (Figure 2.5) is displayed.

Figure 2.5 Database Options

The Database Options screen lists all available database options. By default, you will see that the Oracle Spatial, Oracle Ultra Search, and Example Schemas have been selected. I suggest unselecting the Example Schemas unless this is to be a learning database, as they are not needed for a production environment. This screen also allows you to add either custom-designed scripts or choose, via browse capabilities, scripts such as catblock.sql, catparr.sql, or other Oracle-provided scripts not run by catproc.sql. Once you have selected the options to be loaded and have specified any custom scripts that you want run, select the Additional database configurations button; the screen that allows you to select, or deselect, the Oracle JVM and Intermedia options is displayed. This additional option screen is shown in Figure 2.6.

Figure 2.6 Additional Configurations screen.

Oracle suggests (and I agree) that you add the JVM and Intermedia to your selection of options; they both provide additional power and functionality that are easy to add now, but may be more difficult in a fully functional production environment. Once you have chosen (or not chosen, as the case may be) the JVM and Intermedia options, select the OK button and then the Next button on the Options screen to display the Database Connection Options screen (Figure 2.7).

Figure 2.7 Database Connection Options screen.

In the Connection Options screen, you choose between using dedicated connections and configuring a multithreaded server (MTS). Generally, if your system isn't serving about 100 connections per CPU to the database, you won't need MTS, however if you will be using large, complex SQL commands you may need to turn on a minimal MTS configuration to ensure that the large pool is utilized. I have seen cases where not using the large pool will generate ORA-04031 and ORA-1037 errors with large, complex SQL. MTS is used when you either have many connections or when you have a very small amount of memory and many connections. If you have sufficient memory resources to serve the connected users, you will want to use dedicated connections. Make your selection, and then either select Next, if you choose dedicated connections, or configure the MTS (called shared connection) parameters; then select OK and Next. The next screen displayed will be the Initialization Parameters screen, shown in Figure 2.8.

Figure 2.8 Database Initialization Parameters screen.

As you can see, this screen allows you to alter the values for key initialization parameters in the general categories of Memory, Archive, DB Sizing and File Locations, whose screens are shown, respectively, in Figures 2.9, 2.10, and 2.11. You can also specify that you wish to examine and alter all initialization parameters; that brings up the screen shown in Figure 2.12.

Figure 2.9 Database Archive Initialization Parameters screen.

Figure 2.10 Database DB Sizing Initialization Parameters screen.

Figure 2.11 Database File Locations Initialization Parameters screen.

Figure 2.12 Database All Initialization Parameters screen.

Once you have set the initialization parameters as you desire (note, Oracle sets the initialization parameter _unnest_subquery to TRUE, which will dramatically alter the execution plans of statements; set it to FALSE to obtain the explain plans generated in Oracle8i, at least in version 9.0.1.), select the Next button to display the File Location Variables screen where you can specify the values for ORACLE_BASE, ORACLE_HOME, DB_NAME, and SID; normally, these should not be changed from the displayed values. This screen is shown in Figure 2.13.

Figure 2.13 Database File Location Variables screen.

Once you have specified any required changes to the file location variables, select the OK button to display the Database Storage screen (Figure 2.14), where you can change the default size of the basic tablespace datafiles (TEMP, INDX, UDOTBS, USERS, TOOLS, DRSYS, SYSTEM). Once you have made any desired changes, you can choose to alter the size of rollback segments, redo logs, or the location of the control files. Select the Next button.

Figure 2.14 Database Storage screen.

The next screen displayed will be the Creation Options screen (Figure 2.15). Here you can specify whether the database, a template a script, or all should be created. The Creation Options screen is the final step of input; the rest of the screens show status. Once you have chosen your creation options,  select the Finish button to implement them. I suggest always creating the script so that you can review the creation activity and have a document that describes how the database was created.

Figure 2.15 Database Creation Options screen.

The next screen shown, in Figure 2.16, is the Oracle Database Configuration Assistant--Database Creation Script generation status screen.  The next tells you the location of the script that was created, as shown in Figure 2.17. Once you have noted the location of the script, select OK.

Figure 2.16 Creation Script status screen.

Figure 2.17 Script Location screen.

The next screen displayed shows the various steps being performed and their status. To create a default database as shown in the screens in this section, it took several hours, most of which was taken up by the Java Virtual Machine (JVM) creation on a 400 MgHz, Pentium III, Linux SuSE7.2, with a single 30-gigabyte hard drive with 512 megabytes of memory. The creation status screen is shown in Figure 2.18.

Figure 2.18 Database Creation Status screen.

If all goes as anticipated, the next screen (shown in Figure 2.19) will tell you that the database was successfully created. It will also inform you that the default users are locked and that passwords not set. I suggest selecting the Password Management button at this point.

Figure 2.19 Successful Database Creation screen.

The Password Management screen (shown in Figure 2.20) allows you to specify passwords and change the locked status of the default database users. Once the passwords and account status are updated, select the OK button.

Figure 2.20 The Password Management screen.

If you have made it this far, you have successfully created a new Oracle database. Congratulations. If you had problems, you can either restart the process at the beginning after correcting any problems or use the script you hopefully generated earlier to manually create the database. The generated scripts will be located in the $ORACLE_HOME/admin/sid/scripts directory on UNIX or Linux, and on the ORACLE_HOME\admin\sid\scripts on NT4.0 or W2K.

The list of scripts generated during the above database creation contains: Master script that calls the others.

CreateDB.sql. Creates the database.

CreateDBFiles.sql. Creates the additional database tablespaces and files.

CreateDBCatalog.sql. Creates the catalog and procedural options.

JServer.sql. Creates the JVM.

ordinst.sql. Installs the ORDSYS tables, views, and packages for handling Oracle cartridges.

interMedia.sql. Creates the Intermedia option.

context.sql. Creates required support for contexts.

spatial.sql. Creates the spatial option.

ultraSearch.sql. Creates the Ultra Search option.

postDBCreation.sql. Creates the SPFILE; performs a clean shutdown and startup.

There may be more or fewer of these scripts depending on the options you chose to install. If your database creation fails, use these scripts to build the database manually.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter