Rem 
Rem $Header: prgrmanc.sql@@/main/st_rdbms_fsanchez_927353_816/3 Checked in on 09/09/99 01:55:47 by fsanchez $
Rem
Rem prgrmanc.sql
Rem 
Rem Copyright (c) Oracle Corporation 1995-1998, 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      prgrmanc.sql
Rem    DESCRIPTION
Rem      Purges from RMAN Recovery Catalog the records marked as deleted by
Rem      the user.
Rem
Rem      It is up to the user to mark the records as deleted using the
Rem      RMAN command: CHANGE ... DELETE
Rem
Rem      The Media Manager catalog is not updated by this script, only
Rem      the recovery catalog
Rem
Rem      As of 8.1.6 records removed by this script will not be reinserted
Rem      if a "resync from backup controlfile" is performed.  
Rem      Versions previous to 8.1.6 might reinsert the records, this might undo
Rem      both the CHANGE...DELETE and the physical delete of the record.
Rem
Rem      This script removes records from the following tables:
Rem          AL 
Rem          RLH
Rem          BP
Rem          BS
Rem          BCF
Rem          BDF
Rem          BCB
Rem          BRL
Rem          CCF
Rem          CDF
Rem          CCB
Rem          XCF (for 8.1)
Rem          XDF (for 8.1)
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    fsanchez    10/12/99 - backport from 8.1.7
Rem    fsanchez    09/29/99 - Make proxy cursors dynamic so this can be
Rem                           used in 8.0 catalogs
Rem    fsanchez    09/22/99 - Delete AL records without BRL records
Rem    fsanchez    09/08/99 - Delete al records only if catalog is 8.1.6
Rem    fsanchez    08/13/98 - Add proxy tables
Rem    fsanchez    07/28/98 - Creation
Rem
Rem   NOTES
Rem      To avoid using large amounts of rollback the script commits
Rem      every 500 records by default
Rem      This value can be changed by modyfing the following line
define csize=500

set verify off
declare
    i       number;
    key     number;
    thr     number;
    seq     number;
    lscn    number;
    eoc     boolean;

-- Dynamic SQL variables
    confcur integer;
    xcfcur  integer;
    delxcfcur
            integer;
    xdfcur  integer;
    delxdfcur
            integer;
    compat  integer;
    ignore  integer;

-- Cursors

-- Cursor to obtain AL records without BRL records
    cursor alrecs_noref is
        select dbinc_key, thread#, sequence#, low_scn
          from al
         where status = 'D'
           and not exists
             (select null
                from brl
               where al.thread# = brl.thread#
                 and al.sequence# = brl.sequence#
                 and al.low_scn = brl.low_scn)
           for update of status;

-- Cursor to obtain all AL records
    cursor alrecs_all is
        select dbinc_key, thread#, sequence#, low_scn
          from al
         where status = 'D'
           for update of status;

-- Cursor to obtain all BP records
    cursor bprecs is
        select bp_key
          from bp
         where status = 'D'
           for update of status;

-- Cursor to obtain all BS records
    cursor bsrecs is
        select bs_key
          from bs
         where not exists
             (select null
                from bp
               where bs.bs_key = bp.bs_key)
           for update of status;

-- Cursor to obtain all CCF records
    cursor ccfrecs is
        select ccf_key
          from ccf
         where status = 'D'
           for update of status;

-- Cursor to obtain all CDF records
    cursor cdfrecs is
        select cdf_key
          from cdf
         where status = 'D'
           for update of status;

-- Exceptions
    no_table_found exception;
    pragma exception_init(no_table_found, -942);

begin
    -- Check if compat table exist and if it does, if the
    -- compatibility is set to 8.1.6
    begin
        confcur := dbms_sql.open_cursor;
        dbms_sql.parse(confcur, 
                       'select to_number(value) value
                          from config 
                         where name=''compatible'' ',
                       dbms_sql.native);
        dbms_sql.define_column(confcur, 1, compat);

        ignore := dbms_sql.execute(confcur);

        if dbms_sql.fetch_rows(confcur) > 0 then
            dbms_sql.column_value(confcur, 1, compat);
        else
            compat := 80004;
        end if;
        dbms_sql.close_cursor(confcur);
    exception
        when no_table_found then
            compat:= 80004;
    end;
    if compat >= 80106 then
        -- Step 1) Remove al records marked as deleted.  
        --         If a rlh record is found, also remove it
        eoc := false;
        while not eoc loop
            open alrecs_all;
            i := 0;
            while not eoc and i <= &&csize loop
                fetch alrecs_all into key, thr, seq, lscn;
                if not alrecs_noref%NOTFOUND then
                    -- Delete the log history for this archivelog
                    delete rlh
                     where dbinc_key = key
                       and thread# = thr
                       and sequence# = seq
                       and low_scn = lscn;
                    -- Delete the current al record
                    delete al
                     where current of alrecs_all;
                    -- Increment counter
                    i := i + 1;
                else
                    -- signal that we have processed all al records
                    eoc := true;
                end if;
            end loop;
            --- close and commit changes
            close alrecs_all;
            commit;
        end loop;
--  else
--      cannot remove AL records blindly.  Once the BS records are removed
--      then it might be possible to remove AL record without BRL records
    end if;

    -- Step 2) Delete the bp records marked as deleted
    eoc := false;
    while not eoc loop
        open bprecs;
        i := 0;
        while not eoc and i <= &&csize loop
            fetch bprecs into key;
            if not bprecs%NOTFOUND then
                -- Delete the current bp record
                delete bp
                 where current of bprecs;
                -- Increment counter
                i := i + 1;
            else
                -- signal that we have processed all bp records
                eoc := true;
            end if;
        end loop;
        --- close and commit changes
        close bprecs;
        commit;
    end loop;

    -- Step 3) Remove the bs records that do not have any pieces left.
    --         When the bs record is removed, the  bcf, bdf, bcb and brl
    --         records are removed automatically by the integrity constraints
    eoc := false;
    while not eoc loop
        open bsrecs;
        i := 0;
        while not eoc and i <= &&csize loop
            fetch bsrecs into key;
            if not bsrecs%NOTFOUND then
                -- Delete the current bs record, that in turn will remove
                -- records from bcf, bdf and brl.
                -- If a bdf record is removed, the bcb record will
                -- also be deleted.
                delete bs
                 where current of bsrecs;
                -- Increment counter
                i := i + 1;
            else
                -- signal that we have processed all bs records
                eoc := true;
            end if;
        end loop;
        --- close and commit changes
        close bsrecs;
        commit;
    end loop;
    
    -- Step 4) Remove the ccf records that are marked as deleted
    eoc := false;
    while not eoc loop
        open ccfrecs;
        i := 0;
        while not eoc and i <= &&csize loop
            fetch ccfrecs into key;
            if not ccfrecs%NOTFOUND then
                -- Delete the current ccf record
                delete ccf
                 where current of ccfrecs;
                -- Increment counter
                i := i + 1;
            else
                -- signal that we have processed all bs records
                eoc := true;
            end if;
        end loop;
        --- close and commit changes
        close ccfrecs;
        commit;
    end loop;

    -- Step 5) Remove the cdf records that are marked as deleted
    eoc := false;
    while not eoc loop
        open cdfrecs;
        i := 0;
        while not eoc and i <= &&csize loop
            fetch cdfrecs into key;
            if not cdfrecs%NOTFOUND then
                -- Delete the current cdf record.  This in turn will
                -- remove ccb records by integrity constraints
                delete cdf
                 where current of cdfrecs;
                -- Increment counter
                i := i + 1;
            else
                -- signal that we have processed all bs records
                eoc := true;
            end if;
        end loop;
        --- close and commit changes
        close cdfrecs;
        commit;
    end loop;

    -- Step 6) Delete the xcf records marked as deleted
    eoc := false;
    while not eoc loop
        begin
            -- open xcf cursor
            xcfcur := dbms_sql.open_cursor;
            delxcfcur := dbms_sql.open_cursor;
            dbms_sql.parse(xcfcur, 
                           'select xcf_key
                              from xcf
                             where status = ''D''
                               for update of status',
                           dbms_sql.native);
            dbms_sql.define_column(xcfcur, 1, key);
            ignore := dbms_sql.execute(xcfcur);

            -- prepare delete cursor
            dbms_sql.parse(delxcfcur, 
                           'delete xcf
                             where xcf_key = :xcfkey',
                           dbms_sql.native);
            i := 0;
            while not eoc and i <= &&csize loop
                if dbms_sql.fetch_rows(xcfcur) > 0 then
                    dbms_sql.column_value(xcfcur, 1, key);
                    -- Delete the current xcf record.
                    dbms_sql.bind_variable(delxcfcur, 'xcfkey', key);
                    ignore := dbms_sql.execute(delxcfcur);
                    -- Increment counter
                    i := i + 1;
                else
                    -- signal that we have processed all xcf records
                    eoc := true;
                end if;
            end loop;
            dbms_sql.close_cursor(xcfcur);
            dbms_sql.close_cursor(delxcfcur);
        exception
            when no_table_found then
                eoc := true;
        end;
        --- close and commit changes
        commit;
    end loop;

    -- Step 7) Delete the xdf records marked as deleted
    eoc := false;
    while not eoc loop
        begin
            -- open xdf cursor
            xdfcur := dbms_sql.open_cursor;
            delxdfcur := dbms_sql.open_cursor;
            dbms_sql.parse(xdfcur, 
                           'select xdf_key
                              from xdf
                             where status = ''D''
                               for update of status',
                           dbms_sql.native);
            dbms_sql.define_column(xdfcur, 1, key);
            ignore := dbms_sql.execute(xdfcur);

            -- prepare delete cursor
            dbms_sql.parse(delxdfcur, 
                           'delete xdf
                             where xdf_key = :xdfkey',
                           dbms_sql.native);
            i := 0;
            while not eoc and i <= &&csize loop
                if dbms_sql.fetch_rows(xdfcur) > 0 then
                    dbms_sql.column_value(xdfcur, 1, key);
                    -- Delete the current xdf record.
                    dbms_sql.bind_variable(delxdfcur, 'xdfkey', key);
                    ignore := dbms_sql.execute(delxdfcur);
                    -- Increment counter
                    i := i + 1;
                else
                    -- signal that we have processed all xdf records
                    eoc := true;
                end if;
            end loop;
            dbms_sql.close_cursor(xdfcur);
            dbms_sql.close_cursor(delxdfcur);
        exception
            when no_table_found then
                eoc := true;
        end;
        --- close and commit changes
        commit;
    end loop;

    if compat < 80106 then
        -- Step 7) Remove al records without brl records marked as deleted.  
        --         If a rlh record is found, also remove it
        --         If the al record does not have a brl record then it can
        --         be deleted as it cannot be restored anymore
        eoc := false;
        while not eoc loop
            open alrecs_noref;
            i := 0;
            while not eoc and i <= &&csize loop
                fetch alrecs_noref into key, thr, seq, lscn;
                if not alrecs_noref%NOTFOUND then
                    -- Delete the log history for this archivelog
                    delete rlh
                     where dbinc_key = key
                       and thread# = thr
                       and sequence# = seq
                       and low_scn = lscn;
                    -- Delete the current al record
                    delete al
                     where current of alrecs_noref;
                    -- Increment counter
                    i := i + 1;
                else
                    -- signal that we have processed all al records
                    eoc := true;
                end if;
            end loop;
            --- close and commit changes
            close alrecs_noref;
            commit;
        end loop;
    end if;
end;
/
