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