How to use RMANs LIST command to find individual archivelogs or a sequence of archivelogs

To list an individual backup, any of the three alternatives bellow would be valid query using rman:

RMAN> list backup of archivelog logseq=120316;
RMAN> list backup of archivelog logseq 120316;
RMAN> list backup of archivelog sequence 120316;

To view backups of archivelog between two sequences:

RMAN> list backup of archivelog sequence between 120316 and 120317;
RMAN> list backup of archivelog from logseq 412593 until logseq 412656;

Use the SUMMARY directive to view only the backupsets affected:

RMAN> list backup of archivelog from logseq 412593 until logseq 412656 summary;
RMAN> list backup of archivelog sequence between 120316 and 120317 summary;

To view backups completed before a specific date:

RMAN> list backup completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list backup of archivelog all summary completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list archivelog all completed before 'sysdate-2';:

To list archivelogs recognized by the controlfile between two sequnce numbers:

RMAN> list archivelog sequence between 110880 and 110881;

To list archivelogs recognized by the controlfile up until yesterday:

RMAN> list archivelog until time 'sysdate-1';

To list archivelogs recognized by the controlfile from two days ago, up until yesterday:

RMAN> list archivelog from time 'sysdate-2' until time 'sysdate-1';

To limit the list of archivelogs to the ones taken on a specific date, in this case, the last of January 2022:

RMAN> list archivelog from time '31.01.2022' until time '01.02.2022';

If you remove the "until time" clause, RMAN will list all the archivelogs from 31.01.2022 up until the latest ones:

RMAN> list archivelog from time '31.01.2022';

Remember, the value of sysdate is the time right now, sysdate-1 is 24hrs ago, sysdate-2 is 48 hours ago, etc.

 select sysdate, sysdate-1, sysdate-2 from dual;

SYSDATE             SYSDATE-1           SYSDATE-2
------------------- ------------------- -------------------
03.02.2022 12:24:12 02.02.2022 12:24:12 01.02.2022 12:24:12

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

Template for RMAN script, including Netbackup directives

Executed as user oracle directly on the server:

connect target /
connect catalog rmancat/password@RMANCAT
run {
allocate channel t1 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
allocate channel t2 type sbt PARMS="BLKSIZE=1048576"  maxopenfiles 64;
send 'NB_ORA_POLICY=my_policy_name,NB_ORA_CLIENT=my_client_name-bkp.mydomain.com,NB_ORA_SCHED=my_schedule_name';

rman commands here


release channel t1;
release channel t2;
}

to execute:

$ rman cmdfile='restore_archlog.cmd' log='restore_archlog.log'

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

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

How to restore archive logs to a new destination

This example restores all archived redo logs to the /oracle/temp_restore directory:

RMAN> RUN
{ 
  SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore/'; <-- note the last /
  RESTORE ARCHIVELOG ALL;
}

If the file is already on disk you will get an error message from RMAN.

To override this, use the force option:

RMAN> run {
2> allocate channel t1 device type 'sbt';
3> set archivelog destination to '/oracle/temp_restore/';
4> restore archivelog logseq 15572 force;
5> }

How is the database time zone set?

Answer: it is set at creation time.
If not explicitly defined by the DBA, it will use the time zone of the server's operating system.

If you want to set it explicitly, do so in the CREATE DATABASE statement:

CREATE DATABASE PRODDB
.
.
SET TIME_ZONE='-05:00';

Or set it to a named region, like this:

CREATE DATABASE PRODDB
.
.
SET TIME_ZONE='Europe/Zurich';

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns.

You can change the database time zone by using the SET TIME_ZONE clause of the ALTER DATABASE statement:

ALTER DATABASE SET TIME_ZONE='05:00';
ALTER DATABASE SET TIME_ZONE='Europe/Zurich';

The ALTER DATABASE SET TIME_ZONE statement will return an error if the database contains a table using a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data. You will also have to restart the database.


To see the current time zone of the database:
SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;

DBTIMEZONE	SESSIONTIMEZONE
+01:00	Europe/Zurich

For a database used globally, it may be beneficial to set the database time to UTC (0:00) regardless of where it is physically hosted.

Source: Oracle Documentation