Lets explore an oracle feature and test it today.
What is alternate archive log location and how to test alternate archive log location feature?
Basically we set archivelog location in log_archive_dest_1 parameter but what if that location is full or due to some network issue archiver failed to write there.All your transaction will hang until the issue is resolved.Oracle has given a feature to set an alternate archivelog location in that scenario so that archiver can continue writing there and we can prevent database hang issue.
To do that we shall create a backup table considerable large and decrease FRA size relatively small so that archiver error situation is created
Prepation:
--Geting size of the table
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments where segment_name='CI_CC';
SUM(BYTES)/(1024*1024*1024)
---------------------------
25.875
--Current FRA size
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 300G
--Current FRA usage
SQL>select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 4.8 0 2 0
BACKUP PIECE .01 0 1 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 1.74 0 3 0
AUXILIARY DATAFILE COPY 0 0 0 0
--Current setting of archive log locatiomn
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST VALID_FOR=(ALL_LOGFILES,A
LL_ROLES) MAX_FAILURE=1 REOPEN
=5 DB_UNIQUE_NAME=ccbx ALTERNA
TE=log_archive_dest_10
log_archive_dest_10 string LOCATION=+DATA/ccbx/arc10 VALI
D_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ccbx ALTERNATE
=log_archive_dest_1
Emphasize altenate highlighted in bold.In case dest1 is not available archiver will attempt to write in dest10.Once dest1 is availble archiver will start writing back in dest1.
Existence of ASM path for log_archive_dest_10:-
Lets check whether asm path +DATA/ccbx/arc10 is alreday created or not.To do that login as grid user and execute below
asmcmd ls -l +DATA/ccbx/arc10
If this command does not return any error which means path is existing and valid.
Plan in action:-
Now we will reduce FRA to 30G so that FRA gets filled up while creating backup table.
SQL> alter system set db_recovery_file_dest_size=30G;
System altered.
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 30G
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 47.99 0 2 0
BACKUP PIECE .05 0 1 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 17.36 0 3 0
AUXILIARY DATAFILE COPY 0 0 0 0
Now we will create a table like ci_cc_bkp copying all data from cisadm.ci_cc.
create table cisadm.ci_cc_bkp tablespace cists_01 as (select * from cisadm.ci_cc);
After some time I see archiver error in database alert log and as expected it has started created archvivelog in alternate location.LAD stands for log archive destination.
We can see that it has started writing in LAD10
-----Snippet of alert log-------------------------------------------------------------
2024-06-13T02:27:12.327483-07:00
ORACLE Instance ccbx, archival error, archiver continuing
2024-06-13T02:27:18.817944-07:00
ARC3 (PID:20343): Archived Log entry 4 added for T-1.S-4 ID 0x8563c335 LAD:10
---------------------------------------------------------------------------------------
When I checked in ASM path I can see archivelog in alternate location
asmcmd ls -l +DATA/ccbx/arc10
Type Redund Striped Time Sys Name
ARCHIVELOG MIRROR COARSE JUN 13 02:00:00 N 1_4_1170387009.dbf => +DATA/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_4.572.1171506433
ARCHIVELOG MIRROR COARSE JUN 13 02:00:00 N 1_5_1170387009.dbf => +DATA/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_5.573.1171506493
ARCHIVELOG MIRROR COARSE JUN 13 02:00:00 N 1_6_1170387009.dbf => +DATA/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_6.576.1171506553
ARCHIVELOG MIRROR COARSE JUN 13 02:00:00 N 1_7_1170387009.dbf => +DATA/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_7.577.1171506609
ARCHIVELOG MIRROR COARSE JUN 13 02:00:00 N 1_8_1170387009.dbf => +DATA/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_8.578.1171506663
Now I have reverted back FRA to 300G and saw below status
select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
DEST_NAME TARGET DESTINATION VALID_ROLE STATUS ALTERNATE
------------------------------ ---------------- ------------------------------ ------------ --------- ------------------------------
LOG_ARCHIVE_DEST_1 PRIMARY USE_DB_RECOVERY_FILE_DEST ALL_ROLES VALID LOG_ARCHIVE_DEST_10
LOG_ARCHIVE_DEST_10 PRIMARY +DATA/ccbx/arc10 ALL_ROLES ALTERNATE LOG_ARCHIVE_DEST_1
Also,When I checked archievlog file creation status then I see latest archivelog is now generating in log_archive_dest_1
SQL> select name,archived,completion_time from v$archived_log;
NAME ARC COMPLETION_TIME
-------------------------------------------------------------------------------- --- --------------------
+RECO/CCBX/ARCHIVELOG/2024_05_31/thread_1_seq_1.609.1170387619 YES 31-MAY-2024 03:40:25
+RECO/CCBX/ARCHIVELOG/2024_06_07/thread_1_seq_2.613.1170992223 YES 07-JUN-2024 03:37:09
+RECO/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_3.598.1171506375 YES 13-JUN-2024 02:26:21
+DATA/ccbx/arc10/1_4_1170387009.dbf YES 13-JUN-2024 02:27:18
+DATA/ccbx/arc10/1_5_1170387009.dbf YES 13-JUN-2024 02:28:18
+DATA/ccbx/arc10/1_6_1170387009.dbf YES 13-JUN-2024 02:29:18
+DATA/ccbx/arc10/1_7_1170387009.dbf YES 13-JUN-2024 02:30:15
+DATA/ccbx/arc10/1_8_1170387009.dbf YES 13-JUN-2024 02:31:09
+DATA/ccbx/arc10/1_9_1170387009.dbf YES 13-JUN-2024 02:32:00
+DATA/ccbx/arc10/1_10_1170387009.dbf YES 13-JUN-2024 02:32:48
+DATA/ccbx/arc10/1_11_1170387009.dbf YES 13-JUN-2024 02:33:33
+DATA/ccbx/arc10/1_12_1170387009.dbf YES 13-JUN-2024 02:34:21
+DATA/ccbx/arc10/1_13_1170387009.dbf YES 13-JUN-2024 02:35:09
+RECO/CCBX/ARCHIVELOG/2024_06_13/thread_1_seq_14.592.1171507699 YES 13-JUN-2024 02:48:22
This concludes failover and failback is automatic and archive log location setting is as expected.
Suggestion:Once dest1 is reachable do not try to change log_archive_dest_state_1 to enable manually.
You will end up enabling two valid archievlog location in that case.
