Oracle DBA 11g/12c - Database Administration for Junior DBA

Explore Oracle process architecture, including user, server, and listener processes, and how server processes parse SQL and return results.

Learn how the database writer moves dirty and cold buffers from the buffer cache to data files using a least recently used algorithm, and configure additional writers with db_writer_processes.

Learn how the log writer manages the redo log buffer, writes redo entries to files, frees space, and triggers on commit, one-third full, before disk writes, or every three seconds.

Checkpoints synchronize modified memory buffers with data files by writing the system change number to the data file headers and the control file, ensuring data consistency and faster recovery.

Describe how the system monitor process performs recovery at instance startup, recovers skipped transactions when a tablespace or file returns online, and cleans up unused temporary segments.

The PMON process monitors and cleans the database buffer cache for user processes, restarts stopped processes, and monitors idle sessions and timeouts, updating the network listener about instance status.

Learn how the recover process in distributed databases automatically resolves in-doubt transactions by connecting to involved databases and removing rows from the table when needed to ensure consistency.

Copy redo log files to backup storage after each log switch in archive log mode. Transmit redo data to a standby database and tune archiving with the log_archive_max_processes parameter.

Explore Oracle memory architecture by comparing the shared global area, where SGA holds shared data and redo log info, while PGA remains private to each server process.

Learn how the database buffer cache in the shared global area stores recently read data blocks, enabling cache hits, handling cache misses, and loading blocks from data files when needed.

Explore the shared pool's memory structures, including library cache, data dictionary cache, and results cache, and how shade SQL areas and private SQL areas optimize parsing and execution.

Explore the redo log buffer, a circular memory area that stores redo data for database recovery, and learn to set its size with the initialization parameter log underscore buffer.

Configure the large pool to handle memory-heavy operations and prevent shared pool contention during session memory, Oracle exec interface, backups, parallel queries, and advanced queues, with size set by large_pool_size.

Configure the Java pool to store Java code and JVM data using the java_pool_size parameter, and allocate the streams pool for Oracle streams memory with streams_pool_size when streams are enabled.

Pin frequently used data blocks in the oracle keep buffer pool to reduce I/O, improve performance, and avoid over-pinning large objects, controlled by db_keep_cache_size.

The recycle buffer pool stores large, rarely accessed data blocks to avoid removing them during full table scans. DBAs should regularly review SQL statements and configure it with db_recycle_cache_size.

learn how the buffer cache holds data blocks with sizes different from the default 8192 bytes and how to configure memory with db_two_cache_size and db_eight_k_cache_size based on your block size.

The program global area (PGA) is a private memory region for each process, containing stack space and user global area with cursor state, sort and hash areas, and bitmap areas.

Compare dedicated vs shared servers in Oracle, detailing per-user processes, memory use, and dispatcher queues. Note that most organizations favor dedicated servers for speed, while shared servers offer scalability.

Explore how the program global area behaves in a shared server environment, where the user global area moves to the system global area, leaving only stack in program global area.

Follow an Oracle transaction from client connection through shared pool parsing, buffer cache updates, redo/undo logging to rollback segments, and final commit with database writer writes to data files.

Install the VMware software, unzip the virtual machine package, and launch the VM to install Linux and the Oracle database, focusing on the Linux operating system.

Install Linux operating system in a VMware Workstation, configure disk partitions, network settings, and root password, then install VMware Tools and prepare for Oracle database installation.

Install the Oracle database software by creating the oracle user and group, setting environment variables, sharing the installer from Windows, and running the Oracle Universal Installer for a single-instance setup.

Understand how parameter files store database initialization parameters, including memory and configuration values. Compare p file and spfile; spfile is binary and recommended, edited via alter system.

Manually create the oracle database by building disk folders and an init parameter file with memory_target. Then run catalog.sql and catproc.sql to build the data dictionary and finalize creation.

Verify the prod database creation by checking control files, data files, and the system date, then confirm the prod folder structure.

Learn to create a database with dbca, select general purpose oltp, name prod one, configure storage (fast recovery area, data files, redo logs), and save the setup as html.

Master the Oracle startup sequence from no mount to open. Understand how the instance, comprising the SGA and background processes, links to the database's data files and redo logs.

Learn how Oracle shutdown works through the three phases—close, dismount, and instance shutdown—across immediate, transactional, normal, and abort modes, including recovery implications.

Upgrade an Oracle 11g database to 12c by installing 12c software, running the upgrade assistant, validating pre-checks, backing up, and updating the listener and memory settings.

Explore how Oracle stores data across table spaces, data files, segments, extents, and blocks, with 8 KB data blocks and the transition from logical to physical storage.

Explore Oracle tablespace concepts—permanent, undo, and temporary spaces; their roles for data, undo data, and temporary operations—plus default spaces like system, system auxiliary, temp, undo, users, and sample.

Discover online and offline tablespaces in Oracle, where a tablespace is online by default and can be taken offline for backups, recoveries, or moving a data file.

Create a tablespace with a data file, set an initial size of 50 MB, enable auto extend, and configure the growth increment and a maximum size of 250 MB.

Learn to add space to an Oracle database by creating a new tablespace, adding or resizing data files, and enabling auto extend on, while monitoring available space.

Learn to explore and manage Oracle tablespaces, view data files with DBA_DATA_FILES, and practice resizing, adding files, and enabling auto extend with next and max size options.

Learn to manage tablespaces by renaming a tablespace, adding and removing data files, renaming data files with offline mode, and bringing the tablespace back online.

Explore larger block sizes in tablespaces, such as 16k, to support indexes, large objects, and large rows, including temporary tablespaces for sorting, and verify 8 kb with show parameter db_block_size.

Learn how to manage temporary tablespace to support large joins and sorts when memory is insufficient, storing intermediate results for temporary data and for distinct, order by, and set operations.

Create a temporary tablespace group, a collection of temporary tablespaces that enables multiple default temporary tablespaces and parallel sessions, reduces contention, and supports multi-space sorting.

Explore locally managed table spaces, storing extent information in the space with bitmaps. Learn why dictionary managed spaces are deprecated and how local management enables readable standby databases.

Learn how undo data captures pre-modified data and stores it in the undo segment of the undo tablespace to support rollbacks, read consistent queries, and flashback.

Understand read consistency in Oracle by returning data as of the query start, using rollback segments to fetch unmodified rows while modified rows are read from pre-modified data.

Learn how the Ora-01555 snapshot too old error arises when undo data is overwritten in the rollback segment, breaking read consistency as undo retention time is exceeded.

Activate automatic undo management and set undo_management to auto; the database tunes the undo retention period and uses a dedicated undo tablespace, avoiding the system tablespace.

Learn to manage undo in Oracle by inspecting undo tablespace, adding data files to reduce contention, and switching the active undo tablespace between undo tbs one and undo tbs two.

Configure undo_retention to control how long committed undo data stays available, adjusting for auto extending versus fixed-size undo tablespaces, illustrated by the 1800-second example.

Learn how undo retention guarantee preserves undo data for 1800 seconds to support read-consistency queries and how to enable or disable it with alter system and alter tablespace commands.

Learn how redo log files enable Oracle database recovery by capturing redo entries for inserts, updates, and deletes, and how multiple groups and disks support archival and reliability.

Explore redo log file management in Oracle database administration by examining log groups and file sizes with data dictionary views, adding log file members, and switching logs to active status.

Learn to manage redo log file groups in Oracle by deleting an inactive group, adding a new group with specified disk files, and verifying the changes.

Explore archive mode versus no archive mode, and how redo log files are archived to an archive location to enable recovery. Learn about multiple log groups, circular switching, and archival.

Demonstrates enabling archive log mode on an Oracle database by checking current mode, mounting, setting the archive destination, and opening for automatic archival.

Discover how database users own objects, control access privileges, and use schemas, default and temporary tablespaces, profiles, and common user creation tasks.

Learn practical user management in oracle db: create users, grant create session, assign default tablespaces and quotas, and handle password expiry, with drop user cascade demonstrations.

Learn to reset a user password with alter user identified by, set password expired, and handle login prompts. Also lock and unlock user accounts in Oracle DBA 11g/12c.

Understand privileges in Oracle DBA 11g/12c, covering system privileges with admin option and object privileges like select, execute, and grant on specific objects, plus the any keyword.

Demonstrate granting create table privileges, using admin option, and applying select any table to query across schemas.

Demonstrates granting object privileges on the six.dot.customer table, including select and delete, with grant option. Emphasizes careful use of grant option to prevent privilege misuse.

Learn how to revoke system privileges and object privileges with admin option and grant option, and note that object revocation cascades to grantees like Tom, unlike system privileges.

Revoke system privileges, such as create table, from a user; verify that John's revoke removes his create table rights but does not revoke Tom's grant with admin option.

Revoke object privileges on a table, like select and delete, and see how John's revocation also affects Tom. Contrast object privileges with system privileges such as create table.

Practice session demonstrates creating users W and Peter, granting session and create table privileges, and creating three tables: sales, sales history, and product.

Learn to create roles by grouping privileges, such as manager and operations roles, and assign them to users to simplify privilege management.

Learn to create and assign roles to manage privileges, using a manager role and an operations role to grant insert, update, and create table rights across dw.sales_history and dw.product.

Explore how profiles manage user access by setting limits on CPU, disk IO, connect time, idle time, concurrent sessions, private SGA, and password related parameters.

Create a profile, assign it to users, and configure limits in an Oracle database, such as sessions per user, connect time, and idle time to manage resources.

Configure password policies at the profile level by adjusting lifetime, grace period, failed attempts, and lock time, then apply changes with alter profile and verify via a profile query.

Discover how Oracle Net Services enables client connections to a database via a listener, using host, port, protocol, and service name, with listener.ora on server and tnsnames.ora on client.

Configure the listener via the graphical interface, set the host name or IP and port 1521, then start, check status, and verify services like prod one and prod one xdb.

Learn how to configure tnsnames.ora and a dns entry to streamline Oracle database connections, using service name prod1 and host 192.168.50.55:1521, then connect with sql plus.

Learn how a database link, a schema object, lets you access objects in another database, including creating, connecting via dns service name, and using public or private links.

Learn to create and use a public database link to access a remote table, query it across users, and assess when to prefer private over public links.

Show private database links and contrast them with a public link, demonstrating that private links are accessible only by their creator, while public links are accessible by all users.