Renaming or relocating Oracle datafiles prior to Oracle 11g was not straight forward process. You had to perform 4 steps.
1. Take the tablespace offline.
2. Rename or relocate using OS
3. Issue ” ALTER DATABASE RENAME FILE” command. All that this command did was update the control files.
4. Bring the tablespace online.
Starting with Oracle12c, All you need is one single command and the best thing is that this is done online. In fact this command will work only on online datafiles. The new command is “ALTER DATABASE MOVE DATAFILE ‘file1′ TO ‘file2′;
This command copies/renames the datafile to new location, updates the controlfile and deletes the old datafile. You also have options to retain the old datafile with “KEEP” option but as far the database goes, the retained file is not part of database.
See example below
15:52:56 SQL> create tablespace myts datafile ‘/mydb06/oradata/mydb/
Tablespace created.
Elapsed: 00:00:55.01
15:54:48 SQL> alter database move datafile ‘/mydb06/oradata/mydb/
Database altered.
Elapsed: 00:05:32.34 ==> 5 minutes to copy & rename the file
16:37:32 SQL> select name , status from v$datafile where name like ‘%ts%’;
NAME STATUS
———————————————————————————-
/mydb05/oradata/mydb/
Elapsed: 00:00:00.02
Major Drawback
Renaming files using offline (pre-12c) approach is much faster if the datafiles need to be just renamed on the same filesystem and not relocated. With the new approach, Oracle makes a copy of datafile irrespective of whether KEEP option is specified or not. This can be achieved very fast with OS “mv” command using the offline(pre-12c) approach. So if you can afford to take the files offline and all you want is rename and not relocate, use the offline approach as it is very fast.
16:37:51 SQL> alter database move datafile ‘/mydb05/oradata/mydb/
Database altered.
Elapsed: 00:05:46.57
16:54:26 SQL>
-bash-3.2$ ls -latr
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:08 myts01.dbf
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:51 mytabs01.dbf
-bash-3.2$
Things to know before using this command
Dataguard
Move operations on the primary or standby is independent of each other
Flashback
Flashback operations will retain the new location of datafile irrespective of the flashback time.
OS
Old files are not deleted on Windows
1. Take the tablespace offline.
2. Rename or relocate using OS
3. Issue ” ALTER DATABASE RENAME FILE” command. All that this command did was update the control files.
4. Bring the tablespace online.
Starting with Oracle12c, All you need is one single command and the best thing is that this is done online. In fact this command will work only on online datafiles. The new command is “ALTER DATABASE MOVE DATAFILE ‘file1′ TO ‘file2′;
This command copies/renames the datafile to new location, updates the controlfile and deletes the old datafile. You also have options to retain the old datafile with “KEEP” option but as far the database goes, the retained file is not part of database.
See example below
15:52:56 SQL> create tablespace myts datafile ‘/mydb06/oradata/mydb/
Tablespace created.
Elapsed: 00:00:55.01
15:54:48 SQL> alter database move datafile ‘/mydb06/oradata/mydb/
Database altered.
Elapsed: 00:05:32.34 ==> 5 minutes to copy & rename the file
16:37:32 SQL> select name , status from v$datafile where name like ‘%ts%’;
NAME STATUS
———————————————————————————-
/mydb05/oradata/mydb/
Elapsed: 00:00:00.02
Major Drawback
Renaming files using offline (pre-12c) approach is much faster if the datafiles need to be just renamed on the same filesystem and not relocated. With the new approach, Oracle makes a copy of datafile irrespective of whether KEEP option is specified or not. This can be achieved very fast with OS “mv” command using the offline(pre-12c) approach. So if you can afford to take the files offline and all you want is rename and not relocate, use the offline approach as it is very fast.
16:37:51 SQL> alter database move datafile ‘/mydb05/oradata/mydb/
Database altered.
Elapsed: 00:05:46.57
16:54:26 SQL>
-bash-3.2$ ls -latr
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:08 myts01.dbf
-rw-r—– 1 oracle dba 21474844672 Aug 11 16:51 mytabs01.dbf
-bash-3.2$
Things to know before using this command
Dataguard
Move operations on the primary or standby is independent of each other
Flashback
Flashback operations will retain the new location of datafile irrespective of the flashback time.
OS
Old files are not deleted on Windows
No comments:
Post a Comment