Scenario - Archive Destination Full

This post will discuss what action you can take if your Archive Destination is full. There are many ways to solve this scenario but I found this solution very easy to understand and it is working (in my environment.)

So lets get started. I will now tell you how to simulate so that your archive destination will be full. This is very simple technique, there is nothing complicated in this simulation just follow the steps as described.

• So first create a init.ora file as: (set other parameters whatever you want, but to simulate this scenario easily you must set db_recovery_file_dest and db_recovery_file_dest_size as set in following example)
  
  

  db_name='test01'
  db_unique_name='test01'
  sga_target=500M
  db_block_size=8192
  db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
  db_recovery_file_dest_size=546M
  diagnostic_dest='/u01/app/oracle'
  remote_login_passwordfile='EXCLUSIVE'
  undo_tablespace='UNDOTBS1'
  control_files = ('/u01/app/oracle/oradata/test01/control01.ctl')
  compatible ='11.2.0'
  

• Use following CREATE DATABASE statement to create the database. (don't change the specification of LOGFILE.)
  
  

  CREATE DATABASE TEST01 ARCHIVELOG
  DATAFILE '/u01/app/oracle/oradata/test01/sys01.dbf' SIZE 300M
  SYSAUX
  DATAFILE '/u01/app/oracle/oradata/test01/sysaux01.dbf' SIZE 200M
  UNDO TABLESPACE UNDOTBS1
  DATAFILE '/u01/app/oracle/oradata/test01/undotbs1.dbf' SIZE 90M
  DEFAULT TEMPORARY TABLESPACE TEMPTBS1
  TEMPFILE '/u01/app/oracle/oradata/test01/temptbs1.dbf' SIZE 90M
  LOGFILE
  GROUP 1 ('/u01/app/oracle/oradata/test01/redo01.log') SIZE 10M,
  GROUP 2 ('/u01/app/oracle/oradata/test01/redo02.log') SIZE 10M
  /
  

• Now create database manaully, using above mentioned files. After creating the database run the usual files you run after creating database, i.e. '$ORACLE_HOME/rdbms/catalog.sql' & '$ORACLE_HOME/rdbms/catproc.sql' then by logging as SYSTEM user run the '$ORACLE_HOME/sqlplus/admin/pupbld.sql'
• After running these scripts successfully your archive destination will now acquire around 540MB of size. Now we have to get the remaining 6MB so our archive destination will be full.
• To get the remaining 6MB, do the same steps as I have done in the following example.

  [oracle@oracleasm1 test01]$ rlwrap sqlplus / as sysdba
  
  SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 19 23:49:05 2016
  
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
  
  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  
  SQL> show parameter db_recovery_file_dest
  
  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area
  db_recovery_file_dest_size           big integer 546M
  SQL> select space_limit/1024/1024 as "Limit MB",space_used/1024/1024 as "Used MB"
    2  from v$recovery_file_dest;
  
    Limit MB    Used MB
  ---------- ----------
         546 539.394531
  
  SQL> create tablespace xyz
    2  datafile '/u01/app/oracle/oradata/test01/xyz01.dbf' size 30m;
  
  Tablespace created.
  
  SQL> create user us01 identified by us01
    2  default tablespace xyz quota unlimited on xyz;
  
  User created.
  
  SQL> grant create table, connect to us01;
  
  Grant succeeded.
  SQL> conn us01/us01
  Connected.
  SQL> CREATE TABLE countries (country_id CHAR(2), country_name VARCHAR2(40), region_id NUMBER);
  
  Table created.
  SQL> BEGIN
    2  INSERT INTO countries VALUES ('IT','Italy',1);
    3  INSERT INTO countries VALUES ('JP','Japan',3);
    4  INSERT INTO countries VALUES ('US','United States of America',2);
    5  INSERT INTO countries VALUES ('CA','Canada',2);
    6  INSERT INTO countries VALUES ('CN','China',3);
    7  INSERT INTO countries VALUES ('IN','India',3);
    8  INSERT INTO countries VALUES ('AU','Australia',3);
    9  INSERT INTO countries VALUES ('ZW','Zimbabwe',4);
   10  INSERT INTO countries VALUES ('SG','Singapore',3);
   11  INSERT INTO countries VALUES ('UK','United Kingdom',1);
   12  INSERT INTO countries VALUES ('FR','France',1);
   13  INSERT INTO countries VALUES ('DE','Germany',1);
   14  INSERT INTO countries VALUES ('ZM','Zambia',4);
   15  INSERT INTO countries VALUES ('EG','Egypt',4);
   16  INSERT INTO countries VALUES ('BR','Brazil',2);
   17  INSERT INTO countries VALUES ('CH','Switzerland',1);
   18  INSERT INTO countries VALUES ('NL','Netherlands',1);
   19  INSERT INTO countries VALUES ('MX','Mexico',2);
   20  INSERT INTO countries VALUES ('KW','Kuwait',4);
   21  INSERT INTO countries VALUES ('IL','Israel',4);
   22  INSERT INTO countries VALUES ('DK','Denmark',1);
   23  INSERT INTO countries VALUES ('HK','HongKong',3);
   24  INSERT INTO countries VALUES ('NG','Nigeria',4);
   25  INSERT INTO countries VALUES ('AR','Argentina',2);
   26  INSERT INTO countries VALUES ('BE','Belgium',1);
   27  END;
   28  /
  
  PL/SQL procedure successfully completed.
  
  SQL> commit;
  
  Commit complete.
  
  SQL> select count(*) from countries;
  
    COUNT(*)
  ----------
          25
  
  SQL> conn / as sysdba
  Connected.
  SQL> select space_limit/1024/1024 as "Limit MB",space_used/1024/1024 as "Used MB"
    2  from v$recovery_file_dest;
  
    Limit MB    Used MB
  ---------- ----------
         546 539.394531
  
  SQL> alter system switch logfile; 

• After firing the last statement which is ALTER SYSTEM SWITCH LOGFILE; if your session stuck here then you've done everything right, else you must have left something out. So now you've successfully simulated the scenario.
• To solve this problem, just open another Terminal or Putty session, login as SYSDBA privilege and give another archive log destination.

  [oracle@oracleasm1 test01]$ rlwrap sqlplus / as sysdba
  
  SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 19 23:54:53 2016
  
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
  
  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  
  SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/test01/archives';
  
  System altered.
  SQL> archive log list
  Database log mode              Archive Mode
  Automatic archival             Enabled
  Archive destination            /u01/app/oracle/oradata/test01/archives
  Oldest online log sequence     57
  Next log sequence to archive   58
  Current log sequence           58
  

As soon as you set value for log_archive_dest_1 parameter the stuck session will now no longer stuck. Now if you still want to use the DB_RECOVERY_AREA then you can take an RMAN backup of archive log with the DELETE INPUT so that it will delete all the archives it has taken backup of. And make the log_archive_dest_state_1 as DEFFER so archive generation won't happen here.