redolog file status

Redo log file is very crucial for database recovery...
and most of time we confuse about "status" of v$log file for redo log files.

1. UNUSED
Whenever you create any redo log file ...that time v$log view showing status "UNUSED"

2. CURRENT
After Creation when redo log "lgwr" process use that file ....that time v$log view showing status "CURRENT"

3. ACTIVE
If database running in archivelog mode then after log switch "arch" process use log file and create archivelog file that time v$Log file view showing status "ACTIVE"

4. CLEARING
Whenever any log file is corrupted or missing ( which is not "active" or not "current") then we use "alter database clear logfile" statement to recreate corrupted log file ... that time v$log view showing status "CLEARING" after clearing it is showing "UNUSED"

5. INACTIVE
During archivelog file creation v$log view showing status "ACTIVE" and when archivelog file is created then it is showing "INACTIVE" for particular one redo log file at a time.


6. CLEARING_CURRENT
If any error occured during "alter database clear logfile" then v$log file view showing "CLEARING_CURRENT".

Relocate Database Files

Three files is called database files
1. Datafiles & Tempfiles
2. Control files
3. Redo log files

Sometime we need to move database files from old to new location.

How to move DATAFILES from old to new location
Except SYSTEM,UNDO & TEMP tablespace

Step:
1. Take tablespace offline
alter tablespace tbsname OFFLINE;

2. through OS command MOVE datafiles from old to new location.
mv 'old location' to 'new location'

3. rename datafiles in database.
alter database rename file 'old location' to 'new location';

4. Take tablespace ONLINE
alter tablespace tbsname ONLINE;


For SYSTEM or UNDO tablespace

Step:
1. shutdown database
shutdown immediate;

2. move datafile old to new location through OS command.
mv old location new location

3. startup database with mount stage.
startup mount;

4. rename datafile in database
alter database
rename file 'old location' to 'new location';

5. open database for normal use
alter database open;


We can't relocate tempfile, if we need to relocate tempfile then best option is recreate temp tablespace with new location.

How to move REDO LOG MEMBER from old to new location

Step:
1. shutdown database
shutdown immediate;

2. mv redo log member from old to new location
mv oldlocation newlocation

3. startup database with mount stage
startup mount;

4. rename redo log member in database
alter database rename file 'old location' to 'new location';

5. open database for normal use
alter database open;

How to move control file old to new location

Step:
1. shutdown database
shutdown immediate;

2. mv controlfile to old to new location
mv old location new location

3. edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'

4. recreate SPFILE from PFILE
create spfile from pfile;

5. startup database
startup;