How to move a datafile from a file system to ASM - System Tablespace - 2

Moving oracle system datafile while the database is in mount stage is performed in the following way:

1) Add the sample datafile in system tablespace location of the file system.

SQL> alter tablespace system add datafile 'system1' size 500m;

Tablespace altered.

2) Ensure you have enough space in the ASM diskgroup to copy the datafile. First identify the size of the datafile you wish to move.

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;


     FILE# NAME                                                                   FILE_SIZE_MB
---------- ---------------------------------------------------------------------- ------------
         1 +DATA01/TSTWPS/DATAFILE/system.278.920287861             790
         2 +DATA01/TSTWPS/DATAFILE/enc.297.920408943                  10
         3 +DATA01/TSTWPS/DATAFILE/sysaux.277.920287797              780
         4 +DATA01/TSTWPS/DATAFILE/undotbs1.280.920287929          05
         5 +DATA01/TSTWPS/DATAFILE/undotbs2.285.920288099           50
         6 +DATA01/TSTWPS/DATAFILE/users.279.920287927                  5
         7 +DATA01/TSTWPS/DATAFILE/system.298.926421235              500
         8 /u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system2              500
7 rows selected.

3) Stop the RAC database and start the database as a mount stage using the below command.

[oracle@exadb01 admin]$ srvctl stop database -d tstwps

[oracle@exadb01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:11:58 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1929379840 bytes
Fixed Size                  2942352 bytes
Variable Size             444598896 bytes
Database Buffers         1476395008 bytes
Redo Buffers                5443584 bytes
Database mounted.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

4) Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.

[oracle@exadb01 admin]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 11:12:34 2016

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

connected to target database: TSTWPS (DBID=591086125, not open)

RMAN> copy datafile 8 to '+DATA01';

Starting backup at 28-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=TSTWPS1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=59 instance=TSTWPS1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u02/app/oracle/product/12.1.0.2/dbhome_1/dbs/system2
output file name=+DATA01/TSTWPS/DATAFILE/system.299.926451593 tag=TAG20161028T193951 RECID=2 STAMP=926451609
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 28-OCT-16

Starting Control File and SPFILE Autobackup at 28-OCT-16
piece handle=+RECO01/TSTWPS/AUTOBACKUP/2016_10_28/s_926451445.386.926451623 comment=NONE
Finished Control File and SPFILE Autobackup at 28-OCT-16

Recovery Manager complete.

5) Update the controlfile with the new location of the datafile.

[oracle@exadb01 admin]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 11:18:44 2016

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

connected to target database: TSTWPS (DBID=591086125, not open)

RMAN> switch datafile 8 to copy;

datafile 8 switched to datafile copy "+DATA01/TSTWPS/DATAFILE/system.299.926451593"

RMAN>

Recovery Manager complete.

6. Restart the database and check the file is now if the new location.
SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

[oracle@exadb01 admin]$ srvctl start database -d tstwps
[oracle@exadb01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 28 11:18:42 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA01/TSTWPS/DATAFILE/system.278.920287861
+DATA01/TSTWPS/DATAFILE/enc.297.920408943
+DATA01/TSTWPS/DATAFILE/sysaux.277.920287797
+DATA01/TSTWPS/DATAFILE/undotbs1.280.920287929
+DATA01/TSTWPS/DATAFILE/undotbs2.285.920288099
+DATA01/TSTWPS/DATAFILE/users.279.920287927
+DATA01/TSTWPS/DATAFILE/system.298.926421235
+DATA01/TSTWPS/DATAFILE/system.299.926451593

8 rows selected.

SQL>

7) The system datafile has been successfully moved to the ASM diskgroup.