Oracle rac环境下数据文件误建在本地目录的处理过程

于 2017-07-06 17:11:26 发布
阅读量402 收藏
点赞数

错误描述

 

Mon Nov 16 19:02:38 2015

Errors in file /u01/app/oracle/diag/rdbms/zwzwdb/zwzwdb1/trace/zwzwdb1_m000_27416.trc:

ORA-01157: cannot identify/lock data file 18 - see DBWR trace file

ORA-01110: data file 18: '/u01/app/oracle/11.2.0/db/dbs/goa31new'

Errors in file /u01/app/oracle/diag/rdbms/zwzwdb/zwzwdb1/trace/zwzwdb1_m000_27416.trc:

ORA-01157: cannot identify/lock data file 19 - see DBWR trace file

ORA-01110: data file 19: '/u01/app/oracle/11.2.0/db/dbs/goafilenew'

 

错误分析

根据【4、数据文件误建案列1】，可以知道，这是在rac环境下将数据文件误建在了本地磁盘上，导致另一个节点无法访问该数据文件，从而报出ora-01157和ora-01110错误。

直接查看数据文件路径：

SQL> select file#,name from v$datafile;

 

     FILE# NAME

---------- ---------------------------------------------

         1 +DATA/zwzwdb/datafile/system.259.855244341

         2 +DATA/zwzwdb/datafile/sysaux.260.855244345

         3 +DATA/zwzwdb/datafile/undotbs1.261.855244345

         4 +DATA/zwzwdb/datafile/undotbs2.263.855244353

         5 +DATA/zwzwdb/datafile/users.264.855244355

         6 +DATA/smsmain_def_1.dbf

         7 +DATA/smsmain_def_2.dbf

         8 +DATA/smsmain_def_3.dbf

         9 +DATA/smsmain_def_4.dbf

        10 +DATA/smsmain_idx_1.dbf

        11 +DATA/smsmain_idx_2.dbf

 

     FILE# NAME

---------- ---------------------------------------------

        12 +DATA/smsmain_def_5.dbf

        13 +DATA/smsmain_idx_3.dbf

        14 +DATA/gszwy_system_1.dbf

        15 +DATA/gszwy_system_2.dbf

        16 +DATA/gszwy_system_3.dbf

        17 +DATA/gszwy_system_4.dbf

        18 /u01/app/oracle/11.2.0/db/dbs/goa31new

        19 /u01/app/oracle/11.2.0/db/dbs/goafilenew

        20 +DATA/zwzwdb/datafile/egovcpp.293.874859211

        21 +DATA/zwzwdb/datafile/ioop_test.294.879093349

        22 +DATA/zwzwdb/datafile/ioop_file_test.295.8790

 

     FILE# NAME

---------- ---------------------------------------------

           93537

 

 

22 rows selected.

可以很明确的看到，18和19号数据文件是建立在了本地的/u01/app/oracle/11.2.0/db/dbs目录下。

 

解决方法

问题已经分析清楚了，现在根据已经知道的18、19号数据文件的目录，在每个节点对应的目录下去查看，确定该数据文件被创建在那个节点上，此处案例是创建在了节点2的本地磁盘上。

在节点2进入sqlplus环境下，下线18、19号数据文件：

SQL> alter database datafile 18 offline;

 

Database altered.

 

SQL>  alter database datafile 19 offline;

 

Database altered.

下线完毕，重新确定数据文件状态：

SQL> col name for a45

SQL> set line 234

SQL> select name , file# , status from v$datafile;

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/zwzwdb/datafile/system.259.855244341             1 SYSTEM

+DATA/zwzwdb/datafile/sysaux.260.855244345             2 ONLINE

+DATA/zwzwdb/datafile/undotbs1.261.855244345           3 ONLINE

+DATA/zwzwdb/datafile/undotbs2.263.855244353           4 ONLINE

+DATA/zwzwdb/datafile/users.264.855244355              5 ONLINE

+DATA/smsmain_def_1.dbf                                6 ONLINE

+DATA/smsmain_def_2.dbf                                7 ONLINE

+DATA/smsmain_def_3.dbf                                8 ONLINE

+DATA/smsmain_def_4.dbf                                9 ONLINE

+DATA/smsmain_idx_1.dbf                               10 ONLINE

+DATA/smsmain_idx_2.dbf                               11 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/smsmain_def_5.dbf                               12 ONLINE

+DATA/smsmain_idx_3.dbf                               13 ONLINE

+DATA/gszwy_system_1.dbf                              14 ONLINE

+DATA/gszwy_system_2.dbf                              15 ONLINE

+DATA/gszwy_system_3.dbf                              16 ONLINE

+DATA/gszwy_system_4.dbf                              17 ONLINE

+DATA/zwzwdb/datafile/goa31.339.895952649             18 RECOVER

+DATA/zwzwdb/datafile/goafile31.340.895952749         19 RECOVER

+DATA/zwzwdb/datafile/egovcpp.293.874859211           20 ONLINE

+DATA/zwzwdb/datafile/ioop_test.294.879093349         21 ONLINE

+DATA/zwzwdb/datafile/ioop_file_test.295.8790         22 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

93537

 

 

22 rows selected.

可以看到，18、19号数据文件处于recover的状态，暂时可以不用管，先在rman环境下复制数据文件到共享目录下：

RMAN> copy datafile '/u01/app/oracle/11.2.0/db/dbs/goa31new' to '+DATA';

 

Starting backup at 16-NOV-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00018 name=/u01/app/oracle/11.2.0/db/dbs/goa31new

output file name=+DATA/zwzwdb/datafile/goa31.339.895952649 tag=TAG20151116T194409 RECID=1 STAMP=895952650

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 16-NOV-15

 

RMAN> copy datafile '/u01/app/oracle/11.2.0/db/dbs/goafilenew' to '+DATA'

2> ;

 

Starting backup at 16-NOV-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00019 name=/u01/app/oracle/11.2.0/db/dbs/goafilenew

output file name=+DATA/zwzwdb/datafile/goafile31.340.895952749 tag=TAG20151116T194549 RECID=2 STAMP=895952750

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 16-NOV-15

此处需要记住上述copy结果中斜体部分的内容，用于更改系统数据文件路径：

SQL>alter database rename file '/u01/app/oracle/11.2.0/db/dbs/goa31new' to '+DATA/zwzwdb/datafile/goa31.339.895952649';

Database altered.

SQL>alter database rename file '/u01/app/oracle/11.2.0/db/dbs/goafilenew' to '+DATA/zwzwdb/datafile/goafile31.340.895952749';

Database altered.

现在在重新查看数据文件的name和status，如下：

SQL> select name , file# , status from v$datafile;

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/zwzwdb/datafile/system.259.855244341             1 SYSTEM

+DATA/zwzwdb/datafile/sysaux.260.855244345             2 ONLINE

+DATA/zwzwdb/datafile/undotbs1.261.855244345           3 ONLINE

+DATA/zwzwdb/datafile/undotbs2.263.855244353           4 ONLINE

+DATA/zwzwdb/datafile/users.264.855244355              5 ONLINE

+DATA/smsmain_def_1.dbf                                6 ONLINE

+DATA/smsmain_def_2.dbf                                7 ONLINE

+DATA/smsmain_def_3.dbf                                8 ONLINE

+DATA/smsmain_def_4.dbf                                9 ONLINE

+DATA/smsmain_idx_1.dbf                               10 ONLINE

+DATA/smsmain_idx_2.dbf                               11 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/smsmain_def_5.dbf                               12 ONLINE

+DATA/smsmain_idx_3.dbf                               13 ONLINE

+DATA/gszwy_system_1.dbf                              14 ONLINE

+DATA/gszwy_system_2.dbf                              15 ONLINE

+DATA/gszwy_system_3.dbf                              16 ONLINE

+DATA/gszwy_system_4.dbf                              17 ONLINE

+DATA/zwzwdb/datafile/goa31.339.895952649             18 RECOVER

+DATA/zwzwdb/datafile/goafile31.340.895952749         19 RECOVER

+DATA/zwzwdb/datafile/egovcpp.293.874859211           20 ONLINE

+DATA/zwzwdb/datafile/ioop_test.294.879093349         21 ONLINE

+DATA/zwzwdb/datafile/ioop_file_test.295.8790         22 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

93537

 

 

22 rows selected.

发现18、19号数据文件已成功移至指定的共享目录下，但是状态依然是recover状态，现在进行数据文件恢复：

SQL> recover datafile '+DATA/zwzwdb/datafile/goa31.339.895952649';

Media recovery complete.

SQL> recover datafile '+DATA/zwzwdb/datafile/goafile31.340.895952749';

Media recovery complete.

恢复已完成，再查看18、19号数据文件的状态：

SQL> select name,file#,status from v$datafile;

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/zwzwdb/datafile/system.259.855244341             1 SYSTEM

+DATA/zwzwdb/datafile/sysaux.260.855244345             2 ONLINE

+DATA/zwzwdb/datafile/undotbs1.261.855244345           3 ONLINE

+DATA/zwzwdb/datafile/undotbs2.263.855244353           4 ONLINE

+DATA/zwzwdb/datafile/users.264.855244355              5 ONLINE

+DATA/smsmain_def_1.dbf                                6 ONLINE

+DATA/smsmain_def_2.dbf                                7 ONLINE

+DATA/smsmain_def_3.dbf                                8 ONLINE

+DATA/smsmain_def_4.dbf                                9 ONLINE

+DATA/smsmain_idx_1.dbf                               10 ONLINE

+DATA/smsmain_idx_2.dbf                               11 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/smsmain_def_5.dbf                               12 ONLINE

+DATA/smsmain_idx_3.dbf                               13 ONLINE

+DATA/gszwy_system_1.dbf                              14 ONLINE

+DATA/gszwy_system_2.dbf                              15 ONLINE

+DATA/gszwy_system_3.dbf                              16 ONLINE

+DATA/gszwy_system_4.dbf                              17 ONLINE

+DATA/zwzwdb/datafile/goa31.339.895952649             18 OFFLINE

+DATA/zwzwdb/datafile/goafile31.340.895952749         19 OFFLINE

+DATA/zwzwdb/datafile/egovcpp.293.874859211           20 ONLINE

+DATA/zwzwdb/datafile/ioop_test.294.879093349         21 ONLINE

+DATA/zwzwdb/datafile/ioop_file_test.295.8790         22 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

93537

 

 

22 rows selected.

可以看到，18、19号数据文件已经处于offline状态，现在要做的就是将数据文件上线：

SQL> alter database datafile 18 online;

 

Database altered.

 

SQL> alter database datafile 19 online;

 

Database altered.

更改已完成，再看数据文件的状态：

SQL> select name,file#,status from v$datafile;

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/zwzwdb/datafile/system.259.855244341             1 SYSTEM

+DATA/zwzwdb/datafile/sysaux.260.855244345             2 ONLINE

+DATA/zwzwdb/datafile/undotbs1.261.855244345           3 ONLINE

+DATA/zwzwdb/datafile/undotbs2.263.855244353           4 ONLINE

+DATA/zwzwdb/datafile/users.264.855244355              5 ONLINE

+DATA/smsmain_def_1.dbf                                6 ONLINE

+DATA/smsmain_def_2.dbf                                7 ONLINE

+DATA/smsmain_def_3.dbf                                8 ONLINE

+DATA/smsmain_def_4.dbf                                9 ONLINE

+DATA/smsmain_idx_1.dbf                               10 ONLINE

+DATA/smsmain_idx_2.dbf                               11 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

+DATA/smsmain_def_5.dbf                               12 ONLINE

+DATA/smsmain_idx_3.dbf                               13 ONLINE

+DATA/gszwy_system_1.dbf                              14 ONLINE

+DATA/gszwy_system_2.dbf                              15 ONLINE

+DATA/gszwy_system_3.dbf                              16 ONLINE

+DATA/gszwy_system_4.dbf                              17 ONLINE

+DATA/zwzwdb/datafile/goa31.339.895952649             18 ONLINE

+DATA/zwzwdb/datafile/goafile31.340.895952749         19 ONLINE

+DATA/zwzwdb/datafile/egovcpp.293.874859211           20 ONLINE

+DATA/zwzwdb/datafile/ioop_test.294.879093349         21 ONLINE

+DATA/zwzwdb/datafile/ioop_file_test.295.8790         22 ONLINE

 

NAME                                               FILE# STATUS

--------------------------------------------- ---------- -------

93537

 

 

22 rows selected.

可以明确的看到，18、19号数据文件已处于online状态，并且目录已指向共享磁盘下的目录，至此，此次数据文件迁移工作已成功完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31403259/viewspace-2141674/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31403259/viewspace-2141674/

cucany584825
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值