Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
The following PL/SQL scripts perform tasks which arise in the migration scenarios described in this chapter.
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.
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;