There are some shared areas such as /tmp that will cause issues to
running or installing Oracle software if they fill up. So carefully place files
here, or be sure to purge out old installation logs that are placed here. There
are also system areas which can normally be viewed but not modified. If
sharing the server it would be important to make sure that changes to these
system areas are communicated, or discussed first. The Oracle user does not
need full permissions or root access to view the configuration information,
but being able to view the information is useful to verify the configurations.
72
Oracle Database Administration for Microsoft SQL Server DBAs
CHAPTER
4
Database Definitions
and Setup
I
n the previous chapter, we walked through installing the Oracle
software. The server should now be configured for Oracle, and
the required components installed. The advantage of installing the
software by itself first is that if there are any issues with the
configuration, the database doesn’t need to be dropped and re-
created each time. It is very easy to launch the Oracle configuration assistants
after the installation.
This chapter covers the next steps after the software is installed. The
Database Configuration Assistant will guide you through the creation of the
database. We will look at some of the configuration options, as well as how
to use templates and scripts.
Security is a big part of database setup. We already talked about security
at the operating system level, and there will be more at the application
level. Here, we will look at server and schema security. These various levels
of security will help you to achieve a more secure system.
Before we get into the details of database setup, let’s clarify some of the

at the server level and the database level. Also, there are no users inside the
Oracle schemas, because they are only at the server level.
The Oracle database is almost like the instance level for SQL Server. The
Oracle database is the overall group of datafiles and system information.
The Oracle software, memory structures, and processes make up an
instance. There is one Oracle database for the instance. In a clustered
environment, there can be multiple instances that all point to one database
on a shared disk.
Database owner
is another term that doesn’t really exist in Oracle.
Typically, the term
schema owner
is used.
Figure 4-1 shows some of these terms and how they apply in the different
database environments. Understanding the differences will help you to see
where the services, processes, and datafiles play their parts, and how the
different levels interact and handle processes within the various structures.
Chapter 4: Database Definitions and Setup
75
FIGURE 4-1.
Comparing SQL Server and Oracle terminology
To clarify how these terms are used in the different database systems,
let’s look at some naming examples. We’ll use the domain us.demo.com
and a server intended to support a human resources (HR) application, with
information about payroll, benefits, employees, and so on. Table 4-1 shows
the names for a single server, and Table 4-2 shows the names for a clustered
environment.
NOTE
It is helpful to name a server with a department
or functional name, so that it’s easy to

Name SQL Server Oracle
Server sqlsrvhr01v, sqlsrvhr02v orasrvhr01v,
orasrvhr02v
Instance PRODHR PRODHR01,
PRODHR02 (SID)
Database payroll_db, benefits_db,
hr_db, employee_db
PRODHR
TABLE 4-2.
Clustered Environment Naming Examples
Name SQL Server Oracle
Server (Windows
or Linux)
sqlsrvhr01.us
.demo.com
orasrvhr01.us
.demo.com
Instance Local server or named instance:
PRODHR
PRODHR (SID)
Database payroll_db, benefits_db,
hr_db, employee_db
PRODHR
(global database name
PRODHR.us.demo.com)
Schema dbo PAYROLL, BENEFITS,
HR, EMPLOYEE
Database server sqlsrvhr01\PRODHR orasrvhr01\PRODHR
TABLE 4-1.
Database Server Naming Examples

msdb SQL_Latin1_General_CP1_CI_AS 100
mmtest SQL_Latin1_General_CP1_CI_AS 90
With Oracle, the software is installed, and then we set up the database
with character sets, system information, and version. The database and
instance have the same character set. The Oracle schemas do not have the
option for changing the character set. The software components installed
are the versions that are used for the database server. This demonstrates that
the Oracle database has the system objects and keeps the system-level options
at the database level.
78
Oracle Database Administration for Microsoft SQL Server DBAs
Creating Databases
Oracle provides the Database Configuration Assistant (DBCA) to help
you create new databases. Other assistants are available for configuring
upgrades (DBUA) and Oracle Enterprise Manager (EMCA). You can also
use database scripts and templates to re-create databases with the same
configuration. We’ll start by walking through the DBCA.
Using the DBCA
The DBCA will create the instance parts, which are the processes and the
datafiles for the database. It will set up the memory structures.
Launch the DBCA from the ORACLE_HOME\bin directory. Figure 4-2
shows step 1 of the assistant, where you select to create a database.
Chapter 4: Database Definitions and Setup
79
FIGURE 4-2.
Selecting to create a database with the DBCA
As you step through the assistant, you name the database and accept
default values or change them as necessary. Here are some points to keep
in mind:
■

tablespaces are created. A user tablespace can also be configured.
■
You can specify locations for the database files, as shown in Figure 4-4.
Chapter 4: Database Definitions and Setup
81
FIGURE 4-4.
Choosing database file locations with the DBCA