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
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 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 "sid.world" 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 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 Database Initialization Parameters
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
Figure 2.10 Database DB Sizing Initialization
Figure 2.11 Database File Locations
Initialization Parameters screen.
Figure 2.12 Database All Initialization
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
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
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:
galinux2.sh. Master script that calls
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
context.sql. Creates required support
spatial.sql. Creates the spatial
ultraSearch.sql. Creates the Ultra
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.
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.