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 18c 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 a10select 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 a100select 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 done09-AUG-18 15:01:07 0 7 990MB 990MB 100%
Online data file move: data file 3: 1038090240 out of 1038090240 bytes done09-AUG-18 15:06:34 0 2767 512,000MB 512,000MB 100%
Online data file move: data file 5: 536870912000 out of 536870912000 bytes done09-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.
Follow @RonEkinsselect 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