Previous | Table of Contents | Next

Page 108

Running character oradim is where things get interesting. From the command line, you can create, edit, and delete existing Oracle services. For experienced Oracle DBAs, the command- line version of oradim is often preferred over the GUI because of the higher level of control and functionality. For this example, we will assume the command-line version of oradim is used.

The basic oradim command line follows the following format:

oradim _command _sid SID [flags]

oradim commands are described in Table 7.4.

Table 7.4 oradim Command-Line Options


Command Function
NEW Create a new database instance service
EDIT Edit an existing instance service
STARTUP Start up an instance or database
SHUTDOWN Shut down an instance or database
DELETE Delete instance services

An important thing to realize is that the instance services are completely independent of their associated databases. A database can exist on a Windows NT server without an associated instance service. Also, the version of oradim used when creating the service specifies the version of the Oracle server executable (oracle.exe) that will be run when starting the instance.

To create an Oracle service, issue the following command:

oradim _new _sid [sid_name] _pfile [init.ora directory] _intpwd [internal 
password] _startmode auto

This would create the OracleServiceSID and OracleStartSID, with the SID value equal to whatever you made the sid_name. The start mode of auto creates the OracleStartSID file. The pfile parameter gives the location of the init.ora file used to configure the instance started with the OracleServiceSID service.

For more information and examples on using the oradim command, refer to your Oracle Windows NT operating system-specific documentation.

Creating the Database

After the instance is configured, you can create the database. This is where you'll actually issue the CREATE DATABASE command. The CREATE DATABASE command will create the system datafile and tablespace, controlfiles, initial redo log groups, the system rollback segment, and the internal database tables necessary for the operation of the Oracle database.

Page 109

The create database command syntax can be found in the Oracle Server Reference Manual. It is important to correctly size the datafile specified in the CREATE DATABASE command. This datafile will be assigned to the System tablespace. Because hard drive space is relatively inexpensive, size the file two to three times larger than what you expect to use (at least 50_100MB). Allocate more space if the database will make heavy usage of stored packages, procedures, functions, and triggers, because the code for these objects is stored in the System tablespace and can use up quite a bit of space. Set your max parameters high as well. The defaults, which will be used if you do not explicitly state values for MAXDATAFILES, MAXLOGFILES, and so on, are generally too low. The only drawback here is that the controlfile will be a few KB larger than it needs to be—hardly a drawback at all. Also, be sure you specify the correct value for CHARACTERSET. This is one of the parameters that is non-trivial to change after database
creation.

Before running the CREATE DATABASE command, ensure the controlfile parameter in the init.ora is set properly and your ORACLE_SID is set to the proper database name. If you've set the db_name init.ora parameter, make sure it is the same as the SID value of your Oracle service. Store the text of the CREATE DATABASE SQL statement in a text file. This will reduce the likelihood of errors due to typos, as well as provide a level of documentation. Also, be sure that the OracleServiceSID service is started by running the Services applet in the Windows NT Control Panel and making sure OracleServiceSID is running.

Run svrmgrl (svrmgr## on NT systems, where ## is the major release number of your Oracle version) and connect internal to the database. You should get a message stating you have "connected to an idle instance." This indicates that the instance is running, but no database has been attached to it. Start up the database nomount by issuing the command:

startup nomount pfile=[init.ora file path and name];

When the database has been started, run your create database script. The time it takes for this command to complete depends mostly on how large you've made your system datafile. When the create command finishes, your database will be opened automatically. At this point, you have a working (albeit bare) Oracle database.

Running Post-Database Creation Procedures

Immediately after creating your database, there are two Oracle-supplied SQL scripts you
must run:

The oradim GUI tool automatically executes these scripts after creating the database. Both of these scripts are found in $ORACLE_HOME/rdbms/admin. Catalog.sql creates the data dictionary views and v$ view synonyms, while catproc.sql runs the scripts necessary to install the Procedural Option, or PL/SQL objects, and its supporting database structures. Failing to run either of these two scripts will result in strange and unpredictable errors.

Page 110

Another script you may want to run is pupbld.sql. This creates the product user profile tables. These are Oracle-supplied tables that can be used to limit access of Oracle programs to the database. The product user profile tables are rarely used today due to the many third-party applications that access the database using native or ODBC drivers, and do not check these tables for security privileges. If you do not create these tables, however, whenever users other than SYSTEM log into the database through SQL*Plus, they will get an annoying error message on their screen. Log in as SYSTEM when creating these tables.

Finally, if this database will be involved in replication you'll need to run $ORACLE_HOME/rdbms/admin/catrep.sql. This script creates the procedures and objects necessary to support the replication options.

Creating the Supporting Database Objects

Now that you have a skeleton database, you can begin creating the supporting objects that will allow your database to support a production-level workload. The key to creating the supporting objects is flexibility and manageability. If you keep these two goals in mind, your database installation will be a success.

The first thing you need to do is create a rollback segment to be used when creating the rest of your supporting database objects. When created, your database will have one system rollback segment, created in the system tablespace. This rollback segment is only used for transactions involving objects in the system tablespace and cannot be dropped or taken offline. Create a temporary rollback segment using the following command:

create rollback segment rbs_temp
storage (initial 10k next 10k minextents 3 maxextents 121)
tablespace system;

Bring the rollback segment online by issuing:

alter rollback segment rbs_temp online;

Now, create your production tablespaces. At a minimum, create the tablespaces listed in
Table 7.5.

Table 7.5 Required Database Tablespaces


Tablespace Name Purpose
TEMP Stores temporary database segments
TOOLS Stores third-party or in-house database tool objects
RBS Stores rollback segments
RBS_02 Stores large rollback segment(s) for bulk data loads
USER_DATA Default tablespace for end users
USER_INDEX Index tablespace for end users

Previous | Table of Contents | Next