Oracle Version: 11g
OS Version: Rhel 6
Step 1. Check the datafiles location & tablespace name .
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE
/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf GHHSTORE_DATA_TBS
SQL> select file_name , tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/prim/temp01.dbf TEMP
Step 2.Shutdown the database and move the data files into new location.
SQL>shut immediate;
SQL>! mv /u01/app/oracle/oradata/prim/users01.dbf /u02/oradata/users01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/undotbs01.dbf /u02/oradata/undotbs01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/sysaux01.dbf /u02/oradata/sysaux01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/system01.dbf /u02/oradata/system01.dbf
SQL>! mv /u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf /u02/oradata/GHHSTORE_DATA_TBS.dbf
SQL>! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf
Step 3. Start the database in mount point and rename the datafiles.
SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/undotbs01.dbf' to
'/u02/oradata/undotbs01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to
'/u02/oradata/sysaux01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/system01.dbf' to
'/u02/oradata/system01.dbf';
SQL> alter database rename file '/u01/app/oracle/oradata/prim/GHHSTORE_DATA_TBS.dbf' to
'/u02/oradata/GHHSTORE_DATA_TBS.dbf' ;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';
Step 4.Open the database and check datafile location.
SQL>alter database open;
SQL> column file_name format a50
SQL> select file_name , tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u02/oradata/users01.dbf USERS
/u02/oradata/undotbs01.dbf UNDOTBS1
/u02/oradata/sysaux01.dbf SYSAUX
/u02/oradata/system01.dbf SYSTEM
/u02/oradata/example01.dbf EXAMPLE
/u02/oradata/GHHSTORE_DATA_TBS.dbf GHHSTORE_DATA_TBS
SQL> select file_name , tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
---------------------------------- ------------------------------
/u02/oradata/temp01.dbf TEMP
Done..
Incase if we cant shutdown the database, we can still perform the activity except for system datafile
and undo datafile.However we can change the default undo tablespace by creating a new undo tablespace
and drop the old one.
For users or any non-default system datafile:-
SQL> alter tablespace users offline;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/users01.dbf' to
'/u02/oradata/users01.dbf';
SQL> alter tablespace users online;
SQL> select file_name , online_status , tablespace_name from dba_data_files where tablespace_name='USERS';
FILE_NAME ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/users01.dbf ONLINE USERS
For sysaux datafile:-
SQL> alter tablespace sysaux offline;
Tablespace altered.
SQL> ! mv /u01/app/oracle/oradata/prim/sysaux01.dbf /u02/oradata/sysaux01.dbf;
SQL> alter database rename file '/u01/app/oracle/oradata/prim/sysaux01.dbf' to '/u02/oradata/sysaux01.dbf';
Database altered.
SQL> alter tablespace sysaux online;
SQL> select file_name , online_status , tablespace_name from dba_data_files where tablespace_name='SYSAUX';
FILE_NAME ONLINE_ TABLESPACE_NAME
-------------------------------------------------- ------- ------------------------------
/u02/oradata/sysaux01.dbf ONLINE SYSAUX
For temporary datafile:-
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;
FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- ---------------------- -------
/u01/app/oracle/oradata/prim/temp01.dbf TEMP ONLINE
SQL> alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' offline;
Database altered.
SQL> ! mv /u01/app/oracle/oradata/prim/temp01.dbf /u02/oradata/temp01.dbf
SQL> alter database rename file '/u01/app/oracle/oradata/prim/temp01.dbf' to
'/u02/oradata/temp01.dbf';
Database altered.
SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_TEMP_FILES;
FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- ---------------------- -------
/u02/oradata/temp01.dbf TEMP OFFINE
SQL> alter database tempfile '/u02/oradata/temp01.dbf' online;
Database altered.
SQL> select file_name, tablespace_name , status from dba_temp_files;
FILE_NAME TABLESPACE_NAME STATUS
---------------------------------------- ------------------------------ -------
/u02/oradata/temp01.dbf TEMP ONLINE
For system Datafile:-
We cant offline a system tablespace. so for the we need to shutdown the database and then
move it to the new location as describe before.
For undo datafile:-
We cant drop or move default undo tablespace.
First create a new undo tablespace and then drop the old one.
SQL> create undo tablespace undotbs2 datafile '/u02/oradata/undotbs01.dbf' size 100M;
Tablespace created.
SQL> alter system set undo_tablespace= undotbs2 ;
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL>select file_name , online_status , tablespace_name from dba_data_files where tablespace_name like 'UNDO%';
FILE_NAME ONLINE_ TABLESPACE_NAME
---------------------------------------- ------- ------------------------------
/u02/oradata/undotbs01.dbf ONLINE UNDOTBS2
Please share your ideas and opinions about this topic.
If you like this post, then please share with others.
Please subscribe on email for every updates on mail.
No comments:
Post a Comment