Moving Redo Log files

Oracle redo log groups should have two members per group with each member located on different physical storage devices to ensure optimal performance and recoverability.
This procedure covers the movement of existing redo logs to different devices in Oracle 10.2, however it can be used in several other versions.
1. Obtain the current location and log file names and groups in the V$LOGFILE view.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
—— ——- ——- ——————–
3 ONLINE /u05/oradata/DB1/redo03.log
2 ONLINE /u05/oradata/DB1/redo02.log
1 ONLINE /u05/oradata/DB1/redo01.log
2 Logon to Oracle with sysdba privileges and shutdown immediate.
mylinux# sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 11 13:32:22 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
3. At the OS level, move the log files to their new locations.
mylinux# mv /u05/oradata/DB1/redo03.log /u03/oradata/DB1/redo03.log
mylinux# mv /u05/oradata/DB1/redo02.log /u02/oradata/DB1/redo02.log
4. Logon to Oracle with sysdba privileges and startup the database in mount mode.
mylinux# sqlplus /nolog
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Dec 11 13:38:12 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2028568 bytes
Variable Size 201329640 bytes
Database Buffers 104857600 bytes
Redo Buffers 6356992 bytes
Database mounted.
SQL>
5. Issue an alter database command to rename the log files to their new locations.
SQL> ALTER DATABASE
2 RENAME FILE ‘/u05/oradata/DB1/redo03.log’
3 TO ‘/u03/oradata/DB1/redo03.log’;
Database altered.
SQL> ALTER DATABASE
2 RENAME FILE ‘/u05/oradata/DB1/redo02.log’
3 TO ‘/u02/oradata/DB1/redo02.log’;
Database altered.
SQL>
6. Now you can open the database for use.
SQL> alter database open;
Database altered.
SQL>
7. Reselect from the view V$LOGFILE shows the redo log files in their new locations.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
—— ——- ——- ——————–
3 ONLINE /u03/oradata/DB1/redo03.log
2 ONLINE /u02/oradata/DB1/redo02.log
1 ONLINE /u05/oradata/DB1/redo01.log

Oracle Temporary Tablespace Movement

Temporary tablespace’s in the Oracle environment are used for sorting and other non-permanent data operations. Although the space in a temporary tablespace is not permanent, a database of any size cannot properly function without the use of very large amounts of temporary tablespace. This procedure covers the movement of a temporary tablespace after space on the original storage device becomes insufficient. We will cover the movement of the TEMPORARY tablespace from the mount point data1 to mount point data3.
1. Find all tablespaces defined as TEMPORARY
SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’;
TABLESPACE_NAME
——————————
TEMP
TEMPORARY
SQL>
2. Locate the temp_files in the temporary tablespace.
SQL> select file_name from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME
/data1/temporary01.dbf
/data1/temporary02.dbf
/data1/temporary03.dbf
SQL>
3. Find size of current files
SQL> select file_name, bytes/1024/1024 from dba_temp_files where tablespace_name = ‘TEMPORARY’;
FILE_NAME BYTES/1024/1024
—————— —————
/data1/temporary01.dbf 4096
/data1/temporary02.dbf 2048
/data1/temporary03.dbf 2048
SQL>
4. Add new temp files on your new mount point to the tablespace temporary with the command ‘alter tablespace’.
SQL> alter tablespace temporary add tempfile ‘/data3/temporary01.dbf’
size 4096m;
Tablespace altered.
SQL> alter tablespace temp add tempfile ‘/data3/temprary02.dbf’
size 2048m;
Tablespace altered.
SQL> alter tablespace temp add tempfile ‘/data3/temprary03.dbf’
size 2048m;
Tablespace altered.
5. Shutdown database to free all in-use segments of the temporary tablespace TEMPORARY with the command ‘shutdown immediate’.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
6. Startup the database in restricted mode using the command ‘startup restict’ so no user activity can pickup segments from the tablespace TEMPORARY.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 159386500 bytes
Database Buffers 444596224 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
7. Drop the old datafiles using the command ‘alter database’
SQL> alter database tempfile ‘/data1/temprary01.dbf’
drop including datafiles;
Database altered.
SQL> alter database tempfile ‘/data1/temprary02.dbf’
drop including datafiles;
Database altered.
SQL> alter database tempfile ‘/data1/temprary03.dbf’
drop including datafiles;
Database altered.
8. View the new temp files with the following command.
SQL> select file_name from dba_temp_files where tablespace_name=’TEMPORARY’;
FILE_NAME
———————–
/data3/temprary01.dbf
/data3/temprary02.dbf
/data3/temprary03.dbf
SQL>
9. Open the database for normal use with ‘alter system’ command.
SQL> alter system disable restricted session;
System altered.
SQL>
This completes the movement of temporary tablespaces tempfiles.