[Translated from mos] in rac, The asmcmd command is used to move the system datafile from the file system to the asm disk group. asmcmddatafile

[Translated from mos] in rac, The asmcmd command is used to move the system datafile from the file system to the asm disk group. asmcmddatafile

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.

[Oracle @ mbrac2 ~] $ Echo $ ORACLE_SID
ORCL2
File exists on one node but not on the other:

[Oracle @ mbrac2 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 =======================< <HERE

[Oracle @ mbrac1 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

[Oracle @ mbrac1 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.

[Oracle @ mbrac1 dbs] $ echo $ ORACLE_SID
ORCL1
[Oracle @ mbrac1 dbs] $
[Oracle @ mbrac1 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


Role of the ASM command in linux

Oracle 10R2 releases the asmcmd tool to manage files stored in asm.
1. To use the asmcmd command, you must first start the asm instance. Otherwise, the following error occurs: rac2-> asmcmd-p
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory (dbd error: OCISessionBegin) 2. when using asmcmd, you must specify ORACLE_HOME and ORACLE_SID. Note that ORACLE_SID is the sid of asm. Otherwise, the following error occurs: rac2-> asmcmd-p.
A ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory (dbd error: OCISessionBegin) 3. The asmcmd command has the following functions:
With p, asmcmd will display the current path: rac2-> asmcmd-p
ASMCMD [+]> ls
DG1/
RECOVERDEST/
ASMCMD [+]> cd DG1
ASMCMD [+ DG1]> ls
DEVDB/
ASMCMD [+ DG1]> cd DEVDB
ASMCMD [+ DG1/DEVDB]> without p, the current path is not displayed: rac2-> asmcmd
ASMCMD> ls
DG1/
RECOVERDEST/
ASMCMD> cd DG1
ASMCMD> ls
DEVDB/
ASMCMD> cd DEVDB
ASMCMD> 4. For other related parameters, use help to view ASMCMD [+]> help
Asmcmd [-p] [command]

The environment variables ORACLE_HOME and ORACLE_SID determine
Instance to which the program connects, and ASMCMD establishes
Bequeath connection to it, in the same manner as a SQLPLUS/
SYSDBA. The user must be a member of the SYSDBA group.

Specifying the-p option allows the current directory to be displayed
In the command prompt, like so:

ASMCMD [+ DATAFILE/ORCL/CONTROLFILE]>

[Command] specifies one of the following commands, along with its
Parameters.

Type "h... the remaining full text>