Oracle DBA – A lifelong learning experience

Timestamps within ASM

Posted by John Hallas on April 2, 2009

Blogs are normally about how to resolve things or sharing some newly found information. In this case I am trying to resolve a question which I have struggled to understand for a while. It is concerned with the timestamps used within ASM.

 

An example is provided below. I switched  the archive log three times at 7:31 and the logs numbers were 100,101,102

 

Looking in ASM I see the timestamps as 07:00

 

ARCHIVELOG  UNPROT  COARSE   APR 02 04:00:00  Y   thread_1_seq_97.379.683084013

ARCHIVELOG  UNPROT  COARSE   APR 02 04:00:00  Y   thread_1_seq_98.380.683093773

ARCHIVELOG  UNPROT  COARSE   APR 02 04:00:00  Y   thread_1_seq_99.381.683093777

ARCHIVELOG  UNPROT  COARSE   APR 02 07:00:00  Y   thread_1_seq_100.382.683105505

ARCHIVELOG  UNPROT  COARSE   APR 02 07:00:00  Y   thread_1_seq_101.383.683105513

ARCHIVELOG  UNPROT  COARSE   APR 02 07:00:00  Y   thread_1_seq_102.384.683105517

Fair enough, it is to the nearest hour, or to be more precise the first minute of the current hour and that is proved with another switch at 08:37

 

ARCHIVELOG  UNPROT  COARSE   APR 02 08:00:00  Y   thread_1_seq_103.385.683109437

Now I look at datafiles and  ls –lrt of the DATAFILES older shows the last few entries as

 

DATAFILE  UNPROT  COARSE   APR 02 00:00:00  Y    MIS_SEP08.304.682602085

DATAFILE  UNPROT  COARSE   APR 02 00:00:00  Y    MIS_SEP09.315.682602327

DATAFILE  UNPROT  COARSE   APR 02 00:00:00  Y    UNDOTBS1.263.675360475

DATAFILE  UNPROT  COARSE   APR 02 01:00:00  Y    SYSAUX.262.675360447

 

And this does not change after I take a checkpoint (alter system checkpoint).

 

I then take a tablespace off line and bring it back online which I would expect to update the file timestamp

 

 

Thu Apr 02 09:47:04 2009

alter tablespace mis_sep09 offline

Completed: alter tablespace mis_sep09 offline

alter tablespace mis_sep09 offline

ORA-1539 signalled during: alter tablespace mis_sep09 offline…

Thu Apr 02 09:47:20 2009

alter tablespace mis_sep09 online

Completed: alter tablespace mis_sep09 online

 

 

Looking in ASM I note that some datafiles, including the datafile for the mis_SEP09 tablespaces has had the timestamp updated to 09:00 which is the first minute of the current hour and yet one file is timestamped at 01:00 and the rest are at midnight 00:00. I have not shown all the datafiles but the information below is sufficient to see why I am a bit mystified by it all.  As I said at the beginning this is more of a blog seeking input so if anybody wishes to comment then feel free

 

 

DATAFILE  UNPROT  COARSE   APR 02 00:00:00  Y    UNDOTBS1.263.675360475

DATAFILE  UNPROT  COARSE   APR 02 01:00:00  Y    SYSAUX.262.675360447

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_APR07.287.682601701

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_BAW.277.680541513

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_FEB08.297.682601933

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_FEB09.308.682602173

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_JAN09.307.682602151

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_JUL07.290.682601765

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_JUN09.312.682602259

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_MAY08.300.682601997

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_OCT08.305.682602107

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_ODS.283.682443071

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_REPO_DAC.272.680541211

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_REPO_PUBLISHER.275.680541225

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_REPO_SCHEDULER.274.680541221

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    MIS_SEP09.315.682602327

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    SYSTEM.261.675360423

DATAFILE  UNPROT  COARSE   APR 02 09:00:00  Y    USERS.264.675360485

2 Responses to “Timestamps within ASM”

  1. Paul said

    Hi, According to MOS note “ASMCMD ls -lt shows wrong date/time for control and data files (Doc ID 1603991.1)”. The timestamp is ASMCMD is the last time the file was opened/closed for writing. Not when it was last updated or checkpointed.

    • John Hallas said

      Thanks Paul, I must admit I had forgotten that post – it was nearly 7 years ago.
      The note you refer to was written in 2013 so may jnot have existed when I wrote my post. However I still dont think it is as clear as all that

      Take the following example
      sandpit database

      ASMCMD> ls -l
      Type Redund Striped Time Sys Name
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y DWB_BASE_TBS.329.875101149
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y SOE.313.854816707
      DATAFILE UNPROT COARSE FEB 04 12:00:00 Y SYSAUX.257.851337533
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y SYSTEM.256.851337533
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y UNDOTBS1.258.851337533
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y USERS.259.851337533

      oracle 30734 1 0 Jan05 ? 00:27:42 ora_pmon_TST11204

      SQL> alter tablespace SOE offline

      ASMCMD [+] > ls -l data/tst11204/datafile
      Type Redund Striped Time Sys Name
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y DWB_BASE_TBS.329.875101149
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y SOE.313.854816707
      DATAFILE UNPROT COARSE FEB 04 12:00:00 Y SYSAUX.257.851337533
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y SYSTEM.256.851337533
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y UNDOTBS1.258.851337533
      DATAFILE UNPROT COARSE FEB 03 22:00:00 Y USERS.259.851337533

      shutdown the database and restart it

      ASMCMD [+] > ls -l data/tst11204/datafile
      Type Redund Striped Time Sys Name
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y DWB_BASE_TBS.329.875101149
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y SOE.313.854816707
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y SYSAUX.257.851337533
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y SYSTEM.256.851337533
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y UNDOTBS1.258.851337533
      DATAFILE UNPROT COARSE FEB 04 15:00:00 Y USERS.259.851337533

      I agree that when I shut the database down or took the SOE datafile offline the timestamp was updated.
      Hwoever you will also note that before I did anything the timestamp of all datafiles bar SYSAUX was 03 Feb 22:00. We know the database was not down at that time or the tablespaces taken off line.
      According to the docs then all databases I look at should have quite old timestamps if they have been up a long time but that is not the case. Most are either yesterday or today and within the last 24 hours.

      John

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: