Thursday, December 15, 2016

Oracle 12C: Moving datafiles feature

Trying to test new Oracle 12C feature "Moving or renaming datafiles" in attempt to fix "
ORA-19566: exceeded limit of 0 corrupt blocks for file". This Oracle error simply means, the block no longer belonging to any extents and Oracle 'marked' it as corruption, so, logically, if I can find a way to scrub it, it should  fix the issue.



According to the Oracle DOC this should perform the following upon moving the datafile."When you rename or relocate online data files, the pointers to the data files, as recorded in the database control file, are changed. The files are also physically renamed or relocated at the operating system level."


set current session to the database that I intend to test which is pdorcl.

SQL> alter session set container=PDBORCL;
Session altered.

Intent to change the vpxadmin3.dbf to vpxadmin4.dbf. Essentially, this can be point to somewhere else.

SQL> col file_name format a130;
SQL> SELECT FILE_NAME, BYTES FROM dba_data_files;

FILE_NAME                                                                               BYTES
-------------------------------------------------------------------------------------- ----------
/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_system_8xy2dwhf_.dbf     272629760
/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_sysaux_8xy2dwhd_.dbf     681574400
/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_users_8xy2fwlk_.dbf      5242880
/u01/data/ORCL/datafile/vpxadmin3.dbf                                                   1073741824


Move the datafile. This takes about 3 minutes to complete.

SQL> alter database move datafile '/u01/data/ORCL/datafile/vpxadmin3.dbf' to '/u01/data/ORCL/datafile/vpxadmin4.dbf';
Database altered.


The datafile name vpxadmin3.dbf is changed to vpxadmin4.dbf

SQL> col file_name format a130;
SQL> SELECT FILE_NAME, BYTES FROM dba_data_files;

FILE_NAME                                                                                 BYTES
----------------------------------------------------------------------------------------- ----------
/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_system_8xy2dwhf_.dbf       272629760
/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_sysaux_8xy2dwhd_.dbf       681574400
/u01/data/ORCL/E1428863D6CF20F7E043CB41830AFB4F/datafile/o1_mf_users_8xy2fwlk_.dbf        5242880
/u01/data/ORCL/datafile/vpxadmin4.dbf                                                     1073741824


First time I get the chance to test out the "datafile move" feature. I can see that this may come very handy moving things around. Though, the performance can be questionable for large production environment.

No comments:

Post a Comment