Tuesday, December 30, 2008

Move Tempfile and Online logs to a new location

One partition on the disk was almost full and I had to move database files around. This is what I did on Tempfile and Redo logs without re-creating control file. It also shows a way to re-size the tempfile (sometimes, you have to do that because it becomes too big).

SQL> conn / as sysdba
Connected.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 780056 bytes
Variable Size 274995432 bytes
Database Buffers 260046848 bytes
Redo Buffers 1048576 bytes
Database mounted.

-- Drop the tempfile when the database is mounted
SQL> alter database tempfile '/var/oracle/oradata/emrep/temp01.dbf' drop including datafiles;

Database altered.

-- Rename the online redo logs. Make sure you copied all them to the new loaction before issuing below lines to avoid ORA-27037 error.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo01.log'
2 to '/var/oracle/oradata/emrep/redo01.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo02.log'
2 to '/var/oracle/oradata/emrep/redo02.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo03.log'
2 to '/var/oracle/oradata/emrep/redo03.log';
Database altered.

SQL> alter database rename file '/app/oracle/oradata/emrep/redo04.log'
2 to '/var/oracle/oradata/emrep/redo04.log';
Database altered.

-- Open the database
SQL> alter database open;
Database altered.

-- Run "SQL> select * from v$logfile;" to verify all redo logs are in "ONLINE" status.

-- Verify the tempfile was dropped
SQL> select * from dba_temp_files;
no rows selected

-- Add tempfile to the new location. Make sure there is no same file in the location to avoid over-writing an existin one!
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/emrep/temp01.dbf'
2 SIZE 8012M REUSE AUTOEXTEND ON NEXT 102400000 MAXSIZE 12000M;
Tablespace altered.

-- Verify the result
SQL> select tablespace_name, file_name, bytes/(1024*1024) from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024)
--------------- --------- -----------------
TEMP /app/oracle/oradata/emrep/temp01.dbf 8012

-- Backup the new controlfile
SQL> alter database backup controlfile to trace;
Database altered.

Metalink note 99275.1 gives more details on "alter database rename file '/old path/filename.dbf' to '/new path/filename.dbf';"

No comments: