[Translated from MOS] in RAC, The asmcmd command is used to move the system datafile from the file system to the ASM disk group.

In RAC, use the asmcmd command to move the system datafile from the file system to the ASM disk group.

Reference Original:
How to move a system datafile from filesystem to the ASM diskgroup using asmcmd on RAC. (Doc ID 1607292.1)

Applicable:
Oracle Database-Enterprise Edition-version 11.2.0.1 to 11.2.0.3 [release 11.2]
Oracle Database-Enterprise Edition-version 10.2.0.1 to 10.2.0.1 [release 10.2]
Information in this document applies to any platform.


Symptoms:
Due to misoperations, when you add a datafile for the system tablespace, there is no plus (+). After you run the Add datafile command, datafile is created under $ ORACLE_HOME/DBS of a node. The newly added datafile is not visible to other nodes and the ORA-01157: cannot identify/lock data file is reported

You need to move the empty datafile of the system table to the ASM disk group.

Cause:
Datafile is created in the file system instead of in the ASM disk group.

[[Email protected] ~] $ Echo $ oracle_sid
Orcl2
File exists on one node but not on the other:

[[Email protected] DBS] $ LS-LTR
Total 524848
-RW-r --. 1 Oracle oinstall 2851 May 15 2009 init. ora
-RW-r -----. 1 Oracle oinstall 1536 Nov 28 orapworcl2
-RW-r -----. 1 Oracle oinstall 35 Nov 28 16: 13 initorcl2.
-RW ----. 1 Oracle DBA 1544 Dec 3 hc_orcl2.dat
-RW-r -----. 1 Oracle DBA 536879104 Dec 10 Data =======================<
[[Email protected] DBS] $ LS-LTR
Total 32
-RW-r --. 1 Oracle oinstall 2851 May 15 2009 init. ora
-RW-r -----. 1 Oracle oinstall 1536 Nov 28 orapworcl1
-RW-r -----. 1 Oracle oinstall 35 Nov 28 16: 13 initorcl1.
-RW ----. 1 Oracle DBA 1544 Dec 3 hc_orcl1.dat

The above display shows that data is only available on mbrac2. this file is not available on mbrac1.


Solution:

1. Stop DB
2. Run asmcmd to move datafile.
3. Mount DB
4. Rename the datafile
5. Open DB
6. Restart the database instance on another RAC node, otherwise it will encounter an error ORA-01516 or original error ORA-01157: cannot identify/lock Data File

====== The following are the specific steps ======

1. Stop DB
For system tablespace, you have to stop the DB; otherwise, an error is reported: ORA-01541: System tablespace cannot be brought offline

[[Email protected] DBS] $ pwd
/U01/APP/Oracle/product/11.2.0/dbhome_1/dbs
SQL> select tablespace_name, file_name, file_id, online_status from dba_data_files where tablespace_name = 'system ';

Tablespace_name
------------------------------
File_name
--------------------------------------------------------------------------------
File_id online _
-----------------
System
+ Data/orcl/datafile/system.256.832695063
1 System

System
/U01/APP/Oracle/product/11.2.0/dbhome_1/dbs/Data
6 System

SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
SQL>

No errors in alert. Log

2. Use asmcmd CP to move datafile. Note: The alias is normal because it is not assigned by the system.

Asmcmd> CP/u01/APP/Oracle/product/11.2.0/dbhome_1/dbs/Data + Data/orcl/datafile/system2
Copying/u01/APP/Oracle/product/11.2.0/dbhome_1/dbs/data-> + Data/orcl/datafile/system2
Asmcmd> ls-lt
Type redund striped time sys name
Datafile unprot coarse Dec 10 18:00:00 y users.259.832695063
Datafile unprot coarse Dec 10 18:00:00 y undotbs2.264.832695177
Datafile unprot coarse Dec 10 18:00:00 y undotbs1.258.832695063
N system2 => + Data/ASM/datafile/system2.268.833825159
Datafile unprot coarse Dec 10 18:00:00 y system.256.832695063
Datafile unprot coarse Dec 10 18:00:00 y sysaux.257.832695063

---> Note that the CP command under asmcmd is not available when oracle is 10 Gb,
---> Therefore, if it is 10 Gb, see note: 390274.1-how to move a datafile from a file system to ASM.


3. Mount Database
SQL> startup Mount;
Oracle instance started.

Total system global area 1252663296 bytes
Fixed size 2252824 bytes
Variable Size 805310440 bytes
Database buffers 436207616 bytes
Redo buffers 8892416 bytes
Database mounted.


4. Rename datafile:

SQL> alter database rename File '/u01/APP/Oracle/product/11.2.0/dbhome_1/dbs/data' to' + Data/orcl/datafile/system2 ';
Database altered.


5. Open DB:
SQL> alter database open;
Database altered.

6. Restart the database instance on another RAC node, otherwise it will encounter an error ORA-01516 or original error ORA-01157: cannot identify/lock Data File
Compare the datafile of the system tablespace on all instances to see if it is the same.

[[Email protected] DBS] $ echo $ oracle_sid
Orcl1
[[Email protected] DBS] $
[[Email protected] DBS] $ sqlplus

SQL * Plus: Release 11.2.0.4.0 production on TUE Dec 10 19:00:47 2013
Copyright (c) 1982,201 3, Oracle. All rights reserved.
Enter user-Name:/As sysdba

Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
With the partitioning, real Application Clusters, automatic storage management, OLAP,
Data Mining and real application testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup;
Oracle instance started.

Total system global area 1252663296 bytes
Fixed size 2252824 bytes
Variable Size 872419304 bytes
Database buffers 369098752 bytes
Redo buffers 8892416 bytes
Database mounted.
Database opened.
SQL>

SQL>
SQL> select tablespace_name, file_name, file_id from dba_data_files where tablespace_name = 'system ';
Tablespace_name file_name file_id
--------------------------------------------------------------------------------
System
+ Data/orcl/datafile/system.256.832695063
1
System
+ Data/orcl/datafile/system2
6

SQL> select instance_number, instance_name from V $ instance;
Instance_number instance_name
-------------------------------
1 orcl1

Then on the second node:

SQL> select tablespace_name, file_name, file_id, online_status from dba_data_files where tablespace_name = 'system ';
Tablespace_name file_name file_id online _
--------------------------------------------------------------------------------
System + Data/orcl/datafile/system.256.832695063
1 System

System + Data/orcl/datafile/system2
6 System
SQL> select instance_number, instance_name from V $ instance;
Instance_number instance_name
-------------------------------
2 orcl2