SELECT thread#,
COUNT(*) AS cnt,
MIN(sequence#) AS min_seq,
MAX(sequence#) AS max_seq,
MIN(first_time) AS min_time,
MAX(next_time) AS max_time
FROM v$archived_log
WHERE deleted = 'NO'
GROUP BY thread#
ORDER BY thread#;
THREAD# CNT MIN_SEQ MAX_SEQ MIN_TIME MAX_TIME
__________ ______ __________ __________ ______________________ ______________________
1 80 7936 8015 2026-02-16 15:38:02 2026-02-18 14:15:42
Showing posts with label Flash Recovery Area. Show all posts
Showing posts with label Flash Recovery Area. Show all posts
Wednesday, February 18, 2026
Useful query against v$archived_log in recovery situations
Friday, March 13, 2020
Remember to delete archivelogs from obsolete incarnations after a flashback database operation
I have recently been supporting a customer by setting guaranteed restore points and using these to flash the database back on their signal, typically when they need to redeploy some code for their application.
It's easy to forget that every time you open the database with the RESETLOG options, you create another incarnation of your database.
Here is what my flash recovery area looked like:
cd /fra/SALESDB/archivelogs du -sh * 300M 2020_01_30 36G 2020_02_03 248M 2020_02_04 1.5G 2020_02_05 273M 2020_02_06 284M 2020_02_07 332M 2020_02_08 3.7G 2020_02_09 510G 2020_02_11 15G 2020_02_12 1.1G 2020_02_13 386M 2020_02_14 237M 2020_02_15 3.7G 2020_02_16 14G 2020_02_17 523G 2020_02_18 1.5G 2020_02_19 208M 2020_02_20 213M 2020_02_21 239M 2020_02_22 3.6G 2020_02_23 217M 2020_02_24 293M 2020_02_25 1.5G 2020_02_26 258M 2020_02_27 261M 2020_02_28 296M 2020_02_29 3.6G 2020_03_01 507G 2020_03_02 216M 2020_03_03 13G 2020_03_04 214M 2020_03_05 211M 2020_03_06 237M 2020_03_07 3.6G 2020_03_08 288M 2020_03_09 244M 2020_03_10 1.6G 2020_03_11 16G 2020_03_12 504G 2020_03_13
The latest incarnation was created on the 12.03.2020:
RMAN> list incarnation of database; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 SALESDB 1626528189 PARENT 270012427233 30.01.2020 11:39:03 2 2 SALESDB 1626528189 PARENT 270012433324 30.01.2020 11:51:46 3 3 SALESDB 1626528189 PARENT 270020068677 11.02.2020 11:00:20 4 4 SALESDB 1626528189 PARENT 270020175869 13.02.2020 12:02:52 5 5 SALESDB 1626528189 PARENT 270020238995 18.02.2020 10:50:25 6 6 SALESDB 1626528189 PARENT 270020500540 02.03.2020 13:51:53 7 7 SALESDB 1626528189 CURRENT 270020646622 12.03.2020 14:18:33
This situation caused the flash recovery area to fill, since Oracle wasn't able to delete archivelogs from the previous incarnation.
I solved this by deleting archivelogs from before the last incarnation was created.
First, list the logs you want to delete:
rman target / nocatalog log=list_archlogs.txt'
RMAN> list archivelog all completed before "to_date('12.03.2020 14:18:33')";
When I check the file, these were all older logs, residing in folders dated before the last incarnation was created.
I then deleted them as follows:
delete archivelog all completed before "to_date('12.03.2020 14:18:33')";
The size of the flash recovery area has now dropped to 17% and the file listing of /fra/SALESDB/archivelogs now shows empty folders:
4.0K 2020_01_30 8.0K 2020_02_03 4.0K 2020_02_04 4.0K 2020_02_05 4.0K 2020_02_06 4.0K 2020_02_07 4.0K 2020_02_08 16K 2020_02_09 48K 2020_02_11 4.0K 2020_02_12 4.0K 2020_02_13 4.0K 2020_02_14 4.0K 2020_02_15 4.0K 2020_02_16 4.0K 2020_02_17 52K 2020_02_18 4.0K 2020_02_19 4.0K 2020_02_20 4.0K 2020_02_21 4.0K 2020_02_22 4.0K 2020_02_23 4.0K 2020_02_24 4.0K 2020_02_25 4.0K 2020_02_26 4.0K 2020_02_27 4.0K 2020_02_28 4.0K 2020_02_29 4.0K 2020_03_01 52K 2020_03_02 4.0K 2020_03_03 4.0K 2020_03_04 4.0K 2020_03_05 4.0K 2020_03_06 4.0K 2020_03_07 4.0K 2020_03_08 4.0K 2020_03_09 4.0K 2020_03_10 4.0K 2020_03_11 15G 2020_03_12 504G 2020_03_13
Monday, August 5, 2019
How to work around RMAN-08120: warning: archived log not deleted, not yet applied by standby
Problem scenario:
* You have a primary database with two standby databases.
* One of them is functional, the other one is about to be rebuilt.
* Archived redo logs are piling up on the primary.
You have already set the log_archive_dest_n to DEFERRED on the primary site, and this will prevent logs from being shipped to the standby database that is about to be rebuilt.
The reason for this is explained in Doc ID 1380368.1 on My Oracle Support: Oracle will, by default, also consider deferred locations, before deleting archive logs from the primary.
Solution:
This can be changed by setting a hidden parameter, _deferred_log_dest_is_valid, from TRUE to FALSE.
To verify that this worked as intended, I listed a couple of the archivelogs on the primary:
RMAN> list archivelog sequence between 110880 and 110881;
List of Archived Log Copies for database with db_unique_name PRODDB01
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
173550 1 110880 A 19-JUL-19
Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc
173551 1 110881 A 19-JUL-19
Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc
Now try to delete them from the primary server:
RMAN> delete archivelog sequence between 110880 and 110881; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=78 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=723 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=14 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=295 device type=DISK RMAN-08120: warning: archived log not deleted, not yet applied by standby archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc thread=1 sequence=110880 RMAN-08120: warning: archived log not deleted, not yet applied by standby archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc thread=1 sequence=110881Oracle doesn't allow you to delete them, since they haven't been applied to all standby destinations.
To work around this, set the hidden parameter:
alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;
Try deleting them once more shows that Oracle now allows you to do so:
RMAN> delete archivelog sequence between 110880 and 110881;
List of Archived Log Copies for database with db_unique_name proddb01
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
173550 1 110880 A 19-JUL-19
Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc
173551 1 110881 A 19-JUL-19
Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc RECID=173550 STAMP=1014076575
Deleted 1 objects
deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc RECID=173551 STAMP=1014076710
Deleted 1 objects
Use
delete noprompt archivelog sequence between 110880 and 110881;to avoid having to confirm the delete operation.
Friday, June 29, 2018
Overview over archivelogs present in the FRA
The following query reveals that my FRA contains archived logs from the last 3 days only (indicated by an "A" - available).
-- Set your NLS_DATE_FORMAT in your session to avoid ORA-01830: date format picture ends before converting entire input string
alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
select trunc(completion_time),status,count(*)
from v$archived_log
where completion_time > to_date('20.06.2018 00:00:00')
group by trunc(completion_time),status
order by 1 desc;Output:
| TRUNC(COMPLETION_TIME) | STATUS | COUNT(*) |
|---|---|---|
| 29.06.2018 | A | 37 |
| 28.06.2018 | A | 57 |
| 27.06.2018 | A | 54 |
| 27.06.2018 | D | 1 |
| 26.06.2018 | D | 75 |
| 25.06.2018 | D | 75 |
| 24.06.2018 | D | 53 |
| 23.06.2018 | D | 55 |
| 22.06.2018 | D | 59 |
| 21.06.2018 | D | 58 |
| 20.06.2018 | D | 54 |
Archive log files prior to these, have been deleted (indicated by a "D") by rman after having been successfully backed up.
Monday, June 30, 2014
log_archive_format default value
Having trouble finding out why the value of the parameter LOG_ARCHIVE_FORMAT does not seem to apply to your database?
From Oracle 10g and onwards, the default value for LOG_ARCHIVE_FORMAT is %t_%S_%r.dbf
However, this parameter will only make a difference if you are NOT using a "Fast Recovery Area" (in Version 10 Oracle called the same thing a "Flash Recovery Area")
If you have defined a FRA by using the parameters db_recovery_file_dest and db_recovery_file_dest_size, the archivelogs will be automatically named according to OMF (Oracle Managed Files) standard and the value of LOG_ARCHIVE_FORMAT will be ignored.
An example of an OMF managed file in the FRA:
From the documentation:
At first glance, letting the LOG_ARCHIVE_FORMAT default to such a poor naming standard may seem like a bad idea, but I guess it illustrates the point of defining an FRA vs. using conventional archive log destinations.
From Oracle 10g and onwards, the default value for LOG_ARCHIVE_FORMAT is %t_%S_%r.dbf
However, this parameter will only make a difference if you are NOT using a "Fast Recovery Area" (in Version 10 Oracle called the same thing a "Flash Recovery Area")
If you have defined a FRA by using the parameters db_recovery_file_dest and db_recovery_file_dest_size, the archivelogs will be automatically named according to OMF (Oracle Managed Files) standard and the value of LOG_ARCHIVE_FORMAT will be ignored.
An example of an OMF managed file in the FRA:
/fra/PRODDB01/archivelog/2014_06_30/o1mv_1_1_9vxzch5_.arc
From the documentation:
"The fast recovery area is an Oracle Database managed space that can be used to hold RMAN disk backups, control file autobackups and archived redo log files. The files placed in this location are maintained by Oracle Database and the generated file names are maintained in Oracle Managed Files (OMF) format."
At first glance, letting the LOG_ARCHIVE_FORMAT default to such a poor naming standard may seem like a bad idea, but I guess it illustrates the point of defining an FRA vs. using conventional archive log destinations.
Tuesday, January 21, 2014
ksh: script that will send alert when flash recovery are is filling up
#!/usr/bin/ksh
# Vegard Kasa
# 11.07.2013
#
# Alert when FRA is filling up...
#############################################################################################################
export NOW=`date +\%d.\%m.\%y`
export NLS_DATE_FORMAT='DD.MM.YYYY'
####################################################################
# Set the script base to either $DBA_ADMIN in case of original build
# or $DBA_ADMIN_HOME in case of newer build.
####################################################################
if [ -n "${DBA_ADMIN}" ]; then
##############
# Original build
##############
SCRIPT_BASE=$DBA_ADMIN
elif [ -n "${DBA_ADMIN_HOME}" ]; then
#################
# Another build
# uses different
# variable names
#################
SCRIPT_BASE=${DBA_ADMIN_HOME}
fi
cat << EoF > ${SCRIPT_BASE}/sql/get_sum_fra.sql
set termout off
set trimspool on
set verify off
set feedback off
set echo off
set linesize 200
set heading off
set pagesize 0
spool ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log
SELECT LTRIM(sum(PERCENT_SPACE_USED))
FROM V\$FLASH_RECOVERY_AREA_USAGE;
exit
EoF
sqlplus -s / as sysdba @${SCRIPT_BASE}/sql/get_sum_fra.sql
##########################################################
# Send the output via e-mail to the designated receipients
##########################################################
PRC=`cat ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log`
if [ ${PRC} -gt 80 ]; then
echo "Warning: In database ${ORACLE_SID}, the FRA is ${PRC} percent used!" |mailx -r monitoring@yourdomain.com -s "FRA usage report for database ${ORACLE_SID}" dba@yourdomain.com
fi
exit
Monday, January 20, 2014
How the Archived Redo Log Deletion Policy works
Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Note that *only* logs in the flash recovery area can be deleted automatically by the database.
You can use RMAN to create a persistent configuration that governs when archived redo logs are eligible for deletion from disk, by using the ARCHIVELOG DELETION POLICY. The archived redo log deletion policy is configured to NONE by default.
When the Archived Redo Log Deletion Policy Is ENABLED
You can use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo logs are eligible for deletion. This deletion policy applies to all archiving destinations, including the flash recovery area.
For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs [only] when additional disk space is required.
You can also manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.
When the Archived Redo Log Deletion Policy Is DISABLED
In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:
1. The archived redo logs, whether in the flash recovery area or outside of it, have been transferred to the required remote destinations specified by LOG_ARCHIVE_DEST_n.
2. The archived redo logs have been backed up at least once to disk or SBT or the logs are obsolete according to the backup retention policy.
The backup retention policy considers logs obsolete only if the logs are not needed by a guaranteed restore point and the logs are not needed by Oracle Flashback Database.
Source: Oracle Documentation
You can use RMAN to create a persistent configuration that governs when archived redo logs are eligible for deletion from disk, by using the ARCHIVELOG DELETION POLICY. The archived redo log deletion policy is configured to NONE by default.
When the Archived Redo Log Deletion Policy Is ENABLED
You can use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo logs are eligible for deletion. This deletion policy applies to all archiving destinations, including the flash recovery area.
For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs [only] when additional disk space is required.
You can also manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.
When the Archived Redo Log Deletion Policy Is DISABLED
In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:
1. The archived redo logs, whether in the flash recovery area or outside of it, have been transferred to the required remote destinations specified by LOG_ARCHIVE_DEST_n.
2. The archived redo logs have been backed up at least once to disk or SBT or the logs are obsolete according to the backup retention policy.
The backup retention policy considers logs obsolete only if the logs are not needed by a guaranteed restore point and the logs are not needed by Oracle Flashback Database.
Source: Oracle Documentation
How does Oracle manage disk space in the Flash Recovery Area?
Files in the recovery area are permanent or transient.
Permanent files are active files used by the database instance (like control files).
All files that are not permanent are transient.
In general, Oracle Database eventually deletes transient files after they become obsolete under the backup retention policy or have been backed up to tape.
Space in the flash recovery area is balanced among backups and archived logs that must be kept according to the retention policy, and other files which may be subject to deletion.
Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose.
Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape.
When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.
The following rules govern when files become eligible for deletion from the recovery area:
• Permanent files are never eligible for deletion.
• Files that are obsolete under the retention policy are eligible for deletion.
• Transient files that have been copied to tape are eligible for deletion.
• Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements. Consumers of logs can include RMAN, standby databases, Oracle Streams databases, and the Flashback Database feature.
The safe and reliable way to control deletion of files from the flash recovery area is to configure your retention policy and archived log deletion policy.
To increase the likelihood that files moved to tape are retained on disk, increase the flash recovery area quota.
Source: Oracle Documentation
Permanent files are active files used by the database instance (like control files).
All files that are not permanent are transient.
In general, Oracle Database eventually deletes transient files after they become obsolete under the backup retention policy or have been backed up to tape.
Space in the flash recovery area is balanced among backups and archived logs that must be kept according to the retention policy, and other files which may be subject to deletion.
Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose.
Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape.
When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.
The following rules govern when files become eligible for deletion from the recovery area:
• Permanent files are never eligible for deletion.
• Files that are obsolete under the retention policy are eligible for deletion.
• Transient files that have been copied to tape are eligible for deletion.
• Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements. Consumers of logs can include RMAN, standby databases, Oracle Streams databases, and the Flashback Database feature.
The safe and reliable way to control deletion of files from the flash recovery area is to configure your retention policy and archived log deletion policy.
To increase the likelihood that files moved to tape are retained on disk, increase the flash recovery area quota.
Source: Oracle Documentation
Saturday, October 19, 2013
How to instruct Oracle to use the flash recovery area as archive log destination
Find out what the current setting is:
sqlplus / as sysdba SQL> show parameter db_recovery NAME TYPE VALUE ---------------------------- ------------- ------------------------ db_recovery_file_dest string /data/oracle/PRODDB/f01 db_recovery_file_dest_size big integer 500G show parameter log_archive NAME TYPE VALUE --------------------------- -------------- -------------------------- log_archive_dest_1 string LOCATION=/data/oracle/PRODDB/u01/newarchloc/
Then, switch to the flash recovery area:
alter system set log_archive_dest_1='location=use_db_recovery_file_dest' scope=both;
Subscribe to:
Comments (Atom)