12c New Feature: Move datafile online


12c_logo_breit

Today i have made a simple test of a 12c new feature. In the latest release of Oracle you can move a datafile online, without taking it offline. Thats sound great, for example if you want to do some storage maintenance.

First we create a small tablespace for some testing.

SQL> create tablespace omove datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' size 50M;

Tablespace created.

So, now i try my first move operation.

SQL> alter database move datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' to '+DG_DATA';
alter database move datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' to '+DG_DATA'
*
ERROR at line 1:
ORA-01119: error in creating database file '+DG_DATA'
ORA-17502: ksfdcre:4 Failed to create file +DG_DATA
ORA-15001: diskgroup "DG_DATA" does not exist or is not mounted
ORA-01017: invalid username/password; logon denied

So, whats this? My first move operation and my first 12c database problem. After some research, i found out that my database and my ASM are running under different user. My grid user was in the asmdba OS group. After i have placed my database user into the asmdba group i fixed the problem but i am getting a new error.

SQL> alter database move datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' to '+DG_DATA'
*
ERROR at line 1:
ORA-01119: error in creating database file '+DG_DATA'
ORA-17502: ksfdcre:4 Failed to create file +DG_DATA
ORA-15001: diskgroup "DG_DATA" does not exist or is not mounted
ORA-15040: diskgroup is incomplete

After a look into the alert.log i found this.

Moving datafile /u01/app/oracle/oradata/db1211/omove01.dbf (5) to +DG_DATA
Thu Jun 27 22:27:17 2013
NOTE: ASMB mounting group 2 (DG_DATA)
Thu Jun 27 22:27:17 2013
ORA-15025: could not open disk "/dev/raw/raw7"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15040: diskgroup is incomplete
NOTE: Disk 0 in group 2 could not be opened.
WARNING: Failed to complete group 2
WARNING: group 2 is being dismounted.
WARNING: ASMB force dismounting group 2 (DG_DATA) due to failed mount

The key here is „Linux-x86_64 Error: 13: Permission denied“. So after i made my ASM disks also avaible for the database user (before only the grid user has access to it), the command run without any problem.

SQL> alter database move datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' to '+DG_DATA';

Database altered.

Now it looks good. My datafile was moved, without take it offline. The alert log shows me a successfully completion.

alter database move datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' to '+DG_DATA'
Thu Jun 27 22:28:28 2013
Moving datafile /u01/app/oracle/oradata/db1211/omove01.dbf (5) to +DG_DATA
Thu Jun 27 22:28:28 2013
NOTE: ASMB mounting group 2 (DG_DATA)
NOTE: Assigning number (2,0) to disk (/dev/raw/raw7)
SUCCESS: mounted group 2 (DG_DATA)
NOTE: grp 2 disk 0: DG_DATA_0000 path:/dev/raw/raw7
Thu Jun 27 22:28:32 2013
NOTE: dependency between database db1211 and diskgroup resource ora.DG_DATA.dg is established
Thu Jun 27 22:28:33 2013
Move operation committed for file +DG_DATA/DB1211/DATAFILE/omove.256.819239311
Completed: alter database move datafile '/u01/app/oracle/oradata/db1211/omove01.dbf' to '+DG_DATA'

Conclusion:
A nice new feature. The pitfall? It is not useable for a Filesystem to ASM migration, because you have the controlfiles where you need to shutdown your database. Also (in the moment) i was not able to move a tempfile online. OK you can create a new temporary tablespace and rename it. But in this case you need to wait that all your transaction are done.

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s