Files for Loading into External Tables
External tables
allow direct access of data located in the
operating system level files by using the SQL
interface within the database. It is a way of
reading and writing files into and out of the
database. Note that one cannot write to an
existing external table.
Data stored in operating system level
files (ASCII filer) can be accessed as if they
are a table with rows and columns. Joins and
views can be constructed with data in the O/S
file and logical database tables.
For all practical purposes, external tables act
the same as the usual tables; however, the data
is not stored with the Oracle data files.
External tables
are a great way to load the data into a database
and do data processing. Inserts, updates, and
index creation are not allowed on an external
table.
A DROP TABLE statement issued against an
external table removes the table metadata only;
the operating system file is not removed.
There is no restriction as to where the external
table data file has to be located. In a RAC
database system, it can be located on the local
file system or on shared disk. For the sake of
concurrent access, it becomes more meaningful to
keep the external table file on a shared
storage. This allows for transparent access to
the external table so that any instance in the
RAC database should be able to read it at the
operating system level (ASCII). This is possible
only if the external table file is located on a
shared disk.
Oracle Cluster Registry (OCR)
The OCR contains
cluster and database configuration information
for RAC and Cluster Ready Services
(CRS) such as the cluster node list, instance to
node mapping, and CRS application resource
profiles.
The OCR is a shared file located in a cluster
file system. When not using a cluster file
system, the OCR file can be located on a shared
raw device in UNIX-based systems or a shared
logical partition in Windows environments. If
more than one database is created on the
cluster, they all share the same Oracle cluster
registry.
Voting Disk
The voting disk file must reside on shared
storage.
The voting disk file manages information
for node membership.
Oracle recommends using multiple voting
disk files.
ORACLE_HOME Files (Oracle Binaries)
Typically, every instance in Oracle 11g RAC will
have its own ORACLE_HOME and a set of exclusive
binaries. However, the Oracle binaries are
located either on a local file system or on a
clustered file system. Locating the Oracle home
(binaries) on a clustered file system provides
easier management by keeping a single copy of
Oracle home supporting all the instances.
A common Oracle home for multiple instances has
some advantages because it helps to easily
expand the nodes and shrink the nodes as needed.
It helps the dynamic addition and expansion of
nodes without bothering with a fresh install of
the Oracle binaries for the new instance. This
feature is useful for large clusters, and it
fits into the grid strategy of easier addition
and reduction of computing resources.
UNDO Tablespace Files
UNDO tablespaces are special tablespaces that
have system undo segments. They contain before
images of blocks involved in uncommitted
transactions. As such, they are the primary
support structure allowing a transaction to
rollback if the decision is made to not commit
the transaction.
Tables and indexes cannot be created in
the undo tablespace.
A database can have more than one undo
tablespace, but only one can be used at one
time. Automatic undo management is the default
mode for an 11g database.
DBCA will automatically create an undo
tablespace named UNDOTBS1.
