Get Oracle DBA Training

Topics for the Oracle DBA Training Course(Online Training, Live Sessions).

1	Intro
2	SQL Plus and iSQL Plus
3	Installing Oracle Software and Creating a Database
4	Oracle Database and Instance Architecture
5	Startup, Shutdown, and the Initialization File
6	Managing Tables, Views, Constraints and Sequences
7	Data Moving Utilities
8	PL/SQL Basics
9	Triggers, procedures, functions
10	Packages
11	Managing undo
12	Tablespaces
13	Managing users
14	Oracle net services
15	Oracle's shared server option
16	Oracle security
17	Locks, latches, and waits
18	Performance
19	Backup and Recovery Techniques
20	Backups using RMAN
21	Database recovery
22	Enterprise Manager

TABLESPACES

A tablespace can contain many tables, indexes, and so forth. It has a size that is
initially assigned by the DBA. As data is inserted and updated, the amount of free
space in the tablespace diminishes. As the space used reaches 100%, the DBA
may choose to archive rows or expand the tablespace by extending one or more
existing datafiles or by adding a new datafile to the tablespace

CREATE A TABLESPACE
You create a new tablespace with the CREATE TABLESPACE command, which has
the following syntax:
CREATE TABLESPACE <tablespace_name>
DATAFILE <datafile_name> SIZE <size_of_datafile>
DEFAULT STORAGE
(INITIAL <size_of_initial_extent>
NEXT <size_of_next_extent>
MINEXTENTS <minimum_number_of_extents>
MAXEXTENTS <maximum_number_of_extents>
PCTINCREASE <extent_growth_rate>
)
PERMANENT;

DELETE A TABLESPACE

DROP TABLESPACE <tablespace_name>;

ORACLE NETWORKING: CONFIGURING BASIC NET8/SQL*NET COMPONENTS

One of the important components of Oracle networking is the Oracle listener.
The listener is the process responsible for receiving database connection requests
and redirecting them to another process, which in turn becomes the main point
of contact between the client and the database. The process that ultimately services
user requests is called a server process. It is the responsibility of the server
process to perform such tasks as reading data from disk into the Database Buffer
Cache and sorting query results.
There are two possible configurations for the server process. In the dedicated server
configuration, one server process is started for each client connection to the database.
This server process is then dedicated to serving only that connection’s
request. In the multi-threaded server configuration, shared servers perform most of
the same tasks as the dedicated server. The main difference is that, where a dedicated
server only supports one client, a shared server supports one or more
clients. The ability to support multiple client connections can be useful if the
number of requests made by each client connection is low. Therefore, a dedicated
server configuration would be useful for operations such as a batch load of data
into the database, whereas a multi-threaded server configuration would be more
appropriate for supporting many clients, each of which periodically performs
transactions on the database that affect small amounts of data.
The information needed to establish database connections is stored in several
configuration files. The following two configuration files will be discussed in this
chapter:
• tnsnames.ora
• listener.ora



template for two TNS service name definitions is shown below:
####################################
# FILENAME: tnsnames.ora #
# LAST MODIFIED: March 24, 2000 #
####################################
example00 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = development)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = db00)
)
)
example01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = production)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = db01)
)
)






####################################
# FILENAME: listener.ora #
# LAST MODIFIED: March 24, 2000 #
####################################
LISTENER =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = production)
(PORT = 1521)
)
)
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
LOG_DIRECTORY_LISTENER = e:\Oracle\Ora81\network\log
LOG_FILE_LISTENER = listener
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = db00)
(ORACLE_HOME = e:\Oracle\Ora81)
)
)

Oracle server Default DBA Accounts

SYS CHANGE_ON_INSTALL SYS owns the data dictionary tables, the
place where all the information about
database objects is stored. You should only
connect to the database as SYS if you are
explicitly instructed to do so in this book
or in the Oracle documentation.
SYSTEM MANAGER SYSTEM was originally created to maintain
database objects that support the Oracle
Developer suite of tools. SYSTEM can perform
most of the actions that SYS can and
will be used most of the time for the exercises
in this book.
INTERNAL ORACLE (in some INTERNAL is not truly a database
environments, account, but is used when performing
INTERNAL) certain maintenance work against—and
starting and stopping—the database. Note
that for this book’s exercises, if you are
prompted for a password when connecting
INTERNAL use the password ORACLE (as
shown in the createdb00_01.sql script
below). Oracle states that connecting
INTERNAL will no longer be supported as
of Oracle version 8.2. From then on, you
will connect as SYS AS SYSDBA.

Oracle Server statup modes

Startup Modes

startup nomount Starts the Instance—Creates the SGA and runs the background
processes after reading the parameters in the
init.ora (Instance configuration) file.
startup mount Starts the Instance and mounts the database—Reads the
control file and identifies the Database files.
startup open Starts the Instance and allows access to the Database for
users via the Oracle Server.
startup force Performs a shutdown abort, followed by a startup open.
Note that startup force is an option, not a mode.

CREATING YOUR DATABASE

The Optimal Flexible Architecture (OFA) model was designed by Oracle to assist
users in laying out their databases in a way that would support high performance
and ease of maintenance. The Oracle white paper by Cary Millsap entitled “The
OFA Standard” contains the specification in detail. For the purposes of this book,
you will be making use of the characteristics of OFA that identify the logical location
of the Oracle installation and associated files. Note that the Oracle Installer
does not require that Oracle be installed according to OFA specifications and you
will find different Oracle installations at varying levels of OFA compliance at different
sites.


1) Create the admin directory if it does not already exist.
2) Create a directory below admin giving it the name of your database. For
organizational purposes of this book, your database will be referred
to as db00.
Allowing db00 to represent the name of your database, create a directory called
db00 below the admin directory. If you wish to name the database something
other than db00, that is fine.

3) Create the following directories below db00:
• adhoc
• adump
• arch
• bdump
• cdump
• create
• exp
• pfile
• udump


You need to find a location for the physical files that will make up your Oracle
Database. For OFA compliance, Database files are put in directories as
MountPoint/oradata/DatabaseName.
For instance, in UNIX, you might see the following directory structures:
/u01/oradata/db00
/u02/oradata/db00
/u03/oradata/db00