1. log archive destination full, what to do?
2. Unable to switch / generate the archive log due to log archive destination full
3. Database is hung due to log archive destination full
Solution1: Delete the archive logs:
Solution2: Increase the archive log destination
Solution3: Change the archive log destination
Solution4: Move archive logs from FRA to temporary location
Solution1: Delete the archive logs:
SQL> archive log list
SQL> show parameter db_recovery_file_dest
SQL>
set lines 1000 pages 1000
col DEST_NAME for a20
col STATUS for a15
col DESTINATION for a30
select dest_name, status, destination from v$archive_dest;
run {
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;
delete noprompt expired backup;
}
Solution2: Increase the archive log destination
v$recovery_area_usage
select * from v$recovery_area_usage;
v$recovery_file_dest
set lines 1000 pages 1000
col NAME for a50
select * from v$recovery_file_dest;
select SPACE_USED/1024/1204/1024 from v$recovery_file_dest;
select SPACE_USED/1024/1204/1024 from dual;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 1G SCOPE=BOTH SID=’*’;
Solution3: Change the archive log destination
SQL> archive log list
SQL> show parameter db_recovery_file_dest
SQL>
set lines 1000 pages 1000
col DEST_NAME for a20
col STATUS for a15
col DESTINATION for a30
select dest_name, status, destination from v$archive_dest;
show parameter log_archive_format
ALTER SYSTEM SET log_archive_dest_1='location=/u01/backup' scope=both;
Solution4: Move archive logs from FRA to temporary location
cd $FRA
mv * /u01/backup/.
Note:
alter system set db_recovery_file_dest_size=100M scope=both;
ALTER SYSTEM SET log_archive_dest='location=USE_DB_RECOVERY_FILE_DEST' scope=both;
Logs:
[oracle@oraclelab1 DEVDB]$ env |grep ORA
ORACLE_SID=DEVDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 DEVDB]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 16 00:34:26 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 56
Next log sequence to archive 56
Current log sequence 58
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 100M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL>
SQL>
SQL>
SQL>
SQL> set lines 1000 pages 1000
col DEST_NAME for a20
col STATUS for a15
col DESTINATION for a30
select dest_name, status, destination from v$archive_dest;
SQL> SQL> SQL> SQL>
DEST_NAME STATUS DESTINATION
-------------------- --------------- ------------------------------
LOG_ARCHIVE_DEST_1 ERROR USE_DB_RECOVERY_FILE_DEST
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
LOG_ARCHIVE_DEST_11 INACTIVE
LOG_ARCHIVE_DEST_12 INACTIVE
LOG_ARCHIVE_DEST_13 INACTIVE
LOG_ARCHIVE_DEST_14 INACTIVE
LOG_ARCHIVE_DEST_15 INACTIVE
LOG_ARCHIVE_DEST_16 INACTIVE
LOG_ARCHIVE_DEST_17 INACTIVE
LOG_ARCHIVE_DEST_18 INACTIVE
LOG_ARCHIVE_DEST_19 INACTIVE
LOG_ARCHIVE_DEST_20 INACTIVE
LOG_ARCHIVE_DEST_21 INACTIVE
LOG_ARCHIVE_DEST_22 INACTIVE
LOG_ARCHIVE_DEST_23 INACTIVE
LOG_ARCHIVE_DEST_24 INACTIVE
LOG_ARCHIVE_DEST_25 INACTIVE
LOG_ARCHIVE_DEST_26 INACTIVE
LOG_ARCHIVE_DEST_27 INACTIVE
LOG_ARCHIVE_DEST_28 INACTIVE
LOG_ARCHIVE_DEST_29 INACTIVE
LOG_ARCHIVE_DEST_30 INACTIVE
LOG_ARCHIVE_DEST_31 INACTIVE
31 rows selected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab1 DEVDB]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 16 00:38:22 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEVDB (DBID=996518900)
RMAN> run {
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;
delete noprompt expired backup;
}
2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_43_1091788918.dbf RECID=39 STAMP=1093857297
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_44_1091788918.dbf RECID=40 STAMP=1093857320
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_45_1091788918.dbf RECID=41 STAMP=1093909144
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_46_1091788918.dbf RECID=42 STAMP=1093909162
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_47_1091788918.dbf RECID=43 STAMP=1093909826
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_48_1091788918.dbf RECID=44 STAMP=1093909844
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_49_1091788918.dbf RECID=45 STAMP=1093910225
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_50_1091788918.dbf RECID=46 STAMP=1093910235
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_51_1091788918.dbf RECID=47 STAMP=1093998660
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_52_1091788918.dbf RECID=48 STAMP=1094056261
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_53_1091788918.dbf RECID=49 STAMP=1094084037
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_54_1091788918.dbf RECID=50 STAMP=1094084112
validation succeeded for archived log
archived log file name=/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/db_recovery_file_dest1_55_1091788918.dbf RECID=51 STAMP=1094084130
Crosschecked 13 objects
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20220113_0n0j5s0h_1_1 RECID=23 STAMP=1093857297
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20220113_0o0j5s0p_1_1 RECID=24 STAMP=1093857305
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20220113_0p0j5s18_1_1 RECID=25 STAMP=1093857320
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20220113_0q0j5s19_1_1 RECID=26 STAMP=1093857322
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093857323_jxz82mpp_.bkp RECID=27 STAMP=1093857323
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20220113_0s0j7eko_1_1 RECID=28 STAMP=1093909144
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Fullback_20220113_0t0j7ekr_1_1 RECID=29 STAMP=1093909147
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Archive_20220113_0u0j7ela_1_1 RECID=30 STAMP=1093909162
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/Controlback_20220113_0v0j7elc_1_1 RECID=31 STAMP=1093909165
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093909166_jy0tppbv_.bkp RECID=32 STAMP=1093909166
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/1/Archive_20220113_110j7fa2_1_1 RECID=33 STAMP=1093909826
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/1/Fullback_20220113_120j7fa5_1_1 RECID=34 STAMP=1093909829
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/1/Archive_20220113_130j7fak_1_1 RECID=35 STAMP=1093909844
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/1/Controlback_20220113_140j7fam_1_1 RECID=36 STAMP=1093909847
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093909848_jy0vd0bj_.bkp RECID=37 STAMP=1093909848
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/2/Archive_20220113_160j7fmh_1_1 RECID=38 STAMP=1093910225
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/2/Fullback_20220113_170j7fmk_1_1 RECID=39 STAMP=1093910228
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/2/Archive_20220113_180j7fmr_1_1 RECID=40 STAMP=1093910235
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/backup/2/Controlback_20220113_190j7fms_1_1 RECID=41 STAMP=1093910237
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093910239_jy0vr73v_.bkp RECID=42 STAMP=1093910239
Crosschecked 20 objects
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 26 13-JAN-22
Backup Piece 26 13-JAN-22 /u01/backup/Controlback_20220113_0q0j5s19_1_1
Backup Set 27 13-JAN-22
Backup Piece 27 13-JAN-22 /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093857323_jxz82mpp_.bkp
Backup Set 31 13-JAN-22
Backup Piece 31 13-JAN-22 /u01/backup/Controlback_20220113_0v0j7elc_1_1
Backup Set 32 13-JAN-22
Backup Piece 32 13-JAN-22 /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093909166_jy0tppbv_.bkp
Backup Set 36 13-JAN-22
Backup Piece 36 13-JAN-22 /u01/backup/1/Controlback_20220113_140j7fam_1_1
Backup Set 37 13-JAN-22
Backup Piece 37 13-JAN-22 /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093909848_jy0vd0bj_.bkp
Backup Set 41 13-JAN-22
Backup Piece 41 13-JAN-22 /u01/backup/2/Controlback_20220113_190j7fms_1_1
deleted backup piece
backup piece handle=/u01/backup/Controlback_20220113_0q0j5s19_1_1 RECID=26 STAMP=1093857322
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093857323_jxz82mpp_.bkp RECID=27 STAMP=1093857323
deleted backup piece
backup piece handle=/u01/backup/Controlback_20220113_0v0j7elc_1_1 RECID=31 STAMP=1093909165
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093909166_jy0tppbv_.bkp RECID=32 STAMP=1093909166
deleted backup piece
backup piece handle=/u01/backup/1/Controlback_20220113_140j7fam_1_1 RECID=36 STAMP=1093909847
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_01_13/o1_mf_s_1093909848_jy0vd0bj_.bkp RECID=37 STAMP=1093909848
deleted backup piece
backup piece handle=/u01/backup/2/Controlback_20220113_190j7fms_1_1 RECID=41 STAMP=1093910237
Deleted 7 objects
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
specification does not match any archived log in the repository
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
23 23 1 1 EXPIRED DISK /u01/backup/Archive_20220113_0n0j5s0h_1_1
24 24 1 1 EXPIRED DISK /u01/backup/Fullback_20220113_0o0j5s0p_1_1
25 25 1 1 EXPIRED DISK /u01/backup/Archive_20220113_0p0j5s18_1_1
28 28 1 1 EXPIRED DISK /u01/backup/Archive_20220113_0s0j7eko_1_1
29 29 1 1 EXPIRED DISK /u01/backup/Fullback_20220113_0t0j7ekr_1_1
30 30 1 1 EXPIRED DISK /u01/backup/Archive_20220113_0u0j7ela_1_1
33 33 1 1 EXPIRED DISK /u01/backup/1/Archive_20220113_110j7fa2_1_1
34 34 1 1 EXPIRED DISK /u01/backup/1/Fullback_20220113_120j7fa5_1_1
35 35 1 1 EXPIRED DISK /u01/backup/1/Archive_20220113_130j7fak_1_1
38 38 1 1 EXPIRED DISK /u01/backup/2/Archive_20220113_160j7fmh_1_1
39 39 1 1 EXPIRED DISK /u01/backup/2/Fullback_20220113_170j7fmk_1_1
40 40 1 1 EXPIRED DISK /u01/backup/2/Archive_20220113_180j7fmr_1_1
deleted backup piece
backup piece handle=/u01/backup/Archive_20220113_0n0j5s0h_1_1 RECID=23 STAMP=1093857297
deleted backup piece
backup piece handle=/u01/backup/Fullback_20220113_0o0j5s0p_1_1 RECID=24 STAMP=1093857305
deleted backup piece
backup piece handle=/u01/backup/Archive_20220113_0p0j5s18_1_1 RECID=25 STAMP=1093857320
deleted backup piece
backup piece handle=/u01/backup/Archive_20220113_0s0j7eko_1_1 RECID=28 STAMP=1093909144
deleted backup piece
backup piece handle=/u01/backup/Fullback_20220113_0t0j7ekr_1_1 RECID=29 STAMP=1093909147
deleted backup piece
backup piece handle=/u01/backup/Archive_20220113_0u0j7ela_1_1 RECID=30 STAMP=1093909162
deleted backup piece
backup piece handle=/u01/backup/1/Archive_20220113_110j7fa2_1_1 RECID=33 STAMP=1093909826
deleted backup piece
backup piece handle=/u01/backup/1/Fullback_20220113_120j7fa5_1_1 RECID=34 STAMP=1093909829
deleted backup piece
backup piece handle=/u01/backup/1/Archive_20220113_130j7fak_1_1 RECID=35 STAMP=1093909844
deleted backup piece
backup piece handle=/u01/backup/2/Archive_20220113_160j7fmh_1_1 RECID=38 STAMP=1093910225
deleted backup piece
backup piece handle=/u01/backup/2/Fullback_20220113_170j7fmk_1_1 RECID=39 STAMP=1093910228
deleted backup piece
backup piece handle=/u01/backup/2/Archive_20220113_180j7fmr_1_1 RECID=40 STAMP=1093910235
Deleted 12 EXPIRED objects
RMAN>
[oracle@oraclelab1 DEVDB]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 16 00:39:46 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> set lines 1000 pages 1000
SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 10.09 0 1 0
REDO LOG 600 0 3 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 10.2 0 1 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL> set lines 1000 pages 1000
col NAME for a50
select * from v$recovery_file_dest;
select SPACE_USED/1024/1204/1024 from v$recovery_file_dest;
SQL> SQL>
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
-------------------------------------------------- ----------- ---------- ----------------- --------------- ----------
/u01/app/oracle/fast_recovery_area 104857600 651476992 0 5 0
SQL>
SPACE_USED/1024/1204/1024
-------------------------
.516027305
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 650M SCOPE=BOTH SID='*';
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 650M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 56
Next log sequence to archive 56
Current log sequence 58
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 650M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 1G SCOPE=BOTH SID='*';
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
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 58
Next log sequence to archive 60
Current log sequence 60
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 60
Next log sequence to archive 62
Current log sequence 62
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
SQL> set lines 1000 pages 1000
col DEST_NAME for a20
col STATUS for a15
col DESTINATION for a30
select dest_name, status, destination from v$archive_dest;
SQL> SQL> SQL> SQL>
DEST_NAME STATUS DESTINATION
-------------------- --------------- ------------------------------
LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
LOG_ARCHIVE_DEST_11 INACTIVE
LOG_ARCHIVE_DEST_12 INACTIVE
LOG_ARCHIVE_DEST_13 INACTIVE
LOG_ARCHIVE_DEST_14 INACTIVE
LOG_ARCHIVE_DEST_15 INACTIVE
LOG_ARCHIVE_DEST_16 INACTIVE
LOG_ARCHIVE_DEST_17 INACTIVE
LOG_ARCHIVE_DEST_18 INACTIVE
LOG_ARCHIVE_DEST_19 INACTIVE
LOG_ARCHIVE_DEST_20 INACTIVE
LOG_ARCHIVE_DEST_21 INACTIVE
LOG_ARCHIVE_DEST_22 INACTIVE
LOG_ARCHIVE_DEST_23 INACTIVE
LOG_ARCHIVE_DEST_24 INACTIVE
LOG_ARCHIVE_DEST_25 INACTIVE
LOG_ARCHIVE_DEST_26 INACTIVE
LOG_ARCHIVE_DEST_27 INACTIVE
LOG_ARCHIVE_DEST_28 INACTIVE
LOG_ARCHIVE_DEST_29 INACTIVE
LOG_ARCHIVE_DEST_30 INACTIVE
LOG_ARCHIVE_DEST_31 INACTIVE
31 rows selected.
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/backup' scope=both;
System altered.
SQL> select dest_name, status, destination from v$archive_dest;
DEST_NAME STATUS DESTINATION
-------------------- --------------- ------------------------------
LOG_ARCHIVE_DEST_1 VALID /u01/backup
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
LOG_ARCHIVE_DEST_11 INACTIVE
LOG_ARCHIVE_DEST_12 INACTIVE
LOG_ARCHIVE_DEST_13 INACTIVE
LOG_ARCHIVE_DEST_14 INACTIVE
LOG_ARCHIVE_DEST_15 INACTIVE
LOG_ARCHIVE_DEST_16 INACTIVE
LOG_ARCHIVE_DEST_17 INACTIVE
LOG_ARCHIVE_DEST_18 INACTIVE
LOG_ARCHIVE_DEST_19 INACTIVE
LOG_ARCHIVE_DEST_20 INACTIVE
LOG_ARCHIVE_DEST_21 INACTIVE
LOG_ARCHIVE_DEST_22 INACTIVE
LOG_ARCHIVE_DEST_23 INACTIVE
LOG_ARCHIVE_DEST_24 INACTIVE
LOG_ARCHIVE_DEST_25 INACTIVE
LOG_ARCHIVE_DEST_26 INACTIVE
LOG_ARCHIVE_DEST_27 INACTIVE
LOG_ARCHIVE_DEST_28 INACTIVE
LOG_ARCHIVE_DEST_29 INACTIVE
LOG_ARCHIVE_DEST_30 INACTIVE
LOG_ARCHIVE_DEST_31 INACTIVE
31 rows selected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/backup
Oldest online log sequence 60
Next log sequence to archive 62
Current log sequence 62
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 1G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system switch logfile;
System altered.
SQL>
SQL> alter system reset LOG_ARCHIVE_DEST_1 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select dest_name, status, destination from v$archive_dest;
DEST_NAME STATUS DESTINATION
-------------------- --------------- ------------------------------
LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST
LOG_ARCHIVE_DEST_2 INACTIVE
LOG_ARCHIVE_DEST_3 INACTIVE
LOG_ARCHIVE_DEST_4 INACTIVE
LOG_ARCHIVE_DEST_5 INACTIVE
LOG_ARCHIVE_DEST_6 INACTIVE
LOG_ARCHIVE_DEST_7 INACTIVE
LOG_ARCHIVE_DEST_8 INACTIVE
LOG_ARCHIVE_DEST_9 INACTIVE
LOG_ARCHIVE_DEST_10 INACTIVE
LOG_ARCHIVE_DEST_11 INACTIVE
LOG_ARCHIVE_DEST_12 INACTIVE
LOG_ARCHIVE_DEST_13 INACTIVE
LOG_ARCHIVE_DEST_14 INACTIVE
LOG_ARCHIVE_DEST_15 INACTIVE
LOG_ARCHIVE_DEST_16 INACTIVE
LOG_ARCHIVE_DEST_17 INACTIVE
LOG_ARCHIVE_DEST_18 INACTIVE
LOG_ARCHIVE_DEST_19 INACTIVE
LOG_ARCHIVE_DEST_20 INACTIVE
LOG_ARCHIVE_DEST_21 INACTIVE
LOG_ARCHIVE_DEST_22 INACTIVE
LOG_ARCHIVE_DEST_23 INACTIVE
LOG_ARCHIVE_DEST_24 INACTIVE
LOG_ARCHIVE_DEST_25 INACTIVE
LOG_ARCHIVE_DEST_26 INACTIVE
LOG_ARCHIVE_DEST_27 INACTIVE
LOG_ARCHIVE_DEST_28 INACTIVE
LOG_ARCHIVE_DEST_29 INACTIVE
LOG_ARCHIVE_DEST_30 INACTIVE
LOG_ARCHIVE_DEST_31 INACTIVE
31 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 700M scope=both sid='*';
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 700M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system switch logfile;
System altered.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 650M scope=both sid='*';
System altered.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 650M
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 625M scope=both sid='*';
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100M scope=both sid='*';
System altered.
SQL> alter system switch logfile;
^Calter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 8G scope=both sid='*';
System altered.
SQL>
Regards,
Mallik
thanks
ReplyDelete