Learn Oracle Database Administration From Scratch

Understand how a user connects to an Oracle database, from three-tier architecture to direct connections, and how the listener, PMON, base tables, data dictionary cache, and server process enable authentication.

Explore the three stages of SQL execution: parsing, execution, and fetch. Learn how syntax checking, semantic checking, literal division, ASCII conversion, compilation, and data fetch enable query processing.

Explain how a select statement is processed from user to server, through library cache parsing, PGA execution, and LRU-driven memory and disk fetch, with optimizer choosing the best plan.

Explore how DML and DDL statements modify the database, with parsing, optimization, and PGA execution, plus undo and redo handling using LRU, undo tablespace, and redo log buffer.

Explore Oracle architecture by examining the SGA, PGA, and core background processes, and learn how PGA_AGGREGATE_TARGET automates memory distribution, in-memory sorting, and temporary tables for larger sorts.

S1 automates instance recovery and releases temporary segments, while Essman coalesces table spaces; P1 handles locks, authentication, and listener registration.

Understand Oracle data files, redo log files, archived redo logs, log switches, archiver CPT, and memory management evolution from manual sizing to automatic shared memory and memory_target.

Navigate to oracle.com and accept the license to access Oracle database 11g release 2 for Linux 64-bit. Download file one and file two (see all) to obtain the database software.

Configure your Linux server with the same VirtualBox VM setup—two GB RAM, 50 GB disk—and install Oracle 11g 64-bit for Linux on the VM, using VirtualBox or VMware.

Verify hostname and disk space, create /u01 and /u02, copy Oracle software, install prerequisites, set Oracle password, set permissions, unzip the files, and prepare the database folder for graphical installation.

Install Oracle 11g via the graphical installer, fix host entry errors by editing /etc/hosts with the machine IP and hostname, then install database software and run prerequisite checks.

Run two root scripts on the same server after installation, as root user. Then return to the installer to finish the Oracle database setup.

Set OS level environmental variables by editing the bashprofile to define Oracle_home and update the PATH, enabling sqlplus and Oracle utilities to connect without navigating to the installation directory.

Create your first Oracle database with dbca by selecting a general purpose prod db and setting the system user password.

Start and connect to the prod db with sqlplus, export oracle cid and oracle sid in bash profile, then log in as sysdba and verify the database name using v$database.

Learn how to manually create an Oracle database from a prod-like parameter file, adjusting pfile or spfile, setting Oracle sid, and running post-create scripts; compare with Dbca automation.

Delete a database in Oracle using dbca, verify it is no longer running, and confirm its entry is removed from the /edc/auto file.

Access database metadata as data about data, stored in encrypted base tables under system table space, via data dictionary and dynamic performance views created after database creation.

Learn data dictionary views for static database metadata and dynamic performance views for real-time activity, including DBA_TABLES, DBA_TABLESPACES, DBA_USERS, and V$SESSION.

Identify key external Oracle files—ora tab, ora instalok, and inventory.xml—to locate databases and Oracle home, and learn how pfile and spfile govern startup, including control and data files.

Explore physical and logical storage structures, including table spaces, segments, extents, and data blocks. Learn how Oracle uses data files and default table spaces—system, temp, undo, and Sysops.

Identify the locations of Oracle database files by querying v$ views for parameter, p file and sp file, data, temp, and log files, and verify archive log mode and destination.

Discover how table spaces map to physical data files in Oracle databases, and use practical queries to list spaces and their data files, including system, undo, temp, and user spaces.

Learn Oracle database startup and shutdown modes, including no mount, mount, and open phases, and how startup and shutdown commands transition the database with rapid restarts using shutdown immediate.

Practice shutting down the database with shutdown immediate and starting it up, using status checks, startup mount, alter database open, and optional no mount paths, with downloadable lab exercises.

Learn how multiplexing creates multiple copies of database files across different disks or mount points to enable quick recovery if a file is lost.

Multiplex control files by storing copies in different locations to prevent loss and set the control_files parameter with SP file scope.

Learn to multiplex redo log files across distinct locations, manage redo log groups and members, and perform add, rename, relocate, and drop operations during mount.

Enable archive log mode to backup redo logs to a designated destination, monitor status with archive log list and log sequence numbers, and practice switching logs in mount mode.

Use a real-time query to assess the table space utilization rate, showing size, used size, free space, and usage percentages for proactive space management.

Learn how to create a new tablespace, specify data file location and size, verify utilization, resize or add data files, and drop the tablespace with its contents.

Learn how capacity planning keeps Oracle databases by monitoring table space utilization, keeping usage below 80%, resizing data files, and coordinating with storage teams to ensure mount points have space.

Learn how Oracle users are defined and managed by the DBA, including the distinction between user and schema, and how to assign default and temporary tablespaces and quotas.

Learn to manage Oracle database users by creating and dropping accounts, setting default tablespaces and quotas, granting connect and resource privileges, and locking or unlocking accounts.

Create a role, grant insert, update, and delete permissions to it, and assign the role to users to streamline access and reduce manual grants.

Define roles and permissions and use real-time queries to identify a user's system level privileges, assigned roles, and object level permissions granted to roles.

Learn how Oracle profile management assigns profiles to users to control resources and secure access, using password management and resource management rules.

Create a profile with resource limit enabled, enforcing failed login attempts, password lock time, sessions per user, and idle time; assign it to a user and test the session limits.

Install the Oracle client on the application server to connect to a remote production database, showing how Oracle networking enables client-server connectivity, with listener and DNS considerations.

Configure the Oracle database listener by editing listener.ora with host, port 1521, and sid list for prod db and test db; start and stop the listener to enable client connections.

Configure Oracle networking by aligning DNS entries with the listener and creating tnsnames.ora entries on the client, then verify connectivity with the DNS ping utility and sqlplus.

Learn how Oracle database links enable querying data across databases by creating a link and querying remote tables.

Explore SQL Loader to upload CSV and text files into an Oracle database, using control, input, log, batch, and discard files with a where clause to filter data.

Learn to import a CSV into an Oracle table using SQL Loader: create a sample table, prepare CSV, write a control file, run the load, and verify with log.

Explore Oracle export and import utilities to move data from production to test databases using dump files, enabling schema, table, and row level exports for testing.

Learn to perform Oracle exports with the exp utility, including database, schema, table, and row level exports, using dump and log files, and prepare for imports on a test server.

Learn to use the Oracle import utility to import schemas and tables from dump files, configure logs, and manage from user to user transfers between production and test servers.

Explore Oracle data pump, an enhanced, more secure alternative to export import, featuring a master table, parallel processing, and multi-level export options, including directory-based safeguards across OS and database.

Create data pump directories on OS and in the database, grant read and write permissions, and prepare source and target servers with distinct export and import directories.

Learn how to use the data pump export utility expdp to perform database, schema, table, and row-level exports, with dump and log files, using directory and query options.

Operate the data pump import utility by copying the production dump to the test server's data pump directory, specify the dump and log files, and run remap schema as needed.