Only Four Left…

Small disclaimer: Query output in this post has been altered slightly. The purpose is to obfuscate dates and instance names, not to make the output more interesting.

The boring, easy problem

There are many ways to determine when an E-Business Suite clone was created. To name a few:

1. Query FND_NODES to find the creation date of nodes in the system:

   SQL> select node_name, creation_date
   2  from fnd_nodes;
   
   NODE_NAME              CREATION_DATE
   ---------------------  ----------------
   JPR12                  29-FEB-10
   

2. Query FND_APPS_SYSTEM:

   SQL> select name, creation_date
     2  from FND_APPS_SYSTEM;
   
   NAME                   CREATION_DATE
   ---------------------- ---------------
   JPR12                  29-FEB-10
   

3. Use OAM to find when Site-level profile options such as Two Task, Applications Database ID, or Apps Servlet Agent were set, since these values rarely change over the life of an instance. Querying FND_PROFILE_OPTIONS_VALUES and FND_PROFILE_OPTIONS_TL also reveals this sort of information.
4. Look at the RESETLOGS_TIME field in V$DATABASE_INCARNATION
5. Look on the filesystem for log files from adcfgclone.pl ($INST_TOP/admin/log on an R12 apps tier, or $ORACLE_HOME/appsutil/log/$CONTEXT_NAME on the database tier)

None of these options are foolproof, of course. #1 and #2 can be invalidated by running FND_CONC_CLONE.SETUP_CLEAN, profile options can change, and sometimes, people even clean up log files. It's likely that these methods will at least point in the direction of a useful answer, however, and there are no doubt other ways.

None of these methods will answer the really interesting question, though: when did the data in your cloned system diverge from its source?

A more interesting, if somewhat contrived, problem

Imagine you're looking at a cloned E-Business Suite system, and you want to know when its data was copied from the source instance. All you know is that the clone was created from a pile of DVDs labelled "2009 Year-end backups." Thanks to incomplete record-keeping, you don't know what "Year-end" means in this case. It could mean calendar year, fiscal year, or "whenever Accounting reconciled and closed the year." For the sake of argument, assume that the creation dates on the files on the DVDs are also of no help. Furthermore, either because of security policies or an attack of personal integrity, you don't want to abuse your access to the APPS schema to look at data in the Financials, Order Management, or other functional modules to check for record modification dates.

...Hey, I did call this a "somewhat contrived" problem .

If you're fortunate enough to have just created this clone, and you're running a 10g RDBMS, you can look at the SCN associated with the most recent resetlogs:

SQL> @checkscn
SQL> --Only valid for 10g and up...need to consult smon_scn_time in 9i, no
SQL> --scn_to_timestamp there
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:ss';

Session altered.

SQL> select resetlogs_time, scn_to_timestamp(resetlogs_change#) resetscntime
2 from v$database_incarnation
3 where incarnation#=(select max(incarnation#)
4 from v$database_incarnation);

RESETLOGS_TIME            RESETSCNTIME
-------------------------      ----------------------------------------
29-FEB-2010 02:02:38     15-JAN-10 03.08.22.000000000 PM

Of course, this won't be very helpful if the clone has been up long enough for a lots of SCNs to cycle through:

select resetlogs_time, scn_to_timestamp(resetlogs_change#) resetscntime
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

Another possibility: If you're lazy, and not purging sign-on audit data on a regular database, you could get a reasonable guess from FND_LOGINS. What's that? You're not lazy, the scheduled job just hasn't run yet? Oh good, then you can still benefit:

SQL> @checklogin
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:ss';

Session altered.

SQL> select max(start_time)
2 from fnd_logins
3 where start_time < (select resetlogs_time
4 from v$database_incarnation
5 where incarnation#=(select max(incarnation#)
6 from v$database_incarnation))
7 /

MAX(START_TIME)
--------------------------
15-JAN-2010 13:06:42

Or, maybe you have a concurrent program that runs frequently (or even infrequently) in production that hasn't run yet in the clone? That would be convenient!

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:ss';

Session altered.

SQL> set echo on
SQL> select i.concurrent_program_id prog_id,
2 p.user_concurrent_program_name prog_name,
3 i.last_run_date
4 from fnd_conc_prog_onsite_info i, fnd_concurrent_programs_tl p
5 where i.last_run_date is not null
6 and p.concurrent_program_id= i.concurrent_program_id
7 and i.last_run_date < (select resetlogs_time
8 from v$database_incarnation
9 where incarnation#=(select max(incarnation#)
10 from v$database_incarnation))
11 order by i.last_run_date asc
12 /

PROG_ID   PROG_NAME                                LAST_RUN_DATE
--------- ---------------------------------------- ---------------------
(many rows later)
   41566  Compile Security                         15-JAN-2010 11:14:43

Please note that each of the queries above assume that the last resetlogs in the database was performed by adcfgclone.pl dbTier. If you've done another resetlogs since then, adjustments to the queries will be necessary.

I don't claim to have exhaustively considered the options in this situation, and if you have other ideas about how to solve this problem, I'd love to hear them in the comments. Blindingly obvious answers that will make me feel stupid are equally welcome; I can take it.