Oracle/HP Best Practices Guide for HP IO Accelerators

Single-instance performance architectures 21
1.
Log on the TESTDB database as sysdba:
export ORACLE_SID=TESTDB
sqlplus / as sysdba
NOTE: The datafiles that make up the existing REDO logs cannot be renamed or relocated
while the database is open. Instead, you must create new REDO logs in the new location, and
then drop the old
REDO
logs.
To relocate the REDO logs, create new REDO log groups using the new IO Accelerator diskgroup
location, and then drop the old REDO log groups.
2. Use the following PL/SQL script to migrate the online redo log groups to the new IO Accelerator
diskgroup. For each online redo log group, the script adds a log file in the new location, archives
the current redo logs, and then drops the old 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 || ' ''+REDODG'' 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 || ' ''+REDODG'' 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;