6 Replies Latest reply: May 13, 2013 2:24 AM by CKPT RSS

    oraphan(deleted) entries in the v$archived_log

    883677
      1)why I have oraphan(deleted) entries in the v$archived_log
      77338 04-28-2013 22:10:04 04-28-2013 22:10:39
      77489 04-29-2013 18:07:21 04-29-2013 18:07:27
      77489 04-29-2013 18:07:21 04-29-2013 18:07:27
      ....
      ....
      ....
      1 04-30-2013 16:35:28 05-01-2013 08:47:20
      1 04-30-2013 16:35:28 05-01-2013 08:47:20
      ------------
      5279 05-08-2013 00:05:12 05-08-2013 05:14:19
      5280 05-08-2013 05:14:19 05-08-2013 11:36:16
      5280 05-08-2013 05:14:19 05-08-2013 11:36:16
      I checked my physical stand is in synced with primary.I have a script to monitor arch gap which grab max seq.So in this case it grabs (77497 29 apr) instead of (5280 may 8 2013)
        • 1. Re: oraphan(deleted) entries in the v$archived_log
          Hemant K Chitale
          I don't understand the output. You haven't presented the column headers.

          archivelog entries wouldn't be automatically deleted. There would be a manual / automated / scheduled rman script to delete archivelogs. Look for such a script.
          The one condition in which archivelogs may be automatically deleted is when they are in the FRA and the FRA is running out of space -- when Oracle may automatically delete archivelogs that are obsolete.


          Hemant K Chitale
          • 2. Re: oraphan(deleted) entries in the v$archived_log
            CKPT
            880674 wrote:
            1)why I have oraphan(deleted) entries in the v$archived_log
            77338 04-28-2013 22:10:04 04-28-2013 22:10:39
            77489 04-29-2013 18:07:21 04-29-2013 18:07:27
            77489 04-29-2013 18:07:21 04-29-2013 18:07:27
            ....
            ....
            ....
            1 04-30-2013 16:35:28 05-01-2013 08:47:20
            1 04-30-2013 16:35:28 05-01-2013 08:47:20
            ------------
            5279 05-08-2013 00:05:12 05-08-2013 05:14:19
            5280 05-08-2013 05:14:19 05-08-2013 11:36:16
            5280 05-08-2013 05:14:19 05-08-2013 11:36:16
            I checked my physical stand is in synced with primary.I have a script to monitor arch gap which grab max seq.So in this case it grabs (77497 29 apr) instead of (5280 may 8 2013)
            Better if you post complete output with v$archived_Log including the column resetlogs_change# then we can know whether it is orphan archives.
            Orphan archives can be catloged may be earlier drills, Such scenario i put in article, You can read it here

            http://www.oracle-ckpt.com/misconception-of-archive-log-sequences-in-data-guard/

            HTH.
            • 3. Re: oraphan(deleted) entries in the v$archived_log
              883677
              Thanks a lot to HC and CKPT for finding time and replying me.
              CKPT I really like your article.I think I am facing same issue.
              We have to reset primary on 30 april 2013.
              DEVL PRIMARY          77490     5474 09-MAY/00:06      ####

              Primary:
              select distinct RESETLOGS_CHANGE#, RESETLOGS_TIME from v$archived_log order by RESETLOGS_TIME desc;

              RESETLOGS_CHANGE# RESETLOGS_TIME
              ----------------- -------------------
              2.2701E+10 05-01-2013 08:47:20
              2.2701E+10 04-30-2013 16:35:28
              1 08-28-2012 16:34:30

              SQL> select distinct RESETLOGS_CHANGE#, RESETLOGS_TIME from v$archived_log order by RESETLOGS_TIME desc;

              RESETLOGS_CHANGE# RESETLOGS_TIME
              ----------------- -------------------
              2.2701E+10 05-01-2013 08:47:20
              2.2701E+10 04-30-2013 16:35:28
              1 08-28-2012 16:34:30

              SQL> select max(sequence#) from v$archived_log;

              MAX(SEQUENCE#)
              --------------
              77490

              SQL> archive log list
              Database log mode Archive Mode
              Automatic archival Enabled
              Archive destination USE_DB_RECOVERY_FILE_DEST
              Oldest online log sequence 5471
              Next log sequence to archive 5476
              Current log sequence 5476

              STANDBY
              SQL> select max(sequence#) from v$archived_log where applied='YES';

              MAX(SEQUENCE#)
              --------------
              5474
              • 4. Re: oraphan(deleted) entries in the v$archived_log
                883677
                onnect target

                change archivelog sequence 1 uncatalog;

                using target database control file instead of recovery catalog
                specification does not match any archived log in the repository

                RMAN> change archivelog all uncatalog;

                uncataloged archived log
                archived log file name=/ora/DB/DEVL/flash_rec_area/DEVL/archivelog/2013_05_08/o1 mf1_5282_8rop9w0w_.arc RECID=9297 STAMP=814903278
                uncataloged archived log
                archived log file name=/ora/DB/DEVL/flash_rec_area/DEVL/archivelog/2013_05_08/o1 mf1_5283_8ropbx0j_.arc RECID=9299 STAMP=814903311
                uncataloged archived log
                archived log file name=/ora/DB/DEVL/flash_rec_area/DEVL/archivelog/2013_05_08/o1 mf1_5284_8ropc94v_.arc RECID=9300 STAMP=814903323
                uncataloged archived log


                .....


                ...
                archived log file name=/ora/DB/DEVL/flash_rec_area/DEVL/archivelog/2013_05_10/o1 mf1_5669_8rsfsplc_.arc RECID=10071 STAMP=815025640
                Uncataloged 388 object



                catalog start with 'archive_log_location';

                searching for all files that match the pattern archive_log_location
                no files found to be unknown to the database
                • 5. Re: oraphan(deleted) entries in the v$archived_log
                  Hemant K Chitale
                  catalog start with 'archive_log_location';
                  Is that "catalog start with 'archive_log_location'" OR "catalog start with '/ora/DB/DEVL/flash_rec_area/DEVL/archivelog' ?


                  Hemant K Chitale
                  • 6. Re: oraphan(deleted) entries in the v$archived_log
                    CKPT
                    archived log file name=/ora/DB/DEVL/flash_rec_area/DEVL/archivelog/2013_05_10/o1 mf1_5669_8rsfsplc_.arc RECID=10071 STAMP=815025640
                    Uncataloged 388 object
                    So far it is very good. :)
                    catalog start with 'archive_log_location';

                    searching for all files that match the pattern archive_log_location
                    no files found to be unknown to the database
                    Here you have to change the archive location as per your environment,
                    See.... archive log location may be your OS is Unix and may be am using Windows. And i may use ASM and you are using non-ASM, and i may use /oraarch and it can be "/u01" for you. So archive location you have to change according to your environment. It should fix and update the thread once you are done and able to view the queries what you are expecting. Have a great day. :)