DBA Hub

📋Steps in this guide1/5

Online Move Datafile in Oracle Database 12c Release 1 (12.1)

Rename and relocate datafiles with no downtime in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Basic Syntax

The text description of the syntax is shown below, but the syntax diagrams and a full description of the command is available in the documentation here . The source file can be specified using the file number or name, while the destination file must be specified by the file name. The keyword indicates the new file should be created even if it already exists. The keyword indicates the original copy of the datafile should be retained. When the source file is an OMF file the option can not be used. If the destination file is an OMF file, the clause can be omitted and the file will be created with an OMF name in the location. The file number can be queried from the and views.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
 [ TO ( 'filename' | 'ASM_filename' ) ]
 [ REUSE ] [ KEEP ]

SQL> CONN / AS SYSDBA

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

     FILE# NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf
	 3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
	 4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
	 6 /u01/app/oracle/oradata/cdb1/users01.dbf

SQL>

SQL> COLUMN file_name FORMAT A70
SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf
	 3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
	 4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
	 6 /u01/app/oracle/oradata/cdb1/users01.dbf

SQL>
2

Examples

The following example shows a basic file move, specifying both source and destination by name. Notice the original file is no longer present. The next example uses the file number for the source file and keeps the original file. The next example shows the use of OMF. The final example attempts to use the option, where the source file in an OMF file. Notice how the option is ignored.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

SQL>


SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /tmp/system01.dbf

SQL>


SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf
ls: cannot access /u01/app/oracle/oradata/cdb1/system01.dbf: No such file or directory

SQL> HOST ls -al /tmp/system01.dbf
-rw-r-----. 1 oracle oinstall 838868992 Oct  8 22:48 /tmp/system01.dbf

SQL>

SQL> ALTER DATABASE MOVE DATAFILE 1 TO '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP;

Database altered.

SQL>


SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf

SQL>


SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf
-rw-r-----. 1 oracle oinstall 838868992 Oct  8 22:48 /u01/app/oracle/oradata/cdb1/system01.dbf

SQL> HOST ls -al /tmp/system01.dbf
-rw-r-----. 1 oracle oinstall 838868992 Oct  8 22:49 /tmp/system01.dbf

SQL>

SQL> ALTER SYSTEM SET db_create_file_dest='/u01/app/oracle/oradata/cdb1';

System altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf';

Database altered.

SQL>


SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf

SQL>

SQL> ALTER DATABASE MOVE DATAFILE 1 To '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP;

Database altered.

SQL> 


SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf

SQL>


SQL> host ls -al /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf
ls: cannot access /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf: No such file or directory

SQL>
3

Pluggable Database (PDB)

The container database (CDB) can not move files that belong to a pluggable database. The following query displays all the datafiles for the CDB and the PDBs. If we try to move a datafile belonging to a PDB an error is returned. If we switch to the PDB container, the datafile can be moved as normal.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
SQL> SELECT file#, name FROM v$datafile ORDER BY file#;

     FILE# NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/system01.dbf
	 3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
	 4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
	 5 /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
	 6 /u01/app/oracle/oradata/cdb1/users01.dbf
	 7 /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
	 8 /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
	 9 /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
	10 /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
	29 /u01/app/oracle/oradata/pdb2/system01.dbf
	30 /u01/app/oracle/oradata/pdb2/sysaux01.dbf
	31 /u01/app/oracle/oradata/pdb2/pdb2_users01.dbf

SQL>

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "29"

SQL>

SQL> ALTER SESSION SET container=pdb2;

Session altered.

SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE;

Database altered.

SQL>


SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 29;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	29 /tmp/system01.dbf

SQL>


SQL> ALTER DATABASE MOVE DATAFILE 29 TO '/u01/app/oracle/oradata/pdb2/system01.dbf' REUSE;

Database altered.

SQL>


SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 29;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	29 /u01/app/oracle/oradata/pdb2/system01.dbf

SQL>ALTER SESSION SET container=cdb1;


SQL> ALTER SESSION SET container=CDB$ROOT;

Session altered.

SQL>
4

Tempfiles

Not surprisingly, the syntax does not work for temporary files. That is not major problem as temporary files can be created and removed quite simply.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> SELECT file_id, file_name FROM dba_temp_files;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
	 1 /u01/app/oracle/oradata/cdb1/temp01.dbf

SQL>


SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/temp01.dbf' TO '/tmp/temp01.dbf' REUSE;
ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/temp01.dbf' TO '/tmp/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oradata/cdb1/temp01.dbf"

SQL>
5

Data Guard

We connect to the primary database (cdb1), check the datafile location, move it and check again. We connect to the standby database (cdb1_stby) and check the location of file 1. We can see it hasn't moved. We can move it on the standby database, but we have to turn off the apply process. We can now move the file on the standby. Once the move is complete we need to turn on the apply process. For more information see: - Renaming and Relocating Online Data Files - ALTER DATABASE - Multitenant : Online Move of Datafiles in CDBs and PDBs - Renaming or Moving Oracle Files Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
sqlplus sys/${SYS_PASSWORD}@cdb1 as sysdba

SQL> SELECT name FROM v$datafile where file# = 1;

NAME
--------------------------------------------------------------------------------
/u01/oradata/CDB1/system01.dbf

SQL> ALTER DATABASE MOVE DATAFILE '/u01/oradata/CDB1/system01.dbf' TO '/tmp/system01.dbf';

Database altered.

SQL> SELECT name FROM v$datafile where file# = 1;

NAME
--------------------------------------------------------------------------------
/tmp/system01.dbf

SQL>

sqlplus sys/${SYS_PASSWORD}@cdb1_stby as sysdba

SQL> SELECT name FROM v$datafile where file# = 1;

NAME
--------------------------------------------------------------------------------
/u01/oradata/CDB1_STBY/datafile/o1_mf_system_h4p21n94_.dbf

SQL>

dgmgrl sys/${SYS_PASSWORD}@cdb1 <<EOF
EDIT DATABASE 'cdb1_stby' SET STATE='APPLY-OFF';
EXIT;
EOF

sqlplus sys/${SYS_PASSWORD}@cdb1_stby as sysdba

SQL> SELECT name FROM v$datafile where file# = 1;

NAME
--------------------------------------------------------------------------------
/u01/oradata/CDB1_STBY/datafile/o1_mf_system_h4p21n94_.dbf

SQL> ALTER DATABASE MOVE DATAFILE 1 TO '/tmp/system01.dbf';

Database altered.

SQL> SELECT name FROM v$datafile where file# = 1;

NAME
--------------------------------------------------------------------------------
/tmp/system01.dbf

SQL>

dgmgrl sys/${SYS_PASSWORD}@cdb1 <<EOF
EDIT DATABASE 'cdb1_stby' SET STATE='APPLY-ON';
EXIT;
EOF

Comments (0)

Please to add comments

No comments yet. Be the first to comment!