Administering Oracle Database

As the database administrator, you must set up and maintain your Oracle Database to store all of your system data and ensure that you configure the database for optimal performance.

Oracle Database performance

Configure and tune your Oracle Database to ensure optimal performance. You can optimize the database for memory usage, storage allocation, and connections.

Recommendation: Enabling Automatic Shared Memory Management can help in efficiently using system memory.

Oracle connection parameters

Run your Oracle Database in dedicated server mode where you need one database connection (that are Oracle process) for each application connection (user process). In Oracle databases, the maximum number of connections that are allowed by the database is set by using the PROCESSES parameter in the initsid.ora file where sid is the system ID of your database.

Example

If you have only one Product Master instance that is connected to your database and the instance needs a maximum of 133 connections, then ensure that the PROCESSES parameter is set to at least 143, which is 10 more than your required maximum. You need 10 extra connections to account for Oracle background processes and some SQL PLUS connections. In addition, if you add another Product Master instance, which also requires 133 connections to the same database, then you should set the PROCESSES parameter to at least 276, where 143 accounts for the first instance and an extra 133 connections account for the second Product Master instance.

Oracle table spaces

Data that is entered into Product Master gets stored in the underlying database and is organized in different table spaces in the database. Because large amounts of data transactions occur, you must ensure that database is tuned optimally.

You can further improve the performance and availability of the database by monitoring the free space available to table spaces and by using temporary tables.

Configuration parameters for Oracle databases

To ensure optimal performance of your database, you can configure the parameters in database configuration.

Oracle uses configuration parameters to locate files and specify runtime parameters that are common to all Oracle products. All Oracle parameters are stored in the init.ora file of the registry.

Oracle Database health check

You can use the following Oracle tools to monitor your database:

Oracle Alerts: Monitoring table space usage.
Automatic Workload Repository (AWR) Reports: Monitoring various performance diagnostics.
Oracle Enterprise Manager for database: Monitoring state and workload.