Sep 17

12c Move Datafiles Online

Prior to Oracle 12c, if you wanted to move a database’s file, you either had to shutdown the database, or take the datafile/tablespace offline. Here is an example of the steps you might take:

  1. ALTER TABLESPACE my_ts OFFLINE;
  2. !mv /old_dir/my_ts01.dbf /new_dir/my_ts01.dbf
  3. ALTER DATABASE RENAME FILE ‘/old_dir/my_ts01.dbf’ TO ‘/old_dir/my_ts01.dbf’;
  4. ALTER TABLESPACE my_ts ONLINE;

Now in Oracle 12c, you can do this quite simply with one command and another user can be accessing the file at the same time. To illustrate this, I will create a tablespace, and while moving it have another session access the file.

SQL> create tablespace move_me
2  datafile '/u01/app/oracle/oradata/ora12c/move_me01.dbf' size 5g;
Tablespace created.
SQL> create table system.test_tab (id number) tablespace move_me;
Table created.
SQL> insert into system.test_tab values (10);
1 row created.
SQL> commit;
Commit complete.

Now let’s begin the move of this file.

SQL> alter database move datafile
  2  '/u01/app/oracle/oradata/ora12c/move_me01.dbf' to
  3  '/u02/app/oracle/oradata/ora12c/move_me01.dbf';

At the same time, in another session, let’s insert a row into the only table in this tablespace.

SQL> insert into system.test_tab values (20);
1 row created.
SQL> commit;
Commit complete.

As you can see, the transaction was allowed to complete even though we are in the middle of moving the file.

While the file was moving I looked at the contents of both mount points.

[oracle@msp-test-ora12 ora12c]$ ls -l /u01/app/oracle/oradata/ora12c
total 8769660
-rw-r----- 1 oracle oinstall   10043392 Sep 17 10:52 control01.ctl
-rw-r----- 1 oracle oinstall   10043392 Sep 17 10:52 control02.ctl
-rw-r----- 1 oracle oinstall 5368717312 Sep 17 10:52 move_me01.dbf
-rw-r----- 1 oracle oinstall   52429312 Sep 17 10:52 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Sep 16 22:00 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Sep 17 02:00 redo03.log
-rw-r----- 1 oracle oinstall 1090527232 Sep 17 10:52 sysaux01.dbf
-rw-r----- 1 oracle oinstall  734011392 Sep 17 10:52 system01.dbf
-rw-r----- 1 oracle oinstall   68165632 Sep 17 10:45 temp01.dbf
-rw-r----- 1 oracle oinstall  471867392 Sep 17 10:52 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Sep 17 02:06 users01.dbf
[oracle@msp-test-ora12 ora12c]$  ls -l /u02/app/oracle/oradata/ora12c
total 684044
-rw-r----- 1 oracle oinstall 5368717312 Sep 17 10:52 move_me01.dbf

tadalafil online australia Earlier, most men were shy to talk about their problem. As researchers have shown that men suffering from diabetes has excess sugar in their urine, browse around for info free get viagra then there’s a strong possibility that they become prone to more ‘Urinary Tract Infection’ (UTI). Once the pill is active it relaxes smooth tissues and order cheap viagra muscles get taut to bring hard on. LegitScript is the only Internet pharmacy verification and monitoring service endorsed by the National Association of Boards of Pharmacy (NABP), which acts as a watchdog on behalf of viagra online prescription individual state boards of pharmacy, 96% of Online Pharmacy violates the ethical code of conduct, laid down by NABP.

As you can see, the file is located in both spots, temporarily.
After some time, the MOVE command completes and we see our table has all of the data.
Database altered.
SQL> select * From system.test_tab;
        ID
----------
        10
        20


This new feature will be a great time saver for me. Many times, I need to relocate a file for a number of reasons and I will be using this new feature.



								

		
			





    

        

            

            	
            


            

                
bpeasland

                    
    
    	        
			        
    •