PL/SQL Scripts Used in Migrating to ASM Storage

PL/SQL Scripts Used in Migrating to ASM Storage

 

The following PL/SQL scripts perform tasks which arise in the migration scenarios
described in this chapter.

 

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;