Oracle Database Administration from Zero to Hero

Prepare a Windows VM by importing an OVA, configuring 6 GB RAM, static IP and hostname, updating hosts for Linux and Windows, and validating with pings for Oracle practice.

Explore 2-tier, 3-tier, and N-tier architectures and how Oracle databases support them, plus OLTP vs DSS (OLAP) workloads, warehouse vs OLTP databases, and NoSQL as a separate product.

Explore Oracle database releases, editions, and license options. Compare long-term support releases, innovative releases, cloud services, and autonomous databases.

Provide an overview of Oracle database technologies, options, and management packages, including RAC, Data Guard, Active Data Guard, GoldenGate, ASM, multitenant, in-memory, and Oracle Machine Learning with SQL/PLSQL.

Explore the fundamentals of Oracle database architecture and the components of an Oracle database. Understand the purpose of each component to build a solid foundation for managing Oracle databases.

Learn the Oracle database architecture, including memory areas (SGA, PGA), server processes, buffer cache, redo log buffer, and the roles of DB writer, log writer, and listener.

Explore Oracle database architecture from memory areas to storage files, including control files, online redo logs, system data files, ADR, and user tablespace files, and learn their roles.

Explore how Oracle uses table spaces and data files to organize storage from segments and extents to data blocks. System and Sysaux table spaces are mandatory, housing the data dictionary.

Learn to prepare systems, download, install, patch, and describe the Oracle Inventory Directory for Oracle Database software on Linux and Windows, and set up oracle-base, oracle-home, and sid.

Learn to install Oracle database software using interactive, silent, and response file modes, including rpm installation, and configure Oracle Home user, inventory, and deinstall steps on Windows and Linux.

Install Oracle Database 19c on SRV1 Linux via silent installation using a generated response file after preparing the VM and configuring the Oracle user and groups.

Install Oracle Database 19c on a Windows VM by preparing user accounts, mapping the shared drive, creating Oracle home, extracting the installer, and running setup with the OracleSVC service account.

Plan database capacity, naming, character set, and block size before creating an Oracle database, then use DPCA in silent or interactive mode with a listener configured by NetCA.

Invoke dbca in silent mode with -silent and -responsefile. Mix response files and CLI parameters to create databases, including bluggable databases, and use chopped to uninstall components.

Create an Oracle 19c non-CDB database named Aura WinDB on Windows with dbca, configure the listener, storage, and asmm memory, and enable the hr schema and sample schemas.

Explore Oracle database management tools and learn to connect using SQL Plus, SQL Developer, SQLCL, EMExpress, Oracle Enterprise Manager Cloud Control, and Database Management Cloud Service.

Install sql developer on the hosting pc, connect to SRV1, and run sql statements from the worksheet using ctrl+enter or F5 to execute the statements as a script, via GUI.

Learn to start up and shut down database instances, manage initialization parameters, retrieve data dictionary views, and use Oracle database documentation for basic database administration.

Explore multiple startup and shutdown options for an Oracle database, including normal and immediate shutdown, transactional shutdown, and startup in mount or nomount modes on SRV1.

Explore how to manage Oracle initialization parameters, including SGA target, SGA max size, and NLS date format, using sp and p files with practical examples.

Learn to manage oracle initialization parameters with SPFILE and PFILE. Create a PFILE from SPFILE, set the SGA target to 2400 MB, and rebuild SP from P.

Master how to use Oracle database documentation, differentiate guides from references across basic, intermediate, and advanced topics, and identify the right books for specific topics.

Navigate Oracle database documentation by version to download PDFs, explore installation and administration guides, and use the reference to learn about parameters like cpu_count and cluster creation.

Explore the fundamentals of Oracle database multi-tenancy, enable multi-tenant environments, and learn to create CDB databases and bloggable databases, performing basic administration tasks from Oracle 21c onward.

Explore Oracle multitenant architecture, including the container database root (cdb$root), pluggable databases (PDBs), and the seed pdb$seed; learn about common vs local users and undo configurations.

Develops a CDB and a non-CDB in Oracle by using DBCA in silent mode, managing VirtualBox snapshots, and exploring PDBs and the HR schema in the root container.

Learn to create pluggable databases (PDBs) inside a container database using dbca or sql developer, with Create Pluggable Database and seed-based methods, plus cloning and prerequisites.

Discover how to create a pluggable database (PDB) from the seed using the CreateBlogableDatabase statement, manage data file destinations, open in read-write mode, and clone or drop PDBs with backups.

Master multitenant administration tasks by switching the current container, changing CDB and PDB states, and saving PDB open states. Configure PDB initialization parameters and rename a PDB's global name.

Explore managing memory areas in Oracle databases and sizing storage, then learn to manage tablespaces and data files—critical tasks every DBA should master.

Learn to manage Oracle database memory using automatic memory management and automatic shared memory management, including enabling AMM and ASMM, monitoring memory, and tuning with advisors.

Practice configuring ASMM and AMM in Oracle databases, allocating memory to SGA, PGA, and BGA, handling memory errors, and using advisor views and SP file workarounds.

Explore Oracle database storage structures, from segments and extensions to data blocks mapped to disk and tablespaces, and learn how block size choices and nonstandard block sizes impact performance.

Enable Oracle managed files to centralize and auto-name data, redo, archived redo, block-change, and flashback files, and configure with dbCreateFileDist and dbRecoveryFileDist to monitor table space usage.

Learn to manage permanent tablespaces by taking them offline or online using normal, temporary, or immediate options, set quotas, enforce read-only or read-write states, and separate data and indexes.

Practice managing Oracle storage structures, including tablespaces, segments, extents, and data blocks, using scripts to create testing tables, move them to a tablespace, and adjust data files with OMF.

Connect to the CDB as SysUser and explore data files across root and seed containers. Retrieve permanent and temporary spaces and observe space usage with v$datafile and v$tablespace.

Learn how to manage data files for Oracle databases, including resizing, putting data files offline, renaming, moving, and deleting data files using AlterDatabase and related statements.

Learn to manage Oracle data files by relocating online and offline, handling unavailable data files, and recovering from PDB data file loss, including archive log mode considerations.

Explore resumable space in Oracle database, which suspends and resumes large loads, and learn to enable it with alter session plus alerts and triggers.

Enable resumable space allocation by granting HR the resumable privilege and allowing per-session timeouts; create a 10 MB tablespace, load data, and resolve suspended operations by adding a datafile.

Describe how undo data supports rollback, read consistency, and recovery; configure automatic undo management and an active undo table space, and tune undo retention for long-running queries.

Estimate undo table space and retention with V$ UndoStat and the retention formula. Use the Undo Advisor with AWR snapshots to obtain findings and recommendations.

Manage undo space and retention by enabling retention guarantee, configuring temporary undo, and choosing shared or local undo modes to balance long-running queries with DML needs.

Examine undo data from v$signUndoStat, enable the undo advisor, and review its recommendations. Size the undo tablespace for a specific retention using AWR snapshots and observe undo blocks during transactions.

Learn to manage segments by shrinking fragmented segments, using deferred segment creation, and applying table compression, reflecting routine dba tasks and certification exam requirements.

Shrink segments by defragmenting: compact data, move the high-water mark, and deallocate unused blocks; use shrink with or without compact to balance locking and performance.

Shrink database segments by shrinking tables, deallocating unused space, and moving tables. Enable row movement and use online moves with update indexes to preserve index usability.

Learn how deferred segment creation saves storage by creating only metadata until data insertion, with options for immediate vs deferred segment creation and how to materialize segments using DBMS_ADMIN.MATERIALIZED_DEFERRED_SEGMENTS.

Practice deferred segment creation in Oracle with ten empty tables, observe segment materialization, and learn how enabling saves disk space and setup time.

Explore Oracle table compression, including basic and advanced methods, plus Colomer and hybrid columnar options; learn how deduplication enhances direct path load performance and how to enable or move compression.

Examine basic and advanced table compression by creating three tables and populating them to compare space savings across non-compressed, basic compressed, and advanced compressed data.

Learn to manage Oracle database users and schemas, create users with database authentication, adjust default and temporary tablespaces, and handle predefined and internal accounts.

Learn to create and manage authenticated and schema-only users in Oracle, set passwords, default and temporary tablespaces, quotas, profiles, and use alterUser and dropUser.

Examine how object privileges and system privileges govern who can access and modify objects in Oracle, including grant and revoke, with grant option, and cascading effects.

Create and manage roles in Oracle Database, grant privileges to roles, assign roles to users or other roles, and use password-protected authenticated roles for streamlined security.

Discover how to use password-protected roles, grant them to users, and manage default roles to control application access. Follow best practices for public roles, role dropping, and auditing privileges.

Explore how Oracle user profiles manage password policies and resource limits, implement password lifecycles with grace periods, and create, alter, and assign profiles, including the STIG profile for production security.

Learn to enforce strong passwords in Oracle by using verification functions and profiles, apply aura12c-stig-verify rules, and enable gradual password rollover in 19c/21c.

Manage the user tablespace quota by setting a 5 MB quota, testing with a data load, then adjusting to 15 MB, and compare ora-stig and default password profile limits.

Learn how to acquire container data objects, set and view container data attributes for common users in a multi-tenant Oracle environment, and control visibility across PDBs.

Manage common and local privileges across root and PDB containers, including create session, create table, and unlimited table space, while controlling container data attributes for common users.

Apply the principle of least privileges in Oracle databases by granting only necessary privileges, configuring dictionary accessibility, restricting public execute, managing directory and remote authentication, and auditing critical operations.

Explore operating system authentication in Oracle Database, focusing on administrative OS authentication via OS groups like dba and aura dba. See how dbca configures these groups for secure OS-based logins.

Enable OS authentication by adding OS users to the administrative privilege group and exporting the oracle-sid, oracle-home, and path variables. Connect as sysoper or another administrative privilege without a password.

Learn password file authentication, create a password file with aura-pwd, and set remote login password file parameter; OS authentication supersedes password file authentication, while admin passwords synchronize with data dictionary.

Examine os authentication and password file authentication in Oracle databases, including Sys and SysDBA logins via the DBA group and related password file usage and permissions.