资讯 安全 论坛 下载 读书 程序开发 数据库 系统 网络 电子书 微信学院 站长学院 QQ 考试
频道栏目
Sybase| 安全资讯| 安全公告| 病毒预警| 人物| 企业招聘| 其他综合|
登录注册
首页 > 资讯 > Sybase > 正文

rac环境数据文件误建到rac节点本地处理

15-08-05        来源：[db:作者]  
收藏   我要投稿

在一次RAC例行巡检时，查询表空间的使用情况，节点1竟然抛出来，dbwr 进程无法lock ‘D:oraclefcdataLIXORA.DBF’ 这个数据文件；感觉不对啊，非常不祥的感觉；

光看名字就可以猜到，数据文件建到rac 节点2本地；

看下文件状态吧：

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 --------------吓死宝宝了！惊恐
/u01/app/oracle/product/10.2.0/db_1/dbs/D:oraclefcdataLIXORADATA.DBF ------------------吓死宝宝了！惊恐
24 rows selected.

果不其然，文件建到了rac 节点本地，而且数据文件名，要多奇葩，有多奇葩，这哥们真是纵观古今，然后大手一挥，写下‘hello word ’ 然后会心一笑。。。。。

我们的开发人员也是够惨的啊，单机的oracle也搞不清楚，更不用涉及rac了。。。。。。。

再次确认下，dbfile 确实建在了/u01/app/oracle/product/10.2.0/db_1/dbs/ 目录下

SQL> !ls /u01/app/oracle/product/10.2.0/db_1/dbs/
ab_+ASM2.dat D:oraclefcdataLIXORAtemp.DBF hc_+ASM2.dat initdw.ora orapw+ASM2
CdbsMutex D:oraclefcdataLIXORATEMP.DBF hc_orcl2.dat init.ora orapworcl2
D:oraclefcdataLIXORADATA.DBF D:\oracle\oradata\orcl\hhygbp_temp_dbf initlixora2.ora initorcl2.ora snapcf_lixora2.f
D:oraclefcdataLIXORA.DBF hc_lixora2.dat init+ASM2.ora orapwlixora2 snapcf_orcl2.f

仔细看下内容好像哪里有点问题吧？

貌似是temp 文件啊

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 -------发火

----到底是什么时候出现这个问题的呢？

SQL> select CREATION_TIME,NAME from v$datafile where name='/u01/app/oracle/product/10.2.0/db_1/dbs/D:oraclefcdataLIXORA.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:oraclefcdataLIXORADATA.DBF';
CREATION_TIM
------------
NAME
--------------------------------------------------------------------------------
18-MAY-15
/u01/app/oracle/product/10.2.0/db_1/dbs/D:oraclefcdataLIXORADATA.DBF



接下来一个个来处理下吧

------tempfile 误建到rac 节点本地处理：
temp 文件处理简单，先删除了 ，重新加个就行

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 10 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
10 LIXORATEMP NO NO YES
11 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




+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----数据文件迁移到 rac asm 存储上：

这里提供2个方法： rman cp；dbms_file_transfer.copy_file 【plsql 包 直接拷贝

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;

总结：

开发人员严重缺乏对oracle 数据库（单机，rac）的了解，当然这个也是管理上的疏忽；

从另一个方面也可以看出，业务用户权限管理太粗狂，过于简单粗暴，需要好好审计，规划下；

具体其他的就不想再多说了。。。。。。。

点击复制链接 与好友分享!回本站首页
相关TAG标签
上一篇：Nginx控制域名301跳转出现"此网页包含重定向循环"
下一篇：myibatis传多个参数
相关文章
热门专题推荐 vmware win7激活工具 win10激活工具 excel word office激活 小马激活工具 重装系统 数据恢复 u盘启动工具
图文推荐
文章
推荐
热门新闻
· 锤子坚果Pro发布后，罗永浩哭了
· 想实习的大学党看过来!这些科技巨头最
· 罗永浩锤子发布会抢先消息：锤子科技新
· Google新一代系统Fuchsia OS界面曝光
· 中国唯一连续运营20余年的网络游戏，还
· iPhone都便宜了 为何国产手机越来越贵
· 丢人！谷歌和Facebook竟被虚假企业电邮
· 中国移动支付震惊日本网友 为什么美国

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站