rename log file , data file or dropping log files in oracle straight forward

I'm writting this post for beginner DBAs to let them apply concept of redo log file,instance recovery and file management.

There are two ways to acheive rename datafile/redo logfile it:

1) data file and log files both can be renamed at the mount stage of database with same command ALTER DATABASE RENAME FILE OLDNAME TO NEWNAME;

steps #a) shutdown immediate
         #b) copy /path/redolog1 /newpath/redolog1_a
               copy /path/datafile1 /newpath/datafile01              
         #c) startup mount
         #d) alter database rename file '/path1/redolog1' to '/newpath2/redolog1a';
               alter database rename file '/path/datafile1' '/newpath/datafile01'
         #e) alter database open  

2) redo log files can not be renamed while database is open though you can drop redo logfile and create the one with the new path/name. On the other hand datafile can be renamed while the database is open(online) with ALTER TABLESPACE RENAME DATAFILE OLDNAME TO NEWNAME

steps #a) alter tablespace mydata offline;
          #b) host copy /path1/datafile1 /newpath2/datafile_01
          #c)  alter tablespace rename datafile '/path1/datafile1' to '/newpath2/datafie_01'
          #d) alter tablespace mydata online;

SQL> alter database add logfile 'd:\oracle\product\10.2.0\oradata\orcl\REDO04.log' size 50m reuse;
Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  234881024 bytes
Fixed Size                  1289916 bytes
Variable Size             163578180 bytes
Database Buffers           62914560 bytes
Redo Buffers                7098368 bytes
Database mounted.
SQL> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG D:\REDO04.log
        1 file(s) copied.

SQL> alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG' to 'D:\REDO04.log';
Database altered.

SQL> alter database open;
Database altered.

Dropping redo logfile is also pretty straight forward thing. You just need to know active redo log file/group can not be dropped ,similarly current redo logfile/group can not be dropped.


Active redo log can not be dropped as checkpoint of its contents is not complete obviously and current redo log can be dropped because log write process is currently writing into it. Once I found my jumior DBA attempting to drop redo logfile which was active and it failed for above stated reason.So remedy I told her was to issue first Alter System CHECKPOINT. 


And to drop current redo log file group, first issue alter switch logfile command so that it is no longer current redo log group, it now becomes active redo log group and then issue alter system checkpoint command followed by drop logfile group or drop logfile command.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)ORA-00312: online log 2 thread 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG'

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        403   52428800          1 YES ACTIVE                 2839841 21-SEP-13
         2          1        404   52428800          1 YES ACTIVE                 2839884 21-SEP-13
         3          1        406   52428800          1 NO  CURRENT                2839888 21-SEP-13
         4          1        405   52428800          1 YES ACTIVE                 2839886 21-SEP-13
SQL> alter system checkpoint ;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        403   52428800          1 YES INACTIVE               2839841 21-SEP-13
         2          1        404   52428800          1 YES INACTIVE               2839884 21-SEP-13
         3          1        406   52428800          1 NO  CURRENT                2839888 21-SEP-13
         4          1        405   52428800          1 YES INACTIVE               2839886 21-SEP-13
SQL> alter database drop logfile group 2;
Database altered.

Here alternatively log member of redo log group #2 could be queried and drop logfile command also could be issued: [ select l.group#,l.sequence#,l.status,lf.member,l.bytes/1024/1024 SIZE_MB  from v$log l, v$logfile lf where l.group#=lf.group# order by l.sequence#;

alter database drop logfile '/u05/app/dbf/redo02log';  ]

SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1        410   52428800          1 YES INACTIVE               2839985 21-SEP-13
         3          1        409   52428800          1 YES INACTIVE               2839983 21-SEP-13
         4          1        411   52428800          1 NO  CURRENT                2839987 21-SEP-13
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 4 thread 1: 'D:\REDO04.LOG'