简介
oracle rac环境将datafile创建到本地文件系统，会造成间歇性访问异常。
若数据文件在节点1，而客户端访问连接的节点1，那么可以正常操作；而若数据文件在节点1，而客户端访问连接的节点2，那么此时就会报错：
1ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
2ORA-01110: data file 8: '/home/oracle/a.dbf'
实验
1sql "alter database datafile 8 offline";
2recover datafile 8;
3
4run { copy datafile '/home/oracle/a.dbf' to '+data'; }
5run { switch datafile '/home/oracle/a.dbf' to datafilecopy '+DATA/gbk/datafile/test.302.1120761841'; }
6
7sql "alter database datafile 8 online";
8
详细过程：
1[oracle@xtrac1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -responseFile NO_VALUE \
2> -gdbname gbk -sid gbk \
3> -sysPassword oracle -systemPassword oracle \
4> -datafileDestination '+DATA' -recoveryAreaDestination 'DATA/' \
5> -storageType ASM -asmsnmpPassword oracle -diskGroupName 'DATA' \
6> -characterset ZHS16GBK -nationalCharacterSet AL16UTF16 \
7> -redoLogFileSize 50 \
8> -sampleSchema true \
9> -memoryPercentage 5 \
10> -databaseType OLTP \
11> -emConfiguration NONE \
12> -nodeinfo xtrac1,xtrac2
13Copying database files
141% complete
153% complete
169% complete
1715% complete
1821% complete
1930% complete
20Creating and starting Oracle instance
2132% complete
2236% complete
2340% complete
2444% complete
2545% complete
2648% complete
2750% complete
28Creating cluster database views
2952% complete
3070% complete
31Completing Database Creation
3273% complete
3376% complete
3477% complete
3578% complete
3679% complete
3788% complete
3897% complete
39100% complete
40Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/gbk/gbk.log" for further details.
41[oracle@xtrac1 ~]$ crsctl stat res -t
42--------------------------------------------------------------------------------
43NAME TARGET STATE SERVER STATE_DETAILS
44--------------------------------------------------------------------------------
45Local Resources
46--------------------------------------------------------------------------------
47ora.DATA.dg
48 ONLINE ONLINE xtrac1
49 ONLINE ONLINE xtrac2
50ora.LISTENER.lsnr
51 ONLINE ONLINE xtrac1
52 ONLINE ONLINE xtrac2
53ora.OCR.dg
54 ONLINE ONLINE xtrac1
55 ONLINE ONLINE xtrac2
56ora.asm
57 ONLINE ONLINE xtrac1 Started
58 ONLINE ONLINE xtrac2 Started
59ora.gsd
60 OFFLINE OFFLINE xtrac1
61 OFFLINE OFFLINE xtrac2
62ora.net1.network
63 ONLINE ONLINE xtrac1
64 ONLINE ONLINE xtrac2
65ora.ons
66 ONLINE ONLINE xtrac1
67 ONLINE ONLINE xtrac2
68--------------------------------------------------------------------------------
69Cluster Resources
70--------------------------------------------------------------------------------
71ora.LISTENER_SCAN1.lsnr
72 1 ONLINE ONLINE xtrac2
73ora.cvu
74 1 ONLINE ONLINE xtrac1
75ora.xtorcl.db
76 1 ONLINE ONLINE xtrac1 Open
77 2 ONLINE ONLINE xtrac2 Open
78ora.xtrac1.vip
79 1 ONLINE ONLINE xtrac1
80ora.xtrac2.vip
81 1 ONLINE ONLINE xtrac2
82ora.gbk.db
83 1 ONLINE ONLINE xtrac1 Open
84 2 ONLINE ONLINE xtrac2 Open
85ora.oc4j
86 1 ONLINE ONLINE xtrac1
87ora.scan1.vip
88 1 ONLINE ONLINE xtrac2
89[oracle@xtrac1 ~]$ ORACLE_SID=gbk1
90[oracle@xtrac1 ~]$ sas
91
92SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 17:43:09 2022
93
94Copyright (c) 1982, 2013, Oracle. All rights reserved.
95
96
97Connected to:
98Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
99With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
100Data Mining and Real Application Testing options
101
102SQL> select * from v$tablespace;
103
104 TS# NAME INC BIG FLA ENC
105---------- ------------------------------ --- --- --- ---
106 0 SYSTEM YES NO YES
107 1 SYSAUX YES NO YES
108 2 UNDOTBS1 YES NO YES
109 4 USERS YES NO YES
110 3 TEMP NO NO YES
111 6 EXAMPLE YES NO YES
112 5 UNDOTBS2 YES NO YES
113
1147 rows selected.
115
116SQL>
117SQL> col name format a80
118SQL> set pagesize 9999
119SQL> set line 1000
120SQL>
121SQL> select ts#,file#,name,status from v$datafile d;
122
123 TS# FILE# NAME STATUS
124---------- ---------- -------------------------------------------------------------------------------- -------
125 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM
126 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
127 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
128 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE
129 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE
130 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
131
1326 rows selected.
133
134SQL> create tablespace test ;
135
136Tablespace created.
137
138SQL> select ts#,file#,name,status from v$datafile d;
139
140 TS# FILE# NAME STATUS
141---------- ---------- -------------------------------------------------------------------------------- -------
142 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM
143 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
144 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
145 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE
146 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE
147 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
148 7 7 +DATA/gbk/datafile/test.307.1120758253 ONLINE
149
1507 rows selected.
151
152SQL> alter tablespace test add datafile '/home/oracle/a.dbf' size 10m;
153
154Tablespace altered.
155
156SQL> create table t1 tablespace test as select * from dba_objects;
157
158Table created.
159
160SQL> select ts#,file#,name,status from v$datafile d;
161
162 TS# FILE# NAME STATUS
163---------- ---------- -------------------------------------------------------------------------------- -------
164 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM
165 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
166 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
167 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE
168 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE
169 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
170 7 7 +DATA/gbk/datafile/test.307.1120758253 ONLINE
171 7 8 /home/oracle/a.dbf ONLINE
172
1738 rows selected.
174
175SQL> select owner,segment_name,tablespace_name from dba_extents where file_id=8;
176
177OWNER SEGMENT_NAME TABLESPACE_NAME
178------------------------------ --------------------------------------------------------------------------------- ------------------------------
179SYS T1 TEST
180SYS T1 TEST
181SYS T1 TEST
182SYS T1 TEST
183SYS T1 TEST
184
185SQL> select count(*) from t1;
186
187 COUNT(*)
188----------
189 87039
190
191SQL> exit
192Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
193With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
194Data Mining and Real Application Testing options
195
196-- 节点2查询
197[oracle@xtrac2 ~]$ ORACLE_SID=gbk2
198[oracle@xtrac2 ~]$ sas
199
200SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:32:50 2022
201
202Copyright (c) 1982, 2013, Oracle. All rights reserved.
203
204
205Connected to:
206Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
207With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
208Data Mining and Real Application Testing options
209
210SQL> select count(*) from t1;
211select count(*) from t1
212 *
213ERROR at line 1:
214ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
215ORA-01110: data file 8: '/home/oracle/a.dbf'
216
217
218SQL> exit
219Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
220With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
221Data Mining and Real Application Testing options
接下来做恢复操作：
1RMAN> sql "alter database datafile 8 offline";
2
3sql statement: alter database datafile 8 offline
4RMAN-00571: ===========================================================
5RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
6RMAN-00571: ===========================================================
7RMAN-03009: failure of sql command on default channel at 11/14/2022 18:37:35
8RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 8 offline
9ORA-01145: offline immediate disallowed unless media recovery enabled
10
11RMAN> exit
12
13
14Recovery Manager complete.
15
16-- 2个节点都关闭，然后开启归档模式
17[oracle@xtrac1 ~]$ sas
18
19SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:38:09 2022
20
21Copyright (c) 1982, 2013, Oracle. All rights reserved.
22
23
24Connected to:
25Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
26With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
27Data Mining and Real Application Testing options
28
29SQL> shutdown immediate
30Database closed.
31Database dismounted.
32ORACLE instance shut down.
33SQL> startup mount
34ORACLE instance started.
35
36Total System Global Area 2605551616 bytes
37Fixed Size 2256072 bytes
38Variable Size 721421112 bytes
39Database Buffers 1862270976 bytes
40Redo Buffers 19603456 bytes
41Database mounted.
42SQL> alter database archivelog;
43
44Database altered.
45
46SQL> alter database open;
47
48Database altered.
49
50SQL> exit
51Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
52With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
53Data Mining and Real Application Testing options
54
55
56-- 开始恢复
57
58[oracle@xtrac1 ~]$ rman target /
59
60Recovery Manager: Release 11.2.0.4.0 - Production on Mon Nov 14 18:41:12 2022
61
62Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
63
64connected to target database: GBK (DBID=2254511533)
65
66RMAN> sql "alter database datafile 8 offline";
67
68using target database control file instead of recovery catalog
69sql statement: alter database datafile 8 offline
70
71RMAN> recover datafile 8;
72
73Starting recover at 14-NOV-22
74allocated channel: ORA_DISK_1
75channel ORA_DISK_1: SID=68 instance=gbk1 device type=DISK
76
77starting media recovery
78media recovery complete, elapsed time: 00:00:00
79
80Finished recover at 14-NOV-22
81
82RMAN> report schema;
83
84Report of database schema for database with db_unique_name GBK
85
86List of Permanent Datafiles
87===========================
88File Size(MB) Tablespace RB segs Datafile Name
89---- -------- -------------------- ------- ------------------------
901 760 SYSTEM *** +DATA/gbk/datafile/system.306.1120757563
912 520 SYSAUX *** +DATA/gbk/datafile/sysaux.303.1120757563
923 105 UNDOTBS1 *** +DATA/gbk/datafile/undotbs1.299.1120757563
934 5 USERS *** +DATA/gbk/datafile/users.297.1120757563
945 313 EXAMPLE *** +DATA/gbk/datafile/example.318.1120757617
956 25 UNDOTBS2 *** +DATA/gbk/datafile/undotbs2.316.1120757677
967 100 TEST *** +DATA/gbk/datafile/test.307.1120758253
978 10 TEST *** /home/oracle/a.dbf
98
99List of Temporary Files
100=======================
101File Size(MB) Tablespace Maxsize(MB) Tempfile Name
102---- -------- -------------------- ----------- --------------------
1031 56 TEMP 32767 +DATA/gbk/tempfile/temp.321.1120757617
104
105RMAN>
106
107RMAN>
108
109RMAN> run { copy datafile '/home/oracle/a.dbf' to '+data'; }
110
111Starting backup at 14-NOV-22
112using channel ORA_DISK_1
113channel ORA_DISK_1: starting datafile copy
114input datafile file number=00008 name=/home/oracle/a.dbf
115output file name=+DATA/gbk/datafile/test.302.1120761841 tag=TAG20221114T184401 RECID=3 STAMP=1120761841
116channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
117Finished backup at 14-NOV-22
118
119RMAN> run { switch datafile '/home/oracle/a.dbf' to datafilecopy '+DATA/gbk/datafile/test.302.1120761841'; }
120
121datafile 8 switched to datafile copy
122input datafile copy RECID=3 STAMP=1120761841 file name=+DATA/gbk/datafile/test.302.1120761841
123
124RMAN> exit
125
126
127Recovery Manager complete.
128[oracle@xtrac1 ~]$ sas
129
130SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:45:34 2022
131
132Copyright (c) 1982, 2013, Oracle. All rights reserved.
133
134
135Connected to:
136Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
137With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
138Data Mining and Real Application Testing options
139
140SQL> set line 1000
141SQL> select ts#,file#,name,status from v$datafile d;
142
143 TS# FILE# NAME STATUS
144---------- ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------
145 0 1 +DATA/gbk/datafile/system.306.1120757563 SYSTEM
146 1 2 +DATA/gbk/datafile/sysaux.303.1120757563 ONLINE
147 2 3 +DATA/gbk/datafile/undotbs1.299.1120757563 ONLINE
148 4 4 +DATA/gbk/datafile/users.297.1120757563 ONLINE
149 6 5 +DATA/gbk/datafile/example.318.1120757617 ONLINE
150 5 6 +DATA/gbk/datafile/undotbs2.316.1120757677 ONLINE
151 7 7 +DATA/gbk/datafile/test.307.1120758253 ONLINE
152 7 8 +DATA/gbk/datafile/test.302.1120761841 OFFLINE
153
1548 rows selected.
155
156SQL> alter database datafile 8 online;
157
158Database altered.
159
160SQL>
161SQL> select count(*) from t1;
162
163 COUNT(*)
164----------
165 87039
166
节点2访问：
1[oracle@xtrac2 ~]$ sas
2
3SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 14 18:50:16 2022
4
5Copyright (c) 1982, 2013, Oracle. All rights reserved.
6
7
8Connected to:
9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
10With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
11Data Mining and Real Application Testing options
12
13SQL> select count(*) from t1;
14
15 COUNT(*)
16----------
17 87039
数据库访问正常！！！
最后删除数据库，清理环境：
1[oracle@cwrac1 ~]$ dbca -silent -deleteDatabase -sourceDB gbk
2Connecting to database
39% complete
414% complete
519% complete
623% complete
728% complete
833% complete
938% complete
1047% complete
11Updating network configuration files
1248% complete
1352% complete
14Deleting instances and datafiles
1566% complete
1680% complete
1795% complete
18100% complete
19Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/gbk.log" for further details.
总结
1、在rac中，在增加数据文件时，一定需要把数据文件放在共享存储中，否则会造成访问异常
2、在移动数据文件时，需要注意目标端是否已经含有相同的数据文件名，避免被覆盖。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
