Earn upto Rs. 9,000 pm checking Emails. Join now!

Enter your email address:

Delivered by FeedBurner

Friday, November 23, 2007

Database Operation

Introduction to Database Startup and Database Shutdown
An Oracle database may not always be available to all users. The database administrator can start up a database so that it is open. When a database is open, users can access the information that it contains. If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains.
Only a database administrator can open or close a database. Normal users do not have control over the current status of an Oracle database. Security for database startup and shutdown is controlled via connections to Oracle with administrator privileges.

Connecting with Administrator Privileges
Database startup and shutdown are powerful administrative options and are protected by the ability to connect to Oracle with administrator privileges.
Depending on the operating system, one of the following prerequisites is required to connect to Oracle with administrator privileges:

The user's operating system account has operating system privileges that allow him/her to connect using administrator privileges.
The user is granted the SYSDBA or SYSOPER privileges and the database uses password files to authenticate database administrators.
The database has a password for the INTERNAL login, and the user knows the password.
In addition, users can connect with administrator privileges only to dedicated servers (not shared servers).
When you connect with administrator privileges, you are placed in the schema owned by SYS.

These requirements provide extra security to prevent unauthorized users from starting up or shutting down any Oracle databases.

For more information about password files and authentication schemes for database administrators, see Chapter 17, "Database Access".

Additional Information: For information on how administrator privileges work on your operating system, see your operating system-specific Oracle documentation.


Database and Instance Startup
There are three steps to starting a database and making it available for systemwide use:

1. Start an instance.

2. Mount the database.

3. Open the database.
Starting an Instance
Starting an instance includes the allocation of an SGA -- a shared area of memory used for database information -- and creation of the background processes. Instance startup anticipates a database being mounted by the instance. If an instance has been started but not yet mounted, no database is associated with these memory structures and processes.
Before an instance actually is created, Oracle reads a parameter file, which determines the instance initialization. This file includes parameters that control such things as the size of the SGA, the name of the database to which the instance can connect, and so on.

Note: See "Parameter Files" for more information about parameter files. See Chapter 9, "Memory Structures and Processes", for more information about the terms "SGA", "background processes", and "instance".

Restricted Mode of Instance Startup
You can start an instance in or alter an existing instance to be in restricted mode. This limits connections to only those users who have been granted the RESTRICTED SESSION system privilege.

Forcing an Instance to Startup in Abnormal Situations
In unusual circumstances, an instance might not be shut down "cleanly", for example, one of the instance's processes might not be killed. In such situations, the database might return an error during normal instance startup. To resolve this problem, the database administrator must kill all remnant Oracle processes of the previous instance and then start the new instance.

Mounting a Database
Mounting a database associates a database with a previously started instance. After an instance mounts a database, the database remains closed and is accessible only to database administrators. The database administrator might want to start an instance and only mount the database to complete specific maintenance operations.
When an instance mounts a database, the instance finds the control files and opens them. The control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance. Once the database's control files are opened, Oracle reads them to get the names of the database's datafiles and redo log files.

Modes of Mounting a Database with the Parallel Server
If Oracle allows multiple instances to mount the same database concurrently, the DBA can choose whether to run the database in exclusive or parallel mode.
Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that instance can mount the database. Versions of Oracle that do not support the Parallel Server option only allow an instance to mount a database in exclusive mode.

Parallel Mode If the first instance that mounts a database is started in parallel mode (also called "shared mode"), other instances that are started in parallel mode can also mount the database. The number of instances that can mount the database is subject to a predetermined maximum. See Oracle7 Parallel Server Concepts & Administration for more information about the use of multiple instances with a single database.

Opening a Database
Opening a mounted database makes the database available for normal database operations. Any valid user can connect to the database and access its information once it is open. Usually, the database administrator opens the database to make it available for general use.
When you open the database, Oracle opens the online datafiles and online 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. See "Online and Offline Tablespaces" .

If any of the datafiles or redo log files are not present when you attempt to open the database, Oracle returns an error. You must perform recovery on a backup of any damaged or missing database files before you can open the database.

Instance Recovery
If the database was shut down either because the database administrator aborted its instance or because a power failure occurred while the database was running, Oracle automatically performs instance recovery when the database is reopened. See Chapter 24, "Database Recovery", for complete information concerning instance recovery.

Rollback Segment Acquisition
As an instance opens a database, the instance attempts to acquire one or more rollback segments. See "Instances and Types of Rollback Segments" .

Resolution of In-Doubt Distributed Transaction
Assume that a database is abruptly shut down (for example, a power failure occurs or the instance is aborted) and one or more distributed transactions have not been committed or rolled back. When you reopen the database and instance recovery is complete, the RECO background process automatically, immediately, and consistently resolves any distributed transactions that have been committed or rolled back. For information about distributed transactions, see Chapter 21, "Distributed Databases". For information about recovery from failures associated with distributed transactions, see Oracle7 Server Distributed Systems, Volume I.


Database and Instance Shutdown
There are three steps to shutting down an instance and the database to which it is connected:

1. Close the database.

2. Dismount the database.

3. Shut down the instance.
Oracle automatically performs all three steps when an instance is shut down.

Closing a Database
The first step of database shutdown is closing the 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. After this operation, Oracle closes all online datafiles and online redo log files. Any offline datafiles of any offline tablespaces will have been closed already. When you subsequently reopen the database, the tablespace that was offline and its datafiles remain offline and closed, respectively. The control files remain open after a database is closed but still mounted.

Closing the Database by Aborting the Instance
In rare emergency situations, you can abort the instance of an open database to close and completely shut down the database instantaneously. This process is fast because the operation of writing all data in the buffers of the SGA to the datafiles and redo log files is skipped. The subsequent reopening of the database requires instance recovery, which Oracle performs automatically.
Note: If a system crash or power failure occurs while the database is open, the instance is, in effect, "aborted", and instance recovery is performed when the database is reopened.

Dismounting a Database
The second step accomplished during database shutdown is dismounting or disassociating the database from an instance. After you dismount a database, only an instance remains in the memory of your computer.
After a database is dismounted, Oracle closes the control files of the database.

Shutting 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.

Abnormal Instance Shutdown
In unusual circumstances, shutdown of an instance might not occur cleanly; all memory structures might not be removed from memory or one of the background processes might not be killed. When remnants of previous instances exist, subsequent instance startup most likely will fail. To handle this problem, the database administrator can force the new instance to start up by first removing the remnants of the previous instance and then starting a new instance, or by issuing a SHUTDOWN ABORT statement.


Parameter Files
To start an instance, Oracle must read a parameter file.A parameter file is a text file containing a list of instance configuration parameters. You set these parameters to particular values and to initialize many of the memory and process settings of an Oracle instance. Among other things, the parameters of this file tell Oracle the following:

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 online redo log files
the names and locations of the database's control files
the names of private rollback segments in the database
An Example of a Parameter File
The following is an example of a typical parameter file:

db_block_buffers = 550
db_name = ORA7PROD
db_domain = US.ACME.COM
license_max_users = 64
control_files = filename1, filename2
log_archive_dest = c:\logarch
log_archive_format = arch%S.ora
log_archive_start = TRUE
log_buffer = 64512
log_checkpoint_interval = 256000
# rollback_segments = rs_one, rs_two
Oracle treats string literals defined for National Language Support (NLS) parameters in the file as if they are in the database character set.

Most parameters belong to one of the following groups:

parameters that name things (such as files)
parameters that set limits (such as maximums)
parameters affecting capacity, called variable parameters (such as the DB_BLOCK_BUFFERS parameter, which specifies the number of data blocks to allocate in the computer's memory for the SGA)
Changing Parameter Values
The database administrator can adjust variable parameters to improve the performance of a database system. Exactly which parameters most affect a system is a function of numerous database characteristics and variables

No comments:


Total Pageviews