Results for “ad_snapshots”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AD_SNAPSHOTS is a table owned by the APPLSYS schema within the Oracle E-Business Suite Applications DBA (AD) product family. It stores metadata describing file-system snapshots captured by the AutoPatch and AD Administration utilities. In Oracle EBS 12.1.1 and 12.2.2, a snapshot represents a recorded baseline of the APPL_TOP file system — specifically the set of files and bug fixes (patches) present at a point in time. This baseline is later used to detect and revert changes introduced by patch application, enabling rollback and diagnostic comparison of the application tier.

Each snapshot record ties a named capture to a specific APPL_TOP and a specific AD release, which allows a single EBS instance with multiple application tier nodes or shared file systems to maintain independent snapshot histories. Downstream detail tables record the individual files and associated bug fixes that make up each snapshot.

From a Data Vault modeling perspective, the ETRM heuristic classification for AD_SNAPSHOTS is satellite-leaning. This is a modeling suggestion rather than a physical constraint: the table holds descriptive attributes about a snapshot event and depends on the surrounding APPL_TOP and release context, so it behaves more like a satellite than a true hub. Its primary key, AD_SNAPSHOTS_PK on SNAPSHOT_ID, anchors the identity of each snapshot.

Key Information Stored

The table contains 14 documented columns. The most significant are:

• SNAPSHOT_ID — surrogate primary key (AD_SNAPSHOTS_PK and unique index AD_SNAPSHOTS_U1). It uniquely identifies each snapshot and is the join key used by all dependent detail tables.
• SNAPSHOT_NAME — user-supplied name for the snapshot; combined with APPL_TOP_ID and SNAPSHOT_TYPE it forms the business-key candidate AD_SNAPSHOTS_U2.
• APPL_TOP_ID — foreign key to AD_APPL_TOPS, identifying which application tier file system the snapshot covers.
• RELEASE_ID — foreign key to AD_RELEASES, recording the AD release under which the snapshot was taken.
• SNAPSHOT_TYPE — classifies the snapshot (for example, a standard or patch-related baseline); part of the U2 business key.
• SNAPSHOT_CREATION_DATE and SNAPSHOT_UPDATE_DATE — capture when the snapshot was first created and last modified.
• RAN_SNAPSHOT_FLAG — indicates whether the snapshot process completed successfully.
• IMPORTED_FLAG — indicates whether the snapshot was imported from another source rather than generated locally.
• COMMENTS — free-text annotation supplied by the DBA.
• CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY — standard EBS audit columns.

The distinction between the surrogate key (SNAPSHOT_ID) and the three-column business key (SNAPSHOT_NAME, APPL_TOP_ID, SNAPSHOT_TYPE) is important when querying or reconciling snapshots across APPL_TOPs.

Common Use Cases and Queries

Typical scenarios include identifying the most recent snapshot for a given APPL_TOP, auditing failed or imported snapshots, and locating the snapshot associated with a patch rollback.

• List recent snapshots for an application tier:

  SELECT s.snapshot_id, s.snapshot_name, s.snapshot_type,
         s.snapshot_creation_date, s.ran_snapshot_flag
  FROM   applsys.ad_snapshots s
  WHERE  s.appl_top_id = :appl_top_id
  ORDER  BY s.snapshot_creation_date DESC;

• Find snapshots taken under a specific AD release by joining AD_RELEASES on RELEASE_ID.
• Retrieve the file inventory of a snapshot by joining AD_SNAPSHOT_FILES on SNAPSHOT_ID.
• Detect imported or incomplete snapshots using the IMPORTED_FLAG and RAN_SNAPSHOT_FLAG columns.
• Reporting on snapshot frequency and coverage per APPL_TOP for change-management audits.

Related Objects

AD_SNAPSHOTS sits at the center of a small cluster of AutoPatch objects. The documented foreign-key relationships are:

• AD_APPL_TOPS — parent of AD_SNAPSHOTS via APPL_TOP_ID; defines the application tier file system.
• AD_RELEASES — parent of AD_SNAPSHOTS via RELEASE_ID; defines the AD release context.
• AD_SNAPSHOT_FILES — child table, joined on SNAPSHOT_ID; stores the individual files captured.
• AD_SNAPSHOT_BUGFIXES — child table, joined on SNAPSHOT_ID; records the bug fixes (patches) associated with the snapshot.

Together these tables form the snapshot subsystem used by AD utilities for file-system comparison and rollback within the APPLSYS schema.