We asked a consultant to review our production database and one of the recommendations was to switch our redo logs from 2GB to 4GB. This surprised me at first but after checking the 11.2 documentation, I saw that 4GB is the current recommendation. This is a 4 node RAC cluster and we had previously added several additional redo logs based on the application vendor’s recommendations :-). I decided I would take the opportunity to try out the PL/SQL debugger in SQL Developer 4.0.1. Doing the development entirely in SQL Developer instead of vi turned out to be a good decision and saved me a lot of time. In addition to changing the redo sizes, I wanted to move them from our original Exadata X2 ASM disk groups to our new Exadata X3 disk groups.
Manual process:
- Create a new group like the original group
- If the old group is inactive and has been archived, drop the old group
- If the old group is active, current or has not been archived, force a log switch on all RAC nodes, wait until it’s inactive, then drop the old group
- Repeat until all of the old log groups are gone (I’m using Oracle Managed Files so I don’t need to log on to asmcmd and manually delete the files)
I automated these manual steps in the sys.REDO_MAINT package:
CREATE OR REPLACE PACKAGE REDO_MAINT AS type log_rec_type is record (log_group number,log_thread number,is_archived varchar2(3),log_status varchar2(10) ,log_size number); cursor log_records return log_rec_type; procedure manage_logs ( new_size varchar2, new_location varchar2, new_location2 varchar2); function create_new_group ( newgroup number, newthread number, newfilespec varchar2, newfilespec2 varchar2, newfilesize varchar2) return boolean; function drop_old_group ( oldgroup number) return boolean; function redo_in_use ( groupnum number) return boolean; function redo_not_archived ( groupnum number) return boolean; END REDO_MAINT; / CREATE OR REPLACE PACKAGE BODY REDO_MAINT AS cursor log_records return log_rec_type IS SELECT group# ,thread#,archived,status,BYTES from v$log; procedure manage_logs ( new_size varchar2, new_location varchar2, new_location2 varchar2) AS BEGIN for existing_logs in log_records loop dbms_output.put_line('Group '||existing_logs.log_group || ' Thread ' || existing_logs.log_thread || ' Archived ' || existing_logs.is_archived || ' Status ' || existing_logs.log_status || ' Size ' || existing_logs.log_size); if create_new_group(existing_logs.log_group + 100, existing_logs.log_thread,new_location,new_location2,new_size) then dbms_output.put_line('create new group success'); loop if drop_old_group(existing_logs.log_group) then dbms_output.put_line('Dropped old group ' || existing_logs.log_group); exit; end if; dbms_output.put_line('old group was not dropped - retrying ' || existing_logs.log_group); end loop; else dbms_output.put_line('create new group FAILED'); exit; end if; end loop; NULL; END manage_logs; function create_new_group ( newgroup number, newthread number, newfilespec varchar2, newfilespec2 varchar2, newfilesize varchar2) return boolean AS create_group varchar(200); BEGIN create_group:='ALTER DATABASE ADD LOGFILE THREAD ' || newthread || ' GROUP ' || newgroup || ' ( ' || '''' || newfilespec || '''' || ' , ' || '''' || newfilespec2 || '''' || ' ) size ' || newfilesize ; dbms_output.put_line(create_group); execute immediate create_group; RETURN TRUE; exception when others then dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); return FALSE; END create_new_group; function drop_old_group ( oldgroup number) return boolean AS drop_group varchar(200); BEGIN drop_group:='alter database drop logfile group ' || oldgroup ; if redo_in_use(oldgroup) or redo_not_archived(oldgroup) then execute immediate 'ALTER SYSTEM ARCHIVE LOG CURRENT' ; -- wait until target redo log becomes inactive loop if redo_in_use(oldgroup) or redo_not_archived(oldgroup) then dbms_output.put_line('Sleeping - group ' || oldgroup); DBMS_LOCK.SLEEP(60); else -- was active - now inactive dbms_output.put_line(drop_group); execute immediate drop_group; RETURN TRUE; exit; end if; end loop; return FALSE; end if; -- was not active - no wait required dbms_output.put_line(drop_group); execute immediate drop_group; RETURN TRUE; exception when others then dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); return FALSE; END drop_old_group; function redo_in_use ( groupnum number) return boolean AS current_status varchar2(10); BEGIN select status into current_status from v$log where group# = groupnum; if current_status in ( 'ACTIVE','CURRENT') then RETURN TRUE; else return FALSE; END IF; exception when others then dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); return FALSE; END redo_in_use; function redo_not_archived ( groupnum number) return boolean AS is_it_archived varchar(3); BEGIN select archived into is_it_archived from v$log where group# = groupnum; if is_it_archived = 'NO' then -- redo not archived RETURN TRUE; else return FALSE; END IF; exception when others then dbms_output.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); return FALSE; END redo_not_archived; END REDO_MAINT; / Example: SYS>select group#,thread#,bytes,archived,status from v$log order by group#, thread#; GROUP# THREAD# BYTES ARC STATUS ---------- ---------- ---------------- --- ---------------- 1 1 2,147,483,648 YES INACTIVE 2 1 2,147,483,648 YES INACTIVE 3 2 2,147,483,648 YES INACTIVE 4 2 2,147,483,648 YES INACTIVE 5 1 2,147,483,648 YES INACTIVE 6 2 2,147,483,648 YES INACTIVE 7 3 2,147,483,648 YES INACTIVE 8 3 2,147,483,648 YES ACTIVE 9 3 2,147,483,648 NO CURRENT 10 4 2,147,483,648 YES INACTIVE 11 4 2,147,483,648 YES INACTIVE 12 4 2,147,483,648 YES INACTIVE 13 1 2,147,483,648 YES INACTIVE 14 1 2,147,483,648 YES INACTIVE 15 1 2,147,483,648 YES INACTIVE 16 1 2,147,483,648 NO CURRENT 17 1 2,147,483,648 YES INACTIVE 20 2 2,147,483,648 YES ACTIVE 21 2 2,147,483,648 NO CURRENT 22 2 2,147,483,648 YES INACTIVE 23 2 2,147,483,648 YES INACTIVE 24 2 2,147,483,648 YES INACTIVE 30 3 2,147,483,648 YES INACTIVE 31 3 2,147,483,648 YES INACTIVE 32 3 2,147,483,648 YES INACTIVE 33 3 2,147,483,648 YES INACTIVE 34 3 2,147,483,648 YES INACTIVE 40 4 2,147,483,648 YES INACTIVE 41 4 2,147,483,648 YES INACTIVE 42 4 2,147,483,648 YES INACTIVE 43 4 2,147,483,648 NO CURRENT 44 4 2,147,483,648 YES INACTIVE 32 rows selected. Kick it off: BEGIN REDO_MAINT.MANAGE_LOGS( NEW_SIZE => '4g', NEW_LOCATION => '+RECO_EDP2', NEW_LOCATION2 => '+DATA_EDP2'); END; / on another session: SYS>select group#,thread#,bytes,archived,status from v$log order by group#, thread#; GROUP# THREAD# BYTES ARC STATUS ---------- ---------- ---------------- --- ---------------- 3 2 2,147,483,648 YES INACTIVE 4 2 2,147,483,648 YES INACTIVE 5 1 2,147,483,648 YES INACTIVE 6 2 2,147,483,648 YES INACTIVE 7 3 2,147,483,648 YES INACTIVE 8 3 2,147,483,648 YES INACTIVE 9 3 2,147,483,648 YES INACTIVE 10 4 2,147,483,648 YES INACTIVE 11 4 2,147,483,648 NO CURRENT 12 4 2,147,483,648 YES INACTIVE 13 1 2,147,483,648 YES INACTIVE 14 1 2,147,483,648 YES INACTIVE 15 1 2,147,483,648 YES INACTIVE 16 1 2,147,483,648 YES INACTIVE 17 1 2,147,483,648 YES INACTIVE 20 2 2,147,483,648 YES INACTIVE 21 2 2,147,483,648 YES INACTIVE 22 2 2,147,483,648 YES INACTIVE 23 2 2,147,483,648 YES INACTIVE 24 2 2,147,483,648 NO CURRENT 30 3 2,147,483,648 YES INACTIVE 31 3 2,147,483,648 YES INACTIVE 32 3 2,147,483,648 NO CURRENT 33 3 2,147,483,648 YES INACTIVE 34 3 2,147,483,648 YES INACTIVE 40 4 2,147,483,648 YES INACTIVE 41 4 2,147,483,648 YES INACTIVE 42 4 2,147,483,648 YES INACTIVE 43 4 2,147,483,648 YES INACTIVE 44 4 2,147,483,648 YES INACTIVE 101 1 4,294,967,296 NO CURRENT 102 1 4,294,967,296 YES UNUSED 32 rows selected. Output from PL/SQL: Group 1 Thread 1 Archived NO Status CURRENT Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 101 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success Sleeping - group 1 Sleeping - group 1 Sleeping - group 1 Sleeping - group 1 Sleeping - group 1 alter database drop logfile group 1 Dropped old group 1 Group 2 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 102 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 2 Dropped old group 2 Group 3 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 103 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 3 Dropped old group 3 Group 4 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 104 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 4 Dropped old group 4 Group 5 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 105 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 5 Dropped old group 5 Group 6 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 106 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 6 Dropped old group 6 Group 7 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 107 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 7 Dropped old group 7 Group 8 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 108 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 8 Dropped old group 8 Group 9 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 109 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 9 Dropped old group 9 Group 10 Thread 4 Archived NO Status CURRENT Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 110 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 10 Dropped old group 10 Group 11 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 111 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success Sleeping - group 11 Sleeping - group 11 Sleeping - group 11 Sleeping - group 11 alter database drop logfile group 11 Dropped old group 11 Group 12 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 112 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 12 Dropped old group 12 Group 13 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 113 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 13 Dropped old group 13 Group 14 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 114 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 14 Dropped old group 14 Group 15 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 115 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 15 Dropped old group 15 Group 16 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 116 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 16 Dropped old group 16 Group 17 Thread 1 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 117 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 17 Dropped old group 17 Group 20 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 120 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 20 Dropped old group 20 Group 21 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 121 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 21 Dropped old group 21 Group 22 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 122 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 22 Dropped old group 22 Group 23 Thread 2 Archived NO Status CURRENT Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 123 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 23 Dropped old group 23 Group 24 Thread 2 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 124 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 24 Dropped old group 24 Group 30 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 130 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 30 Dropped old group 30 Group 31 Thread 3 Archived NO Status CURRENT Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 131 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 31 Dropped old group 31 Group 32 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 132 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 32 Dropped old group 32 Group 33 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 133 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 33 Dropped old group 33 Group 34 Thread 3 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 134 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 34 Dropped old group 34 Group 40 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 140 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 40 Dropped old group 40 Group 41 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 141 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 41 Dropped old group 41 Group 42 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 142 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 42 Dropped old group 42 Group 43 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 143 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 43 Dropped old group 43 Group 44 Thread 4 Archived YES Status INACTIVE Size 2147483648 ALTER DATABASE ADD LOGFILE THREAD 4 GROUP 144 ( '+RECO_EDP2' , '+DATA_EDP2' ) size 4g create new group success alter database drop logfile group 44 Dropped old group 44 PL/SQL procedure successfully completed. Elapsed: 00:15:12.47 SYS>select group#,thread#,bytes,archived,status from v$log order by group#, thread#; GROUP# THREAD# BYTES ARC STATUS ---------- ---------- ---------------- --- ---------------- 101 1 4,294,967,296 YES INACTIVE 102 1 4,294,967,296 NO CURRENT 103 2 4,294,967,296 NO CURRENT 104 2 4,294,967,296 YES UNUSED 105 1 4,294,967,296 YES UNUSED 106 2 4,294,967,296 YES UNUSED 107 3 4,294,967,296 NO CURRENT 108 3 4,294,967,296 YES UNUSED 109 3 4,294,967,296 YES UNUSED 110 4 4,294,967,296 NO CURRENT 111 4 4,294,967,296 YES UNUSED 112 4 4,294,967,296 YES UNUSED 113 1 4,294,967,296 YES UNUSED 114 1 4,294,967,296 YES UNUSED 115 1 4,294,967,296 YES UNUSED 116 1 4,294,967,296 YES UNUSED 117 1 4,294,967,296 YES UNUSED 120 2 4,294,967,296 YES UNUSED 121 2 4,294,967,296 YES UNUSED 122 2 4,294,967,296 YES UNUSED 123 2 4,294,967,296 YES UNUSED 124 2 4,294,967,296 YES UNUSED 130 3 4,294,967,296 YES UNUSED 131 3 4,294,967,296 YES UNUSED 132 3 4,294,967,296 YES UNUSED 133 3 4,294,967,296 YES UNUSED 134 3 4,294,967,296 YES UNUSED 140 4 4,294,967,296 YES UNUSED 141 4 4,294,967,296 YES UNUSED 142 4 4,294,967,296 YES UNUSED 143 4 4,294,967,296 YES UNUSED 144 4 4,294,967,296 YES UNUSED 32 rows selected.