Oracle 19c - Archive Space Alert and how to fix
Oracle 19c - Archive Space Alert and how to fix
Important Queries to use ##
-- List of Archive Destination
SET LINES 300
COL dest_name FORMAT A30
COL status FORMAT A20
COL destination FORMAT A30
select dest_name,status,destination from V$ARCHIVE_DEST;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u02/arch' scope=both; -- Dynamic Parameter
ALTER SYSTEM RESET log_archive_dest_1 scope=spfile; -- Instance Restart is required ..
ALTER SYSTEM SWITCH LOGFILE;
-- List of Archive Files with path, name & Status
SET LINES 300
col name FORMAT A40
SELECT name, dest_id, thread#, sequence#, archived, applied, deleted, status, completion_time, con_id
FROM v$archived_log
ORDER BY sequence# DESC;
-- Archive Destination list with Status
SET LINES 300
select dest_id, dest_name, status, type from v$archive_dest_status;
alter system set db_recovery_file_dest='' scope=both;
alter system set db_recovery_file_dest='/u02/fast_recovery_area' scope=both;
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=both;
-- RMAN script to backup archive log files and delete them from the server
run
{
allocate channel c1 type disk format '/u02/backup/rman/arch_bkp_%U.bkp';
allocate channel c2 type disk format '/u02/backup/rman/arch_bkp_%U.bkp';
backup archivelog until time 'sysdate -1' delete input;
release channel c1;
release channel c2;
}
run
{
allocate channel c1 type disk format '/u02/backup/rman/arch_bkp_%U.bkp';
allocate channel c2 type disk format '/u02/backup/rman/arch_bkp_%U.bkp';
backup archivelog all delete input;
release channel c1;
release channel c2;
}