Oracle for the System Administrator

Glossary

Term	Definition
Archiver	This process copies redo logs to another location before they are overwritten, the database must be in archive log mode.
Automatic Undo Mode
Base Tables	Tables that are used in a view.
Checkpoint	Event that writes dirty buffers to disk and updates the headers of the control and datafiles.
Cluster	Groups of one or more tables physically stored together because they share common columns
Control File	Contains entries about the physical structure of the database, such as database name, names and locations of datafiles and redo log files, timestamp of database creation.
Data Dictionary
Data Manipulation Language (DML)	Insert, update and delete SQL statements.
Data Segment	All table data is stored in the extents of the data segment. For a partitioned table each partition has a data segment. Each cluste has a data segment.
Database	Physical files that store data. This is what an instance provides access to.
Database Writer Process (DBWn)	Background process that determines when to write datafile from memory to physical disk
Datablocks	Database data is stored in datablocks - specified by initialization parameter DB_BLOCK_SIZE
Datafiles	Every database consists of one or more datafiles which store the data of logical database structures.
Exclusive Password	The password file can be used only for a single database.
Extents	A specific number of contiguous datablocks, obtained in a single allocation, used to store a specific type of information. Extents are allocated as needed, so extents may not be contiguous.
Indexes	Optional structures associated with tables that increase the performance of data retrieval
Index Partition
Index Segment	Each index is stored in the extents of an index segment, each partititioned segment has an index segment.
Instance	Structures and processes necessary to access data in the database.
Listener
Logical Database Structures	Consists of schema objects, datablocks, extents, segments and tablespaces.
Log Writer (LGWR)	The log writer process writes blocks in the redo log buffer (sga) to the redo log files.
Manual Undo Mode
Multiplexed Redo Logfile	Multiple copies of redo logfiles that can be written to different disks.
Offline Tablespaces	Not accessible
Online Tablespaces	Accessible
Oracle 8i	Versions of Oracle that are greater than or equal to 8.1.5
Oracle 9i	Versions of Oracle that are greater than or equal to 9.0
$ORACLE HOME	Location of the binaries
PFILE	Locations of the parameter file (default is $ORACLE_HOME/dbs/init<SID>.ora)
Physical Database Structures	Includes datafiles, redo log files and control files
Program Global Area (PGA)	Memory that contains data and process information for a single process (not shared).
Process Monitor (Pmon)	Cleans up failed user processes and frees up resources used by those processes.
Redo Log File	Records all changes made to data, in the event of a failure that prevents modified data from being written to disk, the data changes can be applied from the redo logs. There are 2 or more redo log files.
Rollback Segment	Used if operating in manual undo mode to generate read consistent database information and to roll back uncommitted changes for users.
Rolling Forward	The process of applying redo logfiles to a database during a recovery operation.
Schema	A collection of database objects owned by the same user - includes tables, views and indexes.
Segments	A set of extents allocated for a certain logical structure, ie data, index, temporary, rollback, etc.
Shared Password	The password file can be shared across multiple databases.
SID	Name of the instance
SQL Plus	Program used to interact with the database
System Global Area (Shared Global Area)	Shared memory area used by the instance for database buffer cache, redo log buffer, shared pool, library cache and data dictionary cache.
System Monitor (Sysmon)	Performs crash recovery at database startup, and cleans up temporary files/ coalesces free space.
Table	Basic unit of storage, has columns and rows.
Tablespaces	Logical storage units that group the related logical structures of a database together. Each database is logically divided into one or more tablespaces.
Table Partition
Temporary Segment	Created by Oracle when an SQL statement needs a temporary work area to complete it's execution. It is temporary because upon completion of the statement, the extents are returned for the system to use.
V$	Comes from control files and memory, not from the database file.
Views	Customized presentation of data in one or more tables. Views do not contain data, they get their data from the base tables of the view.

Term	Definition
Archive	If archive mode is on then redo logs are archived by the ARCH process to backup media before they are overwritten
Hot Backup
Index	A list of pointers to the rows in a table which contain a particular key value
Instance	A means of accessing a database, it consists of background processes and memory SGA
Listener	Oracle process that listens for incoming database connections on the database server. The lsnrctl utility controls the process and configuration is via the listner.ora file.
Partition	A feature of Oracle that allows tables to be split into sub-tables
Recovery
Redo Log	Record of all changes made to an Oracle Database - makes it possible to replay SQL statements - NOT an undo
Rollback	The act of undoing changes that were made by the transaction
Standby	Physical copy of a database permanently in recovery mode - used to takeover if the working database fails.
System Global Area (SGA)	The memory that Oracle takes when the database is started
Table	Basic unit of the Oracle database - data is stored in tables
Transparent Network Substrate (TNS)	TNS maps database communications through the available networking

Files

$ORACLE_HOME/dbs/lk<instance> --> every instance on the maching will be listed here in a file name that starts with "lk"
$ORACLE_HOME/dbs/init.ora
$ORACLE_HOME/dbs/init<sid>.ora --> parameter file
$ORACLE_HOME/network/admin/tnsnames.ora
listener.ora

BACKGROUND_DUMP_DEST - location of debugging trace files generated by the background processes and the alert log file.
USER_DUMP_DEST - location of debugging trace files generated by the user session
CORE_DUMP_DEST - location of core dump files

Useful Procedures

Determining the version of the oracle database <-- This needs some more work

In listener.ora you will find the location $ORACLE_HOME for the database

Connecting to a database

Make sure that the basic information about the database is correct

$ORACLE_SID
$ORACLE_HOME
Check $PATH to make sure there are no other oracle binaries in the path that will run before the correct version of the binaries.

From svrmgrl

svrmgrl
connect internal

From sqlplus

sqlplus '/ as sysdba'

From sqlnet

Starting up a database

To startup just the instance (only memory and processes)

startup nomount

To startup the database without opening it (start the instance and mount the control files):

startup mount

Startup the database normally:

Set the environment

Manually set the $ORACLE_SID

export ORACLE_SID=xxxxxx

echo $ORACLE_SID

Source oraenv

. oraenv
Verify the instance name is correct

sqlplus '/ as sysdba'
startup or startup open
exit
lsnrctl start
lsnrctl status

Starting up the listener

lsnrctl start
lsnrctl status

Shutdown the Database

To perform a Normal Shutdown

export ORACLE_SID=xxxxxx
echo $ORACLE_SID
sqlplus '/ as sysdba'
shutdown or shutdown normal

To perform an Immediate shutdown

export ORACLE_SID=xxxxxx
echo $ORACLE_SID
sqlplus '/ as sysdba'
shutdown immediate

To Shutdown after current transaction

shutdown transactional

To Shutdown abruptly

shutdown abort

Creating a password file

orapwd

Renaming a database files (Useful if refreshing to different database)

alter database rename file "<old file name>" "<new file name>"
select * from v$datafile - ?
select * from v$recover_file - ?
alter database open -?

Viewing Information

select v$ <command>
show parameters <parameter>

Cloning a database manually

Copy the dbf's from the source datalocation to the destination location

Usually this is done from a hotbackup

Terminating a user session

SELECT username, sid, serial#, status FROM v$session WHERE username = 'JOHN';
ALTER SYSTEM KILL SESSION '<SID>, <SERIAL#>';

Installation

Download gz files
gunzip
cpio -idmv <cpiofilename.cpio>

Troubleshooting

Installation

Installing Oracle 8i on Red Hat 9 Workaround
export LD_ASSUME_KERNEL=2.2.5

Ping

From Unix or Windows
ping <server name>

Verifies that communications is established with the server.

Netstat

From Unix
netstat -aP tcp | grep 1521
This will show established connections to the listener port.

Tnsping

From Windows command prompt
tnsping <instance>

Shows that the database instance is listening and is being resolved properly in tnsnames.ora

tnsping reports: tns-03506 Failed to create address binding

tnsping uses the tnsnames.ora file. This file is extremely picky to typos, not enough parenthesis, etc. Even check for extra spaces where they shouldn't be.

Check for required processes

Pmon - Process Monitor

ps -ef|grep pmon

Smon - System Monitor

ps -ef|grep smon

Dbwr - Database writer

ps -ef | grep dbw

Lgwr - Redo Log writer

ps -ef|grep lgwr

Logs
<oracle binaries>/network/log/listener.log

Sql Plus

Toad (Oracle SQL Gui Client)

Servermgrl