Managing the Oracle RDBMS . Today you will look at the basics, including: Setting up Enterprise Manager Using Enterprise Manager Using Server Manager Starting up and shutting down the Oracle instance . The Oracle DBA. some of the key duties and responsibilities:
some of the key duties and responsibilities:
• Central launching point for all applications
• The Console can be run either in thin mode via the web or as a fat client.
• Can be lanched either standalone or through Oracle Management Server
Example: Launch the console and add a database to it.
3. Add Databases to tree: The Add Database to Tree dialog allows you to either add adatabase manually by supplying the Hostname, Port number, SID and Net Service name oradd selected databases from your local tnsnames.ora file.
5. Right click your working database and select Connect
6. Enter the username, password and service name for the database and click OK.
Standard applications that can be launched from the Console:
• Instance Manager
• Security Manager
• Storage Manager
• Schema Manager
• SQL*Plus Worksheet
• Security Manager: Used to manage users and privileges
• Storage Manager: Maintains tablespaces, data files, rollback segments and log groups
• Schema Manager: Used to create and maintain objects such as tables, indexes, and views
• SQL*Plus Worksheet: Provides the capability to issue SQL statements against any database
• Launch the Console in standalone mode
• Expand the databases folder and expand the relevant database
• Select tools such as Instance Manager, Schema Manager
monitor other Oracle processes to provide increased parallelism for better performance and reliability.
statements. The server process executes the SQL statements sent from the user
Nondatabase files are used to configure the instance, authenticate privileged users, and recover the database in the event of a disk failure.
1 Start an instance.
2 Mount the database.
3 Open the database.
1 Close the database.
2 Dismount the database.
3 Shut down the instance.
When a database is closed, users cannot access it.
1 Launch the Oracle Enterprise Manager Console:
Start—>Programs—>Oracle – ora90 Home
—>Enterprise Manager Console
2 Enter the administrator system, the password manager, and the management server your PC hostname, and click OK.
3 Click the second drawer on the left side of the console and select Instance Manager.
5 Your initialization parameters should now be displayed in the right pane of the window. Click Save and name your configuration before clicking OK to save a stored configuration.
6 Expand the stored configuration folder to verify that your parameters were saved.
• Size the System Global Area (SGA) components to optimize performance.
• Set database and instance defaults.
• Set user or process limits.
• Set limits on database resources.
• Define various physical attributes of the database,
such as the database block size.
• Specify control files, archived log files, the ALERT
file, and trace file locations.
• Specify the values in the following format: keyword=value.
• All parameters are optional.
• The server has a default value for each parameter. This value may be operating
system dependent, depending on the parameter.
• Parameters can be specified in any order.
• Comment lines begin with the # symbol.
• Enclose parameters in double quotation marks to include character literals.
• Additional files can be included with the keyword IFILE.
• If case is significant for the operating system, then it is also significant in filenames.
• Multiple values are enclosed in parentheses and separated by commas.
Note: Develop a standard for listing parameters; either list them alphabetically or
group them by functionality.
• Reading the parameter file initsid.ora
• Allocating the SGA
• Starting the background processes
• Opening the ALERT file and the trace files
• Renaming data files
• Enabling and disabling redo log archiving options
• Performing full database recovery
• Associating a database with a previously started instance
• Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and status of the data files and redo log files (However, no checks are performed to verify the existence of the data files and online redo log files at this time.)
• Opening the online data files
• Opening the online redo log files
database. When the database is closing, the Oracle server writes the buffer cache
changes and redo log buffer cache entries to the data files and online redo log files.
After this operation, the Oracle server closes all online data files and online redo log
files. The control files remain open while a database is closed but still mounted.
instance. After you dismount a database, only an instance remains.When a database is dismounted, the Oracle server closes its control files
the instance. When you shut down an instance, the ALERT file and the trace files are
closed, the SGA is deallocated, and the background processes are terminated.
STARTUP [FORCE] [RESTRICT] [PFILE= filename]
[OPEN [RECOVER][ database]
activities but does not allow user access to
background processes but does not allow
access to the database
used to configure the instance
performing a normal startup
• Any database can be opened as a read-only database
• A read-only database can be used to:
– Execute queries
– Execute disk sorts using locally managed tablespaces
– Take data files offline and online, not tablespaces
– Perform recovery of offline data files and tablespaces
1 Query the accounts to check the account balances.
2 Execute an INSERT command to transfer the funds to the new bank account.
3 Execute a DELETE command to remove the funds from the old bank account.
4 Execute a COMMIT to finish the transaction successfully.
5 Disconnect from the Oracle server.
These views are called dynamic performance views because they are continuously updated while a database is open and in use.
• Are maintained by the Oracle server and continuously updated
• Contain data about disk and memory structures
• Contain data that is useful for performance tuning
• Have public synonyms with the prefix V$
SHOW PARAMETER command.
SQL> SHOW PARAMETER control
NAME TYPE VALUE
------------------------------- ------- --------------------
control_file_record_keep_time integer 7
control_files string /DISK1/control01.con
current settings of any parameter.
SESSION users who do not have the privilege can log on
SQL> SELECT logins FROM v$instance;
1 row selected.
PMON to perform the following steps upon execution:
• Roll back the user’s current transaction
• Release all currently held table or row locks
• Free all resources currently reserved by the user
• Trace files can be written by server and background processes.
• The Oracle server dumps information about errors in trace files.
• The ALERT file consists of a chronological log of messages and errors.
• Server process tracing can be enabled or disabled by:
– An ALTER SESSION command
– The parameter SQL_TRACE
Oracle creates one.
SQL>ALTER SESSION SET sql_trace=TRUE;
A user trace file is produced by the user process connected to the Oracle server through the server process.
• A user trace file contains statistics for traced SQL
statements or user error messages.
• It is created when a user encounters user session errors.
• It can also be generated by you or a server process.
• Its location is defined by USER_DUMP_DEST.
• Its size is defined by MAX_DUMP_FILE_SIZE and
defaults to 10M.
• The MAX_DUMP_FILE_SIZE and USER_DUMP_DEST parameters are
dynamic initialization parameters.
• On UNIX, the ALERT file is named alert_ SID.log and is located in the
$ORACLE_HOME/rdbms/log directory by default.
• On Windows NT, the ALERT file is named SIDalrt.log and is by default located
in the %ORACLE_HOME%\RDBMS80\TRACE directory in Oracle8. If Oracle8i, it is
located in the %ORACLE_HOME%\ADMIN\ SID\BDUMP directory.
It is important to check the ALERT file regularly to detect problems before they become serious.
The following information is logged in the ALERT file:
• All internal errors (ORA-00600) and block corruption errors
• Operations that affect database structures and parameters, as well as commands,such as STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
• The values of all nondefault initialization parameters at the time the instance starts