OK
select * from v$logfile will show you the current logfiles and their locations
GROUP# STATUS MEMBER
---------- ------- ------------------------------
1 /u05/oradata/pkms/log1pkms.ora
2 /u05/oradata/pkms/log2pkms.ora
3 /u05/oradata/pkms/log3pkms.ora
1 /u02/oradata/pkms/log1pkms.ora
2 /u02/oradata/pkms/log2pkms.ora
3 /u02/oradata/pkms/log3pkms.ora
select * from v$log will show you the status of each group (i.e the CURRENT group is what we are looking for)
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 36961 10485760 2 NO CURRENT
19383950 21-MAR-03
2 1 36959 10485760 2 YES INACTIVE
19382184 21-MAR-03
3 1 36960 10485760 2 YES INACTIVE
19383112 21-MAR-03
Every time a log switch occurs (either automatically or by 'alter system switch logfile') the current group will change.
You need to add another group to the database in the new location
This is taken from the docs;
ALTER DATABASE stocks
ADD LOGFILE GROUP 4
('/u01/oradata/db1/log3.log' ,
'/u01/oradata/db1/log3.log') SIZE 50K;
You need to add as many groups as you have now in the database (at least 2 or 3 I would hope!)
Once you have added the new groups you can drop the old groups PROVIDED they are not CURRENT (as in select * from v$log)
ALTER DATABASE stocks DROP LOGFILE GROUP 1;
Use 'alter system switch logfile' to move the current group
You will end up with groups 4,5,6 instead of 1,2,3 - if that bothers you then repeat the procedure until you get the numbers you want
Thats it
Alex