20 July, 2010

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."
.
.
.

5 comments:

Nitin said...

Nice little demo of Myth Buster.

Nicolas Gasparotto said...

Maybe the date of the SYSTEM datafile in the header give the db creation date (creation_date from v$datafile_header where file# = 1).

Nicolas.

Hemant K Chitale said...

CREATION_TIME from v$DATAFILE_HEADER where DATAFILE=1 ?


Consider what it would be if you used DBCA to "create database" from one of the pre-seeded templates.


Hemant K Chitale

Mathew said...

How can i get the database upgrade date?

Hemant K Chitale said...

Mathew,
Two ways :
One : from the log files :
You would look at the alert.log file for the first time the database had been started from the current (upgrade) ORACLE_HOME.

(When running an upgrade, you have to STARTUP UPGRADE from the new ORACLE_HOME and then execute catupgrd.sql)

Two : From dba_registry
query DBA_REGISTRY where you'd find MODIFIED as a VARCHAR2 field storing the modified date for each component.