Chapter 1 detailed how to efficiently install the Oracle binaries. After you've installed the Oracle software, the next logical task is creating a database. There are two standard ways for creating Oracle databases:
dbca
) utilityCREATE DATABASE
statement from SQL*PlusOracle's dbca
utility has a graphical interface from which you can configure and create databases. This visual tool is easy to use and has a very intuitive interface. If you need to create a development database and get going quickly, then this tool is more than adequate. Having said that, I normally don't use the dbca
utility to create databases. In Linux/Unix environments, the dbca
tool depends on X software and an appropriate setting for the operating system DISPLAY
variable. The dbca
utility therefore requires some setup and can perform poorly if you're installing on remote servers when the network throughput is slow.
The dbca
utility also allows you to create a database in silent mode without the graphical component. Using dbca
in silent mode with a response file is an efficient way to create databases in a consistent and repeatable manner. This approach also works well when you're installing on remote servers where the network connection could be slow or you don't have the appropriate X software installed.
When you're creating databases on remote servers, it's usually easier and more efficient to use SQL*Plus. The SQL*Plus approach is simple and inherently scriptable. In addition, SQL*Plus works no matter how slow the network connection is, and it isn't dependent on a graphical component. Therefore, I almost always use the SQL*Plus technique to create databases.
This chapter starts by showing you how to quickly create a database using SQL*Plus and also how to make your database remotely available by enabling a listener process. Later, the chapter shows you how to use the dbca
utility in silent mode with a response file to create a database.
Before getting into creating a database, you need to know a bit about operating system variables, which are often called environment variables. Specifically, you need to know how to set these in support of your Oracle Database environment. You can take several different approaches. This chapter discusses three, beginning with a manual approach and ending with the approach that I personally prefer.
In Linux/Unix, when you're using the Bourne, Bash, or Korn shell, you can set operating system variables manually from the operating system command line using the export
command:
$ export ORACLE_HOME=/ora01/app/oracle/product/11.2.0/db_1
$ export ORACLE_SID=O11R2
$ export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
$ export PATH=$ORACLE_HOME/bin:$PATH
For the C or tcsh shell, use the setenv
command to set variables:
$ setenv ORACLE_HOME <path>
$ setenv ORACLE_SID <sid>
$ setenv LD_LIBRARY_PATH <path>
$ setenv PATH <path>
Another way that DBAs set these variables is by placing the previous export
or setenv
commands into a startup file such as .bash_profile
, .bashrc
, or .profile
. That way, the variables are automatically set upon login.
However, manually setting OS variables (either from the command line or from a startup file) isn't the optimal way of instantiating these variables. For example, if you have multiple databases with multiple Oracle homes on a box, manually setting these variables quickly becomes unwieldy and not very maintainable.
A much better method for setting OS variables uses a script that uses a file that contains the names of all Oracle databases on a server and their associated Oracle homes. This approach is flexible and maintainable. For example, if a database's Oracle home changes (for example, after an upgrade), you only have to modify one file on the server and not hunt down where the Oracle home variables may be hard-coded into scripts.
Oracle provides a mechanism for automatically setting the required OS variables. Oracle's approach relies on two files: oratab
and oraenv
.
You can think of the entries in the oratab
file as a registry of what databases are installed on a box and their corresponding Oracle home directories.
The oratab
file is automatically created for you when you install the Oracle software. On Linux boxes, oratab
is usually placed in the /etc
directory. On Solaris servers, the oratab
file is placed in the /var/opt/oracle
directory. If for some reason the oratab
file isn't automatically created, you can manually create the directory and file.
The oratab
file is used in Linux/Unix environments for the following:
The oratab
file has three columns with the following format:
<database_sid>:<oracle_home_dir>:Y|N
The Y
or N
indicates whether you want Oracle to automatically restart on reboot of the box; Y
indicates yes, and N
indicates no. Automating the startup and shutdown of your database is covered in detail in Chapter 21, “Automating Jobs.”
Comments in the oratab
file start with a pound sign (#
). Here's a typical oratab
file entry:
# 11g prod databases
O11R2:/oracle/app/oracle/product/11.2.0/db_1:N
ORC11G:/oracle/app/oracle/product/11.2.0/db_1:N
Several Oracle-supplied utilities use the oratab
file:
oraenv
uses oratab
to set the operating system variables.dbstart
uses it to automatically start the database on server reboots (if the third field in oratab
is Y
).dbstop
uses it to automatically stop the database on server reboots (if the third field in oratab
is Y
).The oraenv
tool is discussed in the next section.
If you don't properly set the required OS variables for an Oracle environment, then utilities such as SQL*Plus, Oracle Recovery Manager (RMAN), Data Pump, and so on won't work correctly. The oraenv
utility automates the setting of required OS variables (such as ORACLE_HOME
, ORACLE_SID
, and PATH
) on an Oracle database server. This utility is used in Bash, Korn, and Bourne shell environments (if you're in a C shell environment, there is a corresponding coraenv
utility).
The oraenv
utility is located in the ORACLE_HOME/bin
directory. You can run it manually like this:
$ . oraenv
Note that the syntax to run this from the command line requires a space between the dot (.
) and the oraenv
tool. You're prompted for ORACLE_SID
and ORACLE_HOME
values:
ORACLE_SID = [oracle] ?
ORACLE_HOME = [/home/oracle] ?
You can also run the oraenv
utility in a non-interactive way by setting OS variables before you run it. This is useful for scripting when you don't want to be prompted for input:
$ export ORACLE_SID=oracle
$ export ORAENV_ASK=NO
$ . oraenv
I don't use Oracle's oraenv
file to set the OS variables (see the previous section for details of Oracle's approach). Instead, I use a script named oraset
. The oraset
script depends on the oratab
file being in the correct directory and of the expected format:
<database_sid>:<oracle_home_dir>:Y|N
As mentioned in the previous section, the Oracle installer should create an oratab
file for you in the correct directory. If it doesn't, then you can manually create and populate the file. In Linux, the oratab
file is usually created in the /etc
directory. On Solaris servers, the oratab
file is located in /var/opt/oracle
. Here is an example:
O11R2:/ora01/app/oracle/product/11.2.0/db_1:N
DEV1:/ora02/app/oracle/product/11.2.0/db_1:N
The names of the databases on the previous lines are O11R2
and DEV1
. The path of each database's Oracle home directory is next on the line (separated from the database name by a colon [:
]). The last column contains Y
or N
and indicates whether you want the databases to automatically be restarted when the system reboots.
Next, use a script that reads the oratab
file and sets the operating system variables. Here is an example of an oraset
script that reads the oratab
file and presents a menu of choices (based on the database names in the oratab
file):
#!/bin/bash
# Why: Sets Oracle environment variables.
# Setup: 1. Put oraset file in /var/opt/oracle
# 2. Ensure /var/opt/oracle is in $PATH
# Usage: batch mode: . oraset <SID>
# menu mode: . oraset
#====================================================
OTAB=/var/opt/oracle/oratab
if [ -z $1 ]; then
SIDLIST=$(grep -v '#' ${OTAB} | cut -f1 -d:)
# PS3 indicates the prompt to be used for the Bash select command.
PS3='SID? '
select sid in ${SIDLIST}; do
if [ -n $sid ]; then
HOLD_SID=$sid
break
fi
done
else
if grep -v '#' ${OTAB} | grep -w "${1}:">/dev/null; then
HOLD_SID=$1
else
echo "SID: $1 not found in $OTAB"
fi
shift
fi
#
export ORACLE_SID=$HOLD_SID
export ORACLE_HOME=$(grep -v '#' $OTAB|grep -w $ORACLE_SID:|cut -f2 -d:)
export ORACLE_BASE=${ORACLE_HOME%%/product*}
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ADR_HOME=$ORACLE_BASE/diag/rdbms/$(echo $HOLD_SID|tr A-Z a-z)/$HOLD_SID
export PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/opt/SENSsshc/bin/
:/bin:/usr/bin:.:/var/opt/oracle
export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
You can run the oraset
script either from the command line or from a startup file (such as .profile
, .bash_profile
, or .bashrc
). To run oraset
from the command line, place the oraset
file in a standard location like /var/opt/oracle
and run as follows:
$ . /var/opt/oracle/oraset
Note that the syntax to run this from the command line requires a space between the dot (.
) and the rest of the command. When you run oraset
from the command line, you should be presented with a menu like this:
1) O11R2
2) DEV1
In this example, you can now enter 1
or 2
to set the OS variables required for whichever database you want to use. This allows you to interactively set up OS variables regardless of the number of database installations on the server.
You can also call the oraset
file from an operating system startup file. Here is a sample entry in the .bashrc
file:
. /var/opt/oracle/oraset
Now, every time you log on to the server, you're presented with a menu of choices that you can use to indicate the database for which you want the OS variables set.
This section explains how to manually create an Oracle database with the SQL*Plus CREATE DATABASE
statement. Listed next are the steps required to create a database:
Each of these steps is covered in the following subsections.
Before you run SQL*Plus (or any other Oracle utility), you must set several OS variables:
ORACLE_HOME
PATH
ORACLE_SID
LD_LIBRARY_PATH
The ORACLE_HOME
variable defines the default directory location for the initialization file, which is ORACLE_HOME/dbs
on Linux/Unix. On Windows, this directory is usually ORACLE_HOMEdatabase
. The ORACLE_HOME
variable is also important because it defines the directory location of the Oracle binary files (such as sqlplus
) that are in ORACLE_HOME/bin
.
The PATH
variable specifies which directories are looked in by default when you type a command from the operating system prompt. In almost all situations, you require ORACLE_HOME/bin
(the location of the Oracle binaries) to be included in your PATH
variable.
The ORACLE_SID
variable defines the default name of the database you're attempting to create. ORACLE_SID
is also used as the default name for the initialization file, which is init<ORACLE_SID>.ora
.
The LD_LIBRARY_PATH
variable is important because it specifies where to search for libraries on Linux/Unix boxes. The value of this variable is typically set to include ORACLE_HOME/lib
.
Oracle requires that you have an initialization file in place before you attempt to start the instance. The initialization file is used to configure features such as memory and to control file locations. You can use two types of initialization files:
spfile
)init.ora
text fileOracle recommends that you use an spfile
for reasons such as these:
spfile
with the SQL ALTER SYSTEM
statement.These are good reasons to use an spfile
. However, some shops still use the traditional init.ora
file. The init.ora
file also has advantages:
When I first create a database, I find it easier to use an init.ora
file. This file can be easily converted later to an spfile
if required (via the CREATE SPFILE FROM PFILE
statement). Here are the contents of a typical Oracle Database 11g init.ora
file:
db_name=O11R2
db_block_size=8192
memory_target=800M
memory_max_target=800M
processes=200
control_files=(/ora01/dbfile/O11R2/control01.ctl,/ora02/dbfile/O11R2/control02.ctl)
job_queue_processes=10
open_cursors=300
fast_start_mttr_target=500
undo_management=AUTO
undo_tablespace=UNDOTBS1
remote_login_passwordfile=EXCLUSIVE
Ensure that the initialization file is named correctly and located in the appropriate directory. When starting your instance, Oracle looks in the default location for a binary initialization file named spfile<ORACLE_SID>.ora
. If there is no binary spfile
, Oracle looks for a text file with the name init<ORACLE_SID>.ora
. Oracle throws an error if it can't find an initialization file (either spfile
or init.ora
) in the default location. You can explicitly tell Oracle which directory and file to use by specifying the PFILE
clause of the STARTUP
statement, which allows you to specify a nondefault directory and name of a client (not server) initialization file.
On Linux/Unix systems, the initialization file (either a text init.ora
or binary spfile
) is by default located in the ORACLE_HOME/dbs
directory. On Windows, the default directory is ORACLE_HOMEdatabase
.
Table 2–1 lists best practices when configuring an Oracle initialization file.
Table 2–1. Initialization File Best Practices
Best Practice | Reasoning |
Oracle recommends that you use a binary server parameter file (spfile ). However, I still use the old text init.ora files in some cases. |
Use whichever type of initialization parameter file you're comfortable with. If you have a requirement to use an spfile , then by all means implement one. |
In general, don't set initialization parameters if you're not sure of their intended purpose. When in doubt, use the default. | Setting initialization parameters can have far-reaching consequences in terms of database performance. Only modify parameters if you know what the resulting behavior will be. |
For 11g, set the memory_target and memory_max_target initialization parameters. |
Doing this allows Oracle to manage all memory components for you. |
For 10g, set the sga_target and sga_target_max initialization parameters. |
Doing this lets Oracle manage most memory components for you. |
For 10g, set pga_aggregate_target and workarea_size_policy . |
Doing this allows Oracle to manage the memory used for the sort space. |
Starting with 10g, use the automatic UNDO feature. This is set using the undo_management and undo_tablespace parameters. |
Doing this allows Oracle to manage most features of the UNDO tablespace. |
Set open_cursors to a higher value than the default. I typically set it to 500. Active online transaction processing (OLTP) databases may need a much higher value. |
The default value of 50 is almost never enough. Even a small one-user application can exceed the default value of 50 open cursors. |
Name the control files with the pattern /<mount_point>/dbfile/<database_name>/control0N.ctl . |
This deviates slightly from the Optimal Flexible Architecture (OFA) standard. I find this location easier to navigate to, as opposed to being located under ORACLE_BASE . |
Use at least two control files, preferably in different locations using different disks. | If one control file becomes corrupt, it's always a good idea to have at least one other control file available. |
Any directories referenced in the initialization file or CREATE DATABASE
statement must be created on the server before you attempt to create a database. For example, in the previous section's initialization file, the control files are defined as
control_files=(/ora01/dbfile/O11R2/control01.ctl,/ora02/dbfile/O11R2/control02.ctl)
From the previous line, ensure that you've created the directories /ora01/dbfile/O11R2
and /ora02/dbfile/O11R2
(modify this according to your environment). In Linux/Unix, you can create directories and any parent directories required by using the mkdir
command with the p
switch:
$ mkdir -p /ora01/dbfile/O11R2
$ mkdir -p /ora02/dbfile/O11R2
Also ensure that you create any directories required for datafiles and online redo logs referenced in the CREATE DATABASE
statement (see the section “Step 4: Create the Database”). For this example, here are the directories required:
$ mkdir -p /ora01/dbfile/O11R2
$ mkdir -p /ora01/dbfile/O11R2
$ mkdir -p /ora01/dbfile/O11R2
$ mkdir -p /ora02/oraredo/O11R2
$ mkdir -p /ora03/oarredo/O11R2
If you create the previous directories as the root
user, ensure that the oracle
user and dba
group are properly set to own the directories, subdirectories, and files. This example recursively changes the owner and group of the following directories:
# chown -R oracle:dba /ora01
# chown -R oracle:dba /ora02
# chown -R oracle:dba /ora03
If you're using Oracle Database 10g or lower, make sure any background dump directories that are listed in the initialization file are created:
$ mkdir -p /ora01/app/oracle/admin/DB10G/udump
$ mkdir -p /ora01/app/oracle/admin/DB10G/bdump
$ mkdir -p /ora01/app/oracle/admin/DB10G/adump
$ mkdir -p /ora01/app/oracle/admin/DB10G/cdump
After you've established OS variables, created an initialization file, and created any required directories, you can create a database. This step explains how to use the CREATE DATABASE
statement to create a database.
Before you can run the CREATE DATABASE
statement, you must start the background processes and allocate memory via the STARTUP NOMOUNT
statement:
$ sqlplus / as sysdba
SQL> startup nomount;
When you issue a STARTUP NOMOUNT
statement, SQL*Plus attempts to read the initialization file in the ORACLE_HOME/dbs
directory (see the earlier section “Step 2: Create the Initialization File”). The STARTUP NOMOUNT
statement instantiates the background processes and memory areas used by Oracle. At this point, you have an Oracle instance, but you have no database.
Note An Oracle instance is defined to be the background processes and memory areas. The Oracle database is defined to be the physical files on disk.
Listed next is a typical Oracle CREATE DATABASE
statement:
CREATE DATABASE O11R2
maxlogfiles 16
maxlogmembers 4
maxdatafiles 1024
maxinstances 1
maxloghistory 680
character set "UTF8"
DATAFILE '/ora01/dbfile/O11R2/system01.dbf'
SIZE 500m
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE '/ora02/dbfile/O11R2/undotbs01.dbf'
SIZE 800m
SYSAUX DATAFILE '/ora03/dbfile/O11R2/sysaux01.dbf'
SIZE 200m
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/ora03/dbfile/O11R2/temp01.dbf'
SIZE 800m
DEFAULT TABLESPACE users DATAFILE '/ora02/dbfile/O11R2/users01.dbf'
SIZE 20m
LOGFILE GROUP 1
('/ora02/oraredo/O11R2/redo01a.rdo',
'/ora03/oraredo/O11R2/redo01b.rdo') SIZE 100m,
GROUP 2
('/ora02/oraredo/O11R2/redo02a.rdo',
'/ora03/oraredo/O11R2/redo02b.rdo' ) SIZE 100m,
GROUP 3
('/ora02/oraredo/O11R2/redo03a.rdo',
'/ora03/oraredo/O11R2/redo03b.rdo' ) SIZE 100m
USER sys IDENTIFIED BY secretfoo
USER system IDENTIFIED BY secretfoobar;
In this example, the script is placed in a file named credb.sql
and is run from the SQL*Plus prompt as the sys
user:
SQL> @credb.sql
If it's successful, you should see the following message:
Database created.
If any errors are thrown while the CREATE DATABASE
statement is running, check the alert log file. Typical errors occur when required directories don't exist, or the memory allocation isn't enough, or some operating system limit has been exceeded. If you're unsure of the location of your alert log, issue the following:
There are few key things to note about the prior CREATE DATABASE
statement example. For example, notice that the SYSTEM
datafile is defined as locally managed. This means any tablespace created in this database must be locally managed (as opposed to dictionary managed). Oracle throws an error if you attempt to create a dictionary-managed tablespace in this database. This is the desired behavior.
A dictionary-managed tablespace uses the Oracle data dictionary to manage extents and free space, whereas a locally managed tablespace uses a bitmap in each datafile to manage its extents and free space. Locally managed tablespaces have these advantages:
Also notice that the TEMP
tablespace is defined to be the default temporary tablespace. This means any user created in the database automatically has the TEMP
tablespace assigned to them as their default temporary tablespace. You can verify the default temporary tablespace with this query:
select *
from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';
Finally, notice that the USERS
tablespace is defined to be the default permanent tablespace for any users created that don't have a default tablespace defined in a CREATE USER
statement. You can run this query to determine the default temporary tablespace:
select *
from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
Table 2–2 lists best practices to consider when you're creating an Oracle database.
Table 2–2. Best Practices for Creating an Oracle Database
Note that the CREATE DATABASE
statement used in this step deviates slightly from the OFA standard in terms of the directory structure. I prefer not to place the Oracle datafiles, online redo logs, and control files under ORACLE_BASE
(as specified by the OFA standard). I instead directly place files under directories named /<mount_point>/<file_type>/<database_name>
, because the path names are much shorter. The shorter path names make command-line navigation to directories easier, and the names fit more cleanly in the output of SQL SELECT
statements. Figure 2–1 displays this deviation from the OFA standard.
Figure 2–1. A slight deviation from the OFA standard for laying out database files
It's not my intention to have you use nonstandard OFA structures. Rather, do what makes sense for your environment and requirements. Apply reasonable standards that foster manageability, maintainability, and scalability.
After your database is successfully created, you can instantiate the data dictionary by running two scripts. These scripts are created when you install the Oracle binaries. You must run these scripts as the SYS
schema:
SQL> show user
USER is "SYS"
Before I create the data dictionary, I like to spool an output file that I can inspect in the event of unexpected errors:
SQL> spool create_dd.lis
Now, create the data dictionary:
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
After you successfully create the data dictionary, as the SYSTEM
schema, create the product user profile tables:
SQL> connect system/<password>
SQL> @?/sqlplus/admin/pupbld
These tables allow SQL*Plus to disable commands on a user-by-user basis. If the pupbld.sql
script isn't run, then all non-sys
users see the following warning when logging in to SQL*Plus:
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
These errors can be ignored. If you don't want to see them when logging into SQL*Plus, make sure you run the pupbld.sql
script.
At this point, you should have a fully functional database. You next need to configure and implement your listener to enable remote connectivity, and optionally set up a password file. Those tasks are described in the next two sections.
After you've installed binaries and created a database, you need to make the database accessible to remote client connections. You do this by configuring and starting the Oracle listener. Appropriately named, the listener is the process that listens for connection requests from remote clients. If you don't have a listener started on the database server, then you can't connect from a remote client.
When you're setting up a new environment, configuring the listener is a two-step process:
listener.ora
file.The listener.ora
file is located by default in the ORACLE_HOME/network/admin
directory. This is the same directory that the TNS_ADMIN
operating system variable should be set to. Here is a sample listener.ora
file that contains network-configuration information for one database:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora03)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = O11R2)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1)
(SID_NAME = O11R2)
)
)
This code listing has two sections. The first defines the listener name and service; in this example, the listener name is LISTENER
. The second section defines the list of SIDs for which the listener is listening for incoming connections (to the database). The format of the SID list name is SID_LIST_<name of listener>
. The name of the listener must appear in the SID list name. The SID list name in this example is SID_LIST_LISTENER
.
After you have a listener.ora
file in place, you can start the listener background process with the lsnrctl
utility:
$ lsnrctl start
You should see informational messages such as the following:
Listener Parameter File
/oracle/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File
/oracle/app/oracle/diag/tnslsnr/ora03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora03.regis.local)(PORT=1521)))
Services Summary...
Service "O11R2" has 1 instance(s).
Instance "O11R2", status UNKNOWN, has 1 handler(s) for this service...
When the listener has been started, you can test remote connectivity from a SQL*Plus client as follows:
$ sqlplus user/pass@'server:port/db_name'
In the next line of code, the user and password are system/manager
, connecting to the ora03
server, port 1521, to a database named O11R2
:
$ sqlplus system/manager@'ora03:1521/O11R2'
This example demonstrates what is known as the easy connect naming method of connecting to a database. It's easy because it doesn't rely on any setup files or utilities. The only information you have to know is a username, password, server, port, and SID.
Another common connection method is local naming. This method relies on connection information in the TNS_ADMIN/tnsnames.ora
file. In this example, the tnsnames.ora
file is edited and the following Transparent Network Substrate (TNS, Oracle's network architecture) entry is added:
O11R2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora03)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = O11R2)))
Now, from the operating system command line, you establish a connection by referencing the O11R2
TNS information that was placed in the tnsnames.ora
file:
$ sqlplus system/manager@O11R2
This connection method is local because it relies on a local client copy of the tnsnames.ora
file to determine the Oracle Net connection details. By default, SQL*Plus inspects the directory defined by the OS variable TNS_ADMIN
for a file named tnsnames.ora
. If the tnsnames.ora
file contains the alias specified in the SQL*Plus connection string (in this example, O11R2
), the connection details are determined from the entry in the tnsnames.ora
file.
The other connection-naming methods that Oracle uses are external naming and directory naming. Refer to the Oracle Net Services Administrator's Guide for further details (available on Oracle's OTN web site).
Creating a password file is optional. There are some good reasons for requiring a password file:
sys
users to have sysdba
or sysoper
privileges.sysdba
or sysoper
privileges.Perform the following steps to implement a password file:
orapwd
utility.REMOTE_LOGIN_PASSWORDFILE
to EXCLUSIVE
.In a Linux/Unix environment, use the orapwd
utility to create a password file as follows:
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw<ORACLE_SID> password=<sys password>
In a Linux/Unix environment, the password file is usually stored in ORACLE_HOME/dbs
; and in Windows, it's typically placed in the ORACLE_HOMEdatabase
directory.
The format of the filename that you specify in the previous command may vary by OS. For example, on Windows, the format is PWD<ORACLE_SID>.ora
. The following shows the syntax in a Windows environment:
c:> cd %ORACLE_HOME%database
c:> orapwd file=PWD<ORACLE_SID>.ora password=<sys password>
To enable the use of the password file, set the initialization parameter REMOTE_LOGIN_PASSWORDFILE
to EXCLUSIVE
. Setting this value to EXCLUSIVE
instructs Oracle to allow only one instance to connect to the database and also specifies that the password file can contain schemas other than sys
. Table 2–3 details the meanings of the possible values for REMOTE_LOGIN_PASSWORDFILE
.
Table 2–3. Values for remote_login_passwordfile
Value | Meaning |
EXCLUSIVE |
One instance can connect to the database. Users other than sys can be in the password file. |
SHARED |
Multiple databases can share a password file. sys is the only user allowed in the password file. Oracle returns an ORA-01999 if you attempt to grant sysdba privileges to a user when the value is set to SHARED . |
NONE |
Oracle ignores the password file. Only local privileged accounts can connect as sysdba . |
You can add users to the password file via the GRANT SYSDBA
statement. The following example grants sysdba
privileges and adds the user heera
to the password file:
SQL> grant sysdba to heera;
Grant succeeded.
Enabling a password file also allows you to connect to your database remotely with sysdba
privileges via an Oracle Net connection:
$ sqlplus <username>/<password>@<database connection string> as sysdba
This allows you to do remote maintenance with sysdba
privileges that would otherwise require you to physically log on to the database server.
Tip You can query the V$PWFILE_USERS
view to display users granted sysdba
and sysoper
privileges.
The concept of a privileged user is also important to RMAN backup and recovery. Like SQL*Plus, RMAN uses OS authentication and password files to allow privileged users to connect to the database. Only a privileged account is allowed to back up, restore, and recover a database.
Before you can start and stop an Oracle instance, you must set the proper OS variables (previously covered in this chapter). You also need access to either a privileged OS account or a privileged database user account. Connecting as a privileged user allows you to perform administrative tasks such as starting, stopping, and creating databases. You can use either OS authentication or a password file to connect to your database as a privileged user.
OS authentication means that if you can log on to an authorized operating system account, you're allowed to connect to your database without the requirement of an additional password. OS authentication is administered by assigning special privileges to OS accounts.
When you install the Oracle binaries in a Linux/Unix environment, you're required to specify at installation time the names of the OS groups (usually named dba
and oper
) that are assigned the database privileges of sysdba
and sysoper
. In a Windows environment, an OS group is automatically created (typically named ora_dba
) and assigned to the OS user who installs the Oracle software.
The sysdba
and sysoper
privileges allow you to perform administrative tasks such as starting and stopping your database. As shown in Table 2–4, the sysoper
privilege contains a subset of the sysdba
privileges.
Table 2–4. Privileges of sysdba
and sysoper
System Privilege | Authorized Operations |
sysdba (all privileges of the sys schema) |
Start up and shut down, alter database, create and drop database, toggle archivelog mode, recover database |
sysoper |
Start up and shut down, alter database, toggle archivelog mode, recover database |
Any OS account assigned to the authorized OS groups can connect to the database without a password and perform administrative operations. In Linux/Unix, it's common to create an oracle
OS account and assign its primary group to be dba
. Here's an example of displaying the user and group ID information with the Linux/Unix id
command and then connecting to the database using OS authentication:
$ id
uuid=100(oracle) gid=101(dba)
$ sqlplus / as sysdba
In Windows environments, you can verify which OS users belong to the ora_dba
group as follows: select Start Control Panel Administrative Tools Computer Management Local Users and Groups Groups. You should see a group named something like ora_dba
. You can click that group and view which OS users are assigned to it.
In addition, for OS authentication to work in Windows environments, you must have the following entry in your sqlnet.ora
file:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
The sqlnet.ora
file is located in the ORACLE_HOME/network/admin
directory.
Starting and stopping your database is a task that you perform frequently. To start/stop your database, connect with a sysdba
or sysoper
privileged user account, and issue the startup
and shutdown
statements. The following example uses OS authentication to connect to the database:
$ sqlplus / as sysdba
After you're connected as a privileged account, you can start your database as follows:
SQL> startup;
Note Stopping and restarting your database in quick succession is known colloquially in the DBA world as bouncing your database.
However, if the parameter file (pfile
or spfile
) isn't located in ORACLE_HOME/dbs
for Linux/Unix or in ORACLE_HOMEdatabase
for Windows, then you have to include the pfile
clause to reference an init.ora
file as follows:
SQL> startup pfile=C: empinitORCL.ora
You should see messages from Oracle indicating that the system global area (SGA) has been allocated. The database is mounted and then opened:
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 11235813 bytes
Variable Size 31415926 bytes
Database Buffers 192937984 bytes
Redo Buffers 1235711 bytes
Database mounted.
Database opened.
From the prior output, the database startup operation goes through three distinct phases in opening an Oracle database:
You can step through these one at a time when you start your database. First, start the Oracle instance (background processes and memory structures):
SQL> startup nomount;
Next, mount the database. At this point, Oracle reads the control files:
SQL> alter database mount;
Finally, open the datafiles and online redo log files:
SQL> alter database open;
In most cases, you issue a STARTUP
statement with no parameters to start your database. Table 2–5 describes the meanings of parameters that you can use with the database STARTUP
statement.
Table 2–5. Parameters Available with the startup
Command
Parameter | Meaning |
FORCE |
Shuts down the instance with ABORT before restarting it. Useful for troubleshooting startup issues. Not normally used. |
RESTRICT |
Only allows users with the RESTRICTED SESSION privilege to connect to the database. |
PFILE |
Specifies the client parameter file to be used when starting the instance. |
QUIET |
Suppresses the display of SGA information when starting the instance. |
NOMOUNT |
Starts background processes and allocates memory. Doesn't read control files. |
MOUNT |
Starts background processes, allocates memory, and reads the control files. |
OPEN |
Starts background processes, allocates memory, reads control files, and opens online redo logs and datafiles. |
OPEN RECOVER |
Attempts media recovery before opening the database. |
OPEN READ ONLY |
Opens the database in read-only mode. |
UPGRADE |
Used when upgrading a database. |
DOWNGRADE |
Used when downgrading a database. |
Normally, you use the SHUTDOWN IMMEDIATE
statement to stop a database. The IMMEDIATE
parameter instructs Oracle to halt database activity and roll back any open transactions:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
For a detailed definition of the parameters available with the SHUTDOWN
statement, refer to Table 2–6. In most cases, SHUTDOWN IMMEDIATE
is an acceptable method of shutting down your database. If you issue the SHUTDOWN
command with no parameters, it's equivalent to issuing SHUTDOWN NORMAL
.
Table 2–6. Parameters Available with the SHUTDOW
N
Command
Parameter | Meaning |
NORMAL |
Wait for users to log out of active sessions before shutting down. |
TRANSACTIONAL |
Wait for transactions to finish, and then terminate the session. |
TRANSACTIONAL LOCAL |
Perform a transactional shutdown for local instance only. |
IMMEDIATE |
Terminate active sessions immediately. Open transactions are rolled back. |
ABORT |
Terminate the instance immediately. Transactions are terminated and aren't rolled back. |
Starting and stopping your database is a fairly simple process. If the environment is set up correctly, you should be able to connect to your database and issue the appropriate STARTUP
and SHUTDOWN
statements.
Tip If you experience any issues with starting or stopping your database, look in the alert log for details. The alert log usually has a pertinent message regarding any problems.
You should rarely need to use the SHUTDOWN ABORT
statement. Usually, SHUTDOWN IMMEDIATE
is sufficient. Having said that, there's nothing wrong with using SHUTDOWN ABORT
. If SHUTDOWN IMMEDIATE
isn't working for any reason, then use SHUTDOWN ABORT
.
On a few rare occasions, the SHUTDOWN ABORT
statement doesn't work. In those situations, you can use ps -ef | grep smon
to locate the Oracle system-monitor process, and then use the Linux/Unix kill
command to terminate the instance. When you kill a required Oracle background process, this causes the instance to abort. Obviously, you should use an operating system kill
command only as a last resort.
DATABASE VS. INSTANCE
In some situations, you may want to use the Database Configuration Assistant (dbca
) utility to create a database via a response file because using dbca
in graphical mode isn't feasible. This may be due to slow networks or the unavailability of X software. To create a database using dbca
in silent mode, perform the following steps:
dbca.rsp
file.dbca.rsp
file.dbca.rsp
file for your environment.dbca
utility in silent mode.First, navigate to the location in which you copied the Oracle database installation software, and use the find
command to locate dbca.rsp
:
$ find . -name dbca.rsp
./orainst/11.2.0.1/database/response/dbca.rsp
Copy the file so you're not modifying the original (so you always have a good original file):
$ cp dbca.rsp mydb.rsp
Now, edit the mydb.rsp
file. You minimally need to modify the following parameters: GDBNAME
, SID
, SYSPASSWORD
, SYSTEMPASSWORD
, SYSMANPASSWORD
, DBSNMPPASSWORD
, DATAFILEDESTINATION
, STORAGETYPE
, CHARACTERSET
, and NATIONALCHARACTERSET
. Shown next is an example of modified values in the mydb.rsp
file:
[CREATEDATABASE]
GDBNAME = "ORC11G"
SID = "ORC11G"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "foo"
SYSTEMPASSWORD = "foo"
SYSMANPASSWORD = "foo"
DBSNMPPASSWORD = "foo"
DATAFILEDESTINATION ="/ora01/ORC11G"
STORAGETYPE="FS"
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET= "UTF8"
Next, run the dbca
utility in silent mode using a response file:
$ dbca -silent -responseFile /ora01/orainst/11.2.0.1/database/response/mydb.rsp
You should see output such as
Copying database files
1% complete
...
Creating and starting Oracle instance
...
62% complete
Completing Database Creation
...
100% complete
Look at the log file
"/oracle/app/oracle/cfgtoollogs/dbca/ORC11G/ORC11G.log" for further details.
If you look in the log files, notice that the dbca
utility uses the rman
utility to restore the datafiles used for the database. Then, it creates the instance and performs post-installation steps. On a Linux server, you should also have an entry in the /etc/oratab
file for your new database.
Many DBAs launch dbca
and configure databases in GUI mode, but a few exploit the options available to them using the response file. With effective utilization of the response file, you can consistently automate the database-creation process. You can modify the response file to build databases on Automatic Storage Management (ASM) and even create RAC databases. In addition, you can control just about every aspect of the response file, similar to launching the dbca
in graphical mode.
If you have an unused database that you need to drop, you can use the DROP DATABASE
statement to accomplish this. Doing so removes all datafiles, control files, and online redo logs associated with the database.
Needless to say, use extreme caution when dropping a database. Before you drop a database, ensure that you're on the correct server and are connected to the correct database. On a Linux/Unix system, issue the following OS command from the operating system prompt:
$ uname -a
Next, connect to SQL*Plus, and be sure you're connected to the database you want to drop:
SQL> select name from v$database;
After you've verified that you're in the correct database environment, issue the following SQL commands from a SYSDBA
privileged account:
SQ> shutdown immediate;
SQL> startup mount exclusive restrict;
SQL> drop database;
Caution Obviously, you should be careful when dropping a database. You aren't prompted when dropping the database and, as of this writing, there is no UNDROP ACCIDENTALLY DROPPED DATABASE
command. Use extreme caution when dropping a database, because this operation removes datafiles, control files, and online redo log files.
The DROP DATABASE
command is useful when you have a database that needs to be removed. It may be a test database or an old database that is no longer used. The DROP DATABASE
command doesn't remove old archive redo log files. You must manually remove those files with an operating system command (such as rm
in Linux/Unix or del
at the Windows command prompt). You can also instruct RMAN to remove archive redo log files.
Sometimes, when you're creating new databases, this question arises: How many databases should you put on one server? One extreme is to have only one database running on each database server. This architecture is illustrated in Figure 2–2, which shows two different database servers, each with its own installation of the Oracle binaries. This type of setup is profitable for the hardware vendor but in many environments isn't an economical use of resources.
Figure 2–2. Architecture with one server per database
If you have enough memory, CPU, and disk resources, then you should consider creating multiple databases on one server. You can create a new installation of the Oracle binaries for each database or have multiple databases share one set of Oracle binaries. Figure 2–3 shows a configuration using one set of Oracle binaries that's shared by multiple databases on one server. Of course, if you have requirements for different versions of the Oracle binaries, you must have multiple Oracle homes to house those installations.
Figure 2–3. Multiple databases sharing one set of Oracle binaries on a server
If you don't have the CPU, memory, or disk resources to create multiple databases on one server, consider using one database to host multiple applications and users, as shown in Figure 2–4. In environments like this, be careful not to use public synonyms, because there may be collisions between applications. It's typical to create different schemas and tablespaces to be used by different applications in such environments.
Figure 2–4. One database used by multiple applications and users
You must consider several architectural aspects when determining whether to use one database to host multiple applications and users:
Table 2–7 describes the advantages and disadvantages of these architectural considerations regarding how to use Oracle databases and applications.
Table 2–7. Oracle Database Configuration Advantages and Disadvantages
Configuration | Advantages | Disadvantages |
One database per server | Dedicated resources for the application using the database. Completely isolates applications from each other. | Most expensive. Requires more hardware. |
Multiple databases and Oracle homes per server | Requires fewer servers. | Multiple databases competing for disk, memory, and CPU resources. |
Multiple databases and one installation of Oracle binaries on the server | Requires fewer servers. Doesn't require multiple installations of the Oracle binaries. | Multiple databases competing for disk, memory, and CPU resources. |
One database and one Oracle home serving multiple applications | Least expensive. | Multiple databases competing for disk, memory, and CPU resources. Multiple applications dependent on one database. One single point of failure. |
After you've installed the Oracle binaries, you can create a database. Before creating a database, make sure you've correctly set the required operating system variables. You also need an initialization file and need to pre-create any necessary directories. You should carefully think about which initialization parameters should be set to a nondefault value. In general, I try to use as many default values as possible and only change an initialization parameter when there's a good reason.
This chapter focused on using SQL*Plus to create databases. This is an efficient and repeatable method for creating a database. When you're crafting a CREATE DATABASE
statement, consider the size of the datafiles and online redo logs. You should also put some thought into how many groups of online redo logs you require and how many members per group.
I've worked in some environments where management dictated the requirement of one database per server. Usually that is overkill. A fast server with large memory areas and many CPUs should be capable of hosting several different databases. You have to determine what architecture meets your business requirements when deciding how many databases to place on one box.
After you've created a database, the next step is to configure the environment so you can efficiently navigate, operate, and monitor the database. These tasks are described in the next chapter.
3.128.78.30