Migrate data files in the file system to the ASM Storage

Migrate data files in the file system to the ASM Storage

The RAC environment uses ASM for storage.
Recently, some data files are stored in the file system during database maintenance, which is abnormal. As a result, the running instance on another server is abnormal.
Therefore, we need to migrate the data files in the file system to the ASM storage.

How to change the password of ASM sys

How to copy data files in ASM to the Operating System

Restoration After all Oracle 11g rac asm disks are lost

Oracle 11g from entry to proficient in PDF + CD source code

Installing Oracle 11g R2 using RHEL6 ASM

Oracle 10g manual creation of the ASM Database

This scenario is simulated below
Operate on rac1 nodes

Data files before data files are not added
SQL> select name from v $ datafile;

FILE_NAME
------------------------------------------------------------
+ DATA/asmdevdb/datafile/system.273.845258673
+ DATA/asmdevdb/datafile/sysaux.274.845258703
+ DATA/asmdevdb/datafile/undotbs1.264.845258723
+ DATA/asmdevdb/datafile/users.271.845258761

4 rows selected.

SQL> alter tablespace users add datafile 'user01. dbf' size 10 m;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
+ DATA/asmdevdb/datafile/system.273.845258673
+ DATA/asmdevdb/datafile/sysaux.274.845258703
+ DATA/asmdevdb/datafile/undotbs1.264.845258723
+ DATA/asmdevdb/datafile/users.271.845258761
/U01/oracle/product/11.2.0/db_1/dbs/user01.dbf
 
5 rows selected.

After a while, we observe other nodes.
An error will be reported when we use sqlplus to log on.
If you use conn sys/password @ devdb2 as sysdba, the following error is returned:
ERROR:
ORA-01075: you are currently logged on
2. conn username/password @ devdb2 for normal user connection, the following error is reported:
ERROR:
ORA-00604: recursive SQL Level 2 error
ORA-01157: unable to identify/lock Data File 5-see DBWR trace file
ORA-01110: Data File 5: '/u01/oracle/product/11.2.0/db_1/dbs/user01.dbf'
ORA-00604: recursive SQL Level 2 error
ORA-01157: unable to identify/lock Data File 5-see DBWR trace file
ORA-01110: Data File 5: '/u01/oracle/product/11.2.0/db_1/dbs/user01.dbf'

In the following example, we use rman to file/u01/oracle/product/11.2.0/db_1/dbs/user01.dbf.
Migrate to the ASM storage.
Start the database to the mount status.
SQL> startup mount
ORACLE instance started.

Total System Global Area 393375744 bytes
Fixed Size 1336764 bytes
Variable Size 276826692 bytes
Database Buffers 109051904 bytes
Redo Buffers 6160384 bytes
Database mounted.
Use rman to connect to a database

Pxboracle-> rman target/

Recovery Manager: Release 11.2.0.1.0-Production on Mon May 12 22:49:59 2014

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

Connected to target database: DEVDB (DBID = 260178701, not open)

RMAN> report schema;

Using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ASMDEVDB

List of Permanent Datafiles
======================================
File Size (MB) Tablespace RB segs Datafile Name
---------------------------------------------------------------
700 SYSTEM *** + DATA/asmdevdb/datafile/system.273.845258673
2 600 SYSAUX *** + DATA/asmdevdb/datafile/sysaux.274.845258703
3 200 UNDOTBS1 *** + DATA/asmdevdb/datafile/undotbs1.264.845258723
4 5 USERS *** + DATA/asmdevdb/datafile/users.271.845258761
5 10 USERS ***/u01/oracle/product/11.2.0/db_1/dbs/user01.dbf
 
List of Temporary Files
======================================
File Size (MB) Tablespace Maxsize (MB) Tempfile Name
---------------------------------------------------------------
1 20 TEMP 32767 + DATA/asmdevdb/tempfile/temp.275.845258729
 
RMAN> backup as copy datafile 5 format '+ data ';

Starting backup at 12-MAY-14
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID = 138 device type = DISK
Channel ORA_DISK_1: starting datafile copy
Input datafile file number = 00005 name =/u01/oracle/product/11.2.0/db_1/dbs/user01.dbf
Output file name = + DATA/asmdevdb/datafile/users.266.847407067 tag = TAG20140512T225103 RECID = 1 STAMP = 847407068
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 12-MAY-14

RMAN> switch datafile '/u01/oracle/product/11.2.0/db_1/dbs/user01.dbf' to copy;

Datafile 5 switched to datafile copy "+ DATA/asmdevdb/datafile/users.266.847407067"
 
RMAN> alter database open;

Database opened

RMAN> report schema;

Report of database schema for database with db_unique_name ASMDEVDB

List of Permanent Datafiles
======================================
File Size (MB) Tablespace RB segs Datafile Name
---------------------------------------------------------------
700 SYSTEM *** + DATA/asmdevdb/datafile/system.273.845258673
2 600 SYSAUX *** + DATA/asmdevdb/datafile/sysaux.274.845258703
3 200 UNDOTBS1 *** + DATA/asmdevdb/datafile/undotbs1.264.845258723
4 5 USERS *** + DATA/asmdevdb/datafile/users.271.845258761
5 10 USERS *** + DATA/asmdevdb/datafile/users.266.847407067
 
List of Temporary Files
======================================
File Size (MB) Tablespace Maxsize (MB) Tempfile Name
---------------------------------------------------------------
1 20 TEMP 32767 + DATA/asmdevdb/tempfile/temp.275.845258729
 
 

Note:
To add a data file, run the following command:
Alter tablespace users add datafile '+ data' size 10 m;
The file path must be written to the storage path without the need to write the file name. The name of the ASM file is different from that of a common file. The file name is generally not directly specified and is generated by the ASM system, the management method is OMF.

SQL> conn/as sysdba
Connected.
SQL> alter tablespace users add datafile '+ data' size 10 m;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
+ DATA/asmdevdb/datafile/system.273.845258673
+ DATA/asmdevdb/datafile/sysaux.274.845258703
+ DATA/asmdevdb/datafile/undotbs1.264.845258723
+ DATA/asmdevdb/datafile/users.271.845258761
+ DATA/asmdevdb/datafile/users.266.847407067
+ DATA/asmdevdb/datafile/users.270.847408759

6 rows selected.