Oracle switching to non-sequential logs

A forums posting titled "Redo log switch sequence" asks how Oracle could seemingly switch between log files (actually redo log groups) that are not sequential.

This would happen if Redo Log groups are added, what I call, "in-ordinately". If you add Group#n when the CURRENT group is not "n-1" (e.g. it is "n-2"), you will see Oracle switch "out of sequence".

Here's a simple demo :

These are my log files. Notice that GROUP#2 is CURRENT :

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       CURRENT     50.00
3          9 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00

SQL> 

I then add GROUP#4

SQL> alter database add logfile group 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf' size 50M;

Database altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       CURRENT     50.00
3          9 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00
4          0 /oracle_fs/Databases/ORT24FS/redo04.dbf       UNUSED      50.00

SQL> 

However, the next Log Switch (SEQUENCE#11 to #12) goes from GROUP#2 to GROUP#4 :

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3          9 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       CURRENT     50.00

SQL> 

Another switch sets GROUP#3 as SEQUENCE#13 now :

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       INACTIVE    50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       CURRENT     50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       ACTIVE      50.00

SQL> 

I now add two Log Groups: 5 and 6

SQL> alter database add logfile group 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf' size 50M;

Database altered.

SQL> alter database add logfile group 6 '/oracle_fs/Databases/ORT24FS/redo06.dbf' size 50M;

Database altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       INACTIVE    50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       CURRENT     50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       ACTIVE      50.00
5          0 /oracle_fs/Databases/ORT24FS/redo05.dbf       UNUSED      50.00
6          0 /oracle_fs/Databases/ORT24FS/redo06.dbf       UNUSED      50.00

6 rows selected.

SQL> 

Now, GROUP#5 becomes SEQUENCE#14 and GROUP#6 becomes SEQUENCE#15. Oracle has, this time, "jumped" from GROUP#3 to GROUP#5 !

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       INACTIVE    50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       ACTIVE      50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       CURRENT     50.00
6          0 /oracle_fs/Databases/ORT24FS/redo06.dbf       UNUSED      50.00

6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         11 /oracle_fs/Databases/ORT24FS/redo02.dbf       INACTIVE    50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       ACTIVE      50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       ACTIVE      50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       CURRENT     50.00

6 rows selected.

SQL> 

I now drop and recreate GROUP#2 :

SQL> alter database drop logfile group 2;

Database altered.

SQL> @log_files    

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       INACTIVE    50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       ACTIVE      50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       CURRENT     50.00

SQL> alter database add logfile group 2 '/oracle_fs/Databases/ORT24FS/redo02.dbf' size 50M;

Database altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2          0 /oracle_fs/Databases/ORT24FS/redo02.dbf       UNUSED      50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       INACTIVE    50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       ACTIVE      50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       CURRENT     50.00

6 rows selected.

SQL> 

What is the next switch ? It goes from GROUP#6 to GROUP#2 for SEQUENCE#16 ! :

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         16 /oracle_fs/Databases/ORT24FS/redo02.dbf       CURRENT     50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       INACTIVE    50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       INACTIVE    50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       ACTIVE      50.00

6 rows selected.

SQL> 

Is it not very confusing now ?
SEQUENCE#10 is "redo01.dbf"
SEQUENCE#11 is no longer an online redo log !
SEQUENCE#12 is "redo04.dbf"
SEQUENCE#13 is "redo03.dbf"
SEQUENCE#14 is "redo05.dbf"
SEQUENCE#15 is "redo06.dbf"
SEQUENCE#16 is "redo02.dbf"

Anyone looking at the alert.log (but not noticing the DROP GROUP / ADD GROUP commands) and/or V$LOG would think that Oracle has "randomly" moved from red01.dbf to redo04.dbf than backwards to redo03.dbf and so on .... totally "out of sequence !"

(it becomes more confusing if the file names are not numbered -- eg if GROUP#1's, file is "/uA/oracle/redo/redo.dbf", GROUP#2's file is "/uX/oracle/redo/redo.dbf", GROUP#3's file is "/mnt/redo.dbf" and so on !! -- which is possible but very untidy !)

Technically, this mismatch doesn't matter. Oracle doesn't hardcode GROUP# and MEMBER names, when it looks at a Redo Log file, it checks the SEQUENCE# (and SCNs and Timestamps).
Things get more confusing with RAC when GROUPs span Threads. GROUP#1, GROUP#2, GROUP#3, GROUP#7,GROUP#9 could be in THREAD#1, while GROUP#4, GROUP#5, GROUP#6, GROUP#8 and GROUP#10 are in THREAD#2 ! (But you can't have the same GROUP# in two THREAD#s -- GROUP#s are Unique across the database).

Thus, in my non-RAC environment I can "skip" Groups 7 and 8 and move from GROUP#2 to GROUP#9 for going from SEQUENCE#16 to SEQUENCE#17, with the GROUP#9 file name not representing it's group at all :

SQL> alter database add logfile group 9 '/tmp/redoX.dbf' size 50M;

Database altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         16 /oracle_fs/Databases/ORT24FS/redo02.dbf       CURRENT     50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       INACTIVE    50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       INACTIVE    50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       INACTIVE    50.00
9          0 /tmp/redoX.dbf                                UNUSED      50.00

7 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         10 /oracle_fs/Databases/ORT24FS/redo01.dbf       INACTIVE    50.00
2         16 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       INACTIVE    50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       INACTIVE    50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       INACTIVE    50.00
9         17 /tmp/redoX.dbf                                CURRENT     50.00

7 rows selected.

SQL>  

Therefore, it (i.e. the GROUP#s you create and MEMBER names you assign) doesn't really matter. However, if you want to "prettify" things, ensure that you ADD GROUP#n when the CURRENT file is GROUP#n-1.

This is how I "cleanup" and "prettify" things :

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       CURRENT     50.00
2         16 /oracle_fs/Databases/ORT24FS/redo02.dbf       INACTIVE    50.00
3         13 /oracle_fs/Databases/ORT24FS/redo03.dbf       INACTIVE    50.00
4         12 /oracle_fs/Databases/ORT24FS/redo04.dbf       INACTIVE    50.00
5         14 /oracle_fs/Databases/ORT24FS/redo05.dbf       INACTIVE    50.00
6         15 /oracle_fs/Databases/ORT24FS/redo06.dbf       INACTIVE    50.00
9         17 /tmp/redoX.dbf                                INACTIVE    50.00

7 rows selected.

SQL>
SQL> alter database drop logfile group 9;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL>
SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       CURRENT     50.00
2         16 /oracle_fs/Databases/ORT24FS/redo02.dbf       INACTIVE    50.00

SQL>
SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       CURRENT     50.00

SQL> alter database add logfile group 3 '/oracle_fs/Databases/ORT24FS/redo03.dbf' size 50M;

Database altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       CURRENT     50.00
3          0 /oracle_fs/Databases/ORT24FS/redo03.dbf       UNUSED      50.00

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         20 /oracle_fs/Databases/ORT24FS/redo03.dbf       CURRENT     50.00

SQL> alter database add logfile group 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf' size 50M;

Database altered.

SQL> @log_files;

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         20 /oracle_fs/Databases/ORT24FS/redo03.dbf       CURRENT     50.00
4          0 /oracle_fs/Databases/ORT24FS/redo04.dbf       UNUSED      50.00

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         20 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         21 /oracle_fs/Databases/ORT24FS/redo04.dbf       CURRENT     50.00

SQL>
SQL> alter database add logfile group 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf' size 50M;

Database altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         20 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         21 /oracle_fs/Databases/ORT24FS/redo04.dbf       CURRENT     50.00
5          0 /oracle_fs/Databases/ORT24FS/redo05.dbf       UNUSED      50.00

SQL> alter system switch logfile;

System altered.

SQL>
SQL> @log_files;

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         18 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         19 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         20 /oracle_fs/Databases/ORT24FS/redo03.dbf       ACTIVE      50.00
4         21 /oracle_fs/Databases/ORT24FS/redo04.dbf       ACTIVE      50.00
5         22 /oracle_fs/Databases/ORT24FS/redo05.dbf       CURRENT     50.00

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> @log_files

Grp  SEQUENCE# MEMBER                                        Status       Size
---- ---------- --------------------------------------------- --------- -------
1         23 /oracle_fs/Databases/ORT24FS/redo01.dbf       ACTIVE      50.00
2         24 /oracle_fs/Databases/ORT24FS/redo02.dbf       ACTIVE      50.00
3         25 /oracle_fs/Databases/ORT24FS/redo03.dbf       CURRENT     50.00
4         21 /oracle_fs/Databases/ORT24FS/redo04.dbf       ACTIVE      50.00
5         22 /oracle_fs/Databases/ORT24FS/redo05.dbf       ACTIVE      50.00

SQL> 

How did I do it ? I was very careful to use "ALTER SYSTEM SWITCH LOGFILE" to ensure that my CURRENT GROUP# was "n-1" before adding GROUP#n.

Note : The "ALTER DATABASE DROP LOGFILE" command didn't physically remove the logfiles. I used another terminal to run "rm redo0[3-6].dbf" after ensuring that they no longer belonged to the database. Be very very very careful when removing RedoLog files in this manner -- you don't want to remove a file at the OS level when the GROUP# is still "ACTIVE" or "CURRENT" or even "INACTIVE" or "UNUSED" in V$LOG --- the errors and error handling are different in each case (removing the CURRENT file requires a RESTORE and INCOMPLETE RECOVERY of the Database !).

FYI, this is the "log_files.sql" script I used :

SQL> !cat log_files.sql
col member format a45
col sz format 999.99 hea 'Size'
col group# format 999 hea 'Grp'
col status format a9 hea 'Status'

select l.group#, l.sequence#,f.member, l.status, l.bytes/1048576 sz
from v$logfile f, v$log l
where f.group#=l.group#
order by f.group#, f.member
/

SQL> 

Another note : I have only 1 MEMBER per GROUP#. In your production database, if you follow "best practice" you probably have 2 or more MEMBERs per GROUP#.

So, brush up on your understanding of
THREAD#
GROUP#
MEMBER
SEQUENCE# !!


PS : Don't try these commands on your Production database until you have rehearsed the scenarios on a Test Database !
.
.
.

(Off Topic): "What's the body count ?"

An article, by J A Flinn, on the "costing" of the human side of Projects / Change Projects, titled "What's the body count ?"

.

.

.

Preserving the Index when dropping the Constraint

A Unique Index that is used to enforce a Unique or Primary Key constraint is sometimes expected to be dropped when the constraint is dropped.
However, this is not the behaviour if the Index pre-dates the constraint -- i.e the index is created before the constraint and the constraint "uses" the existing index. In such a case, since the index definition is not linked to the constraint definition, dropping the constraint does not drop the index.

Here I demonstrate how dropping a constraint does not drop the index because the index was created before the constraint. Also note how the error message (in 10.2.0.4 and below) can be misleading -- it doesn't really state whether an insert fails because of a Unique Index or a Unique Constraint.

SQL> create table my_table (id number, name varchar2(80));

Table created.

SQL> create unique index my_table_uk on my_table(id);

Index created.

SQL> insert into my_table select object_id, object_name from dba_objects;            

50743 rows created.

SQL> commit;

Commit complete.

SQL> alter table my_table add constraint my_table_uk unique (id);

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE';  

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
MY_TABLE_UK                    U MY_TABLE_UK

SQL> select index_name, uniqueness from user_indexes where table_name = 'MY_TABLE';

INDEX_NAME                     UNIQUENES
------------------------------ ---------
MY_TABLE_UK                    UNIQUE

SQL> alter table my_table drop constraint my_table_uk;

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE';

no rows selected

SQL> select index_name, uniqueness, status from user_indexes where table_name = 'MY_TABLE';

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
MY_TABLE_UK                    UNIQUE    VALID

SQL> insert into my_table select object_id, object_name from dba_objects;
insert into my_table select object_id, object_name from dba_objects
*
ERROR at line 1:
ORA-00001: unique constraint (HEMANT.MY_TABLE_UK) violated


SQL> 

It isn't necessary that the constraint name and index name be the same. However, if the index name is different from the constraint name, while the index is still preserved after the constraint is dropped, the ORA-00001 error reports the index name :

SQL> create table my_table_2 (id_2 number, name_2 varchar2(80));

Table created.

SQL> create unique index my_t_2_uniq on my_table_2(id_2);

Index created.

SQL> insert into my_table_2 select object_id, object_name from dba_objects;

50743 rows created.

SQL> commit;

Commit complete.

SQL> alter table my_table_2 add constraint my_table_2_uk unique (id_2);

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE_2';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
MY_TABLE_2_UK                  U MY_T_2_UNIQ

SQL> select index_name, uniqueness, status from user_indexes where table_name = 'MY_TABLE_2';

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
MY_T_2_UNIQ                    UNIQUE    VALID

SQL> alter table my_table_2 drop constraint my_table_2_uk;

Table altered.

SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'MY_TABLE_2';

no rows selected

SQL> select index_name, uniqueness, status from user_indexes where table_name = 'MY_TABLE_2';

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
MY_T_2_UNIQ                    UNIQUE    VALID

SQL> insert into my_table_2 select object_id, object_name from dba_objects;
insert into my_table_2 select object_id, object_name from dba_objects
*
ERROR at line 1:
ORA-00001: unique constraint (HEMANT.MY_T_2_UNIQ) violated


SQL>  

You may run similar tests with a Primary Key constraint and see how the Index behaves if the constraint is dropped.

.
.
.

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

.
.
.

(Off Topic) : "Now Is That Architecture ?"

Here's a post about an implementation that IT and Business should both study.

.

.

.