Move datafiles(system,sysaux,users,undo,temp) from one location to another with least downtime of database.
Perform some tasks...................................................
............................how to know thw how many tablesapes in our database
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
6 rows selected.
...................................To know the data file location.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/redoa/system01.dbf
/redob/example01.dbf
/redoc/sysaux01.dbf
/temptbs_1/undotbs01.dbf
/u01/app/users01.dbf
...................................To know the data file and associated tablesapce name with location.
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/users01.dbf
UNDOTBS1
/temptbs_1/undotbs01.dbf
SYSAUX
/redoc/sysaux01.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/redoa/system01.dbf
EXAMPLE
/redob/example01.dbf
........................................................................
...............how to change the location of datafile without shutdown the database................................
.........1 offline the tablespce......
SQL> alter tablespace users offline
2 ;
Tablespace altered.
..........2.Os lavel copy the datafile to another location where we want........................................................
cp /u01/app/users01.dbf /u01/app/oracle/oradata/dborcl/users01.dbf
...................3 alter the datafile...........................
SQL> alter tablespace users rename datafile '/u01/app/users01.dbf' to '/u01/app/oracle/oradata/dborcl/users01.dbf'
2 ;
Tablespace altered.
........................4 online the tablespace...........................
SQL> alter tablespace users online;
Tablespace altered.
..........................................
..........................................................1 offline the tablespce...
.SQL> alter tablespace example offline;
Tablespace altered.
2.Os lavel copy the datafile to another location where we want
[oracle@ab ~]$ cp /redob/example01.dbf /u01/app/oracle/oradata/dborcl/example01.dbf
...................3 alter the datafile...........................
SQL> alter tablespace example rename datafile '/redob/example01.dbf' to '/u01/app/oracle/oradata/dborcl/example01.dbf';
Tablespace altered.
........................4 online the tablespace...........................
SQL> alter tablespace example online;
Tablespace altered.
.............................................1 offline the tablespce................................
................................
SQL> alter tablespace SYSAUX offline;
2.Os lavel copy the datafile to another location where we want
[oracle@ab ~]$ cp /redoc/sysaux01.dbf /u01/app/oracle/oradata/dborcl/sysaux01.dbf
...................3 alter the datafile...........................
SQL> alter tablespace SYSAUX rename datafile '/redoc/sysaux01.dbf' to '/u01/app/oracle/oradata/dborcl/sysaux01.dbf';
Tablespace altered.
........................4 online the tablespace...........................
SQL> alter tablespace SYSAUX online;
Tablespace altered.
...................................................................................................
.....................new location of the datafile after ........................
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS..................
/u01/app/oracle/oradata/dborcl/users01.dbf
UNDOTBS1
/temptbs_1/undotbs01.dbf
SYSAUX
/u01/app/oracle/oradata/dborcl/sysaux01.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/redoa/system01.dbf
EXAMPLE
/u01/app/oracle/oradata/dborcl/example01.dbf
.....................................................................................................
...........................How can rename the datafile of sysem and undotbs...............
.....1...system tablespace cannot be brought offline; shut down if necessary
.....2 Cannot offline the undo tablespace
......3 so shut down the database and then rename the both tablespaces
SQL> alter tablespace UNDOTBS1 offline;
alter tablespace UNDOTBS1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
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
..................................................
...1..........................shut the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
.....2....................copy the data file location where we want
[oracle@ab ~]$ cp /redoa/system01.dbf /u01/app/oracle/oradata/dborcl/system01.dbf
[oracle@ab ~]$ cp /temptbs_1/undotbs01.dbf /temptbs_1/dborcl/temp01.dbf
.....3..................startup database in mount
SQL> startup mount
ORACLE instance started.
Total System Global Area 885211136 bytes
Fixed Size 2294456 bytes
Variable Size 775949640 bytes
Database Buffers 100663296 bytes
Redo Buffers 6303744 bytes
Database mounted.
....4.......alter the datafile........
SQL> alter database rename file '/redoa/system01.dbf' to '/u01/app/oracle/oradata/dborcl/system01.dbf';
Database altered.
SQL> alter database rename file '/temptbs_1/undotbs01.dbf' to '/temptbs_1/dborcl/temp01.dbf';
....5.........................open the database................................................................
SQL> alter database open;
Database altered.
...................How can rename the tempfile .......................................................................................
Temporary Tablespace cant not be offline so offline its datafile
SQL> alter database tempfile '/undotbs_1/temp01.dbf' offline;
Database altered.
........2....From os level copy the tempfile to another location...........
[oracle@ab ~]$ cp /undotbs_1/temp01.dbf /temptbs_1/dborcl/temp02.dbf
.........3............renaming the tempfile.......
SQL> alter database rename file '/undotbs_1/temp01.dbf' to '/temptbs_1/dborcl/temp02.dbf';
Database altered.
.....4.......online the temfile with new location..........
SQL> alter database tempfile '/temptbs_1/dborcl/temp02.dbf' online;
Database altered.
..................................................................................................................
..........Now Check the location of all datafiles;
..........................................................
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/oracle/oradata/dborcl/users01.dbf
UNDOTBS1
/temptbs_1/dborcl/temp01.dbf
SYSAUX
/u01/app/oracle/oradata/dborcl/sysaux01.dbf
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/u01/app/oracle/oradata/dborcl/system01.dbf
EXAMPLE
/u01/app/oracle/oradata/dborcl/example01.dbf
..................................................................................
......................Location 0f temfile...
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/temptbs_1/dborcl/temp02.dbf
No comments:
Post a Comment