Oracle: Free space in Fast-Recovery-Area (FRA)

Sooner or later I guess every DBA will be faced with this error-message

ORA-00257: archiver error. Connect internal only, until freed.

and angry phone-calls from users complaining about a hanging database.

The reason for this error is a lack of space for archiving redologs.

When using a FRA and a proper set up backup that deletes obsolete files automatically, you propably only have two options:

  • Increase the FRA-Size by changing the parameter “db_recovery_file_dest_size” and/or
  • Relocate the FRA-Directory to another disc by changing the parameter “db_recovery_file_dest”
  • If space is available, both options are done quick and easy and get you out of your mess instantly. So don’t panic – and beware of deleting e.g. some archive-logs on OS-Level. As laid out in my post “Oracle: How to tell DB about manually freed space in FRA“, the latter won’t get you further and may even worsen your situation.

    To increase the FRA-Size do this:

    alter system set db_recovery_file_dest_size=100g scope=both;
    

    To change to FRA-Location do this:

    alter system set db_recovery_file_dest='/usr4/oracle/fast_recovery_area' scope=both;
    

    Changing the FRA-Location is not complicated as stated here: “Oracle: Relocate Fast-Recovery-Area (FRA)

    Advertisements

    Oracle: Relocate Fast-Recovery-Area (FRA)

    As our database grows and grows, here especially in respect of backupsize and daily generated archive-logs, we may sometime get to a point when we have to relocate our FRA to another device with more free space. This could also be the case in a situation when we have run out of FRA-space because of an unexpected sudden massive increase of archived redologs. I just had the latter when some developers had run an upgrade of their application.

    Luckily it is no big fuss to change the FRAs destination directory. It’s just a matter of changing the location with

    alter system set db_recovery_file_dest='/usr4/oracle/fast_recovery_area' scope=both;
    

    and increasing the FRAs size with

    alter system set db_recovery_file_dest_size=100g scope=both;
    

    To test the new settings we can initiate a redolog-archiving:

    alter system switch logfile;
    alter system checkpoint;
    

    After this we should see a new subdirectory in the new FRA-Location with our DB’s SID as name and some subfolders in there containing our archived redolog.

    Beware not to move the existing/old FRA-Files to the new location using OS-Commands, as the DB would not notice this and couldn’t keep track of that files. We can just let the files stay in the old FRA-Location until they age out and get deleted automatically by our DB. The absolute path to each file is recorded in the DB, so that the change of the db_recovery_file_dest is no problem for existing files. But keep in mind, that the overall size of files in the old db_recovery_file_dest adds up to the total size of our FRA. The FRA is a mere virtual concept that has no necessary 1:1 relationship to a physical directory on disc. So if we create a backup outside of our db_recovery_file_dest, this adds up too to our FRA-Size.

    Oracle: Drop all userobjects

    Especially developers often have the need to clean out their dev-schema and get a fresh start with a given dump-set. A DBA usually would simply drop and recreate the schema. But for not to cross their DBA, kind developers are longing for a solution to carry out that cleaning autonomously. This could be done (e.g. in SQL-Plus) with a script like this. Please mind to execute this script in the “right” schema, as the dropping starts immediately without further inquiry!

    -- This script drops all objects of the executing schema.
    -- 2010-06-15 Marc Tempel (https://logbuffer.wordpress.com)
    
    spool dropped_userobjects.log
    
    set serveroutput on size 1000000
    set heading off
    set echo off
    select 'Number of Userobjects before DROP: ' || count(*) from user_objects;
    set heading on
    set echo on
    
    purge recyclebin;
    
    begin
    
    dbms_output.put_line ('###');
    dbms_output.put_line ('Dropping all TRIGGERS:');
    dbms_output.put_line ('###');
    
      for a in (
    
        select 'DROP ' || object_type || ' ' || object_name as kommando  
        from user_objects where object_type='TRIGGER'
    
      ) loop
        begin
          dbms_output.put_line (a.kommando);
    EXECUTE IMMEDIATE a.kommando;
        exception
          when others then
            dbms_output.put_line (SQLERRM||'#');
        end;
      end loop;
    
     
    dbms_output.put_line ('###');
    dbms_output.put_line ('Dropping all CONSTRAINTS:');
    dbms_output.put_line ('###');
    
      for a in (
    
        select 'ALTER TABLE ' || table_name || ' DROP CONSTRAINT ' || constraint_name as kommando 
        from (select table_name, constraint_name from user_constraints order by constraint_type desc)
    
      ) loop
        begin
          dbms_output.put_line (a.kommando);
    EXECUTE IMMEDIATE a.kommando;
        exception
          when others then
            dbms_output.put_line (SQLERRM||'#');
        end;
      end loop;
    
     
    dbms_output.put_line ('###');
    dbms_output.put_line ('Dropping all TABLES:');
    dbms_output.put_line ('###');
    
      for a in (
    
        select 'DROP ' || object_type || ' ' || object_name as kommando  
        from user_objects where object_type='TABLE'
    
      ) loop
        begin
          dbms_output.put_line (a.kommando);
    EXECUTE IMMEDIATE a.kommando;
        exception
          when others then
            dbms_output.put_line (SQLERRM||'#');
        end;
      end loop;
    
    
    
    EXECUTE IMMEDIATE 'purge recyclebin';
    
    
    dbms_output.put_line ('###');
    dbms_output.put_line ('Dropping all remaining objects:');
    dbms_output.put_line ('### ');
    
      for a in (
    
        select 'DROP ' || object_type || ' ' || object_name as kommando  
        from user_objects
    
      ) loop
        begin
          dbms_output.put_line (a.kommando);
    EXECUTE IMMEDIATE a.kommando;
        exception
          when others then
            dbms_output.put_line (SQLERRM||'#');
        end;
      end loop;
      
    commit;
    end;
    /
    purge recyclebin;
    set heading off
    set echo off
    select 'Number of Userobjects after DROP: ' || count(*) from user_objects;
    set heading on
    set echo on
    spool off