Oracle Database Enable Archivelog Mode and Archivelog Deletion and Generation Frequency

On the other hand, oracle database must be in Archivelog mode so that we can take Full backup in online mode. If Oracle database that is in NoArchivelog mode, RMAN cannot receive Online Backup.

The databases in the production environment must be in Archivelog mode and generally they are in Archivelog mode. You can check following query if it is in archivelog or not.

[oracle@MehmetSalih ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 19 17:19:13 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>

A database that is not in Archivelog mode is taken into Archivelog mode with the following steps.

Firstly, the database is closed consistently.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Secondly. After the database is closed consistently, the database opens in mount mode.

SQL> startup mount
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2230072 bytes
Variable Size             339740872 bytes
Database Buffers          184549376 bytes
Redo Buffers                7942144 bytes
Database mounted.
SQL>

3. The database in the Mount mode is make it into the archive mode as follows.

SQL> alter database archivelog;
Database altered.

4. After the database is in archive mode, the Database is opened as follows.

SQL> alter database open;
Database altered.

5. If we do not specify the location of the archives, it is stored default location of db_recovery_file_dest firstly. The first location where the database archives stores is the location of the log_archive_dest_1 parameter. We can adjust this value as follows.

SQL> alter system set log_archive_dest_1='location=/oracle/oradata/ARCH';

System altered.

You can query and list the Log Switch ( Archivelog ) Frequency map according to per hour and daily as follows.

select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
from v$log_history group by to_char(first_time,'YYYY-MON-DD');

Query result is as follows.

You can query all Archivelogs using the following command.

[MSD1]/home/oracle $ rman target /

Recovery Manager: Release 18.0.0.0.0 - Production on Mon Mar 9 14:41:02 2020
Version 18.7.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MSD (DBID=2054866743)

RMAN> list archivelog all;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name MSD
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - -------------------
3643932 1    456786  A 09-03-2020 10:52:05
        Name: /zfssa/MSDDB/ARCH2/arch_D-MSD_id-2054866743_S-456786_T-1_A-2054879031_dguqleac

3643134 1    456786  A 09-03-2020 10:52:05
        Name: +RECO/MSD/ARCHIVELOG/2020_03_09/thread_1_seq_456786.10371.1034592773
3645414 8    262083  A 09-03-2020 14:37:15
        Name: +RECO/MSD/ARCHIVELOG/2020_03_09/thread_8_seq_262083.28235.1034606329

3645424 8    262084  A 09-03-2020 14:38:48
        Name: +RECO/MSD/ARCHIVELOG/2020_03_09/thread_8_seq_262084.56375.1034606419
RMAN>

If you want to learn how to delete Archivelog and how Archivelog deletion policy works, read the following article to learn it.

https://ittutorial.org/oracle-archivelog-delete/

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/