my notes: Moving Logfile in ASM from DATA to DATA

To check logfile

col MEMBER format a50
select MEMBER, GROUP#, STATUS from v$logfile;

MEMBER GROUP# STATUS
-------------------------------------------------- ---------- -------
+DATA/AGAP/ONLINELOG/group_1.258.954836893 1
+DATA/AGAP/ONLINELOG/group_1.259.954836893 1
+DATA/AGAP/ONLINELOG/group_2.260.954836893 2
+DATA/AGAP/ONLINELOG/group_2.261.954836893 2
+DATA/AGAP/ONLINELOG/group_3.262.954836893 3
+DATA/AGAP/ONLINELOG/group_3.263.954836893 3

6 rows selected.

To check OMF for online log
show parameter DB_CREATE_ONLINE_LOG_DEST
NAME TYPE VALUE
------------------------------------ ----------- -------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string

To check recovery area parameter for online log show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- --------
db_recovery_file_dest string +DATA
db_recovery_file_dest_size big integer 4815M

To check diskgroup
select NAME, TOTAL_MB, FREE_MB, USABLE_FILE_MB, (TOTAL_MB-USABLE_FILE_MB) as Using from v$asm_diskgroup;

select GROUP#, STATUS, BYTES/1024/1024 FROM V$LOG;
GROUP# STATUS BYTES/1024/1024
---------- ---------------- ---------------
1 INACTIVE 50
2 INACTIVE 50
3 CURRENT 50

To set up parameters to use OMF and recovery area
alter system set db_recovery_file_dest='+DATA_GRUP' scope=both sid='*';
alter system set db_create_online_log_dest_2='+DATA_GRUP' scope=both sid='*';
alter system set db_create_online_log_dest_1='+DATA_GRUP' scope=both sid='*';

Moving the logfile, mean create in new location and drop old logfile while checking status not current or activeALTER DATABASE ADD LOGFILE GROUP 4 SIZE 50M;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 SIZE 50M;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 SIZE 50M;
ALTER SYSTEM SWITH LOGFILE;
ALTER SYSTEM SWITH LOGFILE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 SIZE 50M;

To check the result

select MEMBER, GROUP#, STATUS from v$logfile;
MEMBER GROUP# STATUS
-------------------------------------------------- ---------- ------
+DATA_GRUP/AGAP/ONLINELOG/group_1.258.955300845 1
+DATA_GRUP/AGAP/ONLINELOG/group_1.259.955300847 1
+DATA_GRUP/AGAP/ONLINELOG/group_2.260.955300873 2
+DATA_GRUP/AGAP/ONLINELOG/group_2.261.955300873 2
+DATA_GRUP/AGAP/ONLINELOG/group_3.262.955301223 3
+DATA_GRUP/AGAP/ONLINELOG/group_3.263.955301223 3
+DATA_GRUP/AGAP/ONLINELOG/group_4.256.955300779 4
+DATA_GRUP/AGAP/ONLINELOG/group_4.257.955300779 4

8 rows selected.

Moving Logfile in ASM from DATA to DATA_GROUP

To check logfile