PL/SQL Scripts Used in Migrating to ASM Storage

Generating ASM-to-Non-ASM Storage Migration Script

You can use the following PL/SQL script to generate a series of RMAN commands that you can use to migrate your database back from ASM to non-ASM disk storage.

set serveroutput on;
declare
    cursor df is select file#, name from v$datafile;
begin
    dbms_output.put_line('run');
    dbms_output.put_line('{');
    for dfrec in df loop
        dbms_output.put_line('set newname for datafile ' ||
            dfrec.file# || ' to ''' || dfrec.name ||''' ;');
    end loop;
    dbms_output.put_line('restore database;');
    dbms_output.put_line('switch all;');
    dbms_output.put_line('}');
end;

Run this PL/SQL script and save the output into a file. The result is an RMAN script which you can save to a file and later run as a command file in the RMAN client to migrate your datafiles back out of ASM storage to their original non-ASM locations. Even if you later add or delete datafiles, this script provides a useful starting point for a migration script that will work for the new database.

Migrating Online Logs of Primary Database to ASM

The following PL/SQL script can be used to migrate the online redo log groups into ASM, as part of migrating a database or a flash recovery area into ASM. For each online redo log group, the script adds a log file stored in ASM, archives the current redo logs, and then drops the non-ASM log file.

declare
   cursor rlc is
      select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
        from v$log
      union
      select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
        from v$standby_log
      order by 1;
   stmt     varchar2(2048);
   swtstmt  varchar2(1024) := 'alter system switch logfile';
   ckpstmt  varchar2(1024) := 'alter system checkpoint global';
begin
   for rlcRec in rlc loop
      if (rlcRec.srl = 'YES') then
         stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' || 
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
         execute immediate stmt;
      else
         stmt := 'alter database add logfile thread ' ||
                 rlcRec.thr || ' ''+DISK'' size ' ||  
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         begin
            stmt := 'alter database drop logfile group ' || rlcRec.grp;
            dbms_output.put_line(stmt);
            execute immediate stmt;
         exception
            when others then
               execute immediate swtstmt;
               execute immediate ckpstmt;
               execute immediate stmt;
         end;
      end if;
   end loop;
end;