This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,924 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

archivelog info

Mike301
Mike301 Member Posts: 180 Blue Ribbon
edited Jan 21, 2014 9:39PM in General Database Discussions

Friends,

Oracle: 11gR2

OS: Linux

I have below couple of questions...

1. What's the correct way to determine how much (size) archivelog generated yesterday on a primary db? ... there is also standby db in the infrastructure

     Do I have to use dest_id of the primary db in my query?

If I don't use dest_id than the count is double.

SELECT completion_time, thread#, sum(blocks * block_size)/1024/1024 "SIZE_MB"
FROM v$archived_log
WHERE completion_time > trunc(sysdate - 1)
AND dest_id = 1
GROUP BY completion_time, thread#;

2.  V$BACKUP_ASYNC_IO: Is there anyway to find archivelog actual CREATION TIME from this view?

     As I understand this view records all backupset details and archivelog details.

     Basically we record all databases backup details from this view into centralized audit table, so trying to get archivelog original creation time from this view.

Thank you for reading this...

Tagged:

Answers

  • Alvaro
    Alvaro Member Posts: 709 Silver Badge
    edited Jan 21, 2014 7:28PM
    1. What's the correct way to determine how much (size) archivelog generated yesterday on a primary db? ... there is also standby db in the infrastructure
         Do I have to use dest_id of the primary db in my query?
    
    

    If queries on v$ view confuse you, there is a much more simpler way. Simply look at your alert log for an entire day. Take how many log switches you have in a day and multiply by the size of your archivelog. You will then have the total size of redo generated by your dabase in a day.

    Same with the time, every redo log switch means an archivelog creation.

  • Hemant K Chitale
    Hemant K Chitale Oracle DBA OCP OCE SingaporeMember Posts: 15,759 Blue Diamond

    >Do I have to use dest_id of the primary db in my query?

    Yes, you must query for only the primary dest_id

    >Is there anyway to find archivelog actual CREATION TIME from this view

    You'd have to query V$ARCHIVED_LOG.  V$BACKUP views show when the backup of the archivelog was done -- which is much after the time when the archivelog was actually created.

    Hemant K Chitale


    Hemant K Chitale
This discussion has been closed.