rac把数据文件创建在不共享的地方
oracle 11g 11.2.0.1
linux server 64位 5.4
node1:
上创建一个数据文件'/tmp/rman.dbf' 这个文件没有放在共享磁盘上
然后创建一个表 casd ,并添加数据,此时在node1上能查询casd的信息
然后到node2查询的时候会提示如下错误:
SQL> select * from casd;
select * from casd
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/tmp/rman.dbf'
然后查看跟踪文件:
[oracle@rac2 ~]$ cat /u01/diag/rdbms/racdb/racdb2/trace/racdb2_ora_6620.trc
Trace file /u01/diag/rdbms/racdb/racdb2/trace/racdb2_ora_6620.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle
System name: Linux
Node name: rac2.localdomain
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:48 EDT 2009
Machine: x86_64
Instance name: racdb2
Redo thread mounted by this instance: 2
Oracle process number: 42
Unix process pid: 6620, image: oracle@rac2.localdomain (TNS V1-V3)
*** 2013-04-25 16:05:24.915
*** SESSION ID:(49.6) 2013-04-25 16:05:24.915
*** CLIENT ID:() 2013-04-25 16:05:24.915
*** SERVICE NAME:(SYS$USERS) 2013-04-25 16:05:24.915
*** MODULE NAME:(sqlplus@rac2.localdomain (TNS V1-V3)) 2013-04-25 16:05:24.915
*** ACTION NAME:() 2013-04-25 16:05:24.915
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 1 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 1 csec) -----
*** 2013-04-25 16:06:10.952
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 7: '/tmp/rman.dbf'
*** 2013-04-25 16:06:33.733
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
*** 2013-04-25 16:06:45.051
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 7: '/tmp/rman.dbf'
*** 2013-04-25 16:07:23.495
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 7: '/tmp/rman.dbf'
此时解决方法
1,用asmcmd cp这个文件到共享磁盘上,然后rename
具体步骤如下:(笔者在安装rac的时候环境出错,还请见谅)
要使用asmcmd要保证变量的正确性:
node1:
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/oracle/grid
[oracle@rac1 ~]$ export ORACLE_SID=+ASM1
[oracle@rac1 ~]$ asmcmd
ASMCMD> cd +DATA/racdb/datafile
ASMCMD> ls
SYSAUX.260.810947931
SYSTEM.259.810947895
TEST.268.811013157
UNDOTBS1.261.810947961
UNDOTBS2.263.810948005
USERS.264.810948015
test.dbf
ASMCMD>
ASMCMD> ad --这里随便输入一个命令会提示工具支持的哪些命令
commands:
--------
md_backup, md_restore
lsattr, setattr
cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
mkdir, pwd, rm, rmalias
chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
offline, online, rebal, remap, umount
dsget, dsset, lsop, shutdown, spbackup, spcopy, spget
spmove, spset, startup
chtmpl, lstmpl, mktmpl, rmtmpl
chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr
volcreate, voldelete, voldisable, volenable, volinfo
volresize, volset, volstat
ASMCMD> cp /tmp/rman.dbf +DATA/racdb/datafile/rman.dbf --开始拷贝
copying /tmp/rman.dbf -> +DATA/racdb/datafile/rman.dbf
ASMCMD> cd +DATA/racdb/datafile
ASMCMD> ls
SYSAUX.260.810947931
SYSTEM.259.810947895
TEST.268.811013157
UNDOTBS1.261.810947961
UNDOTBS2.263.810948005
USERS.264.810948015
rman.dbf
test.dbf
然后退出用sqlplus更改
[oracle@rac1 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 25 16:40:28 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
SQL>
SQL> ALTER DATABASE RENAME FILE '/tmp/rman.dbf' TO '+DATA/racdb/datafile/rman.dbf';
ALTER DATABASE RENAME FILE '/tmp/rman.dbf' TO '+DATA/racdb/datafile/rman.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 7 - file is in use or recovery
ORA-01110: data file 7: '/tmp/rman.dbf'
SQL>
SQL>
SQL> alter datafile '/tmp/rman.dbf' offline; --这里是非归档模式,所以无法直接用offline
alter datafile '/tmp/rman.dbf' offline
*
ERROR at line 1:
ORA-00940: invalid ALTER command
<