Resize Redo Logs And Keep Existing Names

/* *********************************************************** */
/* PROCEDURES TO RESIZE REDO LOG FILES AND KEEP EXISTING NAMES */
/* *********************************************************** */
/* restricted session: alter system enable/disable restricted session */

/* 1. run checkpoint to flush data from buffers and update data files */
alter system checkpoint;

/* 2.Add new larger logfiles */
ALTER DATABASE ADD LOGFILE ‘D:ORACLEORADATAORANT9REDO-A.LOG’ SIZE 4M;
ALTER DATABASE ADD LOGFILE ‘D:ORACLEORADATAORANT9REDO-B.LOG’ SIZE 4M;
ALTER DATABASE ADD LOGFILE ‘D:ORACLEORADATAORANT9REDO-C.LOG’ SIZE 4M;
ALTER DATABASE ADD LOGFILE ‘D:ORACLEORADATAORANT9REDO-D.LOG’ SIZE 4M;
ALTER DATABASE ADD LOGFILE ‘D:ORACLEORADATAORANT9REDO-E.LOG’ SIZE 4M;

/* 3A. SWITCH LOGFILE TO GET ONTO NEW REDO-A */
/* DETERMINE WHERE CURRENT LOGFILE IS */
/* Status:         */
/* BLANK (in use), stale (incomplete contents ), invalid (file not accessible), deleted (no longer used)  */
SELECT * FROM V$LOGFILE;

/* 3B. SWITCH LOGFILE AS NEEDED*/
ALTER SYSTEM SWITCH LOGFILE;

/* DROP OLD LOG FILES */
alter database drop logfile   ‘D:ORACLEORADATAORANT9REDO01.LOG’ ;
alter database drop logfile   ‘D:ORACLEORADATAORANT9REDO02.LOG’ ;
alter database drop logfile   ‘D:ORACLEORADATAORANT9REDO03.LOG’ ;
alter database drop logfile   ‘D:ORACLEORADATAORANT9REDO04.LOG’ ;
alter database drop logfile   ‘D:ORACLEORADATAORANT9REDO05.LOG’ ;

/* PHYSICALLY DELETE LOG FILES */
DOS>
DELETE REDO01.LOG;
DELETE REDO02.LOG
DELETE REDO03.LOG
DELETE REDO04.LOG
DELETE REDO05.LOG

/* SHUTDOWN DATABASE AND BACK IT UP - YEAH RIGHT, WHERE?*/
SHUTDOWN IMMEDIATE;

/* PHYSICALLY COPY THE REDO LOG FILES TO NEW NAME */
DOS>
COPY REDO-A.LOG REDO01.LOG
COPY REDO-B.LOG REDO02.LOG
COPY REDO-C.LOG REDO03.LOG
COPY REDO-D.LOG REDO04.LOG
COPY REDO-E.LOG REDO05.LOG

/* STARTUP DATABASE */
STARTUP MOUNT;

/* RENAME FILES IN ORACLE - CHANGES POINTER */
ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAORANT9REDO-A.LOG’ TO ‘D:ORACLEORADATAORANT9REDO01.LOG’ ;
ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAORANT9REDO-B.LOG’ TO ‘D:ORACLEORADATAORANT9REDO02.LOG’ ;
ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAORANT9REDO-C.LOG’ TO ‘D:ORACLEORADATAORANT9REDO03.LOG’ ;
ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAORANT9REDO-D.LOG’ TO ‘D:ORACLEORADATAORANT9REDO04.LOG’ ;
ALTER DATABASE RENAME FILE ‘D:ORACLEORADATAORANT9REDO-E.LOG’ TO ‘D:ORACLEORADATAORANT9REDO05.LOG’ ;

/* OPEN DATABASE */
ALTER DATABASE OPEN;

/* BACKUP CONTROL FILE*/
ALTER SYSTEM BACKUP CONTROLFILE TO TRACE;

SELECT * FROM V$LOGFILE;