Tuesday, February 20, 2018

Understanding Archive Log Destination

Change the location of archive logs 


 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     68
Next log sequence to archive   70
Current log sequence           70
SQL>
SQL>
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

 FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      29.62                     27.51              51
BACKUP PIECE                      31.04                         0               2
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

 7 rows selected.

 SQL>
SQL> show parameter db_reco

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
SQL>show parameter dest

 db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
diagnostic_dest                      string      /u01/app/oracle
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string

 SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/backup/archive_log1' scope=both;

 System altered.

 SQL> show parameter log_archive_dest_1

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=/u01/app/oracle/backu
                                                 p/archive_log1
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string
log_archive_dest_19                  string
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/backup/archive_log1
Oldest online log sequence     68
Next log sequence to archive   70
Current log sequence           70
SQL>
[oracle@localhost archive_log1]$
[oracle@localhost archive_log1]$
[oracle@localhost archive_log1]$ ls -lrt /u01/app/oracle/backup/archive_log1
total 2728
-rw-r----- 1 oracle oinstall 2790400 Feb 20 10:25 1_70_966712547.dbf
[oracle@localhost archive_log1]$
[oracle@localhost archive_log1]$ ls -lrt /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_20
total 41728
-rw-r----- 1 oracle oinstall 40453120 Feb 20 00:06 o1_mf_1_68_f8p6b7h9_.arc
-rw-r----- 1 oracle oinstall  2271232 Feb 20 09:55 o1_mf_1_69_f8q8soh1_.arc
[oracle@localhost archive_log1]$

 SQL> show parameter log_archive_dest_2

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL> alter system set log_archive_dest_2='location=/u01/app/oracle/backup/archive_log2' scope=both;

 System altered.

 SQL> show parameter log_archive_dest_2

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      location=/u01/app/oracle/backup/archive_log2
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string
log_archive_dest_29                  string
SQL>

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/backup/archive_log2
Oldest online log sequence     70
Next log sequence to archive   72
Current log sequence           72
SQL> alter system switch logfile;

 System altered.

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/backup/archive_log2
Oldest online log sequence     71
Next log sequence to archive   73
Current log sequence           73
SQL>
SQL> select * from v$log;

     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         73   52428800        512          1 NO  CURRENT                2198499 20-FEB-18   2.8147E+14
         2          1         71   52428800        512          1 YES INACTIVE               2198050 20-FEB-18      2198247 20-FEB-18
         3          1         72   52428800        512          1 YES ACTIVE                 2198247 20-FEB-18      2198499 20-FEB-18

 SQL>

[oracle@localhost archive_log2]$  ls -lrt /u01/app/oracle/backup/archive_log1
total 2956
-rw-r----- 1 oracle oinstall 2790400 Feb 20 10:25 1_70_966712547.dbf
-rw-r----- 1 oracle oinstall  167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall   61952 Feb 20 10:38 1_72_966712547.dbf
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -lrt
total 228
-rw-r----- 1 oracle oinstall 167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall  61952 Feb 20 10:38 1_72_966712547.dbf

[oracle@localhost archive_log2]$

Set archive log destination to FRA
-----------------------------------------

 SQL> select name,
round(space_limit / 1048576) space_limit_in_mb,
round(space_used / 1048576) space_used_in_mb,
round((space_used / 1048576) / (space_limit / 1048576),2)*100 percent_usage
from v$recovery_file_dest;  

 NAME                                               SPACE_LIMIT_IN_MB SPACE_USED_IN_MB PERCENT_USAGE
-------------------------------------------------- ----------------- ---------------- -------------
/u01/app/oracle/fast_recovery_area                              4182             2537            61

 SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

 System altered.

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     71
Next log sequence to archive   73
Current log sequence           73
SQL>
SQL> alter system switch logfile;

 System altered.

 SQL>

 SQL> show parameter RECOVERY_FILE_DEST

 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M
SQL>



[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -l /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_20
total 41728
-rw-r----- 1 oracle oinstall 40453120 Feb 20 00:06 o1_mf_1_68_f8p6b7h9_.arc
-rw-r----- 1 oracle oinstall  2271232 Feb 20 09:55 o1_mf_1_69_f8q8soh1_.arc
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -l /u01/app/oracle/fast_recovery_area/DUCAT/archivelog/2018_02_20
total 42048
-rw-r----- 1 oracle oinstall 40453120 Feb 20 00:06 o1_mf_1_68_f8p6b7h9_.arc
-rw-r----- 1 oracle oinstall  2271232 Feb 20 09:55 o1_mf_1_69_f8q8soh1_.arc
-rw-r----- 1 oracle oinstall   325632 Feb 20 10:50 o1_mf_1_73_f8qd1mhl_.arc
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -lrt /u01/app/oracle/backup/archive_log1
total 2956
-rw-r----- 1 oracle oinstall 2790400 Feb 20 10:25 1_70_966712547.dbf
-rw-r----- 1 oracle oinstall  167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall   61952 Feb 20 10:38 1_72_966712547.dbf
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$ ls -lrt /u01/app/oracle/backup/archive_log2
total 548
-rw-r----- 1 oracle oinstall 167936 Feb 20 10:29 1_71_966712547.dbf
-rw-r----- 1 oracle oinstall  61952 Feb 20 10:38 1_72_966712547.dbf
-rw-r----- 1 oracle oinstall 325632 Feb 20 10:50 1_73_966712547.dbf
[oracle@localhost archive_log2]$
[oracle@localhost archive_log2]$

By -
Labels: ,

No comments:

Post a Comment

About the Author : Anurag Pandey | Database cloud Architect | Oracle | SQL Server | PostgreSQL | AI and Data Platform Enthusiast

Anurag Pandey is a Database Architect with expertise in Oracle, PostgreSQL, SQL Server, Linux, and enterprise database platforms and specializes in database architecture, performance tuning, high availability, disaster recovery, cloud modernization, and operational excellence. With extensive experience in enterprise-scale environments, Anurag is passionate about leveraging AI, automation, and modern data platforms to improve reliability, scalability, and business outcomes. He actively contributes to technical communities through knowledge sharing, architecture guidance, and thought leadership in database and data engineering domains.

Follow more articles at

Visit Blog

Subscribe to: Post Comments (Atom)

Oracle Database Deep Health Check Checklist

Oracle Database Deep Health Check Checklist Recommended assessment levels Level Purpose Typical scope L1 Operational health Availability, sp...