Background
Prior to Oracle 12c moving non-ASM data files required taking data files ‘offline’ and negotiating an outage with the business, since Oracle 12c this is has no longer been the case.
I was recently challenged with moving a good size filesystem 19c Oracle database from an existing storage platform to a new storage array.
Prepare ASM Disk Groups
Use sqlplus to create the ASM disk group directories e.g.
$ export ORACLE_SID=+ASM
$ sqlplus / as sysasm
Disk Group +DATA
SQL> ALTER DISKGROUP DATA ADD DIRECTORY '+DATA/PSTG';
SQL> ALTER DISKGROUP DATA ADD DIRECTORY '+DATA/PSTG/DATAFILE';
SQL> ALTER DISKGROUP DATA ADD DIRECTORY '+DATA/PSTG/CHANGETRACKING';
SQL> ALTER DISKGROUP DATA ADD DIRECTORY '+DATA/PSTG/TEMPFILE';
Disk Group +CONTROL_REDO
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY '+CONTROL_REDO/PSTG';
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY '+CONTROL_REDO/PSTG/CONTROLFILE';
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY '+CONTROL_REDO/PSTG/ONLINELOG';
SQL> ALTER DISKGROUP CONTROL_REDO ADD DIRECTORY '+CONTROL_REDO/PSTG/PARAMETERFILE';
Disk Group +FRA
SQL> ALTER DISKGROUP FRA ADD DIRECTORY '+FRA/PSTG';
SQL> ALTER DISKGROUP FRA ADD DIRECTORY '+FRA/PSTG/ARCHIVELOG';
Identify Datafiles
column dname heading 'Data File Name' format a45 column filename heading 'BCT File Name' format a45 column tablespace_name heading 'Tablespace Name' format a25 column data_mb heading 'Data MB' format a10 select file_name as dname, tablespace_name, to_char(bytes/1024/1024,'999,999') as data_mb from dba_data_files Data File Name Tablespace Name Data MB ---------------------------------------------- ------------------------- ------ /u02/oradata/PSTG/system01.dbf SYSTEM 880 /u02/oradata/PSTG/sysaux01.dbf SYSAUX 990 /u02/oradata/PSTG/sh.dbf SH 512,000 /u02/oradata/PSTG/users01.dbf USERS 5 /u02/oradata/PSTG/soe.dbf SOE 512,000 /u02/oradata/PSTG/undotbs01.dbf UNDOTBS1 400
On-line Datafile move
I have a previously blogged on using the Oracle 12c ALTER DATABASE MOVE DATAFILE command and you can see the full syntax on the link above.
SQL> ALTER DATABASE MOVE DATAFILE '/u02/oradata/PSTG/system01.dbf' TO '+DATA/PSTG/DATAFILE/system01.dbf';
SQL> ALTER DATABASE MOVE DATAFILE '/u02/oradata/PSTG/sysaux01.dbf' TO '+DATA/PSTG/DATAFILE/sysaux01.dbf';
SQL> ALTER DATABASE MOVE DATAFILE '/u02/oradata/PSTG/sh.dbf' TO '+DATA/PSTG/DATAFILE/sh.dbf';
SQL> ALTER DATABASE MOVE DATAFILE '/u02/oradata/PSTG/users01.dbf' TO '+DATA/PSTG/DATAFILE/users01.dbf';
SQL> ALTER DATABASE MOVE DATAFILE '/u02/oradata/PSTG/undotbs01.dbf' TO '+DATA/PSTG/DATAFILE/undotbs01.dbf';
Monitor Move Progress
Below is a query which uses the V$SESSION_LONOPS view to report progress of the Online data file move operations.
column st heading 'Start Time' format a25 column time_remaining heading 'Time|Remaining' column elapsed_seconds heading 'Elaspsed|Seconds' column sofar heading 'Sofar' format a10 column total heading 'Total' format a10 column progress heading 'Progress' format a10 column message heading 'Message' format a100 select to_char(start_time,'DD-MON-YY 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;
Example Output
09-AUG-18 14:58:33 0 7 880MB 880MB 100%
Online data file move: data file 1: 922746880 out of 922746880 bytes done
09-AUG-18 15:01:07 0 7 990MB 990MB 100%
Online data file move: data file 3: 1038090240 out of 1038090240 bytes done
09-AUG-18 15:06:34 0 2767 512,000MB 512,000MB 100%
Online data file move: data file 5: 536870912000 out of 536870912000 bytes done
09-AUG-18 15:57:07 2757 38 6,962MB 512,000MB 1%
Online data file move: data file 2: 7300186112 out of 536870912000 bytes done
Updated Data file locations
We can check the file haven been relocated using the same query we ran earlier to identify the datafiles.
select file_name as dname, tablespace_name, to_char(bytes/1024/1024,'999,999') as data_mb from dba_data_files;
Data File Name Tablespace Name Data MB
--------------------------------------------- ------------------------- --------
+DATA/PSTG/DATAFILE/system01.dbf SYSTEM 880
+DATA/PSTG/DATAFILE/sysaux01.dbf SYSAUX 1,040
+DATA/PSTG/DATAFILE/sh.dbf SH 512,000
+DATA/PSTG/DATAFILE/users01.dbf USERS 5
+DATA/PSTG/DATAFILE/soe.dbf SOE 512,000
+DATA/PSTG/DATAFILE/undotbs01.dbf UNDOTBS1 400
[twitter-follow screen_name=’RonEkins’ show_count=’yes’]