How to migrate local data files of RAC to ASM

At present, there are a large number of zoolerac environments in various industries. Especially for small and medium enterprises and some environments with irregular management, database operations are also disorganized.

At present, there are a large number of Oracle RAC environments in various industries, especially for small and medium enterprises and some environments with irregular management, database operations are also disorganized.

I. Overview
At present, there are a large number of Oracle RAC environments in various industries. Especially for small and medium enterprises and some environments with irregular management, database operations are also disorganized. It is not a core system, however, there are enough problems for O & M personnel to drink. The following is a common problem: In the RAC Environment, Local data files are often encountered, that is, they can only be accessed at one node. There are two main reasons for this situation, the naming rules are not standardized, causing Oracle to automatically create a database under the DBS directory. If you do not know about the RAC system, follow the previous method to create the database. So how can we solve this problem? Let's take a simple look.

Ii. Operation Process
This operation is intended for non-system tablespace, Environment rhel6.3 _ x64 + Oracle RAC11g
1. RAC adopts the archive mode and does not stop database operations. You can use the cp command in Oracle11g asmcmd to perform operations.
First, simulate the environment:

SQL> create tablespace firsoul datafile 'firsoul01. dbf' size 10 m;

Tablespace created.

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

+ DATA/racdb/datafile/system.293.85942.1673

....................................
/Oracle/app/oracle/product/11.2.0/dbs/firsoul01.dbf

Node 1: view the status and information of the data file

SQL> set lines 150

SQL> col file_name for a55

SQL> select file_name, file_id, ONLINE_STATUS, tablespace_name from dba_data_files;

FILE_NAME FILE_ID ONLINE _ TABLESPACE_NAME

-------------------------------------------------------------------------------------

+ DATA/racdb/datafile/system.293.859442573 1 SYSTEM

+ DATA/racdb/datafile/sysaux.288.859410489 2 ONLINE SYSAUX

+ DATA/racdb/datafile/undotbs1.287.85941041 3 ONLINE UNDOTBS1

+ DATA/racdb/datafile/undotbs2.285.859410415 4 ONLINE UNDOTBS2

+ DATA/racdb/datafile/users.278.85942.1619 5 ONLINE USERS

+ DATA/racdb/datafile/test1.261.859451689 6 ONLINE TEST1

/Oracle/app/oracle/product/11.2.0/dbs/firsoul01.dbf 11 ONLINE FIRSOUL

Node 2: view the status and information of data files

SQL> set lines 200

SQL> col file_name for a50

SQL> col tablespace_name for a10

SQL> select file_name, file_id, ONLINE_STATUS, tablespace_name from dba_data_files;

ERROR:

ORA-01157: cannot identify/lock data file 11-see DBWR trace file

ORA-01110: data file 11: '/oracle/app/oracle/product/11.2.0/dbs/firsoul01.dbf'

OFFLINE local data files, or the entire tablespace OFFLINE

SQL> alter database datafile 11 offline;

Database altered.

Use the asmcmd cp command to copy data files

ASMCMD> cp/oracle/app/oracle/product/11.2.0/dbs/firsoul01.dbf.

Copying/oracle/app/oracle/product/11.2.0/dbs/firsoul01.dbf-> + data/racdb/datafile/firsoul01.dbf

ASMCMD> ls-lrt

WARNING: option 'R' is deprecated for 'LS'

Please use 'reverse'

Type Redund Striped Time Sys Name

N firsoul01.dbf => + DATA/ASM/DATAFILE/firsoul01.dbf. 260.882071123

Datafile unprot coarse jun 11 09:00:00 Y SYSAUX.288.859438589

Datafile unprot coarse jun 11 09:00:00 Y system.293.859437973

Datafile unprot coarse jun 11 09:00:00 Y TEST1.261.859451689

Datafile unprot coarse jun 11 09:00:00 Y undotbs1.287.85941041

Datafile unprot coarse jun 11 09:00:00 Y undotbs2.285.859410415

Datafile unprot coarse jun 11 09:00:00 Y users.278.859410419

Rename data file and view information

SQL> alter database rename file '/oracle/app/oracle/product/11.2.0/dbs/firsoul01.dbf' to '+ data/racdb/datafile/firsoul01.dbf ';

Database altered.
SQL> select file_name, file_id, ONLINE_STATUS, tablespace_name from dba_data_files

2;