Oracle 11g Certified Professional Exam Prep I

Install oracle software on Windows, cover prerequisites, install the binaries separate from database creation, then perform checks of directories, registry entries, and shortcuts, and note system groups and user implications.

Identify the essential prerequisites for installing Oracle 11g, including 64-bit server OS, client libraries, hardware specs, DNS/hostname setup, DHCP avoidance, and the need for a dedicated, admin-privileged machine before installation.

Explains installing Oracle database software on a Windows VM, configuring Oracle base and home, and running a single-instance enterprise edition install with an optional response file.

Explore the Oracle base and home directories, and tools like SQL Plus and the database configuration assistant in the post-installation demo, with databases to come in later videos.

Learn how operating system authentication grants super user access to Oracle databases via the new group created during installation, and why careful management of that group matters before database creation.

Explore creating an Oracle database using two methods—the database configuration assistant and by hand with SQL scripts—then compare their differences, instance versus database concepts, and start-up phases.

Create and start an Oracle listener to enable the database configuration assistant and enterprise manager. Use net manager to define the listener, then run lsnrctl to start it.

Learn to create an Oracle 11g database with the Database Configuration Assistant, using built-in templates and configuring options like memory, passwords, and Enterprise Manager.

Complete the dbca setup, review Wind Test, locate the server parameter file, note encryption key storage in the Oracle home, and prepare for enterprise manager.

Master Oracle administration with SQL Plus command-line work and Oracle Enterprise Manager Database Control, including OS authentication, super user access, and database management basics.

Manually create an Oracle database by configuring name, directories, and a parameter file, then run create database and scripts to build dictionary views, default packages, and public user profile tables.

Name the database window and place its files in the wind directory, then configure a minimalist parameter file and a control file with sga_target one gig and pga_aggregate_target 128 meg.

Create a Windows service for the Oracle instance with AUDIN, enable on demand startup, and understand the instance versus database as you prepare to create the database.

Create the database using Oracle's create database statement after configuring the parameter file, directory, and service, defining instance and database names, and allocating log, data, undo, and temp files.

Create the missing dictionary views by running catalog scripts from the oracle home rdbms/admin directory, enabling the data dictionary and the deba users view.

Install the default pl/sql packages by running the Caprock script, creating public tables, public types, and views, and enabling the database's procedural option.

Create a non-standard user and grant connect in sql plus. Install the product user profile tables by running the purp build product user profile script as the system user.

Compare manual database creation with the database configuration assistant, showing how DCA saves scripts, configures directories and passwords, performs RMAN restore and control file steps, and enables enterprise manager setup.

Configure the enterprise manager interface by running the configuration assistant to create the repository, then set up the database control with the correct port 1159, listener, and credentials.

Learn to create a password file, specify its location, and log into Enterprise Manager to manage two databases—one created by the configuration assistant and one by hand.

Explore instanced management by configuring parameters and starting or stopping an Oracle instance, using sqlplus and database control for administration, and review the data dictionary and alert log.

Master sqlplus, the ubiquitous Oracle tool for startup, shutdown, backup, and script execution without extra software. Use it to view substitution variables, parameter values, and SGA information.

Demonstrate sql+ from the command line: login with os authentication, start and stop instances, run scripts, and inspect sga, parameters, and archiving; preview enterprise manager db console.

Explore Oracle database control (enterprise manager) as a web-based graphical tool for startup, shutdown, backups, and maintenance, with grid control for centralized management via agents.

Demonstrates configuring and using Oracle database control (enterprise manager) to administer a single instance, starting the console, listener, and database service; explores performance management and licensing options.

Configure and control the Oracle database by managing parameters, including the parameter file and server parameter file, the DB name parameter, and the basic, advanced, and hidden parameter types.

Identify whether the instance uses a regular or server parameter file. Learn to modify memory target and memory max target with scoped alter system commands.

Explore the Oracle instance startup phases: no mount, mount, and open—and how the instance can start without the database for backup, recovery, or initial creation.

Explore the Oracle 11g instance startup process, from starting the service to startup no mount, mount, and open phases, including handling of the control file, data files, and log files.

Discover how the Oracle data dictionary provides metadata via dictionary views, examine three access levels (user_, all_, dba_), and distinguish static dictionary views from dynamic views for schema and security.

Explore the Oracle data dictionary with practical demos, describe dictionary views, query dba_users and user_tables, and examine access control and security through the DB console and Enterprise Manager.

Oracle database storage structures are outlined, detailing data files, online log stream, and control files, and their roles in data permanence, recovery, and file tracking.

Store data using Oracle data files, which grow by adding or resizing files, with no limit on number or size. Use a single block size and group files into tablespaces.

Explore how a table space groups data files into a single storage unit, assigns users to table spaces, and uses system and sissoko spaces with temporary and undo spaces.

Navigate Oracle table spaces and data files, viewing system table space details via DBA data files. Allocate and auto extend data files, and create a new table space like my_application.

Explore managing Oracle tablespaces and data files: offline and online operations, moving files across directories, and dropping a tablespace, with control file updates and archive logging caveats.

Explore Oracle managed files by turning on DB_CREATE_FILE_DEST, creating a directory, and letting the system automatically manage data file locations for tablespaces and online logs.

Explore how Oracle records every data request and update in the online log, writing entries to the log buffer and periodically flushing them to disk to support failure recovery.

Explore how the Oracle online log system uses log groups and mirrored log file members, with switch and checkpoint mechanics, to manage committed and uncommitted data during log activity.

Explore how an Oracle 11g instance at startup replays the online log to restore the database state, reconciling uncommitted versus committed transactions.

Explain how checkpointing coordinates the log writer, log reader, and database writer to flush log buffers to disk, using multiple log files and groups to allow safe overwrites.

Track data and log files, including names, locations, checkpoint information, and backup recovery information, using the control files. Keep copies on separate disks as directed by the parameter file.

Demonstrates managing Oracle control files: inspecting control file structure, duplicating a control file, configuring multiple control files with an spfile, and verifying via dynamic views.

Explore Oracle's client-server networking, focusing on the TINASHE protocol above the transport layer, server listener configuration, and client library installation, plus diagnosing common connection errors.

Explore how Oracle’s transparent network substrate enables lightweight client libraries to reach the server via a tcp/ip protocol adapter, including lookups of tns aliases and required translations.

Learn how the listener on a database server manages external connections by starting foreground server processes, establishing sessions with clients, and registering instances for connection requests.

Configure the listener with a name, address, and port via the listener profile in Oracle Home Network admin directory, and note database registration at startup with dynamic registration.

Explore configuring and managing Oracle listener with listener.ora and net manager, including dynamic registration, services, and start-stop control via the listener control utility.

Set up a separate client VM and install 32-bit Oracle client on a 64-bit OS using the Java-based installer, selecting administrator package and configuring Oracle Base and client directory.

Complete the Oracle client libraries installation, review net manager and the net configuration assistant, and note that SQL Developer is included; future videos show connecting clients to the server.

Explore how a client resolves a database connection by using a TNS alias and name resolution, including host, instance name, and listener port, via TNSNAMES.ORA or LDAP directory.

Set up Oracle client aliases with the net configuration assistant and net manager, configure tnsnames.ora for wind test, and test a tcp connection to the wind test instance.

Learn to configure name resolution with net manager, create service names and DNS aliases, test TCP/IP connections, and diagnose listener and database instance availability.

Learn to run multiple Oracle clients on one machine by centralizing tnsnames.ora and networking files, and using a single path and tns_admin to maintain connectivity.

Compare dedicated and shared server architectures, focusing on dispatchers, shared servers, and request and response queues, and explain how PGA memory moves to the shared or large pool.

Set up and verify shared servers by configuring dispatchers and shared servers, then test connections with dedicated and shared server listeners.

Diagnose Oracle connection issues by verifying client-to-server connectivity with ping, resolving aliases with the tennis ping utility, and confirming the listener and instance are running and registered.

Demonstrates common Oracle networking errors and a practical troubleshooting flow, from pinging the server and resolving the alias to verifying the listener, static registration, and running instances.

Define the schema as a user's data collection, add users and sessions, and use the concept of a rule to manage privileges, with profiles to limit resources.

Define the schema as a collection of tables, views, indexes, and constraints owned by a user, and note that in Oracle, the terms user and schema are synonymous.

learn how to create a user in Oracle 11g, distinguish user creation from sessions, configure authentication options, and set default and temporary table spaces, quotas, and privileges.

Learn how to create a database user with command line and database control, assign a default and temporary tablespace, set a 10 MB quota, and note login and capitalization considerations.

Learn how to use operating system authentication for Oracle 11g by configuring the OS authentication prefix and creating externally identified users, including Windows domain usage.

Learn how Oracle uses system and object privileges to control user actions, and how grant option and admin option govern permission delegation and cascade revoke.

Demonstrate granting and revoking system and object privileges in Oracle 11g, from create session to create user with admin option, and illustrate cascading rights and namespaces.

Create a role to group common privileges and grant create table, create view, and create session to the role; then assign the role to users so updates apply to all.

This lecture demonstrates creating a developer role in Oracle 11g, granting system privileges, and assigning the role to Dave and Fred, with grantees and defaults explored via dictionary views.

Profiles enforce password management with failed login attempts, account lockouts, and a school function, while offering deprecated basic session restrictions; a demonstration creates a password-based profile requiring uppercase letters.

The lecture demonstrates creating a developer profile that limits sessions to two, enforces a password verify function, and enables resource limits by setting resource_limit to true in Oracle 11g.

Explore heap tables and views as basic schema objects, then preview bitmap and bitrate indexes, plus the five constraints, segments, and extents in space management.

Learn how Oracle tables store data as named column collections, with 30-character names, namespace rules, heap storage, and key data types such as number, date, varchar, char, raw, and timestamp.

Create and manage tables, define columns with numbers, varchar, and dates, explore naming conventions and quotes, and understand drop and recycle bin behavior with purge options.

Explore views as named, stored select statements that hide underlying tables and simplify reporting, as salary history joins multiple tables to present user-friendly data and illustrate the data dictionary concept.

Demonstrate creating a view called employee_department that joins employees and departments to present employee id, first name, last name, and department name, showing how views hide joins.

Discover how Oracle 11g uses indexes to speed queries, covering B-tree and bitmap indexes, real IDs and row IDs, and advanced types like unique, composite, compressed, and function-based indexes.

Use SQL Developer to set up the indexes demo and view the explained plan for queries by creating a JDBC connection to the wind test database.

Explore how an index speeds data access by contrasting a full table scan with an index search, using the explain plan and auto trace to reveal query costs.

Explore the five constraints in Oracle databases—not null, unique, primary key, foreign key, and check—and learn how they enforce data cleanliness, relationships (including composite keys), and validation with demonstrations.

Explore how Oracle enforces not null and unique constraints, how null values affect uniqueness, and how inserts reveal constraint violations.

Identify how the primary key combines not null and unique constraints to uniquely identify rows, using single-column and composite keys, with examples from the HR schema.

Explore foreign key constraints and the parent–child relationship; create tables, enforce primary and foreign keys, and observe data integrity in insert, delete, and check constraints.

Demonstrate creating and testing check constraints with alter table add constraint, using less than 100 and greater than or equal to 100 on a test table.

Turn constraints on and off to demonstrate how data can violate a constraint, then use the Oracle exceptions table to identify and delete violating rows and re-enable primary key constraint.

Explore how Oracle manages space by allocating extents to segments within a tablespace, expanding data files, and freeing space when objects are dropped or truncated.

Explore concurrency concepts in a multi-user oracle database and how undo provides a consistent view while the undoubtable space concept serves as temporary storage for older data.

Explore concurrency concepts in Oracle, including transactions and data versioning. Learn how readers never block writers, while writers block other writers.

Demonstrate concurrency in Oracle by showing how reads and writes occur across multiple sessions, including inserts, deletes, commits, locks, and deadlock resolution.

Explore how Oracle enforces concurrency through transactions, an all-or-nothing unit of work. Understand commit makes changes permanent and rollback undoes them, with implicit transaction boundaries triggered by non-insert/update/delete statements.

Explore transaction concepts through hands-on demos, showing how commits, rollbacks, and isolation levels affect visibility across sessions in Oracle 11g.

Explore the undoubtable space handles in-flight updates by caching the original block, copying and updating it, writing a log entry, and preparing for rollback or commit.

Understand how undo tablespaces manage concurrent updates by storing older block versions, aging them to disk, and enabling flashback via automatic undo management and retention policies.

Explore bulk data loading across databases using data pump utilities for import and export, and learn Siecle Loader and external tables that read flat files.

Discover how data pump exports production data from Oracle to operating system files and imports it into a test system, enabling regression testing and bug fix validation.

Explore the export data pump utility, a server-side command-line tool that uses the DBMS metadata and data pump packages to export data and metadata via master and worker processes.

Set up datapump demos by configuring a separate virtual machine with production and test databases, and simulate data transfer using export and import data pump under the HR user.

Demonstrates exporting a single table from HR schema with Oracle data pump, creating a directory object, granting access, and running a data pump job to generate dump and log files.

This lecture demonstrates exporting an entire schema with the data pump as the system user, using a master table to coordinate workers, monitor status, and dump files.

Import data pump reverses export data pump to bring data and metadata into the system, supports schema recreation and remapping, and inserting, replacing, or appending data.

Learn how to refresh test data by importing production data with data pump, create directories, import the HR schema, and remap or overwrite data as needed.

Explore bulk loading with sql loader via a control file that describes data, and manage bad and discard files, choosing standard or direct path load methods.

Demonstrates SQL loader usage to load tab-delimited data into the last_names table, remove comma formatting, and handle a bad record with a bad file and log.

Create an external table that sources data from external files and query it on demand using sql loader or data pump libraries, without reloading data.

Demonstrates creating and querying an Oracle external table with Oracle Loader libraries, mapping a fixed-length flat file to HR data for live access.