Initialization Parameter File, Spfile vs Pfile, and Oracle Defined Roles

Note: The reference notes did not contain material on this topic. The following answer is based on standard Oracle Database concepts as taught in BSc CSIT Database Administration courses.

1. Initialization Parameter File

An Initialization Parameter File (also called an init file) is a configuration file used by Oracle Database to read startup parameters when the database instance is started.

• It contains a list of key = value pairs that define how the Oracle instance behaves.
• Parameters include memory sizes, file locations, database name, number of processes, etc.
• Oracle reads this file at instance startup to configure the System Global Area (SGA), background processes, and other settings.

Example entries:

db_name = ORCL
memory_target = 512M
processes = 150
control_files = ('/u01/oradata/control01.ctl')

2. Difference Between SPFILE and PFILE

Creating SPFILE from PFILE:

CREATE SPFILE FROM PFILE = '/u01/oracle/initORCL.ora';

Creating PFILE from SPFILE:

CREATE PFILE = '/u01/oracle/initORCL.ora' FROM SPFILE;

Modifying SPFILE parameter:

ALTER SYSTEM SET memory_target = 1G SCOPE = SPFILE;

3. Oracle Defined Roles

A Role in Oracle is a named group of related privileges that can be granted to users or other roles. Oracle provides several predefined (system-defined) roles to simplify privilege management.

Common Oracle Defined Roles:

a) CONNECT Role

• Grants the privilege to connect to the database.
• Historically included CREATE TABLE, CREATE VIEW, etc., but in Oracle 10g onwards it only includes the CREATE SESSION privilege.

GRANT CONNECT TO john;
-- Now john can log in to the database

b) RESOURCE Role

• Grants privileges to create database objects such as tables, sequences, procedures, triggers, etc.
• Suitable for application developers.

Privileges included:

• CREATE TABLE
• CREATE SEQUENCE
• CREATE PROCEDURE
• CREATE TRIGGER
• CREATE TYPE

GRANT RESOURCE TO john;
-- Now john can create tables, sequences, procedures, etc.

c) DBA Role

• The most powerful predefined role in Oracle.
• Grants all system privileges WITH ADMIN OPTION, meaning the user can also grant those privileges to others.
• Should be granted only to database administrators.

GRANT DBA TO admin_user;
-- admin_user now has full administrative privileges

d) SELECT_CATALOG_ROLE

• Grants SELECT privilege on all data dictionary views (DBA_* views).
• Useful for users who need to query the data dictionary without having full DBA access.

GRANT SELECT_CATALOG_ROLE TO analyst;
-- analyst can now query DBA_TABLES, DBA_USERS, etc.

e) EXP_FULL_DATABASE and IMP_FULL_DATABASE Roles

• EXP_FULL_DATABASE: Grants privileges needed to perform a full database export using Oracle Data Pump or exp utility.
• IMP_FULL_DATABASE: Grants privileges needed to perform a full database import.

GRANT EXP_FULL_DATABASE TO backup_user;
GRANT IMP_FULL_DATABASE TO restore_user;

f) SCHEDULER_ADMIN Role

• Grants privileges to manage Oracle Scheduler jobs, programs, and chains.

GRANT SCHEDULER_ADMIN TO job_manager;

Summary Table of Oracle Defined Roles

Granting and Revoking Roles:

-- Grant a role
GRANT CONNECT, RESOURCE TO john;

-- Revoke a role
REVOKE RESOURCE FROM john;

-- Check roles granted to a user
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'JOHN';

Key Benefit of Roles: Instead of granting individual privileges to each user, a DBA can grant a role (bundle of privileges) to multiple users, making privilege management easier and more efficient.

Privilege in Oracle Database

What is Privilege?

A privilege is a right or permission granted to a database user to perform a specific operation or to access a specific database object. Privileges control what actions a user can perform in the database system.

There are two types of privileges in Oracle:

• System Privileges - Allow users to perform specific database operations (e.g., CREATE TABLE, CREATE SESSION)
• Object Privileges - Allow users to perform operations on specific database objects like tables, views, etc. (e.g., SELECT, INSERT, UPDATE, DELETE on a table)

Oracle Database Commands

1. Create User

CREATE USER username IDENTIFIED BY password;

Example:

CREATE USER john IDENTIFIED BY john123;

2. Grant Privilege

GRANT privilege_name TO username;

Examples:

-- Grant system privilege
GRANT CREATE SESSION TO john;
GRANT CREATE TABLE TO john;

-- Grant object privilege
GRANT SELECT, INSERT ON employees TO john;

-- Grant all privileges
GRANT ALL PRIVILEGES TO john;

The WITH GRANT OPTION allows the user to further grant the privilege to others:

GRANT SELECT ON employees TO john WITH GRANT OPTION;

3. Revoke Privilege

REVOKE privilege_name FROM username;

Examples:

-- Revoke system privilege
REVOKE CREATE TABLE FROM john;

-- Revoke object privilege
REVOKE SELECT, INSERT ON employees FROM john;

4. Drop User

DROP USER username;

Example:

DROP USER john;

-- To drop user along with all their objects (tables, views, etc.)
DROP USER john CASCADE;

The CASCADE option is used when the user owns database objects; it removes the user and all associated objects.

Summary Table

Oracle AWR (Automatic Workload Repository)

What is Oracle AWR?

AWR (Automatic Workload Repository) is a built-in Oracle Database feature that automatically collects, processes, and maintains performance statistics for problem detection and self-tuning purposes.

Note: The following answer is based on standard Oracle Database documentation, as no specific curriculum notes were provided.

Key Characteristics of AWR:

• AWR is part of Oracle's self-management framework, introduced in Oracle Database 10g.
• It collects snapshots of database performance data at regular intervals (default: every 60 minutes).
• Snapshots are stored in the SYSAUX tablespace and retained for a default period of 8 days.
• It captures statistics such as:
  • SQL execution statistics
  • Wait events
  • System and session statistics
  • OS statistics
  • Memory usage

Purpose of AWR:

• Identify performance bottlenecks
• Support capacity planning
• Enable trend analysis over time
• Used by Oracle's ADDM (Automatic Database Diagnostic Monitor) for automated diagnosis

How AWR Report is Viewed in Oracle

AWR reports can be generated and viewed using the following methods:

Method 1: Using SQL*Plus Script

-- Connect as SYSDBA
sqlplus / as sysdba

-- Run the AWR report script
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Steps during execution:

1. Choose report format: HTML or TEXT
2. Specify the number of days to list snapshots
3. Select the Begin Snapshot ID
4. Select the End Snapshot ID
5. Provide the output filename (e.g., awr_report.html)

Method 2: Using Oracle Enterprise Manager (OEM)

1. Log in to Oracle Enterprise Manager (OEM)
2. Navigate to: Performance > AWR > AWR Report
3. Select the desired time period / snapshot range
4. Click Generate Report
5. View the report directly in the browser (HTML format)

Method 3: Using DBMS_WORKLOAD_REPOSITORY Package

-- Generate AWR report programmatically
SELECT output 
FROM TABLE(
    DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
        l_dbid    => &dbid,
        l_inst_num => 1,
        l_bid     => &begin_snap,
        l_eid     => &end_snap
    )
);

AWR Report Contents:

Summary

AWR is a powerful Oracle performance diagnostic tool that automatically captures workload data. Reports are primarily viewed via the awrrpt.sql script in SQL*Plus or through Oracle Enterprise Manager, helping DBAs analyze and resolve database performance issues.

Data Pump in Oracle

What is Data Pump?

Oracle Data Pump is a high-speed utility introduced in Oracle 10g that allows fast bulk data and metadata movement between Oracle databases. It is an improved replacement for the older exp/imp utilities. Data Pump uses server-side processing, meaning the export/import operations are performed on the database server rather than the client side, making it significantly faster and more efficient.

Key features:

• Supports parallel execution for faster performance
• Allows fine-grained object selection
• Supports network-based data transfer (no dump file needed)
• Provides restart/resume capability for failed jobs

Exporting Data Using Data Pump (expdp)

The export utility is called expdp (Export Data Pump).

Step-by-Step Procedure:

Step 1: Create a Directory Object A directory object must be created in Oracle pointing to a physical OS directory where dump files will be stored.

CREATE OR REPLACE DIRECTORY dp_dir AS '/u01/backup/';
GRANT READ, WRITE ON DIRECTORY dp_dir TO scott;

Step 2: Run the expdp Command

expdp scott/tiger \
  DIRECTORY=dp_dir \
  DUMPFILE=scott_export.dmp \
  LOGFILE=scott_export.log \
  SCHEMAS=scott

Common Export Modes:

Importing Data Using Data Pump (impdp)

The import utility is called impdp (Import Data Pump).

Step-by-Step Procedure:

Step 1: Ensure Directory Object Exists The same or a new directory object must be available (as created above).

Step 2: Run the impdp Command

impdp scott/tiger \
  DIRECTORY=dp_dir \
  DUMPFILE=scott_export.dmp \
  LOGFILE=scott_import.log \
  SCHEMAS=scott

Common Import Options:

Example with remapping:

impdp system/manager \
  DIRECTORY=dp_dir \
  DUMPFILE=scott_export.dmp \
  REMAP_SCHEMA=scott:hr \
  TABLE_EXISTS_ACTION=REPLACE

Summary

Note: The notes for this topic were not available; this answer is based on standard Oracle Data Pump documentation and is consistent with TU BSc CSIT Database curriculum coverage.

Jobs and Job Classes in Oracle Scheduler

What is a Job?

A Job is a scheduled task or unit of work that Oracle Scheduler executes at a specified time or interval. A job defines:

• What to run (a PL/SQL block, stored procedure, or executable)
• When to run (a schedule or specific time)
• How to run (priority, logging level, etc.)

What is a Job Class?

A Job Class is a logical grouping of jobs that share common attributes such as:

• Resource consumer group (for resource management)
• Service name (which database service to use)
• Logging level (how much logging to perform)
• Priority among jobs within the same window

Job classes allow DBAs to manage and prioritize groups of jobs collectively rather than individually.

Creating an Event-Based Schedule in Oracle

An event-based schedule triggers a job when a specific event occurs (e.g., a message is enqueued into an Oracle Advanced Queue), rather than at a fixed time.

Step 1: Create an Event Queue (Advanced Queue)

-- Create a queue table
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'event_queue_table',
    queue_payload_type => 'SYS.SCHEDULER$_EVENT_INFO'
  );
END;
/

-- Create the queue
BEGIN
  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'my_event_queue',
    queue_table => 'event_queue_table'
  );
END;
/

-- Start the queue
BEGIN
  DBMS_AQADM.START_QUEUE(queue_name => 'my_event_queue');
END;
/

Step 2: Create the Event-Based Schedule

BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name   => 'my_event_schedule',
    event_condition => 'tab.user_data.event_type = ''MY_EVENT''',
    queue_spec      => 'my_event_queue'
  );
END;
/

Step 3: Create a Job Using the Event-Based Schedule

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'my_event_job',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'my_procedure',
    schedule_name   => 'my_event_schedule',
    enabled         => TRUE,
    comments        => 'Job triggered by an event'
  );
END;
/

Step 4: Create a Job Class (Optional but Recommended)

BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS(
    job_class_name          => 'my_job_class',
    resource_consumer_group => 'DEFAULT_CONSUMER_GROUP',
    logging_level           => DBMS_SCHEDULER.LOGGING_FULL,
    comments                => 'Job class for event-based jobs'
  );
END;
/

Summary Table

Note: The DBMS_SCHEDULER package is the standard Oracle utility used to create and manage jobs, job classes, and schedules.