Ver sequência de Archivelog

STANDBY

SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

PROD

SQL> SELECT MAX(SEQUENCE#) FROM V$LOG WHERE ARCHIVED = 'YES';

Ver parâmetros

SQL> SHOW PARAMETERS LOG;

Ver modo

SQ> SELECT LOG_MODE FROM V$DATABASE;

Ver modo – Para pegar print relatorio

SQL> SELECT DBID, LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

Alterar destino

SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/fra/OLTP/archivelog';

Deletar archivelogs

RMAN> DELETE ARCHIVELOG ALL;

Ver porcentagem de uso do FRA

SQL> select round((SPACE_LIMIT-(SPACE_LIMIT-SPACE_USED)) / SPACE_LIMIT*100,2) FROM V$RECOVERY_FILE_DEST;

Archives gerados por dia

SQL> SELECT TRUNC(COMPLETION_TIME,'DD') DAY, THREAD#, ROUND(SUM(BLOCKS*BLOCK_SIZE)/1048576) MB,COUNT(*) ARCHIVES_GENERATED
FROM V$ARCHIVED_LOG
WHERE COMPLETION_TIME > SYSDATE-10
GROUP BY TRUNC(COMPLETION_TIME,'DD'),
THREAD# ORDER BY 1,2;

SQL> SELECT TRUNC(COMPLETION_TIME,'DD') DAY, THREAD#, ROUND(SUM(BLOCKS*BLOCK_SIZE)/1048576) MB,COUNT(*) ARCHIVES_GENERATED

FROM V$ARCHIVED_LOG

WHERE COMPLETION_TIME > SYSDATE-10

GROUP BY TRUNC(COMPLETION_TIME,'DD'),

THREAD# ORDER BY 1,2;

Archives gerados por hora

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
SELECT TRUNC(COMPLETION_TIME,'HH') HOUR,THREAD# , ROUND(SUM(BLOCKS*BLOCK_SIZE)/1048576) MB,COUNT(*) ARCHIVES
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME,'HH'),THREAD#
ORDER BY 1;

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

SELECT TRUNC(COMPLETION_TIME,'HH') HOUR,THREAD# , ROUND(SUM(BLOCKS*BLOCK_SIZE)/1048576) MB,COUNT(*) ARCHIVES

FROM V$ARCHIVED_LOG

GROUP BY TRUNC(COMPLETION_TIME,'HH'),THREAD#

ORDER BY 1;

Backup de Archivelog

RMAN> BACKUP ARCHIVELOG ALL FORMAT '/U01/FRA/%D/BACKUPSET/ARC_%D_%I_%S_%T.BKP' TAG 'BKP_ARCHIVELOG' DELETE INPUT;

RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES FORMAT '/u02/fra/APOLLO/backupset/Archives-%d___DBID-%I___Date-%T___Set-%s___Piece-%p.BKP' DELETE INPUT;

Referências:
http://oraclemais.blogspot.com/2014/05/geracao-de-archive-por-dia-e-por-hora.html