Pro Oracle Database 11g Administration (13 page)
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 10
g
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
Step 4: Create the Database
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
32
CHAPTER 2 ■ IMPLEMENTING A DATABASE
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:
33
CHAPTER 2 ■ IMPLEMENTING A DATABASE
SQL> show parameter background_dump_dest
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:
• No rollback information is generated.
• No coalescing is required.
• Contention for resources in the data dictionary is reduced.
• Recursive space management is reduced.
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
Best Practice
Reasoning
Make the SYSTEM tablespace locally managed.
Doing this enforces that all tablespaces created in
this database are locally managed.
Use the REUSE clause with caution. Normally, you
The REUSE clause instructs Oracle to overwrite
should use it only when you’re re-creating a
existing files, regardless of whether they’re in use.
database.
This is dangerous.
Create a default temporary tablespace with TEMP
Every user should be assigned a temporary
somewhere in the name.
tablespace of type TEMP, including the SYS user. If
you don’t specify a default temporary tablespace,
the SYSTEM tablespace is used. You
never
want a user
to be assigned a temporary tablespace of SYSTEM. If
your database doesn’t have a default temporary
tablespace, use the ALTER DATABASE DEFAULT
TEMPORARY TABLESPACE statement to assign one.
34
CHAPTER 2 ■ IMPLEMENTING A DATABASE
Best Practice
Reasoning
Create a default permanent tablespace named
This ensures that users are assigned a default
USERS.
permanent tablespace other than SYSTEM. If your
database doesn’t have a default permanent
tablespace, use the ALTER DATABASE DEFAULT
TABLESPACE statement to assign one.
Use the USER SYS and USER SYSTEM clauses to
Doing this creates the database with nondefault
specify nondefault passwords.
passwords for database accounts that are usually
the first targets for hackers.
Create at least three redo log groups with two
At least three redo log groups provides time for the
members each.
archive process to write out archive redo logs
between switches. Two members mirror the online
redo log members, providing some fault tolerance.
Name the redo logs something like redoNA.rdo.
This deviates slightly from the OFA standard, but
I’ve had files with the extension of .log
accidentally deleted more than once (it shouldn’t
ever happen, but it has).
Make the database name somewhat intelligent,
This helps you determine what database you’re
such as PAPRD, PADEV1, or PATST1.
operating in and whether it’s a production,
development, or test environment.
Use the ? variable when you’re creating the data
SQL*Plus interprets the ? as the directory
dictionary (see the section “Step 5: Create a Data
contained in the OS ORACLE_HOME variable. This
Dictionary”). Don’t hardcode the directory path.
prevents you from accidentally running scripts
from the wrong version of ORACLE_HOME.
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 /
35
CHAPTER 2 ■ IMPLEMENTING A DATABASE
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/
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.
Configuring and Implementing the Listener
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: 1. Configure the listener.ora file.
2. Start the listener.
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 =
37
CHAPTER 2 ■ IMPLEMENTING A DATABASE
(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_
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