1Z0-083: Oracle Database Administration IIDemo

RMAN supports both pull-based active duplication using backup sets and duplication of only a subset of a source database. A DUPLICATE operation requires an auxiliary instance, but it does not universally require either a recovery catalog or pre-existing backups: active duplication can copy directly from the source database. A new DBID is assigned to an ordinary duplicate database, but not to a standby database created with FOR STANDBY, which retains the source DBID. Oracle documents the pull-based method, auxiliary-instance prerequisite, subset duplication, optional catalog modes, and the standby DBID exception in its RMAN DUPLICATE reference.

OPatchAuto applies patches and automates the necessary shutdown and restart of Oracle Grid Infrastructure and Oracle Database home processes during patching. It must be executed by an operating-system user with root privileges. Rolling is its default patching mode, so a patch plan is not always required and nonrolling is not the default mode.

An application seed is an optional template for application PDBs. A cloned application PDB inherits the application version present in that seed, so it can contain either an older version when the seed has not been synchronized or the current version after the seed is synchronized with the application root. Application-root changes do not automatically synchronize the seed, and an application seed may be created in an existing application container and later dropped.

For backup-based duplication without a TARGET connection, NOREDO is required when the backups were taken while the source was in NOARCHIVELOG mode, and it can also be used to prevent archived redo from being applied to a consistent ARCHIVELOG backup. SBT backups are usable when their media is accessible to the auxiliary/destination host. RMAN does not push pre-existing backups over the network in this mode; the backups must instead be made accessible to the auxiliary host. UNDO TABLESPACE is required only for subset duplication in the relevant no-metadata/source-not-open cases, so neither unconditional UNDO statement is true.

Automatic Shared Memory Management dynamically tunes individual SGA components, allowing one component such as the large pool to grow while another such as the shared pool shrinks. Automatic Memory Management dynamically redistributes the overall instance memory between the SGA and instance PGA. OLTP workloads typically allocate a smaller share of total instance memory to PGA than DSS workloads, whose large memory-intensive queries require more PGA memory.

Because APP1_USER1 is created inside the BEGIN INSTALL / END INSTALL block while connected to the application root APP1_ROOT, it becomes part of the application definition (an application common user). Its underlying account is propagated to APP1_PDB1 (and any other PDB subscribed to that application) when the application is synchronized, but privilege and role grants to that user are performed locally in each application PDB, so it can legitimately end up with different privileges in each Application PDB contained in APP1_ROOT.

Separately, since COMMON_USER_PREFIX is C##, any user whose name begins with C## - such as C##_APP_USER1 - satisfies the naming rule required for common users and can be created directly while connected to CDB1 (the root), where only common users are allowed to be created.

By contrast, a local (non-prefixed) user such as P1_USER1 cannot be created in the root container, and an application common user's name (APP1_USER1) is reserved as part of the CDB-wide common user namespace, so it cannot also be created as an unrelated local user in PDB1 or directly (without the prefix) in CDB1. Also, since c##user1 was only created (not granted any privileges) with CONTAINER=ALL, it does not automatically carry any common privileges/roles across the PDBs.

Loss of the CURRENT online redo log group removes redo needed for crash recovery, so the database must be restored and recovered only through the most recent available redo, followed by RESETLOGS. Loss of every current control file, without a trace-based control-file creation script, requires recovery using a backup or re-created control file and a RESETLOGS open; this scenario requires incomplete recovery. An INACTIVE redo group can be cleared, while lost SYSAUX or active UNDO data files can normally be restored and recovered completely when the needed redo is available.

CONTAINER=CURRENT specifies creation of a local role. A local role can be created only while connected to the target PDB; it cannot be created in CDB$ROOT. An empty COMMON_USER_PREFIX affects common-role naming restrictions but does not change this container requirement.

ALTER PLUGGABLE DATABASE pdb1 OPEN defaults to READ WRITE, and SAVE STATE preserves that open mode across CDB restarts. Therefore, PDB1 reopens READ WRITE even though it was later closed. PDB2 has no saved state, so it remains MOUNTED after the restart. The PDB seed is opened READ ONLY when the CDB opens.

AWR automatically captures a snapshot of database performance statistics every 60 minutes by default. The STATISTICS_LEVEL initialization parameter (BASIC, TYPICAL, or ALL) determines what level of statistics AWR collects; setting it to BASIC turns off automatic AWR snapshot collection entirely, meaning snapshot capture can be disabled. By contrast, AWR data is retained for 8 days by default (not 7), and AWR data resides in the SYSAUX tablespace rather than the SYSTEM tablespace.

Oracle Database supports creating an application seed from an application PDB and plugging an unplugged application seed in as an application PDB. It also supports converting a regular PDB to an application PDB by placing it in an application root and completing the application-PDB conversion process.

A SQL profile contains auxiliary information that improves the optimizer’s estimates for a SQL statement, helping to correct estimation errors and allowing the optimizer to choose a more appropriate execution plan.

V$SERVICE_EVENT aggregates wait counts and wait times for each wait event by service. V$SESSION_WAIT reports each session’s current or last wait, while V$SESSION contains the same wait statistics. Consequently, both views identify the event currently being waited on and, for a non-waiting session, the event it last waited on. V$SESSION_EVENT summarizes waits for sessions rather than retaining data for past sessions, and V$SESSION_WAIT_CLASS reports per-session wait-class totals rather than only currently waiting sessions.

Database point-in-time recovery requires the database to be in ARCHIVELOG mode and is performed with the database mounted. Because it is incomplete recovery, the database must subsequently be opened with the RESETLOGS option. Flashback Database need not be enabled, MRP is a Data Guard recovery process rather than the DBPITR mechanism, and recovery targets can also be specified by restore point or log sequence number.

RMAN can back up an existing backup set only from disk: BACKUP BACKUPSET supports disk-to-disk and disk-to-tape copies, enabling additional copies of a backup set on disk or tape. It does not support tape-to-tape or tape-to-disk backup-set copies. SBT channel requirements depend on the number of tape devices and configured copies; they are not fixed as either the same number or always twice the number used for a non-duplexed backup.

A whole database backup can use either backup sets or image copies. It can be consistent when all data files and control files are synchronized following a consistent shutdown, or inconsistent when backed up while the database is open; an inconsistent backup requires media recovery. A consistent whole-database backup is the only valid recovery option in NOARCHIVELOG mode, but that does not make a whole-database backup the only possible type of backup operation.

Complete recovery applies all redo generated after the restored backup, so an archived log lost from before the most recent backup is not needed. A lost SYSTEM tablespace can be restored from a full RMAN backup and recovered with the required redo. In addition, a full RMAN database backup that includes data file 1 includes the control file when control-file autobackup is off; when it is on, RMAN creates a separate control-file autobackup. Oracle also supports complete recovery after restoring a backup control file. Consequently, the stated facts make three choices technically possible rather than two.

A snapshot copy PDB relies on a storage-managed snapshot, and storage-managed snapshots are supported only on specific file systems. A database-managed PDB snapshot can be sparse when the underlying file system supports sparse copies or full otherwise, so it is not always either type and is distinct from a storage-managed snapshot.

ADRCI can invoke the Incident Packaging Service to bundle diagnostic data about one or more incidents into a package for Oracle Support. The ADR maintains a distinct ADR home directory for every instance of every Oracle product installed on a host that uses ADR (database instance, ASM, listener, etc.). The Data Recovery Advisor relies on failure information already recorded in the ADR to diagnose data failures and generate repair advice/scripts. In contrast, Flashback technology addresses logical, not physical, corruption; RMAN cannot guarantee repair of every corrupt block; and ADR is a file-based repository stored outside the database rather than metadata kept inside an Oracle Database repository.

SQL Tuning Advisor performs in-depth analysis of regressed SQL statements and recommends changes such as statistics collection, indexes, SQL profiles, SQL plan baselines, or SQL rewrites to improve performance. Oracle identifies it as a method for fixing SQL regressions found by SQL Performance Analyzer.

Transparent encryption is RMAN’s default encryption mode and uses the Oracle software keystore. RMAN also permits encryption behavior to be configured for individual tablespaces through CONFIGURE ENCRYPTION FOR TABLESPACE.

RMAN stores a target database’s persistent configuration in its control file. When a fast recovery area needs space, the database can automatically remove files that are obsolete under the configured retention policy, including a redundancy policy. V$RMAN_CONFIGURATION records persistent configuration values that have been explicitly changed from their defaults. SHOW ALL includes both default and changed settings, and recovery-catalog synchronization depends on the catalog and target being available rather than occurring unconditionally.

Flashback Data Archive retains historical table data across a supported TRUNCATE operation when the table was enabled for the archive before the truncation. The preserved historical rows can be queried at a pre-truncate time and used to repopulate the table. Oracle specifies that Flashback Table cannot retrieve the contents of a truncated table.

Oracle Flashback Transaction Query and Oracle Flashback Version Query rely on undo data for historical transaction and row-version information. Flashback Drop recovers tables from the recycle bin rather than undo data, and RECYCLEBIN = ON is required for dropped tables to be retained there. Flashback Data Archive retains historical row versions in archive storage, not undo.