As a DBA, you have a constant struggle between availability and maintenance. Oracle has added features over the years to give DBAs additional options for online maintenance. Oracle 12c has added a feature that is really cool and I have been waiting to have for a while. Online datafile moves!
Prior to 12c, if you needed to move a datafile or rename it, it required an outage of any object inthe tablespace and probably the application while you moved the file. This meant off hours one night, weekend or holiday which no DBA likes. We will take a step by step review of moving a datafile in 11g, then we will do the same in 12c.
Oracle 11g
We are connected to an 11g database, let’s take a look at where the datafiles are located.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> col tablespace_name format a30 SQL> select tablespace_name, file_name, bytes/1024/1024 from dba_data_files; TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ------------------------------ ---------------------------------------------------------------------- --------------- SYSTEM /u001/oradata/DH11204A/DH11204A_system01.dbf 720 SYSAUX /u002/oradata/DH11204A/DH11204A_sysaux01.dbf 700 UNDOTBS1 /u003/oradata/DH11204A/DH11204A_undotbs01.dbf 530 USERS /u004/oradata/DH11204A/DH11204A_users01.dbf 5 Elapsed: 00:00:00.02
Let’s create the test user and give the account privileges.
SQL> create user dhtest identified by dhtest quota unlimited on users; User created. Elapsed: 00:00:00.05 SQL> grant connect, resource to dhtest; Grant succeeded. Elapsed: 00:00:00.04
Now, let’s create a table in the users tablespace, insert some records into the new table and show what we have.
SQL> create table dhtest.test (insdate date, Description varchar(40)) tablespace users; Table created. Elapsed: 00:00:00.04 SQL> col owner format a15 SQL> col table_name format a30 SQL> col tablespace_name format a30 SQL> select owner, table_name, tablespace_name from dba_tables where table_name='TEST'; OWNER TABLE_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ DHTEST TEST USERS Elapsed: 00:00:00.01 SQL> insert into dhtest.test values (sysdate, 'Test data row 1'); 1 row created. Elapsed: 00:00:00.10 SQL> commit; Commit complete. Elapsed: 00:00:00.02 SQL> insert into dhtest.test values (sysdate, 'Test data row 2'); 1 row created. Elapsed: 00:00:00.01 SQL> commit; Commit complete. Elapsed: 00:00:00.02 SQL> select * from dhtest.test; INSDATE DESCRIPTION ------------------------ ---------------------------------------- 14-JUL-16 16:46 Test data row 1 14-JUL-16 16:46 Test data row 2 Elapsed: 00:00:00.00
Now we can take a look at the status of the users tablespace, then take it offline for the move. Keep in mind, when we take this tablespace offline, there is no access to the data in the datafiles until we complete the move.
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='USERS'; TABLESPACE_NAME STATUS ------------------------------ --------- USERS ONLINE Elapsed: 00:00:00.00 SQL> alter tablespace users offline; Tablespace altered. Elapsed: 00:00:00.63 SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='USERS'; TABLESPACE_NAME STATUS ------------------------------ --------- USERS OFFLINE Elapsed: 00:00:00.01
We can prove that the table is unavailable by trying to insert a record into the database. It fails and shows the datafile that it is expecting to use.
SQL> insert into dhtest.test values (sysdate, 'Test data row 3'); insert into dhtest.test values (sysdate, 'Test data row 3') * ERROR at line 1: ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: '/u004/oradata/DH11204A/DH11204A_users01.dbf' Elapsed: 00:00:00.00
In another terminal session, you can see that the datafile is in the original location and nothing exists in the target location.
$ /home/oracle> ls -al /u004/oradata/DH11204A/DH11204A_users01.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 14 16:48 /u004/oradata/DH11204A/DH11204A_users01.dbf $ ls -al /u005/oradata/DH11204A/ total 8 drwxr-xr-x 2 oracle oinstall 4096 Jul 14 16:57 . drwxr-xr-x 4 oracle oinstall 4096 Jul 14 16:57 ..
Now let’s move the file. We can use the linux time program to show how long it takes to move. Since it is a 5 mb file, it takes less than a fraction of a second. But imagine how long it would take with a 10, 20 or 30 gb datafile? And the whole time that the move is occurring, the tablespace is offline!
$ /home/oracle> time mv /u004/oradata/DH11204A/DH11204A_users01.dbf /u005/oradata/DH11204A/ real 0m0.01s user 0m0.00s sys 0m0.01s
After the move is complete, you have to tell the database where the new file located using the datafile rename command. Then we can bring the datafile online again.
SQL> alter database rename file '/u004/oradata/DH11204A/DH11204A_users01.dbf' to '/u005/oradata/DH11204A/DH11204A_users01.dbf'; Database altered. Elapsed: 00:00:00.28 SQL> alter tablespace users online; Tablespace altered. Elapsed: 00:00:00.45
We can finally use the tablespace again and the application is online!
SQL> insert into dhtest.test values (sysdate, 'Test data row 3'); 1 row created. Elapsed: 00:00:00.00 SQL> commit; Commit complete. Elapsed: 00:00:00.02 SQL> select * from dhtest.test; INSDATE DESCRIPTION ------------------------ ---------------------------------------- 14-JUL-16 16:46 Test data row 1 14-JUL-16 16:46 Test data row 2 14-JUL-16 17:00 Test data row 3 Elapsed: 00:00:00.00
We can also look at the operating system and see that the file has been successfully moved.
$ /home/oracle> ls -al /u004/oradata/DH11204A/DH11204A_users01.dbf ls: cannot access /u004/oradata/DH11204A/DH11204A_users01.dbf: No such file or directory oracle@nauskop1495l[DH12102A]: /home/oracle> ls -al /u005/oradata/DH11204A/DH11204A_users01.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 14 16:48 /u005/oradata/DH11204A/DH11204A_users01.dbf
Oracle 12c
Let’s move the datafile in a 12c database. This database has my fake, test data in it and is much larger. We will do the same steps as the 11g test: create the user, create the table and insert a few rows.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SYS@DH12102A:SQL> create user dhtest identified by dhtest quota unlimited on testdata_d; User created. Elapsed: 00:00:00.18 SYS@DH12102A:SQL> grant connect, resource to dhtest; Grant succeeded. Elapsed: 00:00:00.05 SYS@DH12102A:SQL> create table dhtest.test (insdate date, Description varchar(40)) tablespace testdata_d; Table created. Elapsed: 00:00:00.05 SYS@DH12102A:SQL> select owner, table_name, tablespace_name from dba_tables where table_name='TEST'; OWNER TABLE_NAME TABLESPACE_NAME --------------- ------------------------------ ------------------------------ DHTEST TEST TESTDATA_D Elapsed: 00:00:00.00 SYS@DH12102A:SQL> insert into dhtest.test values (sysdate, 'Test data row 1'); 1 row created. Elapsed: 00:00:00.00 SYS@DH12102A:SQL> insert into dhtest.test values (sysdate, 'Test data row 2'); 1 row created. Elapsed: 00:00:00.00 SYS@DH12102A:SQL> commit; Commit complete. Elapsed: 00:00:00.02 SYS@DH12102A:SQL> select * from dhtest.test; INSDATE DESCRIPTION ------------------------ ---------------------------------------- 14-JUL-16 17:13 Test data row 1 14-JUL-16 17:13 Test data row 2 Elapsed: 00:00:00.01
Before we begin the move, let’s take a look at the current location of the datafile and how many records are in the table.
SYS@DH12102A:SQL> select tablespace_name, file_name, bytes/1024/1024 from dba_data_files where tablespace_name='TESTDATA_D'; TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ------------------------------ ---------------------------------------------------------------------- --------------- TESTDATA_D /u002/oradata/DH12102A/DH12102A_testdata_d.dbf 1024 Elapsed: 00:00:00.01 SYS@DH12102A:SQL> select count(*) from dhtest.test COUNT(*) ---------- 2 Elapsed: 00:00:00.00
We are ready for the move. This time we are going to start a PL/SQL loop that will insert 1 million records into the table. In the other terminal session we will issue the move command while the insert continues.
Session 1
SYS@DH12102A:SQL> begin 2 for i in 1 .. 1000000 3 loop 4 insert into dhtest.test values (sysdate, 'Test data row '||i); 5 commit; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:01:55.73
Session 2
SYS@DH12102A:SQL> alter database move datafile '/u002/oradata/DH12102A/DH12102A_testdata_d.dbf' 2 to '/u005/oradata/DH12102A/DH12102A_testdata_d.dbf'; Database altered. Elapsed: 00:02:03.19
As you can see, it did not take long. Most importantly, we were able to complete the move while the tablespace was online and availabile.
Finally, we can show that all of the records were inserted and the datafile has been moved! All with no “application” downtime.
SYS@DH12102A:SQL> select count(*) from dhtest.test; COUNT(*) ---------- 1000002 Elapsed: 00:00:00.16 SYS@DH12102A:SQL> select tablespace_name, file_name, bytes/1024/1024 from dba_data_files where tablespace_name='TESTDATA_D'; TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ------------------------------ ---------------------------------------------------------------------- --------------- TESTDATA_D /u005/oradata/DH12102A/DH12102A_testdata_d.dbf 1024 Elapsed: 00:00:00.02
You might think to yourself: “I never move my datafiles, why do I care?”. Well, here’s few scenarios where this would come in handy:
- The filename for the datafile is wrong
- The datafile in the wrong directory
- You are converting from file systems to ASM
Yes, you can use this to move the datafiles into and out of ASM.
There are a few other things that you should know about the online file moves:
- The database is making a copy of the datafile to the new location. If there is not enough space it will fail and your current datafile is untouched
- You can specify a keep option that will not remove the old datafile after the copy. It will no longer be associated with the database but will still be thee
- If a file with the same name already exists at the target location, the database will not overwrite the file. You can specify the reuse option that will cause it to overwrite the file
- A flashback operation won’t relocate the datafile to the original location. It will remain in the current location.
As always, test it out in your sandbox environment and see how it can help you in your DBA life!
RSS - Posts
Leave a Reply