V$DATABASE.CREATED -- is this the Database Creation timestamp ?

Some of us who have done more than one CREATE CONTROLFILE and observed what happens know that V$DATABASE.CREATED is not "Creation date of the database".
It is actually "Creation date of the *controlfile*". I am surprised that even the 11.2 documentation hasn't been updated yet.

Here's a simple demonstration :

SQL> select created, sysdate from v$database;

CREATED   SYSDATE
--------- ---------
02-MAY-10 20-JUL-10

SQL> alter database backup controlfile to trace;

Database altered.

SQL> --- edited the trace file to create /var/tmp/create_controlfile.sql
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> @/var/tmp/create_controlfile
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORT24FS" NORESETLOGS  ARCHIVELOG
2      MAXLOGFILES 16
3      MAXLOGMEMBERS 3
4      MAXDATAFILES 100
5      MAXINSTANCES 8
6      MAXLOGHISTORY 292
7  LOGFILE
8    GROUP 1 '/oracle_fs/Databases/ORT24FS/redo01.dbf'  SIZE 50M,
9    GROUP 2 '/oracle_fs/Databases/ORT24FS/redo02.dbf'  SIZE 50M,
10    GROUP 3 '/oracle_fs/Databases/ORT24FS/redo03.dbf'  SIZE 50M
11  -- STANDBY LOGFILE
12  DATAFILE
13    '/oracle_fs/Databases/ORT24FS/system01.dbf',
14    '/oracle_fs/Databases/ORT24FS/sysaux01.dbf',
15    '/oracle_fs/Databases/ORT24FS/users01.dbf',
16    '/oracle_fs/Databases/ORT24FS/example01.dbf',
17    '/oracle_fs/Databases/ORT24FS/undotbs.dbf'
18  CHARACTER SET WE8ISO8859P1
19  ;

Control file created.

SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf'
2       SIZE 3025M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

SQL>
SQL>
SQL> select created, sysdate from v$database;

CREATED   SYSDATE
--------- ---------
20-JUL-10 20-JUL-10

SQL>
SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
         1           3632263 04-JUN-10                 3631911 04-JUN-10
CURRENT    720831744                  0 NO


SQL>  

I didn't even do a RESETLOGS. I have retained the same Database Incarnation. Yet my "CREATED" date for the database seems to have got reset -- as if I recreated it today.

So, the next time you see V$DATABASE.CREATED ask yourself or ask the DBA who managed the database before you "When was the last time the controlfile was created --- no, wait, I know the answer. What I do not know is when the database was created (I think)".


UPDATE : 25-Jul-10 :

To show that V$DATAFILE / V$DATAFILE_HEADER may also not be usable, I created a database "TESTDB" using the "General Purpose Database" templcate in dbca. Once the database was created, I found that V$DATAFILE and V$DATAFILE_HEADER show the timestamps in the SYSTEM datafile in the pre-seeded database that is used as the template :

SQL> select sysdate, created, dbid, name from v$database;

SYSDATE            CREATED                  DBID NAME
------------------ ------------------ ---------- ---------
25-JUL-10 15:35:06 25-JUL-10 15:32:14 2508625662 TESTDB

SQL> select file#, creation_time from v$datafile where  file#=1;

   FILE# CREATION_TIME
---------- ------------------
       1 12-MAR-08 00:39:08

SQL> select file#, creation_time, creation_change# from v$datafile_header where file#=1;

   FILE# CREATION_TIME      CREATION_CHANGE#
---------- ------------------ ----------------
       1 12-MAR-08 00:39:08                5

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   611179

SQL>  

.
.
.
UPDATE 25-Aug-10 :

On 25-Jul, I had submitted a comment on the 11.2 Reference page on the V$DATABASE view to the effect : "The CREATED column in V$DATABASE does not show the "Creation date of the database". It really shows the "Creation date of the controlfile".

DBAs have been misled into using V$DATABASE.CREATED as the database creation timestamp. This would be wrong if, at any time in the past, the controlfile for the database has been recreated -- as such step would reset the V$DATABASE.CREATED time."

I have now received a reply :

"The description for V$DATABASE.CREATED will be updated as follows in the next version of the Database Reference:

Creation date of the database. If the control file was re-created using the CREATE CONTROLFILE statement, then this column displays the date that the control file was re-created."

.
.
.