[Fusion] How to Retrieve the Clone Date of an Oracle Fusion Instance Using SQL?

 

In the world of Oracle Fusion Cloud, managing environments like production, test, or development instances is crucial for IT administrators and developers. One common task is determining the "clone date" or the last refresh date of an instance—essentially, when the environment was last cloned or refreshed from another source (e.g., Production-to-Test). This information helps track data freshness, audit changes, and plan maintenance.

If you're familiar with on-premises Oracle systems like E-Business Suite (EBS), you might be used to querying views like v$database for reset logs. However, Oracle Fusion Cloud operates as a SaaS model, limiting direct database access. Fear not! There's still a way to fetch this via SQL, typically through tools like BI Publisher (BIP) or other reporting interfaces with backend access.

In this blog post, we'll walk through the SQL query to get the clone date, explain how it works, and compare it to traditional methods. Whether you're a Fusion admin troubleshooting an issue or just curious about environment metadata, this guide has you covered.

The key table here is ask_deployed_provision_configs, which holds provisioned environment details. The last_update_date column typically captures the completion time of the most recent refresh.

Here's the straightforward SQL to retrieve the last update date:

SELECT last_update_date FROM ask_deployed_provision_configs;

How It Works

• Table Explanation: ask_deployed_provision_configs stores configuration data for deployed environments. It's part of the Oracle Fusion middleware schema.
• Key Column: last_update_date is a timestamp indicating when the configuration was last modified, which aligns with refresh/clone operations.
• Potential Output: This might return multiple rows if there are various configs. To get the most recent one, modify it like this:

            
SELECT MAX(last_update_date) AS latest_clone_date FROM ask_deployed_provision_configs;

Comparison to On-Premises Oracle Systems

If you're coming from an on-premises Oracle Database or EBS, the equivalent query is simpler and targets system views:

SELECT resetlogs_time FROM v$database;

This gives the time when the database was last reset (often during cloning). However, in Fusion Cloud, you can't directly query v$database due to the managed SaaS architecture—hence the reliance on application-level tables like ask_deployed_provision_configs.