We can achieve this task in two
ways..ie,
1.Offline method. 2.Online
method.
To rename/relocate the
datafile from the original location to new location, tablespace should be
offline first. otherwise the following error is encountered.
ORA-01511:
error in renaming log/data files
ORA-01121: cannot rename database file <string> – file is in use or recoveryORA-01110: data file <string>: ‘datafile.dbf’
ORA-01121: cannot rename database file <string> – file is in use or recoveryORA-01110: data file <string>: ‘datafile.dbf’
Method
1:
- Login to SQLPlus.
- Connect as SYS DBA with CONNECT / AS SYSDBA command.
- Shutdown the database instance with SHUTDOWN command.
- Rename or/and move the datafiles at operating system level.
- Start Oracle database in mount state with STARTUP MOUNT command.
- Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
- Open Oracle database instance completely with ALTER DATABASE OPEN command.
$
sqlplus sys/oracle@TNS as sysdba
SQL>shutdown
immediate
$mv
/u01/app/oradata/users01.dbf /u02/app/oradata/users01.dbf
$ sqlplus
sys/oracle@TNS as sysdba
SQL>startup
mount;
SQL>
ALTER DATABASE RENAME FILE '/u01/app/oradata/users01.dbf ' TO
'/u02/app/oradata/users01.dbf ';
SQL>
alter database open;
If the datafiles that
need to be changed or moved do not belong to SYSTEM tablespaces, and do not
contain active rollback segments or temporary segments, there is another
workaround that does not require database instance to be shutdown. Instead,
only the particular tablespace that contains the date files is taken offline.
Method
2:
- Login to SQLPlus.
- Connect as SYS DBA with CONNECT / AS SYSDBA command.
- Make offline the affected tablespace with ALTER TABLESPACE <tablespace name> OFFLINE;
- Modify the name or location of datafiles in Oracle data dictionary using following command syntax:
SQL>ALTER
TABLESPACE USERS RENAME DATAFILE '/u01/app/oradata/users01.dbf ' TO
'/u02/app/oradata/users01.dbf ';
- Bring the tablespace online again with ALTER TABLESPACE <tablespace name> ONLINE;
$ sqlplus sys/oracle@TNS as sysdba
SQL>ALTER TABLESPACE USERS OFFLINE;
SQL>ALTER TABLESPACE USERS RENAME DATAFILE '/u01/app/oradata/users01.dbf ' TO '/u02/app/oradata/users01.dbf ';
SQL>ALTER TABLESPACE USERS ONLINE;
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment