Oracle 11g Certified Professional Exam Prep III

Master advanced RMAN features, including persistent configuration options, retention policies, scripting with OS and recovery catalog, plus monitoring, reporting, point-in-time recovery, and cross-machine database duplication.

Examine configuring Arman settings, from disk backups to tape, set multiple channels, and tune storage in environment repository and recovery catalog, focusing on retention policy and obsolete versus expired backups.

Explore configuring persistent rman settings, including showing all backup options and enabling control file auto backup, then examine how retention and redundancy affect the flash recovery area and obsolete backups.

Master RMAN scripting to automate Oracle tasks by storing scripts in the OS or the recovery catalog. Learn about target-specific versus global scripts and repository storage.

Store and organize RMAN scripts on the operating system and in the recovery catalog, then create, edit, and run full backup scripts to back up databases.

Explore RMAN reporting to manage backups with list and report commands, enforcing retention policies, redundancy, cross-check, validate, and preview of backup needs.

Explore RMAN reporting in Oracle 11g by inspecting backup sets, archive logs, and data files, and learn how the report command guides restore actions to meet retention policies.

Monitor and tune Oracle 11g RMAN backups by configuring multiple channels, managing input and output buffers, and using multiplexing and large pool for faster parallel I/O.

Explore how to monitor and tune RMAN backups by allocating channels, setting rates, and querying long ops to track progress in the flash recovery area.

Explore tablespace point-in-time recovery in Oracle 11g, recovering a single tablespace to a prior state while the rest of the database stays current, using an auxiliary instance and self-contained spaces.

Demonstrates table space point in time recovery using an auxiliary instance, recovery set, and RMAN, including backups, archive logs, and export/import to restore a table space.

Learn to duplicate a database with RMAN, creating an auxiliary from a target on the same or different machines, from current data or backups, with archive logs and reset logs.

Duplicate an Oracle 11g database across two machines by copying to a wind test directory on the second drive, then shut down and mount for a cold backup.

Set up the networking between source and auxiliary machines, run the duplicate command, and register a wind test database service on the listener with proper tns aliases.

Set up a remote Oracle instance for database duplication by copying the parameter file, configuring wind test directories, and starting a Windows service with manual startup.

Duplicate the database by mapping data and log files from the active source to the wind test target, using the spfile and ESP file settings.

Master disaster recovery by restoring to a new host using backups on a common backup server and performing incomplete recovery with a restored control file.

Walk through a complete disaster recovery across three machines, set up a shared backups directory, and configure a Windows service account to enable backups to the wind client.

Configure control file auto backups to disk and direct backups to the backup server directory; perform disaster backups to capture control file and related backups for recovery.

Demonstrates disaster recovery on a Windows host by simulating a complete failure and restarting the Oracle service, preparing for the restore steps to recover the database.

Learn to perform disaster recovery by restoring backups to a new host using auto backup control files, then startup, mount, restore, and open the database with reset logs.

Learn how Oracle supports going back in time with flashback features such as flashback query, flashback table, flashback database, and flashback data archive, enabling point-in-time data access and recovery.

Explore flashback queries and undo in Oracle, viewing prior data states via undo segments and as of timestamps, guided by transaction and system change numbers.

Explore flashback query concepts in Oracle 11g by experimenting with undo data, as of timestamp queries, and system change number mapping to timestamp to view historical rows.

Demonstrate flashback versions query in Oracle 11g, using versions between to walk transaction history with versions operation, start time, end time, and transaction IDs.

Discover how flashback table renames dropped tables to a special name in a recycle bin, enabling recovery via flashback or a manual rename, while foreign keys may not restore automatically.

Explore the flashback table concept and flashback drop in oracle. See a dropped table renamed instead of deleted, and how to flash it back, noting describe and index caveats.

Explore flashback table behavior in Oracle, including locally managed table spaces, extents, and the recycle bin, and learn how overwriting extents affects flashback drop.

Explore Oracle 11g recycle bin basics using the user recycle bin view to map original names to operations, then perform flashback table or purge recycle bin with drop options.

Explore how flashback data archive extends undo to permanently store changes for a table. Set up flashback table spaces and archives with quotas and expiry terms to manage long-term data.

Explore flashback archive in Oracle 11g by creating a dedicated tablespace, attaching a test table, granting flashback archive permissions, and viewing history tables that track changes over five years.

Explore how flashback archive tracks column changes with the archive history and column map tables, including adding and dropping columns, and use as-of queries to view data across change numbers.

Explore flashback database, a fast alternative to incomplete recovery, using flashback logging and a flashback buffer that stores before images in flashback logs, with restore points and retention.

Demonstrate flashback database concepts by configuring a flash recovery area, archive logs, performing a test backup, and using a restore point to flashback and complete an incomplete recovery.

Introduce Oracle's ASRM, explore storage virtualization, outline architecture, disk groups, redundancy, mirroring, and failure groups, and explain how ASRM simplifies database storage decisions in single-instance setups.

Explore storage virtualization fundamentals by defining disks, physical volumes, disk groups, and logical volumes, and explain mirroring, striping, and redundancy in ASRM.

Understand the ASM architecture and how the HSM instance exposes storage metadata to database instances. See how disk groups and descriptors guide read and write actions.

Walks through installing asm on windows in a vm, initializes disks, creates unformatted partitions, stamps disks for disk group discovery, and configures a standalone grid for oracle restart.

Examine the grid installation files and services, note the listener and ASRM/Oracle high availability service, and preview installing the database software in its own Oracle home.

Demonstrates installing the Oracle database software as a separate Oracle home under the same Oracle base to enable independent patching, aligning with enterprise edition and ASRM setup before database creation.

In the DBCA demo, create a database with the database configuration assistant, enable enterprise manager, and observe Oracle Restart registration and the server control start-stop workflow.

Explore the ASRM instance in Oracle 11g, how it starts, how it provides storage metadata to the database for disk groups, and how it coordinates with cluster resources.

Explore the asm instance demo by examining grid and database oracle homes, windows services, startup, then connect to the asrm instance with sqlplus to understand hsm and cluster dynamics.

Demonstrates locating and moving the espie file for an ASM instance using cluster ready services and the esm command utility to mount disk groups managed by ASRM.

Learn how to configure and manage an ASM instance using ASRM, including setting the instance type, disk group mounting, and show parameter usage to control startup behavior.

Describe how ASM storage organizes disks into disk groups, allocation units and extents, with fine and coarse striping, and redundancy options (normal, high, external) via failure groups.

Explore ASM storage by discovering disks, stamping them with ASRM, and creating disk groups with normal and high redundancy; learn to use ASRM disk and ASRM disk group configuration assistant.

Provision storage with Aasim and use RASM for automatic database file placement, including data and control files and logs; learn how HSM balances extents to optimize IO across disks.

Provision storage with asm and asrm, create data and log files in disk groups or file systems, and configure flash recovery area for backups.

Demonstrates moving Oracle control files into ASRM via HSM, configuring disk groups for data and logs, and using RMAN to restore control files into the new locations.

Oracle's resource manager allocates machine resources among user classes, including DBAs, reporting, and transactional users, by buckets and CPU limits, enabling a resource plan.

Explore the resource manager architecture, detailing sessions, consumer groups, resource plans, and directives that allocate cpu, undo, and sessions to groups, with plan switching via the scheduler.

Understand resource consumer groups as collections of sessions that receive allocated resources by group, not by user, with sessions able to move between default and other groups to balance needs.

Create and validate consumer groups with DBMS resource manager, manage a pending area, and submit to allocate resources, using default round-robin and later plan directives.

explore how resource groups allocate and restrict cpu through resource plans, using percentage-based and ratio-based allocations, parallel query limits, active sessions, undo caps, idle time, and maximum execution time throttling.

Create a resource plan to allocate cpu resources among groups, nest plans for multi-level allocations, assign resources to each group, and switch plans to fit day or weekend needs.

Explain how sessions map to consumer groups using rules based on user or program, with initial and default groups, runtime thresholds, and possible re-evaluation or DBA switches.

Demonstrate creating a simple resource plan in Oracle 11g, allocate 60 percent CPU to test group one and 40 percent CPU to test group two, and apply it.

Demonstrates building a resource plan the hard way: create a pending area and plan directives for test group one (60%) and test group two (40%), while accounting for other groups.

Create a comprehensive plan by building groups, using ad hoc queries in the finance and inventory groups, and applying cpu and execution-time limits to demonstrate master plan with sub plans.

Build a complete Oracle 11g resource plan by creating resource consumer groups, defining top level and subplans, allocating CPU percentages, and validating and submitting the plan.

Log into the windmill for machine database 11.2.0.2, enable the company resource plan, and map a test user to the test query group to show CPU throttling.

Demonstrates configuring Oracle resource manager to throttle cpu with a hard 50 percent limit, and switching the test query group to other groups after a 10-second window, validating cpu behavior.

Explore Oracle scheduler, the database's scheduling system, covering program, schedule, and job creation, events, and resource aware, asynchronous, detached jobs, including chain jobs.

Understand how the Oracle 11g scheduler uses the DBMS_scheduler package to manage programs, schedules, and jobs via a single job table and the job queue coordinator and slaves.

Demonstrates how to verify the job queue coordinator is running, view scheduler jobs and next run times, disable and re-enable the queue, and bounce the instance to wake the scheduler.

Explore the DBMS scheduler components, including programs, schedules, and jobs, and how a scheduler job can be self-contained. Understand job types, actions, and the repeat interval with calendaring syntax.

Explore how a program extends a simple action with metadata and arguments, enabling inline, stored procedure, or executable actions, including event-driven use and email via a partial script.

Create and test a PowerShell executable that sends email with a sendmail script, exploring parameters and SMTP setup on a Windows environment, preparing for database integration in the next video.

Enable the database job scheduler, grant create external job to HR, and create an executable program and a corresponding job to send mail with script, subject, and body parameters.

Define when to run and what to run using time-based or event-based schedules. Use repeating calendars, start and end times, and interval rules via calendaring syntax to create flexible schedules.

Create a self-contained DBMS scheduler job that inserts timestamps into a test table every minute, grant create job privileges, enable the job, and verify next run date.

Explore advanced scheduler options, including job classes, windows, and window groups to throttle resources, schedule jobs, and support job chains, credentials, file watchers, and detached execution.

Explore how to use the Oracle 11g DBMS scheduler to load data into a table, process end-of-month records with a two-step job chain, and email a summary.

The demo shows how to create a job chain in Oracle 11g scheduler that loads data into tables, processes it, and sends an email.

Develop proactive monitoring of the Oracle 11g database to stay ahead of issues, rather than firefighting. Explore enterprise edition manageability packs—diagnostics, tuning, and change management—and alternatives for standard editions.

Discover how Oracle collects server and session statistics from in-memory structures, uses metrics to reveal IO and workload, and how to view these through v$statname and v$session_stat.

Explore Oracle statistics by listing statistic names and querying system and session stats. Compare server-wide and session data to interpret sql activity, sorts, and recursive calls.

Learn how the automatic workload repository captures hourly statistics snapshots with the manageability monitor and purge processes, stores them in the AWR, and uses the scheduler and maintenance windows.

Explore how the AWR snapshots and workload repository in Oracle Enterprise Manager expose statistics, snapshot reports, and performance data to identify bottlenecks.

Identify performance issues by using the Automatic Database Diagnostic Monitor to compare snapshots, spot the top five slow SQL statements, and guide further analysis with tuning and repair advisors.

Learn how to run an addm analysis in enterprise manager, view admin runs, and interpret the edm performance analysis and top sql statements.

Explore SQL advisors—access, tuning, and repair—to optimize performance by analyzing statements, access paths, and optimizer statistics, and auto-generate tuning sets and recovery tasks.

Explore the Oracle 11g tuning adviser workflow by running a workload, capturing snapshots via the automatic workload repository, and observe the tuning adviser recommendations after the next snapshot.

Identify high-cost SQL statements using the IDEM advisor, then run the scale tuning advisor to optimize a resource-heavy select, and initiate a cycle tuning advisor installation.

Explore how, in Oracle 11g, the tuning advisor uses table statistics, creates a skull profile, and compares explained plans to optimize hash joins and reduce full table scans.

See how tuning advisors and the equal access advisor use a created index to cut a statement's runtime and boost performance. Monitor with the diagnostics monitor for server-generated alerts.

Explore oracle 11g additional advisors, including sga and pga memory advisors, undo management, mean time to recover, data recovery, and the segment divisor for coalescing extents.

Understand the server generated alert system, configure warning and critical thresholds and consecutive occurrences, and route alerts to the alert queue via enterprise manager or db console.

Demonstrates triggering and monitoring alerts in Oracle enterprise manager, using the alert queue and DBA outstanding alerts, and configuring thresholds on metrics like user commits per second.

Oracle centralizes diagnostic data in a diagnostics repository, collecting alert logs and trace files under Oracle base or Oracle home, with per database and instance folders under the RDBMS directory.

Explore the diagnostics repository contents, tracing tools, and alerting infrastructure that help diagnose database problems. Learn to generate and package diagnostic data for Oracle support and subscribe to server-generated alerts.

Explore the Oracle diagnostics repository, including the rdbms directory, alert log, xml-based alert log, trace files, and incident packaging, to diagnose database issues and prepare support data for Oracle.

Demonstrates generating server alerts by corrupting a data file in a new tablespace and auditing the results through Enterprise Manager and the diagnostic repository.

Leverage the diagnostic repository and enterprise manager to view server alerts and active incidents, inspect trace files, and package incident data for Oracle support via command line and support workbench.