Correction of accidental placement of data files to the local file system when 11g1_lerac adds a new tablespace

Today, I read an article saying that the data files of the newly created tablespace are put on a local system rather than shared storage. The topic of Oracle's Chinese technical support blog is: when data files are mistakenly created on a local disk in RAC

Today, I read an article saying that the data files of the newly created tablespace are put on a local system rather than shared storage. The topic of Oracle's Chinese technical support blog is: when data files are mistakenly created on a local disk in RAC

Today, I read an article saying that the data files of the newly created tablespace are put on a local system rather than shared storage.

Oracle Chinese technical support blog title: correction when data files are mistakenly created on a local disk in RAC

So I think 11G is also compatible with these operations, but one of the new features of 11G is that it can directly support the ASM file system to directly copy files with the local file system, that is, there are three methods.

1. copy datafile method of RMAN

2. dbms_file_transfer Method

3. The direct cp method in ASMCMD

Three methods are demonstrated:

RMAN

1) create a local tablespace.

SQL> create tablespace andy datafile '/home/oracle/andy. dbf' size 100 M;

Tablespace created.

2) Check the status

SQL> select name, status from v $ datafile;

NAME

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

STATUS

-------

+ DATA/racdb/datafile/system.256.823634487

SYSTEM

+ DATA/racdb/datafile/sysaux.257.823634489

ONLINE

+ DATA/racdb/datafile/undotbs1.258.823634489

ONLINE

NAME

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

STATUS

-------

+ DATA/racdb/datafile/users.259.823634491

ONLINE

+ DATA/racdb/datafile/example.264.8236000053

ONLINE

+ DATA/racdb/datafile/undotbs2.265.823634943

ONLINE

NAME

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

STATUS

-------

+ DATA/racdb/datafile/undotbs3.266.823634945

ONLINE

+ DATA/racdb/datafile/andylhz.272.823709117

ONLINE

+ DATA/racdb/datafile/archivedata.274.109748169

ONLINE

NAME

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

STATUS

-------

+ DATA/racdb/datafile/archivedata01.275.20.72.1635

ONLINE

/Home/oracle/andy. dbf

ONLINE

3) Create the table andy01 on the local tablespace.

SQL> create table andy01 tablespace andy as select * from dba_objects;

Table created.

4) create a table on the second instance and place it in the tablespace andy. Pay attention to the red font. The report cannot locate the andy. dbf data file.

[Oracle @ racdb2 ~] $ Sqlplus/nolog

SQL * Plus: Release 11.2.0.1.0 Production on Wed Feb 12 17:00:30 2014

Copyright (c) 1982,200 9, Oracle. All rights reserved.

SQL> conn/as sysdba

Connected.

SQL> create table andy02 tablespace andy as select * from dba_objects;

Create table andy02 tablespace andy as select * from dba_objects

*

ERROR at line 1:

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

ORA-01110: data file 11: '/home/oracle/andy. dbf'

5) then perform the correction operation. The andy tablespace is offline first.

Then copy RMAN and move the data file online.

SQL> alter database rename file '/home/oracle/andy. dbf' to' + data/racdb/datafile/andy. dbf ';

Method 2

1) first create two directories: offline tablespace andy

SQL> create directory andy01 as '/home/oracle /';

Directory created.

SQL> create directory andy02 as '+ data/racdb/datafile /';

Directory created.

SQL>

2) file transmission

SQL> exec dbms_file_transfer.copy_file ('andy01 ', 'andy. dbf', 'andy02', 'andy. dbf ');

PL/SQL procedure successfully completed.

SQL>

Go to asmcmd to check

ASMCMD> pwd

+ Data/racdb/datafile

ASMCMD> ls

ANDYLHZ.272.823709117

Archivedata.274.425748169

Archivedata01.275.20.72.1635

COPY_FILE.279.839351419

Example.264.8236000053

SYSAUX.257.823634489

SYSTEM.256.823634487

UNDOTBS1.258.823634489

UNDOTBS2.265.823634943

UNDOTBS3.266.823634945

USERS.259.823634491

Andy. dbf

ASMCMD>

Copied successfully

Move data files and online data files

SQL> alter database rename file '/home/oracle/andy. dbf' to' + data/racdb/datafile/andy. dbf ';

Database altered.

SQL> alter tablespace andy online;

Tablespace altered.

Third, the grid user enters the asmcmd environment and then directly enters the path of the data file in the RAC environment. Because the data file is created in the home directory of oracle ,, grid does not have permission to read, so the root user is used to move the data file to the grid home directory and change the permission

[Root @ racdb1 grid] # mv andy. dbf/home/grid/

[Root @ racdb1 grid] # chown grid. asmadmin andy. dbf

[Root @ racdb1 grid] # su-grid

[Grid @ racdb1 ~] $ Asmcmd

ASMCMD> cd data/racdb/datafile/

ASMCMD> ls

ANDYLHZ.272.823709117

Archivedata.274.425748169

Archivedata01.275.20.72.1635

Example.264.8236000053

SYSAUX.257.823634489

SYSTEM.256.823634487

UNDOTBS1.258.823634489

UNDOTBS2.265.823634943

UNDOTBS3.266.823634945

USERS.259.823634491

ASMCMD> cp/home/grid/andy. dbf ./

Copying/home/grid/andy. dbf-> + data/racdb/datafile/andy. dbf

ASMCMD> ls

ANDYLHZ.272.823709117

Archivedata.274.425748169

Archivedata01.275.20.72.1635

Example.264.8236000053

SYSAUX.257.823634489

SYSTEM.256.823634487

UNDOTBS1.258.823634489

UNDOTBS2.265.823634943

UNDOTBS3.266.823634945

USERS.259.823634491

Andy. dbf

ASMCMD>

Move data files to shared storage

SQL> alter database rename file '/home/oracle/andy. dbf' to' + data/racdb/datafile/andy. dbf ';

Database altered.

SQL>

Online the data file.

Verify the data file status

SQL> select name, status from v $ datafile;

NAME

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

STATUS

-------

+ DATA/racdb/datafile/system.256.823634487

SYSTEM

+ DATA/racdb/datafile/sysaux.257.823634489

ONLINE

+ DATA/racdb/datafile/undotbs1.258.823634489

ONLINE

NAME

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

STATUS

-------

+ DATA/racdb/datafile/users.259.823634491

ONLINE

+ DATA/racdb/datafile/example.264.8236000053

ONLINE

+ DATA/racdb/datafile/undotbs2.265.823634943

ONLINE

NAME

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

STATUS

-------

+ DATA/racdb/datafile/undotbs3.266.823634945

ONLINE

+ DATA/racdb/datafile/andylhz.272.823709117

ONLINE

+ DATA/racdb/datafile/archivedata.274.109748169

ONLINE

NAME

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

STATUS

-------

+ DATA/racdb/datafile/archivedata01.275.20.72.1635

ONLINE

+ DATA/racdb/datafile/andy. dbf

ONLINE

11 rows selected.

This article is from the "Shadow Knight" blog. Please keep this source