In a RAC routine patrol, query table space usage, Node 1 unexpectedly thrown, DBWR process can not lock ' d:oraclefcdatalixora.dbf ' this data file; feeling wrong ah, very ominous feeling;
Just look at the name to guess, the data file is built to RAC Node 2 local;
Look at the file status:
Sql> select File_name,tablespace_name from Dba_data_files;
file_name
--------------------------------------------------------------------------------
+data/lixora/datafile/sys_yyxt.271.785089053
+data/lixora/datafile/sys_yyxt.270.785089231
+data/lixora/datafile/sys_yyxt.269.785089375
+data/lixora/datafile/users.276.784277257
+data/lixora/datafile/sysaux.274.784277257
+data/lixora/datafile/undotbs1.275.784277257
+data/lixora/datafile/system.273.784277257
+data/lixora/datafile/undotbs2.284.784277321
+data/lixora/datafile/sys_yyxt.268.785089665
+data/lixora/datafile/sys_yyxt.265.785089753
+data/lixora/datafile/sys_yyxt.264.785089843
file_name
--------------------------------------------------------------------------------
+data/lixora/datafile/sys_yyxt.400.785089933
+data/lixora/datafile/sys_yyxt01
+data/lixora/datafile/sys_yyxt02.dbf
+data/lixora/datafile/sys_jkxt.401.785090209
+data/lixora/datafile/sys_yyxt03.dbf
+data/lixora/datafile/sys_yyxt201401
+data/lixora/datafile/sys_yyxt201402
+data/lixora/datafile/sys_yyxt201403
+data/lixora/datafile/sys_yyxt201404
+data/lixora/datafile/sys_yyxt201405
+data/lixora/datafile/sys_yyxt201407
file_name
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixora.dbf--------------scared the baby!
/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/DBS/D:ORACLEFCDATALIXORADATA.DBF------------------Scared the baby!
Rows selected.
Sure enough, the file was built to the RAC node local, and the data file name, how wonderful, there are many wonderful, this guy is really looking at ancient and modern, and then a big hand, write down ' Hello word ' and then smile ...
Our developers are also miserable, ah, single-machine Oracle is not clear, not to mention the RAC ....
Once again, DBFile is actually built under the/u01/app/oracle/product/10.2.0/db_1/dbs/directory.
Sql>!ls/u01/app/oracle/product/10.2.0/db_1/dbs/
Ab_+asm2.dat
d:oraclefcdatalixoratemp.dbfHc_+asm2.dat Initdw.ora orapw+asm2
Cdbsmutex
d:oraclefcdatalixoratemp.dbfHc_orcl2.dat Init.ora Orapworcl2
D:oraclefcdatalixoradata.dbf
D:\oracle\oradata\orcl\hhygbp_temp_dbfInitlixora2.ora Initorcl2.ora snapcf_lixora2.f
D:ORACLEFCDATALIXORA.DBF hc_lixora2.dat Init+asm2.ora orapwlixora2 snapcf_orcl2.f
Take a closer look at the content as if there's something wrong with it?
It looks like temp file.
Sql> select name from V$tempfile;
NAME
--------------------------------------------------------------------------------
+data/lixora/tempfile/temp.283.784277305
/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/DBS/D:ORACLEFCDATALIXORATEMP.DBF-------
When did----come to this problem?
Sql> Select Creation_time,name from V$datafile where Name= '/u01/app/oracle/product/10.2.0/db_1/dbs/d:o RACLEFCDATALIXORA.DBF ';
Creation_tim
------------
NAME
--------------------------------------------------------------------------------
18-may-15
/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixora.dbf
Sql> Select Creation_time,name from V$datafile where Name= '/u01/app/oracle/product/10.2.0/db_1/dbs/d:o RACLEFCDATALIXORADATA.DBF ';
Creation_tim
------------
NAME
--------------------------------------------------------------------------------
18-may-15
/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoradata.dbf
Let's take care of the next step.
------Tempfile is mistakenly built to the RAC node for local processing:
Temp file is simple to handle, delete first, add it again.
Sql> select * from V$tempfile;
file# creation_change# creation_tim ts# rfile# STATUS ENABLED BYTES BLOCKS create_bytes block_size
---------- ---------------- ------------ ---------- ---------- ------- ---------- ---------- ---------- ------------ ---- ------
NAME
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------
1 388351 26-may-12 3 1 ONLINE READ WRITE 1.0737E+10 1310720 20971520 8192
+data/lixora/tempfile/temp.283.784277305
2 6324358667 18-may-15 1 ONLINE READ WRITE 83886080 10240 83886080 8192
/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoratemp.dbf
sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/DBS/D:ORACLEFCDATALIXORATEMP.DBF ' offline;
Database altered.
sql> ALTER DATABASE Tempfile '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoratemp.dbf ' drop;
Database altered.
Sql> select * from V$tempfile;
file# creation_change# creation_tim ts# rfile# STATUS ENABLED BYTES BLOCKS create_bytes block_size
---------- ---------------- ------------ ---------- ---------- ------- ---------- ---------- ---------- ------------ ---- ------
NAME
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------
1 388351 26-may-12 3 1 ONLINE READ WRITE 1.0737E+10 1310720 20971520 8192
+data/lixora/tempfile/temp.283.784277305
Sql> Show Parameter Db_crea
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_create_file_dest string +data
Db_create_online_log_dest_1 string
Db_create_online_log_dest_2 string
Db_create_online_log_dest_3 string
Db_create_online_log_dest_4 string
Db_create_online_log_dest_5 string
Sql> select * from V$tablespace;
ts# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM Yes NO Yes
1 UNDOTBS1 Yes NO Yes
2 Sysaux Yes NO Yes
4 USERS Yes NO Yes
3 TEMP No no YES
5 UNDOTBS2 Yes NO Yes
7 Sys_yyxt Yes NO Yes
6 SYS_JKXT Yes NO Yes
8 Lixora Yes NO Yes
9 Lixoradata Yes NO Yes
Ten Lixoratemp no no YES
One by one rows selected.
Sql> alter tablespace lixoratemp add tempfile ' +data ' size 100m autoextend on;
Tablespace altered.
Sql> select * from V$tempfile;
file# creation_change# creation_tim ts# rfile# STATUS ENABLED BYTES BLOCKS create_bytes block_size
---------- ---------------- ------------ ---------- ---------- ------- ---------- ---------- ---------- ------------ ---- ------
NAME
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------
1 388351 26-may-12 3 1 ONLINE READ WRITE 1.0737E+10 1310720 20971520 8192
+data/lixora/tempfile/temp.283.784277305
2 6629906774 04-aug-15 3 2 ONLINE READ WRITE 104857600 12800 104857600 8192
+data/lixora/tempfile/temp.1592.886865983
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----data files migrated to the RAC ASM storage:
There are 2 methods available: Rman cp;dbms_file_transfer.copy_file "plsql Package Direct copy
Sql> select file_name, Tablespace_name, bytes/1024/1024/1024 from Dba_data_files where tablespace_name= ' LIXORA ';
file_name
--------------------------------------------------------------------------------
Tablespace_name bytes/1024/1024/1024
------------------------------ --------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixora.dbf
Lixora. 0390625
Sql> select file_name, Tablespace_name, bytes/1024/1024/1024 from Dba_data_files where tablespace_name= ' LIXORADATA ' ;
file_name
--------------------------------------------------------------------------------
Tablespace_name bytes/1024/1024/1024
------------------------------ --------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoradata.dbf
Lixoradata. 3515625
Create directory Test1 as '/u01/app/oracle/product/10.2.0/db_1/dbs/';
Create directory Test2 as ' +data/lixora/datafile/';
Alter tablespace Lixora offline;
Alter tablespace lixoradata offline;
exec dbms_file_transfer.copy_file (' TEST1 ', ' d:oraclefcdatalixora.dbf ', ' TEST2 ', ' Lixora. DBF ');
exec dbms_file_transfer.copy_file (' TEST1 ', ' d:oraclefcdatalixoradata.dbf ', ' TEST2 ', ' lixoradata. DBF ');
Alter DATABASE rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixora.dbf ' to ' +data/lixora/ Datafile/lixora. DBF ';
Alter DATABASE rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoradata.dbf ' to ' +data/lixora/ Datafile/lixoradata. DBF ';
Alter tablespace Lixora Online;
Alter tablespace Lixoradata Online;
++++++++++++++++++++++++++++rman Copy ++++++++++++++++++++++++++++
Rman>
SQL "alter tablespace lixora offline";
SQL "alter tablespace lixoradata offline";
Rman>
Copy datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixora.dbf ' to ' +data/lixora/datafile/lixora. DBF ';
Copy datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoradata.dbf ' to ' +DATA/lixora/datafile/ Lixoradata. DBF ';
Sql>
Alter DATABASE rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixora.dbf ' to ' +data/lixora/ Datafile/lixora. DBF ';
Alter DATABASE rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/d:oraclefcdatalixoradata.dbf ' to ' +data/lixora/ Datafile/lixoradata. DBF ';
Sql>
Alter tablespace Lixora Online;
Alter tablespace Lixoradata Online;
Summarize:
Developers have a serious lack of knowledge of Oracle Database (single-machine, RAC), which is also a management oversight;
From another aspect can also be seen, business user rights management too rough, too simple rough, need good audit, planning;
The others don't want to say more ....
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
RAC environment data file incorrectly built to RAC node local processing
