archivelog info

Mike301

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

Alvaro

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

>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