Blog

Howto move datafiles online in Oracle 18c

Mário Barduchi

By Mario Barduchi

Hi guy’s…

In Oracle database 12c, the move online of datafiles it was already possible. Today, I’m showing the process in Oracle 18c.

Going straight to the point…

Check if be connected on CDB.

SQL> select cdb from v$database;
CDB
---
YES

Check the datafiles for the drive.

SQL> SET LINESIZE 100
SQL> COLUMN con_id 	FORMAT 99999
SQL> COLUMN file 	FORMAT 99999
SQL> COLUMN name 	FORMAT A70
SQL> SELECT con_id, file#, name FROM v$datafile ORDER BY con_id, file# ;

 CON_ID	   FILE#  NAME
---------- ------ -------------------------------------------------
	 1	1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf
	 1	3 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
	 1	7 /u01/app/oracle/oradata/ORCLCDB/users01.dbf
	 1     14 /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
	 2	5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.
	 2	6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.
	 2	8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01
	 3	9 /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbj
	 3     10 /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
	 3     12 /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
	 3     13 /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_158159.dbf
	 3     15 /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf

First, we going to move the datafiles of CDB.

SQL> ALTER SESSION SET container=CDB$ROOT;
     Session altered.

We have check again because I’m crazy and I like to do a double check in anything hahahahaha… 

SQL> SET PAGES 120 LINES 1000
SQL> COLUMN con_id 	FORMAT 99999
SQL> COLUMN file 	FORMAT 99999
SQL> COLUMN name 	FORMAT A70
SQL> SELECT file#, name FROM v$datafile ORDER BY con_id, file# ;

 FILE# NAME
------ -------------------------------------------------------------
     1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf
     3 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
     7 /u01/app/oracle/oradata/ORCLCDB/users01.dbf
    14 /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf

With that SQL, we have the datafiles for moving.

SQL> SELECT 
'ALTER DATABASE MOVE DATAFILE ''' || name || ''' TO 
     ''/u02/oracle/oradata/orclcdb/ORCLCDB/' || 
     substr(NAME,instr(NAME,'/',-1)+1,70) || ''' REUSE;'
AS MOVE
FROM 
	v$datafile 
 WHERE 
	con_id = 1 
ORDER BY 
	file#;

MOVE
-------------------------------------------------------------
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/system01.dbf'  TO '/u02/oracle/oradata/orclcdb/ORCLCDB/system01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf'  TO '/u02/oracle/oradata/orclcdb/ORCLCDB/sysaux01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/users01.dbf'   TO '/u02/oracle/oradata/orclcdb/ORCLCDB/users01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf'  TO '/u02/oracle/oradata/orclcdb/ORCLCDB/undotbs2.dbf' REUSE;

And now, execute the command…

Example:
SQL> ALTER DATABASE MOVE DATAFILE 
     '/u01/app/oracle/oradata/ORCLCDB/system01.dbf' TO 
     '/u02/oracle/oradata/orclcdb/ORCLCDB/system01.dbf' REUSE;
Database altered.

And check again…

SQL> SET PAGES 120 LINES 1000
SQL> COLUMN con_id 	FORMAT 99999
SQL> COLUMN file 	FORMAT 99999
SQL> COLUMN name 	FORMAT A70
SQL> SELECT file#, name FROM v$datafile ORDER BY con_id, file# ;

     FILE# NAME
---------- ------------------------------------------------------------
	 1 /u02/oracle/oradata/orclcdb/ORCLCDB/system01.dbf
	 3 /u02/oracle/oradata/orclcdb/ORCLCDB/sysaux01.dbf
	 7 /u02/oracle/oradata/orclcdb/ORCLCDB/users01.dbf
	14 /u02/oracle/oradata/orclcdb/ORCLCDB/undotbs2.dbf

The same process is done for the PDB.

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCL 			  READ WRITE NO

SQL> ALTER SESSION SET container=ORCL;
     Session altered.
				 
SQL> SET PAGES 120 LINES 1000
SQL> COLUMN con_id 	FORMAT 99999
SQL> COLUMN file 	FORMAT 99999
SQL> COLUMN name 	FORMAT A70
SQL> SELECT con_id, file#, name FROM v$datafile ORDER BY con_id, file# ;

   CON_ID   FILE# NAME
---------- ------ ------------------------------------------------
	 3      9 /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
	 3     10 /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
	 3     12 /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
	 3     13 /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_158159.dbf
	 3     15 /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf

With that SQL, we have the datafiles for moving.

SQL> SELECT 
     'ALTER DATABASE MOVE DATAFILE ''' || name || ''' TO 
     ''/u02/oracle/oradata/orclcdb/ORCLCDB/' || 
     substr(NAME,instr(NAME,'/',-1)+1,70) || ''' REUSE;'
AS MOVE
FROM 
    v$datafile 
ORDER BY 
    file#;
		
Example:
SQL> ALTER DATABASE MOVE DATAFILE 
     '/u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf' TO 
     '/u02/oracle/oradata/orclcdb/ORCL/system01.dbf' REUSE;

This query uses the V$SESSION_LONGOPS view to report progress of the Online data file move operations.

SQL> column st 				heading 'Start Time' format a25
SQL> column time_remaining  heading 'Time|Remaining'
SQL> column elapsed_seconds heading 'Elaspsed|Seconds'
SQL> column sofar 	    heading 'Sofar' format a10
SQL> column total 	    heading 'Total' format a10
SQL> column progress 	    heading 'Progress' format a10
SQL> column message 	    heading 'Message' format a100

SQL> select 
     to_char(start_time,'DD/MM/YYYY hh24:mi:ss') as st, 
     time_remaining, elapsed_seconds, 
     to_char(SOFAR/1024/1024,'999,999') || 'MB' as sofar, 
     to_char(TOTALWORK/1024/1024,'999,999') || 'MB' as total, 
     to_char((SOFAR/TOTALWORK)*100,'999') || '%' as progress, 
     message 
from 
     V$SESSION_LONGOPS;

And now, we going to do the final checks.

SQL> ALTER SESSION SET container=CDB$ROOT;
     Session altered.

SQL> SET LINESIZE 100
SQL> COLUMN con_id 	FORMAT 99999
SQL> COLUMN file 	FORMAT 99999
SQL> COLUMN name 	FORMAT A70
SQL> SELECT con_id, file#, name FROM v$datafile ORDER BY con_id, file# ;

CON_ID	    FILE#  NAME
---------   ------ ------------------------------------------------
	1	 1 /u02/oracle/oradata/orclcdb/ORCLCDB/system01.dbf
	1   	 3 /u02/oracle/oradata/orclcdb/ORCLCDB/sysaux01.dbf
	1    	 7 /u02/oracle/oradata/orclcdb/ORCLCDB/users01.dbf
	1 	14 /u02/oracle/oradata/orclcdb/ORCLCDB/undotbs2.dbf
	3	 9 /u02/oracle/oradata/orclcdb/ORCL/system01.dbf
	3	10 /u02/oracle/oradata/orclcdb/ORCL/sysaux01.dbf
	3	12 /u02/oracle/oradata/orclcdb/ORCL/users01.dbf
	3	13 /u02/oracle/oradata/orclcdb/ORCL/APEX_158159.dbf
	3	15 /u02/oracle/oradata/orclcdb/ORCL/undotbs2.dbf

Important:
We don’t move the temp files online. For the temp files, we have to do the recreatre a temporary tablespace.

That’s it. I hope this helps. See you guy’s.

Mario.

Fonte: ORADICAS