{"id":7233,"date":"2021-05-14T19:32:38","date_gmt":"2021-05-14T19:32:38","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/alternate-archive-destinations-in-oracle\/"},"modified":"2026-11-03T19:28:16","modified_gmt":"2026-11-03T19:28:16","slug":"alternate-archive-destinations-in-oracle","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/alternate-archive-destinations-in-oracle\/","title":{"rendered":"Alternate archive destinations in Oracle"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

Archiving Oracle databases lets you retain and store data for the long term, but what happens if you run out of space?<\/p>\n

If the archive destination fills up, your Oracle Database can hang or become stuck, and you might see one of the following errors:
\n <\/p>\n

– **0RA-00257**: Archiver error, connect internal only until freed
\n– **ORA-16014**: Log 2 sequence# 1934 not archived, no available destinations<\/em><\/p>\n

In this case, you need to have an alternate archiving destination to use when the original archive destination fails. You can leverage the alternate archive destination in the event of any failure (space, hardware, and so on) in the primary archive destination.<\/p>\n

Let’s go through a quick demonstration to understand how it works.<\/p>\n

Demo part one<\/strong><\/p>\n

First, I set the primary archive destination to \/u03\/primary_dest location<\/strong>, which is currently 99% utilized. You can do the same thing as a test.<\/p>\n

Then, run the following commands:<\/p>\n

    SQL> select version from v$instance;<\/em><\/p>\n

    VERSION
\n    —————–
\n    12.1.0.2.0<\/p>\n

    SQL> select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS 
\n    from v$archive_dest where status!=’INACTIVE’;<\/em><\/p>\n

    DEST_NAME             TARGET     DESTINATION          VALID_ROLE   STATUS
\n    ——————— ———- ——————– ———— ———
\n    LOG_ARCHIVE_DEST_1    PRIMARY    \/u03\/primary_dest     ALL_ROLES    VALID<\/em><\/p>\n

 <\/p>\n

Demo part two<\/strong><\/p>\n

Let’s add an alternate archive destination **\/u04\/alternate_dest**, which the system can use if the primary destination is unavailable.<\/p>\n

    SQL> !df -h \/u04\/alternate_dest
\n    Filesystem            Size  Used Avail Use% Mounted on
\n    \/dev\/ddg2            1004M   18M 
\n    936M   2% \/u04<\/em><\/p>\n

    SQL> alter system set log_archive_dest_2=’LOCATION=\/u04\/alternate_dest’ scope=both;<\/em><\/p>\n

    System altered.<\/p>\n

    SQL> alter system set log_archive_dest_state_2=ALTERNATE scope=both;<\/em><\/p>\n

    System altered.<\/p>\n

    SQL> select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!=’INACTIVE’;<\/em><\/p>\n

    DEST_NAME           TARGET      DESTINATION         VALID_ROLE   STATUS      ALTERNATE
\n    ——————- ———– ——————- ———— ———– ———
\n    LOG_ARCHIVE_DEST_1   PRIMARY    \/u03\/primary_dest    ALL_ROLES    VALID      NONE
\n    LOG_ARCHIVE_DEST_2   PRIMARY    \/u04\/alternate_dest  ALL_ROLES    ALTERNATE  NONE<\/em>
\n <\/p>\n

Demo part three<\/strong><\/p>\n

You can link the primary and alternate archive destination, and Oracle can fail over to an alternate archive location when the primary location reaches 100% usage. However, Oracle cannot execute a failback to the primary destination after it is available again. For that to work, you need to set the primary archive destination,
\nLOG_ARCHIVE_DEST_1<\/strong>, as the ALTERNATE<\/strong> for the alternate archive destination,
\nLOG_ARCHIVE_DEST_2<\/strong>, as shown in the following example:<\/p>\n

  SQL> alter system set log_archive_dest_2=’LOCATION=\/u04\/alternate_dest 
\n    NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_1′ scope=both;<\/em><\/p>\n

    System altered.<\/p>\n

    SQL> alter system set log_archive_dest_1=’LOCATION=\/u03\/primary_dest
\n    NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2′ scope=both;<\/em><\/p>\n

    System altered.<\/p>\n

    SQL> select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE
\n    from v$archive_dest where status!=’INACTIVE’;<\/em><\/p>\n

    DEST_NAME            TARGET      DESTINATION          VALID_ROLE   STATUS     ALTERNATE
\n    ——————– ———– ——————– ———— ———- ——————
\n    LOG_ARCHIVE_DEST_1   PRIMARY     \/u03\/primary_dest     ALL_ROLES    VALID     LOG_ARCHIVE_DEST_2
\n    LOG_ARCHIVE_DEST_2   PRIMARY     \/u04\/alternate_dest   ALL_ROLES    ALTERNATE LOG_ARCHIVE_DEST_1<\/em><\/p>\n

You can check to confirm that you linked the archive destinations one and two. Oracle should now be able to fail back to the primary after the primary has free space.<\/p>\n

Demo part four<\/strong><\/p>\n

Next, I executed some DMLs so that the archive destination hit 100% usage, as shown in the following output:
\n <\/p>\n

 Thread 1 is advanced to log sequence 20 (LGWR switch)
\n      Current log# 2 seq# 20 mem# 0: \/u01\/app\/oracle\/oradata\/MODI\/redo02.log
\n    2020-12-05T04:44:29.216275+05:30
\n    ARC0: Encountered disk I\/O error 19502
\n    2020-12-05T04:44:29.216575+05:30
\n    ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1 ‘\/u03\/primary_dest\/1_19_1058325294.dbf’ (error 19502) (MODI)
\n    2020-12-05T04:44:29.217417+05:30
\n    Errors in file \/u01\/app\/oracle\/diag\/rdbms\/modi\/MODI\/trace\/MODI_arc0_23015.trc:
\n    ORA-27072: File I\/O error
\n    Additional information: 4
\n    Additional information: 350208
\n    Additional information: 446464
\n    ORA-19502: write error on file “\/u03\/primary_dest\/1_19_1058325294.dbf”, block number 350208 (block size=512)
\n    2020-12-05T04:44:32.583182+05:30
\n    MODIP(3):Resize operation completed for file# 11, old size 1172480K, new size 1182720K
\n    2020-12-05T04:44:33.183355+05:30
\n    Errors in file \/u01\/app\/oracle\/diag\/rdbms\/modi\/MODI\/trace\/MODI_arc0_23015.trc:
\n    ORA-19502: write error on file “\/u03\/primary_dest\/1_19_1058325294.dbf”, block number 350208 (block size=512)
\n    ORA-27072: File I\/O error
\n    Additional information: 4
\n    Additional information: 350208
\n    Additional information: 446464
\n    ORA-19502: write error on file “\/u03\/primary_dest\/1_19_1058325294.dbf”, block number 350208 (block size=512)<\/em><\/p>\n

Step five<\/strong><\/p>\n

Oracle always tries to archive to the primary destination, and the automatic failback to the primary destination works in version 12c. After you clean up the primary destination and make it available for archiving, Oracle continues to archiving the redo logs to the primary destination without reporting any errors or failback message in the alert log file, as shown in the following example where I queried v$archived_log<\/strong><\/p>\n

SQL>  select thread#,sequence#,name from v$archive_log;:<\/em><\/p>\n

  THREAD#  SEQUENCE#  NAME
\n    ——– ———- ———————————————–
\n         1          2 \/u03\/primary_dest\/1_2_1058325294.dbf
\n         1          3 \/u03\/primary_dest\/1_3_1058325294.dbf
\n         1          4 \/u03\/primary_dest\/1_4_1058325294.dbf
\n         1          5 \/u03\/primary_dest\/1_5_1058325294.dbf
\n         1          6 \/u04\/alternate_dest\/1_6_1058325294.dbf
\n         1          7 \/u04\/alternate_dest\/1_7_1058325294.dbf
\n         1          8 \/u04\/alternate_dest\/1_8_1058325294.dbf
\n         1          9 \/u04\/alternate_dest\/1_9_1058325294.dbf
\n         1         10 \/u04\/alternate_dest\/1_10_1058325294.dbf
\n         1         11 \/u04\/alternate_dest\/1_11_1058325294.dbf
\n         1         12 \/u04\/alternate_dest\/1_12_1058325294.dbf
\n         1         13 \/u04\/alternate_dest\/1_13_1058325294.dbf
\n         1         14 \/u04\/alternate_dest\/1_14_1058325294.dbf
\n         1         15 \/u04\/alternate_dest\/1_15_1058325294.dbf
\n         1         16 \/u04\/alternate_dest\/1_16_1058325294.dbf
\n         1         17 \/u04\/alternate_dest\/1_17_1058325294.dbf
\n         1         18 \/u04\/alternate_dest\/1_18_1058325294.dbf
\n         1         19 \/u04\/alternate_dest\/1_19_1058325294.dbf
\n         1         20 \/u04\/alternate_dest\/1_20_1058325294.dbf
\n         1         21 \/u04\/alternate_dest\/1_21_1058325294.dbf
\n         1         22 \/u04\/alternate_dest\/1_22_1058325294.dbf
\n         1         23 \/u04\/alternate_dest\/1_23_1058325294.dbf
\n         1         24 \/u04\/alternate_dest\/1_24_1058325294.dbf
\n         1         25 \/u04\/alternate_dest\/1_25_1058325294.dbf
\n         1         26 \/u04\/alternate_dest\/1_26_1058325294.dbf
\n         1         27 \/u04\/alternate_dest\/1_27_1058325294.dbf
\n         1         28 \/u04\/alternate_dest\/1_28_1058325294.dbf
\n         1         29 \/u03\/primary_dest\/1_29_1058325294.dbf
\n         1         30 \/u03\/primary_dest\/1_30_1058325294.dbf
\n         1         31 \/u03\/primary_dest\/1_31_1058325294.dbf
\n         1         32 \/u03\/primary_dest\/1_32_1058325294.dbf<\/em><\/p>\n

31 rows selected.<\/p>\n

Conclusion<\/strong><\/p>\n

Oracle has made a significant improvement in the automatic management of archive destination switching between the primary and alternate destination.  Hopefully, you can use my demonstration to take advantage of this functionality.<\/p>\n

Having an alternate archive serves you well, but remember that Oracle intermittently logs error messages in the alert log file about the primary destination failure until the primary archive destination is ready again.<\/p>\n","protected":false},"excerpt":{"rendered":"

Alternate archive destinations in Oracle<\/p>\n","protected":false},"author":2,"featured_media":7235,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26326","content_type":"blog","created_by":"10130","authored_on":"2023-11-30T20:32:38+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Jay Vardhan, Oracle Database Administrator, Rackspace Technology","seo_description":"Understand how to set up and leverage alternate archiving destinations in Oracle Databases for avoiding system hang-ups due to space constraints.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26326-insights_image-oracle-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Oracle","product":"","publisheddate":"2021-05-14T19:32:38","rxt_solutions":"Databases","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Alternate Archive Destinations in Oracle | Rackspace Technology","field_topics":"","field_slug":"alternate-archive-destniations-in-oracle","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7233","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7233"}],"version-history":[{"count":1,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7233\/revisions"}],"predecessor-version":[{"id":7234,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7233\/revisions\/7234"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7235"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}