简介
oracle rac环境将datafile创建到本地文件系统，会造成间歇性访问异常。
若数据文件在节点1，而客户端访问连接的节点1，那么可以正常操作；而若数据文件在节点1，而客户端访问连接的节点2，那么此时就会报错：
代码语言：javascript复制ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/home/oracle/a.dbf'
实验
代码语言：javascript复制sql "alter database datafile 8 offline";
recover datafile 8;
run { copy datafile '/home/oracle/a.dbf' to ' data'; }
run { switch datafile '/home/oracle/a.dbf' to datafilecopy ' DATA/gbk/datafile/test.302.1120761841'; }
sql "alter database datafile 8 online";
详细过程：
代码语言：javascript复制[oracle@xtrac1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE
> -gdbname gbk -sid gbk
> -sysPassword oracle -systemPassword oracle
> -datafileDestination ' DATA' -recoveryAreaDestination 'DATA/'
> -storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA'
> -characterset ZHS16GBK -nationalCharacterSet AL16UTF16
> -redoLogFileSize 50
> -sampleSchema true
> -memoryPercentage 5
> -databaseType OLTP
> -emConfiguration NONE
> -nodeinfo xtrac1,xtrac2
Copying database files
1% complete
3% complete
9% complete
15% complete
21% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
77% complete
78% complete
79% complete
88% complete
97% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/gbk/gbk.log" for further details.
[oracle@xtrac1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE xtrac1
ONLINE ONLINE xtrac2
ora.LISTENER.lsnr
ONLINE ONLINE xtrac1
ONLINE ONLINE xtrac2
ora.OCR.dg
ONLINE ONLINE xtrac1
ONLINE ONLINE xtrac2
ora.asm
ONLINE ONLINE xtrac1 Started
ONLINE ONLINE xtrac2 Started
ora.gsd
OFFLINE OFFLINE xtrac1
OFFLINE OFFLINE xtrac2
ora.net1.network
ONLINE ONLINE xtrac1
ONLINE ONLINE xtrac2
ora.ons
ONLINE ONLINE xtrac1
ONLINE ONLINE xtrac2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE xtrac2
ora.cvu
1 ONLINE ONLINE xtrac1
ora.xtorcl.db
1 ONLINE ONLINE xtrac1 Open
2 ONLINE ONLINE xtrac2 Open
ora.xtrac1.vip
1 ONLINE ONLINE xtrac1
ora.xtrac2.vip
1 ONLINE ONLINE xtrac2
ora.gbk.db
1 ONLINE ONLINE xtrac1 Open
2 ONLINE ONLINE xtrac2 Open
ora.oc4j
1 ONLINE ONLINE xtrac1
ora.scan1.vip
1 ONLINE ONLINE xtrac2
[oracle@xtrac1 ~]$ ORACLE_SID=gbk1
[oracle@xtrac1 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 17:43:09 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
5 UNDOTBS2 YES NO YES
7 rows selected.
SQL>
SQL> col name format a80
SQL> set pagesize 9999
SQL> set line 1000
SQL>
SQL> select ts#,file#,name,status from v$datafile d;
TS# FILE# NAME STATUS
---------- ---------- -------------------------------------------------------------------------------- -------
0 1 DATA/gbk/datafile/system.306.1120757563 SYSTEM
1 2 DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
2 3 DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
4 4 DATA/gbk/datafile/users.297.1120757563 ONLINE
6 5 DATA/gbk/datafile/example.318.1120757617 ONLINE
5 6 DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
6 rows selected.
SQL> create tablespace test ;
Tablespace created.
SQL> select ts#,file#,name,status from v$datafile d;
TS# FILE# NAME STATUS
---------- ---------- -------------------------------------------------------------------------------- -------
0 1 DATA/gbk/datafile/system.306.1120757563 SYSTEM
1 2 DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
2 3 DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
4 4 DATA/gbk/datafile/users.297.1120757563 ONLINE
6 5 DATA/gbk/datafile/example.318.1120757617 ONLINE
5 6 DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
7 7 DATA/gbk/datafile/test.307.1120758253 ONLINE
7 rows selected.
SQL> alter tablespace test add datafile '/home/oracle/a.dbf' size 10m;
Tablespace altered.
SQL> create table t1 tablespace test as select * from dba_objects;
Table created.
SQL> select ts#,file#,name,status from v$datafile d;
TS# FILE# NAME STATUS
---------- ---------- -------------------------------------------------------------------------------- -------
0 1 DATA/gbk/datafile/system.306.1120757563 SYSTEM
1 2 DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
2 3 DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
4 4 DATA/gbk/datafile/users.297.1120757563 ONLINE
6 5 DATA/gbk/datafile/example.318.1120757617 ONLINE
5 6 DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
7 7 DATA/gbk/datafile/test.307.1120758253 ONLINE
7 8 /home/oracle/a.dbf ONLINE
8 rows selected.
SQL> select owner,segment_name,tablespace_name from dba_extents where file_id=8;
OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------------ --------------------------------------------------------------------------------- ------------------------------
SYS T1 TEST
SYS T1 TEST
SYS T1 TEST
SYS T1 TEST
SYS T1 TEST
SQL> select count(*) from t1;
COUNT(*)
----------
87039
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
-- 节点2查询
[oracle@xtrac2 ~]$ ORACLE_SID=gbk2
[oracle@xtrac2 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:32:50 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/home/oracle/a.dbf'
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
接下来做恢复操作：
代码语言：javascript复制RMAN> sql "alter database datafile 8 offline";
sql statement: alter database datafile 8 offline
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 11/14/2022 18:37:35
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 8 offline
ORA-01145: offline immediate disallowed unless media recovery enabled
RMAN> exit
Recovery Manager complete.
-- 2个节点都关闭，然后开启归档模式
[oracle@xtrac1 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:38:09 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2605551616 bytes
Fixed Size 2256072 bytes
Variable Size 721421112 bytes
Database Buffers 1862270976 bytes
Redo Buffers 19603456 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
-- 开始恢复
[oracle@xtrac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 14 18:41:12 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: GBK (DBID=2254511533)
RMAN> sql "alter database datafile 8 offline";
using target database control file instead of recovery catalog
sql statement: alter database datafile 8 offline
RMAN> recover datafile 8;
Starting recover at 14-NOV-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 instance=gbk1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-NOV-22
RMAN> report schema;
Report of database schema for database with db_unique_name GBK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 760 SYSTEM *** DATA/gbk/datafile/system.306.1120757563
2 520 SYSAUX *** DATA/gbk/datafile/sysaux.303.1120757563
3 105 UNDOTBS1 *** DATA/gbk/datafile/undotbs1.299.1120757563
4 5 USERS *** DATA/gbk/datafile/users.297.1120757563
5 313 EXAMPLE *** DATA/gbk/datafile/example.318.1120757617
6 25 UNDOTBS2 *** DATA/gbk/datafile/undotbs2.316.1120757677
7 100 TEST *** DATA/gbk/datafile/test.307.1120758253
8 10 TEST *** /home/oracle/a.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 56 TEMP 32767 DATA/gbk/tempfile/temp.321.1120757617
RMAN>
RMAN>
RMAN> run { copy datafile '/home/oracle/a.dbf' to ' data'; }
Starting backup at 14-NOV-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/home/oracle/a.dbf
output file name= DATA/gbk/datafile/test.302.1120761841 tag=TAG20221114T184401 RECID=3 STAMP=1120761841
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-NOV-22
RMAN> run { switch datafile '/home/oracle/a.dbf' to datafilecopy ' DATA/gbk/datafile/test.302.1120761841'; }
datafile 8 switched to datafile copy
input datafile copy RECID=3 STAMP=1120761841 file name= DATA/gbk/datafile/test.302.1120761841
RMAN> exit
Recovery Manager complete.
[oracle@xtrac1 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:45:34 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set line 1000
SQL> select ts#,file#,name,status from v$datafile d;
TS# FILE# NAME STATUS
---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------
0 1 DATA/gbk/datafile/system.306.1120757563 SYSTEM
1 2 DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
2 3 DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
4 4 DATA/gbk/datafile/users.297.1120757563 ONLINE
6 5 DATA/gbk/datafile/example.318.1120757617 ONLINE
5 6 DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
7 7 DATA/gbk/datafile/test.307.1120758253 ONLINE
7 8 DATA/gbk/datafile/test.302.1120761841 OFFLINE
8 rows selected.
SQL> alter database datafile 8 online;
Database altered.
SQL>
SQL> select count(*) from t1;
COUNT(*)
----------
87039
节点2访问：
代码语言：javascript复制[oracle@xtrac2 ~]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:50:16 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select count(*) from t1;
COUNT(*)
----------
87039
数据库访问正常！！！
最后删除数据库，清理环境：
代码语言：javascript复制[oracle@cwrac1 ~]$ dbca -silent -deleteDatabase -sourceDB gbk
Connecting to database
9% complete
14% complete
19% complete
23% complete
28% complete
33% complete
38% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instances and datafiles
66% complete
80% complete
95% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/gbk.log" for further details.
总结
1、在rac中，在增加数据文件时，一定需要把数据文件放在共享存储中，否则会造成访问异常
2、在移动数据文件时，需要注意目标端是否已经含有相同的数据文件名，避免被覆盖。
