Oracle Database Administration II cheat sheet

RMAN persistence: CONFIGURE settings are stored in the target control file, or in the recovery catalog when one is used; SHOW ALL lists current values and CLEAR resets to default.

Retention policies: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS keeps everything needed to recover to any point in the last n days; TO REDUNDANCY n keeps n copies of each file.

Backup types: full (all blocks), incremental level 0 (all used blocks, base for later incrementals), level 1 differential (blocks changed since last level 0 or 1), level 1 cumulative (blocks changed since last level 0).

Block change tracking (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING) records changed blocks in a CTWR-maintained file and lets level 1 backups scan only changed blocks; it does not change what is backed up.

BACKUP ... PLUS ARCHIVELOG switches the current log, archives, backs up archived logs, then backs up datafiles; this yields a consistent recoverable set without a separate log backup step.

BACKUP INCREMENTAL LEVEL 1 ... FOR RECOVER OF COPY WITH TAG creates a fast incremental roll-forward strategy: a level 0 image copy plus daily incrementals that can be applied to the copy.

RMAN compression: BACKUP ... AS COMPRESSED BACKUPSET uses MEDIUM (default, BZIP2-like) or HIGH (ZLIB) algorithms; compression reduces backup size at the cost of CPU during backup and restore.

Encryption: CONFIGURE ENCRYPTION FOR DATABASE ON with a wallet (ENCRYPTION WALLET) encrypts backupsets transparently; TRANSPARENT mode encrypts only when a key is available, otherwise writes plaintext.

VALIDATE and RESTORE ... VALIDATE check backup integrity and block-level corruption without writing restored files; BACKUP VALIDATE can detect physical and logical block corruption.

RESTORE copies files back from backups; RECOVER applies redo (archived and online) to make them consistent. A restore without recover usually leaves files at an older SCN.

Point-in-time recovery: SET UNTIL TIME/SCN/SEQUENCE or RESTORE ... UNTIL; recovery stops at the target and requires a subsequent OPEN RESETLOGS when the target precedes the current log sequence.

Incomplete recovery always requires OPEN RESETLOGS, which creates a new incarnation; full recovery of an open database does not.

Media recovery with a missing or corrupted current online redo log needs a backup control file and either a full restore or recovery to a point before the missing log.

Tablespace point-in-time recovery (TSPITR) uses a auxiliary instance, restores and recovers the tablespace to a target time, then exports/imports or uses transportable tablespaces to move the recovered objects into the production database.

Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) diagnoses failures from the control file and can generate and execute repair scripts; repair is only possible for failures it can fix automatically.

Restore optimization: RMAN restores only files that are missing or older than the recovery target; RESTORE ... PREVIEW lists the files and backups RMAN would use.

Crosscheck (CROSSCHECK) validates that backups recorded in the repository still exist and marks expired ones; DELETE EXPIRED removes those records, while DELETE OBSOLETE removes backups that violate retention.

Block media recovery (BLOCKRECOVER) restores and recovers individual corrupt blocks from a backup or flashback log while the database stays open, using the current datafile as the base.

Flashback Query (AS OF TIMESTAMP/SCN) reads past versions of rows from undo; it needs undo retention long enough to cover the target time and does not change data.

Flashback Version Query (VERSIONS BETWEEN) and Flashback Transaction Query show row history and the transactions that changed rows, using undo and, for transaction query, the redo/undo metadata.

Flashback Table restores a table to a past point using undo and requires the table's row movement to be enabled; it does not recover dropped tables.

Flashback Drop restores a dropped table from the recycle bin (FLASHBACK TABLE t TO BEFORE DROP); the recycle bin can be purged with PURGE RECYCLEBIN or PURGE TABLE.

Flashback Database rewinds the whole database using flashback logs; it requires ARCHIVELOG mode, a flash recovery area, and DB_FLASHBACK_RETENTION_TARGET, and ends with OPEN RESETLOGS.

Guaranteed restore points (CREATE RESTORE POINT ... GUARANTEE FLASHBACK DATABASE) protect flashback logs from deletion so a flashback to that point remains possible.

Flashback Data Archive (FDA) tracks row history for long periods independent of undo retention; it records changes in a tablespace and is configured with CREATE FLASHBACK ARCHIVE.

Data Recovery Advisor works only when the database is in a mounted or open state; failures are detected automatically or with LIST FAILURE, and ADVISE FAILURE produces a repair script that REPAIR FAILURE can execute.

Flashback Database cannot be used after a datafile is lost or a control file restore; those cases require RMAN restore and recovery instead.

A CDB contains one root container (CDB$ROOT), one seed PDB (PDB$SEED), and zero or more user PDBs; the root and seed are read-only and cannot be dropped.

The root stores common users and common objects; a PDB has its own local users, schemas, and data dictionary views, and its datafiles live under its own directory.

Connecting as a common user requires the common user name prefixed with C##; local users are created inside a PDB and are visible only there.

A PDB is opened with ALTER PLUGGABLE DATABASE pdb OPEN; OPEN READ ONLY or OPEN RESTRICTED limits access. MOUNTED state allows some maintenance but not user connections.

PDB$SEED is used as a template by CREATE PLUGGABLE DATABASE ... FROM PDB$SEED; it must remain read-only and unmodified.

The CDB root has one undo tablespace; each PDB can have its own local undo tablespace when LOCAL UNDO is enabled, which supports PDB cloning and point-in-time recovery.

A common user with SYSDBA or SYSOPER privilege can connect to any PDB; a local user with those privileges can connect only to its own PDB.

ALTER PLUGGABLE DATABASE ... SAVE STATE persists the open mode across CDB restarts when the PDB is in the root's saved state list.

CREATE PLUGGABLE DATABASE ... ADMIN USER ... IDENTIFIED BY ... creates a PDB with a local admin user; the ADMIN USER clause is required unless the PDB is created from a seed with an existing admin.

Cloning a PDB: CREATE PLUGGABLE DATABASE new FROM old; the source must be read-only and the clone uses the source's datafiles or a snapshot copy.

PDB snapshot clones (SNAPSHOT COPY) require the source PDB to be read-only and use storage-level snapshots; they are fast but depend on storage support.

Unplugging a PDB (ALTER PLUGGABLE DATABASE ... UNPLUG INTO file) writes an XML manifest; the PDB is then dropped from the CDB and can be plugged into another CDB with CREATE PLUGGABLE DATABASE ... USING manifest.

A PDB can be relocated with CREATE PLUGGABLE DATABASE ... FROM ...@dblink or via a manifest file; the source and destination must have compatible character sets and endianness or use CONVERT.

Resource management for PDBs uses CDB resource plans (CREATE PLAN in the root) with shares, utilization limits, and parallel server limits per PDB, applied with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN.

PDB lockdown profiles restrict operations (such as ALTER SYSTEM or certain packages) for a PDB and are assigned with ALTER PLUGGABLE DATABASE ... SET LOCKDOWN PROFILE.

A PDB's MAX_STRING_SIZE, time zone, and character set must be compatible with the CDB; the CDB's character set is inherited and cannot be changed per PDB.

Transportable tablespaces move datafiles between databases without exporting rows: the tablespace is made read-only, exported with TRANSPORT_TABLESPACE=Y, and the datafiles and dump are copied to the target.

The source and target must have the same database character set and national character set, and compatible endianness or the tablespace must be converted with RMAN CONVERT TABLESPACE.

RMAN CONVERT TABLESPACE ... TO PLATFORM ... produces converted datafiles for a different-endianness platform; CONVERT DATAFILE works on individual files.

Data Pump export/import (expdp/impdp) moves schema objects; TRANSPORT_TABLESPACES=Y in the export and import performs a transportable tablespace move.

The target database must not already contain a tablespace with the same name; plugging the tablespace into a different database requires the datafiles to be copied and the tablespace imported.

Cross-platform transport requires the source and target platforms to be listed in V$TRANSPORTABLE_PLATFORM; RMAN CONVERT DATABASE can convert a whole database for transport.

Full transportable export (FULL=Y TRANSPORTABLE=Y) moves an entire database, including the SYSTEM and SYSAUX tablespaces, using Data Pump and RMAN conversion when needed.

After transport, the tablespace in the source can be made read-only or dropped; the target must run the import with the datafiles in place, then the tablespace is read-write.

AWR snapshots are stored in the SYSAUX tablespace and are controlled by STATISTICS_LEVEL (TYPICAL or ALL); reports are generated with awrrpt.sql or DBMS_WORKLOAD_REPOSITORY.

ASH samples active sessions every second and is the basis for top-event and blocking analysis; V$ACTIVE_SESSION_HISTORY holds in-memory samples and DBA_HIST_ACTIVE_SESS_HISTORY holds persisted ones.

ADDM runs automatically after each AWR snapshot and reports the top findings with estimated impact; DBMS_ADVISOR can run it manually.

SQL Tuning Advisor (DBMS_SQLTUNE) analyzes a SQL statement and may recommend indexes, statistics, or SQL profiles; SQL Access Advisor recommends indexes and materialized views for a workload.

SQL plan baselines (DBMS_SPM) capture an accepted plan and prevent plan regressions; plans are added from the cursor cache or SQL tuning sets and are evolved with EVOLVE_SQL_PLAN_BASELINE.

Automatic SQL tuning runs during maintenance windows and can create SQL profiles for high-load statements; it is controlled by the automatic SQL tuning task and its result is visible in DBA_ADVISOR.

Real-time SQL monitoring (V$SQL_MONITOR) activates for statements that run long or consume significant resources; DBMS_SQLTUNE.REPORT_SQL_MONITOR produces the report.

The SQL Tuning Set (DBMS_SQLTUNE) captures statements for analysis; SQL Performance Analyzer (DBMS_SQLPA) compares performance before and after a change.

The Resource Manager plan is activated with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = plan_name; the plan is stored in the data dictionary and applies to sessions in the CDB or PDB.

Resource consumer groups assign sessions via DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP and mapping rules; the default group is OTHER_GROUPS, which must be included in every plan.

Plan directives set CPU allocation by shares or by utilization limit, and can limit parallel degree, active sessions, and undo space per consumer group.

The Scheduler runs jobs defined with DBMS_SCHEDULER; a job needs a program or a command, a schedule, and enabled state, and runs under a job class for resource mapping.

Scheduler windows (CREATE WINDOW) define time periods with a resource plan; window groups combine windows and are activated by the scheduler.

Automated maintenance tasks (Automatic Workload Repository, Automatic Database Diagnostic Monitor, Automatic SQL Tuning, Segment Advisor) run in maintenance windows defined by the MAINTENANCE_WINDOW_GROUP.

Maintenance tasks are enabled or disabled with DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE and their clients are listed in DBA_AUTOTASK_CLIENT; DBA_AUTOTASK_OPERATION shows operations.

A job's repeat interval uses calendaring syntax (FREQ=DAILY; BYHOUR=2), and DBMS_SCHEDULER.RUN_JOB can force a run outside the schedule.

RMAN persistence: CONFIGURE settings are stored in the target control file, or in the recovery catalog when one is used; SHOW ALL lists current values and CLEAR resets to default.

Retention policies: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS keeps everything needed to recover to any point in the last n days; TO REDUNDANCY n keeps n copies of each file.

Backup types: full (all blocks), incremental level 0 (all used blocks, base for later incrementals), level 1 differential (blocks changed since last level 0 or 1), level 1 cumulative (blocks changed since last level 0).

Block change tracking (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING) records changed blocks in a CTWR-maintained file and lets level 1 backups scan only changed blocks; it does not change what is backed up.

BACKUP ... PLUS ARCHIVELOG switches the current log, archives, backs up archived logs, then backs up datafiles; this yields a consistent recoverable set without a separate log backup step.

BACKUP INCREMENTAL LEVEL 1 ... FOR RECOVER OF COPY WITH TAG creates a fast incremental roll-forward strategy: a level 0 image copy plus daily incrementals that can be applied to the copy.

RESTORE copies files back from backups; RECOVER applies redo (archived and online) to make them consistent. A restore without recover usually leaves files at an older SCN.

Point-in-time recovery: SET UNTIL TIME/SCN/SEQUENCE or RESTORE ... UNTIL; recovery stops at the target and requires a subsequent OPEN RESETLOGS when the target precedes the current log sequence.

Incomplete recovery always requires OPEN RESETLOGS, which creates a new incarnation; full recovery of an open database does not.

Media recovery with a missing or corrupted current online redo log needs a backup control file and either a full restore or recovery to a point before the missing log.

Tablespace point-in-time recovery (TSPITR) uses an auxiliary instance, restores and recovers the tablespace to a target time, then exports/imports or uses transportable tablespaces to move the recovered objects into the production database.

Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) diagnoses failures from the control file and can generate and execute repair scripts; repair is only possible for failures it can fix automatically.

Restore optimization: RMAN restores only files that are missing or older than the recovery target; RESTORE ... PREVIEW lists the files and backups RMAN would use.

Crosscheck (CROSSCHECK) validates that backups recorded in the repository still exist and marks expired ones; DELETE EXPIRED removes those records, while DELETE OBSOLETE removes backups that violate retention.

Block media recovery (BLOCKRECOVER) restores and recovers individual corrupt blocks from a backup or flashback log while the database stays open, using the current datafile as the base.

Flashback Query (AS OF TIMESTAMP/SCN) reads past versions of rows from undo; it needs undo retention long enough to cover the target time and does not change data.

Flashback Version Query (VERSIONS BETWEEN) and Flashback Transaction Query show row history and the transactions that changed rows, using undo and, for transaction query, the redo/undo metadata.

Flashback Table restores a table to a past point using undo and requires the table's row movement to be enabled; it does not recover dropped tables.

Flashback Drop restores a dropped table from the recycle bin (FLASHBACK TABLE t TO BEFORE DROP); the recycle bin can be purged with PURGE RECYCLEBIN or PURGE TABLE.

Flashback Database rewinds the whole database using flashback logs; it requires ARCHIVELOG mode, a flash recovery area, and DB_FLASHBACK_RETENTION_TARGET, and ends with OPEN RESETLOGS.

Guaranteed restore points (CREATE RESTORE POINT ... GUARANTEE FLASHBACK DATABASE) protect flashback logs from deletion so a flashback to that point remains possible.

Flashback Data Archive (FDA) tracks row history for long periods independent of undo retention; it records changes in a tablespace and is configured with CREATE FLASHBACK ARCHIVE.

Data Recovery Advisor works only when the database is in a mounted or open state; failures are detected automatically or with LIST FAILURE, and ADVISE FAILURE produces a repair script that REPAIR FAILURE can execute.

Flashback Database cannot be used after a datafile is lost or a control file restore; those cases require RMAN restore and recovery instead.

A CDB contains one root container (CDB$ROOT), one seed PDB (PDB$SEED), and zero or more user PDBs; the root and seed are read-only and cannot be dropped.

The root stores common users and common objects; a PDB has its own local users, schemas, and data dictionary views, and its datafiles live under its own directory.

Connecting as a common user requires the common user name prefixed with C##; local users are created inside a PDB and are visible only there.

A PDB is opened with ALTER PLUGGABLE DATABASE pdb OPEN; OPEN READ ONLY or OPEN RESTRICTED limits access. MOUNTED state allows some maintenance but not user connections.

PDB$SEED is used as a template by CREATE PLUGGABLE DATABASE ... FROM PDB$SEED; it must remain read-only and unmodified.

The CDB root has one undo tablespace; each PDB can have its own local undo tablespace when LOCAL UNDO is enabled, which supports PDB cloning and point-in-time recovery.

A common user with SYSDBA or SYSOPER privilege can connect to any PDB; a local user with those privileges can connect only to its own PDB.

ALTER PLUGGABLE DATABASE ... SAVE STATE persists the open mode across CDB restarts when the PDB is in the root's saved state list.

CREATE PLUGGABLE DATABASE ... ADMIN USER ... IDENTIFIED BY ... creates a PDB with a local admin user; the ADMIN USER clause is required unless the PDB is created from a seed with an existing admin.

Cloning a PDB: CREATE PLUGGABLE DATABASE new FROM old; the source must be read-only and the clone uses the source's datafiles or a snapshot copy.

PDB snapshot clones (SNAPSHOT COPY) require the source PDB to be read-only and use storage-level snapshots; they are fast but depend on storage support.

Unplugging a PDB (ALTER PLUGGABLE DATABASE ... UNPLUG INTO file) writes an XML manifest; the PDB is then dropped from the CDB and can be plugged into another CDB with CREATE PLUGGABLE DATABASE ... USING manifest.

A PDB can be relocated with CREATE PLUGGABLE DATABASE ... FROM ...@dblink or via a manifest file; the source and destination must have compatible character sets and endianness or use CONVERT.

Resource management for PDBs uses CDB resource plans (CREATE PLAN in the root) with shares, utilization limits, and parallel server limits per PDB, applied with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN.

PDB lockdown profiles restrict operations (such as ALTER SYSTEM or certain packages) for a PDB and are assigned with ALTER PLUGGABLE DATABASE ... SET LOCKDOWN PROFILE.

A PDB's MAX_STRING_SIZE, time zone, and character set must be compatible with the CDB; the CDB's character set is inherited and cannot be changed per PDB.

Transportable tablespaces move datafiles between databases without exporting rows: the tablespace is made read-only, exported with TRANSPORT_TABLESPACE=Y, and the datafiles and dump are copied to the target.

The source and target must have the same database character set and national character set, and compatible endianness or the tablespace must be converted with RMAN CONVERT TABLESPACE.

RMAN CONVERT TABLESPACE ... TO PLATFORM ... produces converted datafiles for a different-endianness platform; CONVERT DATAFILE works on individual files.

Data Pump export/import (expdp/impdp) moves schema objects; TRANSPORT_TABLESPACES=Y in the export and import performs a transportable tablespace move.

The target database must not already contain a tablespace with the same name; plugging the tablespace into a different database requires the datafiles to be copied and the tablespace imported.

Cross-platform transport requires the source and target platforms to be listed in V$TRANSPORTABLE_PLATFORM; RMAN CONVERT DATABASE can convert a whole database for transport.

Full transportable export (FULL=Y TRANSPORTABLE=Y) moves an entire database, including the SYSTEM and SYSAUX tablespaces, using Data Pump and RMAN conversion when needed.

After transport, the tablespace in the source can be made read-only or dropped; the target must run the import with the datafiles in place, then the tablespace is read-write.

AWR snapshots are stored in the SYSAUX tablespace and are controlled by STATISTICS_LEVEL (TYPICAL or ALL); reports are generated with awrrpt.sql or DBMS_WORKLOAD_REPOSITORY.

ASH samples active sessions every second and is the basis for top-event and blocking analysis; V$ACTIVE_SESSION_HISTORY holds in-memory samples and DBA_HIST_ACTIVE_SESS_HISTORY holds persisted ones.

ADDM runs automatically after each AWR snapshot and reports the top findings with estimated impact; DBMS_ADVISOR can run it manually.

SQL Tuning Advisor (DBMS_SQLTUNE) analyzes a SQL statement and may recommend indexes, statistics, or SQL profiles; SQL Access Advisor recommends indexes and materialized views for a workload.

SQL plan baselines (DBMS_SPM) capture an accepted plan and prevent plan regressions; plans are added from the cursor cache or SQL tuning sets and are evolved with EVOLVE_SQL_PLAN_BASELINE.

Automatic SQL tuning runs during maintenance windows and can create SQL profiles for high-load statements; it is controlled by the automatic SQL tuning task and its result is visible in DBA_ADVISOR.

Real-time SQL monitoring (V$SQL_MONITOR) activates for statements that run long or consume significant resources; DBMS_SQLTUNE.REPORT_SQL_MONITOR produces the report.

The SQL Tuning Set (DBMS_SQLTUNE) captures statements for analysis; SQL Performance Analyzer (DBMS_SQLPA) compares performance before and after a change.

The Resource Manager plan is activated with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = plan_name; the plan is stored in the data dictionary and applies to sessions in the CDB or PDB.

Resource consumer groups assign sessions via DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP and mapping rules; the default group is OTHER_GROUPS, which must be included in every plan.

Plan directives set CPU allocation by shares or by utilization limit, and can limit parallel degree, active sessions, and undo space per consumer group.

The Scheduler runs jobs defined with DBMS_SCHEDULER; a job needs a program or a command, a schedule, and enabled state, and runs under a job class for resource mapping.

Scheduler windows (CREATE WINDOW) define time periods with a resource plan; window groups combine windows and are activated by the scheduler.

Automated maintenance tasks (Automatic Workload Repository, Automatic Database Diagnostic Monitor, Automatic SQL Tuning, Segment Advisor) run in maintenance windows defined by the MAINTENANCE_WINDOW_GROUP.

Maintenance tasks are enabled or disabled with DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE and their clients are listed in DBA_AUTOTASK_CLIENT; DBA_AUTOTASK_OPERATION shows operations.

A job's repeat interval uses calendaring syntax (FREQ=DAILY; BYHOUR=2), and DBMS_SCHEDULER.RUN_JOB can force a run outside the schedule.

RMAN persistence: CONFIGURE settings are stored in the target control file, or in the recovery catalog when one is used; SHOW ALL lists current values and CLEAR resets to default.

Retention policies: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS keeps everything needed to recover to any point in the last n days; TO REDUNDANCY n keeps n copies of each file.

Backup types: full (all blocks), incremental level 0 (all used blocks, base for later incrementals), level 1 differential (blocks changed since last level 0 or 1), level 1 cumulative (blocks changed since last level 0).

Block change tracking (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING) records changed blocks in a CTWR-maintained file and lets level 1 backups scan only changed blocks; it does not change what is backed up.

BACKUP ... PLUS ARCHIVELOG switches the current log, archives, backs up archived logs, then backs up datafiles; this yields a consistent recoverable set without a separate log backup step.

BACKUP INCREMENTAL LEVEL 1 ... FOR RECOVER OF COPY WITH TAG creates a fast incremental roll-forward strategy: a level 0 image copy plus daily incrementals that can be applied to the copy.

RESTORE copies files back from backups; RECOVER applies redo (archived and online) to make them consistent. A restore without recover usually leaves files at an older SCN.

Point-in-time recovery: SET UNTIL TIME/SCN/SEQUENCE or RESTORE ... UNTIL; recovery stops at the target and requires a subsequent OPEN RESETLOGS when the target precedes the current log sequence.

Incomplete recovery always requires OPEN RESETLOGS, which creates a new incarnation; full recovery of an open database does not.

Media recovery with a missing or corrupted current online redo log needs a backup control file and either a full restore or recovery to a point before the missing log.

Tablespace point-in-time recovery (TSPITR) uses an auxiliary instance, restores and recovers the tablespace to a target time, then exports/imports or uses transportable tablespaces to move the recovered objects into the production database.

Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) diagnoses failures from the control file and can generate and execute repair scripts; repair is only possible for failures it can fix automatically.

Restore optimization: RMAN restores only files that are missing or older than the recovery target; RESTORE ... PREVIEW lists the files and backups RMAN would use.

Crosscheck (CROSSCHECK) validates that backups recorded in the repository still exist and marks expired ones; DELETE EXPIRED removes those records, while DELETE OBSOLETE removes backups that violate retention.

Block media recovery (BLOCKRECOVER) restores and recovers individual corrupt blocks from a backup or flashback log while the database stays open, using the current datafile as the base.

Flashback Query (AS OF TIMESTAMP/SCN) reads past versions of rows from undo; it needs undo retention long enough to cover the target time and does not change data.

Flashback Version Query (VERSIONS BETWEEN) and Flashback Transaction Query show row history and the transactions that changed rows, using undo and, for transaction query, the redo/undo metadata.

Flashback Table restores a table to a past point using undo and requires the table's row movement to be enabled; it does not recover dropped tables.

Flashback Drop restores a dropped table from the recycle bin (FLASHBACK TABLE t TO BEFORE DROP); the recycle bin can be purged with PURGE RECYCLEBIN or PURGE TABLE.

Flashback Database rewinds the whole database using flashback logs; it requires ARCHIVELOG mode, a flash recovery area, and DB_FLASHBACK_RETENTION_TARGET, and ends with OPEN RESETLOGS.

Guaranteed restore points (CREATE RESTORE POINT ... GUARANTEE FLASHBACK DATABASE) protect flashback logs from deletion so a flashback to that point remains possible.

Flashback Data Archive (FDA) tracks row history for long periods independent of undo retention; it records changes in a tablespace and is configured with CREATE FLASHBACK ARCHIVE.

Data Recovery Advisor works only when the database is in a mounted or open state; failures are detected automatically or with LIST FAILURE, and ADVISE FAILURE produces a repair script that REPAIR FAILURE can execute.

Flashback Database cannot be used after a datafile is lost or a control file restore; those cases require RMAN restore and recovery instead.

A CDB contains one root container (CDB$ROOT), one seed PDB (PDB$SEED), and zero or more user PDBs; the root and seed are read-only and cannot be dropped.

The root stores common users and common objects; a PDB has its own local users, schemas, and data dictionary views, and its datafiles live under its own directory.

Connecting as a common user requires the common user name prefixed with C##; local users are created inside a PDB and are visible only there.

A PDB is opened with ALTER PLUGGABLE DATABASE pdb OPEN; OPEN READ ONLY or OPEN RESTRICTED limits access. MOUNTED state allows some maintenance but not user connections.

PDB$SEED is used as a template by CREATE PLUGGABLE DATABASE ... FROM PDB$SEED; it must remain read-only and unmodified.

The CDB root has one undo tablespace; each PDB can have its own local undo tablespace when LOCAL UNDO is enabled, which supports PDB cloning and point-in-time recovery.

A common user with SYSDBA or SYSOPER privilege can connect to any PDB; a local user with those privileges can connect only to its own PDB.

ALTER PLUGGABLE DATABASE ... SAVE STATE persists the open mode across CDB restarts when the PDB is in the root's saved state list.

CREATE PLUGGABLE DATABASE ... ADMIN USER ... IDENTIFIED BY ... creates a PDB with a local admin user; the ADMIN USER clause is required unless the PDB is created from a seed with an existing admin.

Cloning a PDB: CREATE PLUGGABLE DATABASE new FROM old; the source must be read-only and the clone uses the source's datafiles or a snapshot copy.

PDB snapshot clones (SNAPSHOT COPY) require the source PDB to be read-only and use storage-level snapshots; they are fast but depend on storage support.

Unplugging a PDB (ALTER PLUGGABLE DATABASE ... UNPLUG INTO file) writes an XML manifest; the PDB is then dropped from the CDB and can be plugged into another CDB with CREATE PLUGGABLE DATABASE ... USING manifest.

A PDB can be relocated with CREATE PLUGGABLE DATABASE ... FROM ...@dblink or via a manifest file; the source and destination must have compatible character sets and endianness or use CONVERT.

Resource management for PDBs uses CDB resource plans (CREATE PLAN in the root) with shares, utilization limits, and parallel server limits per PDB, applied with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN.

PDB lockdown profiles restrict operations (such as ALTER SYSTEM or certain packages) for a PDB and are assigned with ALTER PLUGGABLE DATABASE ... SET LOCKDOWN PROFILE.

A PDB's MAX_STRING_SIZE, time zone, and character set must be compatible with the CDB; the CDB's character set is inherited and cannot be changed per PDB.

Transportable tablespaces move datafiles between databases without exporting rows: the tablespace is made read-only, exported with TRANSPORT_TABLESPACE=Y, and the datafiles and dump are copied to the target.

The source and target must have the same database character set and national character set, and compatible endianness or the tablespace must be converted with RMAN CONVERT TABLESPACE.

RMAN CONVERT TABLESPACE ... TO PLATFORM ... produces converted datafiles for a different-endianness platform; CONVERT DATAFILE works on individual files.

Data Pump export/import (expdp/impdp) moves schema objects; TRANSPORT_TABLESPACES=Y in the export and import performs a transportable tablespace move.

The target database must not already contain a tablespace with the same name; plugging the tablespace into a different database requires the datafiles to be copied and the tablespace imported.

Cross-platform transport requires the source and target platforms to be listed in V$TRANSPORTABLE_PLATFORM; RMAN CONVERT DATABASE can convert a whole database for transport.

Full transportable export (FULL=Y TRANSPORTABLE=Y) moves an entire database, including the SYSTEM and SYSAUX tablespaces, using Data Pump and RMAN conversion when needed.

After transport, the tablespace in the source can be made read-only or dropped; the target must run the import with the datafiles in place, then the tablespace is read-write.

AWR snapshots are stored in the SYSAUX tablespace and are controlled by STATISTICS_LEVEL (TYPICAL or ALL); reports are generated with awrrpt.sql or DBMS_WORKLOAD_REPOSITORY.

ASH samples active sessions every second and is the basis for top-event and blocking analysis; V$ACTIVE_SESSION_HISTORY holds in-memory samples and DBA_HIST_ACTIVE_SESS_HISTORY holds persisted ones.

ADDM runs automatically after each AWR snapshot and reports the top findings with estimated impact; DBMS_ADVISOR can run it manually.

SQL Tuning Advisor (DBMS_SQLTUNE) analyzes a SQL statement and may recommend indexes, statistics, or SQL profiles; SQL Access Advisor recommends indexes and materialized views for a workload.

SQL plan baselines (DBMS_SPM) capture an accepted plan and prevent plan regressions; plans are added from the cursor cache or SQL tuning sets and are evolved with EVOLVE_SQL_PLAN_BASELINE.

Automatic SQL tuning runs during maintenance windows and can create SQL profiles for high-load statements; it is controlled by the automatic SQL tuning task and its result is visible in DBA_ADVISOR.

Real-time SQL monitoring (V$SQL_MONITOR) activates for statements that run long or consume significant resources; DBMS_SQLTUNE.REPORT_SQL_MONITOR produces the report.

The SQL Tuning Set (DBMS_SQLTUNE) captures statements for analysis; SQL Performance Analyzer (DBMS_SQLPA) compares performance before and after a change.

The Resource Manager plan is activated with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = plan_name; the plan is stored in the data dictionary and applies to sessions in the CDB or PDB.

Resource consumer groups assign sessions via DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP and mapping rules; the default group is OTHER_GROUPS, which must be included in every plan.

Plan directives set CPU allocation by shares or by utilization limit, and can limit parallel degree, active sessions, and undo space per consumer group.

The Scheduler runs jobs defined with DBMS_SCHEDULER; a job needs a program or a command, a schedule, and enabled state, and runs under a job class for resource mapping.

Scheduler windows (CREATE WINDOW) define time periods with a resource plan; window groups combine windows and are activated by the scheduler.

Automated maintenance tasks (Automatic Workload Repository, Automatic Database Diagnostic Monitor, Automatic SQL Tuning, Segment Advisor) run in maintenance windows defined by the MAINTENANCE_WINDOW_GROUP.

Maintenance tasks are enabled or disabled with DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE and their clients are listed in DBA_AUTOTASK_CLIENT; DBA_AUTOTASK_OPERATION shows operations.

A job's repeat interval uses calendaring syntax (FREQ=DAILY; BYHOUR=2), and DBMS_SCHEDULER.RUN_JOB can force a run outside the schedule.

RMAN persistence: CONFIGURE settings are stored in the target control file, or in the recovery catalog when one is used; SHOW ALL lists current values and CLEAR resets to default.

Retention policies: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS keeps everything needed to recover to any point in the last n days; TO REDUNDANCY n keeps n copies of each file.

Backup types: full (all blocks), incremental level 0 (all used blocks, base for later incrementals), level 1 differential (blocks changed since last level 0 or 1), level 1 cumulative (blocks changed since last level 0).

Block change tracking (ALTER DATABASE ENABLE BLOCK CHANGE TRACKING) records changed blocks in a CTWR-maintained file and lets level 1 backups scan only changed blocks; it does not change what is backed up.

BACKUP ... PLUS ARCHIVELOG switches the current log, archives, backs up archived logs, then backs up datafiles; this yields a consistent recoverable set without a separate log backup step.

BACKUP INCREMENTAL LEVEL 1 ... FOR RECOVER OF COPY WITH TAG creates a fast incremental roll-forward strategy: a level 0 image copy plus daily incrementals that can be applied to the copy.

RESTORE copies files back from backups; RECOVER applies redo (archived and online) to make them consistent. A restore without recover usually leaves files at an older SCN.

Point-in-time recovery: SET UNTIL TIME/SCN/SEQUENCE or RESTORE ... UNTIL; recovery stops at the target and requires a subsequent OPEN RESETLOGS when the target precedes the current log sequence.

Incomplete recovery always requires OPEN RESETLOGS, which creates a new incarnation; full recovery of an open database does not.

Media recovery with a missing or corrupted current online redo log needs a backup control file and either a full restore or recovery to a point before the missing log.

Tablespace point-in-time recovery (TSPITR) uses an auxiliary instance, restores and recovers the tablespace to a target time, then exports/imports or uses transportable tablespaces to move the recovered objects into the production database.

Data Recovery Advisor (LIST FAILURE, ADVISE FAILURE, REPAIR FAILURE) diagnoses failures from the control file and can generate and execute repair scripts; repair is only possible for failures it can fix automatically.

Restore optimization: RMAN restores only files that are missing or older than the recovery target; RESTORE ... PREVIEW lists the files and backups RMAN would use.

Crosscheck (CROSSCHECK) validates that backups recorded in the repository still exist and marks expired ones; DELETE EXPIRED removes those records, while DELETE OBSOLETE removes backups that violate retention.

Block media recovery (BLOCKRECOVER) restores and recovers individual corrupt blocks from a backup or flashback log while the database stays open, using the current datafile as the base.

Flashback Query (AS OF TIMESTAMP/SCN) reads past versions of rows from undo; it needs undo retention long enough to cover the target time and does not change data.

Flashback Version Query (VERSIONS BETWEEN) and Flashback Transaction Query show row history and the transactions that changed rows, using undo and, for transaction query, the redo/undo metadata.

Flashback Table restores a table to a past point using undo and requires the table's row movement to be enabled; it does not recover dropped tables.

Flashback Drop restores a dropped table from the recycle bin (FLASHBACK TABLE t TO BEFORE DROP); the recycle bin can be purged with PURGE RECYCLEBIN or PURGE TABLE.

Flashback Database rewinds the whole database using flashback logs; it requires ARCHIVELOG mode, a flash recovery area, and DB_FLASHBACK_RETENTION_TARGET, and ends with OPEN RESETLOGS.

Guaranteed restore points (CREATE RESTORE POINT ... GUARANTEE FLASHBACK DATABASE) protect flashback logs from deletion so a flashback to that point remains possible.

Flashback Data Archive (FDA) tracks row history for long periods independent of undo retention; it records changes in a tablespace and is configured with CREATE FLASHBACK ARCHIVE.

Data Recovery Advisor works only when the database is in a mounted or open state; failures are detected automatically or with LIST FAILURE, and ADVISE FAILURE produces a repair script that REPAIR FAILURE can execute.

Flashback Database cannot be used after a datafile is lost or a control file restore; those cases require RMAN restore and recovery instead.

A CDB contains one root container (CDB$ROOT), one seed PDB (PDB$SEED), and zero or more user PDBs; the root and seed are read-only and cannot be dropped.

The root stores common users and common objects; a PDB has its own local users, schemas, and data dictionary views, and its datafiles live under its own directory.

Connecting as a common user requires the common user name prefixed with C##; local users are created inside a PDB and are visible only there.

A PDB is opened with ALTER PLUGGABLE DATABASE pdb OPEN; OPEN READ ONLY or OPEN RESTRICTED limits access. MOUNTED state allows some maintenance but not user connections.

PDB$SEED is used as a template by CREATE PLUGGABLE DATABASE ... FROM PDB$SEED; it must remain read-only and unmodified.

The CDB root has one undo tablespace; each PDB can have its own local undo tablespace when LOCAL UNDO is enabled, which supports PDB cloning and point-in-time recovery.

A common user with SYSDBA or SYSOPER privilege can connect to any PDB; a local user with those privileges can connect only to its own PDB.

ALTER PLUGGABLE DATABASE ... SAVE STATE persists the open mode across CDB restarts when the PDB is in the root's saved state list.

CREATE PLUGGABLE DATABASE ... ADMIN USER ... IDENTIFIED BY ... creates a PDB with a local admin user; the ADMIN USER clause is required unless the PDB is created from a seed with an existing admin.

Cloning a PDB: CREATE PLUGGABLE DATABASE new FROM old; the source must be read-only and the clone uses the source's datafiles or a snapshot copy.

PDB snapshot clones (SNAPSHOT COPY) require the source PDB to be read-only and use storage-level snapshots; they are fast but depend on storage support.

Unplugging a PDB (ALTER PLUGGABLE DATABASE ... UNPLUG INTO file) writes an XML manifest; the PDB is then dropped from the CDB and can be plugged into another CDB with CREATE PLUGGABLE DATABASE ... USING manifest.

A PDB can be relocated with CREATE PLUGGABLE DATABASE ... FROM ...@dblink or via a manifest file; the source and destination must have compatible character sets and endianness or use CONVERT.

Resource management for PDBs uses CDB resource plans (CREATE PLAN in the root) with shares, utilization limits, and parallel server limits per PDB, applied with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN.

PDB lockdown profiles restrict operations (such as ALTER SYSTEM or certain packages) for a PDB and are assigned with ALTER PLUGGABLE DATABASE ... SET LOCKDOWN PROFILE.

A PDB's MAX_STRING_SIZE, time zone, and character set must be compatible with the CDB; the CDB's character set is inherited and cannot be changed per PDB.

Transportable tablespaces move datafiles between databases without exporting rows: the tablespace is made read-only, exported with TRANSPORT_TABLESPACE=Y, and the datafiles and dump are copied to the target.

The source and target must have the same database character set and national character set, and compatible endianness or the tablespace must be converted with RMAN CONVERT TABLESPACE.

RMAN CONVERT TABLESPACE ... TO PLATFORM ... produces converted datafiles for a different-endianness platform; CONVERT DATAFILE works on individual files.

Data Pump export/import (expdp/impdp) moves schema objects; TRANSPORT_TABLESPACES=Y in the export and import performs a transportable tablespace move.

The target database must not already contain a tablespace with the same name; plugging the tablespace into a different database requires the datafiles to be copied and the tablespace imported.

Cross-platform transport requires the source and target platforms to be listed in V$TRANSPORTABLE_PLATFORM; RMAN CONVERT DATABASE can convert a whole database for transport.

Full transportable export (FULL=Y TRANSPORTABLE=Y) moves an entire database, including the SYSTEM and SYSAUX tablespaces, using Data Pump and RMAN conversion when needed.

After transport, the tablespace in the source can be made read-only or dropped; the target must run the import with the datafiles in place, then the tablespace is read-write.

AWR snapshots are stored in the SYSAUX tablespace and are controlled by STATISTICS_LEVEL (TYPICAL or ALL); reports are generated with awrrpt.sql or DBMS_WORKLOAD_REPOSITORY.

ASH samples active sessions every second and is the basis for top-event and blocking analysis; V$ACTIVE_SESSION_HISTORY holds in-memory samples and DBA_HIST_ACTIVE_SESS_HISTORY holds persisted ones.

ADDM runs automatically after each AWR snapshot and reports the top findings with estimated impact; DBMS_ADVISOR can run it manually.

SQL Tuning Advisor (DBMS_SQLTUNE) analyzes a SQL statement and may recommend indexes, statistics, or SQL profiles; SQL Access Advisor recommends indexes and materialized views for a workload.

SQL plan baselines (DBMS_SPM) capture an accepted plan and prevent plan regressions; plans are added from the cursor cache or SQL tuning sets and are evolved with EVOLVE_SQL_PLAN_BASELINE.

Automatic SQL tuning runs during maintenance windows and can create SQL profiles for high-load statements; it is controlled by the automatic SQL tuning task and its result is visible in DBA_ADVISOR.

Real-time SQL monitoring (V$SQL_MONITOR) activates for statements that run long or consume significant resources; DBMS_SQLTUNE.REPORT_SQL_MONITOR produces the report.

The SQL Tuning Set (DBMS_SQLTUNE) captures statements for analysis; SQL Performance Analyzer (DBMS_SQLPA) compares performance before and after a change.

The Resource Manager plan is activated with ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = plan_name; the plan is stored in the data dictionary and applies to sessions in the CDB or PDB.

Resource consumer groups assign sessions via DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP and mapping rules; the default group is OTHER_GROUPS, which must be included in every plan.

Plan directives set CPU allocation by shares or by utilization limit, and can limit parallel degree, active sessions, and undo space per consumer group.

The Scheduler runs jobs defined with DBMS_SCHEDULER; a job needs a program or a command, a schedule, and enabled state, and runs under a job class for resource mapping.

Scheduler windows (CREATE WINDOW) define time periods with a resource plan; window groups combine windows and are activated by the scheduler.

Automated maintenance tasks (Automatic Workload Repository, Automatic Database Diagnostic Monitor, Automatic SQL Tuning, Segment Advisor) run in maintenance windows defined by the MAINTENANCE_WINDOW_GROUP.

Maintenance tasks are enabled or disabled with DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE and their clients are listed in DBA_AUTOTASK_CLIENT; DBA_AUTOTASK_OPERATION shows operations.

A job's repeat interval uses calendaring syntax (FREQ=DAILY; BYHOUR=2), and DBMS_SCHEDULER.RUN_JOB can force a run outside the schedule.