RakDBA Oracle Posts Online Move datafile since Oracle 12c

Online Move datafile since Oracle 12c

November 30, 2020November 30, 2020 Sami RAKIZSami RAKIZ 0 Comments 1:37 pm
Categories:

Prior to Oracle 12c, moving datafiles has always been an offline task, you had to put a tablespace or your complete database offline in order to move or rename a datafile. Since12c version, Oracle gives a new ALTER DATABASE MOVE command to allow datafiles to be moved or renamed when both database and datafile are online.

let’s see that!


Commande Syntax :
ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
 [ TO ( 'filename' | 'ASM_filename' ) ]
 [ REUSE ] [ KEEP ]
  • REUSE : overwrite the existing file if it already exists.
  • KEEP   : retain the data file in the old location and copy it to the new location. When the source file is an OMF file the KEEP option can not be used.


Example :

Identify the datatafile to be relocated or renamed. In this example we will move the datafile number 8 “tbs_data01.dbf” for the tablespace “TBS_TEST“.

SQL> SET LINESIZE 100
SQL> COLUMN name FORMAT A50
SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';

     FILE# NAME
---------- -------------------------------------------------------
	  8    /opt/oracle/oradata/TEST/tbs_data01.dbf
	  9    /opt/oracle/oradata/TEST/tbs_data02.dbf
	 10    /opt/oracle/oradata/TEST/tbs_data03.dbf
	 11    /opt/oracle/oradata/TEST/tbs_idx01.dbf
  • Relocating a datafile
    • SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/TEST/tbs_data01.dbf' 
TO '/u01/oracle/oradata/TEST/tbs_data01.dbf';


SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';

     FILE# NAME
---------- -------------------------------------------------------
	  8    /u01/oracle/oradata/TEST/tbs_data01.dbf
	  9    /opt/oracle/oradata/TEST/tbs_data02.dbf
	 10    /opt/oracle/oradata/TEST/tbs_data03.dbf
	 11    /opt/oracle/oradata/TEST/tbs_idx01.dbf
  • Relocating a datafile to ASM
    • SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/TEST/tbs_data01.dbf' 
TO '+DATA/oradata/TEST/tbs_data01.dbf';


SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';

     FILE# NAME
---------- -------------------------------------------------------
	  8    +DATA/oradata/TEST/tbs_data01.dbf
	  9    /opt/oracle/oradata/TEST/tbs_data02.dbf
	 10    /opt/oracle/oradata/TEST/tbs_data03.dbf
	 11    /opt/oracle/oradata/TEST/tbs_idx01.dbf
  • Renaming a datafile
    • SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/TEST/tbs_data01.dbf' 
TO '/opt/oracle/oradata/TEST/new_name_tbs_data01.dbf';

SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';

     FILE# NAME
---------- -------------------------------------------------------
	  8    /opt/oracle/oradata/TEST/new_name_tbs_data01.dbf
	  9    /opt/oracle/oradata/TEST/tbs_data02.dbf
	 10    /opt/oracle/oradata/TEST/tbs_data03.dbf
	 11    /opt/oracle/oradata/TEST/tbs_idx01.dbf


    Move datafile for a Pluggable Database (PDB) :

    From the container database (CDB) we can not move files that belong to a pluggable database. We must switch to the PDB container and the datafile can be moved as normal.

    SQL> ALTER SESSION SET container=pdb1;

Session altered.

SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/pdb1/tbs_data01.dbf' 
TO '/u01/oracle/oradata/pdb1/tbs_data01.dbf';

Database altered.


SQL> SELECT file#, name FROM v$datafile WHERE tablespace_name = 'TBS_TEST';

     FILE# NAME
---------- -------------------------------------------------------
	  8    /u01/oracle/oradata/pdb1/tbs_data01.dbf
	  9    /opt/oracle/oradata/pdb1/tbs_data02.dbf
	 10    /opt/oracle/oradata/pdb1/tbs_data03.dbf
	 11    /opt/oracle/oradata/pdb1/tbs_idx01.dbf


    Move datafile for a DATA GUARD :

    Moving datafile for the primary database does not move automaticly the datafile for the standby database. It can be done manually on the standby database, but we have to turn off the apply process.

  • Turn off the apply process
    • dgmgrl sys/${SYS_PASSWORD}@db_pry
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Nov 30 16:21:03 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> EDIT DATABASE 'db_stby' SET STATE='APPLY-OFF';

Succeeded.
  • Move the file on the standby
    • sqlplus sys/${SYS_PASSWORD}@db_stby as sysdba

Connecte a :
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> ALTER DATABASE MOVE DATAFILE '/opt/oracle/oradata/pdb1/tbs_data01.dbf' 
TO '/u01/oracle/oradata/pdb1/tbs_data01.dbf';

Database altered.


SQL> SELECT file#, name FROM v$datafile WHERE file# = 8;

     FILE# NAME
---------- -------------------------------------------------------
	  8    /u01/oracle/oradata/pdb1/tbs_data01.dbf
  • Turn on the apply process
    • dgmgrl sys/${SYS_PASSWORD}@db_pry
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Mon Nov 30 16:21:03 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.

DGMGRL> EDIT DATABASE 'db_stby' SET STATE='APPLY-ON';

Succeeded.


    Monitor Move progess :

    Via the V$SESSION_LONGOPS view, we can monitor the data file move progress. The reported bytes done will increment until the move is completed.

    SQL> select to_char(start_time,’DD-MON-YY hh24:mi:ss’),  
            timestamp, 
            time_remaining, 
            elapsed_seconds, 
            message 
from V$SESSION_LONGOPS;


30-NOV-20 15:30:25   0 35
Online data file move: data file 8: 233591317380 out of 233591317380 bytes done
    Post Views: 1,563
    5 1 vote
    Article Rating
    Subscribe
    Notify of
    0 Comments
    Oldest
    Newest Most Voted
    Inline Feedbacks
    View all comments

    You May Also Like