How to move all the datafiles, redo logs, tempfiles, undo datafile and controlfiles in a database
There are sometimes when you need to move the whole oracle database to another location which includes moving all datafiles (system, undo, sysaux and other datafiles), redo logs, temp files and controlfiles.
Please note that I am assuming that there are no queries running on the database and I am the only user connected to the database.
1. Moving all datafiles expect system datafiles
2. Moving oracle temp datafile
3. Moving oracle undo tablespace/datafile
4. Moving oracle system datafiles
5. Moving oracle redo log files
6. Moving oracle controlfiles
Moving all datafiles expect system datafiles:
select file_name, tablespace_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example02.dbf
MICHAEL /u01/app/oracle/oradata/orcl/michael01.dbf
MICHAEL /u01/app/oracle/oradata/orcl/michael02.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS /u01/app/oracle/oradata/orcl/temp/users01.dbf
USERS /u01/app/oracle/oradata/orcl/temp/users02.dbf
SQL> alter tablespace example offline;
Tablespace altered.
SQL> alter tablespace michael offline;
Tablespace altered.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> SELECT NAME, FILE#, STATUS, CHECKPOINT_CHANGE# "CHECKPOINT" FROM V$DATAFILE;
NAME FILE# STATUS CHECKPOINT
----------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM 11517939
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 ONLINE 11517939
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 ONLINE 11517939
/u01/app/oracle/oradata/orcl/temp/users01.dbf 4 OFFLINE 11536249
/u01/app/oracle/oradata/orcl/example01.dbf 5 OFFLINE 11536178
/u01/app/oracle/oradata/orcl/example02.dbf 6 OFFLINE 11536178
/u01/app/oracle/oradata/orcl/michael01.dbf 7 OFFLINE 11536204
/u01/app/oracle/oradata/orcl/temp/users02.dbf 8 OFFLINE 11536249
/u01/app/oracle/oradata/orcl/michael02.dbf 9 OFFLINE 11536204
$ cp /u01/app/oracle/oradata/orcl/temp/users01.dbf /u01/app/oracle/oradata/new_location/users01.dbf
$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/new_location/example01.dbf
$ cp /u01/app/oracle/oradata/orcl/example02.dbf /u01/app/oracle/oradata/new_location/example02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael01.dbf /u01/app/oracle/oradata/new_location/michael01.dbf
$ cp /u01/app/oracle/oradata/orcl/temp/users02.dbf /u01/app/oracle/oradata/new_location/users02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael02.dbf /u01/app/oracle/oradata/new_location/michael02.dbf
$ cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/new_location/sysaux01.dbf
SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users01.dbf' to '/u01/app/oracle/oradata/new_location/users01.dbf';
SQL>
Tablespace altered.
SQL> alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';
Enter value for ts_name: users
Enter value for old_location: /u01/app/oracle/oradata/orcl/temp/users02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/users02.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users02.dbf' to '/u01/app/oracle/oradata/new_location/users02.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example01.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example01.dbf' to '/u01/app/oracle/oradata/new_location/example01.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example02.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example02.dbf' to '/u01/app/oracle/oradata/new_location/example02.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael01.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael01.dbf' to '/u01/app/oracle/oradata/new_location/michael01.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael02.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael02.dbf' to '/u01/app/oracle/oradata/new_location/michael02.dbf'
Tablespace altered.
SQL> alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';
Enter value for ts_name: sysaux
Enter value for old_location: /u01/app/oracle/oradata/orcl/sysaux01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/sysaux01.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace sysaux rename datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/new_location/sysaux01.dbf'
Tablespace altered.
SQL> alter tablespace michael online;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> alter tablespace example online;
Tablespace altered.
SQL> alter tablespace sysaux offline;
Tablespace altered.
SQL> SELECT NAME, FILE#, STATUS, CHECKPOINT_CHANGE# "CHECKPOINT" FROM V$DATAFILE;
NAME FILE# STATUS CHECKPOINT
-------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM 11517939
/u01/app/oracle/oradata/new_location/sysaux01.dbf 2 ONLINE 11517939
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 ONLINE 11517939
/u01/app/oracle/oradata/new_location/users01.dbf 4 ONLINE 11536743
/u01/app/oracle/oradata/new_location/example01.dbf 5 ONLINE 11536772
/u01/app/oracle/oradata/new_location/example02.dbf 6 ONLINE 11536772
/u01/app/oracle/oradata/new_location/michael01.dbf 7 ONLINE 11536718
/u01/app/oracle/oradata/new_location/users02.dbf 8 ONLINE 11536743
/u01/app/oracle/oradata/new_location/michael02.dbf 9 ONLINE 11536718
Moving oracle temp datafile:
SQL> SELECT FILE_NAME , TABLESPACE_NAME FROM dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- --------------------
SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/new_location/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> SELECT FILE_NAME,TABLESPACE_NAME FROM dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- --------------------
/u01/app/oracle/oradata/new_location/temp01.dbf TEMP
Moving oracle undo tablespace/datafile:
SQL> alter system set undo_tablespace = '';
System altered.
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/new_location/undotbs01.dbf' size 500M reuse autoextend on maxsize 2000M;
Tablespace created.
SQL> alter system set undo_tablespace = 'UNDOTBS1';
System altered.
select file_name, tablespace_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
EXAMPLE /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS /u01/app/oracle/oradata/new_location/users01.dbf
USERS /u01/app/oracle/oradata/new_location/users02.dbf
Moving oracle system datafiles:
Moving system file is little bit tricky we can not bring the system tablespace offline and then copy the files from old to new location and then rename.
SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
Below steps will recreate the system datafile at new location:
SQL> alter database backup controlfile to trace;
Database altered.
SQL> !ls -laht /u01/app/oracle/diag/rdbms/orcl/orcl/trace | head -10
total 10M
drwxr-x--- 2 oracle oinstall 52K Feb 7 13:12 .
-rw-r----- 1 oracle oinstall 515K Feb 7 13:10 alert_orcl.log
-rw-r----- 1 oracle oinstall 7.3K Feb 7 13:10 orcl_ora_16748.trc
-rw-r----- 1 oracle oinstall 131 Feb 7 13:10 orcl_ora_16748.trm
-rw-r----- 1 oracle oinstall 843 Feb 7 13:06 orcl_ora_17506.trc
-rw-r----- 1 oracle oinstall 60 Feb 7 13:06 orcl_ora_17506.trm
-rw-r----- 1 oracle oinstall 881 Feb 7 13:05 orcl_dbrm_16711.trc
-rw-r----- 1 oracle oinstall 60 Feb 7 13:05 orcl_dbrm_16711.trm
-rw-r----- 1 oracle oinstall 840 Feb 7 13:03 orcl_mman_16717.trc
SQL> !cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
.
.
.
.
.
copy the command from startup mount to the semicomma (;)
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/new_location/sysaux01.dbf',
'/u01/app/oracle/oradata/new_location/undotbs01.dbf',
'/u01/app/oracle/oradata/new_location/users01.dbf',
'/u01/app/oracle/oradata/new_location/example01.dbf',
'/u01/app/oracle/oradata/new_location/example02.dbf',
'/u01/app/oracle/oradata/new_location/michael01.dbf',
'/u01/app/oracle/oradata/new_location/users02.dbf',
'/u01/app/oracle/oradata/new_location/michael02.dbf'
CHARACTER SET WE8MSWIN1252
;
Then change the line '/u01/app/oracle/oradata/orcl/system01.dbf' with the new location which is '/u01/app/oracle/oradata/new_location/system01.dbf' do the following steps.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/new_location/system01.dbf
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 385877640 bytes
Database Buffers 142606336 bytes
Redo Buffers 5840896 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 1168
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/new_location/system01.dbf',
14 '/u01/app/oracle/oradata/new_location/sysaux01.dbf',
15 '/u01/app/oracle/oradata/new_location/undotbs01.dbf',
16 '/u01/app/oracle/oradata/new_location/users01.dbf',
17 '/u01/app/oracle/oradata/new_location/example01.dbf',
18 '/u01/app/oracle/oradata/new_location/example02.dbf',
19 '/u01/app/oracle/oradata/new_location/michael01.dbf',
20 '/u01/app/oracle/oradata/new_location/users02.dbf',
21 '/u01/app/oracle/oradata/new_location/michael02.dbf'
22 CHARACTER SET WE8MSWIN1252
23 ;
Control file created.
SQL> alter database open;
Database altered.
select file_name, tablespace_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
EXAMPLE /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/new_location/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/new_location/undotbs01.dbf
USERS /u01/app/oracle/oradata/new_location/users01.dbf
USERS /u01/app/oracle/oradata/new_location/users02.dbf
Moving oracle redo log files:
SQL> select lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1;
MEMBER
----------------------------------------
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo03.log
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/new_location/redo01.log
SQL> !cp /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/new_location/redo02.log
SQL> !cp /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/new_location/redo03.log
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 385877640 bytes
Database Buffers 142606336 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> alter database rename file '&old_redo_file' to '&new_redo_file';
Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo01.log
Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo01.log
old 1: alter database rename file '&old_redo_file' to '&new_redo_file'
new 1: alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/new_location/redo01.log'
Database altered.
SQL> /
Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo02.log
Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo02.log
old 1: alter database rename file '&old_redo_file' to '&new_redo_file'
new 1: alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/new_location/redo02.log'
Database altered.
SQL> /
Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo03.log
Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo03.log
old 1: alter database rename file '&old_redo_file' to '&new_redo_file'
new 1: alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/new_location/redo03.log'
Database altered.
SQL> alter database open;
Database altered.
SQL> select lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1;
MEMBER
------------------------------------------------
/u01/app/oracle/oradata/new_location/redo01.log
/u01/app/oracle/oradata/new_location/redo02.log
/u01/app/oracle/oradata/new_location/redo03.log
Moving oracle controlfiles:
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
All the files are moved to new directory
SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
There are sometimes when you need to move the whole oracle database to another location which includes moving all datafiles (system, undo, sysaux and other datafiles), redo logs, temp files and controlfiles.
Please note that I am assuming that there are no queries running on the database and I am the only user connected to the database.
1. Moving all datafiles expect system datafiles
2. Moving oracle temp datafile
3. Moving oracle undo tablespace/datafile
4. Moving oracle system datafiles
5. Moving oracle redo log files
6. Moving oracle controlfiles
Moving all datafiles expect system datafiles:
select file_name, tablespace_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
EXAMPLE /u01/app/oracle/oradata/orcl/example02.dbf
MICHAEL /u01/app/oracle/oradata/orcl/michael01.dbf
MICHAEL /u01/app/oracle/oradata/orcl/michael02.dbf
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS /u01/app/oracle/oradata/orcl/temp/users01.dbf
USERS /u01/app/oracle/oradata/orcl/temp/users02.dbf
SQL> alter tablespace example offline;
Tablespace altered.
SQL> alter tablespace michael offline;
Tablespace altered.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> SELECT NAME, FILE#, STATUS, CHECKPOINT_CHANGE# "CHECKPOINT" FROM V$DATAFILE;
NAME FILE# STATUS CHECKPOINT
----------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM 11517939
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2 ONLINE 11517939
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 ONLINE 11517939
/u01/app/oracle/oradata/orcl/temp/users01.dbf 4 OFFLINE 11536249
/u01/app/oracle/oradata/orcl/example01.dbf 5 OFFLINE 11536178
/u01/app/oracle/oradata/orcl/example02.dbf 6 OFFLINE 11536178
/u01/app/oracle/oradata/orcl/michael01.dbf 7 OFFLINE 11536204
/u01/app/oracle/oradata/orcl/temp/users02.dbf 8 OFFLINE 11536249
/u01/app/oracle/oradata/orcl/michael02.dbf 9 OFFLINE 11536204
$ cp /u01/app/oracle/oradata/orcl/temp/users01.dbf /u01/app/oracle/oradata/new_location/users01.dbf
$ cp /u01/app/oracle/oradata/orcl/example01.dbf /u01/app/oracle/oradata/new_location/example01.dbf
$ cp /u01/app/oracle/oradata/orcl/example02.dbf /u01/app/oracle/oradata/new_location/example02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael01.dbf /u01/app/oracle/oradata/new_location/michael01.dbf
$ cp /u01/app/oracle/oradata/orcl/temp/users02.dbf /u01/app/oracle/oradata/new_location/users02.dbf
$ cp /u01/app/oracle/oradata/orcl/michael02.dbf /u01/app/oracle/oradata/new_location/michael02.dbf
$ cp /u01/app/oracle/oradata/orcl/sysaux01.dbf /u01/app/oracle/oradata/new_location/sysaux01.dbf
SQL> alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users01.dbf' to '/u01/app/oracle/oradata/new_location/users01.dbf';
SQL>
Tablespace altered.
SQL> alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';
Enter value for ts_name: users
Enter value for old_location: /u01/app/oracle/oradata/orcl/temp/users02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/users02.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace users rename datafile '/u01/app/oracle/oradata/orcl/temp/users02.dbf' to '/u01/app/oracle/oradata/new_location/users02.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example01.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example01.dbf' to '/u01/app/oracle/oradata/new_location/example01.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: example
Enter value for old_location: /u01/app/oracle/oradata/orcl/example02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/example02.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace example rename datafile '/u01/app/oracle/oradata/orcl/example02.dbf' to '/u01/app/oracle/oradata/new_location/example02.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael01.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael01.dbf' to '/u01/app/oracle/oradata/new_location/michael01.dbf'
Tablespace altered.
SQL> /
Enter value for ts_name: michael
Enter value for old_location: /u01/app/oracle/oradata/orcl/michael02.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/michael02.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace michael rename datafile '/u01/app/oracle/oradata/orcl/michael02.dbf' to '/u01/app/oracle/oradata/new_location/michael02.dbf'
Tablespace altered.
SQL> alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location';
Enter value for ts_name: sysaux
Enter value for old_location: /u01/app/oracle/oradata/orcl/sysaux01.dbf
Enter value for new_location: /u01/app/oracle/oradata/new_location/sysaux01.dbf
old 1: alter tablespace &TS_NAME rename datafile '&old_location' to '&new_location'
new 1: alter tablespace sysaux rename datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/new_location/sysaux01.dbf'
Tablespace altered.
SQL> alter tablespace michael online;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> alter tablespace example online;
Tablespace altered.
SQL> alter tablespace sysaux offline;
Tablespace altered.
SQL> SELECT NAME, FILE#, STATUS, CHECKPOINT_CHANGE# "CHECKPOINT" FROM V$DATAFILE;
NAME FILE# STATUS CHECKPOINT
-------------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM 11517939
/u01/app/oracle/oradata/new_location/sysaux01.dbf 2 ONLINE 11517939
/u01/app/oracle/oradata/orcl/undotbs01.dbf 3 ONLINE 11517939
/u01/app/oracle/oradata/new_location/users01.dbf 4 ONLINE 11536743
/u01/app/oracle/oradata/new_location/example01.dbf 5 ONLINE 11536772
/u01/app/oracle/oradata/new_location/example02.dbf 6 ONLINE 11536772
/u01/app/oracle/oradata/new_location/michael01.dbf 7 ONLINE 11536718
/u01/app/oracle/oradata/new_location/users02.dbf 8 ONLINE 11536743
/u01/app/oracle/oradata/new_location/michael02.dbf 9 ONLINE 11536718
Moving oracle temp datafile:
SQL> SELECT FILE_NAME , TABLESPACE_NAME FROM dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- --------------------
SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Database altered.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/app/oracle/oradata/new_location/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> SELECT FILE_NAME,TABLESPACE_NAME FROM dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- --------------------
/u01/app/oracle/oradata/new_location/temp01.dbf TEMP
Moving oracle undo tablespace/datafile:
SQL> alter system set undo_tablespace = '';
System altered.
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/new_location/undotbs01.dbf' size 500M reuse autoextend on maxsize 2000M;
Tablespace created.
SQL> alter system set undo_tablespace = 'UNDOTBS1';
System altered.
select file_name, tablespace_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
EXAMPLE /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
USERS /u01/app/oracle/oradata/new_location/users01.dbf
USERS /u01/app/oracle/oradata/new_location/users02.dbf
Moving oracle system datafiles:
Moving system file is little bit tricky we can not bring the system tablespace offline and then copy the files from old to new location and then rename.
SQL> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
Below steps will recreate the system datafile at new location:
SQL> alter database backup controlfile to trace;
Database altered.
SQL> !ls -laht /u01/app/oracle/diag/rdbms/orcl/orcl/trace | head -10
total 10M
drwxr-x--- 2 oracle oinstall 52K Feb 7 13:12 .
-rw-r----- 1 oracle oinstall 515K Feb 7 13:10 alert_orcl.log
-rw-r----- 1 oracle oinstall 7.3K Feb 7 13:10 orcl_ora_16748.trc
-rw-r----- 1 oracle oinstall 131 Feb 7 13:10 orcl_ora_16748.trm
-rw-r----- 1 oracle oinstall 843 Feb 7 13:06 orcl_ora_17506.trc
-rw-r----- 1 oracle oinstall 60 Feb 7 13:06 orcl_ora_17506.trm
-rw-r----- 1 oracle oinstall 881 Feb 7 13:05 orcl_dbrm_16711.trc
-rw-r----- 1 oracle oinstall 60 Feb 7 13:05 orcl_dbrm_16711.trm
-rw-r----- 1 oracle oinstall 840 Feb 7 13:03 orcl_mman_16717.trc
SQL> !cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_16748.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
.
.
.
.
.
copy the command from startup mount to the semicomma (;)
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/new_location/sysaux01.dbf',
'/u01/app/oracle/oradata/new_location/undotbs01.dbf',
'/u01/app/oracle/oradata/new_location/users01.dbf',
'/u01/app/oracle/oradata/new_location/example01.dbf',
'/u01/app/oracle/oradata/new_location/example02.dbf',
'/u01/app/oracle/oradata/new_location/michael01.dbf',
'/u01/app/oracle/oradata/new_location/users02.dbf',
'/u01/app/oracle/oradata/new_location/michael02.dbf'
CHARACTER SET WE8MSWIN1252
;
Then change the line '/u01/app/oracle/oradata/orcl/system01.dbf' with the new location which is '/u01/app/oracle/oradata/new_location/system01.dbf' do the following steps.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/oradata/orcl/system01.dbf /u01/app/oracle/oradata/new_location/system01.dbf
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 385877640 bytes
Database Buffers 142606336 bytes
Redo Buffers 5840896 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 1168
7 LOGFILE
8 GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/app/oracle/oradata/new_location/system01.dbf',
14 '/u01/app/oracle/oradata/new_location/sysaux01.dbf',
15 '/u01/app/oracle/oradata/new_location/undotbs01.dbf',
16 '/u01/app/oracle/oradata/new_location/users01.dbf',
17 '/u01/app/oracle/oradata/new_location/example01.dbf',
18 '/u01/app/oracle/oradata/new_location/example02.dbf',
19 '/u01/app/oracle/oradata/new_location/michael01.dbf',
20 '/u01/app/oracle/oradata/new_location/users02.dbf',
21 '/u01/app/oracle/oradata/new_location/michael02.dbf'
22 CHARACTER SET WE8MSWIN1252
23 ;
Control file created.
SQL> alter database open;
Database altered.
select file_name, tablespace_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
EXAMPLE /u01/app/oracle/oradata/new_location/example01.dbf
EXAMPLE /u01/app/oracle/oradata/new_location/example02.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael01.dbf
MICHAEL /u01/app/oracle/oradata/new_location/michael02.dbf
SYSAUX /u01/app/oracle/oradata/new_location/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/new_location/system01.dbf
UNDOTBS1 /u01/app/oracle/oradata/new_location/undotbs01.dbf
USERS /u01/app/oracle/oradata/new_location/users01.dbf
USERS /u01/app/oracle/oradata/new_location/users02.dbf
Moving oracle redo log files:
SQL> select lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1;
MEMBER
----------------------------------------
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo03.log
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/oradata/orcl/redo01.log /u01/app/oracle/oradata/new_location/redo01.log
SQL> !cp /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/new_location/redo02.log
SQL> !cp /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/new_location/redo03.log
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 385877640 bytes
Database Buffers 142606336 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> alter database rename file '&old_redo_file' to '&new_redo_file';
Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo01.log
Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo01.log
old 1: alter database rename file '&old_redo_file' to '&new_redo_file'
new 1: alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/new_location/redo01.log'
Database altered.
SQL> /
Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo02.log
Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo02.log
old 1: alter database rename file '&old_redo_file' to '&new_redo_file'
new 1: alter database rename file '/u01/app/oracle/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/new_location/redo02.log'
Database altered.
SQL> /
Enter value for old_redo_file: /u01/app/oracle/oradata/orcl/redo03.log
Enter value for new_redo_file: /u01/app/oracle/oradata/new_location/redo03.log
old 1: alter database rename file '&old_redo_file' to '&new_redo_file'
new 1: alter database rename file '/u01/app/oracle/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/new_location/redo03.log'
Database altered.
SQL> alter database open;
Database altered.
SQL> select lf.member from v$logfile lf, v$log lg where lg.group# = lf.group# order by 1;
MEMBER
------------------------------------------------
/u01/app/oracle/oradata/new_location/redo01.log
/u01/app/oracle/oradata/new_location/redo02.log
/u01/app/oracle/oradata/new_location/redo03.log
Moving oracle controlfiles:
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
All the files are moved to new directory
SQL> alter system set control_files='/u01/app/oracle/oradata/new_location/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope=spfile;
System altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
flash_recovery_area/orcl/contr
ol02.ctl
SQL> select name from v$controlfile;
NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
No comments:
Post a Comment