Oracle Database Administration Workshop ( 12c and Higher)

Learn the end-to-end Oracle installation steps using VirtualBox and Oracle Linux, including VM sizing, prerequisites, and installing Oracle database 18c with Putty and WinSCP.

Download and install Oracle VirtualBox on Windows host by following step-by-step instructions, including navigating to virtualbox.org and choosing the download, then proceeding through the simple installation.

Download the Oracle Linux image from Oracle eDelivery, sign in or create an account, choose Oracle Linux 7.6 64-bit ISO, and save the ISO for VM creation.

Create an Oracle VirtualBox VM named Oracle 18c, Linux, Oracle 64 bit, updating BIOS if needed. Allocate half RAM, choose a dynamically allocated VDI, and set 25–30 GB disk.

Learn to install Oracle Linux in a virtual box, configure network bridge, guest additions, and installation options, then create root and user accounts and log in.

Install guest additions to enable copy and paste and a shared folder between the virtual machine and the host; disable Linux security, edit /etc/selinux/config, reboot, then install the additions.

Create and configure a shared folder in VirtualBox with auto mount and vboxsf access to share files between the host and the Oracle 18c VM.

Learn basic Linux commands like pwd, tilde, ls, cd, mkdir, touch, vi, cat, man, rm, and rm -r to navigate, create, edit, and manage files on the desktop.

Learn to read ls -l outputs, distinguish directories from files by the first character, and understand user, group, and others permissions with read/write for the group and read-only for others.

Learn basic Linux commands for file navigation, copying, moving, and renaming, then create and manage users and groups, verify user IDs, and access shared folders on Oracle Linux Server.

Edit the host file to map the hostname to its IP, install prerequisites with yum, create Oracle user and groups, set directory ownership, disable firewall, and set the Oracle password.

Learn the automatic setup for Oracle prerequisites and contrast it with the manual path, including sysctl.conf and security limits steps, and how the setup creates Oracle user and groups.

Configure the oracle user's OS environment by defining oracle base, oracle home, oracle id, and oracle unique name in the dot bash_profile for a single instance setup.

install oracle database 18c on linux by downloading the 18c linux 64-bit package, unzip, run oracle universal installer, configure a container database with orcl and pdb, and install sample schemas.

Learn how to start an Oracle database from no mount to open, using sqlplus with spfile, understand SGA allocation, and apply SYSDBA and SIS privileges for administration.

Learn how to start up and shut down an Oracle database using startup modes such as no mount, mount, open, and force, and understand restricted sessions and pfile usage.

Shutdown the Oracle database by logging in as sysdba and choosing normal, transactional, immediate, or abort modes; normal, transactional, and immediate maintain consistency, abort may cause inconsistency.

Understand shutdown modes: normal waits for transactions and sessions to finish, transactional waits for transactions, and immediate rolls back uncommitted changes and closes sessions.

Explore how Oracle net services use the oracle listener as a gateway to the database, with listener.ora and tnsnames.ora configuring tcp/ip connections.

Understand the service name as an alias for a database and how the listener registers databases dynamically to enable client connections via the service name, including multiple services like sales.us.example.com.

Use the tnsnames.ora file on the client to map a tns name to host, port, protocol, and service name, enabling seamless connections to the Oracle service.

Discover how a client uses tnsnames.ora to reach ORCL at localhost:1521 TCP, how the listener spawns a server process, and why sessions persist if the listener stops.

Install PuTTY, a free tool to connect to a remote server, and configure host mappings. Log in as Oracle, open sqlplus, and verify connectivity.

Install winSCP to transfer files between client and server using FTP and SFTP; configure the hostname and login with Oracle credentials, and practice transferring a sample text file.

Install Oracle SQL Developer on the client machine and test the client–server connection. Download from Oracle, extract Windows 64 bit with JDK, create a desktop shortcut, and explore listener errors.

Learn multiple ways for the sysdba to log in to Oracle, including operating system authentication, password file login, and password-based logins, plus changing the sis password and security considerations.

Move from a container database to pluggable databases in oracle, open and close pluggable databases, and switch sessions between containers using alter session and alter pluggable database.

Start and stop the listener with lsnrctl, review listener.ora and tnsnames.ora, and rely on the default port 1521 with tcp. Test connections to orcl and orcl pdb using these files.

Learn to connect with easy connect by supplying user, password, host, port, and service. The listener must be up to resolve services; OS authentication permits sysdba login without the listener.

Learn to connect using the tnsnames.ora file by creating and using entries such as ORCL and ORCL PDB, then test connections with sqlplus as sysdba.

Unlock the HR schema in the ORCL PDB by switching to the pluggable database, unlocking HR, setting a password, and verifying access via SQL Plus and SQL Developer.

save and run scripts using sqlplus by creating test.sql in the home Oracle directory or on the desktop and executing it with @test.sql, both inside and from outside sqlplus.

Explore how the Oracle data dictionary stores database metadata in server-maintained tables and views. See container and pluggable database concepts and how to switch containers with alter session.

Compare common users and local users in Oracle 12c and above. A CDB common user is created in the root container and inherited by all pluggable databases.

Learn the difference between common and local users in Oracle across CDBs and PDBs. Use the C## prefix to create common users and verify with CDB_USERS.

Understand dynamic performance views, dollar sign and v$ views updated in real time for performance monitoring with memory versus disk reads and live data.

Learn to count tables across containers by querying cdb_tables from the root container, not a pluggable database, using alter session set container to switch containers.

Learn how to query data files in Oracle databases, including using CDB and PDB contexts, querying CDB_data_files and DBA_data_files for file names, file IDs, and tablespace names.

Learn how to query temp files in Oracle's temporary tablespace, identify temp file names and IDs across CDBs and pluggable databases using CDB temp files and DB temp files.

Identify the container database name, version, and instance name in a multi-tenant Oracle CDB setup. Explore querying the CDB root, pdb_seed, and pluggable databases to verify with v$version and v$instance.

Explore how the dollar sign containers reveal the root and pluggable databases, and how to switch containers. Review the CDB_PDBs dictionary for pdb ids, names, and statuses.

Examine the V$DATAFILE view and its difference from the CDB_DATA_FILES dictionary table, including container visibility and pluggable database open states.

Learn to query the log files and control file in Oracle, including redo log files and instance versus container scope, to understand database startup and recovery processes.

Learn how to keep a pluggable database open after startup by using alter pluggable database save state in Oracle 12c release 2, ensuring the PDB opens automatically in read-write mode.

Learn to manage initialization parameters and parameter files to start and configure an Oracle database instance. Distinguish the server parameter file and the text pfile, their locations, and editing methods.

Learn the Oracle parameter file search order at startup, sp file with sidora, then sp file, then b file with sidora, and how to name a p file for startup.

Learn how initialization parameters and parameter files optimize Oracle performance by adjusting memory structures, database limits, and specifying control and redo log file locations for the instance to start.

Explore types of initialization parameters in Oracle databases—basic, advanced, and derived—with examples like db block size and session, and how derived values depend on other parameters.

Explore modifying initialization parameters in Oracle databases using v$parameter and alter system or alter session with scope options to apply changes across memory, spfile, or all pluggable databases.

Learn to use alter session to change session-level parameters such as nls_date_format, verify results with sysdate from dual, and identify which parameters are session-modifiable versus system-level or pdb-modifiable.

Explore how v$parameter and v$parameter2 reveal session parameters, including is_session_modifiable and is_system_modifiable, and differentiate static, immediate, and deferred changes for pluggable databases.

Explore how v$system_parameter shows instance-level defaults inherited by new sessions, distinguish derived vs spfile or pfile values, and note alter session effects on v$parameter versus v$system_parameter (ns date format).

Demonstrates the startup parameter file search order, showing Oracle checking spfile, then pfile, then init.ora, and how to create a pfile from an spfile to start the database.

Learn to identify whether an Oracle database started with an spfile or a pfile by checking the spfile parameter and using show parameter spfile.

Alter system set max idle time=30 scope=memory changes the value in memory only, not the SP file; new sessions read 30, and startup reverts to default.

Learn to use alter system with scope spfile to set max idle time to 50, write to the spfile, restart the database, and verify the change.

Practice shows how alter system with scope both updates memory and spfile, demonstrates preventing certain increases, and explains resetting parameters by removing them from spfile and restarting.

Understand how default scope works with alter system set, depending on SPFILE or PFILE startup, with both or memory as defaults, and learn how to specify scope accordingly.

Explains how the open_cursors parameter limits per-session cursors and demonstrates altering the open_cursors setting with scope memory or spfile, noting pfile constraints.

Demystify the container clause in alter system and inheritance in Oracle container databases by explaining root and pluggable database parameters, PDB_modifiable, v$system_parameter, and the effects of container all vs current.

Explore how the container clause in alter system controls open cursor limits across root and pluggable databases, showing inheritance and override behavior using container current and all.

Explore the automatic diagnostic repository (adr) as a wide, file-based tracing and logging store. Learn how it collects traces, alert logs, and health monitor reports to help DBAs diagnose issues.

Learn how the Oracle alert log records startup parameters, internal errors, and administrative actions, and locate diagnostic directories via the diag_info view with vi or addr.

Learn to locate the Oracle alert log in two places: alert (XML) and trace (text), and perform case-insensitive searches for startup events, shutdowns, and non-default parameters using a text editor.

Learn to view Oracle alert logs with Adrci, load diag rdbms details for a database, and search logs in vi with case-sensitive queries for start times and alter database events.

Trace files store error information and guidance for tuning, while ADR collects diagnostic data for incidents and supports automatic purging by retention policy and size.

Enable DDL logging in the Oracle session to capture create and drop table statements, logging them to DDL_sql.log and its XML versions for auditing.

Explore the spfile versus server parameter file using the dollar sign parameter and v$sp_parameter, and verify changes in v$parameter after altering open_cursor with scope memory.

Master creating pluggable databases using the seed, cloning, and plug/unplug methods within a single container; learn to use SQL Plus, SQL Developer, Enterprise Manager, Cloud Control, and DBCA.

Learn to create a pluggable database from the seed in Oracle database, copying data files and creating system and undo tablespaces, a default schema, common users, and services.

Create a new pluggable database from seed named pdb one, with admin user pdb one admin and dba role, and configure the users tablespace, data files, and file name convert.

Connect to the new pluggable database (PDB One) via listener service names and easy connect, switch to the PDB, and validate connections with sqlplus, sql developer, and tnsnames.ora.

Use the database configuration assistant (DBCA) to create a pluggable database from seed, configuring name, admin, and storage. Then verify the new PDB with SQLPlus and check its service name.

Connect to the root database as sysdba, close the pluggable database, and drop it including data files; never drop a PDB that contains information.

Discover how to drop a pluggable database with dbca by navigating manage pluggable databases, choosing delete, and confirming the orcl container will remove the data files.

Clone a pluggable database by creating PDB five from ORCL PDB, copying data files and tablespaces, and preserving users, with uncommitted changes not copied.

Learn to unplug and plug in pluggable databases (PDBs) across container databases (CDBs) using XML definitions. Verify compatibility and preserve data files while moving PDBs between CDBs.

Learn to unplug and plug pluggable databases within a container, clone PDBs with XML, keep data files, and recreate a new PDB from the XML.

Practice unplugging and plugging pluggable databases (PDBs), open and mount operations, generate a pdb test xml file, and explore data file paths and the no copy temp file reuse approach.

Learn how the Oracle listener receives a connect packet, validates the service name, and spawns a dedicated server process that authenticates, creates a session, and serves SQL requests.

Learn how the default listener is created by the Oracle Net Configuration Assistant and listens on TCP/IP port 1521, with dynamic service registrations populating its services.

Discover how the default listener and dynamic registrations enable connecting to the Oracle database on the default port 1521 via tcp/ip, even after renaming listener.ora.

Explore changing the Oracle listener to a non-default port, update listener.ora and tnsnames.ora, restart the listener, register services with sqlplus as sysdba, and revert to the original port.

Learn to create a dynamic listener, configure listener.ora and tnsnames.ora, start a second listener on a new port, and verify connections with SQL Developer.

Create and configure a static listener for Oracle database by manually adding services, using pluggable databases, and managing the listener start and stop to expose Orcl Pdbs and Pdb1 services.

Configure and manage Oracle listeners with the net configuration assistant and net manager, create or delete listeners, and update tnsnames.ora using dynamic or static registrations.

Master Oracle Net Manager to update the listener, tnsnames.ora, and sqlnet.ora, create listeners, and add pdb entries with tcp/ip, host, and port settings.

Learn how to create and use a database link to access objects in a remote database, including private and public links, and how to run cross-database queries using SQL.

Create two pluggable databases with dbca in Oracle 12c/18c, name prod one and prod two, grant DBA to prod one admin, and verify with container switching.

Create a second pluggable database from the seed and assign a prod_two_admin with DBA privileges. Build a private database link read_prod_two to enable cross-database queries on the employees table.

Learn how to manage user access in Oracle databases by creating users, granting system and object privileges, using roles, and revoking privileges and altering passwords.

Create a local user demo in a pluggable database and grant system privileges. Grant object privileges on HR tables, including select, insert, update, and delete.

Demonstrates testing and verifying system and object privileges in Oracle database administration. Create and modify objects, grant privileges, and explore privilege dictionary views such as session_privs and user_tab_privs.

Create private roles, grant system and object privileges to roles such as manager, and assign those roles to users like Ahmed in a pluggable database, then test privileges.

Create common users in the root container with container equal all, grant create session across all pluggable databases, and distinguish them from local users bound to specific pdbs.

Explore how common roles and common users streamline privilege management across container and pluggable databases, granting sysdba privileges and testing role scope in root and pluggables.

Explore how to define and manage database users, configuring unique usernames, password authentication, default and temporary tablespaces, user profiles, an initial consumer group, and account status, including locking mechanisms.

Explore Oracle supplied administrator accounts such as sis and system, plus sis backup, sysdig, zcm, sis rack, and sysmon, with roles in backup, data guard, tde, rac, and oem.

Explore seven special system privileges for Oracle administrators, including Sysdba and restricted sessions, super, SSIs backup, Sysdig, RAC, and SIS HSM, with emphasis on CDB privileges.

Explore Oracle supplied roles, including the DBA role and its best practice for pluggable databases, plus resource, schedule_admin, and select_catalog_role privileges for developers and tuning.

Create a common user with SYS-like privileges, assign default and temporary tablespaces, grant session, DBA, and sysdba roles, and verify logins and session privileges for auditing.

Create a pluggable database admin by adding a local user in the pluggable database, grant create session and DBA privileges, and verify the admin is restricted to the pluggable database.

Create master and sub master roles, grant system privileges (create session, create table, create view), and grant the master role to a user in an Oracle pluggable database.

The database configuration assistant creates a pluggable database with the PDB_DBA role, but requires additional DBA privileges for the PDB ten underscore admin user.

Discover how the with admin option allows passing system privileges to other users in a pluggable database. Create a test user and grant create session with admin option.

Explore using user profiles to enforce resource limits and password parameters, including idle time and password aging, and learn to create and assign common or local profiles.

Explore how password parameters in Oracle database administration strengthen security by enforcing account lockout rules, password aging and expiration, password history, and password complexity checks with built-in verification functions.

Explore how Oracle supplied password verification functions enforce rules and how a database administrator can create custom ones, including verify_function_g, 12c_verify_functions, 12c_strong_verify_function, and the 11g variant.

Learn how user profiles control resource limits like cpu per session and cpu per call. Also review connect time, idle time, concurrent sessions, private SGA, disk input/output, and resource_limit enforcement.

Learn how to create a common profile at the pluggable database level, configuring session per user, idle time, and password settings, then apply it to a common user.

Create a local profile in the pluggable database with session per user unlimited, then set inactive account time to 15 days and assign it to Sarah with required grants.

Apply password verification in Oracle using profiles and ora12c_verify_function to enforce length, special characters, and uniqueness. Learn how to change passwords with replace and alter user privileges.

Understand how table spaces manage storage from data files to blocks, extents, and segments, and the roles of system, temp, undo, and user table spaces.

Discover how Oracle stores table data inside a table space using segments, data blocks, and row pieces; learn when a row spans multiple blocks due to size or many columns.

Explore the database block content, including the block header with segment type, address, and dictionary, the raw data growing upward, and the free space enabling growth.

Create table spaces using SQL or the Enterprise Express tool to allocate space for schema objects, and learn the three types—permanent, undo, and temporary—plus the prerequisites.

Learn to create a tablespace by specifying the data file or temp file name and size, enabling auto extend, and configuring extent management (auto allocate or uniform) with online availability.

Learn to create Oracle table spaces, apply the logging clause for redo log backup and recovery, and use auto bitmap or manual free lists for segment management.

Create a new pluggable database (PDB) with DBCA and a default tablespace. Connect via SQL Developer after updating tnsnames.ora and grant create session and DBA roles to the PDB admin.

Learn to query Oracle tablespaces in a pluggable database, identify default permanent and temporary tablespaces from database properties, and use dbms_metadata.get_ddl to view user definitions.

Create a permanent tablespace using Oracle managed files. Set a data file destination and rely on a default 100 megabyte size with auto extend.

Learn to view tablespace information and create a new permanent tablespace using the SQL developer wizard, configuring a 500 MB data file in a pluggable database.

Alter tablespace and datafile demonstrates resizing data files, adding new data files, and toggling read/write and read-only modes for table spaces, with practical DBA commands.

Move and rename online data files with alter database move data file, relocate to a new location, and perform queries, DML, and DDL during the move.

Learn to create and manage table spaces with read-write status, switch to online or offline options: normal, temporary, and immediate, set read-only when needed, and add or resize data files.

Explore how Oracle automatically manages storage space using Oracle managed files, local bitmap tablespace management, automatic segment space management, and proactive alerts to prevent file overwrites and capacity issues.

Explore segments, extents, and blocks in Oracle by creating a tablespace and a table, then observe how extents grow across eight kilobyte blocks and are tracked.

Explore how segments, extents, and blocks store table data, query dba extents and segments, and understand rowids. Compare delete and truncate behavior to manage space.

Explore how delete and update statements free space in a data block and how inserts reuse it in the same transaction or after commit, with Oracle coalescing fragmented space.

Explore row chaining and migrating in Oracle, where oversized rows spill across multiple data blocks or move to new blocks after updates, affecting performance due to extra I/O.

Explore the key Oracle segment types: table, cluster, index, undo, and temporary; learn how segments store data and the role of partition tables and undo tablespaces for rollback.

Explore deferred segment creation in Oracle, controlling when segments appear via the deferred segment creation parameter or immediate options in create table and alter session.

Explore space-saving techniques by creating unusable indexes, deferring segment creation, and rebuilding indexes to activate them, as illustrated with a sample employees table and primary key index.

Explore basic compression in the Oracle database, focusing on bulk loads and create table as select. Compare no compression to basic compression on data warehouses and OLTP workloads.

Explore basic compression in Oracle database administration, including direct insert via append, compress basic, and move to compress old data, reducing blocks and boosting data migration performance.

Explore advanced row compression, storing duplicates once per data block for rows inserted with or without direct path insert. Use row store compress advance; ideal for oltp and data warehouse.

Explore how to monitor Oracle tablespace usage, configure warning and critical thresholds with dbms_salver, and respond to alerts by resizing data files and managing temporary space.

Learn to use the dbms_server_alert package to set a tablespace threshold, configure warning and critical values for TPS alert, and monitor alerts in a pluggable database.

Learn to reclaim wasted space from fragmented free space using online segment shrink, with shrink space compact followed by shrink space and row movement considerations.

Use the segment advisor to identify segments that would benefit from online segment shrink, before shrinking tables, with locally managed tablespace and automatic segment space management, via dbms_advisor.

Learn to use segment advisor to shrink a tablespace, enable row movement on test 100, and apply shrink space based on Oracle recommendations.

Enable resumable space allocation to suspend long operations instead of failing, then resume after fixing space issues, with timeout settings and auto extend data files.