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

错误描述

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状态,并且目录已指向共享磁盘下的目录,至此,此次数据文件迁移工作已成功完成。

上一篇:12c 表恢复


下一篇:Oracle11G RMAN-06214: Datafile Copy /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_cpbd.f