在Oracle 11g的RAC环境中，将数据文件'/tmp/rman.dbf'创建在非共享位置导致在不同节点间无法访问。错误信息为ORA-01157和ORA-01110。解决方法包括使用asmcmd将文件复制到共享磁盘并重命名，或者通过RMAN在归档模式下备份和恢复数据文件。经过这些步骤，数据文件可以在所有节点上正常访问。
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
SQL> alter database datafile '/tmp/rman.dbf' offline drop; --使用offline drop
Database altered.
SQL> ALTER DATABASE RENAME FILE '/tmp/rman.dbf' TO '+DATA/racdb/datafile/rman.dbf'; --此时再次更改
Database altered.
SQL>
SQL> alter database datafile '+DATA/racdb/datafile/rman.dbf' online; --直接online会报错（因为scn不是当前的）
alter database datafile '+DATA/racdb/datafile/rman.dbf' online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '+DATA/racdb/datafile/rman.dbf'
SQL> recover database datafile 7; --笔者记错命令
ORA-00274: illegal recovery option DATAFILE
SQL> recover datafile 7; --使用介质恢复
Media recovery complete.
SQL>
SQL>
SQL> alter database datafile '+DATA/racdb/datafile/rman.dbf' online; --这次online成功了
Database altered.
SQL>
SQL>
SQL>
SQL> select * from casd --查询数据成功
2 ;
ID NAME
---------- --------------------
1 cc
再到节点二查询：
[oracle@rac2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 25 16:53:46 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> select * from casd;
ID NAME
---------- --------------------
1 cc
OK 成功！！！
2，用rman恢复
1，注意使用rman备份数据文件的时候要在归档模式下
否则在备份的时候提示如下：
RMAN> backup datafile 7;
Starting backup at 25-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/25/2013 17:03:37
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
要模拟这个案例笔者停了node1实例，然后mount 然后alter database archivelog;
结果提示：
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
然后回到node2 把实例也停了，然后再回到node1
SQL> alter database archivelog;
Database altered.
然后执行backup
[oracle@rac1 tmp]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 25 17:10:23 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=808564626, not open)--这里我是在mount状态下连接的，当然也可以在open状态下
RMAN> backup datafile 7;
Starting backup at 25-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/tmp/rman.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-13
channel ORA_DISK_1: finished piece 1 at 25-APR-13
piece handle=/u01/app/oracle/dbs/05o7vrob_1_1 tag=TAG20130425T171035 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-13
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 17.67M DISK 00:00:16 25-APR-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130425T162944
Piece Name: /u01/app/oracle/dbs/02o7vpbo_1_1
Control File Included: Ckp SCN: 1987592 Ckp time: 25-APR-13
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.10M DISK 00:00:01 25-APR-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130425T171035
Piece Name: /u01/app/oracle/dbs/05o7vrob_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 1991004 25-APR-13 /tmp/rman.dbf
[oracle@rac1 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 25 17:11:39 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> alter database open;
Database altered.
SQL> !mv /tmp/rman.dbf /tmp/rman.dbf.bak
SQL>
SQL> select * from rman;
ID NAME
---------- ----------
1 cc
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from rman;
select * from rman
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/tmp/rman.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
[oracle@rac1 tmp]$ rman target /
RMAN> run{
2> set newname for datafile '/tmp/rman.dbf' to '+DATA/racdb/datafile/rman.dbf';
3> restore datafile 7;
4> switch datafile 7;
5> recover datafile 7;
6> }
executing command: SET NEWNAME
Starting restore at 25-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to +DATA/racdb/datafile/rman.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/dbs/05o7vrob_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/dbs/05o7vrob_1_1 tag=TAG20130425T171035
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 25-APR-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of switch command on default channel at 04/25/2013 17:19:13
ORA-19623: file 7 is open
此时，我用sqlplus：
SQL> alter database datafile '/tmp/rman.dbf' offline drop;
然后再次：
RMAN> run{
2> set newname for datafile '/tmp/rman.dbf' to '+DATA/racdb/datafile/rman.dbf';
3> restore datafile 7;
4> switch datafile 7;
5> recover datafile 7;
6> }
executing command: SET NEWNAME
Starting restore at 25-APR-13
using channel ORA_DISK_1
datafile 7 is already restored to file +DATA/racdb/datafile/rman.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 25-APR-13
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=813691325 file name=+DATA/racdb/datafile/rman.dbf
Starting recover at 25-APR-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-APR-13
节点1：
SQL> select * from rman;
ID NAME
---------- ----------
1 cc
节点2：
SQL> select * from rman;
ID NAME
---------- ----------
1 cc
OK，完成.........
扫一扫
572
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
