Transcript ADVANCED SQL AND PL/SQL TOPICS

Database Instance startup and
shutdown
A Guide to Oracle9i
1
client/server Architecture: Distributed
Processing
• Distributed Processing uses More than one
processor to divide the processing for a set of related
jobs
• It reduces the load on a single processor
• An Oracle database system can easily take
advantage of Distributed Processing by using
client/server Architecture
• In this architecture the database system is divided
into two parts : a. client portion
b. server portion
A Guide to Oracle9i
2
client/server Architecture: Distributed
Processing
• The Client Portion interacts with a user through the
keyboard, screen, mouse
• The client portion has no data responsibilities , it
concentrate on requesting and presenting data
managed by the server portion
• The server portion runs oracle software and handles
the functions required for shared data access
• The server receives and processes the SQL
statements that comes from the client applications
• The computer that manages the server portion
should have large disk capacity for fast processors
A Guide to Oracle9i
3
Introduction to an Oracle Instance
• Every running Oracle database is associated with an
Oracle instance
• When a database is started on a database server,
Oracle allocates a memory area called the System
Global Area (SGA) and starts one or more Oracle
processes
• This combination of the SGA and the Oracle
processes is called an Oracle instance.
A Guide to Oracle9i
4
Description of an instance
A Guide to Oracle9i
5
The Instance and the Database
• After starting an instance, Oracle associates the
instance with the specified database. This is a
mounted database
• Multiple instances can run concurrently on the same
computer, each accessing its own physical database
• Security for database startup and shutdown is
controlled through connections to Oracle with
administrator privileges. Normal users do not have
control over the current status of an Oracle database.
• When a database is closed, users cannot access the
information that it contains.
A Guide to Oracle9i
6
Parameter Files
• To start an instance, Oracle must read parameter
files
• These files contain a list of configuration parameters
for that instance and database.Example
–
–
–
–
–
–
Parameters that name things, such as files
Parameters that set limits, such as maximums
The name of the database for which to start up an instance
How much memory to use for memory structures in the SGA
What to do with filled redo log files
The names and locations of the database control files
A Guide to Oracle9i
7
Overview of Instance and
Database Startup
• The three steps to starting an Oracle database and
making it available for system wide use are:
– Start an instance.
– Mount the database.
– Open the database.
A Guide to Oracle9i
8
How an Instance Is Started
• When Oracle starts an instance, it reads the
parameter file to determine the values of initialization
• Then, it allocates an SGA, which is a shared area of
memory used for database information and creates
background processes
• At this point, no database is associated with these
memory structures and processes.
A Guide to Oracle9i
9
How a Database Is Mounted
• The instance mounts a database to associate the
database with that instance
• To mount the database, the instance finds the
database control files and opens them
• Control files are specified in the CONTROL_FILES in
the parameter file used to start the instance
• Oracle then reads the control files to get the names
of the database's datafiles and redo log files.
• At this point, the database is still closed and is
accessible only to the database administrator.
A Guide to Oracle9i
10
What Happens When You Open a
Database
• Opening a mounted database makes it available for
normal database operations. Any valid user can
connect to an open database and access its
information.
• When you open the database, Oracle opens the
online datafiles and redo log files. If a tablespace was
offline when the database was previously shut down,
the tablespace and its corresponding datafiles will still
be offline when you reopen the database
A Guide to Oracle9i
11
Instance Recovery
• If the database was last closed abnormally, because
of a power failure, then Oracle automatically performs
recovery when the database is reopened.
A Guide to Oracle9i
12
Overview of Database and Instance
Shutdown
• The three steps to shutting down a database and its
associated instance are:
– Close the database.
– Unmount the database.
– Shut down the instance.
A Guide to Oracle9i
13
Close a Database
• When you close a database, Oracle writes all
database data and recovery data in the SGA to the
datafiles and redo log files, respectively
• Next, Oracle closes all online datafiles and redo log
files
• At this point, the database is closed and inaccessible
for normal operations. The control files remain open
after a database is closed but still mounted
A Guide to Oracle9i
14
Unmount a Database
• After the database is closed, Oracle unmounts the
database to disassociate it from the instance
• At this point, the instance remains in the memory of
your computer
• After a database is unmounted, Oracle closes the
control files of the database
A Guide to Oracle9i
15
Shut Down an Instance
• The final step in database shutdown is shutting down
the instance. When you shut down an instance, the
SGA is removed from memory and the background
processes are terminated.
A Guide to Oracle9i
16
Database Backup and Recovery
• Important database administration support tasks include
– Backup: creating a copy of the database files
– Recovery: restoring the database to a working state after a
hardware, software, Network, process or system malfunction
• Backup operations on an Oracle database can be classified as:
– Offline (cold) backups
– Online (hot) backups
A Guide to Oracle9i
17
Database Backup and Recovery
• Offline Backup – (cold backup) requires shutting down the
database, then copying all of the database files to an alternate
location
• Online Backup – (hot backup) involves backing up critical
database files while the instance is running, as well as creating
an ongoing archive of database changes so the DBA can
restore the database to its state at any point in time
A Guide to Oracle9i
18