Search My Oracle Blog

Custom Search

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.

Gasparotto Nicolas 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.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016