OracleRedo & archiving

Archived redo generated per day

Count and size of archived logs for the last 14 days, from the local destination only so standby copies aren't double-counted. Useful for sizing the FRA and the network link to a standby.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN day  FORMAT A104COLUMN logs FORMAT 99,9905COLUMN gb   HEADING "GB" FORMAT 99,990.996 7SELECT TO_CHAR(TRUNC(completion_time), 'YYYY-MM-DD') AS day,8       COUNT(*) AS logs,9       ROUND(SUM(blocks * block_size) / POWER(1024, 3), 2) AS gb10FROM v$archived_log11WHERE completion_time > TRUNC(SYSDATE) - 1412  AND dest_id = 113GROUP BY TRUNC(completion_time)14ORDER BY TRUNC(completion_time);

Save it as ora-archive-volume.sql and run it with SQL> @ora-archive-volume.

Open in denrepoRelated tool: Redo log sizing

Helps with

• ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
• ORA-19815: WARNING: db_recovery_file_dest_size of %s bytes is %s%% used, and has %s remaining bytes available.

Part of these runbooks

• Space emergency

More Oracle scripts: Redo & archiving

• Online redo log groups and membersEvery redo log group with its thread, sequence, archived flag, status, member file and size. More than one CURRENT per thread, or members missing…
• Log switches per hour, last 7 daysA day-by-hour grid of log switches. Aim for roughly four an hour at peak; consistently more means the redo logs are too small.
• Redo generated per hourActual redo volume per hour for the last 7 days, from archived log sizes on the local destination, so early log switches and Data Guard copies don't…
• Sessions generating the most redoConnected sessions ranked by redo generated since logon. Finds the job behind a sudden flood of archive logs.