D192/D330 Test Items

Pfile (parameter file)

contains initialization parameters for an Oracle instance.

SPfile(server parameter file)

contains same init parameters in a binary file

V_$

Prefix for dynamic performance views

SMON

system monitor process recovers after instance failure and monitors temporary segments and extents.

PMON

process monitor process recovers failed process resources. Monitors and restarts any failed dispatcher or server processes.

DBWR- Database Writerprocess

process is responsible for writing dirty buffers from the database block cache to the DB data files. Only writes blocks back to Data files on commits or when cache is full.

LGWR- Log Writer process

process is responsible for writing log buffers out to the redo logs. It writes when the log buffer is one-third full or there is a commit issued. It writes all changes made to the database whether committed or uncommitted to the redo log for database or transaction recovery.

CKPT – Checkpoint process

responsible for updating file headers in the DB datafiles.

RECO

recovers distributed transactions which occur via a database link.

MMON/MMNL/MMAN

Used to perform various DB management tasks.

CONNECT john/jyoung123

How do you connect to a user SQL from the command line (john with

a password of ‘jyoung123’? sqlplus john/jyoung123

Archive Log mode

A database mode that allows for the continuous archiving of redo log files, enabling point-in-time recovery and preventing data loss in case of a failure.

RMAN (Oracle Recovery Manager) is the tool.

It is used for backup, recovery, and maintenance of Oracle databases.

RMAN (Oracle Recovery Manager)

can perform full, incremental, and differential backups, as well as image copies and backup sets.

C:\Program Files\Oracle\Backup [Windows]

/user/local/oracle/backup [Linux/Unix]

is the default directory for storing RMAN backups on Windows and Linux/Unix systems.

Block change tracking

is a feature that allows RMAN to keep track of changes to data blocks, enabling more efficient incremental backups.

Location of the alert log file

Found inside the database Automatic Diagnostics Repository (ADR)

$ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE

_SID/trace/

tablespaces (TTS)

Do not have to unload and reload the data; the source data files are transported to the destination system and attached to the target DB.

Generally, much faster than a conventional export and import.

Administrator privileges sysoper and sysdba

are special privileges in Oracle databases that allow a user to perform administrative tasks, including managing database instances and performing backup and recovery operations.

Startup (nomount)

Oracle first opens and reads the init parameter file to see how DB is configured. After, memory areas associated w/ DB instance are allocated. Background processes are started.

Startup (mount)

During this stage, the database is mounted, which means Oracle checks the control files to ensure the database is consistent and can be opened. The database is not yet open to users, allowing for certain configuration changes to be made before full access. This stage allows for recovery operations and configuration changes before opening the database to users.

Startup (open)

In this final stage, the database is opened for user access, and the data files are made available for read and write operations. Users can now connect to the database and perform transactions.

Every Oracle Database should have at least two control files. Each stored on a different physical disk. Only one control file is required

Minimum number of control files for database startup

allow users to viewing data without modifying table data

Grant them Read Only Privilege

GRANT SELECT ON[ **table*]* TO [**user**]

Create a view on the table or view.

Configuration requirement for data communication between 2 databases.

Establishing a database link and ensuring compatible network settings and protocols.

content of a temporary tablespace

Contain data that persists only for the duration of a user’s session.

Oracle does not allow users to create objects in a temporary tablespace.

It contains sorting such as order by and group by. It also contains indexes as they are being built. Once built, the index is moved to the defined tablespace per the create index statement.

Information stored by undo tablespace

Consists of records of the actions of transactions, primarily before they are committed.

A method of maintaining information that is used to roll back, or undo, changes to the DB. The before image.owners of a

table/function or just about anything.

USER

owners of a table/function or just about anything.

PROFILE

list of limits for a given user. can only be seen by administrators. System privileges such as CPU per session, CPU per call.

ROLE

set of privileges. A database role can be granted table or view privileges which can be granted to users selectively. A bundle of grants can be inherited via a

role. This is used instead of public grants.

WITH ADMIN OPTION

Any ‘global’ privileges like roles and system privileges are granted using

Only for system privileges, not object privileges.

WITH GRANT OPTION

Only for object privileges, not system privileges. Only the person who granted the privilege can revoke the privilege.

Revoked privileges can cascade, allowing the first grantor to revoke many

subsequent grants.

create session

command for granting minimum database access

Public schema

can be accessed by everyone in the database

Private schema

is only accessible to the user responsible

SET ROLE

statement to enable and disable roles for your current session.

SGA – System Global Area

group of shared memory areas that are dedicated to an Oracle Instance.

Database buffer cache

memory area that stores copies of data blocks to optimize data access and improve performance by reducing the need to read from disk. It allows multiple users to quickly access frequently used data, enhancing overall database efficiency.

shared pool

It's responsible for caching various types of program data

redo log buffer

a memory area in a database that temporarily holds changes made to the database before they are written to disk. It helps improve performance by reducing the number of disk writes needed during transactions

Java pool

used to cache Java code and objects during runtime

Large pool

optional memory area within the System Global Area (SGA) of an Oracle database, designed to handle large memory allocations for specific tasks, such as user global area (UGA) management in a shared server environment. It helps to segregate memory usage from the shared pool, which is primarily used for caching SQL statements and PL/SQL code

Streams pool

helps manage memory allocation for these processes.

Configure automatic shared memory management.

Enable automatic shared memory management feature by setting the SGA_TARGET initialization parameter to a nonzero value. This parameter sets the total size of the SGA.

Automatic Database Diagnostic Monitor

a tool in Oracle Database that analyzes performance data to identify and diagnose performance issues. It provides recommendations for resolving these issues based on data from the Automatic Workload Repository (AWR).

Automatic Workload Repository (AWR)

collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

To reclaim space in a table

In order to perform a shrink operation on a table the commands MUST be issued in the following order;

altertable <table_name> shrink space compact

followed by

alter table <table_name> shrink space

DMBS_ADVISOR

A set of expert systems that identifies and helps resolve performance problems relating to database server components.

Part of server manageability suite of advisors.

storing undo data

It is the original copy of modified data.

To undo any uncommitted changes made to the database in the event that a rollback operation is necessary.

rollback segment and tablespace

To undo the changes when a transaction is rolled back. Recover data in case of db failure

segment advisor

Identifies segments that have space available for reclamation.

Resumable space

After the error condition is corrected, the suspended operation automatically resumes.

identify and resolve deadlocks

Issue a COMMIT or ROLLBACK

Tune application to remove deadlocks / prevent them

from happening.

Flash recovery

DBAs can quickly recover from damage to a datafile without using a traditional restore and recovery operation.

Using Flash recovery, DBAs can direct all backups to a single location managed by Oracle Database.

prevent overwriting of undo data

If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

Dynamic view and/or join for locked sessions

V$LOCKED_OBJECT v$lock, v$session, dba_objects

Data Dictionary

Used to find information about users, schema objects, and storage structures.

Oracle Database Configuration Assistant (DBCA)

is a tool for creating and configuring an oracle database.

Logical backups

exports where they extract data and logical definitions from the database into a file.

Oracle GoldenGate

premier replication tool used for data integration, high availability, and online migrations.