Oracle/HP Best Practices Guide for HP IO Accelerators

Single-instance performance architectures 19
3.
Create the subdirectory for the TESTDB database in the /u02/oradata directory:
mkdir -p /u02/oradata/TESTDB
The IO Accelerator is now configured as a filesystem and ready for use by the Oracle REDO log
storage.
4. As the oracle user, move the REDO log members to the IO Accelerator.
5. Log on to sqlplus as sysdba:
export ORACLE_SID=TESTDB
sqlplus / as sysdba
The datafiles that make up the existing REDO logs cannot be renamed/relocated while the database
is open. Instead, new REDO logs are created in the new location, and then the old REDO logs are
dropped.
6. Enter the following SQL query to display the current REDO log member information:
Select a.group# "Group Nbr",
b.thread# "Thread Nbr",
substr(a.member,1,65) "Member",
b.bytes/1048576 size_kb "Size (MB)",
b.sequence# "Seq Nbr",
b.archived "Archived",
b.status "Status"
from v$logfile a, v$log b
order by b.thread#, a.group#, substr(a.member,1,65);
To relocate the REDO logs, you must create new REDO log groups using the new IO Accelerator location,
and then drop the old REDO log groups.
1. Use the following PL/SQL script to migrate the online REDO log groups to the new IO Accelerator
filesystem location. 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 || ' ''/u02/oradata/TESTDB/stdby_redo0' || rlcRec.grp |
'.log'' size ' || rlcRec.bytes_k || 'K';
execute immediate stmt;
stmt := 'alter database drop standby logfile group ' || rlcRec.grp;