5 Replies Latest reply: Jan 20, 2010 9:06 AM by Girish Sharma RSS

    Last refresh date

    533779
      Hi All,

      DB 10.2.0.4.0 - 64bit Production

      Is there anyway to find the last refresh date on which the database refreshed from production. I've tried with Alert.log, but the log does not contain the information, may be the required log ahs been pushed to tape. I've also tried with ls -cdate, but it again gives the last updated time. Would really apprictae, if you come up with a magic.

      hare krishna
        • 1. Re: Last refresh date
          Pavan DBA
          can you tell how refresh happens in your environment?
          • 2. Re: Last refresh date
            533779
            Well, may be EXP/IMP may full refresh with backup or rman. We really have no idea. just got a request from client.


            hare krishna
            • 3. Re: Last refresh date
              Pavan DBA
              if it is exp/imp or expdp/impdp, check whether you are having import logs. for rman also i think we require logs.
              if it is with hot backup, then your instance start date is your refresh date (but the instance should not have been shutdown after that)
              • 4. Re: Last refresh date
                Anurag Tibrewal
                Amit Beheria wrote:
                Well, may be EXP/IMP may full refresh with backup or rman. We really have no idea. just got a request from client.


                hare krishna
                Hi,

                If it is exp/imp.
                I believe they would have truncated/created the objects (tables/views/packages). in that case you could check last_ddl_time. And the timestamp which are present in maximum objects could be the time of refresh

                If it is rman or any other refresh.
                And if they do resetlogs then you can check when was the resetlog was done by querying v$database.

                Again these methods are just on estimation

                Regards
                Anurag
                • 5. Re: Last refresh date
                  Girish Sharma
                  Database refresh means/ways :
                  1.Export/Import or Data Pump (because you are using 10g)
                  2.Transportable tablespaces.
                  3.RMAN's duplicate
                  4.restore hot or cold backup.
                  5.Patch apply.

                  Now your question is when one of the above activies done? So, for
                  1.object creation timestamp from dba_objects, export logs.
                  2.No idea yet...
                  3..if RMAN duplicate command session was started with log option then log creation date or

                  rman> spool log to c:\oracle\yourpath\mylog.log;
                  rman> run {rman's duplication commands...}
                  rman> spool log off;

                  4.check alert.log for shutdown and start history which can give some clue.
                  5.How to know the patches applied on DB?

                  Since above activities are not part of auditing; so its very hard to tell the exact date and time so far.

                  HTH
                  Girish Sharma